Snowflake (https://www.snowflake.com/) is a Large Data Solution for data warehousing, data lakes, data engineering, data science, data application development, and for securely sharing and consuming shared data.
Linx, naturally, would be a great tool to load data into Snowflake, and there are a couple of different ways to do this. I find the easiest way is to use the provided ODBC driver with Linx and access the data via SQL.
In order to test a connection with Snowflake I did the following:
Snowflake Setup
-
I registered for Snowflake on https://www.snowflake.com/
-
Got a new Snowflake instance i.e. : https://xxxxxxx.west-europe.azure.snowflakecomputing.com/
-
I created a Database called LinxTest
-
I created a Table called BIGDATA with some fields (ID and Value)
-
I created a Stage called TestStage
-
Created a File Format BDLOAD
-
Then (this was the part that took the longest to figure out, I had to Grant Privileges to all of these to be able to use them.)
Database Driver Setup
-
The easiest way to upload files from a local drive, using Linx, is with the ODBC drivers from Snowflake (Downloading Snowflake Clients, Connectors, Drivers, and Libraries | Snowflake Documentation)
-
After installing the driver, run the Windows ODBC Data Source Administrator and set the settings like this (This part can be done using a Connection String as well if you don’t want to set up a DSN):
Using Snowflake in Linx:
- In Linx, I set up a Connection string to the new DSN created above called LinxTest:
- To read details from Snowflake I can then use normal SQL like:
select ID, Value from Bigdata
- Can upload a local CSV file:
PUT file://C:\temp\datatest.csv @TESTSTAGE/ui1607004130056
- And import the CSV into the Database:
COPY INTO “LINXTEST”.“PUBLIC”.“BIGDATA” FROM @“LINXTEST”.“PUBLIC”.“TESTSTAGE” FILE_FORMAT = ( FORMAT_NAME = “LINXTEST”.“PUBLIC”.“BDLOAD” )