Refreshing external tables automatically for Amazon S3

Published: None

Source: https://www.linkedin.com/pulse/refreshing-external-tables-automatically-amazon-s3-arabinda-mohapatra-e52hc?trackingId=XFuzrPv8SFqHvSecoJC58A%3D%3D

---

Refreshing external tables automatically for Amazon S3

Arabinda Mohapatra

Running Kafka streams after dark, diving into genetic code by daylight, and wrestling with Databricks and Tableflow in every spare moment—sleep is optional

August 4, 2024

Step-1

Code:

drop database if exists s3_to_snowflake;

Step-2; --Database Creation

create database if not exists s3_to_snowflake;

Step-3:

Specify the active/current database for the session.

use s3_to_snowflake;

Step-4:

Specify the role

use role ACCOUNTADMIN;

Step-5:

Storage Integration Creation

create or replace storage integration s3_int

TYPE = EXTERNAL_STAGE

STORAGE_PROVIDER = S3

ENABLED = TRUE

STORAGE_AWS_ROLE_ARN = '{}'

STORAGE_ALLOWED_LOCATIONS = ('s3://{}')

COMMENT = 'Testing Snowflake getting refresh or not';

Step-6:

Describe the Integration Object

DESC INTEGRATION  s3_int;

Step-7:

External Stage Creation

create stage mystage

url = 's3://{}'

storage_integration = s3_int;

Step-8:

list @mystage;

Step-9:

File Format Creation

create or replace file format my_csv_format

type = csv field_delimiter = ',' skip_header = 1

field_optionally_enclosed_by = '"'

null_if = ('NULL', 'null')

empty_field_as_null = true;

Step-10:

Table Creation

create or replace external table s3_to_snowflake.PUBLIC.Iris_dataset (Id number(10,0) as (Value:c1::int),sepal_length number(10,5) as  (Value:c2::number(10,5)),

sepal_width number(10,4) as (Value:c3::number(10,4)),petal_length number(10,3) as (Value:c4::number(10,3)),

petal_width number(10,4) as (Value:c5::number(10,4)), CLASS_NAME varchar(20) as (Value:c6::varchar)) with location = @mystage file_format ='my_csv_format';

Step-11:

show external tables;

select * from s3_to_snowflake.PUBLIC.Iris_dataset;

alter external table s3_to_snowflake.PUBLIC.Iris_dataset refresh;

Refer- https://docs.snowflake.com/en/user-guide/tables-external-s3