[大数据]从Hive平滑过渡到Spark SQL

Hive->Spark SQL

SQLContext使用

1.概述

Spark1.x的入口

The entry point(入口点) into all functionality in Spark SQL is the SQLContext class, or one of its descendants. To create a basic SQLContext, all you need is a SparkContext.

2.Maven依赖

#1.构建Maven项目
org.scala-tools.archetypes:scala-archetype-simple

#2.修改默认scala版本
<scala.version>2.11.8</scala.version>

#3.添加依赖
<dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-sql_2.11</artifactId>
      <version>2.3.1</version>
</dependency>

3.SQLContext处理Json文件

package com.saddam.spark.MuKe.Hive过渡到SparkSQL

import org.apache.spark.{SparkConf, SparkContext}
import org.apache.spark.sql.SQLContext

/**
  * SQLContext的使用之处理Json文件(本地执行)
  *
  * 服务器执行：
  * sparkConf.setAppName("SQLContext").setMaster("local[2]")注释掉
  * load(args(0))
  */
object SQLContextApp {
  def main(args: Array[String]): Unit = {
    //1.创建相应的Context
    val sparkConf=new SparkConf()
    sparkConf.setAppName("SQLContext").setMaster("local[2]")

    val sc=new SparkContext(sparkConf)
    val sqlContext=new SQLContext(sc)//过时了

    //2.相关处理(业务逻辑)
    /**
      * 处理json文件
      */
    //TODO load返回的是一个DataFrame，也就是一张表
    val DF = sqlContext.read.format("json").load("D:\\Spark\\DataSets\\SQLContextDatas\\people.json")
//    val DF = sqlContext.read.format("json").load(args(0))

    //打印schema信息
    DF.printSchema()
    /*
    root
 |-- age: long (nullable = true)
 |-- name: string (nullable = true)
     */

    //打印全部
    DF.show()
/*
+----+-------+
| age|   name|
+----+-------+
|null|Michael|
|  30|   Andy|
|  19| Justin|
+----+-------+
 */
    //3.关闭资源
    sc.stop()
  }

}

4.打包至服务器执行

#1.通过IDEA编写代码，使用Maven的package打包成jar

#2.把打包好的jar上传至服务器

#3.提交(官网代码)
./bin/spark-submit \
  --class <main-class>
  --name <class-name>
  --master <master-url> \
  --deploy-mode <deploy-mode> \
  --conf <key>=<value> \
  ... # other options
  <application-jar> \
  [application-arguments]
  
------------------命令行代码测试-------------------------
spark-submit \
--class com.saddam.spark.MuKe.SparkSQL.SQLContextApp \
--master local[2] \
/usr/local/src/spark/lib/Spark-1.0.jar \
/usr/local/src/spark/examples/src/main/resources/people.json

------------------shell 脚本测试------------------------
#1.构建sh脚本
[root@CQ-WEB-Centos1 spark]# vi sqlContext.sh

#2.将命令行测试代码拷贝入sh内

#3.给shell文件赋权
[root@CQ-WEB-Centos1 spark]# chmod u+x sqlContext.sh

#4.执行shell文件
[root@CQ-WEB-Centos1 spark]# ./sqlContext.sh

HiveContext使用

1.概述

 To use a HiveContext, you do not need to have an existing Hive setup(设置)

2.Maven依赖

#1.pom.xml文件中添加依赖
<!-- SparkSQL  ON  Hive-->
    <dependency>
      <groupId>org.apache.spark</groupId>
      <artifactId>spark-hive_2.11</artifactId>
      <version>2.3.1</version>
    </dependency>

3.HiveContext访问Hive表

package com.saddam.spark.MuKe.SparkSQL

import org.apache.spark.sql.hive.HiveContext
import org.apache.spark.{SparkConf, SparkContext}

