Spark SQL 快速入门系列（五）SparkSQL 访问 Hive
程序员文章站
2022-04-01 16:21:20
...
访问 Hive
导读
1，整合 SparkSQL 和 Hive, 使用 Hive 的 MetaStore 元信息库
2，使用 SparkSQL 查询 Hive 表
3，案例, 使用常见 HiveSQL
4，写入内容到 Hive 表
SparkSQL 整合 Hive
导读
1，开启 Hive 的 MetaStore 独立进程
2，整合 SparkSQL 和 Hive 的 MetaStore
和一个文件格式不同, Hive 是一个外部的数据存储和查询引擎, 所以如果 Spark 要访问 Hive 的话, 就需要先整合 Hive
整合什么 ?
如果要讨论 SparkSQL 如何和 Hive 进行整合, 首要考虑的事应该是 Hive 有什么, 有什么就整合什么就可以
- MetaStore, 元数据存储
SparkSQL 内置的有一个 MetaStore, 通过嵌入式数据库 Derby 保存元信息, 但是对于生产环境来说, 还是应该使用 Hive 的 MetaStore, 一是更成熟, 功能更强, 二是可以使用 Hive 的元信息 - 查询引擎
SparkSQL 内置了 HiveSQL 的支持, 所以无需整合
为什么要开启 Hive 的 MetaStore
Hive 的 MetaStore 是一个 Hive 的组件, 一个 Hive 提供的程序, 用以保存和访问表的元数据, 整个 Hive 的结构大致如下
由上图可知道, 其实 Hive 中主要的组件就三个, HiveServer2 负责接受外部系统的查询请求, 例如 JDBC, HiveServer2 接收到查询请求后, 交给 Driver 处理, Driver 会首先去询问 MetaStore 表在哪存, 后 Driver 程序通过 MR 程序来访问 HDFS 从而获取结果返回给查询请求者
而 Hive 的 MetaStore 对 SparkSQL 的意义非常重大, 如果 SparkSQL 可以直接访问 Hive 的 MetaStore, 则理论上可以做到和 Hive 一样的事情, 例如通过 Hive 表查询数据
而 Hive 的 MetaStore 的运行模式有三种
- 内嵌 Derby 数据库模式
这种模式不必说了, 自然是在测试的时候使用, 生产环境不太可能使用嵌入式数据库, 一是不稳定, 二是这个 Derby 是单连接的, 不支持并发
- Local 模式
Local 和 Remote 都是访问 MySQL 数据库作为存储元数据的地方, 但是 Local 模式的 MetaStore 没有独立进程, 依附于 HiveServer2 的进程
- Remote 模式
和 Loca 模式一样, 访问 MySQL 数据库存放元数据, 但是 Remote 的 MetaStore 运行在独立的进程中
我们显然要选择 Remote 模式, 因为要让其独立运行, 这样才能让 SparkSQL 一直可以访问
Hive 开启 MetaStore
Step 1: 修改 hive-site.xml
<?xml version="1.0"?>
<?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://Bigdata01:3306/metastore?createDatabaseIfNotExist=true</value>
<description>JDBC connect string for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>Driver class name for a JDBC metastore</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against metastore database</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>000000</value>
<description>password to use against metastore database</description>
</property>
<property>
<name>hive.cli.print.header</name>
<value>true</value>
</property>
<property>
<name>hive.cli.print.current.db</name>
<value>true</value>
</property>
<property>
<name>hive.metastore.warehouse.dir</name>
<value>/user/hive/warehouse</value>
</property>
<property>
<name>hive.metastore.local</name>
<value>false</value>
</property>
<property>
<name>hive.metastore.uris</name>
<value>thrift://Bigdata01:9083</value>
</property>
</configuration>
Step 2: 启动 Hive MetaStore
nohup /opt/module/hive/bin/hive --service metastore 2>&1 >> /opt/module/hive/logs/log.log &
SparkSQL 整合 Hive 的 MetaStore
即使不去整合 MetaStore, Spark 也有一个内置的 MateStore, 使用 Derby 嵌入式数据库保存数据, 但是这种方式不适合生产环境, 因为这种模式同一时间只能有一个 SparkSession 使用, 所以生产环境更推荐使用 Hive 的 MetaStore
SparkSQL 整合 Hive 的 MetaStore 主要思路就是要通过配置能够访问它, 并且能够使用 HDFS 保存 WareHouse, 这些配置信息一般存在于 Hadoop 和 HDFS 的配置文件中, 所以可以直接拷贝 Hadoop 和 Hive 的配置文件到 Spark 的配置目录
cd /opt/module/hadoop/etc/hadoop
cp hive-site.xml core-site.xml hdfs-site.xml /opt/module/spark/conf/
scp -r /opt/module/spark/conf Bigdata02:`pwd`
scp -r /opt/module/spark/conf Bigdata03:`pwd`
Spark 需要 hive-site.xml 的原因是, 要读取 Hive 的配置信息, 主要是元数据仓库的位置等信息
Spark 需要 core-site.xml 的原因是, 要读取安全有关的配置
Spark 需要 hdfs-site.xml 的原因是, 有可能需要在 HDFS 中放置表文件, 所以需要 HDFS 的配置
如果不希望通过拷贝文件的方式整合 Hive, 也可以在 SparkSession 启动的时候, 通过指定 Hive 的 MetaStore 的位置来访问, 但是更推荐整合的方式
访问 Hive 表
导读
1，在 Hive 中创建表
2，使用 SparkSQL 访问 Hive 中已经存在的表
3，使用 SparkSQL 创建 Hive 表
4，使用 SparkSQL 修改 Hive 表中的数据
创建文件名称 ：studenttabl10k
添加数据如下：（只添加150行）
ulysses thompson 64 1.90
katie carson 25 3.65
luke king 65 0.73
holly davidson 57 2.43
fred miller 55 3.77
holly white 43 0.24
luke steinbeck 51 1.14
nick underhill 31 2.46
holly davidson 59 1.26
calvin brown 56 0.72
rachel robinson 62 2.25
tom carson 35 0.56
tom johnson 72 0.99
irene garcia 54 1.06
oscar nixon 39 3.60
holly allen 32 2.58
oscar hernandez 19 0.05
alice ichabod 65 2.25
wendy thompson 30 2.39
priscilla hernandez 73 0.23
gabriella van buren 68 1.32
yuri thompson 42 3.65
yuri laertes 60 1.16
sarah young 23 2.76
zach white 32 0.20
nick van buren 68 1.75
xavier underhill 41 1.51
bob ichabod 56 2.81
zach steinbeck 61 2.22
alice garcia 42 2.03
jessica king 29 3.61
calvin nixon 37 0.30
fred polk 66 3.69
bob zipper 40 0.28
alice young 75 0.31
nick underhill 37 1.65
mike white 57 0.69
calvin ovid 41 3.02
fred steinbeck 47 3.57
sarah ovid 65 0.00
wendy nixon 63 0.62
gabriella zipper 77 1.51
david king 40 1.99
jessica white 30 3.82
alice robinson 37 3.69
zach nixon 74 2.75
irene davidson 27 1.22
priscilla xylophone 43 1.60
oscar zipper 25 2.43
fred falkner 38 2.23
ulysses polk 58 0.01
katie hernandez 47 3.80
zach steinbeck 55 0.68
fred laertes 69 3.62
quinn laertes 70 3.66
nick garcia 50 0.12
oscar young 55 2.22
bob underhill 47 0.24
calvin young 77 1.60
mike allen 65 2.95
david young 77 0.26
oscar garcia 69 1.59
ulysses ichabod 26 0.95
wendy laertes 76 1.13
sarah laertes 20 0.24
zach ichabod 60 1.60
tom robinson 62 0.78
zach steinbeck 69 1.01
quinn garcia 57 0.98
yuri van buren 32 1.97
luke carson 39 0.76
calvin ovid 73 0.82
luke ellison 27 0.56
oscar zipper 50 1.31
fred steinbeck 52 3.14
katie xylophone 76 1.38
luke king 54 2.30
ethan white 72 1.43
yuri ovid 37 3.64
jessica garcia 54 1.08
luke young 29 0.80
mike miller 39 3.35
fred hernandez 63 0.17
priscilla hernandez 52 0.35
ethan garcia 43 1.70
quinn hernandez 25 2.58
calvin nixon 33 1.01
yuri xylophone 47 1.36
ulysses steinbeck 63 1.05
jessica nixon 25 2.13
bob johnson 53 3.31
jessica ichabod 56 2.21
zach miller 63 3.87
priscilla white 66 2.82
ulysses allen 21 1.68
katie falkner 47 1.49
tom king 51 1.91
bob laertes 60 3.33
luke nixon 27 3.54
quinn johnson 42 2.24
wendy quirinius 71 0.10
victor polk 55 3.63
rachel robinson 32 1.11
sarah king 57 1.37
victor young 38 1.72
priscilla steinbeck 38 2.11
fred brown 19 2.72
xavier underhill 55 3.56
irene ovid 67 3.80
calvin brown 37 2.22
katie thompson 20 3.27
katie carson 66 3.55
tom miller 57 2.83
rachel brown 56 0.74
holly johnson 38 2.51
irene steinbeck 29 1.97
wendy falkner 37 0.14
ethan white 29 3.62
bob underhill 26 1.10
jessica king 64 0.69
luke steinbeck 19 1.16
luke laertes 70 3.58
rachel polk 74 0.92
calvin xylophone 52 0.58
luke white 57 3.86
calvin van buren 52 3.13
holly quirinius 59 1.70
mike brown 44 1.93
yuri ichabod 61 0.70
ulysses miller 56 3.53
victor hernandez 64 2.52
oscar young 34 0.34
luke ovid 36 3.17
quinn ellison 50 1.13
quinn xylophone 72 2.07
nick underhill 48 0.15
rachel miller 23 3.38
mike van buren 68 1.74
zach van buren 38 0.34
irene zipper 32 0.54
sarah garcia 31 3.87
rachel van buren 56 0.35
fred davidson 69 1.57
nick hernandez 19 2.11
irene polk 40 3.89
katie young 26 2.88
priscilla ovid 49 3.28
jessica hernandez 39 3.13
yuri allen 29 3.51
victor garcia 66 3.45
在 Hive 中创建表
第一步, 需要先将文件上传到集群中, 使用如下命令上传到 HDFS 中
hdfs dfs -mkdir -p /input
hdfs dfs -put studenttabl10k /input/
第二步, 使用 Hive 或者 Beeline 执行如下 SQL
CREATE DATABASE IF NOT EXISTS spark_integrition;
USE spark_integrition;
CREATE EXTERNAL TABLE student
(
name STRING,
age INT,
gpa string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse';
LOAD DATA INPATH '/input/studenttab10k' OVERWRITE INTO TABLE student;
通过 SparkSQL 查询 Hive 的表
查询 Hive 中的表可以直接通过 spark.sql(…) 来进行, 可以直接在其中访问 Hive 的 MetaStore, 前提是一定要将 Hive 的配置文件拷贝到 Spark 的 conf 目录
[aaa@qq.com bin]# ./spark-shell --master local[6]
20/09/03 20:55:20 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
Spark context Web UI available at http://Bigdata01:4040
Spark context available as 'sc' (master = local[6], app id = local-1599137751998).
Spark session available as 'spark'.
Welcome to
____ __
/ __/__ ___ _____/ /__
_\ \/ _ \/ _ `/ __/ '_/
/___/ .__/\_,_/_/ /_/\_\ version 2.4.6
/_/
Using Scala version 2.11.12 (Java HotSpot(TM) 64-Bit Server VM, Java 1.8.0_144)
Type in expressions to have them evaluated.
Type :help for more information.
scala> spark.sql("use spark_integrition")
20/09/03 20:56:45 WARN HiveConf: HiveConf of name hive.metastore.local does not exist
res0: org.apache.spark.sql.DataFrame = []
scala> spark.sql("select * from student limit 100")
res1: org.apache.spark.sql.DataFrame = [name: string, age: int ... 1 more field]
scala> res1.show()
+-------------------+---+----+
| name|age| gpa|
+-------------------+---+----+
| ulysses thompson| 64|1.90|
| katie carson| 25|3.65|
| luke king| 65|0.73|
| holly davidson| 57|2.43|
| fred miller| 55|3.77|
| holly white| 43|0.24|
| luke steinbeck| 51|1.14|
| nick underhill| 31|2.46|
| holly davidson| 59|1.26|
| calvin brown| 56|0.72|
| rachel robinson| 62|2.25|
| tom carson| 35|0.56|
| tom johnson| 72|0.99|
| irene garcia| 54|1.06|
| oscar nixon| 39|3.60|
| holly allen| 32|2.58|
| oscar hernandez| 19|0.05|
| alice ichabod| 65|2.25|
| wendy thompson| 30|2.39|
|priscilla hernandez| 73|0.23|
+-------------------+---+----+
only showing top 20 rows
通过 SparkSQL 创建 Hive 表
通过 SparkSQL 可以直接创建 Hive 表, 并且使用 LOAD DATA 加载数据
[aaa@qq.com bin]# ./spark-shell --master local[6]
20/09/03 21:17:37 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
Setting default log level to "WARN".
To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use setLogLevel(newLevel).
Spark context Web UI available at http://Bigdata01:4040
Spark context available as 'sc' (master = local[6], app id = local-1599139087222).
Spark session available as 'spark'.
Welcome to
____ __
/ __/__ ___ _____/ /__
_\ \/ _ \/ _ `/ __/ '_/
/___/ .__/\_,_/_/ /_/\_\ version 2.4.6
/_/
Using Scala version 2.11.12 (Java HotSpot(TM) 64-Bit Server VM, Java 1.8.0_144)
Type in expressions to have them evaluated.
Type :help for more information.
scala> :paste
// Entering paste mode (ctrl-D to finish)
val createTableStr =
"""
|create EXTERNAL TABLE student
|(
| name STRING,
| age INT,
| gpa string
|)
|ROW FORMAT DELIMITED
| FIELDS TERMINATED BY '\t'
| LINES TERMINATED BY '\n'
|STORED AS TEXTFILE
|LOCATION '/user/hive/warehouse'
""".stripMargin
spark.sql("CREATE DATABASE IF NOT EXISTS spark_integrition1")
spark.sql("USE spark_integrition1")
spark.sql(createTableStr)
spark.sql("LOAD DATA INPATH '/input/studenttab10k' OVERWRITE INTO TABLE student")
// Exiting paste mode, now interpreting.
20/09/03 21:20:57 WARN HiveConf: HiveConf of name hive.metastore.local does not exist
20/09/03 21:21:01 ERROR KeyProviderCache: Could not find uri with key [dfs.encryption.key.provider.uri] to create a keyProvider !!
createTableStr: String =
"
create EXTERNAL TABLE student
(
name STRING,
age INT,
gpa string
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\t'
LINES TERMINATED BY '\n'
STORED AS TEXTFILE
LOCATION '/user/hive/warehouse'
"
res0: org.apache.spark.sql.DataFrame = []
scala> spark.sql("select * from student limit 100")
res1: org.apache.spark.sql.DataFrame = [name: string, age: int ... 1 more field]
scala> res1.where('age > 50).show()
+-------------------+---+----+
| name|age| gpa|
+-------------------+---+----+
| ulysses thompson| 64|1.90|
| luke king| 65|0.73|
| holly davidson| 57|2.43|
| fred miller| 55|3.77|
| luke steinbeck| 51|1.14|
| holly davidson| 59|1.26|
| calvin brown| 56|0.72|
| rachel robinson| 62|2.25|
| tom johnson| 72|0.99|
| irene garcia| 54|1.06|
| alice ichabod| 65|2.25|
|priscilla hernandez| 73|0.23|
|gabriella van buren| 68|1.32|
| yuri laertes| 60|1.16|
| nick van buren| 68|1.75|
| bob ichabod| 56|2.81|
| zach steinbeck| 61|2.22|
| fred polk| 66|3.69|
| alice young| 75|0.31|
| mike white| 57|0.69|
+-------------------+---+----+
only showing top 20 rows
目前 SparkSQL 支持的文件格式有 sequencefile, rcfile, orc, parquet, textfile, avro, 并且也可以指定 serde 的名称
idea实现SparkSQL连接hive
使用 SparkSQL 处理数据并保存进 Hive 表
前面都在使用 SparkShell 的方式来访问 Hive, 编写 SQL, 通过 Spark 独立应用的形式也可以做到同样的事, 但是需要一些前置的步骤, 如下
Step 1: 导入 Maven 依赖
<dependency>
<groupId>org.apache.spark</groupId>
<artifactId>spark-hive_2.11</artifactId>
<version>${spark.version}</version>
</dependency>
Step 2: 配置 SparkSession
如果希望使用 SparkSQL 访问 Hive 的话, 需要做两件事
1，开启 SparkSession 的 Hive 支持
经过这一步配置, SparkSQL 才会把 SQL 语句当作 HiveSQL 来进行解析
2，设置 WareHouse 的位置
虽然 hive-stie.xml 中已经配置了 WareHouse 的位置, 但是在 Spark 2.0.0 后已经废弃了 hive-site.xml 中设置的 hive.metastore.warehouse.dir, 需要在 SparkSession 中设置 WareHouse 的位置
设置 MetaStore 的位置
val spark = SparkSession
.builder()
.appName("hive example")
.config("spark.sql.warehouse.dir", "/user/hive/warehouse") //1
.config("hive.metastore.uris", "thrift://Bigdata01:9083") //2
.enableHiveSupport() //3
.getOrCreate()
1，设置 WareHouse 的位置
2，设置 MetaStore 的位置
3，开启 Hive 支持
配置好了以后, 就可以通过 DataFrame 处理数据, 后将数据结果推入 Hive 表中了, 在将结果保存到 Hive 表的时候, 可以指定保存模式
全套代码如下：
package com.spark.hive
import org.apache.spark.sql.{SaveMode, SparkSession}
import org.apache.spark.sql.types.{FloatType, IntegerType, StringType, StructField, StructType}
object HiveAccess {
def main(args: Array[String]): Unit = {
//1.创建SparkSession
// 1.开启hive支持
// 2.指定Metastore 的位置
// 3.指定Warehouse 的位置
val spark = SparkSession.builder().appName(this.getClass.getSimpleName)
.enableHiveSupport()//开启hive支持
.config("hive.metatore.uris","thrift://Bigdata01:9083")
.config("spark.sql.warehouse.dir","/user/hive/warehouse")
.getOrCreate()
//隐式转换
import spark.implicits._
//2.读取数据
/**
* 1.上传HDFS, 因为要在集群中执行，所以没办法保证程序在哪个机器上执行
* 所以,要把文件上传到所有机器中，才能读取本地文件,
* 上传到HDFS中就可以解决这个问题,所有的机器都可以读取HDFS中的文件
* 它是一个外部系统
* 2.使用DF读取文件
*/
val schema = StructType(
List(
StructField("name",StringType),
StructField("age",IntegerType),
StructField("gpa",FloatType)
)
)
val dataframe = spark.read
//分隔符
.option("delimiter","\t")
//添加字段 (源码)
.schema(schema)
.csv("hdfs:///input/studenttab10k")
val resultDF = dataframe.where('age > 50)
//3.写入数据
resultDF.write.mode(SaveMode.Overwrite)
.saveAsTable("spark_integrition1.student")
}
}
通过 mode 指定保存模式, 通过 saveAsTable 保存数据到 Hive
打包jar
放入spark 目录下 将 jar 重命名为spark-sql.jar
[aaa@qq.com spark]# mv original-spark-sql-1.0-SNAPSHOT.jar spark-sql.jar
提交集群运行 （出现如下结果，则运行成功）
[aaa@qq.com spark]# bin/spark-submit --master spark://Bigdata01:7077 \
> --class com.spark.hive.HiveAccess \
> ./spark-sql.jar
20/09/03 22:28:52 WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Using Spark's default log4j profile: org/apache/spark/log4j-defaults.properties
20/09/03 22:28:55 INFO SparkContext: Running Spark version 2.4.6
20/09/03 22:28:55 INFO SparkContext: Submitted application: HiveAccess$
20/09/03 22:28:55 INFO SecurityManager: Changing view acls to: root
20/09/03 22:28:55 INFO SecurityManager: Changing modify acls to: root
20/09/03 22:28:55 INFO SecurityManager: Changing view acls groups to:
20/09/03 22:28:55 INFO SecurityManager: Changing modify acls groups to:
20/09/03 22:28:55 INFO SecurityManager: SecurityManager: authentication disabled; ui acls disabled; users with view permissions: Set(root); groups with view permissions: Set(); users with modify permissions: Set(root); groups with modify permissions: Set()
20/09/03 22:28:57 INFO Utils: Successfully started service 'sparkDriver' on port 40023.
20/09/03 22:28:57 INFO SparkEnv: Registering MapOutputTracker
20/09/03 22:28:57 INFO SparkEnv: Registering BlockManagerMaster
20/09/03 22:28:57 INFO BlockManagerMasterEndpoint: Using org.apache.spark.storage.DefaultTopologyMapper for getting topology information
20/09/03 22:28:57 INFO BlockManagerMasterEndpoint: BlockManagerMasterEndpoint up
20/09/03 22:28:57 INFO DiskBlockManager: Created local directory at /tmp/blockmgr-02e6d729-f8d9-4a26-a95d-3a019331e164
20/09/03 22:28:57 INFO MemoryStore: MemoryStore started with capacity 366.3 MB
20/09/03 22:28:57 INFO SparkEnv: Registering OutputCommitCoordinator
20/09/03 22:28:58 WARN Utils: Service 'SparkUI' could not bind on port 4040. Attempting port 4041.
20/09/03 22:28:58 INFO Utils: Successfully started service 'SparkUI' on port 4041.
20/09/03 22:28:58 INFO SparkUI: Bound SparkUI to 0.0.0.0, and started at http://Bigdata01:4041
20/09/03 22:28:59 INFO SparkContext: Added JAR file:/opt/module/spark/./spark-sql.jar at spark://Bigdata01:40023/jars/spark-sql.jar with timestamp 1599143339071
20/09/03 22:28:59 INFO StandaloneAppClient$ClientEndpoint: Connecting to master spark://Bigdata01:7077...
20/09/03 22:29:00 INFO TransportClientFactory: Successfully created connection to Bigdata01/192.168.168.31:7077 after 331 ms (0 ms spent in bootstraps)
20/09/03 22:29:00 INFO StandaloneSchedulerBackend: Connected to Spark cluster with app ID app-20200903222900-0001
20/09/03 22:29:00 INFO StandaloneAppClient$ClientEndpoint: Executor added: app-20200903222900-0001/0 on worker-20200903203039-192.168.168.31-54515 (192.168.168.31:54515) with 8 core(s)
20/09/03 22:29:01 INFO StandaloneSchedulerBackend: Granted executor ID app-20200903222900-0001/0 on hostPort 192.168.168.31:54515 with 8 core(s), 1024.0 MB RAM
20/09/03 22:29:01 INFO StandaloneAppClient$ClientEndpoint: Executor added: app-20200903222900-0001/1 on worker-20200903203048-192.168.168.32-39304 (192.168.168.32:39304) with 6 core(s)
20/09/03 22:29:01 INFO StandaloneSchedulerBackend: Granted executor ID app-20200903222900-0001/1 on hostPort 192.168.168.32:39304 with 6 core(s), 1024.0 MB RAM
20/09/03 22:29:01 INFO StandaloneAppClient$ClientEndpoint: Executor added: app-20200903222900-0001/2 on worker-20200903203050-192.168.168.33-35682 (192.168.168.33:35682) with 6 core(s)
20/09/03 22:29:01 INFO StandaloneSchedulerBackend: Granted executor ID app-20200903222900-0001/2 on hostPort 192.168.168.33:35682 with 6 core(s), 1024.0 MB RAM
20/09/03 22:29:01 INFO Utils: Successfully started service 'org.apache.spark.network.netty.NettyBlockTransferService' on port 58667.
20/09/03 22:29:01 INFO NettyBlockTransferService: Server created on Bigdata01:58667
20/09/03 22:29:01 INFO BlockManager: Using org.apache.spark.storage.RandomBlockReplicationPolicy for block replication policy
20/09/03 22:29:01 INFO StandaloneAppClient$ClientEndpoint: Executor updated: app-20200903222900-0001/2 is now RUNNING
20/09/03 22:29:01 INFO StandaloneAppClient$ClientEndpoint: Executor updated: app-20200903222900-0001/0 is now RUNNING
20/09/03 22:29:01 INFO BlockManagerMaster: Registering BlockManager BlockManagerId(driver, Bigdata01, 58667, None)
20/09/03 22:29:01 INFO BlockManagerMasterEndpoint: Registering block manager Bigdata01:58667 with 366.3 MB RAM, BlockManagerId(driver, Bigdata01, 58667, None)
20/09/03 22:29:01 INFO BlockManagerMaster: Registered BlockManager BlockManagerId(driver, Bigdata01, 58667, None)
20/09/03 22:29:01 INFO BlockManager: Initialized BlockManager: BlockManagerId(driver, Bigdata01, 58667, None)
20/09/03 22:29:03 INFO StandaloneAppClient$ClientEndpoint: Executor updated: app-20200903222900-0001/1 is now RUNNING
20/09/03 22:29:12 INFO EventLoggingListener: Logging events to hdfs://Bigdata01:9000/spark_log/app-20200903222900-0001.lz4
20/09/03 22:29:13 INFO StandaloneSchedulerBackend: SchedulerBackend is ready for scheduling beginning after reached minRegisteredResourcesRatio: 0.0
20/09/03 22:29:14 INFO SharedState: loading hive config file: file:/opt/module/spark/conf/hive-site.xml
20/09/03 22:29:15 INFO SharedState: Setting hive.metastore.warehouse.dir ('/user/hive/warehouse') to the value of spark.sql.warehouse.dir ('/user/hive/warehouse').
20/09/03 22:29:15 INFO SharedState: Warehouse path is '/user/hive/warehouse'.
20/09/03 22:29:19 INFO CoarseGrainedSchedulerBackend$DriverEndpoint: Registered executor NettyRpcEndpointRef(spark-client://Executor) (192.168.168.31:39316) with ID 0
20/09/03 22:29:19 INFO StateStoreCoordinatorRef: Registered StateStoreCoordinator endpoint
20/09/03 22:29:24 INFO BlockManagerMasterEndpoint: Registering block manager 192.168.168.31:44502 with 366.3 MB RAM, BlockManagerId(0, 192.168.168.31, 44502, None)
20/09/03 22:29:25 INFO CoarseGrainedSchedulerBackend$DriverEndpoint: Registered executor NettyRpcEndpointRef(spark-client://Executor) (192.168.168.33:60974) with ID 2
20/09/03 22:29:25 INFO InMemoryFileIndex: It took 857 ms to list leaf files for 1 paths.
20/09/03 22:29:26 INFO BlockManagerMasterEndpoint: Registering block manager 192.168.168.33:55821 with 366.3 MB RAM, BlockManagerId(2, 192.168.168.33, 55821, None)
20/09/03 22:29:32 INFO CoarseGrainedSchedulerBackend$DriverEndpoint: Registered executor NettyRpcEndpointRef(spark-client://Executor) (192.168.168.32:35910) with ID 1
20/09/03 22:29:36 INFO HiveUtils: Initializing HiveMetastoreConnection version 1.2.1 using Spark classes.
20/09/03 22:29:38 INFO BlockManagerMasterEndpoint: Registering block manager 192.168.168.32:50317 with 366.3 MB RAM, BlockManagerId(1, 192.168.168.32, 50317, None)
20/09/03 22:29:39 WARN HiveConf: HiveConf of name hive.metastore.local does not exist
20/09/03 22:29:40 INFO metastore: Trying to connect to metastore with URI thrift://Bigdata01:9083
20/09/03 22:29:40 INFO metastore: Connected to metastore.
20/09/03 22:29:43 INFO SessionState: Created local directory: /tmp/c21738d9-28fe-4780-a950-10d38e9e32ca_resources
20/09/03 22:29:43 INFO SessionState: Created HDFS directory: /tmp/hive/root/c21738d9-28fe-4780-a950-10d38e9e32ca
20/09/03 22:29:43 INFO SessionState: Created local directory: /tmp/root/c21738d9-28fe-4780-a950-10d38e9e32ca
20/09/03 22:29:43 INFO SessionState: Created HDFS directory: /tmp/hive/root/c21738d9-28fe-4780-a950-10d38e9e32ca/_tmp_space.db
20/09/03 22:29:43 INFO HiveClientImpl: Warehouse location for Hive client (version 1.2.2) is /user/hive/warehouse
20/09/03 22:29:47 INFO FileSourceStrategy: Pruning directories with:
20/09/03 22:29:47 INFO FileSourceStrategy: Post-Scan Filters: isnotnull(age#1),(age#1 > 50)
20/09/03 22:29:47 INFO FileSourceStrategy: Output Data Schema: struct<name: string, age: int, gpa: float ... 1 more fields>
20/09/03 22:29:47 INFO FileSourceScanExec: Pushed Filters: IsNotNull(age),GreaterThan(age,50)
20/09/03 22:29:48 INFO ParquetFileFormat: Using default output committer for Parquet: org.apache.parquet.hadoop.ParquetOutputCommitter
20/09/03 22:29:48 INFO FileOutputCommitter: File Output Committer Algorithm version is 1
20/09/03 22:29:48 INFO SQLHadoopMapReduceCommitProtocol: Using user defined output committer class org.apache.parquet.hadoop.ParquetOutputCommitter
20/09/03 22:29:48 INFO FileOutputCommitter: File Output Committer Algorithm version is 1
20/09/03 22:29:48 INFO SQLHadoopMapReduceCommitProtocol: Using output committer class org.apache.parquet.hadoop.ParquetOutputCommitter
20/09/03 22:29:50 INFO CodeGenerator: Code generated in 1046.0442 ms
20/09/03 22:29:50 INFO MemoryStore: Block broadcast_0 stored as values in memory (estimated size 281.9 KB, free 366.0 MB)
20/09/03 22:29:51 INFO MemoryStore: Block broadcast_0_piece0 stored as bytes in memory (estimated size 24.2 KB, free 366.0 MB)
20/09/03 22:29:51 INFO BlockManagerInfo: Added broadcast_0_piece0 in memory on Bigdata01:58667 (size: 24.2 KB, free: 366.3 MB)
20/09/03 22:29:51 INFO SparkContext: Created broadcast 0 from saveAsTable at HiveAccess.scala:54
20/09/03 22:29:53 INFO FileSourceScanExec: Planning scan with bin packing, max size: 4194304 bytes, open cost is considered as scanning 4194304 bytes.
20/09/03 22:29:54 INFO SparkContext: Starting job: saveAsTable at HiveAccess.scala:54
20/09/03 22:29:54 INFO DAGScheduler: Got job 0 (saveAsTable at HiveAccess.scala:54) with 1 output partitions
20/09/03 22:29:54 INFO DAGScheduler: Final stage: ResultStage 0 (saveAsTable at HiveAccess.scala:54)
20/09/03 22:29:54 INFO DAGScheduler: Parents of final stage: List()
20/09/03 22:29:54 INFO DAGScheduler: Missing parents: List()
20/09/03 22:29:54 INFO DAGScheduler: Submitting ResultStage 0 (MapPartitionsRDD[1] at saveAsTable at HiveAccess.scala:54), which has no missing parents
20/09/03 22:29:55 INFO MemoryStore: Block broadcast_1 stored as values in memory (estimated size 153.1 KB, free 365.9 MB)
20/09/03 22:29:55 INFO MemoryStore: Block broadcast_1_piece0 stored as bytes in memory (estimated size 55.6 KB, free 365.8 MB)
20/09/03 22:29:55 INFO BlockManagerInfo: Added broadcast_1_piece0 in memory on Bigdata01:58667 (size: 55.6 KB, free: 366.2 MB)
20/09/03 22:29:55 INFO SparkContext: Created broadcast 1 from broadcast at DAGScheduler.scala:1163
20/09/03 22:29:55 INFO DAGScheduler: Submitting 1 missing tasks from ResultStage 0 (MapPartitionsRDD[1] at saveAsTable at HiveAccess.scala:54) (first 15 tasks are for partitions Vector(0))
20/09/03 22:29:55 INFO TaskSchedulerImpl: Adding task set 0.0 with 1 tasks
20/09/03 22:29:55 INFO TaskSetManager: Starting task 0.0 in stage 0.0 (TID 0, 192.168.168.33, executor 2, partition 0, ANY, 8261 bytes)
20/09/03 22:29:57 INFO BlockManagerInfo: Added broadcast_1_piece0 in memory on 192.168.168.33:55821 (size: 55.6 KB, free: 366.2 MB)
20/09/03 22:30:24 INFO BlockManagerInfo: Added broadcast_0_piece0 in memory on 192.168.168.33:55821 (size: 24.2 KB, free: 366.2 MB)
20/09/03 22:30:28 INFO TaskSetManager: Finished task 0.0 in stage 0.0 (TID 0) in 32924 ms on 192.168.168.33 (executor 2) (1/1)
20/09/03 22:30:28 INFO TaskSchedulerImpl: Removed TaskSet 0.0, whose tasks have all completed, from pool
20/09/03 22:30:28 INFO DAGScheduler: ResultStage 0 (saveAsTable at HiveAccess.scala:54) finished in 34.088 s
20/09/03 22:30:28 INFO DAGScheduler: Job 0 finished: saveAsTable at HiveAccess.scala:54, took 34.592171 s
20/09/03 22:30:29 INFO FileFormatWriter: Write Job 3b048e0c-6b5e-43ea-aad2-b1e64f4d9657 committed.
20/09/03 22:30:29 INFO FileFormatWriter: Finished processing stats for write job 3b048e0c-6b5e-43ea-aad2-b1e64f4d9657.
20/09/03 22:30:30 INFO InMemoryFileIndex: It took 26 ms to list leaf files for 1 paths.
20/09/03 22:30:30 INFO HiveExternalCatalog: Persisting file based data source table `spark_integrition1`.`student` into Hive metastore in Hive compatible format.
20/09/03 22:30:32 INFO SparkContext: Invoking stop() from shutdown hook
20/09/03 22:30:32 INFO SparkUI: Stopped Spark web UI at http://Bigdata01:4041
20/09/03 22:30:32 INFO StandaloneSchedulerBackend: Shutting down all executors
20/09/03 22:30:32 INFO CoarseGrainedSchedulerBackend$DriverEndpoint: Asking each executor to shut down
20/09/03 22:30:32 INFO MapOutputTrackerMasterEndpoint: MapOutputTrackerMasterEndpoint stopped!
20/09/03 22:30:32 INFO MemoryStore: MemoryStore cleared
20/09/03 22:30:32 INFO BlockManager: BlockManager stopped
20/09/03 22:30:32 INFO BlockManagerMaster: BlockManagerMaster stopped
20/09/03 22:30:32 INFO OutputCommitCoordinator$OutputCommitCoordinatorEndpoint: OutputCommitCoordinator stopped!
20/09/03 22:30:32 INFO SparkContext: Successfully stopped SparkContext
20/09/03 22:30:32 INFO ShutdownHookManager: Shutdown hook called
20/09/03 22:30:32 INFO ShutdownHookManager: Deleting directory /tmp/spark-5d113d24-2e67-4d1c-a6aa-e75de128da16
20/09/03 22:30:32 INFO ShutdownHookManager: Deleting directory /tmp/spark-f4a4aed1-1746-4e87-9f62-bdaaf6eff438
进入hive 目录查询
hive (spark_integrition1)> select * from student limit 10;
OK
student.name student.age student.gpa
ulysses thompson 64 1.9
luke king 65 0.73
holly davidson 57 2.43
fred miller 55 3.77
luke steinbeck 51 1.14
holly davidson 59 1.26
calvin brown 56 0.72
rachel robinson 62 2.25
tom johnson 72 0.99
irene garcia 54 1.06
Time taken: 0.245 seconds, Fetched: 10 row(s)
end
本次分享就到这里了
上一篇： STM32 移植 STemwin