Snowflake External tables allow you to access files stored in external stage as a regular table. You can join the Snowflake external table with permanent or managed table to get required information or perform the complex transformations involving various tables. The External tables are commonly used to build the data lake where you access the raw data which is stored in the form of file and perform join with existing tables.
Snowflake External Tables
As mentioned earlier, external tables access the files stored in external stage area such as Amazon S3, GCP bucket, or Azure blob storage.
You can create a new external table in the current/specified schema. You can also replace an existing external table. When queried, an external table reads data from a set of one or more files in a specified external stage and outputs the data in a single VARIANT (JSON) column. The VARIANT column name would be VALUE. You write the expression to extract the values from VALUE fields.
DML on the Snowflake External Table
External tables are read-only, therefore no DML operations can be performed on them. However, you can use external tables for query and join operations. Views can be created against external tables.
Querying data stored external to the Snowflake database is likely to be slower than querying native database tables; however, materialized views based on external tables can improve query performance. Note that, materialized views are available starting from Snowflake enterprise version.
Steps to Create Snowflake External Table
You can follow the below steps to create external tables on Cloud data warehouse.
- Create File Format
- Create External Stage for External Storage (S3, GCP bucket, Azure Blob)
- Define or Create External Table using external stage location
Note that, for simplicity, we are going to use Amazon S3 as an external Stage.
We have already uploaded following CSV file to S3 bucket.
ID,NAME,DEPT 1234567890,aaa,32114 0987654123,bbb,45789 1122445521,ccc,75896 7845951656,ddd,45795 5645798965,xyz,45564 4547858551,vvd,84654
Define or Create File format
The first step is to create file format.
For example, we have CSV file so we have to create a CSV file format. Note that, you can directly mention the file format on STAGE object, but, it is always best practice to create file format.
Following is the file format example.
create or replace file format mys3csv type = 'CSV' field_delimiter = ',' skip_header = 1;
Create External Stage Object (S3 Stage)
Secondly, create an external stage for Amazon S3 bucket.
For example, consider following example.
create or replace stage MYS3STAGE url='s3://snow-testing/testfiles/' CREDENTIALS=(aws_key_id='ABCDRHDKSNF' aws_secret_key='***********') file_format = mys3csv;
Create Snowflake External Table
The Third step would be to create an external table by providing external stage as a location.
There are two types of external tables that you can create.
- External Table without Column Names
- External Tables with Column Names
Snowflake External Table without Column Details
Following example allow you to create an external table without a column Name.
create or replace external table sample_ext with location = @mys3stage file_format = mys3csv;
Now, query the external table. Note that, we have derived the column names from the VALUE VARIANT column.
select value:c1::int as ID, value:c2::varchar as name , value:c3::int as dept from sample_ext; +------------+------+-------+ | ID | NAME | DEPT | |------------+------+-------| | 1234567890 | aaa | 32114 | | 987654123 | bbb | 45789 | | 1122445521 | ccc | 75896 | | 7845951656 | ddd | 45795 | | 5645798965 | xyz | 45564 | | 4547858551 | vvd | 84654 | +------------+------+-------+
Snowflake External Tables with Column Names
Create a column expression on VALUE json object.
For example, consider following
create or replace external table sample_ext (ID INT as (value:c1::int), Name varchar(20) as ( value:c2::varchar), dept int as (value:c3::int)) with location = @mys3stage file_format = mys3csv;
Check the records
select id, name, dept from sample_ext;
+------------+------+-------+ | ID | NAME | DEPT | |------------+------+-------|
| 1234567890 | aaa | 32114 |
| 987654123 | bbb | 45789 |
| 1122445521 | ccc | 75896 |
| 7845951656 | ddd | 45795 |
| 5645798965 | xyz | 45564 |
| 4547858551 | vvd | 84654 |