使用hive sql 为hive增加或者创建自增列，自增id的五种方式

北凉徐凤年

7838人浏览 · 2023-03-10 11:07:35

北凉徐凤年 · 2023-03-10 11:07:35 发布

*注意：此篇完全是废话，是错误演示文档

我的原始查询语句展示和表展示。

select
            channelCount,
            intBuffer[0] data1
from vibration_data;

第一种方式

创建带有自增ID的Hive表的方法是使用Hive中的SERDE（序列化和反序列化）和ROW FORMAT关键字来为表添加自增ID。具体步骤如下：

CREATE TABLE your_table (
  id INT,
  col1 STRING,
  col2 INT,
  col3 DOUBLE
)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe'
WITH SERDEPROPERTIES (
  'serialization.format' = ',',
  'field.delim' = ',',
  'line.delim' = '\n',
  'collection.delim' = '#'
)
STORED AS TEXTFILE;

SET hive.exec.dynamic.partition.mode=nonstrict;

INSERT INTO TABLE your_table
SELECT ROW_NUMBER() OVER (), col1, col2, col3
FROM your_source_table;

在上面的代码中，我们首先使用CREATE TABLE语句创建一个名为your_table的表，并指定了表的列和数据类型。其中，我们为表添加了一个名为id的列，其数据类型为INT。接下来，我们使用ROW FORMAT和SERDE关键字为表定义序列化和反序列化格式，并指定了序列化和反序列化所需的参数。然后，我们使用STORED AS TEXTFILE将表存储为文本文件格式。在这里，我们使用了默认的LazySimpleSerDe，但是你也可以选择其他的序列化和反序列化类来满足你的需求。

接下来，我们使用SET语句设置了一个Hive参数，用于启用动态分区模式。最后，我们使用INSERT INTO语句将原表中的所有行插入到新表中，并使用ROW_NUMBER() OVER ()函数为每一行生成一个递增的ID。最后，我们将原表中的其他列（col1，col2和col3）一起插入到新表中。

请将上述代码中的your_table，your_source_table，col1，col2和col3替换为你的实际表名和列名。

会造成原数据位置变化不建议用

第二种方式

select
      tab1.channelCount,
      tab1.data1,
        ROW_NUMBER() OVER (ORDER BY tab1.id) vibration_id
from (select
            channelCount,
            intBuffer[0] data1,
            1 id
        from vibration_data) tab1;

新增一列固定值，然后使用开窗函数

会造成原数据位置变化不建议用

第三种方式

使用Hive的UDF函数uuid()来为每一行生成唯一的ID，并通过ROW_NUMBER()函数生成递增的ID序列

select
            channelCount,
            intBuffer[0] data1,
            row_number() OVER (ORDER BY uuid()) vibration_id
        from vibration_data;

我们使用uuid()函数为每一行生成一个唯一的ID，这个ID不是递增的。接下来，我们使用row_number()函数生成递增的ID序列，该函数根据uuid()函数生成的唯一ID排序。

会造成原数据位置变化不建议用

第四种方式

使用Hive的内置变量__ROW__ID__来生成递增的ID序列，并将其赋值给新添加的ID列。具体步骤如下：

ALTER TABLE your_table ADD COLUMN id INT;

SET hivevar:row_id=0;
INSERT INTO TABLE your_table
SELECT (${hivevar:row_id}+1), *
FROM your_table
CROSS JOIN (SELECT @hivevar:row_id:=@hivevar:row_id+1) t;

我们首先向表中添加了一个名为id的新列，其数据类型为INT。然后，我们定义了一个名为hivevar:row_id的Hive变量，初始值为0。接下来，我们使用INSERT INTO语句将原表中的所有行插入到一个新的表中，并在这个新表中为每一行生成一个递增的ID。在这里，我们使用了CROSS JOIN操作符将原表与一个虚拟表连接，该虚拟表使用SET语句中定义的变量递增生成递增的ID。最后，我们将生成的ID和原表中的所有列一起插入到原表中。

请将上述代码中的your_table替换为你的实际表名。