SparkSQL之关联mysql和hive查询

青旅半醒 2023-07-19 08:19 13阅读 0赞

# 创建Mysql数据库 #

CREATE DATABASE spark;
    USE spark;
    
    CREATE TABLE DEPT(
    DEPTNO int(2) PRIMARY KEY,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
    );
    
    INSERT INTO DEPT VALUE(10, 'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUE(20, 'RESEARCH','DALAS');
    INSERT INTO DEPT VALUE(30, 'SALES','CHICAGO');
    INSERT INTO DEPT VALUE(40, 'OPERATIONS','BOSTON');

# 创建Hive数据库 #

CREATE TABLE emp(
    empno int,
    ename string,
    job string,
    mgr int,
    hiredate string,
    sal double,
    comm double,
    deptno int
    ) ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t';

准备文本数据`emp.txt`：

1       tom     clerk   9088    1980-12-09      800.0   NULL    20
    2       vincent cl      9999    1992-03-04      1000.0  300.1   30
    5       sofia   salesman        8000    1996-02-22      100.0   908     20

将数据导入到hive中：  
`hive> load data local inpath '/home/iie4bu/data/emp.txt' overwrite into table emp;`

# JOIN操作 #

package cn.ac.iie.spark
    
    import org.apache.spark.sql.SparkSession
    
    /** * 使用外部数据源综合查询Hive和Mysql的表数据 */
    object HiveMysqlApp { 
      def main(args: Array[String]): Unit = { 
        val spark = SparkSession.builder().appName("HiveMysqlApp").master("local[2]").getOrCreate()
        // 加载hive表数据
        val hiveDF = spark.table("emp")
        // 加载mysql数据
        val mysqlDF = spark.read
          .format("jdbc")
          .option("url", "jdbc:mysql://swarm-manager:3306")
          .option("driver", "com.mysql.jdbc.Driver")
          .option("dbtable", "spark.DEPT")
          .option("user", "root")
          .option("password", "123456")
          .load()
        // JOIN
        val resultDF = hiveDF.join(mysqlDF,hiveDF.col("deptno") === mysqlDF.col("DEPTNO"))     resultDF.select(hiveDF.col("empno"),hiveDF.col("ename"),mysqlDF.col("deptno"),mysqlDF.col("dname")).show()
        resultDF.show()
        spark.close()
      }
    }

代码需要导入mysql驱动jars ，所以在spark-shell中执行：  
![在这里插入图片描述][20200330124932525.png]

[20200330124932525.png]: /images/20230528/7d28468dcf62442a88e4fede340a0a6f.png