SparkSQL之关联mysql和hive查询

创建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中执行：