Tutorial: Update data and files using a connection

This tutorial provides step-by-step instructions for using Web Request jobs to update records and files via Google APIs.

Prerequisites

Before continuing with this tutorial, ensure you have completed the following prerequisites:

  1. Open a Snowflake worksheet and execute the following SQL statements using a role with the necessary privileges:

    CREATE WAREHOUSE IF NOT EXISTS LEARN_SW_WH WAREHOUSE_SIZE=XSMALL AUTO_SUSPEND=60;
CREATE ROLE IF NOT EXISTS LEARN_DEV_DEVELOPER;
GRANT USAGE ON WAREHOUSE LEARN_SW_WH TO ROLE LEARN_DEV_DEVELOPER;
GRANT ROLE LEARN_DEV_DEVELOPER TO USER <Your User Account>;

Create an OAuth integration

  1. Access the Admin Console with the owner role of Snoweaver or a custom role with the SNOWEAVER.APP_ADMIN application role.

  2. Open the OAuth Integrations page.

  3. Configure a new integration with the following settings:

    Name:                         google_oauth
OAuth Client ID:              <Consumer Key>
OAuth Client Secret:          <Consumer Secret>
OAuth Token Endpoint:         https://oauth2.googleapis.com/token
OAuth Authorization Endpoint: https://accounts.google.com/o/oauth2/v2/auth
OAuth Allowed Scopes:         https://www.googleapis.com/auth/drive
                              https://www.googleapis.com/auth/calendar
    ../_images/61.png

    Tip

    Refer to the Enable Google APIs and Obtain OAuth Credentials guide for instructions on obtaining a consumer key and a consumer secret.

  4. Click Generate Script to create a SQL script for setting up the Snowflake OAuth Integration and granting Snoweaver access.

  5. Run the generated script in a Snowflake worksheet. Ensure that the role executing the script has the CREATE INTEGRATION privilege, such as the AccountAdmin role.

    Currently, Snowflake restricts native applications from requesting privileges to create security integrations on consumer accounts. This limitation may be lifted in the future, potentially enabling automation of this process.

  6. Return to Snoweaver and register the new OAuth integration via the sidebar menu.

    ../_images/71.png

Create an OAuth secret

  1. Open the Secrets page.

  2. Configure a new secret with the following settings:

    Name:                   google_oauth_token
Secreet Type:           OAuth2
Refresh Token:          <Refresh Token>
OAuth Allowed Scopes:   https://www.googleapis.com/auth/drive
                        https://www.googleapis.com/auth/calendar
OAuth API Integration:  google_oauth

    Tip

    Refer to the Enable Google APIs and Obtain OAuth Credentials guide for instructions on obtaining a refresh token.

  3. Save the new secret.

Create a Network Rule

  1. Access the Snoweaver application with the owner role or a custom role with the SNOWEAVER.APP_ADMIN application role.

  2. Open the Network Rules page.

  3. Apply the following configuration to create a new network rule:

    Name:             google_access_rule
Allowed Domains:  www.googleapis.com

  4. Save the Network Rule.

Create an external integration

  1. Open the External Integrations page.

  2. Apply the following configuration to create a new external integration:

    Name:                   google_access_integration
Allowed Secrets:        google_oauth_token
Allowed Network Rules:  google_access_rule

  3. Click Generate Script to generate a SQL script that creats a new Snowflake External Access Integration and grants Snoweaver access.

  4. Run the Script in a Snowflake worksheet. Ensure that the executing role has the CREATE INTEGRATION privilege, such as the AccountAdmin role.

    Note

    Currently, Snowflake does not support native applications requesting permissions to create integrations on consumer accounts. This step may be automated in the future should this restriction be lifted.

  5. Return to Snoweaver and register the new external access integraiton via the sidebar

Create a new connection

  1. Open the Connections page.

  2. Apply the following configuration:

    Name:                         google
Base URL:                     https://www.googleapis.com/
External Access Integration:  google_access_integration
Secrets:                      google_oauth_token

  3. Save the connection.

Create a new Calendar event

  1. Open a Snowflake worksheet and execute the following SQL statements with the owner role or a custom role with the SNOWEAVER.APP_ADMIN application role:

    GRANT APPLICATION ROLE SNOWEAVER.GOOGLE_OPERATOR TO ROLE LEARN_DEV_DEVELOPER;

  2. Execute the following SQL statements to preview the content of the request:

    USE ROLE LEARN_DEV_DEVELOPER;
USE SCHEMA SNOWEAVER.GOOGLE;

