BigQuery

This document describes how to migrate data from Google BigQuery into your warehouse. The overall workflow uses object storage as an intermediate medium:

1. Use BigQuery's Export statement to export data to Google Cloud Storage.
2. Create the corresponding target table in your warehouse.
3. Use VeloDB Cloud's S3 Load feature to pull the data from Google Cloud Storage into your warehouse. For details on the mechanism, see Google Cloud Storage.

Before you begin

Use a running warehouse and a warehouse SQL user with the privileges required by the selected method. Run SQL in SQL Editor or a connected SQL client. Create or select a database before running unqualified table statements. Replace sample paths and credentials with your own values. Use a new load label for each independent job.

This migration exports a snapshot to object storage and loads it into warehouse tables. It does not continuously synchronize later source changes. For supported object storage sources, Data Integration offers a guided import. Use the SQL path below when you need explicit load options or partition batches.

Plan source export, object storage, network transfer, and warehouse compute costs. Verify row counts, nulls, numeric ranges, timestamps, and key semantics before switching workloads. Remove staging files when validation and your retention requirements permit. Example output illustrates job and error fields; it is not a Cloud performance measurement.

Notes

Before starting the migration, pay attention to the following items to avoid common issues:

• Table schema design: Choose the appropriate VeloDB Cloud data model, as well as partitioning and bucketing strategies based on the BigQuery table schema. For more table creation strategies, see Load best practices.
• JSON type export: BigQuery does not support exporting JSON types in Parquet format. Use JSON format for the export instead.
• Time type export: When BigQuery exports a Time type, cast it to String before exporting.
• Complex type import: For Parquet/ORC files containing complex types (Struct/Array/Map), match the nested source schema to supported target types. Use TVF Load to inspect the inferred schema or transform fields before insertion.

Data type mapping

Before migrating, create columns in your warehouse that correspond to the BigQuery fields according to the following rules:

BigQuery	VeloDB Cloud	Notes
Array	Array
BOOLEAN	BOOLEAN
DATE	DATE
DATETIME/TIMESTAMP	DATETIME	Normalize timestamps to the intended time zone before export.
JSON	JSON
INT64	BIGINT
NUMERIC	DECIMAL(p, s)	Choose precision and scale to cover the source range.
FLOAT64	DOUBLE
STRING	VARCHAR/STRING	VARCHAR maximum length is 65533 bytes
STRUCT	STRUCT
TIME	STRING	Cast to String before exporting
OTHER	UNSUPPORTED	Types not currently supported

Migration steps

Step 1: create the target table in your warehouse

Before migrating a BigQuery table to VeloDB Cloud, first create a warehouse table with a matching schema.

Assume the following table and sample data already exist in BigQuery:

CREATE OR REPLACE TABLE test.sales_data (
    order_id      INT64,
    customer_name STRING,
    order_date    DATE,
    amount        NUMERIC(10,2),
    country       STRING
)
PARTITION BY  order_date;

INSERT INTO test.sales_data (order_id, customer_name, order_date, amount, country) VALUES
(1, 'Alice', '2025-04-08', 99.99, 'USA'),
(2, 'Bob', '2025-04-08', 149.50, 'Canada'),
(3, 'Charlie', '2025-04-09', 75.00, 'UK'),
(4, 'Diana', '2025-04-10', 200.00, 'Australia');

Based on this schema, you can create a unique key partitioned table in your warehouse. The partition column matches the BigQuery partition column, and the table is partitioned by day:

CREATE TABLE `sales_data` (
  order_id      INT,
  order_date    DATE NOT NULL,
  customer_name VARCHAR(128),
  amount        DECIMAL(10,2),
  country       VARCHAR(48)
) ENGINE=OLAP
UNIQUE KEY(`order_id`,`order_date`)
PARTITION BY RANGE(`order_date`) (
PARTITION p20250408 VALUES [('2025-04-08'), ('2025-04-09')),
PARTITION p20250409 VALUES [('2025-04-09'), ('2025-04-10')),
PARTITION p20250410 VALUES [('2025-04-10'), ('2025-04-11'))
)
DISTRIBUTED BY HASH(`order_id`) BUCKETS 16
PROPERTIES (
 "dynamic_partition.enable" = "false",
 "dynamic_partition.time_unit" = "DAY",
 "dynamic_partition.end" = "5",
 "dynamic_partition.prefix" = "p",
 "dynamic_partition.buckets" = "16"
);

