1.      DROP TABLE IF EXISTS mdc_appdev_lifeflux.mdc_flx_events_procedures_icd;
2.  
3.        CREATE TABLE mdc_appdev_lifeflux.mdc_flx_events_procedures_icd
4.        PARTITIONED BY (internalpatientid)
5.        STORED AS PARQUET
6.        AS
7.         select insertDate, dateOfEvent, eventXML, internalpatientid
8.         from (
9.         SELECT
10.                             now() insertDate,
11.                             procedures_icd.startdate dateOfEvent,
12.                             CONCAT
13.                             (
14.                             '<Ev ',
15.                                 'Type="',  'procedures_icd', '" ',
16.                                 'Alias="','procedures_icd', '" ',
17.                                 'Age="', cast(procedures_icd.ageatevent as varchar(23)), '" ',
18.                                 'Time="', cast(procedures_icd.startdate as varchar(23)), '">',
19.                                 '<Pr Name="ageatevent" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.ageatevent as string),'Null'), '</Pr>','<Pr Name="servicecode" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.servicecode as string),'Null'), '</Pr>','<Pr Name="insert_date" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.insert_date as string),'Null'), '</Pr>','<Pr Name="internalpatientid" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.internalpatientid as string),'Null'), '</Pr>','<Pr Name="startdate" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.startdate as string),'Null'), '</Pr>','<Pr Name="sequencenumber" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.sequencenumber as string),'Null'), '</Pr>','<Pr Name="servicecodedesc" Hier="','No Hierarchy','">',isnull(cast(procedures_icd.servicecodedesc as string),'Null'), '</Pr>',
20.                             '</Ev>'
21.                             ) eventXML,
22.                             procedures_icd.internalpatientid internalpatientId
23.                         FROM
24.                             mimic_db.procedures_icd INNER JOIN mimic_db.demographics_static
25.                             ON (mimic_db.procedures_icd.internalpatientid=mimic_db.demographics_static.internalpatientid)    
26.         ) tbl;