SparkSQL（13）：shell命令中spark-shell、spark-sql以及thriftserver&beeline访问hive表

一、spark-shell

1.把hive的hive-site.xml复制到spark的conf下面

2.开启spark-shell

bin/spark-shell --master local[2] --jars /opt/datas/mysql-connector-java-5.1.27-bin.jar

3.运行测试语句

spark.sql("select * from imooc.emp").show
spark.sql("show tables from imooc").show
spark.sql("select * from imooc.emp e join imooc.dept d on e.deptno=d.deptno").show

 

二、spark-sql

1.把hive的hive-site.xml复制到spark的conf下面

2.启动

bin/spark-sql --master local[2] --jars /opt/datas/mysql-connector-java-5.1.27-bin.jar

3.直接运行sql语句

select * from imooc.emp;
show tables from imooc;
select * from imooc.emp e join imooc.dept d on e.deptno=d.deptno;、

三、thriftserver&beeline

1.把hive的hive-site.xml复制到spark的conf下面

2.启动thriftserver

sbin/start-thriftserver.sh  \
--master local[2] \
--jars /opt/datas/mysql-connector-java-5.1.27-bin.jar  \
--hiveconf hive.server2.thrift.port=14000 

服务器：

sbin/start-thriftserver.sh \
--master local[2] \
--jars /opt/datas/mysql-connector-java-5.1.27-bin.jar \
--hiveconf hive.server2.thrift.port=14000

3.启动beeline，连接thriftserver

bin/beeline -u jdbc:hive2://bigdata.ibeifeng.com:14000 -n bigdata.ibeifeng.com

服务器：

bin/beeline -u jdbc:hive2://hadoop:14000 -n hadoop

结果：
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://hadoop:14000> 

其中

“bigdata.ibeifeng.com:14000”是thriftserver设定端口

“bigdata.ibeifeng.com:14000”是服务器hostname

4.测试

0: jdbc:hive2://hadoop:14000> show tables;
+-----------+-----------------+--------------+--+
| database  |    tableName    | isTemporary  |
+-----------+-----------------+--------------+--+
| default   | dept            | false        |
| default   | emp             | false        |
| default   | hbase2hive      | false        |
| default   | hive2hbase_emp  | false        |
| default   | t               | false        |
+-----------+-----------------+--------------+--+
0: jdbc:hive2://hadoop:14000> select * from emp e join dept d on e.deptno=d.deptno;
+--------+---------+------------+-------+-------------+---------+---------+---------+---------+-------------+-----------+--+
| empno  |  ename  |    job     |  mgr  |  hiredate   |   sal   |  comm   | deptno  | deptno  |    dname    |    loc    |
+--------+---------+------------+-------+-------------+---------+---------+---------+---------+-------------+-----------+--+
| 7369   | SMITH   | CLERK      | 7902  | 1980-12-17  | 800.0   | NULL    | 20      | 20      | RESEARCH    | DALLAS    |
| 7499   | ALLEN   | SALESMAN   | 7698  | 1981-2-20   | 1600.0  | 300.0   | 30      | 30      | SALES       | CHICAGO   |
| 7521   | WARD    | SALESMAN   | 7698  | 1981-2-22   | 1250.0  | 500.0   | 30      | 30      | SALES       | CHICAGO   |
| 7566   | JONES   | MANAGER    | 7839  | 1981-4-2    | 2975.0  | NULL    | 20      | 20      | RESEARCH    | DALLAS    |
| 7654   | MARTIN  | SALESMAN   | 7698  | 1981-9-28   | 1250.0  | 1400.0  | 30      | 30      | SALES       | CHICAGO   |
| 7698   | BLAKE   | MANAGER    | 7839  | 1981-5-1    | 2850.0  | NULL    | 30      | 30      | SALES       | CHICAGO   |
| 7782   | CLARK   | MANAGER    | 7839  | 1981-6-9    | 2450.0  | NULL    | 10      | 10      | ACCOUNTING  | NEW YORK  |
| 7788   | SCOTT   | ANALYST    | 7566  | 1987-4-19   | 3000.0  | NULL    | 20      | 20      | RESEARCH    | DALLAS    |
| 7839   | KING    | PRESIDENT  | NULL  | 1981-11-17  | 5000.0  | NULL    | 10      | 10      | ACCOUNTING  | NEW YORK  |
| 7844   | TURNER  | SALESMAN   | 7698  | 1981-9-8    | 1500.0  | 0.0     | 30      | 30      | SALES       | CHICAGO   |
| 7876   | ADAMS   | CLERK      | 7788  | 1987-5-23   | 1100.0  | NULL    | 20      | 20      | RESEARCH    | DALLAS    |
| 7900   | JAMES   | CLERK      | 7698  | 1981-12-3   | 950.0   | NULL    | 30      | 30      | SALES       | CHICAGO   |
| 7902   | FORD    | ANALYST    | 7566  | 1981-12-3   | 3000.0  | NULL    | 20      | 20      | RESEARCH    | DALLAS    |
| 7934   | MILLER  | CLERK      | 7782  | 1982-1-23   | 1300.0  | NULL    | 10      | 10      | ACCOUNTING  | NEW YORK  |
+--------+---------+------------+-------+-------------+---------+---------+---------+---------+-------------+-----------+--+

结果：