The example keeps the historical sample partitions fixed by disabling dynamic partition scheduling. For ongoing daily data, configure the desired dynamic partition window before enabling it.

Step 2: export BigQuery data to Google Cloud Storage

2.1 Export Parquet files with the export statement

Use BigQuery's EXPORT DATA to export the target table to Google Cloud Storage:

EXPORT DATA
  OPTIONS (
    uri = 'gs://mybucket/export/sales_data/*.parquet',
    format = 'PARQUET')
AS (
  SELECT *
  FROM test.sales_data
);

2.2 Inspect the exported files on Google Cloud Storage

The command above exports sales_data to Google Cloud Storage. The wildcard produces one or more output files. It does not by itself organize the output into partition directories. Export partitions with separate queries or explicit paths if you want directory-based load batches. For details, see exporting-data.

Step 3: import data into your warehouse with S3 Load

The import uses S3 Load. S3 Load is an asynchronous data import method. After the job is submitted, VeloDB Cloud actively pulls data from the data source. Use the corresponding provider adapter for AWS S3, Google Cloud Storage, and Azure.

This method is suitable for scenarios with large data volumes that need asynchronous background processing. For data imports that need synchronous processing, see TVF Load.

Note:

For Parquet/ORC files containing complex types (Struct/Array/Map), validate the nested schema and target type mapping before loading. TVF can help inspect or transform the fields.

3.1 Import data from a single file

Use the following LOAD LABEL statement to import a single Parquet file from Google Cloud Storage:

LOAD LABEL sales_data_2025_04_08
(
    DATA INFILE("s3://mybucket/export/sales_data/000000000000.parquet")
    INTO TABLE sales_data
    FORMAT AS "parquet"
    (order_id, order_date, customer_name, amount, country)
)
WITH S3
(
    "provider" = "GCP",
    "s3.endpoint" = "https://storage.googleapis.com",
    "s3.region" = "asia-southeast1",
    "s3.access_key" = "<ak>",
    "s3.secret_key" = "<sk>"
);

3.2 Check job status with SHOW LOAD

Because S3 Load is submitted asynchronously, use SHOW LOAD to query the import status for a given label:

mysql> show load where label = "sales_data_2025_04_08"\G
*************************** 1. row ***************************
        JobId: 17956078
        Label: sales_data_2025_04_08
        State: FINISHED
      Progress: 100.00% (1/1)
          Type: BROKER
      EtlInfo: unselected.rows=0; dpp.abnorm.ALL=0; dpp.norm.ALL=2
      TaskInfo: cluster:storage.googleapis.com; timeout(s):3600; max_filter_ratio:0.0; priority:NORMAL
      ErrorMsg: NULL
    CreateTime: 2025-04-10 17:50:53
  EtlStartTime: 2025-04-10 17:50:54
EtlFinishTime: 2025-04-10 17:50:54
LoadStartTime: 2025-04-10 17:50:54
LoadFinishTime: 2025-04-10 17:50:54
          URL: NULL
    JobDetails: {"Unfinished backends":{"5eec1be8612d4872-91040ff1e7208a4f":[]},"ScannedRows":2,"TaskNumber":1,"LoadBytes":91,"All backends":{"5eec1be8612d4872-91040ff1e7208a4f":[10022]},"FileNumber":1,"FileSize":1620}
TransactionId: 766228
  ErrorTablets: {}
          User: <warehouse-user>
      Comment:
1 row in set (0.00 sec)

3.3 Handle errors during import

When multiple import jobs exist, use the following statement to query failed imports and their causes:

mysql> show load where state='CANCELLED' and label like "label_test%"\G
*************************** 1. row ***************************
        JobId: 18312384
        Label: label_test123
        State: CANCELLED
      Progress: 100.00% (3/3)
          Type: BROKER
      EtlInfo: unselected.rows=0; dpp.abnorm.ALL=4; dpp.norm.ALL=0
      TaskInfo: cluster:storage.googleapis.com; timeout(s):14400; max_filter_ratio:0.0; priority:NORMAL
      ErrorMsg: type:ETL_QUALITY_UNSATISFIED; msg:quality not good enough to cancel
    CreateTime: 2025-04-15 17:32:59
  EtlStartTime: 2025-04-15 17:33:02
