[大数据]Hive 使用 JdbcStorageHandler 查询其他数据库中的表示例

在 Hive 3.0 中，JdbcStorageHandler 可以把任何数据库中的表或者视图映射为 Hive 数据库中的外部表，然后可以像普通表一样执行 SELECT 操作，和其他表 join 操作。但是不能插入数据，现在 JdbcOutputFormat 的 getHiveRecordWriter 方法现在抛出异常（“Write operations are not allowed”）。

先把相应数据库的 jdbc 驱动放到 ${HIVE_HOME}/lib

以下操作以 MYSQL 为例，把 mysql-connector-java.jar 拷贝到目录。

MYSQL 数据库端操作

连接 MYSQL 数据库后，执行以下语句：

create database test;
create table test.t1(c1 int);
insert into test.t1 values(1),(2);

在 Hive 环境中执行以下语句：

注意替换相应的 url, username,password 字段。注意：新版的MySQL 驱动改为 com.mysql.cj.jdbc.Driver, 原来是 com.mysql.jdbc.Driver, 使用原来的驱动，也能执行，第一次有告警信息。

CREATE EXTERNAL TABLE t1(c1 INT)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
"hive.sql.database.type"="MYSQL",
"hive.sql.jdbc.driver" = "com.mysql.cj.jdbc.Driver",
"hive.sql.jdbc.url" = "jdbc:mysql://localhost/metastore",
"hive.sql.table" = "t1",
"hive.sql.dbcp.username" = "root",
"hive.sql.dbcp.password" = "mysql_password"
);

show create table

hive> show create table t1;
OK
CREATE EXTERNAL TABLE `t1`(
  `c1` int COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.apache.hive.storage.jdbc.JdbcSerDe' 
STORED BY 
  'org.apache.hive.storage.jdbc.JdbcStorageHandler' 
WITH SERDEPROPERTIES ( 
  'serialization.format'='1')
TBLPROPERTIES (
  'bucketing_version'='2', 
  'hive.sql.database.type'='MYSQL', 
  'hive.sql.dbcp.password'='mysql_password', 
  'hive.sql.dbcp.username'='root', 
  'hive.sql.jdbc.driver'='com.mysql.cj.jdbc.Driver', 
  'hive.sql.jdbc.url'='jdbc:mysql://localhost/metastore', 
  'hive.sql.table'='t1', 
  'last_modified_by'='houzhizhen', 
  'last_modified_time'='1647859796', 
  'transient_lastDdlTime'='1647859796')

检索数据测试

hive> select * from t1;
OK
1
2

其他TBLIPROPERTIES 参数

column.mapping 测试

多个列用逗号分割，对于每个列有两个列名，用‘=’ 分割，前面是 hive 的列名，后面是后端数据库的列名。以下示例把后端数据库的 c1 列映射为 Hive 的 c2 列。

CREATE EXTERNAL TABLE t1_1(c2 INT)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
"hive.sql.database.type"="MYSQL",
"hive.sql.jdbc.driver" = "com.mysql.jdbc.Driver",
"hive.sql.jdbc.url" = "jdbc:mysql://localhost/metastore",
"hive.sql.table" = "t1",
"hive.sql.dbcp.username" = "root",
"hive.sql.dbcp.password" = "mysql_password",
"hive.sql.column.mapping" = "c2=c1"
);

进行测试

select * from t1_1;