Installing and configuring the Snowflake Connector for Google Analytics Raw Data

This topic provides information on installing and configuring the Snowflake Connector for Google Analytics Raw Data through Snowsight.

Installing the Snowflake Connector for Google Analytics Raw Data

To install the connector, do the following:

  1. Sign in to Snowsight as a user with the ACCOUNTADMIN role.

  2. In the navigation menu, select Data Products » Marketplace.

  3. Search for the Snowflake Connector for Google Analytics Raw Data, then select the tile for the connector.

  4. In the page for the Snowflake Connector for Google Analytics Raw Data, select Get.

    This displays a dialog box that you use to begin the initial part of the installation process.

    In the dialog box configure the following:

    1. In the Options->Application name field, enter the database to use as the database for the connector instance. This database is created for you automatically.

    2. In the Warehouse used for installation field, select the warehouse that you want to use for installing the connector.

      Note

      This is not the same warehouse that is used by the connector to synchronize data from Google Analytics. In a later step, you will create a separate warehouse for this purpose.

    3. Select Get.

  5. Select Open.

    The dialog box closes, and the Snowflake Connector for Google Analytics Raw Data page displays the UI for configuring and managing the connector.

Configuring the Snowflake Connector for Google Analytics Raw Data

To configure the connector, do the following:

  1. Sign in to Snowsight as a user with the ACCOUNTADMIN role.

  2. In the navigation menu, select Data Products » Apps.

  3. Select the Snowflake Connector for Google Analytics Raw Data.

    The configuration wizard starts.

  4. Prerequisites

    1. Make sure all prerequisites from the list are met and mark them done.

    2. Click Start configuration

  5. Configure warehouse, database, schema and role

    Note

    By default, the fields are set to the names of objects that are created when you configure the connector. Snowflake recommends using new objects for these fields. However, you can specify the names of existing objects, if needed (e.g. if you are reinstalling the connector).

    Populate the following fields and select Configure at the bottom of the screen:

    Field

    Description

    Warehouse

    Enter the identifier for a new, dedicated virtual warehouse for the connector or select an existing one.

    Specify a name that is unique for your account. The name of the warehouse must be a valid object identifier.

    Note

    Do not specify the same warehouse that you selected at the beginning of the connector installation.

    The configuration process creates a new X-Small warehouse with the specified name.

    Alternatively you can select an existing warehouse.

    Destination Database

    Identifier for a new database that will contain the schema with the tables for the Google Analytics data in Snowflake. Data downloaded from Google Analytics will land here.

    Specify a name that is unique for your account. The name of the database must be a valid object identifier.

    The configuration process creates a new database with the specified name.

    Alternatively you can select an existing database.

    Destination Schema

    Identifier for a new schema that will contain the Google Analytics data in Snowflake.

    The Snowflake Connector for Google Analytics Raw Data ingests Google Analytics data into tables in this schema.

    The name of the schema must be a valid object identifier.

    The configuration process creates a new schema with the specified name.

    Alternatively you can select an existing schema.

    Role

    Identifier for a new custom role for the connector.

    Specify a name that is unique for your account. The name of the role must be a valid object identifier.

    The role is an account-level role that will have read access to the ingested data.

    Alternatively you can select an existing role.

    It can take some time for the configuration process to complete. When the configuration process finishes successfully, the configuration wizard advances to Authentication.

  6. Configure authentication

    The Snowflake Connector for Google Analytics Raw Data support two authentication methods - OAuth and Service Accounts. Each of methods requires additional configuration in your GCP project.

    For more information on how to configure each authentication see:

    If using authentication method Service Account, provide a JSON file with Service Account credentials.

    Alternatively you can populate the following fields:

    Field

    Description

    Client email

    Google service account email which was generated during service account creation process in Google Cloud Platform project.

    Private key

    Private key which was generated during service account creation process in Google Cloud Platform project.

    Ensure you have removed the —–BEGIN PRIVATE KEY—–, —–END PRIVATE KEY—–, and \n symbols.

    If using authentication method Oauth2, populate the following fields:

    Field

    Description

    Client id

    Client ID generated in Google Cloud Platform project.

    Client secret

    Client secret ID generated in Google Cloud Platform project.

    Select Connect

    If you have selected Oauth2 authentication, you will be presented with the Google OAuth2 authentication dialog flow.

    In the dialog, log in to Google to complete the Google OAuth2 authentication flow.

    It can take some time for the authentication process to complete.

  7. Validate source

After successfully connection, the conenctor will verify that it can access the Google Analytics data. On error, the connector will guide you with additional instruction.

If the process completes successfully you can start configuring ingestion. For more information see Setting up data ingestion for your Snowflake Connector for Google Analytics Raw Data

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

To configure the connector using SQL statements, do the following:

Note

In order to provision the connector and configure connection you will have to use stored procedures that are defined in the PUBLIC schema of the database that serves as an instance of the connector installation database.

Before calling these stored procedures, select that database as the database to use for the session.

For example, if that database is named snowflake_connector_for_google_analytics_raw_data, run the following command:

USE DATABASE snowflake_connector_for_google_analytics_raw_data;
Copy

Prepare a warehouse, data owner role and destination database

  1. 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;
    
    Copy
  2. 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
  3. 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
  4. 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

Provision the connector

  1. 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 Snowflake objects required for connecting to the GCP

  1. 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
    snowflake_connector_for_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
  2. 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 = snowflake_connector_for_google_analytics_raw_data_security_integration;
    
    Copy
  3. 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
  4. 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

Configure connection with the GCP

  1. Call the CONFIGURE_CONNECTION procedure.

    Pass the name of the external access integration, and the full path to the secret. 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'
    );
    
    Copy
  2. 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.