Connecting to Snowflake

Create a Snowflake database connection

A sample connection for Snowflake would be:

CREATE DATACONNECTION snowflake_connection CXNPROPERTIES
(
  'connection_type' = ' JDBC ',
  'driver' = ' snowflake ',
  'account' = ' okera123 ',
  'host' = ' my-host.com ',
  'port'= ' 3306 ',
  'user'= ' awsps:///my-username ',
  'password' = ' awsps:///my-password ',
  'default_catalog' = ' OKERA_DEMO ',
  'default_schema' = ' marketing ' -- optional
)

default_catalog: is the Snowflake database you want to establish a connection to.

Note

Snowflake object names are sensitive to case, so please specify your database and schema name in the correct case.

Properties file (old way)

A sample properties file for Snowflake would be:

driver=snowflake
host=<snowflake account>.snowflakecomputing.com
port=<n/a for snowflake>
db=SNOWFLAKE_SAMPLE_DATA
user=<username>
password=<password>
account=<Required. Snowflake account>
jdbc.db.name=<Default catalog to connect to. Note, skipping this value can result in errors>
jdbc.schema.name<Optional. Specify the schema name if used>
defaultdb=<The default database to connect to. Typically same as jdbc.db.name>

Note

For Snowflake, 'db' parameter is mandatory. This should be the default database for connection.

Querying data in Snowflake

Okera's Snowflake connector (beta) pushes down full queries (including joins and aggregations) to Snowflake, while enforcing the complete access policy, as well as audit log entries. Note this connector is designed for data read/SELECT queries and not INSERT operations or DDL operations on the underlying Snowflake database.

Users, such as data analysts, can connect their favorite SQL tool (e.g. DBeaver, Tableau, Looker) via Okera’s ODBC/JDBC endpoint, and their queries will be transparently sent to Snowflake, after being authorized and audited by Okera (and if the user does not have permission to access the data they are trying to access, the query will be rejected). With this connector you get the benefit of Snowflake's native performance scale and Okera's complete policy and auditing capabilities.

Note that each BI tool generates SQL in a slightly idiosyncratic way. Okera's Snowflake connector currently supports these BI tools:

  • DBeaver
  • Tableau
  • Looker
  • PowerBI

Note

The entire query must only access data in Snowflake and in a single connection. Any queries joining other data (e.g. S3) to Snowflake will not be pushed down. Some predicates will still be pushed down by Okera’s scan, but not the full set.

Okera Snowflake Integration

Supported privacy functions

We broadly support mask, mask_ccn, null, sha2, tokenize, and zero as privacy functions for snowflake pushdown. You will need to run the script we provide to ensure these functions are installed in Snowflake.

Warning

These functions should not be shared with end users and only a Snowflake admin should create/use them.

Datatype limitations

  • mask, mask_ccn only supported for STRING,CHAR, VARCHAR types
  • tokenize supported for CHAR, VARCHAR, STRING and INT types. tokenize preserves format and referential integrity for INT and STRING
  • TIMESTAMP and DATE support yet to be implemented for all the functions

Known SQL incompatibilities:

  • TRY_CAST functions not supported yet.
  • LIMIT ALL not supported in snowflake.
  • Wildcards from two tables which have same column name would throw error. Example, if both a and b had same column names in each, the below query would throw duplicate column error SELECT a.*, b.* FROM
  • Main table aliases referenced in sub-queries do not work (due to a Snowflake limitation). For example, the t.* below does not work
SELECT EXISTS(SELECT t.* FROM okera.tpch_test_snowflake.region)
FROM okera.tpch_test_snowflake.nation t