Streaming insert with complex data types

Insert data of various BigQuery-supported types into a table.

Code sample

Go

Before trying this sample, follow the Go setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Go API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import (
	"context"
	"fmt"
	"time"

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

// ComplexType represents a complex row item
type ComplexType struct {
	Name         string                 `bigquery:"name"`
	Age          int                    `bigquery:"age"`
	School       []byte                 `bigquery:"school"`
	Location     bigquery.NullGeography `bigquery:"location"`
	Measurements []float64              `bigquery:"measurements"`
	DatesTime    DatesTime              `bigquery:"datesTime"`
}

// DatesTime shows different date/time representation
type DatesTime struct {
	Day        civil.Date     `bigquery:"day"`
	FirstTime  civil.DateTime `bigquery:"firstTime"`
	SecondTime civil.Time     `bigquery:"secondTime"`
	ThirdTime  time.Time      `bigquery:"thirdTime"`
}

// insertingDataTypes demonstrates inserting data into a table using the streaming insert mechanism.
func insertingDataTypes(projectID, datasetID, tableID string) error {
	// projectID := "my-project-id"
	// datasetID := "mydataset"
	// tableID := "mytable"
	ctx := context.Background()
	client, err := bigquery.NewClient(ctx, projectID)
	if err != nil {
		return fmt.Errorf("bigquery.NewClient: %w", err)
	}
	defer client.Close()

	// Manually defining schema
	schema := bigquery.Schema{
		{Name: "name", Type: bigquery.StringFieldType},
		{Name: "age", Type: bigquery.IntegerFieldType},
		{Name: "school", Type: bigquery.BytesFieldType},
		{Name: "location", Type: bigquery.GeographyFieldType},
		{Name: "measurements", Type: bigquery.FloatFieldType, Repeated: true},
		{Name: "datesTime", Type: bigquery.RecordFieldType, Schema: bigquery.Schema{
			{Name: "day", Type: bigquery.DateFieldType},
			{Name: "firstTime", Type: bigquery.DateTimeFieldType},
			{Name: "secondTime", Type: bigquery.TimeFieldType},
			{Name: "thirdTime", Type: bigquery.TimestampFieldType},
		}},
	}
	// Infer schema from struct
	// schema, err := bigquery.InferSchema(ComplexType{})

	table := client.Dataset(datasetID).Table(tableID)
	err = table.Create(ctx, &bigquery.TableMetadata{
		Schema: schema,
	})
	if err != nil {
		return fmt.Errorf("table.Create: %w", err)
	}
	day, err := civil.ParseDate("2019-01-12")
	if err != nil {
		return fmt.Errorf("civil.ParseDate: %w", err)
	}
	firstTime, err := civil.ParseDateTime("2019-02-17T11:24:00.000")
	if err != nil {
		return fmt.Errorf("civil.ParseDateTime: %w", err)
	}
	secondTime, err := civil.ParseTime("14:00:00")
	if err != nil {
		return fmt.Errorf("civil.ParseTime: %w", err)
	}
	thirdTime, err := time.Parse(time.RFC3339Nano, "2020-04-27T18:07:25.356Z")
	if err != nil {
		return fmt.Errorf("time.Parse: %w", err)
	}
	row := &ComplexType{
		Name:         "Tom",
		Age:          30,
		School:       []byte("Test University"),
		Location:     bigquery.NullGeography{GeographyVal: "POINT(1 2)", Valid: true},
		Measurements: []float64{50.05, 100.5},
		DatesTime: DatesTime{
			Day:        day,
			FirstTime:  firstTime,
			SecondTime: secondTime,
			ThirdTime:  thirdTime,
		},
	}
	rows := []*ComplexType{row}
	// Uncomment to simulate insert errors.
	// This example row is missing required fields.
	// badRow := &ComplexType{
	// 	Name: "John",
	// 	Age:  24,
	// }
	// rows = append(rows, badRow)

	inserter := table.Inserter()
	err = inserter.Put(ctx, rows)
	if err != nil {
		if multiErr, ok := err.(bigquery.PutMultiError); ok {
			for _, putErr := range multiErr {
				fmt.Printf("failed to insert row %d with err: %v \n", putErr.RowIndex, putErr.Error())
			}
		}
		return err
	}
	return nil
}

Java

Before trying this sample, follow the Java setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Java API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryError;
import com.google.cloud.bigquery.BigQueryException;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.Field;
import com.google.cloud.bigquery.InsertAllRequest;
import com.google.cloud.bigquery.InsertAllResponse;
import com.google.cloud.bigquery.Schema;
import com.google.cloud.bigquery.StandardSQLTypeName;
import com.google.cloud.bigquery.StandardTableDefinition;
import com.google.cloud.bigquery.TableDefinition;
import com.google.cloud.bigquery.TableId;
import com.google.cloud.bigquery.TableInfo;
import java.util.Base64;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

// Sample to insert data types in a table
public class InsertingDataTypes {

  public static void main(String[] args) {
    // TODO(developer): Replace these variables before running the sample.
    String datasetName = "MY_DATASET_NAME";
    String tableName = "MY_TABLE_NAME";
    insertingDataTypes(datasetName, tableName);
  }

  public static void insertingDataTypes(String datasetName, String tableName) {
    try {
      // Initialize client that will be used to send requests. This client only needs to be created
      // once, and can be reused for multiple requests.
      BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

      // Inserting data types
      Field name = Field.of("name", StandardSQLTypeName.STRING);
      Field age = Field.of("age", StandardSQLTypeName.INT64);
      Field school = Field.of("school", StandardSQLTypeName.BYTES);
      Field location = Field.of("location", StandardSQLTypeName.GEOGRAPHY);
      Field measurements =
          Field.newBuilder("measurements", StandardSQLTypeName.FLOAT64)
              .setMode(Field.Mode.REPEATED)
              .build();
      Field day = Field.of("day", StandardSQLTypeName.DATE);
      Field firstTime = Field.of("firstTime", StandardSQLTypeName.DATETIME);
      Field secondTime = Field.of("secondTime", StandardSQLTypeName.TIME);
      Field thirdTime = Field.of("thirdTime", StandardSQLTypeName.TIMESTAMP);
      Field datesTime =
          Field.of("datesTime", StandardSQLTypeName.STRUCT, day, firstTime, secondTime, thirdTime);
      Schema schema = Schema.of(name, age, school, location, measurements, datesTime);

      TableId tableId = TableId.of(datasetName, tableName);
      TableDefinition tableDefinition = StandardTableDefinition.of(schema);
      TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

      bigquery.create(tableInfo);

      // Inserting Sample data
      Map<String, Object> datesTimeContent = new HashMap<>();
      datesTimeContent.put("day", "2019-1-12");
      datesTimeContent.put("firstTime", "2019-02-17 11:24:00.000");
      datesTimeContent.put("secondTime", "14:00:00");
      datesTimeContent.put("thirdTime", "2020-04-27T18:07:25.356Z");

      Map<String, Object> rowContent = new HashMap<>();
      rowContent.put("name", "Tom");
      rowContent.put("age", 30);
      rowContent.put("school", Base64.getEncoder().encodeToString("Test University".getBytes()));
      rowContent.put("location", "POINT(1 2)");
      rowContent.put("measurements", new Float[] {50.05f, 100.5f});
      rowContent.put("datesTime", datesTimeContent);

      InsertAllResponse response =
          bigquery.insertAll(InsertAllRequest.newBuilder(tableId).addRow(rowContent).build());

      if (response.hasErrors()) {
        // If any of the insertions failed, this lets you inspect the errors
        for (Map.Entry<Long, List<BigQueryError>> entry : response.getInsertErrors().entrySet()) {
          System.out.println("Response error: \n" + entry.getValue());
        }
      }
      System.out.println("Rows successfully inserted into table");
    } catch (BigQueryException e) {
      System.out.println("Insert operation not performed \n" + e.toString());
    }
  }
}

Node.js

Before trying this sample, follow the Node.js setup instructions in the BigQuery quickstart using client libraries. For more information, see the BigQuery Node.js API reference documentation.

To authenticate to BigQuery, set up Application Default Credentials. For more information, see Set up authentication for client libraries.

// Import the Google Cloud client library
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();

async function insertingDataTypes() {
  // Inserts data of various BigQuery-supported types into a table.

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const datasetId = 'my_dataset';
  // const tableId = 'my_table';

  // Describe the schema of the table
  // For more information on supported data types, see
  // https://cloud.google.com/bigquery/docs/reference/standard-sql/data-types
  const schema = [
    {
      name: 'name',
      type: 'STRING',
    },
    {
      name: 'age',
      type: 'INTEGER',
    },
    {
      name: 'school',
      type: 'BYTES',
    },
    {
      name: 'metadata',
      type: 'JSON',
    },
    {
      name: 'location',
      type: 'GEOGRAPHY',
    },
    {
      name: 'measurements',
      mode: 'REPEATED',
      type: 'FLOAT',
    },
    {
      name: 'datesTimes',
      type: 'RECORD',
      fields: [
        {
          name: 'day',
          type: 'DATE',
        },
        {
          name: 'firstTime',
          type: 'DATETIME',
        },
        {
          name: 'secondTime',
          type: 'TIME',
        },
        {
          name: 'thirdTime',
          type: 'TIMESTAMP',
        },
      ],
    },
  ];

  // For all options, see https://cloud.google.com/bigquery/docs/reference/v2/tables#resource
  const options = {
    schema: schema,
  };

  // Create a new table in the dataset
  const [table] = await bigquery
    .dataset(datasetId)
    .createTable(tableId, options);

  console.log(`Table ${table.id} created.`);

  // The DATE type represents a logical calendar date, independent of time zone.
  // A DATE value does not represent a specific 24-hour time period.
  // Rather, a given DATE value represents a different 24-hour period when
  // interpreted in different time zones, and may represent a shorter or longer
  // day during Daylight Savings Time transitions.
  const bqDate = bigquery.date('2019-1-12');
  // A DATETIME object represents a date and time, as they might be
  // displayed on a calendar or clock, independent of time zone.
  const bqDatetime = bigquery.datetime('2019-02-17 11:24:00.000');
  // A TIME object represents a time, as might be displayed on a watch,
  // independent of a specific date and timezone.
  const bqTime = bigquery.time('14:00:00');
  // A TIMESTAMP object represents an absolute point in time,
  // independent of any time zone or convention such as Daylight
  // Savings Time with microsecond precision.
  const bqTimestamp = bigquery.timestamp('2020-04-27T18:07:25.356Z');
  const bqGeography = bigquery.geography('POINT(1 2)');
  const schoolBuffer = Buffer.from('Test University');
  // a JSON field needs to be converted to a string
  const metadata = JSON.stringify({
    owner: 'John Doe',
    contact: 'johndoe@example.com',
  });
  // Rows to be inserted into table
  const rows = [
    {
      name: 'Tom',
      age: '30',
      location: bqGeography,
      school: schoolBuffer,
      metadata: metadata,
      measurements: [50.05, 100.5],
      datesTimes: {
        day: bqDate,
        firstTime: bqDatetime,
        secondTime: bqTime,
        thirdTime: bqTimestamp,
      },
    },
    {
      name: 'Ada',
      age: '35',
      measurements: [30.08, 121.7],
    },
  ];

  // Insert data into table
  await bigquery.dataset(datasetId).table(tableId).insert(rows);

  console.log(`Inserted ${rows.length} rows`);
}

What's next

To search and filter code samples for other Google Cloud products, see the Google Cloud sample browser.