Hive实现自增列的两种方法

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

  假设有维度表tbl_dim和过渡表tbl_stg，现在要将tbl_stg的数据装载到tbl_dim，装载的同时生成维度表的代理键。

• 用row_number()函数生成代理键

[sql]  view plain  copy

 

1. insert into tbl_dim    
2. select row_number() over (order by tbl_stg.id) + t2.sk_max, tbl_stg.*    
3. from tbl_stg   
4. cross join (select coalesce(max(sk),0) sk_max from tbl_dim) t2;   

        上面语句中，先查询维度表中已有记录最大的代理键值，如果维度表中还没有记录，利用coalesce函数返回0。然后使用cross join连接生成过渡表和最大代理键值的笛卡尔集，最后使用row_number()函数生成行号，并将行号与最大代理键值相加的值，作为新装载记录的代理键。

• 用UDFRowSequence生成代理键

[sql]  view plain  copy

 

1. add jar hdfs:///user/hive-contrib-2.0.0.jar;    
2. create temporary function row_sequence as 'org.apache.hadoop.hive.contrib.udf.udfrowsequence';   
3.   
4. insert into tbl_dim    
5. select row_sequence() + t2.sk_max, tbl_stg.*    
6. from tbl_stg   
7. 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中可以使用。