I have some code that loads many files from AWS DMS endpoint, so the set of files is not known. So I currently using the LIST command to enumerate all the files.

The code works from Snowsight, or a TASK, start shown here:

create or replace task REFRESH_ALL_PARQUET_TABLES
schedule='USING CRON 10 17 * * * UTC'
as DECLARE
  id_0 varchar;
BEGIN

  list @STAGE_NAME;
  id_0 := last_query_id();
...

but if I put it in a procedure, I get an error about LIST.

CREATE procedure REFRESH_ALL_PARQUET_TABLES_SP()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS 
DECLARE
  id_0 varchar;
BEGIN

  list @STAGE_NAME;
  id_0 := last_query_id();

  return id_0;
END;

call REFRESH_ALL_PARQUET_TABLES_SP();

Uncaught exception of type 'STATEMENT_ERROR' on line 5 at position 2 : Stored procedure execution error: Unsupported statement type 'LIST_FILES'.

queryid: 01bae284-3204-2ea2-0001-ef8e01ec33f2

the Snowflake Scripting works:

DECLARE
  id_0 varchar;
BEGIN

  list @STAGE_NAME;
  id_0 := last_query_id();

  return id_0;
END;

anyways, I would like to know how to run LIST from a procedure, OR have a procedure AWAIT for a TASK, because I am doing my orchestration from a Task, that calls a procedure, that runs SQL from a table. And if I have to execute the task

execute task REFRESH_ALL_PARQUET_TABLES

I want to have the chain await it also. This is done so decencies occur in the correct order, AND because not all steps run on each time period, I was avoid task chains.

I have some code that loads many files from AWS DMS endpoint, so the set of files is not known. So I currently using the LIST command to enumerate all the files.

The code works from Snowsight, or a TASK, start shown here:

create or replace task REFRESH_ALL_PARQUET_TABLES
schedule='USING CRON 10 17 * * * UTC'
as DECLARE
  id_0 varchar;
BEGIN

  list @STAGE_NAME;
  id_0 := last_query_id();
...

but if I put it in a procedure, I get an error about LIST.

CREATE procedure REFRESH_ALL_PARQUET_TABLES_SP()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS 
DECLARE
  id_0 varchar;
BEGIN

  list @STAGE_NAME;
  id_0 := last_query_id();

  return id_0;
END;

call REFRESH_ALL_PARQUET_TABLES_SP();

Uncaught exception of type 'STATEMENT_ERROR' on line 5 at position 2 : Stored procedure execution error: Unsupported statement type 'LIST_FILES'.

queryid: 01bae284-3204-2ea2-0001-ef8e01ec33f2

the Snowflake Scripting works:

DECLARE
  id_0 varchar;
BEGIN

  list @STAGE_NAME;
  id_0 := last_query_id();

  return id_0;
END;

anyways, I would like to know how to run LIST from a procedure, OR have a procedure AWAIT for a TASK, because I am doing my orchestration from a Task, that calls a procedure, that runs SQL from a table. And if I have to execute the task

execute task REFRESH_ALL_PARQUET_TABLES

I want to have the chain await it also. This is done so decencies occur in the correct order, AND because not all steps run on each time period, I was avoid task chains.

• snowflake-cloud-data-platform

Share Improve this question Follow asked Mar 9 at 4:28 Simeon PilgrimSimeon Pilgrim 26.2k33 gold badges3636 silver badges5050 bronze badges

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 1

The error message indicates that this type of operation is not supported, though it works inside an anonymous block. IMO worth reporting to the support.

In the meantime you can consider using directory tables to list files inside a stage:

CREATE STAGE stage_name DIRECTORY = (ENABLE = TRUE);

CREATE OR REPLACE PROCEDURE REFRESH_ALL_PARQUET_TABLES_SP()
RETURNS VARCHAR
LANGUAGE SQL
EXECUTE AS OWNER
AS 
DECLARE
  id_0 varchar;
BEGIN
  ALTER STAGE stage_name REFRESH;
  SELECT * FROM DIRECTORY(@stage_name);
  id_0 := LAST_QUERY_ID();

  RETURN id_0;
END;


CALL REFRESH_ALL_PARQUET_TABLES_SP();
-- <query_id_here>