Hive JdbcStorageHandler 实践

背景

在数据仓库的建设中从业务库同步数据到分析库后者hive是不可避免的流程。在关系型数据库到hive的流程中，现有的业务是用sqoop去同步，但是sqoop1的bug还是有的，例如同步数据的时候的编码问题，特殊字符问题，字段为null的问题等。使用hive的JdbcStorageHandler可以简化数据同步的过程，本文记录使用hive这个功能的过程，和遇到的已经解决的还未解决的问题。

依赖

使用这个功能需要先加载hive-jdbc-handler的依赖包，如果不加jar包的话，会有如下的提示：
FAILED: SemanticException Cannot find class 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
所以需要先下载对应的jar包，在建表之前将jar包引入。
add jar /home/user/hive-jdbc-handler-3.1.2.jar;

建表

建表的语法如下，需要指定数据库的host，dbname,用户名密码等，同时还要指定在hive中使用的方式是read还是write，如下是说在hive中只read响应的表，不需要insert。
``
CREATE EXTERNAL TABLE tmp.click_farm
(
stat_date date,
kind string,
type string,
click_number int,
flag int,
update_time string
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
"hive.sql.database.type" = "POSTGRES",
"hive.sql.jdbc.driver" = "org.postgresql.Driver",
"hive.sql.jdbc.url" = "jdbc:postgresql://host:port/dbname",
"hive.sql.jdbc.read-write" = "read",
"hive.sql.dbcp.username" = "user_name",
"hive.sql.dbcp.password" ="pswd",
"hive.sql.table" = "=tmp.click_farm",
"hive.sql.query"="select * from tmp.click_farm"
);
``

参数说明

参数	描述
hive.sql.jdbc.database.type	数据库类型
hive.sql.jdbc.url	jdbc连接字符串
hive.sql.jdbc.driver	jdbc驱动程序类
hive.sql.jdbc.username	用户名
hive.sql.jdbc.password	密码
hive.sql.jdbc.read-write	只读表 read 可读可写表 read,write
hive.sql.table	对应的表名
hive.sql.jdbc.partition.column	对应分区字段
hive.sql.jdbc.partition.nums	分区数量
hive.sql.jdbc.split.size	切分map的大小
hive.sql.jdbc.fetch.size	jdbc 读取数据 fetch 大小
hive.sql.jdbc.batch.size	jdbc 每次提交的数据量（批量插入数据库）

密码问题

在上面的建表语句中，数据库的密码就写在建表语句中，show create table 就能看到密码，很不安全。官方提供了可以将密码隐去的方式：https://cwiki.apache.org/confluence/display/Hive/JdbcStorageHandler

密码实践

按照wiki上的用法，使用
hadoop credential create host1.password -provider jceks://hdfs/user/user.jceks 命令在hdfs上生成秘钥，验证秘钥确实存在。

建表

`
add jar /home/ticketdev/hive-jdbc-handler-3.1.2.jar;
CREATE EXTERNAL TABLE tmp.click_farm
(
stat_date date,
kind string,
type string,
click_number int,
flag int,
update_time string
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
"hive.sql.database.type" = "POSTGRES",
"hive.sql.jdbc.driver" = "org.postgresql.Driver",
"hive.sql.jdbc.url" = "jdbc:postgresql://host:port/dbname",
"hive.sql.dbcp.username" = "user_name",
"hive.sql.dbcp.password.keystore" = "jceks://hdfs/user/user.jecks",
"hive.sql.dbcp.password.key" = "host1.password",
"hive.sql.table" = "tmp.click_farm",
"hive.sql.query"="select * from tmp.click_farm"
);

`
此时会提示：
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. java.lang.RuntimeException: MetaException(message:org.apache.hadoop.hive.serde2.SerDeException org.apache.hive.storage.jdbc.exception.HiveJdbcDatabaseAccessException: Error while trying to get column names: Cannot create JDBC driver of class 'org.postgresql.Driver' for connect URL 'jdbc:postgresql://host:port/dbname')
提示的是找不到对应的秘钥，但是秘钥确实在hdfs上，这个问题没解决。

性能

用暴露用户名和密码的方式建了一张表，表的大小是110G，3亿的数据量，在hive中去查询的时候会发现查询一直卡住。对于大表，这种方式的风险还是很大的，并且高度依赖索引。测试30G体量，6000万数据的表，发现查询速度还可以。