Configuring the Snowflake Connector for Google Analytics Raw Data using SQL¶

Grant usage on specified warehouse and task execution permissions to the connector application.

USE ROLE accountadmin;
CREATE WAREHOUSE google_analytics_raw_data_warehouse with warehouse_size = 'X-Small';
GRANT USAGE ON WAREHOUSE google_analytics_raw_data_warehouse TO APPLICATION snowflake_connector_for_google_analytics_raw_data;
GRANT EXECUTE TASK ON ACCOUNT TO APPLICATION snowflake_connector_for_google_analytics_raw_data;
GRANT EXECUTE MANAGED TASK ON ACCOUNT TO APPLICATION snowflake_connector_for_google_analytics_raw_data;

Copy

Create the data owner role.

USE ROLE accountadmin;
CREATE OR REPLACE ROLE google_analytics_raw_data_resources_provider;
GRANT CREATE DATABASE ON ACCOUNT TO ROLE google_analytics_raw_data_resources_provider;
GRANT USAGE ON WAREHOUSE google_analytics_raw_data_warehouse TO ROLE google_analytics_raw_data_resources_provider;
GRANT ROLE google_analytics_raw_data_resources_provider TO USER ADMIN;

Copy

Create a destination database and schema.

You may also use an existing destination database and schema – especially if you’re re-installing the connector.

USE ROLE google_analytics_raw_data_resources_provider;
CREATE DATABASE google_analytics_raw_data_dest_db;
CREATE SCHEMA google_analytics_raw_data_dest_db.google_analytics_raw_data_dest_schema;

Copy

Add required grants on the destination database to the application.

USE ROLE accountadmin;
GRANT USAGE ON DATABASE google_analytics_raw_data_dest_db TO APPLICATION snowflake_connector_for_google_analytics_raw_data;
GRANT USAGE ON SCHEMA google_analytics_raw_data_dest_db.google_analytics_raw_data_dest_schema TO APPLICATION snowflake_connector_for_google_analytics_raw_data;

GRANT CREATE TABLE ON SCHEMA google_analytics_raw_data_dest_db.google_analytics_raw_data_dest_schema TO APPLICATION snowflake_connector_for_google_analytics_raw_data;
GRANT CREATE VIEW ON SCHEMA google_analytics_raw_data_dest_db.google_analytics_raw_data_dest_schema TO APPLICATION snowflake_connector_for_google_analytics_raw_data;

Copy

Call the PROVISION_CONNECTOR procedure.

Pass the name of the warehouse, destination database and schema, and data owner role. These values are case-sensitive.

CALL PROVISION_CONNECTOR(
    'GOOGLE_ANALYTICS_RAW_DATA_WAREHOUSE',
    'GOOGLE_ANALYTICS_RAW_DATA_DEST_DB.GOOGLE_ANALYTICS_RAW_DATA_DEST_SCHEMA',
    'GOOGLE_ANALYTICS_RAW_DATA_RESOURCES_PROVIDER'
);

Copy

Create a security integration for your service account.

First, you need a service account key file. For details on how to create one see Configuring service account authentication for Google Cloud Platform (GCP)

CREATE SECURITY INTEGRATION
google_analytics_raw_data_security_integration
type = api_authentication
auth_type = oauth2
oauth_client_id = '<value of client_id from the JSON key file>'
oauth_token_endpoint = 'https://oauth2.googleapis.com/token'
enabled = true
oauth_allowed_scopes = (
    'https://www.googleapis.com/auth/bigquery.readonly',
    'https://www.googleapis.com/auth/cloudplatformprojects.readonly'
)
oauth_assertion_issuer = '<value of client_email from the JSON key file>'
oauth_grant='JWT_BEARER'
oauth_client_secret = '<value of private_key from the JSON key file with no delimiters or newlines>';

Copy

Create a secret using the security integration.

CREATE DATABASE google_analytics_raw_data_connector_secret;
CREATE SCHEMA google_analytics_raw_data_connector_secret.oauth;

USE SCHEMA google_analytics_raw_data_connector_secret.oauth;

CREATE OR REPLACE SECRET google_analytics_raw_data
type = oauth2
api_authentication = google_analytics_raw_data_security_integration;

Copy

Provide secret-related grants to the connector application.

GRANT USAGE ON DATABASE google_analytics_raw_data_connector_secret TO APPLICATION snowflake_connector_for_google_analytics_raw_data;
GRANT USAGE ON SCHEMA google_analytics_raw_data_connector_secret.oauth TO APPLICATION snowflake_connector_for_google_analytics_raw_data;
GRANT READ ON SECRET google_analytics_raw_data_connector_secret.oauth.google_analytics_raw_data TO APPLICATION snowflake_connector_for_google_analytics_raw_data;

Copy

Configure external access.

Keep in mind, that the path to the secret passed to allowed_authentication_secrets is case-sensitive.

USE SCHEMA google_analytics_raw_data_connector_secret.oauth;

CREATE NETWORK RULE
google_analytics_raw_data_allow_rule
mode = EGRESS
type = HOST_PORT
value_list = (
    'www.googleapis.com',
    'bigquery.googleapis.com',
    'bigquerystorage.googleapis.com',
    'cloudresourcemanager.googleapis.com',
    'oauth2.googleapis.com'
);

CREATE EXTERNAL ACCESS INTEGRATION
google_analytics_raw_data_external_access_integration
allowed_network_rules = (google_analytics_raw_data_allow_rule)
allowed_authentication_secrets = ('GOOGLE_ANALYTICS_RAW_DATA_CONNECTOR_SECRET.OAUTH.GOOGLE_ANALYTICS_RAW_DATA')
enabled = true;

GRANT USAGE ON INTEGRATION google_analytics_raw_data_external_access_integration TO APPLICATION snowflake_connector_for_google_analytics_raw_data;

Copy

Call the CONFIGURE_CONNECTION procedure.

Pass the name of the external access integration, the full path to the secret, and the name of the security integration. These values are case sensitive.

CALL CONFIGURE_CONNECTION(
    'GOOGLE_ANALYTICS_RAW_DATA_EXTERNAL_ACCESS_INTEGRATION',
    'GOOGLE_ANALYTICS_RAW_DATA_CONNECTOR_SECRET.OAUTH.GOOGLE_ANALYTICS_RAW_DATA',
    'GOOGLE_ANALYTICS_RAW_DATA_SECURITY_INTEGRATION'
);

Copy

Check the connection status.

CALL CONNECTION_STATUS();

Copy

If there are no errors, you can follow Setting up data ingestion for your Snowflake Connector for Google Analytics Raw Data to enable your Google Analytics properties.