ETL SparkSQL实现

SparkETL

SparkETL主要用SQL方式实现数据仓库ETL，并保持spark的原生多功能、灵活性。采用java对spark功能进行简单封装，对于数据源、目标都是关系型数据库的，从数据抽取、转换、加载完全采用SQL方式，对于SQL不满足的场景，再用spark相关功能实现。
SparkETL是ETL的一个参考实现，实际使用时，需要根据业务需要及模型设计在此基础上增加、修改。

实现背景

Spark基于rdd实现了很多功能，针对批处理，就提供了Spark Core、DataFrame(Dataset)、SQL，开发语言更是支持Scala、Java、Python、R，而且各功能间基本都支持互相转换。但是，绝大多数公司80%以上的数据都是结构化数据，SQL以其简单、友好、高效，尤其适合做ETL处理。Spark支持关系型数据库、文本文件（text、csv、json、xml等）、二进制文件（Parquet、ORC、SequenceFile等）、大量的非关系型数据库也提供了Spark接口（MongoDB、Elasticsearch、Redis等），而且一旦加载到Spark中，其后的处理方式就是完全一样的。也就是说Spark支持多源获取数据、多源连接、可以将结果同步到不同的目标。Spark对不同的源都有不同的连接方式，需要将连接方式封装了，然后才能用SQL处理。

实现方式

本程序用Java调用Spark，对源和目标简单封装，然后在SQL文件中写SQL语句，可以实现绝大部分ETL。对于源和目标不好封装，或者数据是非结构化的，可以用DataFrame、Core处理后，再用SQL实现其余的ETL。
Java程序读取SQL文件中的SQL，然后替换其中的参数，最后调用spark接口执行。
不打算做成一个复杂的程序，因此舍弃了些灵活性，采用了一定的约定，约定方式在示例中有说明。程序的调度是较粗粒度的，最细是一个SQL文件调度一次，因此本程序适用于调度相对简单的数仓。简单的调度可以用Linux的crontab，复杂些的可以用Airflow这样的专业调度工具。

功能

• RDBMS
  通过JDBC或者数据库提供的导入导出功能进行双向同步。
  目前测试通过的有MySQL、Microsoft SQL Server、PostgreSQL
  待测试：DB2、Oracle

TODO

• MongoDB
• Elasticsearch
• Redis
• 文本文件

示例

从rdbms获取数据并插入hive表

• sql文件，如RDB_d.sql：

insert overwrite table stg.s_user partition(time_type=${time_type}, time_id='${time_id}')  
select id,username,password,mobile_phone,email,status,create_time,create_user,last_modify_time,last_modify_user,is_deleted 
from individual_user;

• Java中调用

public ExtractFromRDB(SparkSession spark, Integer timeType, String timeID, Integer backDate, String dbID, Integer frequency) throws SQLException, ClassNotFoundException {
    super(spark, timeType, timeID, backDate, dbID, frequency);
}
public void dpFull() throws Exception {
    exeSQLFile("RDB_d.sql", "insert");  // exeType: insert jdbc方式导入；load 通过rdb命令或者jdbc方式生成文件，然后load到hive表中
}

spark上的执行

• sql文件，如s2i_d.sql

insert overwrite table inte.i_item partition (time_type=${time_type}, time_id='${time_id}')
select i.id, u.id, s.id, w.id, i.item_type,
    i.category_one_id, i.category_two_id, i.category_three_id, w.warehouse_type, u.mobile_phone, u.email
from stg.s_user u, stg.s_shop s, stg.s_item i, stg.s_warehouse w
where u.id = s.user_id and s.id = i.shop_id and s.id = w.shop_id
and u.time_type = ${time_type} and u.time_id ='${time_id}'
and s.time_type = ${time_type} and s.time_id ='${time_id}'
and i.time_type = ${time_type} and i.time_id ='${time_id}'
and w.time_type = ${time_type} and w.time_id ='${time_id}';

• Java中调用

public Tran(SparkSession spark, Integer timeType, String timeID, Integer backDate, Integer frequency) {
    super(spark, timeType, timeID, backDate, frequency);
}
public void s2iD() throws Exception {
    exeSQLFile("s2i_d.sql", "insert"); // exeType: insert 插入hive表中; view 生成v_${表名}的临时试图，供后续使用
}

计算结果导出到rdbms

• sql文件，如RDB_d.sql：

delete from m_item_type where time_type = ${time_type} and time_id = '${time_id}';  -- 删除rdb表数据，rdb语法
@m_item_type -- rdb表名
select cast(time_type as smallint) time_type, time_id, cast(item_type as smallint) item_type, user_no, item_no, shop_no, warehouse_no  -- hive数据查询,字段名、数据类型与rdb保持一致
from dm.m_item_type
where time_type = ${time_type} and time_id = '${time_id}';

• Java中调用

public ExportToRDB(SparkSession spark, Integer timeType, String timeID, Integer backDate, String dbID, Integer frequency) throws SQLException, ClassNotFoundException {
    super(spark, timeType, timeID, backDate, dbID, frequency);
}
public void dpD() throws Exception {
    exeSQLFile("RDB_d.sql", "load"); // exeType: insert 通过jdbc方式插入rdb中；load 将数据导到本地，然后用数据库load方式入库；db: 在rdb中执行SQL
}

git地址

https://github.com/dazheng/SparkETL