CODEWITHSUNDEEP
pythonschemagoogle-bigquery
errordeveloper
errordeveloper

Reputation: 6912

How to define 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.

Upvotes: 1

Views: 1772

Answers (2)

Pentium10
Pentium10

Reputation: 207912

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))
        return

    table = dataset.table(table_name)

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

    table.create()

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

Upvotes: 2

Elliott Brossard
Elliott Brossard

Reputation: 33745

You can create a table with a schema that uses standard SQL types. Here is an example of a valid schema:

{
  "a": "ARRAY<STRUCT<x INT64, y STRING>>",
  "b": "STRUCT<z DATE>",
  "c": "INT64"
}

If you put this in a file such as sample_schema.json, you can create a table from it using bq mk:

bq mk --schema sample_schema.json -t your_dataset.YourTableName

Outside of the bq client, the tables.insert API also supports standard SQL type names.

Upvotes: 2

Related Questions