Snowflake Query/task gets canceled cause of timelimit

Viewed 2938

I set up a code, to load Data from Google Analytics from a raw GA table to an adapted table, that offers more insights. Somehow the Task is not running inside the defined time limit of 3600 seconds. So the task is getting canceled and no data is loaded.

"Statement reached its statement or warehouse timeout of 3,600 second(s) and was canceled."

Then I load it in a manual way by using a fixed Clause: WHERE gae."DAY"='2020-03-31' instead of WHERE gae."DAY">=CuRRENT_DATE-1. It takes still much time but works in the end.

How can I make this query faster? Or anyway, solve my problem.

CREATE OR REPLACE TASK dm."Website"."x009_002_all_GA_events"
        WAREHOUSE = marketing_wh
        SCHEDULE = 'USING CRON 26 5 * * * Europe/Berlin'
        AS
    merge into DM."Website".ALL_GA_EVENTS_CLEAN target --DM."Website".ALL_GA_EVENTS target
    using (
    SELECT
    GAEVENTACTION AS GAEVENTACTION,
    GAEVENTCATEGORY AS GAEVENTCATEGORY,
    "DAY" AS Datum,
    DEVICE_TYPE AS Device,
    EVENT_COUNT AS EventCount,
    GAUNIQUEEVENTS AS Uniqueevents,
    EVENT_VALUE AS eventvalue,
    LABELS AS labelz,
    URL AS urlz,
    --split_part(LABELS,'/_',2) AS "HITSTAMP",
    CASE WHEN split_part(LABELS,'/_',2) IS NOT NULL THEN TRY_CAST(split_part(LABELS,'/_',2) AS timestamp) ELSE NULL END AS "HITSTAMP",
    split_part(LABELS,'/_',3) AS EVENT_INFO,
    split_part(LABELS,'/_',1) AS "SESSIONID",
    CASE
        WHEN CONTAINS (URL, '/checkout/')=TRUE THEN split_part(URL,'/',3)
        WHEN CONTAINS (URL, '/auto/')=TRUE THEN split_part(split_part(URL,'/',3),'?',1)
        WHEN CONTAINS (URL, '/angebote/')=TRUE THEN split_part(URL,'/',3)
        ELSE 'no vehicle'
    END AS vehicleID,
    rank() over (partition BY "SESSIONID" order by "HITSTAMP") as "RANK",
    CASE 
        WHEN (GAEVENTACTION= ('pdp_flash_offer_request' )) THEN 
            CASE WHEN split_part(LABELS,'/_',2)=(SELECT min(split_part(t2.LABELS,'/_',2))
                FROM "DL_Datatap"."PUBLIC"."GA_all_events" t2
                WHERE split_part(t2.LABELS,'/_',1)=split_part(gae.LABELS,'/_',1) AND gae.GAEVENTACTION=t2.GAEVENTACTION)
            THEN TRUE
            ELSE FALSE
            END
        WHEN (GAEVENTACTION= ('chat_started')) THEN 
            CASE WHEN split_part(LABELS,'/_',2)=(SELECT min(split_part(t2.LABELS,'/_',2))
                FROM "DL_Datatap"."PUBLIC"."GA_all_events" t2
                WHERE split_part(t2.LABELS,'/_',1)=split_part(gae.LABELS,'/_',1) AND gae.GAEVENTACTION=t2.GAEVENTACTION)
            THEN TRUE
            ELSE FALSE  
            END
        WHEN (GAEVENTACTION= ('Direct_checkout_send')) THEN 
            CASE WHEN split_part(LABELS,'/_',2)=(SELECT min(split_part(t2.LABELS,'/_',2))
                FROM "DL_Datatap"."PUBLIC"."GA_all_events" t2
                WHERE split_part(t2.LABELS,'/_',1)=split_part(gae.LABELS,'/_',1) AND gae.GAEVENTACTION=t2.GAEVENTACTION)
            THEN TRUE
            ELSE FALSE
            END
        WHEN (GAEVENTACTION= ('pdp_offer_request')) THEN 
            CASE WHEN split_part(LABELS,'/_',2)=(SELECT min(split_part(t2.LABELS,'/_',2))
                FROM "DL_Datatap"."PUBLIC"."GA_all_events" t2
                WHERE split_part(t2.LABELS,'/_',1)=split_part(gae.LABELS,'/_',1) AND gae.GAEVENTACTION=t2.GAEVENTACTION)
            THEN TRUE
            ELSE FALSE
            END
        WHEN (GAEVENTACTION= ('agent-requested')) THEN 
            CASE WHEN split_part(LABELS,'/_',2)=(SELECT min(split_part(t2.LABELS,'/_',2))
                FROM "DL_Datatap"."PUBLIC"."GA_all_events" t2
                WHERE split_part(t2.LABELS,'/_',1)=split_part(gae.LABELS,'/_',1) AND gae.GAEVENTACTION=t2.GAEVENTACTION)
            THEN TRUE
            ELSE FALSE 
            END
        WHEN (GAEVENTACTION= ('SERP_softlead_send')) THEN 
            CASE WHEN split_part(LABELS,'/_',2)=(SELECT min(split_part(t2.LABELS,'/_',2))
                FROM "DL_Datatap"."PUBLIC"."GA_all_events" t2
                WHERE split_part(t2.LABELS,'/_',1)=split_part(gae.LABELS,'/_',1) AND gae.GAEVENTACTION=t2.GAEVENTACTION)
            THEN TRUE
            ELSE FALSE
            END
    ELSE False
    END AS "GOAL_EVENT"
    FROM "DL_Datatap"."PUBLIC"."GA_all_events" gae
    ---WHERE gae."DAY"='2020-03-31'
    WHERE gae."DAY">=CuRRENT_DATE-1 
    ) SOURCE
    ON target.SESSIONID=SOURCE."SESSIONID" AND target.HITSTAMP=SOURCE."HITSTAMP" AND target.EVENT_ACTION=SOURCE.GAEVENTACTION AND target."Date"=SOURCE.Datum
    when NOT matched then INSERT (EVENT_ACTION, EVENT_CATEGORY, "Date", DEVICE, TOTAL_EVENTS, UNIQUE_EVENTS, EVENT_VALUE, EVENT_LABEL, URL, HITSTAMP, EVENT_INFO, SESSIONID, VEHICLEID, EVENT_SEQUENCE, GOAL_EVENT)
    VALUES (SOURCE.GAEVENTACTION, SOURCE.GAEVENTCATEGORY, SOURCE.Datum, SOURCE.Device, SOURCE.eventcount, SOURCE.Uniqueevents, SOURCE. eventvalue, SOURCE.labelz, SOURCE.urlz, SOURCE."HITSTAMP", SOURCE.EVENT_INFO, SOURCE."SESSIONID", SOURCE.vehicleid, SOURCE."RANK", SOURCE."GOAL_EVENT" 
    )
1 Answers

You can increase the task timeout limit as a workaround:

CREATE OR REPLACE TASK dm."Website"."x009_002_all_GA_events"
WAREHOUSE = marketing_wh
SCHEDULE = 'USING CRON 26 5 * * * Europe/Berlin'
USER_TASK_TIMEOUT_MS = 86400000
AS
...

https://docs.snowflake.com/en/sql-reference/sql/create-task.html#optional-parameters

If you need help to tune your SQL, please submit a case to support. As they can see metadata of your tables, and execution plans of your previous runs, they can guide you to tune the query, cluster your target table etc..

Related