CODEWITHSUNDEEP
snowflake-cloud-data-platform
kushagraagarwal
kushagraagarwal

Reputation: 103

Not able to get the load history from INFORMATION_SCHEMA.COPY_HISTORY

I have a Snowflake table which gets its data (via COPY INTO) from an S3 bucket. When I tried to run the below statement to check the load status, it didn't give any result.

SELECT * FROM TABLE(INFORMATION_SCHEMA.COPY_HISTORY(TABLE_NAME=>'HourlyTransactionStaging', START_TIME=> DATEADD(DAY, -14, CURRENT_TIMESTAMP())));

Instead, I got this error

Table DBNAME.STAGING.HOURLYTRANSACTIONSTAGING did not exist or was purged.

However, when I tried to run this, it ran and gave me the results as well.

select * from information_schema.load_history
Where
    Schema_name = 'STAGING'
    AND TABLE_NAME = 'HOURLYTRANSACTIONSTAGING';

Upvotes: 0

Views: 1126

Answers (2)

Simon D
Simon D

Reputation: 6229

Glad you figured it out. Also you need to make sure that you're on the correct database / schema before running the query as below: 

use schema your_db.schema;

select *
from table(information_schema.copy_history(table_name=>'table_name', start_time=> dateadd(hours, -1, current_timestamp())));

Upvotes: 0

kushagraagarwal
kushagraagarwal

Reputation: 103

I figured out what the issue was. Apparently, TABLE_NAME parameter in the COPY_HISTORY function is case sensitive and I was providing the table name as per the conventions.

HourlyTransactionStaging --> HOURLYTRANSACTIONSTAGING

Upvotes: 1

Related Questions