Hive JdbcStorageHandler 实践

发布于 1月15日  约 7 分钟

背景

在数据仓库的建设中从业务库同步数据到分析库后者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万数据的表,发现查询速度还可以。

阅读 181发布于 1月15日