Connect a Cloud Warehouse

See Connect to Your Data for where to create a connection and how to use it. This page lists what each warehouse asks for.

BigQuery

• Reading. A table, a view or one query. GEOGRAPHY columns are read as longitude and latitude.
• Writing. "Create a new table", "Add rows" and "Replace the table", when the connection's "Access" is "Read and write".

Snowflake

Choose one "Sign-in method":

• "Access token": paste a programmatic access token into "Key or token".
• "Key pair": enter the "User name", the "Public key fingerprint" exactly as Snowflake shows it for that user (it starts with SHA256:), and choose the "Private key file". The key must not have a passphrase.

A key pair is the better choice for a connection that runs unattended: it does not expire the way a token does.

• Reading. A table, a view or one query.
• Geometry. GEOGRAPHY columns are read as longitude and latitude. A GEOMETRY column is read only when it is in WGS 84 (SRID 4326); for another system, convert it in a query with ST_TRANSFORM, or use a GEOGRAPHY column.
• Writing. "Create a new table", "Add rows" and "Replace the table". Shapes are written to a GEOGRAPHY column.
• Names are upper case. A table and its columns are written in upper case, as Snowflake does for a name that is not quoted. A table written as results appears in the list as RESULTS; choose it from the list rather than typing it.
• Stopping. Stopping a read or a write cancels the statement in Snowflake.
• Cost. Reading runs on your warehouse. A warehouse that suspends itself when idle keeps the cost to the time a read takes.

What the Snowflake role needs

• To read: USAGE on the warehouse, the database and the schema, and SELECT on the tables and views.
• To write from a flow: also CREATE TABLE on the schema being written to.

A role and a service user that sign in with a key pair. The last statement shows the fingerprint to enter in "Public key fingerprint".

-- To read
CREATE ROLE tamaat_role;
GRANT USAGE ON WAREHOUSE gis_wh TO ROLE tamaat_role;
GRANT USAGE ON DATABASE city TO ROLE tamaat_role;
GRANT USAGE ON SCHEMA city.gis TO ROLE tamaat_role;
GRANT SELECT ON ALL TABLES IN SCHEMA city.gis TO ROLE tamaat_role;
GRANT SELECT ON ALL VIEWS IN SCHEMA city.gis TO ROLE tamaat_role;

-- To write from a flow
GRANT USAGE, CREATE TABLE ON SCHEMA city.results TO ROLE tamaat_role;

CREATE USER tamaat TYPE = SERVICE DEFAULT_ROLE = tamaat_role
  DEFAULT_WAREHOUSE = gis_wh RSA_PUBLIC_KEY = 'MIIBIjANBgkq...';
GRANT ROLE tamaat_role TO USER tamaat;

DESCRIBE USER tamaat;  -- RSA_PUBLIC_KEY_FP

Databricks

• Reading. A table, a view or one query.
• Geometry. A Databricks table usually keeps its shapes as well-known text in a text column, and Tamaat reads that: the first text column whose values are shapes is used, or the one you name as the "Geometry column". GEOMETRY and GEOGRAPHY columns are read too. The shapes must be in longitude and latitude; text that cannot be is refused.
• Writing. "Create a new table", "Add rows" and "Replace the table". Shapes are written as well-known text in a text column.
• Stopping. Stopping a read or a write cancels the statement in Databricks.
• Cost. Reading runs on your SQL warehouse. A serverless warehouse that stops when idle keeps the cost to the time a read takes.

What the Databricks user needs

• To read: permission to use the SQL warehouse, USE CATALOG and USE SCHEMA, and SELECT on the tables and views.
• To write from a flow: also CREATE TABLE on the schema being written to.

Run these in the SQL editor as an administrator of the catalog, with the user Tamaat signs in as.

-- To read
GRANT USE CATALOG ON CATALOG city TO `tamaat@example.com`;
GRANT USE SCHEMA, SELECT ON SCHEMA city.gis TO `tamaat@example.com`;

-- To write from a flow
GRANT USE SCHEMA, CREATE TABLE ON SCHEMA city.results TO `tamaat@example.com`;