Loading Avro into BigQuery
BigQuery reads the schema from an Avro file. Without --use_avro_logical_types a date or timestamp loads as a plain INTEGER, a union becomes a record with one field per type, and non-null fields become REQUIRED.
To load an Avro file into BigQuery, run bq load --source_format=AVRO --use_avro_logical_types mydataset.mytable file.avro. The schema comes from the file, so you pass none. Always add --use_avro_logical_types. Without it BigQuery ignores logicalType annotations: a date loads as an INTEGER such as 20513, and a timestamp-micros loads as an INTEGER such as 1772359200123456. The load doesn’t warn you. A batch load is free; you pay only to store the table.
Everything below was tested with real load jobs, on files written with fastavro. The error messages are the ones BigQuery returned.
Loading the file
With bq, from a local file or Cloud Storage:
bq load --source_format=AVRO --use_avro_logical_types \
mydataset.orders \
'gs://my-bucket/orders/2026-03-*.avro'
From SQL, where the option has the same name without the dashes:
LOAD DATA INTO mydataset.orders
FROM FILES (
format = 'AVRO',
uris = ['gs://my-bucket/orders/2026-03-*.avro'],
use_avro_logical_types = true
);
Neither example is run here; both need a bucket and a dataset. A load appends by default; --replace or LOAD DATA OVERWRITE replaces the table.
Logical types: the flag decides
We loaded one file with ten columns, once with the flag and once without:
| Avro type | Without the flag | With --use_avro_logical_types |
|---|---|---|
int with date | INTEGER 20513 | DATE 2026-03-01 |
int with time-millis | INTEGER 36001000 | TIME 10:00:01 |
long with time-micros | INTEGER 36001000005 | TIME 10:00:01.000005 |
long with timestamp-millis | INTEGER 1772359200123 | TIMESTAMP 2026-03-01 10:00:00.123 UTC |
long with timestamp-micros | INTEGER 1772359200123456 | TIMESTAMP 2026-03-01 10:00:00.123456 UTC |
long with local-timestamp-millis | INTEGER 1772359200123 | DATETIME 2026-03-01T10:00:00.123 |
long with local-timestamp-micros | INTEGER 1772359200123456 | DATETIME 2026-03-01T10:00:00.123456 |
bytes or fixed with decimal | NUMERIC | NUMERIC |
string with uuid | STRING | STRING |
The flag also applies to a nullable union around a logical type: ["null", {"type": "long", "logicalType": "timestamp-micros"}] loaded as TIMESTAMP with a NULL row, and as INTEGER without it.
The decimal row is the exception. A decimal is converted to NUMERIC whether the flag is on or off.
If you already loaded a table without the flag, convert in SQL. A date is days since 1970-01-01:
-- Without --use_avro_logical_types an Avro date loads as days since 1970-01-01. SELECT 20513 AS loaded_value, DATE_FROM_UNIX_DATE(20513) AS as_date
| Row | loaded_value | as_date |
|---|---|---|
| 1 | 20513 | 2026-03-01 |
Timestamps are microseconds or milliseconds since the epoch, depending on the logical type:
-- timestamp-micros and timestamp-millis load as INTEGER unless logical types are on. SELECT TIMESTAMP_MICROS(1772359200123456) AS from_micros, TIMESTAMP_MILLIS(1772359200123) AS from_millis
| Row | from_micros | from_millis |
|---|---|---|
| 1 | 2026-03-01 10:00:00.123456 UTC | 2026-03-01 10:00:00.123000 UTC |
A time-micros value is the number of microseconds since midnight:
-- A time-micros value loads as microseconds since midnight. SELECT 36001000005 AS loaded_value, TIME_ADD(TIME '00:00:00', INTERVAL 36001000005 MICROSECOND) AS as_time
| Row | loaded_value | as_time |
|---|---|---|
| 1 | 36001000005 | 10:00:01.000005 |
Check the unit before you pick the function. timestamp-millis data passed to TIMESTAMP_MICROS lands in January 1970. The same mistake happens at load time if you pass your own schema: we loaded a plain long holding 1772359200123 (milliseconds) with --schema t:TIMESTAMP, and BigQuery read it as microseconds, 1970-01-21 12:19:19.200123 UTC.
Passing a schema with Avro
--schema doesn’t cast Avro columns. The Avro type has to match, and with logical types off, a date is an integer:
| Flags | Result |
|---|---|
--schema dt:DATE, no logical types flag | fails: Field dt has incompatible types. Configured schema: date; Avro file: integer. |
--use_avro_logical_types --schema dt:DATE | loads as DATE |
--use_avro_logical_types --schema dt:STRING | fails with the same message, Configured schema: string; Avro file: integer. |
--use_avro_logical_types --schema tsm:STRING (a timestamp) | fails: Field tsm has incompatible types. Configured schema: string; Avro file: integer. |
The last message says the Avro file is an integer even though the flag is on. Ignore that part: what matters is that a string schema for a date or timestamp column fails. Load it typed, then use FORMAT_DATE or CAST in a query.
How the other Avro types map
| In the Avro file | Becomes | Notes |
|---|---|---|
int, long | INTEGER | |
float, double | FLOAT | |
boolean, string, bytes | BOOLEAN, STRING, BYTES | |
fixed | BYTES | abcd came back as base64 YWJjZA==. |
enum | STRING | GREEN loaded as the string GREEN. |
| a field that is not a union | REQUIRED | Every column in our plain files was REQUIRED. |
["null", "string"] or ["string", "null"] | STRING, NULLABLE | The order doesn’t matter. |
["null", "int", "string"] | RECORD with int_value and string_value | See below. |
["int", "string"] (no null) | the same RECORD, REQUIRED | |
map | REPEATED RECORD with key and value | The key is always STRING. |
array | REPEATED | An empty array stays []. |
["null", array] | REPEATED | A null array becomes []. |
record | RECORD | Nested records keep their shape. |
Non-null Avro fields are REQUIRED. That follows the Avro schema: a field without null in a union can’t be null. It has consequences when you append, covered below.
A union with more than one non-null type becomes a record. The field names are the Avro type names plus _value: int_value, string_value, long_value, double_value. Only one is set in any row, and the others are NULL:
{"c": {"int_value": 5, "string_value": null}}
{"c": {"int_value": null, "string_value": "hello"}}
To get one column back, coalesce them:
-- An Avro union of int and string arrives as a RECORD with one field per type. WITH loaded AS ( SELECT 1 AS row_id, STRUCT(5 AS int_value, CAST(NULL AS STRING) AS string_value) AS code UNION ALL SELECT 2, STRUCT(CAST(NULL AS INT64), 'ZZ') ) SELECT row_id, COALESCE(CAST(code.int_value AS STRING), code.string_value) AS code FROM loaded ORDER BY row_id
| Row | row_id | code |
|---|---|---|
| 1 | 1 | 5 |
| 2 | 2 | ZZ |
Pick a common type when you can. Writing ["null", "string"] and converting numbers to strings in the producer avoids the record altogether.
A map is an array of key and value records. There is no MAP type in BigQuery, so there is no lookup by name. Use a subquery over the array:
-- An Avro map arrives as a repeated RECORD of key and value. WITH loaded AS ( SELECT 'p-1' AS product, [STRUCT('stock' AS key, 12 AS value), STRUCT('reserved', 3)] AS counts UNION ALL SELECT 'p-2', [STRUCT('stock' AS key, 0 AS value)] ) SELECT product, (SELECT c.value FROM UNNEST(counts) AS c WHERE c.key = 'reserved') AS reserved FROM loaded ORDER BY product
| Row | product | reserved |
|---|---|---|
| 1 | p-1 | 3 |
| 2 | p-2 | null |
See Arrays and Nested and repeated data for more on reading these.
Arrays with nulls and arrays of arrays
Google’s page says nested arrays aren’t supported and that such files must be converted. A BigQuery array can’t hold a NULL or another array, so we expected errors. We got none:
| In the Avro file | What loaded |
|---|---|
array of ["null", "string"], value ["a", null] | ["a"]; the null is dropped, silently |
array of array of int, value [[1], [2, 3]] | [1, 2, 3]; flattened, silently |
Both loads succeeded. The first loses the position of the null and the second loses the grouping, with no warning. If the nulls or the inner arrays mean something, change them in the producer to an array of records, for example {"items": [{"value": null}]}.
Decimals
A decimal becomes NUMERIC when its values fit: 38 digits, 9 after the decimal point. A decimal with precision: 50, scale: 5 holding 123456789012345678901234567890123456.12345 fails:
The value for field 'd' cannot be converted to NUMERIC: Value is out of range after scaling to NUMERIC type; input length: 17; scale: 5
--decimal_target_types=BIGNUMERIC loaded the same file, exactly, and --decimal_target_types=STRING loaded it as the text 123456789012345678901234567890123456.12345. BIGNUMERIC worked with and without --use_avro_logical_types. The flag takes one type.
Field names
Avro restricts names to [A-Za-z_][A-Za-z0-9_]*, so characters such as -, . and @ can’t appear. What can still fail:
| Field names | Result |
|---|---|
_x and __y | loads |
_TABLE_x | fails: Invalid field name "_TABLE_x". Field names are not allowed to start with the (case-insensitive) prefixes _PARTITION, _TABLE_, _FILE_, _ROW_TIMESTAMP, __ROOT__, _COLIDENTIFIER, and __DREMEL_PK_MERGED_STRUCT_ |
Name and name in one record | fails: Duplicate (case-insensitive) field name 'name' in record 'root'. |
Appending a file whose schema differs
The first load had id long, name string, so both columns were REQUIRED. Then we appended other files:
| Appended file | Result |
|---|---|
an extra string column extra | fails: Provided Schema does not match Table ... Cannot add fields (field: extra) |
the same, with --schema_update_option=ALLOW_FIELD_ADDITION | fails: Cannot add required fields to an existing schema. (field: extra) |
name as ["null", "string"] | fails: Field name has changed mode from REQUIRED to NULLABLE |
the same, with --schema_update_option=ALLOW_FIELD_RELAXATION | loads; name becomes NULLABLE in the table and the new row has NULL |
The second row is the Avro-specific one. ALLOW_FIELD_ADDITION adds columns only when they are optional, and a plain Avro field is REQUIRED. To add a column by appending, write it in the Avro schema as ["null", "string"] with a null default. We loaded such a file with ALLOW_FIELD_ADDITION and the table got a NULLABLE extra column.
The Avro page says that when files with different schemas load together, the schemas must be compatible with Avro’s schema resolution, and that the schema comes from the alphabetically last file. We could not test a wildcard load from a local machine, so we only quote the docs here.
Compression and limits
Avro compresses its blocks inside the file, so .avro files stay one file. Files written with the null (none), deflate and snappy codecs all loaded. Google also lists zstd; we didn’t test it. An Avro file with a schema and no records loads and creates an empty table. A zero-byte file fails with The Apache Avro library failed to parse the header.
| Limit | Value |
|---|---|
| Avro data block | 16 MB |
Row size, to avoid resourcesExceeded | 50 MB |
| Size of one load job | 15 TB (not applied with a reservation) |
| Files in one load job | 10,000,000 |
| Load jobs per table per day | 1,500, failed ones included |
| Load job run time | 6 hours |
Google adds that a row with many or very long array fields should have them split into separate fields. Avro is the format Google recommends for the fastest load of compressed data: its blocks can be read in parallel, which a gzip-compressed CSV or JSON file can’t be.
What Querylab.io does
The Import tab accepts a .avro file, reads the schema in the browser, and shows the columns before anything is written. It reads logical types as the plain type they annotate, so a date column shows as INTEGER there, the same as BigQuery’s default. The load job carries the file unchanged and the schema from the preview. It does not set useAvroLogicalTypes. If you want DATE or TIMESTAMP columns from an Avro file, convert them in SQL after the load, as in the examples above, or load with bq and the flag. See Import.
What it costs
A batch load on the shared slot pool is free. You pay to store the table, and for the Avro file in Cloud Storage while it sits there. See Storage pricing and Loading data into BigQuery for the daily load limit and partition loads.