Set up the Openflow Connector for Google Drive¶

Prerequisites¶

Ensure that you meet the following requirements:

• You have a Google user with Super Admin permissions

• You have a Google Cloud Project with the following roles:

  • Organization Policy Administrator

  • Organization Administrator

Enable service account key creation¶

By default Google disables service account key creation. For Openflow to use the service account JSON, this key creation policy must be turned off.

1. Log in to the Google Cloud Console with a super admin account that has the Organizational Policy Admin Role.

2. Ensure you are in the project associated with your organization, not the project in your organization.

3. Click Organization Policies.

4. Select the Disable service account key creation policy.

5. Click Manage Policy and turn off enforcement.

6. Click Set Policy.

Create service account and key¶

1. Open the Google Cloud Console and authenticate using a user that has been granted access to create service accounts.

2. Ensure you are in a project of your organization.

3. In the left navigation, under the IAM & Admin, select the Service Accounts tab.

4. Click Create Service Account.

5. Enter the service account name and click Create and Continue.

6. Click Done. In the table with the service accounts listed, find the OAuth 2 Client ID column. Copy the Client ID as this will be required later to set up domain-wide delegation in the next section.

7. On the newly created service account, click the menu under the table with the service accounts listed for that service account and select Manage keys.

8. Select Add key and then Create new key.

9. Leave the default selection of JSON and click Create.

The key is downloaded into your browser Downloads directory as a .json file.

Grant service account domain-wide delegation for listed scopes¶

1. Log in to your Google Admin account.

2. Select Admin from Google Apps selector.

3. In the left navigation, expand Security and then Access and select Data control then click on API Controls.

4. On the API Controls screen, select Manage domain wild delegation.

5. Click Add new.

6. Enter the OAuth 2 Client ID taken from the Create Service Account and Key section and the following scopes:

   • https://www.googleapis.com/auth/drive

   • https://www.googleapis.com/auth/drive.metadata.readonly

   • https://www.googleapis.com/auth/admin.directory.group.member.readonly

   • https://www.googleapis.com/auth/admin.directory.group.readonly

   • https://www.googleapis.com/auth/drive.file

   • https://www.googleapis.com/auth/drive.metadata

7. Click Authorize.

Example setup¶

--The following script assumes you'll need to create all required roles, users, and objects.
--However, you may want to reuse some that are already in existence.

--Create a Snowflake service user to manage the connector
USE ROLE USERADMIN;
CREATE USER <openflow_service_user> TYPE=SERVICE COMMENT='Service user for Openflow automation';

--Create a pair of secure keys (public and private). For more information, see
--key-pair authentication. Store the private key for the user in a file to supply
--to the connector’s configuration. Assign the public key to the Snowflake service user:
ALTER USER <openflow_service_user> SET RSA_PUBLIC_KEY = '<pubkey>';


--Create a role to manage the connector and the associated data and
--grant it to that user
USE ROLE SECURITYADMIN;
CREATE ROLE <openflow_connector_admin_role>;
GRANT ROLE <openflow_connector_admin_role> TO USER <openflow_service_user>;


--The following block is for USE CASE 2 (Cortex connect) ONLY
--Create a role for read access to the cortex search service created by this connector.
--This role should be granted to any role that will use the service
CREATE ROLE <cortex_search_service_read_only_role>;
GRANT ROLE <cortex_search_service_read_only_role> TO ROLE <whatever_roles_will_access_search_service>;

--Create the database the data will be stored in and grant usage to the roles created
USE ROLE ACCOUNTADMIN; --use whatever role you want to own your DB
CREATE DATABASE IF NOT EXISTS <destination_database>;
GRANT USAGE ON DATABASE <destination_database> TO ROLE <openflow_connector_admin_role>;

--Create the schema the data will be stored in and grant the necessary privileges
--on that schema to the connector admin role:
USE DATABASE <destination_database>;
CREATE SCHEMA IF NOT EXISTS <destination_schema>;
GRANT USAGE ON SCHEMA <destination_schema> TO ROLE <openflow_connector_admin_role>;
GRANT CREATE TABLE, CREATE DYNAMIC TABLE, CREATE STAGE, CREATE SEQUENCE, CREATE CORTEX
SEARCH SERVICE ON SCHEMA <destination_schema> TO ROLE <openflow_connector_admin_role>;

--The following block is for CASE 2 (Cortex connect) ONLY
--Grant the Cortex read-only role access to the database and schema
GRANT USAGE ON DATABASE <destination_database> TO ROLE <cortex_search_service_read_only_role>;
GRANT USAGE ON SCHEMA <destination_schema> TO ROLE <cortex_search_service_read_only_role>;

--Create the warehouse this connector will use if it doesn't already exist. Grant the
--appropriate privileges to the connector admin role. Adjust the size according to your needs.
CREATE WAREHOUSE <openflow_warehouse>
WITH
   WAREHOUSE_SIZE = 'MEDIUM'
   AUTO_SUSPEND = 300
   AUTO_RESUME = TRUE;
GRANT USAGE, OPERATE ON WAREHOUSE <openflow_warehouse> TO ROLE <openflow_connector_admin_role>;

Copy

Set up the connector¶

As a data engineer, perform the following tasks to install and configure the connector:

Set up the connector¶

As a data engineer, perform the following tasks to install and configure the connector:

Set up the connector¶

As a data engineer, perform the following tasks to install and configure the connector:

Run the flow¶

1. Run the flow.

   1. Start the process group. The flow will create all required objects inside of Snowflake.

   2. Right click on the imported process group and select Start.

2. Query the Cortex Search service.

SELECT PARSE_JSON(
  SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
    '<application_instance_name>.cortex.search_service',
      '{
        "query": "<your_question>",
         "columns": ["chunk", "web_url"],
         "filter": {"@contains": {"user_emails": "<user_emailID>"} },
         "limit": <number_of_results>
       }'
   )
)['results'] AS results

SELECT PARSE_JSON(
     SNOWFLAKE.CORTEX.SEARCH_PREVIEW(
          '<application_instance_name>.cortex.search_service',
          '{
             "query": "What is my vacation carry over policy?",
             "columns": ["chunk", "web_url"],
             "filter": {"@contains": {"user_emails": "<user_emailID>"} },
             "limit": 1
          }'
     )
 )['results'] AS results

import snowflake.snowpark as snowpark
from snowflake.snowpark import Session
from snowflake.core import Root

def main(session: snowpark.Session):

   root = Root(session)

   # fetch service
   my_service = (root
     .databases["<application_instance_name>"]
     .schemas["cortex"]
     .cortex_search_services["search_service"]
   )

   # query service
   resp = my_service.search(
     query="What is my vacation carry over policy?",
     columns = ["chunk", "web_url"],
     filter = {"@contains": {"user_emails": "<user_emailID>"} },
     limit=1
   )
   return (resp.to_json())

curl --location "https://<account_url>/api/v2/databases/<application_instance_name>/schemas/cortex/cortex-search-services/search_service" \
     --header 'Content-Type: application/json' \
     --header 'Accept: application/json' \
     --header "Authorization: Bearer <CORTEX_SEARCH_JWT>" \
     --data '{
         "query": "What is my vacation carry over policy?",
         "columns": ["chunk", "web_url"],
         "limit": 1
     }'