Invalid field name "x". Fields must contain the allowed characters
BigQuery refuses a column name with a dot, a dollar sign or a few other characters, one over 300 characters, or one that starts with a reserved prefix such as _PARTITION. The message has several wordings, and the fix is a new name.
Invalid field name "a.b". Fields must contain the allowed characters, and be at most 300 characters long. For allowed characters, please refer to https://cloud.google.com/bigquery/docs/schemas#column_names
What BigQuery means
A column name was refused. The name is in quotes in the message, so you can find it. It happens when a query returns the name, when a table is created or changed, or when a file is loaded.
Most searches use an older wording of this error: Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 300 characters long. That is the old rule. BigQuery now prints the shorter text above, and the old text still shows up for a name over 300 characters in a dry run (see below).
The current rules:
- Up to 300 characters.
- Letters in any language, digits, underscores, hyphens, spaces (a tab is refused) and a few symbols:
&,%,=,+,:,',<,>,#and|. - A name with a space, a hyphen or a leading digit is allowed. It needs backticks when you write it in SQL.
- Not allowed:
.,/,,,$,(,),*,",;,@,!,?,[,],\,~,^,{,}, and currency signs and other symbols such as€,£and°. - A name cannot start with
_PARTITION,_TABLE_,_FILE_,_ROW_TIMESTAMP,__ROOT__,_COLIDENTIFIERor__DREMEL_PK_MERGED_STRUCT_, in any case. Four more prefixes are reserved:_CHANGE_TYPE,_CHANGE_TIMESTAMP,_ADJUSTED_CHANGE_TIMESTAMPand_CHANGE_SEQUENCE_NUMBER.
Guides that say a name must be letters, digits and underscores, and must not start with a digit, describe the old rule. BigQuery’s Schemas page still opens with it. The list under Flexible column names further down that page is the one BigQuery applies.
The smallest query that reproduces it
SELECT 1 AS `a.b`
Invalid field name "a.b". Fields must contain the allowed characters, and be at most 300 characters long. For allowed characters, please refer to https://cloud.google.com/bigquery/docs/schemas#column_names
The fix is a name without the dot:
SELECT 1 AS a_b
A name with a hyphen or a space does not need a fix. SELECT 1 AS `my-col` and SELECT 1 AS `my col` both run.
The message variants
A character BigQuery refuses. The message above. It is raised by a query, by bq mk with a schema, by bq update with a new schema, by a load with a schema, by ALTER TABLE ... RENAME COLUMN, by a query that writes its result to a destination table, and by a JSON load whose keys have a . or $.
A reserved prefix. The name is fine, the start of it is not:
CREATE TABLE mydataset.t AS SELECT 1 AS `_PARTITIONTIME`
Invalid field name "_PARTITIONTIME". Field names are not allowed to start with the (case-insensitive) prefixes _PARTITION, _TABLE_, _FILE_, _ROW_TIMESTAMP, __ROOT__, _COLIDENTIFIER, and __DREMEL_PK_MERGED_STRUCT_
The same message comes from CREATE TABLE (...), ALTER TABLE ... ADD COLUMN, CREATE VIEW, bq mk, bq load with a schema, bq update and a CSV header. A name that starts with _CHANGE_TYPE, _CHANGE_TIMESTAMP, _ADJUSTED_CHANGE_TIMESTAMP or _CHANGE_SEQUENCE_NUMBER gets the same text with that list of prefixes:
Invalid field name "_CHANGE_TYPE". Field names are not allowed to start with the (case-insensitive) prefixes _CHANGE_TYPE, _CHANGE_TIMESTAMP, _ADJUSTED_CHANGE_TIMESTAMP and _CHANGE_SEQUENCE_NUMBER
Rename the column: partition_time, table_x, file_name.
The DDL wording. CREATE TABLE, CREATE VIEW and ALTER TABLE ... ADD COLUMN give a shorter message with the field name:
CREATE TABLE mydataset.t AS SELECT 1 AS `a.b`
Illegal field name: a.b
It applies to the columns of a SELECT that is saved, to a column list, and to the fields of a STRUCT in a CREATE TABLE ... AS SELECT. Rename the column in the query or in the list.
Longer than 300 characters. A real query with a 301-character alias gives the first message, with the name cut short and ending in .... A dry run of the same query prints the whole name and gives the old text:
Invalid field name "<the 301-character name>". Fields must contain only letters, numbers, and underscores, start with a letter or underscore, and be at most 300 characters long.
Do not trust the character list in that text. It is the old rule, and names with spaces and hyphens run. CREATE TABLE ... AS SELECT gives Illegal field name: followed by the name.
When the name comes from a file
A CSV header or a JSON key becomes the column name. The first two messages apply, with one more for CSV with --autodetect:
Field name 'a.b' is not supported by the current character map. Please change your field name or use character map V2 to let the system modify the field names.
bq load --autodetect --column_name_character_map=V2 loads the file and turns the . into _, so a.b becomes a_b. V1 does the same for that header. Either is faster than editing a wide header. A header that starts with _PARTITIONTIME still fails with the prefix message under V2. See Loading CSV.
JSON has no such switch. --column_name_character_map=V2 on a JSON load gives:
Column Name Character Map is not supported for this format: STORAGE_FORMAT_JSON.
Rename the keys before you load.
Queries that look wrong but run
Only the columns of the final result are checked. A bad name that stays inside the query is fine:
SELECT 1 AS x FROM (SELECT 1 AS `a.b`)
This runs. SELECT * FROM (SELECT 1 AS `a.b`) fails, because the name reaches the result. The same holds for a CTE and for COUNT(*) over a subquery.
A reserved prefix in a plain result. SELECT 1 AS `_PARTITIONTIME` runs and prints the column. It fails when the result is saved: CREATE TABLE ... AS, CREATE VIEW, ALTER TABLE ... ADD COLUMN, or a query that writes its result to a destination table.
An alias that is not saved. INSERT INTO t (id) SELECT 1 AS `a.b` runs. The alias is never saved as a column name.
Two names that differ only in case. SELECT 1 AS A, 2 AS a runs, and the second column comes back as a_1. A table cannot have both: CREATE TABLE t (a INT64, A INT64) gives Duplicate column name A in CREATE TABLE at [1:45], and a CSV with the header a,A gives Duplicate column 'A' for table ....
An unquoted name. SELECT 1 AS my-col is a syntax error: Syntax error: Expected end of input but got "-" at [1:15]. SELECT 1 AS 1abc gives Syntax error: Unexpected integer literal "1" at [1:13]. Put the name in backticks.
In Querylab.io
The editor marks a bad name as you type, on the name itself, with BigQuery’s own wording:
Illegal field name: a.binCREATE TABLE ... AS SELECT,CREATE VIEW, aCREATE TABLEcolumn list,ALTER TABLE ... ADD COLUMNand a field of aSTRUCT(...)that is saved- the same message when
CREATE TABLE ... AS SELECT *reads the name from a subquery or CTE, unless* EXCEPTdrops it Invalid field name "a.b". Fields must contain the allowed characters...for a plainSELECTalias and forALTER TABLE ... RENAME COLUMN- the prefix message for a reserved prefix in
CREATE VIEWand the other saved forms - a name over 300 characters in
CREATE TABLE ... AS SELECT
It does not mark a bad name in SELECT AS STRUCT, in SELECT AS VALUE, in a pipe |> SELECT, or read through SELECT * in a query that is not saved. BigQuery’s dry run reports those when you pause typing. There is no one-click fix, because the new name is your choice.
See Column aliases and DDL.