Sync from Cloud SQL

To ingest data from Cloud SQL, use the following steps to set up Cloud SQL access, create a data store, and ingest data.

Set up staging bucket access for Cloud SQL instances

When ingesting data from Cloud SQL, data is first staged to a Cloud Storage bucket. Follow these steps to give a Cloud SQL instance access to Cloud Storage buckets.

  1. In the Google Cloud console, go to the SQL page.

    SQL

  2. Click the Cloud SQL instance that you plan to import from.

  3. Copy the identifier for the instance's service account, which looks like an email address—for example, p9876-abcd33f@gcp-sa-cloud-sql.iam.gserviceaccount.com.

  4. Go to the IAM & Admin page.

    IAM & Admin

  5. Click Grant access.

  6. For New principals, enter the instance's service account identifier and select the Cloud Storage > Storage Admin role.

  7. Click Save.

Next:

Set up Cloud SQL access from a different project

To give Agentspace Enterprise access to Cloud SQL data that's in a different project, follow these steps:

  1. Replace the following PROJECT_NUMBER variable with your Agentspace Enterprise project number, and then copy the contents of the code block. This is your Agentspace Enterprise service account identifier:

    service-PROJECT_NUMBER@gcp-sa-discoveryengine.iam.gserviceaccount.com`

  2. Go to the IAM & Admin page.

    IAM & Admin

  3. Switch to your Cloud SQL project on the IAM & Admin page and click Grant Access.

  4. For New principals, enter the identifier for the service account and select the Cloud SQL > Cloud SQL Viewer role.

  5. Click Save.

Next, go to Import data from Cloud SQL.

Import data from Cloud SQL

Console

To use the console to ingest data from Cloud SQL, follow these steps:

  1. In the Google Cloud console, go to the Agentspace page.

    Agentspace

  2. Go to the Data Stores page.

  3. Click New data store.

  4. On the Source page, select Cloud SQL.

  5. Specify the project ID, instance ID, database ID, and table ID of the data that you plan to import.

  6. Click Browse and choose an intermediate Cloud Storage location to export data to, and then click Select. Alternatively, enter the location directly in the gs:// field.

  7. Select whether to turn on serverless export. Serverless export incurs additional cost. For information about serverless export, see Minimize the performance impact of exports in the Cloud SQL documentation.

  8. Click Continue.

  9. Choose a region for your data store.

  10. Enter a name for your data store.

  11. Click Create.

  12. To check the status of your ingestion, go to the Data Stores page and click your data store name to see details about it on its Data page. When the status column on the Activity tab changes from In progress to Import completed, the ingestion is complete.

    Depending on the size of your data, ingestion can take several minutes or several hours.

REST

To use the command line to create a data store and ingest data from Cloud SQL, follow these steps:

  1. Create a data store.

    curl -X POST \
-H "Authorization: Bearer $(gcloud auth print-access-token)" \
-H "Content-Type: application/json" \
-H "X-Goog-User-Project: PROJECT_ID" \
"https://discoveryengine.googleapis.com/v1alpha/projects/PROJECT_ID/locations/global/collections/default_collection/dataStores?dataStoreId=DATA_STORE_ID" \
-d '{
  "displayName": "DISPLAY_NAME",
  "industryVertical": "GENERIC",
  "solutionTypes": ["SOLUTION_TYPE_SEARCH"],
}'

    Replace the following:

    • PROJECT_ID: the ID of your project.
    • DATA_STORE_ID: the ID of the data store. The ID can contain only lowercase letters, digits, underscores, and hyphens.
    • DISPLAY_NAME: the display name of the data store. This might be displayed in the Google Cloud console.

  2. Import data from Cloud SQL.

      curl -X POST \
  -H "Authorization: Bearer $(gcloud auth print-access-token)" \
  -H "Content-Type: application/json" \
  "https://discoveryengine.googleapis.com/v1/projects/PROJECT_ID/locations/global/collections/default_collection/dataStores/DATA_STORE_ID/branches/0/documents:import" \
  -d '{
    "cloudSqlSource": {
      "projectId": "SQL_PROJECT_ID",
      "instanceId": "INSTANCE_ID",
      "databaseId": "DATABASE_ID",
      "tableId": "TABLE_ID",
      "gcsStagingDir": "STAGING_DIRECTORY"
    },
    "reconciliationMode": "RECONCILIATION_MODE",
    "autoGenerateIds": "AUTO_GENERATE_IDS",
    "idField": "ID_FIELD",
  }'

    Replace the following:

    • PROJECT_ID: the ID of your Agentspace Enterprise project.
    • DATA_STORE_ID: the ID of the data store. The ID can contain only lowercase letters, digits, underscores, and hyphens.
    • SQL_PROJECT_ID: the ID of your Cloud SQL project.
    • INSTANCE_ID: the ID of your Cloud SQL instance.
    • DATABASE_ID: the ID of your Cloud SQL database.
    • TABLE_ID: the ID of your Cloud SQL table.
    • STAGING_DIRECTORY: optional. A Cloud Storage directory—for example, gs://<your-gcs-bucket>/directory/import_errors.
    • RECONCILIATION_MODE: optional. Values are FULL and INCREMENTAL. Default is INCREMENTAL. Specifying INCREMENTAL causes an incremental refresh of data from Cloud SQL to your data store. This does an upsert operation, which adds new documents and replaces existing documents with updated documents with the same ID. Specifying FULL causes a full rebase of the documents in your data store. In other words, new and updated documents are added to your data store, and documents that are not in Cloud SQL are removed from your data store. The FULL mode is helpful if you want to automatically delete documents that you no longer need.

Next steps

  • To attach your data store to an app, create an app and select your data store following the steps in Create a search app.

  • To preview how your search results appear after your app and data store are set up, see Preview search results.