如何使用Spark SQL 的JDBC server

最新推荐文章于 2022-12-11 22:31:55 发布

转载最新推荐文章于 2022-12-11 22:31:55 发布 · 232 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：https://my.oschina.net/cloudcoder/blog/467713

文章标签：

#大数据 #shell #python

本文详细介绍了如何在CDH5.3.0环境下配置并使用Spark SQL的JDBC服务器，包括启动JDBC服务器、连接及操作过程，并通过Beeline进行数据表创建、加载、查询等基本操作。

2019独角兽企业重金招聘Python工程师标准>>>

简介

Spark SQL provides JDBC connectivity, which is useful for connecting business intelligence (BI) tools to a Spark cluster and for sharing a cluster across multipleusers. The JDBC server runs as a standalone Spark driver program that can be shared by multiple clients. Any client can cache tables in memory, query them, and so on and the cluster resources and cached data will be shared among all of them.

Spark SQL’s JDBC server corresponds to the HiveServer2 in Hive. It is also known as the “Thrift server” since it uses the Thrift communication protocol. Note that the JDBC server requires Spark be built with Hive support

运行环境

集群环境：CDH5.3.0

具体JAR版本如下：

spark版本：1.2.0-cdh5.3.0

hive版本：0.13.1-cdh5.3.0

hadoop版本：2.5.0-cdh5.3.0

启动 JDBC server

cd /etc/spark/conf
ln -s /etc/hive/conf/hive-site.xml hive-site.xml
cd /opt/cloudera/parcels/CDH/lib/spark/
chmod- -R 777 logs/
cd /opt/cloudera/parcels/CDH/lib/spark/sbin
./start-thriftserver.sh  --master yarn --hiveconf hive.server2.thrift.port=10008

Connecting to the JDBC server with Beeline

cd /opt/cloudera/parcels/CDH/lib/spark/bin
beeline -u jdbc:hive2://hadoop04:10000

[root@hadoop04 bin]# beeline -u jdbc:hive2://hadoop04:10000
scan complete in 2ms
Connecting to jdbc:hive2://hadoop04:10000
Connected to: Spark SQL (version 1.2.0)
Driver: Hive JDBC (version 0.13.1-cdh5.3.0)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 0.13.1-cdh5.3.0 by Apache Hive
0: jdbc:hive2://hadoop04:10000>

Working with Beeline

Within the Beeline client, you can use standard HiveQL commands to create, list, and query tables. You can find the full details of HiveQL in the Hive Language Manual,but here, we show a few common operations.

CREATE TABLE IF NOT EXISTS mytable (key INT, value STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ',';

create table mytable(name string,addr string,status string) row format delimited fields terminated by '#'

#加载本地文件
load data local inpath '/external/tmp/data.txt' into table mytable

#加载hdfs文件
load data inpath 'hdfs://ju51nn/external/tmp/data.txt' into table mytable;

describe mytable;

explain select * from mytable where name = '张三'

select * from mytable where name = '张三'   

cache table mytable

 select count(*) total,count(distinct addr) num1,count(distinct status) num2 from mytable where addr='gz';
 
 uncache table mytable

使用数据示例

张三#广州#学生
李四#贵州#教师
王五#武汉#讲师
赵六#成都#学生
lisa#广州#学生
lily#gz#studene

Standalone Spark SQL Shell

Spark SQL also supports a simple shell you can use as a single process: spark-sql

它主要用于本地的开发环境，在共享集群环境中，请使用JDBC SERVER

cd /opt/cloudera/parcels/CDH/lib/spark/bin
./spark-sql

转载于:https://my.oschina.net/cloudcoder/blog/467713