1
0
During this post we will discuss about AUTO_REFRESH property on External tables. We know in an external table, the data stores in files in an external stage. External tables are read-only, therefore no DML operations can be perform on them. As per our requirement we receive feed file holds 5M records. This data need not to be ingest in snowflake and will use it for querying purpose only. So instead of consuming this data into Snowflake we have created the External table. Later on map the location of External stage with External table.
However, Down the time we expects the next set of Delta records/file would be uploaded to the External stage. So as per the ask new file needs to be refresh automatically to the External table. Hence, there should not be manual ALTER REFRESH command to be perform. Moreover if we purge the file from the bucket then should reflect on External table count as well. Therefore, To cater this scenario AUTO_REFRESH property comes into the significance.
Firstly, We have bucket:
Secondly, Create the stage:
create or replace stage demo_db.public.ext_csv_stage
URL = 's3://autorefreshexternalbucket/'
STORAGE_INTEGRATION = s3_int
file_format = demo_db.public.csv_format;
Thirdly, Create External Table:
create or replace external table colors
(Color_Code varchar(10) as (value:c1::varchar),
Color varchar(20) as ( value:c2::varchar),
color_Type varchar(10) as (value:c3::varchar))
with location = @demo_db.public.ext_csv_stage
auto_refresh = true
file_format = csv_format;
show external tables;
Finally, Configure this notification value on Bucket the same way we do while Snowpipe configure.
EXTERNAL_TABLE_FILES:
This table function can be used to query information about the staged data files included in the metadata for a specified external table.
EXTERNAL_TABLE_FILE_REGISTRATION_HISTORY: This table function can be used to query information about the metadata history for an external table, including:
Files added or removed automatically as part of a metadata refresh.
Now upload the new file, say color_file1.csv to the bucket.
Therefore, An SQS notification would trigger and count of the External Table would be updated. No manual intervention is require and changes reflect automatically.
Metadata andRegistration:
Verify the metadata and registration of new file in External table.
Now we will purge the file from the Bucket and will observe the behaviour on External table.
We can verify the last perform action by the SQS on file delete.
Now verify the metadata and registration of purge file in External table.
Verify the latest count.
