Hi All,
I'm trying to upload a csv (attached here) with replace into command but it gives me NULL timestamp error with time_parse. Below is the script. can someone please help ?
REPLACE INTO denorm_manf_entitlements_prod OVERWRITE ALL
WITH denorm_manf_entitlements_prod AS (SELECT *
FROM TABLE(
EXTERN(
'{"type":"s3","prefixes":["
s3://hida-data-dev/druid_poc_4/manf_entitlements_new_prod/"]}',
'{"type":"csv","findColumnsFromHeader":true,"listDelimiter":"|"}',
'[{"name":"date_new","type":"string"},
{"name":"row_number","type":"string"},
{"name":"unspsc_code","type":"string"},
{"name":"hida_data_modified","type":"string"},
{"name":"cot","type":"string"},
{"name":"manufacturer_desc","type":"string"},
{"name":"geography_entitlement","type":"string"},
{"name":"data_frequency","type":"string"},
{"name":"role","type":"string"},
{"name":"hida_modified","type":"string"},
{"name":"email_address","type":"string"},
{"name":"hida_data","type":"string"},
{"name":"hida","type":"string"}]'
)
)
)
SELECT
TIME_PARSE(dm.date_new,'yyyyMMdd') AS __time,
--current_timestamp as __time,
dm.email_address,
dm.manufacturer_desc,
dm.role,
dm.geography_entitlement,
dm.data_frequency,
dm.unspsc_code,
dm.cot,
dm.hida_modified,
dm.hida_data_modified,
dm.hida_data,
dm.hida
FROM denorm_manf_entitlements_prod dm PARTITIONED BY YEAR