snowflake_sql.sql

copy into test_semi_structured
  from @CDC.PUBLIC.STAGE/
  file_format = (type = json STRIP_OUTER_ARRAY = TRUE);
  


select 
var:tran_begin_time::timestamp as tran_begin_time,
var:tran_end_time::timestamp as tran_end_time,
var:__$start_lsn::string as start_lsn,
var:__$_lsn::string as end_lsn,
to_number(replace(var:__$seqval::string, '0x'),'XXXXXXXXXXXXXXXXXXXXXXXXXXX') as seqval,
var:__$operation::numeric as __$operation,
var:__$update_mask::string as update_mask,
var:ID::numeric as ID,
var:COL1::string as COL1,
var:COL2::string as COL2,
var:COL3::string as COL3,
var:COL4::string as COL4,
var:COL5::string as COL5,
var:StartTime::timestamp as StartTime,
var:EndTime::timestamp as EndTime,
var:__$command_id::numeric as __$operation
from test_semi_structured as src;