【Spark九十四】spark-sql工具的使用

Spark SQL与Hive整合使用教程

最新推荐文章于 2023-07-03 10:19:04 发布

原创最新推荐文章于 2023-07-03 10:19:04 发布 · 634 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#大数据 #数据库

Spark 专栏收录该内容

109 篇文章

订阅专栏

本文详细介绍了如何在Spark环境下使用Spark SQL与Hive进行整合，包括配置步骤、数据库展示、表显示、查询操作及创建加载数据表等核心功能，展示了Spark SQL等同于Hive操作的能力。

spark-sql是Spark bin目录下的一个可执行脚本，它的目的是通过这个脚本执行Hive的命令，即原来通过

hive>输入的指令可以通过spark-sql>输入的指令来完成。

spark-sql可以使用内置的Hive metadata-store，也可以使用已经独立安装的Hive的metadata store

关于Hive build into Spark

Spark SQL can be built with or without Apache Hive, the Hadoop SQL engine. Spark
SQL with Hive support allows us to access Hive tables, UDFs (user-defined functions),
SerDes (serialization and deserialization formats), and the Hive query language
(HiveQL). Hive query language (HQL) It is important to note that including
the Hive libraries does not require an existing Hive installation.

2.关于Hive和Spark SQL的关系，参见：http://bit1129.iteye.com/blog/2192739

Note that if you don’t have an existing Hive installation, Spark SQL will create its
own Hive metastore (metadata DB) in your program’s work directory, called meta
store_db. 
In addition, if you attempt to create tables using HiveQL’s CREATE TABLE
statement (not CREATE EXTERNAL TABLE), they will be placed in the /user/hive/warehouse
directory on your default filesystem (either your local filesystem, or HDFS if
you have a hdfs-site.xml on your classpath).

配置步骤：

1. 将Hive的conf目录的hive-site.xml拷贝到Spark的conf目录

2. 将hive-site.xml中关于时间的配置的时间单位，比如ms，s全部删除掉

3. 将mysql jdbc的驱动添加到Spark的Classpath上

export SPARK_CLASSPATH=$SPARK_CLASSPATH:/home/hadoop/software/spark-1.2.0-bin-hadoop2.4/lib/mysql-connector-java-5.1.34.jar

4.启动Spark SQL

[hadoop@hadoop bin]$ ./spark-sql
Spark assembly has been built with Hive, including Datanucleus jars on classpath
SET spark.sql.hive.version=0.13.1

5. 显示所有的数据库

spark-sql> show databases;
OK
default
financials
financials2
sales
Time taken: 18.67 seconds

6. 显示表

spark-sql> use default;
OK
Time taken: 0.657 seconds
spark-sql> show tables;
OK
abc
avro_table
employees
invites
my_word
mytable1
parquet_table
table1
word
word3
word4
word5
Time taken: 1.011 seconds

7. 查询

spark-sql> select * from word
         > ;
1	MSN  
10	QQ  
100	Gtalk  
1000	Skype 
NULL	NULL
Time taken: 39.852 seconds

8.创建表并加载数据

spark-sql> create table word6 (id int,word string) row format delimited fields terminated by ',' stored as textfile ; 
OK
Time taken: 10.852 seconds

spark-sql> load data local inpath '/home/hadoop/word.txt' into table word6;
Copying data from file:/home/hadoop/word.txt
Copying file: file:/home/hadoop/word.txt
Loading data to table default.word6
Table default.word6 stats: [numFiles=1, numRows=0, totalSize=31, rawDataSize=0]
OK
Time taken: 2.307 seconds

通过如上操作可以看到，实际上spark-sql操作等同于Hive操作，也就是spark-sql是提供了等价于Hive的能力