Loading data from a remote database into Snowflake ….. without ETL tools
Inspired by this article I decided this should be tested: -> a direct query from within Snowflake to a remote database.
Why would this be interesting: with this, there is no need to use separate tooling for source extraction. That will certainly save some euro’s !!!
For this setup, I created a MySql database online at https://aiven.io/. Works really easy:
After that, I used DBeaver to create a table and insert some data:
Next, go to your Snowflake environment and use this repo to load a Snowpark script: https://gitlab.com/samuel.ehrlich/federated_queries. It contains:
- a MySql driver to upload to a Snowflake stage
- a Snowpark python script to create the required components in Snowflake, such as a network rule and an external access integration
It works really easy: enter stuff like the hostname, user, password, port and the procedure will create these components for you in Snowflake:
Then, execute the next cell (call load_to_table) and Snowflake will actually access MySql and load the data into a Snowflake table:
This is pretty cool !!! Will save you time & money in setting up your Extract step in your datawarehouse.
When testing this yourself: it will not work on a trial account, as trial accounts do not support external access integrations. That part is not so cool :)