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

た入场券 2023-07-03 02:41 12阅读 0赞

### 一、hive配置文件  ###

在spak/conf目录添加hive-site.xml配置，设置mysql作为元数据存储的数据库

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
    <?xml-stylesheet type="text/xsl" href="configuration.xsl"?>
    <configuration>
        <property>
        <name>javax.jdo.option.ConnectionURL</name>
        <value>jdbc:mysql://192.168.150.1:3306/spark_metadata_db?createDatabaseIfNotExist=true&characterEncoding=UTF-8</value>
        <description>JDBC connect string for a JDBC metastore</description>
      </property>
      <property>
        <name>javax.jdo.option.ConnectionDriverName</name>
        <value>com.mysql.jdbc.Driver</value>
        <description>Driver class name for a JDBC metastore</description>
      </property>
      <property>
        <name>javax.jdo.option.ConnectionUserName</name>
        <value>root</value>
        <description>Username to use against metastore database</description>
      </property>
      <property>
        <name>javax.jdo.option.ConnectionPassword</name>
        <value>admin</value>
        <description>password to use against metastore database</description>
      </property>
      
      <property>
        <name>hive.cli.print.header</name>
        <value>true</value>
      </property>
      
      <property>
        <name>hive.cli.print.current.db</name>
        <value>true</value>
      </property>
    </configuration>

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

\--driver-class-path 是spark元数据存储的驱动类路径，这里使用mysql作为metastore，故使用mysql-connector-java-5.1.26-bin.jar

\--jars 是executer执行器的额外添加类的路径，这里使用mysql的test表进行操作，故使用mysql-connector-java-5.1.26-bin.jar

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

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

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][]

启动shell前：

![2020013115561144.png][]

启动shell后：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1][]

mysql数据库里面创建了spark\_metadata\_db

### 三、执行sql ###

show tables;
    
    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;
    
    select * from test;
    
    insert into test values(4, 'd'), (5, 'e'), (6, 'f');
    
    select * from test;

![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][]

![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][]

![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 7][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70]: https://img-blog.csdnimg.cn/20200131160454109.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70
[2020013115561144.png]: https://img-blog.csdnimg.cn/2020013115561144.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA_size_16_color_FFFFFF_t_70 1]: https://img-blog.csdnimg.cn/20200131160137373.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 2]: https://img-blog.csdnimg.cn/20200131160911523.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/20200131161006619.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/2020013116113992.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/20200131161257436.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/20200131161426498.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 7]: https://img-blog.csdnimg.cn/2020013116152584.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9tYW9rdW4uYmxvZy5jc2RuLm5ldA==,size_16,color_FFFFFF,t_70