ToDo: Update

Snowflake DB Writer for GCP

Writes data to Snowflake Database.

Example configuration

{
  "parameters": {
    "db": {
      "host": "HOST",
      "port": "PORT",
      "database": "DATABASE",
      "user": "USERNAME",
      "password": "PASSWORD",
      "schema": "SCHEMA",
      "warehouse": "WAREHOUSE",
      "ssh": {
        "enabled": true,
        "keys": {
          "private": "ENCRYPTED_PRIVATE_SSH_KEY",
          "public": "PUBLIC_SSH_KEY"
        },
        "sshHost": "PROXY_HOSTNAME"
      }
    },
    "tableId": "simple",
    "dbName": "simple",
    "export": true,
    "incremental": false,
    "primaryKey": ["id"],
    "items": [
      {
        "name": "id",
        "dbName": "id",
        "type": "int",
        "size": null,
        "nullable": null,
        "default": null
      },
      {
        "name": "name",
        "dbName": "name",
        "type": "varchar",
        "size": 255,
        "nullable": null,
        "default": null
      },
      {
        "name": "glasses",
        "dbName": "glasses",
        "type": "varchar",
        "size": 255,
        "nullable": null,
        "default": null
      }
    ]
  },
  "storage": {
    "input": {
      "tables": [
        {
          "source": "simple",
          "destination": "simple.csv"
        }
      ]
    }
  }
}

Development

Required snowflake resource for writer:

CREATE DATABASE "snowflake_writer_gcs";
USE DATABASE "snowflake_writer_gcs";
CREATE TRANSIENT SCHEMA "snowflake_writer_gcs";
CREATE WAREHOUSE "snowflake_writer_gcs" WITH 
  WAREHOUSE_SIZE = 'XSMALL' 
  WAREHOUSE_TYPE = 'STANDARD' 
  AUTO_SUSPEND = 900 
  AUTO_RESUME = TRUE;
CREATE ROLE "snowflake_writer_gcs";
GRANT USAGE ON WAREHOUSE "snowflake_writer_gcs" TO ROLE "snowflake_writer_gcs";
GRANT USAGE ON DATABASE "snowflake_writer_gcs" TO ROLE "snowflake_writer_gcs";
GRANT ALL ON SCHEMA "snowflake_writer_gcs" TO ROLE "snowflake_writer_gcs";
GRANT ALL ON FUTURE TABLES IN SCHEMA "snowflake_writer_gcs" TO ROLE "snowflake_writer_gcs";
GRANT ALL ON FUTURE VIEWS IN SCHEMA "snowflake_writer_gcs" TO ROLE "snowflake_writer_gcs";
CREATE USER "snowflake_writer_gcs" 
  PASSWORD = 'password' 
  DEFAULT_ROLE = "snowflake_writer_gcs" 
  DEFAULT_WAREHOUSE = "snowflake_writer_gcs" 
  DEFAULT_NAMESPACE = "snowflake_writer_gcs"."snowflake_writer_gcs" 
  MUST_CHANGE_PASSWORD = FALSE;
GRANT ROLE "snowflake_writer_gcs" TO USER "snowflake_writer_gcs";

App is developed on localhost using TDD.

1. Clone from repository: git clone git@github.com:keboola/db-writer-snowflake.git
2. Change directory: cd db-writer-snowflake
3. Install dependencies: docker-compose run --rm dev composer install -n
4. Create .env file:

KBC_URL=
KBC_RUNID=
DB_HOST=
DB_PORT=
DB_DATABASE=
DB_USER=
DB_PASSWORD=
DB_SCHEMA=
DB_WAREHOUSE=

5. Run docker-compose, which will trigger phpunit: docker-compose run --rm app

License

MIT licensed, see LICENSE file.