Export your data into BigQuery

This page describes how to upload retail product and user event data into BigQuery. After your data is in BigQuery, you'll be able to use it to perform sales forecasting with Vertex AI and to view the data in pre-built Looker dashboards.

If your retail data is already in product and user event tables in BigQuery, in Vertex AI Search for retail format, you can skip this page and go directly to Get dashboards that show key performance indicators and Generate sales forecasts from retail data. For more information about the format, see Product schema and About user events.

Before you begin

Before you can export your retail data into BigQuery, you must have completed the procedures in Before you begin. This includes importing your catalog information and your user events.

For more information about importing, see:

• Import catalog information
• Record real-time user events
• Import historical user events

Create a dataset in BigQuery

You need to create one or two datasets in BigQuery to hold your product and user event data. You can use one dataset to hold both types of data or you can create two datasets, one for each type of data.

You must create the datasets in the same project where you implemented Vertex AI Search for retail.

1. If you haven't used BigQuery in your project before, enable the BigQuery API and make sure that you have the IAM role that allows you to create datasets and tables.

   See Before you begin and Access control with IAM in the BigQuery documentation.

2. Create a dataset in BigQuery in the US (multiple regions in the United States) multi-region. For example, name it retail_data. If you create your dataset in a different region, exporting your data into BigQuery fails.

   For information about creating a dataset, see Creating datasets in the BigQuery documentation.

   This dataset will be used to hold the data table(s) that you export. The following procedures describe how to export.

3. Optional: To place your user event data in a separate dataset from your product data, create a second dataset. For example, name it retail_user_event_data.

Export your Vertex AI Search for retail catalog to a BigQuery table

Use the products.export method to export your retail catalog into a BigQuery table.

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by executing gcloud init or gcloud auth login, or by using Cloud Shell, which automatically logs you into the gcloud CLI.

Before using any of the request data, make the following replacements:

• PROJECT_ID: The ID of the Vertex AI Search for retail API project where you created the BigQuery dataset.
• BRANCH_ID: The ID of the catalog branch. Use default_branch to get data from the default branch. For more information, see Catalog branches.
• DATASET_ID: The name of the dataset that you created in Create a dataset in BigQuery. For example, use retail_data or retail_product_data. The dataset must be in the same project. Do not add the project ID to the datasetId field here.
• TABLE_ID_PREFIX: A prefix for the table ID. This prefix can't be an empty string. A suffix of retail_products is added to complete the table name. For example, if the prefix is test, the table is named test_retail_products.

Request JSON body:

