Hive实现自增列的两种方法

落日映苍穹つ 2023-06-17 08:54 2阅读 0赞

多维数据仓库中的维度表和事实表一般都需要有一个代理键，作为这些表的主键，代理键一般由单列的自增数字序列构成。Hive没有关系数据库中的自增列，但它也有一些对自增序列的支持，通常有两种方法生成代理键：使用row\_number()窗口函数或者使用一个名为UDFRowSequence的用户自定义函数（UDF）。

假设有维度表tbl\_dim和过渡表tbl\_stg，现在要将tbl\_stg的数据装载到tbl\_dim，装载的同时生成维度表的代理键。  
用row\_number()函数生成代理键  
\[sql\] view plain copy  
   
insert into tbl\_dim      
select row\_number() over (order by tbl\_stg.id) + t2.sk\_max, tbl\_stg.\*      
from tbl\_stg     
cross join (select coalesce(max(sk),0) sk\_max from tbl\_dim) t2;     
        上面语句中，先查询维度表中已有记录最大的代理键值，如果维度表中还没有记录，利用coalesce函数返回0。然后使用cross join连接生成过渡表和最大代理键值的笛卡尔集，最后使用row\_number()函数生成行号，并将行号与最大代理键值相加的值，作为新装载记录的代理键。  
用UDFRowSequence生成代理键  
\[sql\] view plain copy  
   
add jar hdfs:///user/hive-contrib-2.0.0.jar;      
create temporary function row\_sequence as 'org.apache.hadoop.hive.contrib.udf.udfrowsequence';     
    
insert into tbl\_dim      
select row\_sequence() + t2.sk\_max, tbl\_stg.\*      
from tbl\_stg     
cross join (select coalesce(max(sk),0) sk\_max from tbl\_dim) t2;    
        hive-contrib-2.0.0.jar中包含一个生成记录序号的自定义函数udfrowsequence。上面的语句先加载JAR包，然后创建一个名为row\_sequence()的临时函数作为调用UDF的接口，这样可以为查询的结果集生成一个自增伪列。之后就和row\_number()写法类似了，只不过将窗口函数row\_number()替换为row\_sequence()函数。

以上摘自：http://blog.csdn.net/wzy0623/article/details/53893174博文！

心得:

这里需要强调下：

第二种方式使用为apache版本的hive。对于CDH版本hive需要在集群的中找到其相关jar，

具体步骤：

1.进入集群如\[root@sdzn-server06 dm\_lots\]\# find / -name hive-contrib-\*

准确找到jar包和路经。如/opt/cloudera/parcels/CDH-5.3.6-1.cdh5.3.6.p0.11/jars/hive-contrib-0.13.1-cdh5.3.6.jar

2.将其放入到对应（自定义）的集群下的目录

如图

3.创建函数

create temporary function row\_sequence as 'org.apache.hadoop.hive.contrib.udf.UDFRowSequence';

这里需要注意org.apache.hadoop.hive.contrib.udf.UDFRowSequence必须完全正确否则，创建失败！

查看方式：

将jar导出并解压，一一核对即可！

4、UDF使用异常

如下：

Logging initialized using configuration in jar:file:/opt/cloudera/parcels/CDH-5.3.6-1.cdh5.3.6.p0.11/jars/hive-common-0.13.1-cdh5.3.6.jar!/hive-log4j.properties  
FAILED: SemanticException \[Error 10004\]: Line 3:7 Invalid table alias or column reference 'row\_sequence'

如异常所提示，无效别名或者列，说明该自定义函数不可用

可能情况：

1.核对函数据（不要笑，很多人会犯这种错误）

2.查看创建方式是否正确，

解决方案：

create  function dw.rows\_sequence  as 'org.apache.hadoop.hive.contrib.udf.UDFRowSequence' using jar 'hdfs:///UDF/hive-contrib-0.13.1-cdh5.3.6.jar';

org.apache.hadoop.hive.contrib.udf.UDFRowSequence：该路径是jar解压缩后的路径。

这样在hive -e中可以使用。

————————————————  
版权声明：本文为CSDN博主「顺顺顺子」的原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接及本声明。  
原文链接：https://blog.csdn.net/xiaoshunzi111/article/details/68067674