从DynamoDB在Hive创建外部表报错‘Unsupported Hive type: int’求助

我尝试通过以下SQL在Hive中基于DynamoDB表创建外部表StoreData：

create external table StoreData(rowid INT, order_priority STRING, 
discount FLOAT, unit_price FLOAT, shipping_cost FLOAT, customer_id INT,
customer_name STRING, ship_mode STRING, customer_segment STRING,
product_category STRING, product_subcategory STRING,
product_container STRING, product_name STRING, product_base_margin FLOAT,
country STRING, region STRING, state_or_province STRING, city STRING,
postal_code INT, order_date STRING, ship_date STRING, profit DOUBLE,
quantity_ordered_new INT, sales FLOAT, order_id INT)
stored by 'org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler' TBLPROPERTIES ("dynamodb.table.name" = "bigdataassignment1",
"dynamodb.column.mapping" = "rowid:`Row ID`,order_priority:`Order Priority`,
discount:Discount,unit_price:`Unit Price`,shipping_cost:`Shipping Cost`,
customer_id:`Customer ID`,customer_name:`Customer Name`,ship_mode:`Ship Mode`,
customer_segment:`Customer Segment`,product_category:`Product Category`,
product_subcategory:`Product Sub-Category`,product_container:`Product Container`,
product_name:`Product Name`,product_base_margin:`Product Base Margin`,
country:Country,region:Region,state_or_province:`State or Province`,city:City,
postal_code:`Postal Code`,order_date:`Order Date`,ship_date:`Ship Date`,
profit:Profit,quantity_ordered_new:`Quantity ordered new`,sales:Sales,order_id:`Order ID");

该表结构在Redshift中可正常使用，但在Hive执行时触发错误：Unsupported Hive type: int。我确认INT/INTEGER是Hive默认支持类型，且未找到相关错误案例，寻求解决办法。

解决方法 #

1. 替换INT类型为BIGINT #

问题根源在于org.apache.hadoop.hive.dynamodb.DynamoDBStorageHandler对Hive 32位整数类型INT存在兼容性问题——DynamoDB的数值类型（N）默认适配更大范围的整数，部分handler版本未兼容Hive的INT。将所有INT字段替换为BIGINT即可解决：

修改后的核心SQL片段：

create external table StoreData(
  rowid BIGINT, 
  order_priority STRING, 
  discount FLOAT, 
  unit_price FLOAT, 
  shipping_cost FLOAT, 
  customer_id BIGINT,
  -- 其余字段按原有逻辑保留，仅将所有INT替换为BIGINT
  postal_code BIGINT,
  quantity_ordered_new BIGINT,
  order_id BIGINT
)
-- 存储handler及表属性部分保持不变

2. 升级DynamoDB存储Handler版本 #

若替换类型后仍报错，需检查hive-dynamodb-handler的版本是否与当前Hive版本兼容。旧版本的handler可能存在类型映射缺陷，建议升级到与Hive版本匹配的最新稳定版。

3. 验证DynamoDB字段类型 #

确认DynamoDB表中对应Hive整数字段的属性类型为N（数值类型）。如果DynamoDB中是用字符串存储的数字，需将Hive表对应字段改为STRING，后续可通过CAST函数转换为数值类型。

内容的提问来源于stack exchange，提问作者Andriy Sultanov