Snowflake External Tables have been verified to use with Wasabi.
For further reference, please visit the Snowflake Documentation on configuring Wasabi storage.
Prerequisites
Active Wasabi Storage account
A dataset supported by Snowflake uploaded to your Wasabi Bucket
Active Snowflake account
An understanding of Data Lake / Data Warehouse terminology and basic usage of Snowflake
NOTE: At this time, Snowflake must authorize using external s3 urls. You must open a support case with Snowflake ahead of time and ask that the s3 URL for the region your bucket lives can be authorized for your account. You can find the Wasabi S3 Urls here: Wasabi Service URLs article.
1. Upload Data to your Wasabi Bucket
To use Snowflake with Wasabi, you must have a dataset you wish to read from Wasabi. In this KB, we are using sample data generated with Faker. The sample data consists of 1000 files with 1000 rows of fake customer names, addresses, emails, phone numbers, companies, etc...
The data resides in the "snowflake-sample-test " bucket in the "us-east-2" region and a folder of "db1". The path to the data will be "//snowflake-sample-test/db1". You should adjust this accordingly when following along.
2. Create an SQL Worksheet
After logging into your Snowflake account, you want to create a new SQL Worksheet. This will be your scratchpad for working with the data living in Wasabi.
3. Create a Snowflake Database
After you have the Worksheet open, create a Database to store information about the External Table and any related data.
We are going to run the two commands located below. In this KB, use a database called MY_TEST_WASABI_DATABASE. Be sure to adjust this accordingly for your use case.
The first command will create the database. The second will select it for use in further queries in the Worksheet. After copying the commands, select the first statement and click Run.
a Snowflake Stage Object
A stage object must be created in Snowflake to indicate where your data is stored or staged. This is where we create the reference to a Wasabi Bucket, with the Region and Credentials.
Be sure to update the name of your stage, wasabi_east2 in this example, the URL with your bucket name/path, region, and access keys.
Note: This configuration example discusses the use of Wasabi's us-east-2 storage region. To use other Wasabi storage regions, please use the appropriate Wasabi service URL as described in our Wasabi Service URLs article.
CREATE STAGE wasabi_east2
URL = 's3compat://snowflake-sample-test/db1'
ENDPOINT = 's3.us-east-2.wasabisys.com'
REGION = 'us-east-2'
CREDENTIALS = ( AWS_KEY_ID = 'xxx' AWS_SECRET_KEY = 'xxx' )
5. List the data in your Wasabi Account
After your stage has been created successfully, you should be able to list the contents of the files inside your Wasabi Bucket.
We will call the list query on the stage name.
LIST @wasabi_east2;
If you are successful in listing the contents of your Wasabi Bucket, we can then create the Snowflake External Table to query your data. You will need to update the stage name and file format based on your dataset and the stage name you chose.
CREATE EXTERNAL TABLE et
LOCATION=@wasabi_east2/
AUTO_REFRESH = FALSE
REFRESH_ON_CREATE = TRUE
FILE_FORMAT = (TYPE = CSV);
7. Run a Query on your data
After creating your table, you should be all set to run some queries against your data in order to validate it.
SELECT * FROM et;
8. Success
You have created a Snowflake External Table that references data stored on Wasabi.