Hive中如何转换超2^63-1的16位十六进制字符串为bigint？

问题分析 #

核心矛盾在于：16位十六进制字符串对应64位无符号整数（范围0~2^{64-1），但Hive的`bigint`是**有符号64位整数**，最大值仅为2}63-1（9223372036854775807），超出该范围的数值用常规cast会返回null；而unhex返回二进制类型，Hive不支持直接将其转为bigint，因此触发报错。

解决方案 #

根据需求不同，可采用两种处理方式：

方式一：转为有符号BigInt（映射到-2^63~263-1范围）

16位十六进制的最高位（第一个字符）如果是8-F，说明对应无符号数超过2^63-1，需转换为有符号负数：

SELECT
  CASE
    -- 判断最高位是否为符号位（对应无符号数≥2^63）
    WHEN SUBSTR(hash_col, 1, 1) IN ('8','9','A','B','C','D','E','F')
    -- 先转成大精度decimal，再减去2^64得到对应的有符号负数
    THEN CAST(conv(hash_col, 16, 10) AS DECIMAL(38,0)) - POWER(2,64)
    -- 数值在2^63-1以内，直接转bigint
    ELSE CAST(conv(hash_col, 16, 10) AS BIGINT)
  END AS hash_bigint
FROM mytable LIMIT 10;

方式二：用Decimal存储超大数值

如果不需要限制为bigint类型，直接用decimal(38,0)存储完整的无符号数值：

SELECT CAST(conv(hash_col, 16, 10) AS DECIMAL(38,0)) AS hash_decimal
FROM mytable LIMIT 10;

对之前尝试的说明 #

• conv(hash_col,16,10)返回字符串格式的十进制数，当数值超过2^63-1时，cast(...) as bigint会因超出范围返回null；
• unhex仅将十六进制字符串转为二进制字节流，并非直接转换为数值，Hive无内置函数支持binary直接转bigint，因此报错。

内容的提问来源于stack exchange，提问作者John Jiang