How to use copy Storage Integration in a Snowflake task statement?

Question

I'm testing SnowFlake. To do this I created an instance of SnowFlake on GCP.

One of the tests is to try the daily load of data from a STORAGE INTEGRATION.

To do that I had generated the STORAGE INTEGRATION and the stage.

I tested the copy

copy into DEMO_DB.PUBLIC.DATA_BY_REGION from @sg_gcs_covid pattern='.*data_by_region.*'

and all goes fine.

Now it's time to test the daily scheduling with the task statement. I created this task:

CREATE TASK schedule_regioni
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = 'USING CRON 42 18 9 9 * Europe/Rome'
  COMMENT = 'Test Schedule'
AS
  copy into DEMO_DB.PUBLIC.DATA_BY_REGION from @sg_gcs_covid pattern='.*data_by_region.*';

And I enabled it:

alter task schedule_regioni resume;

I got no errors, but the task don't loads data. To resolve the issue i had to put the copy in a stored procedure and insert the call of the storede procedure instead of the copy:

DROP TASK schedule_regioni;
CREATE TASK schedule_regioni
  WAREHOUSE = COMPUTE_WH
  SCHEDULE = 'USING CRON 42 18 9 9 * Europe/Rome'
  COMMENT = 'Test Schedule'
AS
  call sp_upload_c19_regioni();

The question is: this is a desired behavior or an issue (as I suppose)?

Someone can give to me some information about this?

You shouldn't need the SP. I think you have a permission issue somewhere. Do you have the output of the task when it is executed directly with the COPY INTO, rather than the SP? — Mike Walton

Saul Bonardi Saul Bonardi · Accepted Answer · 2020-09-10T19:22:00

I've just tried ( but with storage integration and stage on AWS S3) and it works fine also using copy command inside sql part of the task, without calling a stored procedure. In order to start investigating the issue, I would check following info (maybe for debugging I would create the task scheduling it every few minutes):

check task_history and verify executions

select *
   from table(information_schema.task_history(
   scheduled_time_range_start=>dateadd('hour',-1,current_timestamp()),
   result_limit => 100,
   task_name=>'YOUR_TASK_NAME'));

if previous step is successfull, check copy_history and verify the input file name , target table and number of records/errors are the expected ones

SELECT *
FROM TABLE (information_schema.copy_history(TABLE_NAME => 'YOUR_TABLE_NAME',
            start_time=> dateadd(hours, -1, current_timestamp())))
ORDER BY 3 DESC;

Check if the results are the same you get when the task with sp call is executed.

Please also confirm that you are loading new files not yet loaded into your table with COPY command (otherwise you need to specify FORCE = TRUE parameter in the copy command or remove metadata information truncating your target table to reload the same files).

How to use copy Storage Integration in a Snowflake task statement?

1 Answers