{
  "outputConfig":
  {
    "bigqueryDestination":
    {
      "datasetId": "DATASET_ID",
        "tableIdPrefix": "TABLE_ID_PREFIX",
        "tableType": "view"
    }
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: Ensure you have set the GOOGLE_APPLICATION_CREDENTIALS environment variable to the path for your service account private key file.

Save the request body in a file named request.json. Run the following command in the terminal to create or overwrite this file in the current directory:

cat > request.json << 'EOF'
{
  "outputConfig":
  {
    "bigqueryDestination":
    {
      "datasetId": "DATASET_ID",
        "tableIdPrefix": "TABLE_ID_PREFIX",
        "tableType": "view"
    }
  }
}
EOF

Then execute the following command to send your REST request:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://retail.googleapis.com/v2alpha/projects/PROJECT_ID/locations/global/catalogs/default_catalog/branches/BRANCH_ID/products:export"

PowerShell (Windows)

Note: Ensure you have set the GOOGLE_APPLICATION_CREDENTIALS environment variable to the path for your service account private key file.

Save the request body in a file named request.json. Run the following command in the terminal to create or overwrite this file in the current directory:

@'
{
  "outputConfig":
  {
    "bigqueryDestination":
    {
      "datasetId": "DATASET_ID",
        "tableIdPrefix": "TABLE_ID_PREFIX",
        "tableType": "view"
    }
  }
}
'@  | Out-File -FilePath request.json -Encoding utf8

Then execute the following command to send your REST request:

$cred = gcloud auth application-default print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://retail.googleapis.com/v2alpha/projects/PROJECT_ID/locations/global/catalogs/default_catalog/branches/BRANCH_ID/products:export" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "name": "projects/PROJECT_NUMBER/locations/global/catalogs/default_catalog/branches/0/operations/17986570020347019923",
  "done": true,
  "response": {
    "@type": "type.googleapis.com/google.cloud.retail.v2alpha.ExportProductsResponse",
    "outputResult": {
      "bigqueryResult": [
        {
          "datasetId": "DATASET_ID",
          "tableId": "TABLE_ID_PREFIX_retail_products"
        }
      ]
    }
  }
}

Export your user events to a BigQuery table

Use the userEvents.export method to export your retail user events into a BigQuery table:

Note: The following command assumes that you have logged in to the gcloud CLI with your user account by executing gcloud init or gcloud auth login, or by using Cloud Shell, which automatically logs you into the gcloud CLI.

Before using any of the request data, make the following replacements:

• PROJECT_ID: The ID of the Vertex AI Search for retail API project where you created the BigQuery dataset.
• DATASET_ID: The name of the dataset that you created in Create a dataset in BigQuery. For example, use retail_data or retail_product_data.
• TABLE_ID_PREFIX: A prefix for the table ID. This prefix can't be an empty string. A suffix of retail_products is added to complete the table name. For example, if the prefix is test, the table is namedtest_retail_products.

Request JSON body:

{
  "outputConfig":
  {
    "bigqueryDestination":
    {
      "datasetId": "DATASET_ID",
        "tableIdPrefix": "TABLE_ID_PREFIX",
        "tableType": "view"
    }
  }
}

To send your request, expand one of these options:

curl (Linux, macOS, or Cloud Shell)

Note: Ensure you have set the GOOGLE_APPLICATION_CREDENTIALS environment variable to the path for your service account private key file.

Save the request body in a file named request.json. Run the following command in the terminal to create or overwrite this file in the current directory:

cat > request.json << 'EOF'
{
  "outputConfig":
  {
    "bigqueryDestination":
    {
      "datasetId": "DATASET_ID",
        "tableIdPrefix": "TABLE_ID_PREFIX",
        "tableType": "view"
    }
  }
}
EOF

Then execute the following command to send your REST request:

curl -X POST \
     -H "Authorization: Bearer $(gcloud auth application-default print-access-token)" \
     -H "Content-Type: application/json; charset=utf-8" \
     -d @request.json \
     "https://retail.googleapis.com/v2alpha/projects/PROJECT_ID/locations/global/catalogs/default_catalog/userEvents:export"

PowerShell (Windows)

Note: Ensure you have set the GOOGLE_APPLICATION_CREDENTIALS environment variable to the path for your service account private key file.

Save the request body in a file named request.json. Run the following command in the terminal to create or overwrite this file in the current directory:

@'
{
  "outputConfig":
  {
    "bigqueryDestination":
    {
      "datasetId": "DATASET_ID",
        "tableIdPrefix": "TABLE_ID_PREFIX",
        "tableType": "view"
    }
  }
}
'@  | Out-File -FilePath request.json -Encoding utf8

Then execute the following command to send your REST request:

$cred = gcloud auth application-default print-access-token
$headers = @{ "Authorization" = "Bearer $cred" }

Invoke-WebRequest `
    -Method POST `
    -Headers $headers `
    -ContentType: "application/json; charset=utf-8" `
    -InFile request.json `
    -Uri "https://retail.googleapis.com/v2alpha/projects/PROJECT_ID/locations/global/catalogs/default_catalog/userEvents:export" | Select-Object -Expand Content

You should receive a JSON response similar to the following:

{
  "name": "projects/PROJECT_NUMBER/locations/global/catalogs/default_catalog/operations/17203443067109586170",
  "done": true,
  "response": {
    "@type": "type.googleapis.com/google.cloud.retail.v2alpha.ExportUserEventsResponse",
    "outputResult": {
      "bigqueryResult": [
        {
          "datasetId": "DATASET_ID",
          "tableId": "TABLE_ID_PREFIX_retail_user_events"
        }
      ]
    }
  }
}

About the exported data

Here are things to know about the retail data that you export to BigQuery tables:

• The data that is exported to BigQuery tables in your project are authorized views, not materialized views.

• You cannot change or update the data in these tables.

• Products are refreshed hourly.

• User events are refreshed near-real time.

About the exported user event data

Here are things to know about the product information that is included with the exported user event data.

Product price information

The way product price information is returned by the userEvents.export method depends on the following:

• You included price information in your user event data at the time of ingestion. When you call the userEvents.export method, the price returned with a user event is the price of the product at the time of the event.

• You did not include price information with your user event data, but you included price information in your product data at the time of ingestion. When you call the userEvents.export method, the price returned with a user event is not necessarily the price of the product at the time of the event. It is the price found in your product data at the time of ingestion.

• You did not include price information with your user event data and there is no price information available in your product data. When you call the userEvents.export method, no price is returned with user events.

Other product information

All product information (except price) is joined to user event information at the time you call the userEvents.export method. Product values can change from the time of the user event to the time you call userEvents.export. For this reason, the non-price product values returned in the user event table could be different from the product values at the time of the user event.

Optional: Confirm that the new tables are in BigQuery

After you have exported your product data and user-event data to BigQuery, make sure that new tables are present.

1. In BigQuery, navigate to the dataset or datasets that you created in Create dataset in BigQuery.

2. Open the dataset(s) and make sure you see the two tables that you exported. For example, look for tables with names ending in retail_products and retail_user_events.