i:10010#SparkSQL概述及原理 提供了兩種操作數(shù)據(jù)的方式? SQL查詢? DataFrame和DataSet API1提供了非常豐富的數(shù)據(jù)源API? 如:Text阻肿、JSON亥鸠、Parquet派草、MySQL等2在Spark上實(shí)現(xiàn)SQL引擎? 提供高伸縮性API：DataFrame和DataSet API? 提供高效率的查詢優(yōu)化引擎：Catalyst Optimizer# SparkSQL-DataFrameRDD + Schema? 以行為單位構(gòu)成的分布式數(shù)據(jù)集合只酥，按照列賦予不同的名稱?對select器钟，filter崇裁，aggregation和sort等操作符的抽象哀澈，在Spark1.3之前滓窍，被稱為SchemaRDD。為什么要引入SparkSQL? 寫更少的代碼? 讀更少的數(shù)據(jù)? 讓優(yōu)化器自動優(yōu)化程序? 輕松享受Spark高效的性能舉例使用SQL：select word,count(1) from words group by word使用DataFrame：wrodDF.select("userId").groupBy("userId").count()使用RDD：sc.textFile(inPath).flatMap(_.split("\t")).map((_,1)).reduceByKey(_ + _).collect舉例2讀/寫復(fù)雜的數(shù)據(jù)結(jié)構(gòu)更加便捷? 讀JSON文件val josnDF = spark.read.json("/tmp/user_json")? 讀Parquetval parquetDF = spark.read.parquet("/tmp/user_parquet")? 寫JSON文件userCoreDF2.write.json("tmp/user_json")? 寫ParquetuserCoreDF2.write.parquet("/tmp/user_parquet")特點(diǎn)：采用更高效的數(shù)據(jù)格式保存數(shù)據(jù)使用列式存儲格式（比如parquet）使用統(tǒng)計(jì)數(shù)據(jù)自動跳過數(shù)據(jù)（如：min了赌、max）使用分區(qū)查詢下推：將謂詞下推到存儲系統(tǒng)執(zhí)行#Spark1.6后墨榄，出現(xiàn)DataFrame擴(kuò)展自DataFrame API，提供編譯時(shí)類型安全勿她，面向?qū)ο箫L(fēng)格的API?DataSet API? 類型安全? 高效：代碼生成編解碼器袄秩，序列化更高效? 協(xié)作：DataSet與DataFrame可互相轉(zhuǎn)換? DataFrame = DataSet[Row]#RDD、DataFrame逢并、DataSet的關(guān)系val parquetDF = spark.read.parquet(inpath) // parquet -> dataframe? val ds = parquetDF.as[UserCore] // dataframe -> dataset? val df = ds.toDF() // dataset -> dataframe? val dsRdd = ds.rdd // dataset -> rdd? val dfRdd = df.rdd // dataframe -> rdd*將RDD轉(zhuǎn)換成DF例子見DFTest.scala//顯式創(chuàng)建DF例子見DFTest2.scala*通過jdbc之剧，創(chuàng)建mql表的DF,在本地運(yùn)行，打出表的信息注意：需要實(shí)現(xiàn)在spark2.2 jars 目錄下有mysql的jar驅(qū)動包bin/spark-submit \--class bigdata.spark.sql.test.DFTest2 \--master local \./DFTest2.jar#SparkSQL與HIve相結(jié)合使用Spark SQL訪問Hive? 將hive安裝包中conf/hive-site.xml配置文件拷貝到spark安裝包的conf目錄下? 將mysql驅(qū)動jar包拷貝到spark安裝包的jars目錄下在node01中? 啟動：spark安裝包下bin/spark-sql/home/hadoop/apps/spark2.2/bin/spark-sqlspark-sql> show databases;spark-sql>use rel;spark-sql>show tables砍聊；spark-sql>select gender,sum(core) from user_core_info where age>20 group by gender;#構(gòu)建分布式SQL查詢引擎配置HiveServer2 Thrift服務(wù)的訪問地址和端口號? 方法：在hive-site.xml文件中添加hiveserver2的配置信息hive.server2.thrift.port10010hive.server2.thrift.bind.host192.168.183.101在node01上背稼，在spark的安裝目錄下? yarn-client模式啟動sbin/start-thriftserver.sh \--hiveconf hive.server2.thrift.port=10010 \--hiveconf hive.server2.thrift.bind.host=192.168.183.101 \--master yarn \--deploy-mode client \--executor-memory 3g \--executor-cores 1 \--num-executors 2 \--driver-cores 1 \--driver-memory 1g lsof -i:10010./bin/beeline!connect jdbc:hive2://192.168.183.101:10010Connecting to jdbc:hive2://192.168.183.101:10010Enter username for jdbc:hive2://192.168.183.101:10010: hadoopEnter password for jdbc:hive2://192.168.183.101:10010: ******