Snowflake
The Ververica Snowflake connector described in this guide serves as a sink-only connector, which means it is designed exclusively to send data to the Snowflake data warehouse. It does not support reading or extracting data from Snowflake. This functionality aligns with typical use cases where data is streamed from various sources and aggregated or processed in Apache Flink before being persisted in Snowflake for further analysis or storage.
This connector uses the most recent Flink interfaces, Sink<InputT>
and SinkWriter<InputT>
, enabling the writing of data to a Snowflake table that can be configured as needed.
Use the Snowflake connector with Ververica Cloudâ
Prerequisitesâ
-
Snowflake Account Identifier: Ensure you have your organization's account identifier. You can find this information in the Snowflake documentation.
-
Snowflake Account: Ensure you have a user account with the necessary privileges to access the specific tables you need to interact with. While
ACCOUNTADMIN
privileges allow for comprehensive access, the minimum requirement is to have sufficient permissions for the tables involved in your tasks. -
Setup Key-Pair Authentication: Follow the instructions for configuring key-pair authentication as outlined in the Snowflake documentation.
-
Create a SQL Worksheet: Set up a SQL worksheet to create a table for transferring data from Ververica Cloud to Snowflake. Use the following SQL commands to create your database and table in Snowflake:
-- Create a database if it does not exist
CREATE DATABASE IF NOT EXISTS SNOW_TEST;
-- Create or replace a table in the public schema
CREATE OR REPLACE TABLE SNOW_TEST.PUBLIC.SNOW_TEST (
ID NUMBER(38,0),
NAME VARCHAR(16777216),
AGE NUMBER(38,0)
);
(Optional) Step 0: Create a Secret for SQL Scriptâ
Before deploying the script, create a secret key that will be used within your SQL script:
- Go to Security â Secret Values.
- Click Add Secret Value.
- Fill in Secret Name and Secret Value. Note that they both are case-sensitive. Then click OK.
Step 1: Prepare SQL Script for Data Generationâ
The following SQL script generates random users, inserts them into the Snowflake database, and logs the output.
Replace <your-account-id>
and <your-user-name>
with the relevant details found in your Snowflake admin panel.
-- Create a temporary table to generate data
CREATE TEMPORARY TABLE datagen (
`ID` BIGINT,
`NAME` STRING,
`AGE` INT
) WITH (
'connector' = 'datagen',
'fields.ID.kind' = 'sequence',
'fields.ID.start' = '1',
'fields.ID.end' = '1000000',
'fields.AGE.min' = '18',
'fields.AGE.max' = '35',
'fields.NAME.length' = '8',
'rows-per-second' = '1'
);
-- Create a temporary table in Snowflake to receive data
CREATE TEMPORARY TABLE snowflake WITH (
'connector' = 'snowflake',
'url' = 'https://<your-account-id>.snowflakecomputing.com',
'user' = '<your-user-name>',
'user-role' = 'ACCOUNTADMIN',
'db' = 'SNOW_TEST',
'schema' = 'PUBLIC',
'table' = 'SNOW_TEST',
'private-key' = '${secret_values.snowflake_pvt_key}'
) LIKE datagen (EXCLUDING ALL);
-- Create a temporary table to print data to Flink logs
CREATE TEMPORARY TABLE print WITH (
'connector' = 'print',
'logger' = 'true'
) LIKE datagen (EXCLUDING ALL);
-- Insert and log data
BEGIN STATEMENT SET;
insert into snowflake select * from datagen;
insert into print SELECT * from datagen;
END;
Step 2: Deploy SQL Script on Ververica Cloudâ
- Copy and paste the sql code into a Ververica SQL worksheet.
- Replace placeholders with your details in the Ververica SQL worksheet.
- Deply the worksheet. While deploying you will need to choose
vera-1.0.3-u1-flink-1.17
engine version.
Step 3: View Resultsâ
After running your SQL script, you can verify the results in the following ways:
- Data printed on the TaskManager side
- Data inserted into the Snowflake table
Data printed on the TaskManager sideâ
Check the logs on your TaskManager to see the printed data. Logs are accessible as follows:
- Go to Deployments and click the deployment you want to check.
- Display the Logs > Diagnostics > Running Task Managers tab.
- Click the name of the TaskManager.
- Display the Stdout tab.
Data inserted into the Snowflake tableâ
Verify the data inserted in the Snowflake table by querying the table or checking the database's data preview tool.
Type conversionâ
The following table shows supported types and type mappings between VERA and Snowflake:
Flink Type | Castable Snowflake Types |
---|---|
CHAR, VARCHAR | CHAR, VARCHAR |
SMALLINT | VARCHAR, CHAR, BOOLEAN, NUMBER, FLOAT |
BOOLEAN | BOOLEAN, CHAR, VARCHAR |
BINARY | BINARY |
VARBINARY | BINARY |
TINYINT | BINARY, NUMBER, FLOAT, BOOLEAN |
DECIMAL | VARCHAR, CHAR, BOOLEAN, NUMBER, FLOAT |
INTEGER, DATE | VARCHAR, CHAR, BOOLEAN, NUMBER, FLOAT |
BIGINT | VARCHAR, CHAR, BOOLEAN, NUMBER, FLOAT |
FLOAT, DOUBLE | NUMBER, FLOAT, BOOLEAN |
ARRAY | STRING |