mysql和hive实现关联查询

最近有个需求就是要求实现传统数据库和大数据数据库的关联查询。

刚开始的时候一头雾水，感觉要实现这个需求很难，但是查了相关资料，渐渐了些思绪。

起初我想到的是想实现mysql和hive实现关联查询，hive和hbase是可以整合的，如果实现了这个也就实现了mysql和hbase的关联查询，开始的时候有了一个思路，就是利用sqoop将mysql的数据表导入到hive表中，或者将hive表的数据导入到mysql中，放到一个库中再做关联查询，这个思路是肯定能实现的，但是总觉得这样做很low，总感觉用这种方法实现有种投机取巧的感觉，于是我又查相关资料，有了另一种思路。

那就是，利用spark rdd，将mysql中的数据导入到一个spark的rdd中，将hive中的数据导入到spark的一个rdd中，两个rdd之间实现关联查询，具体实现过程如下：

 1.在mysql准备数据

         create database mysql_db default character set utf8;

        剩下的可直接运行以下脚本：

mysql -uhive -phive -e "show databases;use mysql_db ;

drop table if exists student;

CREATE TABLE student(sid int,sname VARCHAR(10),sage DATETIME,ssex CHAR(4));

INSERT INTO student VALUES(2 , '钱电' , '1990-12-21' , '男');

INSERT INTO student VALUES(3 , '孙风' , '1990-05-20' , '男');

INSERT INTO student VALUES(4 , '李云' , '1990-08-06' , '男');

INSERT INTO student VALUES(5 , '周梅' , '1991-12-01' , '女');

INSERT INTO student VALUES(6 , '吴兰' , '1992-03-01' , '女');

INSERT INTO student VALUES(7 , '郑竹' , '1989-07-01' , '女');

INSERT INTO student VALUES(8 , '王菊' , '1990-01-20' , '女');

select * from student;"

 

     2.在hive上创建测试数据

beeline -u "jdbc:hive2://localhost:10000" -n hive -e  "show

databases;create database test;

use test;create table dept(id int,position string);

insert into dept select decode(binary('1'),'utf-8'),decode(binary('ceo'),'utf-8');

insert into dept select decode(binary('1'),'utf-8'),decode(binary('保安'),'utf-8');

insert into dept select decode(binary('3'),'utf-8'),decode(binary('保洁'),'utf-8');

insert into dept select decode(binary('4'),'utf-8'),decode(binary('会计'),'utf-8');

insert into dept select decode(binary('5'),'utf-8'),decode(binary('员工'),'utf-8');

insert into dept select decode(binary('6'),'utf-8'),decode(binary('员工'),'utf-8');

insert into dept select decode(binary('7'),'utf-8'),decode(binary('前台'),'utf-8');

insert into dept select decode(binary('8'),'utf-8'),decode(binary('律师'),'utf-8'); "

 

     3.在spark rdd中导入mysql数据

      切换用户到hive，否则没权限

         a.spark-shell --master=local[4] --driver-class-path=/usr/lib/spark/lib/mysql-connector-java-5.1.35-bin.jar

b.执行以下代码

import org.apache.spark.sql.SQLContext

  val sqlContext = new SQLContext(sc)

  import sqlContext.implicits._

        val prop = new java.util.Properties

        prop.put( " user " , " hive " )

        prop.put( " password " , " hive " )

         val mysql_db_rdd=sqlContext.read.jdbc( " jdbc:mysql :// 172.17.201.134 :3306/ mysql_db " , " student " ,prop)

         mysql_db_rdd.show

 

     4.在spark rdd中导入hive数据

val hive_db_rdd=sqlContext.sql("select * from test.dept")         hive_db_rdd.foreach(t=>println("id"+t(0)+":"+t(1)))

        

      5.实现关联操作（join操作）

     mysql_db_rdd.join( hive_db_rdd , $"sid" === $"id", "left").show

    

  mysql_db_rdd.join( hive_db_rdd , $"sid" === $"id", "right").show

 

  mysql_db_rdd.join( hive_db_rdd ).collect

越来越spark感兴趣了，确实比hive效率快多了