Integrating Snowflake :: SQLstream Documentation
> Integrating Guavus SQLstream with Other Systems > Integrating Snowflake
You can write data out of s-Server to a file in a Snowflake warehouse. s-Server formats files for Snowflake according to the same principles as it does for writing to the file system. See Writing to the File System for more details. You can specify how files rotate according to time, size, or both, and you can specify how rotated files are named. Currently, the Snowflake ECDA adapter works with CSV only.
The minimum credentials required to write to a Snowflake warehouse are the warehouse name, user name/password, account, database, schema, and stream/table.
To write from local locations, you configure and launch the adapter in SQL, using either server or foreign stream/table options. See Writing to Snowflake Using SQL below. To write from remote locations, you configure such options using a properties file and launch the agent at the command line. See Writing to Snowflake Using the ECD Agent below. Many of the options for the ECD adapter and agent are common to all I/O systems. The CREATE FOREIGN STREAM topic in the <%SQLRF_TITLE%> has a complete list of options for the ECD adapter.
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. Some of the options for the ECD adapter and agent are common to all I/O systems. The CREATE FOREIGN STREAM topic in the SQLstream Streaming SQL Reference Guide has a complete list of options for the ECD adapter.
s-Server rotates files according to options set for FILE_ROTATION_TIME, FILE_ROTATION_SIZE, and FILE_ROTATION_RESPECT_ROWTIME.
Writing to Snowflake Using SQL
To write to Snowflake, you need to create a foreign stream in SQL that references a prebuilt server object called SNOWFLAKE_SERVER. In the foreign stream's options, you configure how s-Server connects to Snowflake. For more information on creating foreign streams, see the topic CREATE FOREIGN STREAM in the Streaming SQL Reference Guide.
Streams, like most SQL objects (but unlike data wrappers and servers), must be created within a schema. The following code first creates a schema in which to run the rest of the sample code below, then creates a foreign stream named SnowflakeWriterSchema.SnowflakeWriterStream.
CREATE OR REPLACE SCHEMA SnowflakeWriterSchema;
SET SCHEMA 'SnowflakeWriterSchema';
CREATE FOREIGN STREAM SnowflakeWriterSchema.SnowflakeWriterStream (
SERVER SNOWFLAKE_SERVER
"ORIGINAL_FILENAME" 'bus-output.csv',
"FORMATTER_INCLUDE_ROWTIME" 'true');
To begin writing data to Snowflake, you INSERT into SnowflakeWriterSchema.SnowflakeWriterStream. When SnowflakeWriterSchema.SnowflakeWriterStream receives rows, s-Server writes data to the Snowflake warehouse you have configured in the foreign stream options.
In most cases, you will want to set up a pump that writes data to SnowflakeWriterSchema.SnowflakeWriterStream. Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.
CREATE OR REPLACE SCHEMA Pumps;
SET SCHEMA 'Pumps';
CREATE OR REPLACE PUMP writerPump STOPPED AS
INSERT INTO SnowflakeWriterSchema.SnowflakeWriterStream
ALTER PUMP Pumps.writerPump START;
Writing to Snowflake Using the ECD Agent
You can use the ECD agent to write files to Snowflake from remote locations. See Writing Data to Remote Locations for more details. The ECD agent takes similar options, but these options need to be formatted in a properties file along the lines of the following. These properties correspond to those defined for the adapter above.
Note: Before using the ECD agent, you need to create a source stream for it. In the below example, you would need to create the foreign stream "HADOOPWRITERSTREAM."
Sample Property File for ECD Agent
SCHEMA_NAME="SnowflakeWriterSchema"
TABLE_NAME="SnowflakeWriterStream"
ACCOUNT=sqlstream
WAREHOUSE=DEMO_WH
DTABLE=demo
QUOTE_CHARACTER=
ORIGINAL_FILENAME=bus-output.csv
FILE_ROTATION_SIZE=20K'
To invoke the agent, from the directory $SQLSTREAM_HOME/../clienttools/EcdaAgent/ enter the following at the command line:
$ ./commondataagent.sh --output --props sample.test.properties --io snowflake
Integrating Snowflake :: SQLstream Documentation
> Integrating Guavus SQLstream with Other Systems > Integrating Snowflake
You can write data out of s-Server to a file in a Snowflake warehouse. s-Server formats files for Snowflake according to the same principles as it does for writing to the file system. See Writing to the File System for more details. You can specify how files rotate according to time, size, or both, and you can specify how rotated files are named. Currently, the Snowflake ECDA adapter works with CSV only.
The minimum credentials required to write to a Snowflake warehouse are the warehouse name, user name/password, account, database, schema, and stream/table.
To write from local locations, you configure and launch the adapter in SQL, using either server or foreign stream/table options. See Writing to Snowflake Using SQL below. To write from remote locations, you configure such options using a properties file and launch the agent at the command line. See Writing to Snowflake Using the ECD Agent below. Many of the options for the ECD adapter and agent are common to all I/O systems. The CREATE FOREIGN STREAM topic in the <%SQLRF_TITLE%> has a complete list of options for the ECD adapter.
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. Some of the options for the ECD adapter and agent are common to all I/O systems. The CREATE FOREIGN STREAM topic in the SQLstream Streaming SQL Reference Guide has a complete list of options for the ECD adapter.
s-Server rotates files according to options set for FILE_ROTATION_TIME, FILE_ROTATION_SIZE, and FILE_ROTATION_RESPECT_ROWTIME.
Writing to Snowflake Using SQL
To write to Snowflake, you need to create a foreign stream in SQL that references a prebuilt server object called SNOWFLAKE_SERVER. In the foreign stream's options, you configure how s-Server connects to Snowflake. For more information on creating foreign streams, see the topic CREATE FOREIGN STREAM in the Streaming SQL Reference Guide.
Streams, like most SQL objects (but unlike data w
rappers and servers), must be created within a schema. The following code first creates a schema in which to run the rest of the sample code below, then creates a foreign stream named SnowflakeWriterSchema.SnowflakeWriterStream.
CREATE OR REPLACE SCHEMA SnowflakeWriterSchema;
SET SCHEMA 'SnowflakeWriterSchema';
CREATE FOREIGN STREAM SnowflakeWriterSchema.SnowflakeWriterStream (
SERVER SNOWFLAKE_SERVER
"ORIGINAL_FILENAME" 'bus-output.csv',
"FORMATTER_INCLUDE_ROWTIME" 'true');
To begin writing data to Snowflake, you INSERT into SnowflakeWriterSchema.SnowflakeWriterStream. When SnowflakeWriterSchema.SnowflakeWriterStream receives rows, s-Server writes data to the Snowflake warehouse you have configured in the foreign stream options.
In most cases, you will want to set up a pump that writes data to SnowflakeWriterSchema.SnowflakeWriterStream. Pumps are INSERT macros that continually pass data from one point in a streaming pipeline to the other. See the topic CREATE PUMP in the s-Server Streaming SQL Reference Guide for more details.
CREATE OR REPLACE SCHEMA Pumps;
SET SCHEMA 'Pumps';
CREATE OR REPLACE PUMP writerPump STOPPED AS
INSERT INTO SnowflakeWriterSchema.SnowflakeWriterStream
ALTER PUMP Pumps.writerPump START;
Writing to Snowflake Using the ECD Agent
You can use the ECD agent to write files to Snowflake from remote locations. See Writing Data to Remote Locations for more details. The ECD agent takes similar options, but these options need to be formatted in a properties file along the lines of the following. These properties correspond to those defined for the adapter above.
Note: Before using the ECD agent, you need to create a source stream for it. In the below example, you would need to create the foreign stream "HADOOPWRITERSTREAM."
Sample Property File for ECD Agent
SCHEMA_NAME="SnowflakeWriterSchema"
TABLE_NAME="SnowflakeWriterStream"
ACCOUNT=sqlstream
WAREHOUSE=DEMO_WH
DTABLE=demo
QUOTE_CHARACTER=
ORIGINAL_FILENAME=bus-output.csv
FILE_ROTATION_SIZE=20K'
To invoke the agent, from the directory $SQLSTREAM_HOME/../clienttools/EcdaAgent/ enter the following at the command line:
$ ./commondataagent.sh --output --props sample.test.properties --io snowflake