正文

Spark2.x学习笔记：Spark SQL的SQL

IP云
IP云V管理员/0 阅读
09/02
小程序：扫一扫查出行
【扫一扫了解最新限行尾号】
复制小程序

Spark SQL所支持的SQL语法

select [distinct] [column names]|[wildcard]
from tableName
[join clause tableName on join condition]
[where condition]
[group by column name]
[having conditions]
[order by column names [asc|desc]]

如果只用join进行查询，则支持的语法为：

select statement
from statement
[join | inner join | left join | left semi join | left outer join | right join |right outer join | full join | full outer join]
on join condition

Spark SQL的SQL的框架

与Hive Metastore结合

（1）Spark要能找到HDFS和Hive的配置文件

  • 第1种方法：可以直接将core-site.xml、hdfs-site.xml和hive-site.xml复制到Spark安装目录下的conf目录中。该方法存在一个缺陷，如果HDFS或Hive的配置修改了，则需要手动修改Spark对应的配置文件。
  • 第2种方法：在Spark配置文件中指定Hadoop配置文件目录

（2）Spark SQL与Hive Metastore结合，直接使用spark.sql(“select … from table where …”)

15.4 实例演示

（1）spark-shell

[root@node1 ~]# spark-shell
// :: WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
Spark context Web UI available at http://192.168.80.131:4040
Spark context available as 'sc' (master = local[*], app id = local-).
Spark session available as 'spark'.
Welcome to
____ __
/ __/__ ___ _____/ /__
_\ \/ _ \/ _ `/ __/ '_/
/___/ .__/\_,_/_/ /_/\_\ version 2.2.
/_/Using Scala version 2.11. (Java HotSpot(TM) -Bit Server VM, Java 1.8.0_112)
Type in expressions to have them evaluated.
Type :help for more information.scala> spark.sql("show databases").show
+------------+
|databaseName|
+------------+
| default|
| test|
+------------+scala> spark.sql("show tables").show
+--------+---------+-----------+
|database|tableName|isTemporary|
+--------+---------+-----------+
| default| copyemp| false|
| default| demo| false|
| default| dept| false|
| default| dual| false|
| default| emp| false|
| default| empbak| false|
| default|employees| false|
| default| mytb| false|
| default| users| false|
+--------+---------+-----------+scala> spark.sql("select * from emp").show
+----+------+---------+----+----------+------+------+----+
| eid| ename| job| mgr| hiredate| sal| comm| did|
+----+------+---------+----+----------+------+------+----+
|| CLARK| MANAGER||--|2450.0| 0.0| |
|| KING|PRESIDENT| |--|5000.0| 0.0| |
||MILLER| CLERK||--|1300.0| 0.0| |
|| SMITH| CLERK||--| 800.0| 0.0| |
|| JONES| MANAGER||--|2975.0| 0.0| |
|| FORD| ANALYST||--|3000.0| 0.0| |
|| ALLEN| SALESMAN||--|1600.0| 300.0| |
|| WARD| SALESMAN||--|1250.0| 500.0| |
||MARTIN| SALESMAN||--|1250.0|1400.0| |
|| BLAKE| MANAGER||--|2850.0| 0.0| |
||TURNER| SALESMAN||--|1500.0| 0.0| |
|| JAMES| CLERK||--| 950.0| 0.0| |
||HADRON| null|null|--|6666.0| null|null|
+----+------+---------+----+----------+------+------+----+

（2）spark-sql

[root@node1 ~]# spark-sql
// :: WARN NativeCodeLoader: Unable to load native-hadoop library for your platform... using builtin-java classes where applicable
// :: WARN ObjectStore: Version information not found in metastore. hive.metastore.schema.verification is not enabled so recording the schema version 1.2.
// :: WARN ObjectStore: Failed to get database default, returning NoSuchObjectException
spark-sql> show databases;
default
test
Time taken: 3.93 seconds, Fetched row(s)
spark-sql> show tables;
default copyemp false
default demo false
default dept false
default dual false
default emp false
default empbak false
default employees false
default mytb false
default users false
Time taken: 0.145 seconds, Fetched row(s)
spark-sql> select * from emp;
CLARK MANAGER -- 2450.0 0.0
KING PRESIDENT -- 5000.0 0.0
MILLER CLERK -- 1300.0 0.0
SMITH CLERK -- 800.0 0.0
JONES MANAGER -- 2975.0 0.0
FORD ANALYST -- 3000.0 0.0
ALLEN SALESMAN -- 1600.0 300.0
WARD SALESMAN -- 1250.0 500.0
MARTIN SALESMAN -- 1250.0 1400.0
BLAKE MANAGER -- 2850.0 0.0
TURNER SALESMAN -- 1500.0 0.0
JAMES CLERK -- 950.0 0.0
HADRON NULL NULL -- 6666.0 NULL NULL
Time taken: 3.266 seconds, Fetched row(s)
spark-sql>