Specify nested and repeated columns in table schemas

Console

Specify the nested and repeated addresses column:

1. In the Google Cloud console, open the BigQuery page.

   Go to BigQuery

2. In the left pane, click explore Explorer:

   

   If you don't see the left pane, click last_page Expand left pane to open the pane.

3. In the Explorer pane, expand your project, click Datasets, and then select a dataset.

4. In the details pane, click add_box Create table.

5. On the Create table page, specify the following details:

   • For Source, in the Create table from field, select Empty table.

   • In the Destination section, specify the following fields:

     • For Dataset, select the dataset in which you want to create the table.
     • For Table, enter the name of the table that you want to create.

   • For Schema, click add_box Add field and enter the following table schema:

     • For Field name, enter addresses.
     • For Type, select RECORD.
     • For Mode, choose REPEATED.

     • Specify the following fields for a nested field:

       • In the Field name field, enter status.
       • For Type, choose STRING.
       • For Mode, leave the value set to NULLABLE.

       • Click add_box Add field to add the following fields:

         Field name	Type	Mode
         address	STRING	NULLABLE
         city	STRING	NULLABLE
         state	STRING	NULLABLE
         zip	STRING	NULLABLE
         numberOfYears	STRING	NULLABLE

       Alternatively, click Edit as text and specify the schema as a JSON array.

SQL

Use the CREATE TABLE statement. Specify the schema using the column option:

1. In the Google Cloud console, go to the BigQuery page.

   Go to BigQuery

2. In the query editor, enter the following statement:

   CREATE TABLE IF NOT EXISTS mydataset.mytable (
     id STRING,
     first_name STRING,
     last_name STRING,
     dob DATE,
     addresses
       ARRAY<
         STRUCT<
           status STRING,
           address STRING,
           city STRING,
           state STRING,
           zip STRING,
           numberOfYears STRING>>
   ) OPTIONS (
       description = 'Example name and addresses table');

3. Click play_circle Run.

For more information about how to run queries, see Run an interactive query.

bq

To specify the nested and repeated addresses column in a JSON schema file, use a text editor to create a new file. Paste in the example schema definition shown above.

After you create your JSON schema file, you can provide it through the bq command-line tool. For more information, see Using a JSON schema file.

Go

import (
	"context"
	"fmt"
	"io"

	"cloud.google.com/go/bigquery"
)

// createTableComplexSchema demonstrates creating a BigQuery table and specifying a complex schema that includes
// an array of Struct types.
func createTableComplexSchema(w io.Writer, projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydatasetid"
	// tableID := "mytableid"
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %v", err)
	}
	defer client.Close()

	sampleSchema := bigquery.Schema{
		{Name: "id", Type: bigquery.StringFieldType},
		{Name: "first_name", Type: bigquery.StringFieldType},
		{Name: "last_name", Type: bigquery.StringFieldType},
		{Name: "dob", Type: bigquery.DateFieldType},
		{Name: "addresses",
			Type:     bigquery.RecordFieldType,
			Repeated: true,
			Schema: bigquery.Schema{
				{Name: "status", Type: bigquery.StringFieldType},
				{Name: "address", Type: bigquery.StringFieldType},
				{Name: "city", Type: bigquery.StringFieldType},
				{Name: "state", Type: bigquery.StringFieldType},
				{Name: "zip", Type: bigquery.StringFieldType},
				{Name: "numberOfYears", Type: bigquery.StringFieldType},
			}},
	}

	metaData := &bigquery.TableMetadata{
		Schema: sampleSchema,
	}
	tableRef := client.Dataset(datasetID).Table(tableID)
	if err := tableRef.Create(ctx, metaData); err != nil {
		return err
	}
	fmt.Fprintf(w, "created table %s\n", tableRef.FullyQualifiedName())
	return nil
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.