Copy snowflake task results into stage and download to csv

Question

Basically I need to automate all of the below in a snowflake TASK

Create/replace a csv file format and stage in Snowflake
Run task query (which runs every few days to pulls some stats)
Unload the query results each time it runs into the Stage csv
Download the contents of the stage csv to a local file on my machine

What I can't get right is the COPY INTO stage, how do I unload the results of the task each time it is run, into the stage?

I don't know what to put in the FROM statement - TITANLOADSUCCESSVSFAIL is not recognized but this is the name of the TASK

COPY INTO @TitanLoadStage/unload/ FROM TITANLOADSUCCESSVSFAIL FILE_FORMAT = TitanLoadSevenDays

First time using stage, and downloading locally with SF so appreciate any advice on how to get this up and running!

Thanks, Nick

Full Code:


-- create a csv file format 
CREATE OR REPLACE FILE FORMAT TitanLoadSevenDays
    type = 'CSV'
    field_delimiter = '|';

--create a snowflake staging table using the csv 
CREATE OR REPLACE STAGE TitanLoadStage
file_format = TitanLoadSevenDays;   
    
    
CREATE TASK IF NOT EXISTS TitanLoadSuccessVsFail
    WAREHOUSE = ITSM_LWH
     SCHEDULE = 'USING CRON 1 * * * * Australia/Canberra' --every minute for testing purposes 
     COMMENT = 'Last 7 days of Titan game success vs fail load %'
AS
    WITH    SUCCESSCTE AS (
SELECT  CLIENTNAME
,       COUNT(EVENTTYPE) AS SuccessLoad --count success load events for that game 
FROM    vw_fact_gameload60
WHERE   EVENTTYPE = 103 --success load events
    AND     USERTYPE = 1 --real users
    AND     APPID = 2 --titan games
    AND     EVENTARRIVALDATE >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE)) --only looking at the last week  
GROUP BY CLIENTNAME
),

        FAILCTE AS ( --same as above but for failed loads
SELECT  CLIENTNAME
,       COUNT(EVENTTYPE) AS FailedLoads -- count failed load events for that game
FROM    vw_fact_gameload60
WHERE   EVENTTYPE = 106 -- failed load events 
    AND     USERTYPE = 1 -- real users 
    AND     APPID = 2 -- Titan games
    AND     EVENTARRIVALDATE >= DATEADD(DAY, -7, CAST(GETDATE() AS DATE)) -- last 7 days 
  --AND     FACTEVENTARRIVALDATE BETWEEN DATEADD(DAY, -7, GETDATE())AND GETDATE() -- last 7 days 
GROUP BY CLIENTNAME
)
SELECT  COALESCE(s.CLIENTNAME, f.CLIENTNAME) AS ClientName
,       ZEROIFNULL(s.SuccessLoad) + ZEROIFNULL(f.FailedLoads) AS TotalLoads --sum the success and failed loads found for 103, 106 events only, calculated in CTEs
,       ZEROIFNULL(s.SuccessLoad) AS Cnt_SuccessLoad --count from success cte
,       ZEROIFNULL(f.FailedLoads) AS Cnt_FailedLoads --count from fail cte
,       CONCAT(ZEROIFNULL(ROUND(s.SuccessLoad * 100.0 / TotalLoads,2)) , '%') As Pct_Success --percentage of SuccessLoads against total
,       CONCAT(ZEROIFNULL(ROUND(f.FailedLoads * 100.0 / TotalLoads,2)), '%') AS Pct_Fail---percentage of failedLoads against total
FROM    SUCCESSCTE s 
FULL OUTER JOIN FAILCTE f -- outer join in the fail CTE by game name, outer required because some titan games sucess or fail events are NULL  
            ON  s.CLIENTNAME = f.Clientname
ORDER BY CLIENTNAME ASC



--copy the results from the query to the snowflake staging table created above 
COPY INTO @TitanLoadStage/unload/ FROM TITANLOADSUCCESSVSFAIL FILE_FORMAT = TitanLoadSevenDays


-- export the stage data to csv located in common folder 
GET @TitanLoadStage/unload/data_0_0_0.csv.gz file:\\itsm\group\ITS%20Management\Common\All%20Staff\SMD\Games\Snowflake%20and%20GamesDNA\Snowflake\SnowflakeCSV\TitanLoad.csv 


-- start the task 
ALTER TASK IF EXISTS TitanLoadSuccessVsFail RESUME

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2021-02-19T07:07:03

If you want to get the results of a query ran through a task, you need to materialize the results of said query to a table.

What you have now:

CREATE TASK mytask_minute
  WAREHOUSE = mywh
  SCHEDULE = '5 MINUTE'
AS
SELECT 1 x;

COPY INTO @TitanLoadStage/unload/
FROM mytask_minute;

(mytask_minute is not a table, so you can't select from it)

What you should do instead:

CREATE TASK mytask_minute
  WAREHOUSE = mywh
  SCHEDULE = '5 MINUTE'
AS
CREATE OR REPLACE TABLE task_results_table
AS
SELECT 1 x;

COPY INTO @TitanLoadStage/unload/
SELECT *
FROM task_results_table;

Copy snowflake task results into stage and download to csv

1 Answers