Inserción de transmisión con tipos de datos complejos

Inserta datos de varios tipos compatibles con BigQuery en una tabla.

Muestra de código

Go

Antes de probar este ejemplo, sigue las instrucciones de configuración para Go incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Go.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente.

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

Antes de probar esta muestra, sigue las instrucciones de configuración para Java incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Java.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente.

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());
    }
  }
}

Ruby

Antes de probar esta muestra, sigue las instrucciones de configuración para Ruby incluidas en la guía de inicio rápido de BigQuery sobre cómo usar bibliotecas cliente. Para obtener más información, consulta la documentación de referencia de la API de BigQuery para Ruby.

Para autenticarte en BigQuery, configura las credenciales predeterminadas de la aplicación. Si deseas obtener más información, consulta Configura la autenticación para bibliotecas cliente.

require "google/cloud/bigquery"
require "base64"

##
# Inserts a row with various data types into a table.
#
# @param dataset_id [String] The ID of the dataset to create the table in.
# @param table_id   [String] The ID of the table to create.
def inserting_data_types dataset_id, table_id
  bigquery = Google::Cloud::Bigquery.new
  dataset = bigquery.dataset dataset_id
  table = dataset.table table_id

  # Create the table if it doesn't exist.
  unless table
    dataset.create_table table_id do |t|
      t.string "name"
      t.integer "age"
      t.bytes "school"
      t.geography "location"
      t.float "measurements", mode: :repeated
      t.record "datesTime" do |s|
        s.date "day"
        s.datetime "firstTime"
        s.time "secondTime"
        s.timestamp "thirdTime"
      end
    end
    table = dataset.table table_id
  end

  dates_time_content = {
    "day"        => "2019-1-12",
    "firstTime"  => "2019-02-17 11:24:00.000",
    "secondTime" => "14:00:00",
    "thirdTime"  => "2020-04-27T18:07:25.356Z"
  }

  row_content = {
    "name"         => "Tom",
    "age"          => 30,
    "school"       => Base64.strict_encode64("Test University"),
    "location"     => "POINT(1 2)",
    "measurements" => [50.05, 100.5],
    "datesTime"    => dates_time_content
  }

  response = table.insert [row_content]

  if response.success?
    puts "Rows successfully inserted into table"
  else
    puts "Insert operation not performed"
    response.insert_errors.each do |error|
      puts "Error: #{error.errors}"
    end
  end
end

¿Qué sigue?

Si quieres buscar y filtrar muestras de código para otros productos de Google Cloud , consulta el navegador de muestras deGoogle Cloud .