Snowflake Connectivity¶

This page provides instructions on reading data from Snowflake and, when necessary, writing data back to Snowflake.

Syntax¶

CREATE [ OR REPLACE ] FOREIGN TABLE [schema_name].table_name (
  { column_def [, ...] }
   )
  [ FOREIGN DATA ] WRAPPER snowflake_fdw
  [ OPTIONS ( option_def [, ...  ] ) ]

option_def ::=
{
  ACCOUNT_NAME = '<account_name>',
  USERNAME = '<username>',
  PASSWORD = '<password>',
  SF_WAREHOUSE = '<warehouse_name>',
  DATABASE = '<database_name>',
  DB_TABLE = '<table_name>',
  [ SCHEMA = '<schema_name>' ] /* Optional - will use default schema "public" if not specified. */
}

column_def ::=
{ column_name type_name [ default ] [ column_constraint ] }

column_constraint ::=
{ NOT NULL | NULL }

default ::=
DEFAULT default_value
| IDENTITY [ ( start_with [ , increment_by ] ) ]

schema_name ::= identifier

table_name ::= identifier

column_name ::= identifier

DROP TABLE [ IF EXISTS ] [schema_name.]table_name

schema_name ::= identifier

table_name ::= identifier

COPY { [schema_name].table_name [ ( column_name [, ... ] ) ] | query } TO
[FOREIGN DATA] WRAPPER
  snowflake_fdw
OPTIONS
 (
  [ copy_to_option [, ...] ]
 )

copy_to_option ::=
{
  ACCOUNT_NAME = '<account name>',
  USERNAME = '<username>',
  PASSWORD = '<password>',
  SF_WAREHOUSE = '<warehouse_name>',
  DATABASE = '<database_name>',
  DB_TABLE = '<table_name>',
  NEW_TABLE = <TRUE | FALSE>,
  [ SCHEMA = '<schema_name>' ] /* Optional - will use default schema "public" if not specified. */
}

column_name ::= identifier

schema_name ::= identifier

table_name ::= identifier

COPY
  [ "<schema_name>". ]"<table_name>" [ (<column_name>) [ ,...] ]
FROM
[FOREIGN DATA] WRAPPER
  <fdw_name>
OPTIONS
(
  ACCOUNT_NAME = '<account name>',
  USERNAME = '<username>',
  PASSWORD = '<password>',
  SF_WAREHOUSE = '<warehouse_name>',
  SCHEMA = '<schema_name>',
  DATABASE = '<database_name>',
  DB_TABLE = '<table_name>'
  );

Parameters¶

Parameter	Description
`schema_name`	The name of the Snowflake schema where the table to be imported is located
`table_name`	The name of the Snowflake table you wish to import
`ACCOUNT_NAME`	Snowflake account name
`USERNAME`	Snowflake username
`PASSWORD`	Snowflake password
`SF_WAREHOUSE`	The name of the Snowflake warehouse where the table to be imported is located
`DATABASE`	The name of the Snowflake database where the table to be imported is located
`SCHEMA`	The name of the Snowflake schema where the table to be imported is located
`DB_TABLE`	The name of the Snowflake table to be imported
`column_name`	The name of the columns in the Snowflake table to be imported
`type_name`	The column data type in the Snowflake table to be imported
`NEW_TABLE`	Specifies whether or not the `COPY TO` command creates a new table upon execution. `TRUE` = create a new table and `FALSE` = copy data into an existing table. The default is `FALSE`

Usage Notes¶

NEW_TABLE¶: The newly created table will be created within the schema specified under option_def.
Communication¶: Communication with Snowflake web server requires SSL.

Data Types Mapping¶

The following Snowflake data types are not supported: BYTEINT, BINARY, VARBINARY, TIMESTAMP, TIME, TIMESTAMP_LTZ, TIMESTAMP_TZ, VARIANT, OBJECT, GEOGRAPHY, GEOMETRY

SQream Blue Data Type	Snowflake Data Type
`NUMERIC`	`NUMERIC`, `NUMBER`, `DECIMAL`
`INT`, `INTEGER`	`INT`, `INTEGER`
`BIGINT`, `NUMBER`	`BIGINT`
`SMALLINT`	`SMALLINT`
`TINYINT`	`TINYINT`
`DOUBLE`	`FLOAT`
`REAL`	`FLOAT4`
`DOUBLE`	`FLOAT8`
`TEXT`	`VARCHAR`, `CHAR`, `CHARACTER`, `STRING`, `TEXT`
`BOOL`	`BOOLEAN`
`DATE`	`DATE`
`DATETIME`	`DATETIME`
`DATETIME`	`TIMESTAMP_NTZ`
`ARRAY`	`ARRAY`

Examples¶

Creating a Table¶

CREATE OR REPLACE FOREIGN TABLE snowflake_table
(
  id BIGINT,
  address TEXT,
  purchase DOUBLE
)
WRAPPER snowflake_fdw
OPTIONS
 (
  ACCOUNT_NAME = 'my sf account',
  DB_TABLE = 'my_customers',
  USERNAME = 'JohnSmith',
  PASSWORD = 'pa$$w0rD',
  DATABASE = 'master',
  SCHEMA = 'public',
  SF_WAREHOUSE = 'my_sf_warehouse'
);

Joining Blue and Snowflake Tables¶

SELECT
  *
FROM
  snowflake_table sft
  JOIN table1 t1 ON sft.id = t1.id
WHERE
  sft.date >= '2022-01-01'
  AND t1.status = 'active';

Copying Data Into a Snowflake Table¶

COPY
  t TO
WRAPPER
  snowflake_fdw
OPTIONS
 (
  ACCOUNT_NAME = 'my sf account',
  DB_TABLE = 'my_customers',
  USERNAME = 'JohnSmith',
  PASSWORD = 'pa$$w0rD',
  DATABASE = 'master',
  SCHEMA = 'public',
  SF_WAREHOUSE = 'my_sf_warehouse',
  NEW_TABLE = TRUE
 );

Copying Data Into a Blue Table¶

COPY
  customers
FROM
WRAPPER
  snowflake_fdw
OPTIONS
(
  ACCOUNT_NAME = 'my sf account',
  USERNAME = 'JohnSmith',
  PASSWORD = 'pa$$w0rD',
  SF_WAREHOUSE = 'my_sf_warehouse',
  SCHEMA = 'public',
  DATABASE = 'master',
  DB_TABLE = 'my_customers'
  );