How to define the BigQuery schema using standard SQL?


I'd like to use BigQuery Standard SQL in a new project, however I am not able to find any examples on how to define the schema, everything points at Legacy SQL. In particular, I want to use ARRAY and STRUCT.

One way to create a table in BigQuery is by using the API calls. There is no CREATE table syntax.

Creating a table

BigQuery offers various ways to create a new table as detailed here:

  • You can create an empty table by using the command line tool's bq mk command or by using the BigQuery API tables.insert() method.
  • You can load a table from a CSV or JSON data file (compressed or uncompressed), from an Avro file, or from a Cloud Datastore backup.
  • You can create a table from a query result.
  • You can copy a table
  • You can define a table over a file in Cloud Storage
  • you can use Standard SQL types when you define your table schema (see Elliotts answer) and there is a tichet about to update in docs as well. Vote/star here.

lots of Python samples are on GitHub simple as:

def create_table(dataset_name, table_name, project=None):
    """Creates a simple table in the given dataset.
    If no project is specified, then the currently active project is used.
    bigquery_client = bigquery.Client(project=project)
    dataset = bigquery_client.dataset(dataset_name)

    if not dataset.exists():
        print('Dataset {} does not exist.'.format(dataset_name))

    table = dataset.table(table_name)

    # Set the table schema
    table.schema = (
        bigquery.SchemaField('Name', 'STRING'),
        bigquery.SchemaField('Age', 'INTEGER'),
        bigquery.SchemaField('Weight', 'FLOAT'),


    print('Created table {} in dataset {}.'.format(table_name, dataset_name))