Hive：如何分解嵌入 CSV 文件中的 JSON 列？

从 CSV 文件（带有标题和管道分隔符）中，我得到了以下两个内容，其中包含一个 JSON 列（内部有一个集合），如下所示：

第一种情况（使用没有名称的 JSON 集合）：

ProductId|IngestTime|ProductOrders
9180|20171025145034|[{"OrderId":"299","Location":"NY"},{"OrderId":"499","Location":"LA"}]
8251|20171026114034|[{"OrderId":"1799","Location":"London"}]

第二种情况（带有名为“Orders”的 JSON 集合）：

ProductId|IngestTime|ProductOrders
9180|20171025145034|{"Orders":[{"OrderId":"299","Location":"NY"},{"OrderId":"499","Location":"LA"}]}
8251|20171026114034|{"Orders":[{"OrderId":"1799","Location":"London"}]}

首先，我创建我的“原始”表，如下所示：

DROP TABLE IF EXISTS Product;
CREATE EXTERNAL TABLE Product (
  ProductId STRING,
  IngestTime STRING,
  ProductOrders STRING
)
COMMENT "Product raw table"
ROW FORMAT DELIMITED
FIELDS TERMINATED BY '\|'
STORED AS TEXTFILE
LOCATION
  '/data/product'
TBLPROPERTIES ("skip.header.line.count"="1");

当我使用以下命令查询我的表时：

SELECT * FROM Product

我得到了以下答案：

第一种情况（使用没有名称的 JSON 集合）：

ProductId  IngestTime      ProductOrders
9180       20171025145034  [{"OrderId":"299","Location":"NY"},{"OrderId":"499","Location":"LA"}]
8251       20171026114034  [{"OrderId":"1799","Location":"London"}]

第二种情况（带有名为“Orders”的 JSON 集合）：

ProductId  IngestTime      ProductOrders
9180       20171025145034  {"Orders":[{"OrderId":"299","Location":"NY"},{"OrderId":"499","Location":"LA"}]}
8251       20171026114034  {"Orders":[{"OrderId":"1799","Location":"London"}]}

好的，真的很好，到目前为止效果很好！

但我现在需要的是创建一个返回的 SELECT 查询：

ProductId  IngestTime      ProductOrderId ProductLocation
9180       20171025145034  299            NY
9180       20171025145034  499            LA
8251       20171026114034  1799           London

我确实需要一个可移植的 SQL 查询，它适合我的两种情况（带或不带标签“OrderId”）。

到目前为止，我使用“explode”、“get_json_object”等尝试了多种组合，但仍然没有找到正确的 SQL 查询。

非常感谢你的帮助 ：-）

你可以试试

CREATE EXTERNAL TABLE product(productid String,ingesttime String, productorders array<struct<orderid:String,location:string>> ) 

select productid,ingesttime, productorders.orderid[0] as orderid , productorders.location[0] as location from product