Skip to main content

Google BigQuery Data Loader

Use a GoogleSQL query to load BigQuery rows into a BotDojo knowledge folder as JSON documents.

Prerequisites​

Connect Google BigQuery first by following the Google BigQuery integration guide. The connected Google account must be able to create query jobs in the configured project and read every referenced table.

If you do not know the available table paths, use the integration's dataset, table, and schema discovery tools before configuring the loader query.

Query Contract​

The Data Loader accepts Google Standard SQL beginning with SELECT or WITH.

Every result must include a unique column named id. You can alias an existing key:

SELECT
  CAST(order_id AS STRING) AS id,
  customer_id,
  status,
  total,
  updated_at
FROM `analytics.sales.orders`
WHERE updated_at >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 1 DAY)

BotDojo creates one JSON document per row:

  • the id value becomes the external document ID and file name
  • the complete row becomes the JSON content
  • the serialized row is used for content versioning

Rows with an empty id are skipped. Loader validation fails when the query result schema does not include an id column.

Create the Data Loader​

  1. Open Knowledge → Loaders.
  2. Click Create Loader.
  3. Select Google BigQuery.
  4. Choose the connected BigQuery integration.
  5. Enter a SELECT or WITH query that returns a unique id.
  6. Choose whether to Keep Previous Records:
    • enabled: preserve documents that are absent from the latest result
    • disabled: remove documents that are absent from the latest result
  7. Select the destination folder and update schedule.
  8. Test the loader configuration.
  9. Save and run the loader.

Scheduled loaders use the OAuth refresh token, so they continue to run without an interactive Google sign-in.

Query Design and Cost​

  • Add a time-window predicate for scheduled loads when a full scan is unnecessary.
  • Fully qualify tables as `project.dataset.table` unless the integration has a default dataset.
  • Keep the configured location consistent with every table referenced by the query.
  • Review query bytes in BigQuery before scheduling a frequent load.
  • Use a stable business key for id; changing it creates a new BotDojo document.

Manual Verification​

  1. Create a small BigQuery table with two rows and stable IDs.
  2. Create and test a loader query that aliases the stable key as id.
  3. Run the loader and confirm two JSON documents appear in the destination folder.
  4. Change a non-ID field in one row, rerun the loader, and confirm that document updates in place.
  5. Remove one source row and verify both Keep Previous Records modes.
  6. Remove the id alias and confirm loader validation blocks the configuration.

Troubleshooting​

  • Query must start with SELECT or WITH: Data Loaders intentionally reject DML and DDL.
  • Id is a required field: explicitly select or alias a unique column as id.
  • Integration not found: reselect an active Google BigQuery connection.
  • Permission denied: verify both project job permissions and dataset/table read permissions.
  • Location mismatch: update the integration location to match the queried datasets.