-- Insert backend non-realtime event data from S3 stage into BK_NONREALTIME_EVENT table
-- This query reads JSON files from the specified file path and inserts them into the table
INSERT INTO {bk_nonrealtime_event_table_name}(
    NAME,
    TIMESTAMP,
    SOURCE,
    USER_ID,
    SESSION_ID,
    PROPERTIES,
    P_DATE,
    P_HOUR
)
WITH event_data AS (
    SELECT 
        *
    FROM {file_path} (file_format => 'BASIC_JSON_LOADER', pattern => ('.*'))
)
select
    event_data.$1:name as NAME,
    event_data.$1:timestamp::TIMESTAMP as TIMESTAMP,
    event_data.$1:source as SOURCE,
    TRY_CAST(event_data.$1:user_id::string as number) as USER_ID,
    IFF(event_data.$1:session_id='None', null, event_data.$1:session_id) AS SESSION_ID,
    parse_json(event_data.$1:properties) as PROPERTIES,
    '{partition_date}'::DATE as P_DATE,
    {partition_hour} as P_HOUR
from event_data;