Spark入门（五）Spark SQL shell启动方式(元数据存储在derby)

比眉伴天荒 2023-07-03 02:38 21阅读 0赞

### 一、spark-sql shell介绍 ###

Spark sql是以hive SQL提交spark任务到spark集群执行。

由于spark是计算框架没有存储功能，所有spark sql数据表映射关系存储在运行shell的当前目录下metastore\_db目录里面（spark默认使用derby数据库创建的本地存储，使用其他非本地数据库没有此目录），切换不同的目录启动spark-sql shell会创建不同位置的metastore\_db目录存储关系数据。而且metastore\_db目录在spark-sql shell里面访问权限是独占的，同一个目录只能启动一个spark-sql shell进程，多启动会报错。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70][]

### 二、启动spark-sql shell ###

\--driver-class-path是指定driver程序启动数据库连接驱动类库

\--jars是给worker执行调用的类库，需要指定数据库连接驱动类库

\--total-executor-cores  启动的核数，默认是所有核数

\--executor-memory  每个work分配的内存，默认是work的所有内存

数据库连接驱动类库放在spark-sql机器的指定目录。也可以添加到spark集群每台机器的spark安装目录的jars目录下，这样就不用指定--driver-class-path --jars

cd ~/software/spark-2.4.4-bin-hadoop2.6
    bin/spark-sql --master spark://hadoop01:7077,hadoop02:7077,hadoop03:7077 --driver-class-path /home/mk/mysql-connector-java-5.1.26-bin.jar  --jars /home/mk/mysql-connector-java-5.1.26-bin.jar  --total-executor-cores 2  --executor-memory 1g

执行结果：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1][]

![202001311353014.png][]

### 三、执行sql ###

**（1）创建表**

create table test(id int, name string)  
    USING org.apache.spark.sql.jdbc 
    options(url 'jdbc:mysql://192.168.150.1:3306/spark-mysql?user=root&password=admin', dbtable 'test_a');
    
    show tables;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 3][]

**（2）插入数据**

insert into test values(1, 'a'), (2, 'b'), (3, 'c');
    select * from test;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 4][]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 5][]

**（3） 删除元数据表**

drop table test;

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 6][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200131150425500.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200131135200505.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[202001311353014.png]: https://img-blog.csdnimg.cn/202001311353014.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 2]: https://img-blog.csdnimg.cn/20200131135455946.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 3]: https://img-blog.csdnimg.cn/202001311355500.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 4]: https://img-blog.csdnimg.cn/20200131142127844.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 5]: https://img-blog.csdnimg.cn/20200131142216963.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 6]: https://img-blog.csdnimg.cn/20200131143835100.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70