object HiveContextApp {
  def main(args: Array[String]): Unit = {

    //1.创建相应的Context
    val sparkConf=new SparkConf()
      //.setAppName("HiveContext").setMaster("local[2]")

    val sc =new SparkContext(sparkConf)

    //创建HiveContext
    val hiveContext=new HiveContext(sc)


    //2.业务逻辑

    hiveContext.table("emp").show()

	//3.关闭
    sc.stop()
  }
}

4.打包至服务器运行

spark-submit \
--class com.saddam.spark.MuKe.SparkSQL.HiveContextApp \
--master local[2] \
--jars /usr/local/src/spark/lib/mysql-connector-java-5.1.27-bin.jar \ 
/usr/local/src/spark/lib/Spark-1.0.jar 

---注意：#指定mysql驱动

SparkSession使用

1.概述

The entry point into all functionality in Spark is the SparkSession class. To create a basic SparkSession, just use SparkSession.builder():

2.SparkSession处理Json文件

package com.saddam.spark.MuKe.SparkSQL


import org.apache.spark.sql.SparkSession

object SparkSessionApp {
  def main(args: Array[String]): Unit = {

    //1.创建SparkSession
    val spark=SparkSession.builder().appName("SparkSessionApp").master("local[2]").getOrCreate()

    //2.业务逻辑：处理Json文件
//    spark.read.format("json")
    val people = spark.read.json("D:\\Spark\\DataSets\\people.json")
    
    people.show()

    //3.关闭资源
    spark.stop()
  }

}

3.打包至服务器运行

spark-submit \
--class com.saddam.spark.MuKe.SparkSQL.SparkSessionApp \
--master local[2] \
/usr/local/src/spark/lib/Spark-1.0.jar \
/usr/local/src/spark/examples/src/main/resources/people.json

spark-shell使用

1.启动

[root@CQ-WEB-Centos1 shell]# spark-shell --master local[2]

2.访问Hive

#1.将hive/conf/hive-site.xml 拷贝至spark/conf
[root@CQ-WEB-Centos1 ~]# cp /usr/local/src/hive/conf/hive-site.xml /usr/local/src/spark/conf
#2.启动需要指定mysql驱动包
[root@CQ-WEB-Centos1 shell]# spark-shell --master local[2] --jars /usr/local/src/spark/lib/mysql-connector-java-5.1.27-bin.jar

scala> spark.sql("select * from emp").show
+-----+------+---------+----+----------+-------+-------+------+
|empno| ename|      job| mrg|  huredate|    sal|   comm|deptno|
+-----+------+---------+----+----------+-------+-------+------+
| 7369| SMITH|    CLERK|7902|1980-12-17| 800.00|       |    20|
| 7499| ALLEN| SALESMAN|7698| 1981-2-20|1600.00| 300.00|    30|
| 7521|  WARD| SALESMAN|7698| 1981-2-22|1250.00| 500.00|    30|
| 7566| JONES|  MANAGER|7839|  1981-4-2|2975.00|       |    20|
| 7654|MARTIN| SALESMAN|7698| 1981-9-28|1250.00|1400.00|    30|
| 7698| BLAKE|  MANAGER|7839|  1981-5-1|2850.00|       |    30|
| 7782| CLARK|  MANAGER|7839|  1981-6-9|2450.00|       |    10|
| 7788| SCOTT|  ANALYST|7566| 1987-4-19|3000.00|       |    20|
| 7839|  KING|PRESIDENT|    |1981-11-17|5000.00|       |    10|
| 7844|TURNER| SALESMAN|7698|  1981-9-8|1500.00|   0.00|    30|
| 7876| ADAMS|    CLERK|7788| 1987-5-23|1100.00|       |    20|
| 7900| JAMES|    CLERK|7698| 1981-12-3| 950.00|       |    30|
| 7902|  FORD|  ANALYST|7566| 1981-12-3|3000.00|       |    20|
| 7934|MILLER|    CLERK|7782| 1982-1-23|1300.00|       |    10|
+-----+------+---------+----+----------+-------+-------+------+

3.启动Error

解决方案

#修改hive-site.xml
[root@CQ-WEB-Centos1 conf]# vi spark/conf/hive-site.xml 

