BigQuery加载AVRO数据时忽略--use_avro_logical_types标志的问题

Let’s work through this step by step — the issue here almost certainly ties back to how your Avro schema defines those timestamp fields, or how BigQuery is interpreting them. Here’s what to check and fix:

First, clarify why the --use_avro_logical_types=True parameter isn’t working: BigQuery only applies this setting to Avro fields that explicitly include a logicalType definition in their schema. The Spark-style schema you shared just lists fields as long, but that doesn’t mean the underlying Avro files carry the logical type metadata BigQuery needs to recognize them as timestamps.

Step 1: Verify your Avro file’s actual schema #

First, confirm if your Avro files include the required logical type annotations. Use the avro-tools command-line utility to inspect one of your files:

# For a local file
avro-tools getschema /path/to/your/file.avro

# For a GCS file (download first if needed)
gsutil cp gs://test-bucket/production/avro/account_quarter_sales_stats/one-sample-file.avro ./temp.avro
avro-tools getschema ./temp.avro

Look for fields like created_at in the output. You should see something like this if logical types are properly defined:

{
  "name": "created_at",
  "type": ["null", {"type": "long", "logicalType": "timestamp-millis"}]
}

If you don’t see the logicalType key here, that’s the root cause — your Avro files aren’t marking those longs as timestamp values, so BigQuery treats them as regular integers.

Step 2: Fix the Avro generation process (if possible) #

If the logical type is missing, adjust how you’re generating the Avro files. For example, if using Spark:

• Ensure you’re on Spark 2.4+ (versions before this don’t fully support Avro logical types).
• Make sure your timestamp columns are stored as Spark’s TimestampType (don’t manually convert them to longs). Spark will automatically add the logicalType annotation to the Avro schema for these columns.

If using another tool to generate Avro, check its documentation to enable logical type support for timestamps.

Step 3: Workarounds if you can’t modify the Avro files #

If re-generating the Avro files isn’t an option, you have two reliable workarounds:

Option A: Manually specify the schema during load #

Create a JSON schema file (e.g., account_stats_schema.json) that explicitly defines timestamp fields as TIMESTAMP type:

[
  {"name": "id", "type": "INTEGER"},
  {"name": "account_manager_id", "type": "INTEGER"},
  {"name": "total_sales_existing_accounts", "type": "INTEGER"},
  {"name": "total_sales_new_accounts", "type": "INTEGER"},
  {"name": "qtr_start_date", "type": "TIMESTAMP"},
  {"name": "qtr_end_date", "type": "TIMESTAMP"},
  {"name": "created_at", "type": "TIMESTAMP"},
  {"name": "updated_at", "type": "TIMESTAMP"},
  {"name": "total_sales_with_contact", "type": "INTEGER"},
  {"name": "total_sales", "type": "INTEGER"},
  {"name": "total_sales_percentage_compared_to_previous", "type": "INTEGER"}
]

Then load using this schema:

bq --location=US load --source_format=AVRO --schema=./account_stats_schema.json writable.test_account_stat gs://test-bucket/production/avro/account_quarter_sales_stats/*.avro

Note: BigQuery assumes integer timestamps are in seconds by default. If your Avro longs are in milliseconds (common for most systems), you’ll need to adjust with SQL after loading (see Option B).

Option B: Load as integer then convert with SQL #

Load the data into a temporary table first (keeping integer types), then create your final table with converted timestamps:

1. Load into a temp table:

bq --location=US load --source_format=AVRO writable.test_account_stat_temp gs://test-bucket/production/avro/account_quarter_sales_stats/*.avro

2. Create the final table with converted timestamps (use TIMESTAMP_MILLIS for millisecond values, TIMESTAMP_MICROS for microseconds):

CREATE OR REPLACE TABLE writable.test_account_stat AS
SELECT
  id,
  account_manager_id,
  total_sales_existing_accounts,
  total_sales_new_accounts,
  TIMESTAMP_MILLIS(qtr_start_date) AS qtr_start_date,
  TIMESTAMP_MILLIS(qtr_end_date) AS qtr_end_date,
  TIMESTAMP_MILLIS(created_at) AS created_at,
  TIMESTAMP_MILLIS(updated_at) AS updated_at,
  total_sales_with_contact,
  total_sales,
  total_sales_percentage_compared_to_previous
FROM writable.test_account_stat_temp;

3. Clean up the temp table if needed:

bq rm writable.test_account_stat_temp

Final Quick Checks #

• Double-check that --use_avro_logical_types=True is in the correct position in your bq command (it should come before the dataset/table name).
• If using the BigQuery API, ensure you’re setting the useAvroLogicalTypes field to true in your load job configuration.

内容的提问来源于stack exchange，提问作者AKs