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