Thanks a lot for your help. I figured out the missing parenthesis location. As for the alias , It is needed for lateral explode to reference the variables. Writing the working code below: %LET ESQUEMA = gcgdlkusg_ecrm_db;
%LET HDFS = hv_u_stg_ral_ecrm;
%LET amb = ut
/*APPlCANT*/
PROC SQL;
CONNECT TO HADOOP(
host = "bigdataplatform-gcg-&amb..lac.nsroot.net"
schema = &ESQUEMA.
HDFS_TEMPDIR = "/data/&HDFS./work/hive/&HDFS._work/"
DBMAX_TEXT = 32768
uri = "jdbc:hive2://bigdataplatform-gcg-&amb..lac.nsroot.net:10000/&ESQUEMA.;principal=hive/bigdataplatform-gcg-&amb..lac.nsroot.net@LAC.NSROOT.NET;ssl=true"
scratch_db = "&HDFS._work"
subchar=questionmark
);
CREATE TABLE Phone AS
SELECT *
FROM CONNECTION TO HADOOP(
with phn as (
select
xml_data, application_number,interface_id,
get_json_object(xml_data,'$.application.applicationid')as applicationid,
cast(get_json_object(xml_data,'$.application.applicants[0].nationality') as INT) as Nationality,
get_json_object(xml_data,'$.application.applicants[0].cin') as cin,
regexp_replace(regexp_replace(get_json_object(xml_data,'$.application.applicants[].phones'), '\\[|\\]', ''), '\\}\\,\\{', '\\}\\;\\{') AS phone_JSON
from &ESQUEMA..ci_rp_t_d
where application_number='2024123' AND INTERFACE_ID=1023
)
select
application_number,
INTERFACE_ID,
applicationid,
nationality,
cin,
get_json_object(phone_item,'$.phoneType') as phonetype,
get_json_object(phone_item,'$.phoneAvailabilityFlag') as phoneAvailabilityFlag
from phn
LATERAL VIEW OUTER EXPLODE(split(phone_JSON, ';')) p AS phone_item
);
/* disconnect from hadoop;*/
quit;
... View more
