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

1. Snowflake Account Identifier: Ensure you have your organization's account identifier. You can find this information in the Snowflake documentation.

2. 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.

3. Setup Key-Pair Authentication: Follow the instructions for configuring key-pair authentication as outlined in the Snowflake documentation.

4. 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:

1. Go to Security → Secret Values.
2. Click Add Secret Value.
3. 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

1. Copy and paste the sql code into a Ververica SQL worksheet.
2. Replace placeholders with your details in the Ververica SQL worksheet.
3. 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:

1. Go to Deployments and click the deployment you want to check.
2. Display the Logs > Diagnostics > Running Task Managers tab.
3. Click the name of the TaskManager.
4. 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