Hive3连接RDBMS和使用函数
- 2020 年 4 月 7 日
- 笔记
JdbcStorageHandler查询RDBMS
使用JdbcStorageHandler，可以将Hive连接到MySQL，PostgreSQL，Oracle，DB2或Derby数据源。然后，您可以创建一个表示数据的外部表，并查询该表。
此任务假定您是CDP数据中心用户。您创建一个使用JdbcStorageHandler连接到并读取本地JDBC数据源的外部表。
1. 将数据加载到群集中某个节点上受支持的SQL数据库（如MySQL）中，或使自己熟悉数据库中的现有数据。
2. 使用JdbcStorageHandler和指定最小信息的表属性创建一个外部表：数据库类型，驱动程序，数据库连接字符串，用于查询hive的用户名和密码，表名以及与Hive的活动连接数。
CREATE EXTERNAL TABLE mytable_jdbc( col1 string, col2 int, col3 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" = "MYTABLE", "hive.sql.dbcp.maxActive" = "1" );
• 查询外部表。
SELECT * FROM mytable_jdbc WHERE col2 = 19;
使用函数
您可以调用内置的Hive函数来执行多种操作之一，而不必执行多个步骤。您可以使用SHOW FUNCTIONS搜索或列出可用功能。当无法使用内置函数来执行所需的操作时，可以创建用户定义函数（UDF）。您可能需要重新加载函数以更新在另一个会话中创建的函数的可用性。
• 重新加载，查看和筛选 函数 要确定可用的Hive功能和运算符，请重新加载函数，然后使用SHOW FUNCTIONS语句。语句中的可选模式将过滤语句返回的功能列表。
• 创建用户定义的函数 您可以从与Hadoop和Hive兼容的Java项目中将用户自定义函数（UDF）导出到JAR，并将JAR存储在集群或对象存储中。使用Hive命令，您可以基于JAR注册UDF，然后从Hive查询中调用UDF。
2.1. 重新加载，查看和过滤函数
若要确定可用的Hive函数和运算符，请重新加载函数，然后使用SHOW FUNCTIONS语句。语句中的可选模式将过滤语句返回的功能列表。
在此任务中，您首先需要重新加载函数以使会话开始后在Hive会话中注册的所有用户定义函数可用。语法为：
RELOAD (FUNCTION|FUNCTIONS);
接下来，使用SHOW FUNCTIONS语句。该语句的语法为：
SHOW FUNCTIONS [LIKE "<pattern>"];
<pattern>表示可以包含正则表达式通配符的搜索字符。
最后，您可以通过发出DESCRIBE FUNCTION语句来获得有关使用的更多信息。
1. 打开Hive Shell。
在群集节点的命令行上，例如：
beeline -u jdbc:hive2://mycloudhost-3.com:10000 -n <your user name> -p
2. 重新加载功能以确保您的会话中所有注册的UDF均可用。
RELOAD FUNCTIONS;
使用命令的复数形式。RELOAD FUNCTION是为了向后兼容。
3. 生成可用的内置和用户定义函数（UDF）的列表。
SHOW FUNCTIONS;
出现内置函数，运算符和UDF的列表。
+------------------------------+ | tab_name | +------------------------------+ | ! | | != | | $sum0 | | % | ...
4. 使用正则表达式通配符生成功能过滤列表 %。
SHOW FUNCTIONS LIKE "a%";
a 出现以字符开头的所有可用功能。
+------------------------------+ | tab_name | +------------------------------+ | abs | | acos | | add_months | ...
5. 获取有关特定功能的更多信息。
DESCRIBE FUNCTION abs; +-------------------------------------------+ | tab_name | +-------------------------------------------+ | ABS(x) - returns the absolute value of x | +-------------------------------------------+
6. 获取有关该功能的更多信息。
DESCRIBE FUNCTION EXTENDED abs; +----------------------------------------------------+ | tab_name | +----------------------------------------------------+ | ABS(x) - returns the absolute value of x | | Synonyms: abs | | Example: | | > SELECT ABS(0) FROM src LIMIT 1; | | 0 | | > SELECT ABS(-5) FROM src LIMIT 1; | | 5 | | Function class:org.apache.hadoop.hive.ql.udf.generic.GenericUDFAbs | | Function type:BUILTIN | +----------------------------------------------------+
创建用户自定义函数
您可以从与Hadoop和Hive兼容的Java项目中将用户自定义函数（UDF）导出到JAR，然后将JAR存储在集群或对象存储中。使用Hive命令，您可以基于JAR注册UDF，然后从Hive查询中调用UDF。
• 您具有将JAR上载到集群或集群或对象存储的访问权限。
Cloudera Manager，最低要求角色：配置器（也由Cluster Administrator，Full Administrator提供）。
• HiveServer在群集上运行。
• 您已在要创建UDF的计算机或虚拟机上安装了Java和Java集成开发环境（IDE）工具。
1. 设置开发环境 您可以使用IntelliJ 在开发环境 中创建Hive UDF，并使用从Cloudera集群下载的Hive和Hadoop JARS构建UDF。
2. 创建UDF类 您可以在新类中定义UDF逻辑，该类将返回表中所选列的数据类型。
3. 生成项目并上载JAR 您可以将UDF代码编译成JAR，然后将JAR添加到群集上的类路径中。您需要使用直接引用或为Hive配置群集以找到JAR。
4. 注册UDF 在群集中，您登录到Hive，然后从Beeline运行命令以使UDF在Hive查询中起作用。在HiveServer重新启动之间，UDF仍然存在。
5. 在查询中调用UDF 注册UDF之后，无需在查询中使用UDF之前重新启动Hive。在此示例中，您调用在SELECT语句中创建的UDF，Hive返回您指定的列的数据类型。
原文链接：https://docs.cloudera.com/runtime/7.0.3/using-hiveql/topics/hive_query_sql_using_jdbcstoragehandler.html
