Docs/Writing data/Loading Avro
Writing data

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 typeWithout the flagWith --use_avro_logical_types
int with dateINTEGER 20513DATE 2026-03-01
int with time-millisINTEGER 36001000TIME 10:00:01
long with time-microsINTEGER 36001000005TIME 10:00:01.000005
long with timestamp-millisINTEGER 1772359200123TIMESTAMP 2026-03-01 10:00:00.123 UTC
long with timestamp-microsINTEGER 1772359200123456TIMESTAMP 2026-03-01 10:00:00.123456 UTC
long with local-timestamp-millisINTEGER 1772359200123DATETIME 2026-03-01T10:00:00.123
long with local-timestamp-microsINTEGER 1772359200123456DATETIME 2026-03-01T10:00:00.123456
bytes or fixed with decimalNUMERICNUMERIC
string with uuidSTRINGSTRING

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:

an Avro date loaded as INTEGEROpen in Querylab.io
12
-- 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
Result in BigQuery
Rowloaded_valueas_date
1205132026-03-01
1 row

Timestamps are microseconds or milliseconds since the epoch, depending on the logical type:

timestamp-micros and timestamp-millis as INTEGEROpen in Querylab.io
1234
-- 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
Result in BigQuery
Rowfrom_microsfrom_millis
12026-03-01 10:00:00.123456 UTC2026-03-01 10:00:00.123000 UTC
1 row

A time-micros value is the number of microseconds since midnight:

a time-micros INTEGER to TIMEOpen in Querylab.io
1234
-- 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
Result in BigQuery
Rowloaded_valueas_time
13600100000510:00:01.000005
1 row

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:

FlagsResult
--schema dt:DATE, no logical types flagfails: Field dt has incompatible types. Configured schema: date; Avro file: integer.
--use_avro_logical_types --schema dt:DATEloads as DATE
--use_avro_logical_types --schema dt:STRINGfails 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 fileBecomesNotes
int, longINTEGER
float, doubleFLOAT
boolean, string, bytesBOOLEAN, STRING, BYTES
fixedBYTESabcd came back as base64 YWJjZA==.
enumSTRINGGREEN loaded as the string GREEN.
a field that is not a unionREQUIREDEvery column in our plain files was REQUIRED.
["null", "string"] or ["string", "null"]STRING, NULLABLEThe order doesn’t matter.
["null", "int", "string"]RECORD with int_value and string_valueSee below.
["int", "string"] (no null)the same RECORD, REQUIRED
mapREPEATED RECORD with key and valueThe key is always STRING.
arrayREPEATEDAn empty array stays [].
["null", array]REPEATEDA null array becomes [].
recordRECORDNested 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:

read a union that loaded as a recordOpen in Querylab.io
123456789
-- 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
Result in BigQuery
Rowrow_idcode
115
22ZZ
2 rows

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:

look up one entry of a loaded Avro mapOpen in Querylab.io
12345678910
-- 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
Result in BigQuery
Rowproductreserved
1p-13
2p-2null
2 rows

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 fileWhat 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 namesResult
_x and __yloads
_TABLE_xfails: 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 recordfails: 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 fileResult
an extra string column extrafails: Provided Schema does not match Table ... Cannot add fields (field: extra)
the same, with --schema_update_option=ALLOW_FIELD_ADDITIONfails: 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_RELAXATIONloads; 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.

LimitValue
Avro data block16 MB
Row size, to avoid resourcesExceeded50 MB
Size of one load job15 TB (not applied with a reservation)
Files in one load job10,000,000
Load jobs per table per day1,500, failed ones included
Load job run time6 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.

Open the playground. It is the real app on public datasets and needs no sign-in.