Set up process #

kind: source
spec:
  name: bigquery
  path: cloudquery/bigquery
  registry: cloudquery
  version: "v1.10.0"
  tables: ["*"]
  destinations: ["postgresql"]
  # Learn more about the configuration options at https://cql.ink/bigquery_source
  spec:
    project_id: ${PROJECT_ID}
    dataset_id: ${DATASET_ID}
    # Optional parameters
    # dataset_location: ""
    # service_account_key_json: ""
    # endpoint: ""

Configuration Reference #

• project_id (string) (required)

  The id of the project where the destination BigQuery database resides.
• dataset_id (string) (required)

  The id of the BigQuery dataset within the project, e.g. my_dataset.
• dataset_location (string) (optional)

  The data location of the BigQuery dataset. If set, will be used as the default location for job operations. Pro-tip: this can solve "dataset not found" issues for newly created datasets.
• service_account_key_json (string) (optional) (default: empty).

  GCP service account key content. This allows for using different service accounts for the GCP source and BigQuery destination. If using service account keys, it is best to use environment or file variable substitution.
• endpoint (string) (optional)

  The BigQuery API endpoint to use. This is useful for testing against a local emulator.
• table_options (map of Table Options Spec) (optional)

  Table options to set for specific tables. Untemplated table names are the keys. Only valid if queries is empty.
• queries (list of queries Spec) (optional)

  (Preview feature) List of queries to run instead of directly reading tables. The tables in top-level spec should be left as * or can be a subset of these tables.
• destination_table_name (string) (optional) (default: {{TABLE}})

  The destination table name to write the data to. (Preview Feature)

  Supports the following placeholder variables:

  • {{TABLE}} will be replaced with the table name
  • {{DATASET}} will be replaced with the dataset name
  • {{UUID}} will be replaced with a random UUID in raw format to uniquely identify each table
  • {{YEAR}} will be replaced with the current year in YYYY format
  • {{MONTH}} will be replaced with the current month in MM format
  • {{DAY}} will be replaced with the current day in DD format
  • {{HOUR}} will be replaced with the current hour in HH format
  • {{MINUTE}} will be replaced with the current minute in mm format

  At least one of {{TABLE}} or {{UUID}} is required.

  Note that timestamps are in UTC and will be the time sync started.
• concurrency (integer) (optional) (default: 100)

  Number of tables to sync concurrently. Lower or increase this number based on your database size and available resources.
• discovery_concurrency (integer) (optional) (default: 100)

  Number of goroutines to use for discovering table schemas.
• scheduler (string) (optional) (default: dfs)

  The scheduler to use when determining the priority of resources to sync. Supported values are dfs (depth-first search), round-robin, shuffle and shuffle-queue.

  For more information about this, see performance tuning.

Table Options Spec (Preview) #

• incremental_column (string) (optional)

Queries Spec (Preview) #

• name (string) (required)

  Name of the table to be generated from the query.
• query (string) (required)

  SQL query to run. Should have a {incremental_column} > @cursor or similar in the WHERE clause if incremental_column is set.
• incremental_column (string) (optional)

  Name of the incremental column in the query result set. The query must have a reference to @cursor in its WHERE clause, and a column with this name in the result set. If empty, no incremental column will be used.
• incremental_column_type (enum) (required if incremental_column is set)

  Type of the incremental column, one of TIMESTAMP, INTEGER or STRING. This is required if incremental_column is set.

• gcloud auth application-default login (recommended when running locally)

• When you run on Cloud Shell or Cloud Code credentials are already available.

• When running on GKE use workload identity.

• Services such as Compute Engine, App Engine and functions supporting attaching a user-managed service account which will CloudQuery will be able to utilize.

• The suggested way is to use Workload identity federation
• If not available you can always use service account keys and export the location of the key via GOOGLE_APPLICATION_CREDENTIALS. (Not recommended as long-lived keys are a security risk)