I've been using the LIST command to check the files staged to a table in Snowflake. However, the data is unordered and I'd like to order it by last_modified. I tried embedding it into a SELECT query like this:

SELECT *
FROM LIST @MY_DATABASE.MY_SCHEMA.%my_table/path/to/data PATTERN = '.*[.]csv.*'

However, this query fails to compile. I've tried preceding LIST with the CALL keyword as well, but no luck there. I've even tried assigning it to a local variable, but that doesn't work either. The data appears to be tabular so I'm not sure why I can't work with it.

How can I query on the output of LIST?

CodePudding user response：

I am personally using the following "hacky" solution:

1. Start by executing the "list" command.
2. I Then use the result_scan function combined with last_query_id function to fetch the results of that query, as this point I can start querying the data, here's how it looks:

LIST @MY_DATABASE.MY_SCHEMA.%my_table/path/to/data PATTERN = '.*[.]csv.*'

WITH data(name, size, md5, last_modified) as (
     SELECT * FROM table(result_scan(last_query_id()))
)
select *
from data 
order by last_modified desc;

Obviously this is a manual hack as I retrieve the last query id, if you can't ensure this property you need to get the actual query id and use that explicitly instead.