-- Insert backend realtime event data from S3 stage into BK_REALTIME_EVENT table
-- This query reads JSON files from the specified file path and inserts them into the table
INSERT INTO {bk_realtime_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;

