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.
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>
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:
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.
Supported privacy functions¶
We broadly support
zero as privacy functions for snowflake pushdown.
You will need to run the script we provide to ensure these functions are installed in Snowflake.
These functions should not be shared with end users and only a Snowflake admin should create/use them.
mask_ccnonly supported for
tokenizepreserves format and referential integrity for
DATEsupport yet to be implemented for all the functions
Known SQL incompatibilities:
TRY_CASTfunctions not supported yet.
LIMIT ALLnot 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