pipelinewise-target-snowflake

Singer target that loads data into Snowflake following the Singer spec.

This is a PipelineWise compatible target connector.

How to use it

The recommended method of running this target is to use it from PipelineWise. When running it from PipelineWise you don't need to configure this tap with JSON files and most of things are automated. Please check the related documentation at Target Snowflake

If you want to run this Singer Target independently please read further.

Install

First, make sure Python 3 is installed on your system or follow these installation instructions for Mac or Ubuntu.

It's recommended to use a virtualenv:

  python3 -m venv venv
  pip install pipelinewise-target-snowflake

or

  python3 -m venv venv
  . venv/bin/activate
  pip install --upgrade pip
  pip install .

Flow diagram

To run

Like any other target that's following the singer specificiation:

some-singer-tap | target-snowflake --config [config.json]

It's reading incoming messages from STDIN and using the properites in config.json to upload data into Snowflake.

Note: To avoid version conflicts run tap and targets in separate virtual environments.

Pre-requirements

You need to create a few objects in snowflake in one schema before start using this target.

1. Create a named file format. This will be used by the MERGE/COPY commands to parse the files correctly from S3. You can use CSV or Parquet file formats.

To use CSV files:

CREATE FILE FORMAT {database}.{schema}.{file_format_name}
TYPE = 'CSV' ESCAPE='\\' FIELD_OPTIONALLY_ENCLOSED_BY='"';

To use Parquet files (experimental):

CREATE FILE FORMAT {database}.{schema}.{file_format_name} TYPE = 'PARQUET';

Important: Parquet files are not supported with table stages. If you want to use Parquet files then you need to have an external stage in snowflake. Please read further for more details in point 4).

2. Create a Role with all the required permissions:

CREATE OR REPLACE ROLE ppw_target_snowflake;
GRANT USAGE ON DATABASE {database} TO ROLE ppw_target_snowflake;
GRANT CREATE SCHEMA ON DATABASE {database} TO ROLE ppw_target_snowflake;

GRANT USAGE ON SCHEMA {database}.{schema} TO role ppw_target_snowflake;
GRANT USAGE ON STAGE {database}.{schema}.{stage_name} TO ROLE ppw_target_snowflake;
GRANT USAGE ON FILE FORMAT {database}.{schema}.{file_format_name} TO ROLE ppw_target_snowflake;
GRANT USAGE ON WAREHOUSE {warehouse} TO ROLE ppw_target_snowflake;

Replace database, schema, warehouse, stage_name and file_format_name between { and } characters to the actual values from point 1 and 2.

3. Create a user and grant permission to the role:

CREATE OR REPLACE USER {user}
PASSWORD = '{password}'
DEFAULT_ROLE = ppw_target_snowflake
DEFAULT_WAREHOUSE = '{warehouse}'
MUST_CHANGE_PASSWORD = FALSE;

GRANT ROLE ppw_target_snowflake TO USER {user};

Replace warehouse between { and } characters to the actual values from point 3.

4. Optional external stage:

By default table stages are used to load data into snowflake tables. If you want to use external stages with s3 or s3 compatible storage engines then you need to create a STAGE object:

CREATE STAGE {database}.{schema}.{stage_name}
url='s3://{s3_bucket}'
credentials=(AWS_KEY_ID='{aws_key_id}' AWS_SECRET_KEY='{aws_secret_key}')
encryption=(MASTER_KEY='{client_side_encryption_master_key}');
GRANT USAGE ON STAGE {database}.{schema}.{stage_name} TO ROLE ppw_target_snowflake;

Notes:

• To use external stages you need to define s3_bucket and stage values in config.json as well.
• The encryption option is optional and used for client side encryption.
• If you want client side encryption enabled you need to define the same master key in the target config.json.
• Instead of credentials you can also use storage_integration

Further details below in the Configuration settings section.

Configuration settings

Running the the target connector requires a config.json file. Example with the minimal settings:

{

  "account": "rtxxxxx.eu-central-1",
  "dbname": "database_name",
  "user": "my_user",
  "password": "password",
  "warehouse": "my_virtual_warehouse",
  "file_format": "snowflake_file_format_object_name",
  "default_target_schema": "my_target_schema"
}

Full list of options in config.json:

To run tests:

1. Define the environment variables that are required to run the tests by creating a .env file in tests/integration, or by exporting the variables below.

  export TARGET_SNOWFLAKE_ACCOUNT=<snowflake-account-name>
  export TARGET_SNOWFLAKE_DBNAME=<snowflake-database-name>
  export TARGET_SNOWFLAKE_USER=<snowflake-user>
  export TARGET_SNOWFLAKE_PASSWORD=<snowflake-password>
  export TARGET_SNOWFLAKE_WAREHOUSE=<snowflake-warehouse>
  export TARGET_SNOWFLAKE_SCHEMA=<snowflake-schema>
  export TARGET_SNOWFLAKE_AWS_ACCESS_KEY=<aws-access-key-id>
  export TARGET_SNOWFLAKE_AWS_SECRET_ACCESS_KEY=<aws-access-secret-access-key>
  export TARGET_SNOWFLAKE_S3_ACL=<s3-target-acl>
  export TARGET_SNOWFLAKE_S3_BUCKET=<s3-external-bucket>
  export TARGET_SNOWFLAKE_S3_KEY_PREFIX=<bucket-directory>
  export TARGET_SNOWFLAKE_STAGE=<stage-object-with-schema-name>
  export TARGET_SNOWFLAKE_FILE_FORMAT_CSV=<file-format-csv-object-with-schema-name>
  export TARGET_SNOWFLAKE_FILE_FORMAT_PARQUET=<file-format-parquet-object-with-schema-name>
  export CLIENT_SIDE_ENCRYPTION_MASTER_KEY=<client_side_encryption_master_key>

2. Install python test dependencies in a virtual env and run unit and integration tests

  python3 -m venv venv
  . venv/bin/activate
  pip install --upgrade pip
  pip install .[test]

3. To run unit tests:

  pytest tests/unit

4. To run integration tests:

  pytest tests/integration

To run pylint:

1. Install python dependencies and run python linter

  python3 -m venv venv
  . venv/bin/activate
  pip install --upgrade pip
  pip install .[test]
  pylint target_snowflake

License

Apache License Version 2.0

See LICENSE to see the full text.