I need to extract a  parent JSON column present in Hadoop: This is an example of my JSOn.Here I need to extract only values products class but not of offer. I cant use index of product as the the number of occurrences might change in future. Have used the following regex  regexp_replace(regexp_replace(get_json_object(xml_data, '$.customer.products'),'\\}\\,\\{','\\}\\\n\\{'),'\\[|\\]','') AS prd_JSON But is is only giving all values in a row , not in multiple rows. Have also tried something like below, but it doesnt give correct result: regexp_replace(regexp_replace(get_json_object(xml_data, '$.customer.products'),'\\}\\,\\{','\\}\\\n\\{'),'\\[|\\]','') AS prd_JSON from prd_table ) select get_json_object(prd_item,'$.productCode') as productCode, get_json_object(prd_item,'$.type') as Ttype from prd LATERAL VIEW OUTER EXPLODE(split(prd_JSON, ';')) p AS prd_item) a   { "Customer": { "CustomerId": "19900101", "appl": [ { ***Some values } ] "products": [ { "ProductCOde": ABC, "Type": C1, "Offer": 16575.0, "Offer": [ { "ProductCOde": A1, "Offer": 2000.0 "Type": CBA, } { "ProductCOde": A2, "Offer": 2000.0 "Type": DBA, } { "ProductCOde": A3, "Offer": 3000.0 "Type": BBA, } ] } { "ProductCOde": XYZ, "Type": C2, "Offer": 2001, "Offer": [ { "ProductCOde": B1, "Offer": 2000.0 "Type": BBA, } { "ProductCOde": B2, "Offer": 2000.0 "Type": BBA, } ] } ] } }   Can anyone point out what I am doing wrong here   ... View more

I have a table with one column as JSON file. I am using lateral explode to parse values.The code is working fine for classes except for one where subclasses have similar variable name : Json: { "Customer": { "CustomerId": "19900101", "appl": [ { ***Some values } ] "products": [ { "ProductCOde": ABC, "Type": C1, "Offer": 16575.0, "Offer": [ { "ProductCOde": A1, "Offer": 2000.0 "Type": CBA, } { "ProductCOde": A2, "Offer": 2000.0 "Type": DBA, } { "ProductCOde": A3, "Offer": 3000.0 "Type": BBA, } ] } { "ProductCOde": XYZ, "Type": C2, "Offer": 2001, "Offer": [ { "ProductCOde": B1, "Offer": 2000.0 "Type": BBA, } { "ProductCOde": B2, "Offer": 2000.0 "Type": BBA, } ] } ] } }   Code: with product as ( select xml_data, get_json_object(xml_data,'$.customer.customerid')as applicationid, regexp_replace(regexp_replace(get_json_object(xml_data, '$.customer.products'), '\\[|\\]', ''), '\\}\\,\\{', '\\}\\;\\{') AS product_JSOn from Custome_info ) select applicationid, get_json_object(prod_item,'$.ProductCOde') as ProductCOde, get_json_object(prod_item,'$.type') as ttype from product LATERAL VIEW OUTER EXPLODE(split(product_JSOn, ';')) p AS prod_item; Output ApplicationID ProductCode ttype 19900101 ABC NULL 19900101 A1 NULL 19900101 A2 NULL 19900101 B1 NULL   I am not able to figure why values of subclasses are populating here ... View more

I have few tables in hive which I need to access in my SAS platform. I have created a Pass through query present below (marked some paths in *) but it is throwing error.     PROC SQL;     CONNECT TO HADOOP(     host = "bigdataplatform-gcg-&amb..lac.nsroot.net"     schema = &ESQUEMA.     HDFS_TEMPDIR = "/data/&HDFS./work/hive/&HDFS._ofc/"     DBMAX_TEXT = *****     uri = "jdbc:hive2://bigdataplatform-gcg-&amb..lac.nsroot.net:10000/&ESQUEMA.;principal=hive/*****T;ssl=true"      scratch_db = "&HDFS._ofc" subchar=questionmark     );        CREATE TABLE Applicant AS     SELECT     *  FROM CONNECTION TO HADOOP (   WITH json_extract AS (   SELECT      get_json_object(xml_data, '$.app.Id') AS ID,     get_json_object(xml_data, '$.app.apply[0].flag') AS Flag,     get_json_object(xml_data, '$.app.apply[0].Product') AS products   FROM check ) SELECT    ID,   Flag,   get_json_object(product_data, '$.Code') AS Code,   get_json_object(product_data, '$.Line') AS Line,   get_json_object(product_data, '$.status') AS Status FROM json_extract LATERAL VIEW OUTER EXPLODE(SPLIT(products, '},')) p AS product_data; ); disconnect from hadoop; quit;   ... View more