<property>
	<name>hive.metastore.schema.verification</name>
	<value>false</value>
</property>

4.Spark shell 帮助

[root@CQ-WEB-Centos1 ~]# spark-shell --help
Usage: ./bin/spark-shell [options]

Options:
  --master MASTER_URL         spark://host:port, mesos://host:port, yarn, or local.
  --deploy-mode DEPLOY_MODE   Whether to launch the driver program locally ("client") or
                              on one of the worker machines inside the cluster ("cluster")
                              (Default: client).
  --class CLASS_NAME          Your application's main class (for Java / Scala apps).
  --name NAME                 A name of your application.
  --jars JARS                 Comma-separated list of local jars to include on the driver
                              and executor classpaths.
  --packages                  Comma-separated list of maven coordinates of jars to include
                              on the driver and executor classpaths. Will search the local
                              maven repo, then maven central and any additional remote
                              repositories given by --repositories. The format for the
                              coordinates should be groupId:artifactId:version.
  --exclude-packages          Comma-separated list of groupId:artifactId, to exclude while
                              resolving the dependencies provided in --packages to avoid
                              dependency conflicts.
  --repositories              Comma-separated list of additional remote repositories to
                              search for the maven coordinates given with --packages.
  --py-files PY_FILES         Comma-separated list of .zip, .egg, or .py files to place
                              on the PYTHONPATH for Python apps.
  --files FILES               Comma-separated list of files to be placed in the working
                              directory of each executor.

  --conf PROP=VALUE           Arbitrary Spark configuration property.
  --properties-file FILE      Path to a file from which to load extra properties. If not
                              specified, this will look for conf/spark-defaults.conf.

  --driver-memory MEM         Memory for driver (e.g. 1000M, 2G) (Default: 1024M).
  --driver-java-options       Extra Java options to pass to the driver.
  --driver-library-path       Extra library path entries to pass to the driver.
  --driver-class-path         Extra class path entries to pass to the driver. Note that
                              jars added with --jars are automatically included in the
                              classpath.

  --executor-memory MEM       Memory per executor (e.g. 1000M, 2G) (Default: 1G).

  --proxy-user NAME           User to impersonate when submitting the application.
                              This argument does not work with --principal / --keytab.

  --help, -h                  Show this help message and exit.
  --verbose, -v               Print additional debug output.
  --version,                  Print the version of current Spark.

 Spark standalone with cluster deploy mode only:
  --driver-cores NUM          Cores for driver (Default: 1).

 Spark standalone or Mesos with cluster deploy mode only:
  --supervise                 If given, restarts the driver on failure.
  --kill SUBMISSION_ID        If given, kills the driver specified.
  --status SUBMISSION_ID      If given, requests the status of the driver specified.

 Spark standalone and Mesos only:
  --total-executor-cores NUM  Total cores for all executors.

 Spark standalone and YARN only:
  --executor-cores NUM        Number of cores per executor. (Default: 1 in YARN mode,
                              or all available cores on the worker in standalone mode)

 YARN-only:
  --driver-cores NUM          Number of cores used by the driver, only in cluster mode
                              (Default: 1).
  --queue QUEUE_NAME          The YARN queue to submit to (Default: "default").
  --num-executors NUM         Number of executors to launch (Default: 2).
                              If dynamic allocation is enabled, the initial number of
                              executors will be at least NUM.
  --archives ARCHIVES         Comma separated list of archives to be extracted into the
                              working directory of each executor.
  --principal PRINCIPAL       Principal to be used to login to KDC, while running on
                              secure HDFS.
  --keytab KEYTAB             The full path to the file that contains the keytab for the
                              principal specified above. This keytab will be copied to
                              the node running the Application Master via the Secure
                              Distributed Cache, for renewing the login tickets and the
                              delegation tokens periodically.

spark-sql使用

1.启动

启动直接可以访问Hive

[root@CQ-WEB-Centos1 ~]# spark-sql --master local[2] --jars /usr/local/src/spark/lib/mysql-connector-java-5.1.27-bin.jar

