Writing to Snowflake
Version 5.2 Feature
The Snowflake ECDA adapter writes batches of data to a Snowflake warehouse. Currently, the Snowflake ECDA adapter works with CSV only. In order to write to a Snowflake warehouse, you must first define a server object for the Snowflake server. This topic describes setting up and performing an INSERT into a foreign stream in order to write data to a Snowflake warehouse. The minimum credentials required to write to a Snowflake warehouse are the warehouse name, user name/password, account, database, schema, and table.
For adapters, you configure and launch the adapter in SQL, using either server or foreign stream/table options. For agents, you configure such options using a properties file and launch the agent at the command line. Many of the options for the ECD adapter and agent are common to all I/O systems. The CREATE FOREIGN STREAM topic in the Streaming SQL Reference Guide has a complete list of options for the ECD adapter.
Note: Because of the nature of streaming data, you will need to set up a pump in order to move rows continually from an s-Server stream to another stream, file, Kafka topic, RDBMS table or other location. Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. A model for setting up a pump is provided below. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.
CREATE OR REPLACE SERVER "Snowflake_Server" TYPE 'snowflake'
FOREIGN DATA WRAPPER ECDA;
CREATE FOREIGN STREAM "SnowflakeWriterSchema"."SnowflakeWriterStream" (
"reported_at" TIMESTAMP NOT NULL,
--credentials for Snowflake table
--formatting for CSV data
CREATE OR REPLACE SCHEMA "Pumps";
SET SCHEMA '"Pumps"';
CREATE OR REPLACE PUMP "writerPump" STOPPED AS
--We recommend creating pumps as stopped
--then using ALTER PUMP "Pumps"."writerPump" START to start it
INSERT INTO "SnowflakeWriterSchema"."SnowflakeWriterStream"
SELECT STREAM * FROM "MyStream";
--where "MyStream" is a currently existing stream
To start writing data, use the following code:
ALTER PUMP "Pumps"."writerPump" START;