6
0
In continuation of previous post about the External tables, where we were supposed to refresh the tables explicitly to reflect the metadata changes about the new file. However, External table refresh manually is not an ideal solution in production where we saw the files are coming frequently either single time or multiple specified times on daily basis.
ALTER EXTERNAL TABLE Parque_User_Data refresh;
Snowflake provide the way to refresh the external table metadata automatically using Amazon SQS (Simple Queue Service)notifications for an S3 bucket.
- SQS notifications notify Snowflake when new files arrive in monitored S3 buckets and are ready to load. SQS notifications contain the S3 event and a list of the file names. They do not include the actual data in the files.
- This feature is limits to Snowflake accounts on AWS.
- Moreover, Notification handles all external tables configured at a more granular level in the S3 bucket directory
Hence, Once the SQS notifications are configured on the bucket, they notify the Snowflake once the File uploads to the Bucket and metadata gets refreshed automatically with out any Manual intervention.
Steps to Configure S3 Event Notification:
- Firstly, Create a Stage.
- Secondly, Create an External Table.
- Finally, Configure the Event Notification.
Note: Snowflake designates no more than one SQS queue per S3 bucket. This SQS queue may be shared among multiple buckets in the same AWS account.
SHOW EXTERNAL TABLES
Note the ARN of the SQS queue for the external table in the notification_channel column.
To enable and configure event notifications for an S3 bucket:
1. In the Buckets list, choose the name of the bucket that you want to enable events for.
2. Choose Properties.
3. Navigate to the Event Notifications section and choose Create event notification.
Create a single event notification that monitors activity for the entire S3 bucket. This is the simplest approach.
Now upload the new file to the Bucket:
There is no need to refresh explicitly and because of the configuration of S3 event, Files metadata updated automatically.
