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

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

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

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

 

关于Hive build into Spark

1.

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

 

3.

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的能力