Upgrade to Pro — share decks privately, control downloads, hide ads and more …

SparkSQL HadoopCon 2016

906495aee953d1a6dc3d661d28da0081?s=47 Erica Li
September 09, 2016

SparkSQL HadoopCon 2016

Spark tutorial slide:
Introduction to SparkSQL
Data sources
Generic data load & save mode
Cache in memory
User defined functions

906495aee953d1a6dc3d661d28da0081?s=128

Erica Li

September 09, 2016
Tweet

Transcript

  1. Tutorial - SparkSQL Erica Li @HadoopCon 2016 1

  2. About Me Erica Li • ericalitw • inBOUND CTO &

    Co-Founder • Taiwan Spark User Group Founder 2
  3. Agenda • Introduction to SparkSQL • Data sources • Generic

    data load & save mode • Cache in memory • User defined functions 3
  4. Introduction to SparkSQL 4

  5. Challenges for Data Scientists • Lots of analytics experts are

    NOT Java, Scala experts • Learning curve for non-research specialists • Data scientists won’t set up a large computer cluster just for RESEARCH 5
  6. Review of SQL • SQL - strunctured query language •

    Interact with databases 6
  7. SparkSQL • What is SparkSQL for? ◦ Part of distribution

    since Spark 1.0 ◦ Connected BI tools through JDBC • The history of SparkSQL • Initialize SparkSQL with SparkSession NOW 7
  8. https://ogirardot.wordpress.com/2015/05/29/rdds-are-the-new-bytecode-of-apache-spark/ 結構化資料 流式應用 機器學習 圖應用 8

  9. http://stanford.edu/~rezab/sparkclass/slides/itas_workshop.pdf Spark 2.0.0 2016.28 Jul Spark 1.6.2 2016.25.Jun Spark 1.6.1

    2016.09.Mar Spark 1.6.0 2016.04.Jan Spark 1.5.2 2015.09.Nov Spark 1.5.1 2015.02.Oct Spark 1.5.0 2015.09.Sep Spark 1.4.1 2015.15.Jul Spark 1.4.0 2015.11.Jun Spark 1.2.2 1.3.1 2015.17.Apr Spark 1.3.0 2015.13.Mar Spark 1.2.1 2015.09.Feb ... 2016 9
  10. What’s new in SparkSQL 2.0 • SparkSQL ◦ Structured API

    improvements ◦ Spark 2.0 can run all the 99 TPC-DS queries, which require many of the SQL:2003 features ◦ Visualization of dataframe query plan on webUI ◦ Multiple version of Hive (0.12 - 1.2.1) ◦ Reading several Parquet variants, e.g. impala, avro, thrift, Hive, etc ◦ ... 10
  11. Core of SparkSQL • SparkSession ◦ Start point: SQLContext SparkSession

    ◦ Upper point: HiveContext ◦ What’s the difference? ▪ HiveContext has more complete parser function, it is recommened for most use cases • Datasets (1.6+) & DataFrames 11
  12. Dataframes & Datasets • DataFrames ◦ A Dataset organized into

    named columns ◦ Structured data files, tables in Hive, external databases, or existing RDDs • Datasets ◦ A distributed collection of data ▪ Available on Scala & Java ▪ Haven’t support on Python & R 12
  13. From 1.6 to 2.0 • SparkSession ◦ Dataset API and

    DataFrame API are unified ◦ Scala & Java - Dataset ◦ Python & R - DataFrame • For example ◦ unionAll -> union ◦ registerTempTable -> createOrReplaceTempView 13
  14. https://databricks.com/blog/2016/05/11/apache-spark-2-0-technical-preview-easier-faster-and-smarter.html 14

  15. 15

  16. Data Sources 16

  17. Data Sources • Data sources API - external & built-in

    17
  18. Generic data load & save mode 18

  19. Input • Connected with local system • Run SQL on

    file directly • Connected with Hive • JDBC to other databases HDFS File Local File Hive Table 19
  20. Connected with Local • Create RDD from local JSON file

    Python filepath = "examples/src/main/resources/people.json" df = spark.read.load(filepath, format="json") df.show() Scala val df = spark.read.json("examples/src/main/resources/people.json") 20
  21. Create DF from RDD Python from pyspark.sql import Row rdd

    = sc.textFile("$SPARK_HOME/examples/src/main/resources/data.json") df = spark.read.load(rdd) Scala val rdd = sparkContext.textFile("src/main/resources/data.txt") val ds = sparkSession.read.text("src/main/resources/data.txt").as[String] 21
  22. Connected with Local • Run SQL on file directly Python

    df = spark.sql("SELECT * FROM parquet. 'examples/src/main/resources/users.parquet'") Scala val sqlDF = spark.sql("SELECT * FROM parquet.'examples/src/main/resources/users.parquet'") 22
  23. Connected with Hive • SparkSQL supports reading and writing data

    stored in Hive ◦ The Hive dependency is not included in the default Spark distribution • Check the configuration of Hive in conf/. ◦ hive-site.xml ◦ core-site.xml ◦ hdfs-site.xml 23
  24. JDBC to Other Databases • JDBC driver for the DB

    on spark classpath • From the remote DB bin/spark-shell --driver-class-path postgresql-9.4.1207.jar --jars postgresql-9.4.1207.jar Python df = spark.read.format(source="jdbc",url="jdbc:postgresql:dbserver",dbtable ="schema.tablename").load() 24
  25. Output • DataFrames can also be saved as persistent tables

    into Hive metastore using the saveAsTable command df.write.csv('mycsv.csv') df.write.format('json').save(path) df.write.parquet("mytable.parquet") 25
  26. Save Mode Scala/Java Python Description SaveMode.ErrorIfExists error If data already

    exists, an exception is expected to be thrown. SaveMode.Append append DataFrame are expected to be appended to existing data. SaveMode.Overwrite overwrite Data is expected to be overwritten by the contents of the DataFrame. SaveMode.Ignore ignore Like ‘CREATE TABLE IF NOT EXISTS’ 26
  27. DataFrame 27

  28. From SchemaRDD to DataFrame • DataFrame (since Spark 1.3) •

    Idea from Python Pandas and R data frames • Create DataFrame in three ways ◦ an existing RDD ◦ running SQL query ▪ spark.sql("SELECT * FROM table") ◦ data sources ▪ spark.read.json("s3n://path/data.json","json") 28
  29. DataFrames • A distributed collection of data named columns •

    RDD API df.map(lambda x: (x[0], float(x[2])))\ .reduceByKey(lambda x, y: x + y)\ .collect() • DataFrame API df.groupBy("category").sum("sales") 29
  30. • Common operations ◦ Selecting columns ◦ Joining different data

    sources ◦ Filtering with specific statement ◦ Aggregation (sum, count, average, etc) 30
  31. DataFrame Operations - basic • Basic DataFrame operations df.show() df.printSchema()

    df.select("name").show() df.filter(peopledf.age > 20).show() df.groupBy("name").count().show() df.join(df2, df2.uid == df.uid, "left") 31
  32. • Pandas <-> Dataframe #Convert SparkSQL DataFrame to Pandas dataframe

    pandas_df = df.toPandas() #Create a SparkSQL DataFrame from Pandas spark_df = spark.createDataFrame(pandas.DataFrame([[1, 2]]) 32
  33. • RDD <-> Dataframes - (1) # Load a text

    file and convert each line to a Row. lines = sc.textFile("examples/src/main/resources/people.txt") parts = lines.map(lambda l: l.split(",")) people = parts.map(lambda p: Row(name=p[0], age=int(p[1]))) # Infer the schema, and register the DataFrame as a table. schemaPeople = spark.createDataFrame(people) schemaPeople.createOrReplaceTempView("people") 33
  34. • RDD <-> Dataframes - (2) parts = lines.map(lambda l:

    l.split(",")) people = parts.map(lambda p: (p[0], p[1].strip())) schemaString = "name age" fields = [StructField(field_name, StringType(), True) for field_name in schemaString.split()] schema = StructType(fields) # Apply the schema to the RDD. schemaPeople = spark.createDataFrame(people, schema) # Creates a temporary view using the DataFrame schemaPeople.createOrReplaceTempView("people") 34
  35. • Schema merging Python df1 = spark.createDataFrame(sc.parallelize(range(1, 6)).map(lambda i: Row(single=i,

    double=i * 2))) df1.write.parquet("data/test_table/key=1") df2 = spark.createDataFrame(sc.parallelize(range(6, 11)).map(lambda i: Row(single=i, triple=i * 3))) df2.write.parquet("data/test_table/key=2") df3 = spark.read.option("mergeSchema","true").parquet("data/test_table") 35
  36. DataFrame Operations - For Example • Random data generation df.withColumn('normal',

    randn(seed=123)) • Descriptive statistics df.describe('col1', 'col2') • Pearson correlation df.stat.corr('col1','col2') • Cross tabulation df.stat.crosstab('col1','col2') 36
  37. #random data generation from pyspark.sql.functions import randn df1 = df.withColumn('age2',

    df.age*100) #data from standard normal distribution df2 = df.withColumn('var1', randn(seed=123)) df3 = df1.join(df2, df1.name == df2.name).select(df1.name, df1.age, 'age2', 'var1') df3.describe('age') df3.stat.corr('age', 'var1') 37
  38. Caching in memory • How to cache table in Spark

    SQL? spark.cacheTable("tableName") dataFrame.cache() • Remove table from memory spark.uncacheTable("tableName") 38
  39. User Defined Functions 39

  40. User Defined Functions • How to create udf in SparkSQL?

    • Concept - Lambda • In Python sqlContext.registerFunction('strLength', lambda x: len(x)) sqlContext.sql("SELECT col1, \ strLength(col2) AS lenAct \ FROM mytable") 40
  41. Review • SparkSQL 1.6 vs. 2.0 • SQL query from

    Hive, and local files • DataFrame operations • Key points for user defined functions • Cache file for iteration 41
  42. ? • Faster than our Relational Databases? • Spark on

    Hadoop? • Performance tuning? • Which language API is the best? 42