JDBC 存储处理程序

Syntax

JdbcStorageHandler 支持从 Hive 中的 jdbc 数据源读取。当前不支持写入 jdbc 数据源。要使用 JdbcStorageHandler，您需要使用 JdbcStorageHandler 创建一个外部表。这是一个简单的示例：

CREATE EXTERNAL TABLE student_jdbc
(
  name string,
  age int,
  gpa double
)
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/sample",
    "hive.sql.dbcp.username" = "hive",
    "hive.sql.dbcp.password" = "hive",
    "hive.sql.table" = "STUDENT",
    "hive.sql.dbcp.maxActive" = "1"
);

您还可以使用 alter table 语句来更改 jdbc 外部表的表属性，就像其他 nonlocalHive 表一样：

ALTER TABLE student_jdbc SET TBLPROPERTIES ("hive.sql.dbcp.password" = "passwd");

Table Properties

在 create table 语句中，需要指定以下表属性：

hive.sql.database.type：MYSQL，POSTGRES，ORACLE，DERBY，DB2
hive.sql.jdbc.url：jdbc 连接字符串
hive.sql.jdbc.driver：jdbc 驱动程序类
hive.sql.dbcp.username：jdbc 用户名
hive.sql.dbcp.password：明文形式的 jdbc 密码，强烈建议不要使用此参数。推荐的方法是将其存储在密钥库中。有关详细信息，请参见“保护密码”部分。
hive.sql.table/hive.sql.query：您将需要指定“ hive.sql.table”或“ hive.sql.query”以告诉如何从 jdbc 数据库获取数据。 “ hive.sql.table”表示单个表，“ hive.sql.query”表示任意 sql 查询。

除了上述必需属性之外，您还可以指定可选参数来调整连接详细信息和性能：

hive.sql.catalog：jdbc 目录名称(仅在指定“ hive.sql.table”时有效)
hive.sql.schema：jdbc 模式名称(仅在指定“ hive.sql.table”时有效)
hive.sql.jdbc.fetch.size：要批量提取的行数
hive.sql.dbcp.xxx：所有 dbcp 参数将传递给 commons-dbcp。有关参数的定义，请参见https://commons.apache.org/proper/commons-dbcp/configuration.html。例如，如果您在表属性中指定 hive.sql.dbcp.maxActive = 1，则 Hive 会将 maxActive = 1 传递给 commons-dbcp

支持的数据类型

Hive JdbcStorageHandler 表的列数据类型可以是：

数值数据类型：字节，短整数，整数，长整数，浮点数，双精度
小数位数和精度
字符串日期类型：string，char，varchar
Date
Timestamp

注意复杂的数据类型：不支持 struct，map，array

Column/Type Mapping

hive.sql.table/hive.sql.query 使用模式定义表格数据。模式定义必须与表模式定义相同。例如，以下创建表语句将失败：

CREATE EXTERNAL TABLE student_jdbc
(
  name string,
  age int,
  gpa double
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    . . . . . .
    "hive.sql.query" = "SELECT name, age, gpa, gender FROM STUDENT",
);

但是，hive.sql.table/hive.sql.query 架构的列名和列类型可能与表架构不同。在这种情况下，数据库列按位置 Map 到配置单元列。如果数据类型不同，Hive 将尝试根据 Hive 表模式进行转换。例如：

CREATE EXTERNAL TABLE student_jdbc
(
  sname string,
  age int,
  effective_gpa decimal(4,3)
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    . . . . . .
    "hive.sql.query" = "SELECT name, age, gpa FROM STUDENT",
);

Hive 将尝试将带下划线的表 STUDENT 的双“ gpa”转换为“ student_jdbc”表的 valid_gpa 字段的十进制(4,3)。如果无法进行转换，则 Hive 将为该字段生成 null。

Auto Shipping

如果在查询中使用 JdbcStorageHandler，则 JdbcStorageHandler 会将所需的 jar 自动发送到 MR/Tez/LLAP 后端。用户不需要手动添加 jar。如果 JdbcStorageHandler 在 Classpath(包括 mysql，postgres，oracle 和 mssql)中检测到任何 jdbc 驱动程序 jar，它还将把所需的 jdbc 驱动程序 jar 发送到后端。但是，仍然需要用户将 jdbc 驱动程序 jar 复制到 hiveClasspath(通常是 hive 中的 lib 目录)。

Securing Password

在大多数情况下，我们不想在表属性“ hive.sql.dbcp.password”中以明文形式存储 jdbc 密码。相反，用户可以使用以下命令将密码存储在 HDFS 上的 Java 密钥存储文件中：

hadoop credential create host1.password -provider jceks://hdfs/user/foo/test.jceks -v passwd1
hadoop credential create host2.password -provider jceks://hdfs/user/foo/test.jceks -v passwd2

这将在hdfs://user/foo/test.jceks上创建一个密钥库文件，该文件包含两个密钥：host1.password 和 host2.password。在 Hive 中创建表时，您需要在 create table 语句中指定“ hive.sql.dbcp.password.keystore”和“ hive.sql.dbcp.password.key”，而不是“ hive.sql.dbcp.password”：

