HIVE SQL将记录提取为JSON

Question

我在配置单元中有一个包含多个字段的表。例如

-------------------------------------------------------------
|Primary Key|Attribute 1|Attribute 2|Attribute 3|Attribute 4|
-------------------------------------------------------------

我需要使用HIVESQL查询表，并返回嵌入CSV格式的JSON对象。例如：

Primary Key, Attribute 1, {"Primary Key":"", "Attribute 2":"",  "Attribute 2":""}, Attribute 4

我没有任何查询后编程语言来将值转换为JSON格式。我对直接SQL没有问题，只是不知道是否可以使用SQL获得JSON对象。

非常感谢任何想法。

Answer 1

我使用brickhouse JAR (brickhouse-0.6.0.jar)作为JSON输出来实现类似的功能。这是可用的最好的jar，但如果我们需要JSON中的密钥来保留camelcases，则需要在to_json(named_struct())子句中放入一个额外的元素。

您可以在这里阅读更多关于这个JAR的信息。下面是我如何做到这一点的代码片段

ADD JAR path/to/jar/brickhouse-0.6.0.jar;
CREATE TEMPORARY FUNCTION to_json AS 'brickhouse.udf.json.ToJsonUDF'; 
CREATE TABLE IF NOT EXISTS V2
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ','
LINES TERMINATED BY 'n'
SELECT
empId,
to_json(named_struct('camel_case', 1, 'employee_id', employeeId, 'manager_id', msgId, 'org_Id', orgId), true) AS jsonString
FROM 
employee
WHERE 
employeeId=101
);

输出如下：

101, {"camelCase" : true, "employeeId" : 101, "managerId" : 201, "orgId" : 301}

我必须处理必须处理需要放在方括号([](中的Array of JSON元素的情况。如果您确定每条记录只有一个JSON，您可以忽略下面查询中的最外层(即SELECT FROM y，只使用查询直到SELECT FROM x(。查询处理Array of JSON的更改。

CREATE TABLE IF NOT EXISTS V2
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ','
LINES TERMINATED BY 'n'
AS 
SELECT 
y.employeeId, CONCAT('[', y.jsonData, ']') AS jsonData
FROM 
(
SELECT 
x.employeeId, collect_list(jsonString) AS jsonData 
FROM (
SELECT
empId,
to_json(named_struct('camel_case', 1, 'employee_id', employeeId, 'manager_id', msgId, 'org_Id', orgId), true) AS jsonString
FROM 
employee
WHERE 
employeeId=101
) x
GROUP BY 
x.employeeId
) y;

输出如下：

101, [{"camelCase" : true, "employeeId" : 101, "managerId" : 201, "orgId" : 301}]

您可以调整此查询，直接使用以下版本将数据放入HDFS：

INSERT OVERWRITE DIRECTORY '/path/of/target/directory/'
ROW FORMAT DELIMITED 
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY ','
LINES TERMINATED BY 'n'AS 
SELECT ...