with unexpandRaw as(
select
tool_id,recipe_id,version,recipe_level,rev_state,last_modified_time,
replace(replace(regexp_extract(RECIPE_PARAMETER,’(“CONTROL_FLAG”:[).?(])’,0),’“CONTROL_FLAG”:[’,’’),’]’,’’) as CONTROL_FLAG,
replace(replace(regexp_extract(RECIPE_PARAMETER,’(“PARAMETER_NAME”:[).?(])’,0),’“PARAMETER_NAME”:[’,’’),’]’,’’) as PARAMETER_NAME,
replace(replace(regexp_extract(RECIPE_PARAMETER,’(“CHECK_METHOD”:[).?(])’,0),’“CHECK_METHOD”:[’,’’),’]’,’’) as CHECK_METHOD,
replace(replace(regexp_extract(RECIPE_PARAMETER,’(“PARAMETER_TYPE”:[).?(])’,0),’“PARAMETER_TYPE”:[’,’’),’]’,’’) as PARAMETER_TYPE,
replace(replace(regexp_extract(RECIPE_PARAMETER,’(“PARAMETER_DESC”:[).?(])’,0),’“PARAMETER_DESC”:[’,’’),’]’,’’) as PARAMETER_DESC,
replace(replace(regexp_extract(RECIPE_PARAMETER,’(“PARAMETER_STEP”:[).?(])’,0),’“PARAMETER_STEP”:[’,’’),’]’,’’) as PARAMETER_STEP,
replace(replace(regexp_extract(RECIPE_PARAMETER,’(“PARAMETER_MIN_VALUE”:[).?(])’,0),’“PARAMETER_MIN_VALUE”:[’,’’),’]’,’’) as PARAMETER_MIN_VALUE,
replace(replace(regexp_extract(RECIPE_PARAMETER,’(“PARAMETER_MAX_VALUE”:[).?(])’,0),’“PARAMETER_MAX_VALUE”:[’,’’),’]’,’’) as PARAMETER_MAX_VALUE,
replace(replace(regexp_extract(RECIPE_PARAMETER,’(“PARAMETER_ACTUAL_VALUE”:[).*?(])’,0),’“PARAMETER_ACTUAL_VALUE”:[’,’’),’]’,’’) as PARAMETER_ACTUAL_VALUE
from ees_rms_tool_recipe_version_raw limit 10
)select tool_id,recipe_id,version,recipe_level,rev_state,last_modified_time,
s_CONTROL_FLAG,s_PARAMETER_NAME–,s_CHECK_METHOD,s_PARAMETER_TYPE,s_PARAMETER_DESC,
–s_PARAMETER_STEP,s_PARAMETER_MIN_VALUE,s_PARAMETER_MAX_VALUE,s_PARAMETER_ACTUAL_VALUE
from unexpandRaw
lateral view posexplode(split(CONTROL_FLAG,’,’)) t as s_aa_index,s_CONTROL_FLAG
lateral view posexplode(split(PARAMETER_NAME,’,’)) t as s_bb_index,s_PARAMETER_NAME
–lateral view posexplode(split(CHECK_METHOD,’,’)) t as s_cc_index,s_CHECK_METHOD
–lateral view posexplode(split(PARAMETER_TYPE,’,’)) t as s_dd_index,s_PARAMETER_TYPE
–lateral view posexplode(split(PARAMETER_DESC,’,’)) t as s_ee_index,s_PARAMETER_DESC
–lateral view posexplode(split(PARAMETER_STEP,’,’)) t as s_ff_index,s_PARAMETER_STEP
–lateral view posexplode(split(PARAMETER_MIN_VALUE,’,’)) t as s_gg_index,s_PARAMETER_MIN_VALUE
–lateral view posexplode(split(PARAMETER_MAX_VALUE,’,’)) t as s_hh_index,s_PARAMETER_MAX_VALUE
–lateral view posexplode(split(PARAMETER_ACTUAL_VALUE,’,’)) t as s_ii_index,s_PARAMETER_ACTUAL_VALUE
where s_aa_index=s_bb_index
–and s_bb_index=s_cc_index
–and s_cc_index=s_dd_index
–and s_dd_index=s_ee_index
–and s_ee_index=s_ff_index
–and s_ff_index=s_gg_index
–and s_gg_index=s_hh_index
–and s_hh_index=s_ii_index