CREATE EXTERNAL TABLE student_jdbc
(
  name string,
  age int,
  gpa double
)
STORED BY 'org.apache.hive.storage.jdbc.JdbcStorageHandler'
TBLPROPERTIES (
    . . . . . .
    "hive.sql.dbcp.password.keystore" = "jceks://hdfs/user/foo/test.jceks",
    "hive.sql.dbcp.password.key" = "host1.password",
    . . . . . .
);

您只需要授权目标用户使用授权程序(例如 Ranger)读取此文件，就可以保护密钥库文件。 Hive 将检查密钥库文件的权限，以确保用户在创建/更改表时具有对其的读取权限。

Partitioning

Hive 能够拆分 jdbc 数据源并并行处理每个拆分。用户可以使用以下表格属性来决定是否拆分以及拆分为多少拆分：

hive.sql.numPartitions：要为数据源生成多少拆分，如果没有拆分则为 1
hive.sql.partitionColumn：要拆分的列。如果指定了此选项，则 Hive 会将列拆分为从 hive.sql.lowerBound 到 hive.sql.upperBound 相等间隔的 hive.sql.numPartitions。如果未定义 partitionColumn 但 numPartitions> 1，则 Hive 将使用 offset 拆分数据源。但是，对于某些数据库，偏移量并不总是可靠的。如果要拆分数据源，强烈建议定义一个 partitionColumn。 partitionColumn 必须存在于架构“ hive.sql.table” /“ hive.sql.query”产生的模式中。
hive.sql.lowerBound/hive.sql.upperBound：用于计算间隔的 partitionColumn 的上下边界。这两个属性都是可选的。如果未定义，Hive 将针对数据源进行 MIN/MAX 查询，以获取下限/上限。请注意，hive.sql.lowerBound 和 hive.sql.upperBound 都不能为 null。第一个和最后一个分割是开放式的。并且该列的所有空值都将进入第一个拆分。

For example:

TBLPROPERTIES (
    . . . . . .
    "hive.sql.table" = "DEMO",
    "hive.sql.partitionColumn" = "num",
    "hive.sql.numPartitions" = "3",
    "hive.sql.lowerBound" = "1",
    "hive.sql.upperBound" = "10",
    . . . . . .
);

该表将创建 3 个拆分：num<4 or num is null, 4<=num<7, num> = 7

TBLPROPERTIES (
    . . . . . .
    "hive.sql.query" = "SELECT name, age, gpa/5.0*100 AS percentage FROM STUDENT",
    "hive.sql.partitionColumn" = "percentage",
    "hive.sql.numPartitions" = "4",
    . . . . . .
);

Hive 将执行 jdbc 查询以获取查询的百分比列的 MIN/MAX，即 60、100.然后表格将创建 4 个拆分：(，70)，[70,80)，[80,90)， [90，)。第一个拆分还包括空值。

要查看 JdbcStorageHandler 生成的拆分，请在 hiveserver2 日志或 Tez AM 日志中查找以下消息：

jdbc.JdbcInputFormat: Num input splits created 4
jdbc.JdbcInputFormat: split:interval:ikey[,70)
jdbc.JdbcInputFormat: split:interval:ikey[70,80)
jdbc.JdbcInputFormat: split:interval:ikey[80,90)
jdbc.JdbcInputFormat: split:interval:ikey[90,)

Computation Pushdown

Hive 将积极地将计算下推到 jdbc 表，因此我们可以充分利用 jdbc 数据源的本机容量。

例如，如果我们有另一个表表决器_jdbc：

EATE EXTERNAL TABLE voter_jdbc
(
  name string,
  age int,
  registration string,
  contribution decimal(10,2)
)
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/sample",
    "hive.sql.dbcp.username" = "hive",
    "hive.sql.dbcp.password" = "hive",
    "hive.sql.table" = "VOTER"
);

然后，以下联接操作将下推至 mysql：

select * from student_jdbc join voter_jdbc on student_jdbc.name=voter_jdbc.name;

这可以通过解释来体现：

explain select * from student_jdbc join voter_jdbc on student_jdbc.name=voter_jdbc.name;
        . . . . . .
        TableScan
          alias: student_jdbc
          properties:
            hive.sql.query SELECT `t`.`name`, `t`.`age`, `t`.`gpa`, `t0`.`name` AS `name0`, `t0`.`age` AS `age0`, `t0`.`registration`, `t0`.`contribution`
FROM (SELECT *
FROM `STUDENT`
WHERE `name` IS NOT NULL) AS `t`
INNER JOIN (SELECT *
FROM `VOTER`
WHERE `name` IS NOT NULL) AS `t0` ON `t`.`name` = `t0`.`name`
        . . . . . .

仅当“ hive.sql.table”定义了 jdbc 表时，才会进行计算下推。 Hive 将使用“ hive.sql.query”属性重写数据源，并在表顶部进行更多计算。在上面的示例中，mysql 将运行查询并检索联接结果，而不是获取两个表并在 Hive 中进行联接。

运算符可以下推，包括过滤器，转换，联接，联合，聚合和排序。

派生的 mysql 查询可能非常复杂，在许多情况下，我们不想拆分数据源，因此每次拆分都会多次运行复杂查询。因此，如果计算更多，而不仅仅是过滤和变换，即使“ hive.sql.numPartitions”大于 1，Hive 也不会拆分查询结果。

Docs

Docs4dev

Title here