# 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. Source: https://docs.querylab.io/bigquery/loading-avro/ Updated: 2026-11-04 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: ```sh 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: ```sql 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: **an Avro date loaded as INTEGER** ```sql -- 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 (1 row): | loaded_value | as_date | | --- | --- | | 20513 | 2026-03-01 | Timestamps are microseconds or milliseconds since the epoch, depending on the logical type: **timestamp-micros and timestamp-millis as INTEGER** ```sql -- 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 (1 row): | from_micros | from_millis | | --- | --- | | 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 INTEGER to TIME** ```sql -- 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 (1 row): | loaded_value | as_time | | --- | --- | | 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`: ```json {"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 record** ```sql -- 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 (2 rows): | row_id | code | | --- | --- | | 1 | 5 | | 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: **look up one entry of a loaded Avro map** ```sql -- 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 (2 rows): | product | reserved | | --- | --- | | p-1 | 3 | | p-2 | NULL | See [Arrays](https://docs.querylab.io/bigquery/arrays/) and [Nested and repeated data](https://docs.querylab.io/bigquery/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: ```text 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](https://docs.querylab.io/data-management/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](https://docs.querylab.io/bigquery/storage-pricing/) and [Loading data into BigQuery](https://docs.querylab.io/bigquery/loading-data/) for the daily load limit and partition loads. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in. ## Sources Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery: - [Loading Avro data from Cloud Storage](https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-avro) - [Batch loading data](https://cloud.google.com/bigquery/docs/batch-loading-data) - [Quotas and limits, load jobs](https://cloud.google.com/bigquery/quotas#load_jobs)