Skip to main content

Singer.io target for loading data into Snowflake

Project description

Target Snowflake

CircleCI

PyPI version

A Singer Snowflake target, for use with Singer streams generated by Singer taps.

Snowflake Connector

Docs

Install

pip install target-snowflake

Usage

  1. Follow the Singer.io Best Practices for setting up separate tap and target virtualenvs to avoid version conflicts.

  2. Create a config file at ~/singer.io/target_snowflake_config.json with Snowflake connection information and target Snowflake schema and warehouse.

    {
      "snowflake_account": "https://XXXXX.snowflakecomputing.com",
      "snowflake_username": "myuser",
      "snowflake_role": "myrole",
      "snowflake_password": "1234",
      "snowflake_database": "my_analytics",
      "snowflake_schema": "mytapname",
      "snowflake_warehouse": "dw"
    }
    

1. Run `target-snowfkajke` against a [Singer](https://singer.io) tap.

 ```bash
 ~/.virtualenvs/tap-something/bin/tap-something \
   | ~/.virtualenvs/target-snowflake/bin/target-snowflake \
     --config ~/singer.io/target_snowflake_config.json >> state.json

If you are running windows, the following is equivalent:

venvs\tap-exchangeratesapi\Scripts\tap-exchangeratesapi.exe | ^
venvs\target-snowflake\Scripts\target-snowlfake.exe ^
--config target_snowflake_config.json

Config.json

The fields available to be specified in the config file are specified here.

Field Type Default Details
snowflake_account ["string"] N/A ACCOUNT might require the region and cloud platform where your account is located, in the form of: <your_account_name>.<region_id>.<cloud> (e.g. xy12345.east-us-2.azure) Refer to Snowflake's documentation about Account
snowflake_username ["string"] N/A
snowflake_password ["string", "null"] null
snowflake_role ["string"] null If not specified, Snowflake will use the user's default role.
snowflake_database ["string"] N/A
snowflake_authenticator ["string"] "snowflake" Speifies the authentication provider for snowflake to use. Valud options are the internal one ("snowflake"), a browser session ("externalbrowser"), or Okta ("https://<your_okta_account_name>.okta.com"). See the snowflake docs for more details.
snowflake_schema ["string", "null"] "PUBLIC"
snowflake_warehouse ["string"] N/A
invalid_records_detect ["boolean", "null"] true Include false in your config to disable crashing on invalid records
invalid_records_threshold ["integer", "null"] 0 Include a positive value n in your config to allow at most n invalid records per stream before giving up.
disable_collection ["string", "null"] false Include true in your config to disable Singer Usage Logging.
logging_level ["string", "null"] "INFO" The level for logging. Set to DEBUG to get things like queries executed, timing of those queries, etc. See Python's Logger Levels for information about valid values.
persist_empty_tables ["boolean", "null"] False Whether the Target should create tables which have no records present in Remote.
state_support ["boolean", "null"] True Whether the Target should emit STATE messages to stdout for further consumption. In this mode, which is on by default, STATE messages are buffered in memory until all the records that occurred before them are flushed according to the batch flushing schedule the target is configured with.
target_s3 ["object", "null"] N/A When included, use S3 to stage files. See S3 below

S3 Config.json

Field Type Default Details
aws_access_key_id ["string"] N/A
aws_secret_access_key ["string"] N/A
bucket ["string"] N/A Bucket where staging files should be uploaded to.
key_prefix ["string", "null"] "" Prefix for staging file uploads to allow for better delineation of tmp files

Limitations

  • Snowflake SQL Identifiers:
    • Although Snowflake supports quoted identifiers to have non-alphanumeric values, target-snowflake limits identifiers to uppercase alphanumerics, and underscores
    • This is done to make querability/useability in Snowflake simpler, so as to not require users to have to use sometimes cumbersome quotes to query their data
  • Requires a JSON Schema for every stream.
  • Only string, string with date-time format, integer, number, boolean, object, and array types with or without null are supported. Arrays can have any of the other types listed, including objects as types within items.
    • Example of JSON Schema types that work
      • ['number']
      • ['string']
      • ['string', 'null']
      • ['string', 'integer']
      • ['integer', 'number']
    • Exmaple of JSON Schema types that DO NOT work
      • ['any']
      • ['null']
  • JSON Schema combinations such as anyOf and allOf are not supported.
  • JSON Schema $ref is partially supported:
    • NOTE: The following limitations are known to NOT fail gracefully
    • Presently you cannot have any circular or recursive $refs
    • $refs must be present within the schema:
      • URI's do not work
      • if the $ref is broken, the behaviour is considered unexpected
  • Any values which are the string \\N will be streamed to Snowflake as the literal null

Sponsorship

Target Snowflake is sponsored by Data Mill (Data Mill Services, LLC) datamill.co.

Data Mill helps organizations utilize modern data infrastructure and data science to power analytics, products, and services.


Copyright Data Mill Services, LLC 2018

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Source Distribution

target-snowflake-0.2.4.tar.gz (15.1 kB view details)

Uploaded Source

Built Distribution

target_snowflake-0.2.4-py3-none-any.whl (25.3 kB view details)

Uploaded Python 3

File details

Details for the file target-snowflake-0.2.4.tar.gz.

File metadata

  • Download URL: target-snowflake-0.2.4.tar.gz
  • Upload date:
  • Size: 15.1 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.1.1 pkginfo/1.5.0.1 requests/2.23.0 setuptools/46.1.3 requests-toolbelt/0.9.1 tqdm/4.46.0 CPython/3.7.7

File hashes

Hashes for target-snowflake-0.2.4.tar.gz
Algorithm Hash digest
SHA256 09670035c023606646ceeba498fb978549150de272d0783ce763231214cda6ec
MD5 f61025ac501328097016bf248c2d93cc
BLAKE2b-256 23a25b538a1bf49a06d32d33e70022a12e7623dc3375452fe5b9e4396f2c3a7e

See more details on using hashes here.

File details

Details for the file target_snowflake-0.2.4-py3-none-any.whl.

File metadata

  • Download URL: target_snowflake-0.2.4-py3-none-any.whl
  • Upload date:
  • Size: 25.3 kB
  • Tags: Python 3
  • Uploaded using Trusted Publishing? No
  • Uploaded via: twine/3.1.1 pkginfo/1.5.0.1 requests/2.23.0 setuptools/46.1.3 requests-toolbelt/0.9.1 tqdm/4.46.0 CPython/3.7.7

File hashes

Hashes for target_snowflake-0.2.4-py3-none-any.whl
Algorithm Hash digest
SHA256 a4872044616c74da2279ee94728cdc7159fac7a5c80479fec161b7aaf0861b76
MD5 670644cecbf0469d503af00757dfd7cc
BLAKE2b-256 6c9252b365921de6b242f00ef8a3b19d7123d3a42598a44cbd39182e41036f36

See more details on using hashes here.

Supported by

AWS AWS Cloud computing and Security Sponsor Datadog Datadog Monitoring Fastly Fastly CDN Google Google Download Analytics Microsoft Microsoft PSF Sponsor Pingdom Pingdom Monitoring Sentry Sentry Error logging StatusPage StatusPage Status page