2.Spark-sql执行计划

#1.在spark-sql中建表
spark-sql> create table t(key string,value string);
-----------------------------------------------------------------------------------------
spark-sql>explain select a.key*(2+3),b.value from t as a join t as b on a.key=b.key and a.key>3;

== Physical Plan ==
*Project [(cast(key#13 as double) * 5.0) AS (CAST(key AS DOUBLE) * CAST((2 + 3) AS DOUBLE))#17, value#16]
+- *SortMergeJoin [key#13], [key#15], Inner
   :- *Sort [key#13 ASC NULLS FIRST], false, 0
   :  +- Exchange hashpartitioning(key#13, 200)
   :     +- *Filter (isnotnull(key#13) && (cast(key#13 as double) > 3.0))
   :        +- HiveTableScan [key#13], MetastoreRelation default, t
   +- *Sort [key#15 ASC NULLS FIRST], false, 0
      +- Exchange hashpartitioning(key#15, 200)
         +- *Filter (isnotnull(key#15) && (cast(key#15 as double) > 3.0))
            +- HiveTableScan [key#15, value#16], MetastoreRelation default, t
-----------------------------------------------------------------------------------------
spark-sql>explain extended select a.key*(2+3),b.value from t as a join t as b on a.key=b.key and a.key>3;

== Parsed Logical Plan ==
'Project [unresolvedalias(('a.key * (2 + 3)), None), 'b.value]
+- 'Join Inner, (('a.key = 'b.key) && ('a.key > 3))
   :- 'UnresolvedRelation `t`, a
   +- 'UnresolvedRelation `t`, b

== Analyzed Logical Plan ==
(CAST(key AS DOUBLE) * CAST((2 + 3) AS DOUBLE)): double, value: string
Project [(cast(key#21 as double) * cast((2 + 3) as double)) AS (CAST(key AS DOUBLE) * CAST((2 + 3) AS DOUBLE))#25, value#24]
+- Join Inner, ((key#21 = key#23) && (cast(key#21 as double) > cast(3 as double)))
   :- SubqueryAlias a
   :  +- MetastoreRelation default, t
   +- SubqueryAlias b
      +- MetastoreRelation default, t

== Optimized(优化) Logical Plan ==
Project [(cast(key#21 as double) * 5.0) AS (CAST(key AS DOUBLE) * CAST((2 + 3) AS DOUBLE))#25, value#24]
+- Join Inner, (key#21 = key#23)
   :- Project [key#21]
   :  +- Filter (isnotnull(key#21) && (cast(key#21 as double) > 3.0))
   :     +- MetastoreRelation default, t
   +- Filter ((cast(key#23 as double) > 3.0) && isnotnull(key#23))
      +- MetastoreRelation default, t

== Physical Plan ==
*Project [(cast(key#21 as double) * 5.0) AS (CAST(key AS DOUBLE) * CAST((2 + 3) AS DOUBLE))#25, value#24]
+- *SortMergeJoin [key#21], [key#23], Inner
   :- *Sort [key#21 ASC NULLS FIRST], false, 0
   :  +- Exchange hashpartitioning(key#21, 200)
   :     +- *Filter (isnotnull(key#21) && (cast(key#21 as double) > 3.0))
   :        +- HiveTableScan [key#21], MetastoreRelation default, t
   +- *Sort [key#23 ASC NULLS FIRST], false, 0
      +- Exchange hashpartitioning(key#23, 200)
         +- *Filter ((cast(key#23 as double) > 3.0) && isnotnull(key#23))
            +- HiveTableScan [key#23, value#24], MetastoreRelation default, t

thriftsever/beeline使用

先启动thriftsever，再启动beeline

1.启动thriftsever

[root@CQ-WEB-Centos1 sbin]# ./start-thriftserver.sh --master local[2] --jars /usr/local/src/spark/lib/mysql-connector-java-5.1.27-bin.jar

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-QvLSOEfZ-1666592203879)(screenshots\MuKe\启动Thriftserver网页页面.png)]

2.查看进程

[root@CQ-WEB-Centos1 sbin]# jps -m

3859 SparkSubmit --master local[2] --class org.apache.spark.sql.hive.thriftserver.HiveThriftServer2 --name Thrift JDBC/ODBC Server --jars /usr/local/src/spark/lib/mysql-connector-java-5.1.27-bin.jar spark-internal

#网页查看
http://master:4040

3.启动beeline

[root@CQ-WEB-Centos1 bin]# ./beeline -u jdbc:hive2://localhost:10000 -n root

Connecting to jdbc:hive2://localhost:10000
log4j:WARN No appenders could be found for logger (org.apache.hive.jdbc.Utils).
log4j:WARN Please initialize the log4j system properly.
log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig for more info.
Connected to: Spark SQL (version 2.1.0)
Driver: Hive JDBC (version 1.2.1.spark2)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.1.spark2 by Apache Hive

0: jdbc:hive2://localhost:10000> show tables;
+-----------+------------+--------------+--+
| database  | tableName  | isTemporary  |
+-----------+------------+--------------+--+
| default   | emp        | false        |
| default   | t          | false        |
+-----------+------------+--------------+--+
2 rows selected (0.617 seconds)

0: jdbc:hive2://localhost:10000> select * from emp limit 5;
+--------+---------+-----------+-------+-------------+----------+----------+---------+--+
| empno  |  ename  |    job    |  mrg  |  huredate   |   sal    |   comm   | deptno  |
+--------+---------+-----------+-------+-------------+----------+----------+---------+--+
| 7369   | SMITH   | CLERK     | 7902  | 1980-12-17  | 800.00   |          | 20      |
| 7499   | ALLEN   | SALESMAN  | 7698  | 1981-2-20   | 1600.00  | 300.00   | 30      |
| 7521   | WARD    | SALESMAN  | 7698  | 1981-2-22   | 1250.00  | 500.00   | 30      |
| 7566   | JONES   | MANAGER   | 7839  | 1981-4-2    | 2975.00  |          | 20      |
| 7654   | MARTIN  | SALESMAN  | 7698  | 1981-9-28   | 1250.00  | 1400.00  | 30      |
+--------+---------+-----------+-------+-------------+----------+----------+---------+--+
5 rows selected (1.936 seconds)

4.beeline优点及其注意事项

#优点
	可以通过多个客户端同时使用
	
#注意事项
	默认端口10000，可以修改
[root@CQ-WEB-Centos1 sbin]# start-thriftserver.sh \
--master local[2] \
--jars /usr/local/src/spark/lib/mysql-connector-java-5.1.27-bin.jar	\
--hiveconf hive.server2.thrift.port=14000

5.thriftserver和sql/shell区别

(1)spark-sql/spark-shell都是一个spark application
(2)thriftserver,不管启动多少客户端，永远都是一个spark application
a.申请资源只需要申请一次即可
b.解决数据共享问题，多个客户端可以共享数据

JDBC方式编程访问SSQL

1.Maven依赖

<dependency>
      <groupId>org.spark-project.hive</groupId>
      <artifactId>hive-jdbc</artifactId>
      <version>1.2.1.spark</version>
</dependency>

2.代码实现

package com.saddam.spark.MuKe.SparkSQL

import java.sql.DriverManager

/**
  * 通过JDBC方式访问
  */
object SparkSQLThriftServerAPP {
  def main(args: Array[String]): Unit = {
    //第一步：实例化driver
    Class.forName("org.apache.hive.jdbc.HiveDriver")

    val conn = DriverManager.getConnection("jdbc:hive2://183.230.36.250:10000","root","netsky321")

    val pstmt = conn.prepareStatement("select empno,ename,sal from emp")

    val result = pstmt.executeQuery()

    while(result.next()){
      println("empno:"+result.getString("empno")+",ename:"+result.getString("ename")+",sal"+result.getString("sal"))
    }

    result.close()
    pstmt.close()
    conn.close()
  }
}