Set up the Openflow Connector for Google Drive¶

Prerequisites¶

• 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.

Configure the connector¶

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

1. Create a database and schema in Snowflake for the connector to store ingested data.

2. Download the connector definition file for setting up and running the connector. This flow fetches data from Google, uploads them to a stage, and processes additional metadata about permissions and file information, which is uploaded to Snowflake tables.

3. Import the connector definition into Openflow:

   1. Open the Snowflake Openflow canvas.

   2. Add a process group. To do this, drag and drop the Process Group icon from the tool palette at the top of the page onto the canvas. Once you release your pointer, a Create Process Group dialog appears.

   3. On the Create Process Group dialog, select the connector definition file to import.

4. Populate the process group parameters

   1. Right-click on the imported process group and select Parameters.

   2. Enter the required parameter values as described in Flow parameters: Ingest files only.

Configure the connector¶

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

1. Create a database and schema in Snowflake for the connector to store ingested data.

2. Download the connector definition files:

   1. Download this connector definition file for setting up and running the connector. This flow fetches data from Google, runs parsing and chunking processes, updates the Cortex Search service.

   2. Download this connector definition file for managing the connector. This can be used to toggle Cortex Search service indexing and to clean up the connector state.

3. Import the connector definition into Openflow:

   1. Open the Snowflake Openflow canvas.

   2. Add a process group. To do this, drag and drop the Process Group icon from the tool palette at the top of the page onto the canvas. Once you release your pointer, a Create Process Group dialog appears.

   3. On the Create Process Group dialog, select the connector definition file to import.

4. Populate the process group parameters

   1. Right-click on the imported process group and select Parameters.

   2. Enter the required parameter values as described in Flow parameters: Ingest files and perform processing with Cortex.

Configure the connector¶

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

1. Create a database and schema in Snowflake for the connector to store ingested data.

2. Download the connector definition files:

   1. Download this connector definition file for setting up and running the connector. This flow fetches data from Google, runs parsing and chunking processes, updates the Cortex Search service.

   2. Download this connector definition file for managing the connector. This can be used to toggle Cortex Search service indexing and to clean up the connector state.

3. Import the connector definition into Openflow.

   1. Enter an Openflow canvas.

   2. Add a process group to the canvas.

   3. On the Create Process Group pop-up, select the connector definition file to import.

4. Customize the connector definition.

   1. Remove the following Process groups: Check If Duplicate Content, Snowflake Stage and Parse PDF, Update Snowflake Cortex.

   2. Attach any custom processing to the output of the Process Google Drive Metadata process group. Each flow file represents a single Google Drive file change. Flow file attributes can be seen in the FetchGoogleDriveMetadata documentation.

5. Populate the process group parameters. Follow the same process as for the use case 1. Note that after modifying the connector definition, not all parameters might be required.

6. 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.

7. 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
     }'