EtlFinishTime: 2025-04-15 17:33:02
LoadStartTime: 2025-04-15 17:33:02
LoadFinishTime: 2025-04-15 17:33:02
          URL: <load-error-url>
    JobDetails: {"Unfinished backends":{"7602ccd7c3a4854-95307efca7bfe341":[]},"ScannedRows":4,"TaskNumber":1,"LoadBytes":188,"All backends":{"7602ccd7c3a4854-95307efca7bfe341":[10022]},"FileNumber":3,"FileSize":4839}
TransactionId: 769213
  ErrorTablets: {}
          User: <warehouse-user>
      Comment:

The example above is a data quality error (ETL_QUALITY_UNSATISFIED). To get the specific cause, visit the returned URL. For example, the error below indicates that the data exceeds the actual length of the country column in the table schema:

curl "<load-error-url>"
Reason: column_name[country], the length of input is too long than schema. first 32 bytes of input str: [USA] schema length: 1; actual length: 3; . src line [];
Reason: column_name[country], the length of input is too long than schema. first 32 bytes of input str: [Canada] schema length: 1; actual length: 6; . src line [];
Reason: column_name[country], the length of input is too long than schema. first 32 bytes of input str: [UK] schema length: 1; actual length: 2; . src line [];
Reason: column_name[country], the length of input is too long than schema. first 32 bytes of input str: [Australia] schema length: 1; actual length: 9; . src line [];

For data quality errors, if skipping some erroneous rows is acceptable, set the error tolerance ratio in the S3 Load PROPERTIES. For details on the parameters, see Load configuration parameters.

3.4 Import data from multiple files

When migrating large volumes of historical data, use a batched import strategy:

• Batch division: Each batch corresponds to one VeloDB Cloud partition or a small number of partitions.
• Per-batch data volume: Start by testing batches of 100 GB or less, to reduce system pressure and lower the cost of retries after a failed import. This is planning guidance, not a Cloud limit. Tune batch size and concurrency for warehouse resources, file layout, and retry cost.
• Batch script: See s3_load_file_demo.sh. This script splits the file list under a specified directory in object storage and submits multiple S3 Load jobs to VeloDB Cloud in batches, enabling batch import.

FAQ

Q1: Why can JSON types in BigQuery not be exported directly as Parquet?

BigQuery does not currently support exporting JSON types in Parquet format. Set format to JSON for the export, and use a corresponding JSON column in your warehouse to receive the data.

Q2: How does the BigQuery Time type map to VeloDB Cloud?

The BigQuery Time type must be cast to String during export, and stored in a STRING column in your warehouse.

Q3: What should I do when an import fails with ETL_QUALITY_UNSATISFIED?

This error indicates that the data does not meet quality requirements. Handle it as follows:

1. Look at the URL field in the SHOW LOAD result and use curl to fetch the detailed error log.
2. Identify the cause from the log (common causes include column length overflow and type mismatch).
3. Adjust the target table schema, or set max_filter_ratio in the S3 Load PROPERTIES to skip some erroneous rows.

Q4: Can Parquet/ORC files containing Struct/Array/Map be imported with S3 Load?

Yes, for supported nested schemas. Match the target ARRAY, MAP, and STRUCT types to the file schema and test representative files. Use TVF Load when you need schema inspection or transformations.

Q5: How can I avoid the retry cost caused by single-job failures when migrating large volumes of historical data?

Import in batches by partition, with 100 GB or less as an initial test size, and use the s3_load_file_demo.sh script to split and submit batches. This is planning guidance, not a Cloud limit. Tune batch size and concurrency for warehouse resources, file layout, and retry cost.

When an error URL refers to an internal service address that your client cannot reach, use the job error details available to you or contact VeloDB Cloud support with the label and job ID. Do not log in to warehouse hosts to retrieve error logs. Increasing max_filter_ratio can discard rows; reconcile rejected rows before accepting a migration.