-- Preview the content of the request
SELECT
    request(
        type => 'post',
        endpoint => 'calendar/v3/calendars/[Your Google Account]@gmail.com/events',
        headers => {
            'Authorization': 'Bearer {{ secrets.google_oauth_token }}'
        },
        body => {
            'start' : {
                'dateTime': TO_VARCHAR(CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()), 'YYYY-MM-DD"T"HH24:MI:SS'),
                'timeZone': 'UTC'
            },
            'end' : {
                'dateTime': TO_VARCHAR(CONVERT_TIMEZONE('UTC', DATEADD(hour, 1,CURRENT_TIMESTAMP())), 'YYYY-MM-DD"T"HH24:MI:SS'),
                'timeZone': 'UTC'
            },
            'summary': 'Test Event'
        },
        preview => TRUE
    ):data::varchar;
    ../_images/10.png

  3. Execute the following SQL statements to create a new calendar event:

    -- Create a new calendar event and store the response in a temporary table
create or replace temporary table results as
select request(
    type => 'post',
    endpoint => 'calendar/v3/calendars/[Your Google Account]@gmail.com/events',
    headers => {
        'Authorization': 'Bearer {{ secrets.google_oauth_token }}'
    },
    body => {
        'start' : {
            'dateTime': TO_VARCHAR(CONVERT_TIMEZONE('UTC', CURRENT_TIMESTAMP()), 'YYYY-MM-DD"T"HH24:MI:SS'),
            'timeZone': 'UTC'
        },
        'end' : {
            'dateTime': TO_VARCHAR(CONVERT_TIMEZONE('UTC', DATEADD(hour, 1,CURRENT_TIMESTAMP())), 'YYYY-MM-DD"T"HH24:MI:SS'),
            'timeZone': 'UTC'
        },
        'summary': 'Test Event'
    }
) as response;

-- Analyze the response
select
    response:success,
    response:data:status_code,
    response:data:headers,
    response:data:payload
from results;

-- Retrieve the newly created event using its ID from the previous response
select request(
    type => 'get',
    endpoint => 'calendar/v3/calendars/[Your Google Account]@gmail.com/events/' || (select response:data:payload:id from results),
    headers => {
        'Authorization': 'Bearer {{ secrets.google_oauth_token }}'
    }
) as response;
    ../_images/111.png

Delete a Calendar event

  1. Execute the following SQL statements to delete the event created in the previous step:

    -- Delete the newly created event using its ID from the previous response
select request(
    type => 'delete',
    endpoint => 'calendar/v3/calendars/[Your Google Account]@gmail.com/events/' || (select response:data:payload:id from results),
    headers => {
        'Authorization': 'Bearer {{ secrets.google_oauth_token }}'
    }
);

Upload a file to Google Drive

  1. Execute the following SQL statements to preview the content of the upload request:

    -- Create a temporary table with sample data
CREATE OR REPLACE TEMPORARY TABLE ACCOUNTS AS
SELECT '789' AS EXT_ID, 'Grand Hotels & Resorts Ltd' AS NAME
UNION
SELECT '890', 'Express Logistics and Transport';

-- Copy data from the temporary table into a CSV file in the stage
COPY INTO @google.data/drive/accounts.csv.gz
FROM ACCOUNTS
FILE_FORMAT = (TYPE = CSV COMPRESSION = GZIP FIELD_OPTIONALLY_ENCLOSED_BY='"')
HEADER = TRUE
OVERWRITE = TRUE
SINGLE = TRUE;

-- Declare a variable to store the result of the request
DECLARE
    response variant;
BEGIN
    -- Call the request function to upload the file to Google Drive
    response := (
        CALL request(
            type => 'post',
            endpoint => 'upload/drive/v3/files',
            parameters => {'uploadType': 'media'},
            headers => {
                'Authorization': 'Bearer {{ secrets.google_oauth_token }}'
            },
            filepath => 'drive/accounts.csv.gz',
            preview => true
        )
    );
    -- Return the data from the result as a string
    RETURN response:data::varchar;
END;
    ../_images/12.png

  2. Execute the following SQL statements to upload the file and update its metadata:

    DECLARE
   response variant;
BEGIN
   -- Upload the file to Google Drive
   response := (CALL request(
      type => 'post',
      endpoint => 'upload/drive/v3/files',
      parameters => {'uploadType':'media'},
      headers => {
            'Authorization': 'Bearer {{ secrets.google_oauth_token }}'
      },
      filepath => 'drive/accounts.csv.gz',
      compress_decompress => true
   ));

   -- Update the file metadata (name)
   RETURN (SELECT request(
      type => 'patch',
      endpoint => 'drive/v3/files/' || :response:data:payload:id,
      headers => {
            'Authorization': 'Bearer {{ secrets.google_oauth_token }}'
      },
      body => {
            'name': 'accounts.csv'
      }
   )):data:payload;
END;