Java Code Examples for com.google.cloud.bigquery.Schema#of()

private void createTable(String tableName) {
    TableId tableId = TableId.of(dataset.getDatasetId().getDataset(), tableName);

    // Table field definition
    Field id = Field.newBuilder("id", LegacySQLTypeName.INTEGER).setMode(Mode.REQUIRED).build();
    Field name = Field.newBuilder("name", LegacySQLTypeName.STRING).setMode(Mode.NULLABLE).build();
    Field alias = Field.newBuilder("alias", LegacySQLTypeName.STRING).setMode(Mode.REPEATED).build();

    Field zip = Field.newBuilder("zip", LegacySQLTypeName.STRING).setMode(Mode.NULLABLE).build();
    Field city = Field.newBuilder("city", LegacySQLTypeName.STRING).setMode(Mode.NULLABLE).build();
    Field addresses = Field.newBuilder("addresses", LegacySQLTypeName.RECORD, zip, city).setMode(Mode.REPEATED).build();

    Field position = Field.newBuilder("position", LegacySQLTypeName.STRING).setMode(Mode.NULLABLE).build();
    Field company = Field.newBuilder("company", LegacySQLTypeName.STRING).setMode(Mode.NULLABLE).build();
    Field job = Field.newBuilder("job", LegacySQLTypeName.RECORD, position, company).setMode(Mode.NULLABLE).build();

    // Table schema definition
    schema = Schema.of(id, name, alias, addresses, job);
    TableDefinition tableDefinition = StandardTableDefinition.of(schema);
    TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();

    // create table
    bigquery.create(tableInfo);
}
 

@Test
public void shouldCreatePartitionedTable() {
    when(bqConfig.isBQTablePartitioningEnabled()).thenReturn(true);
    when(bqConfig.getBQTablePartitionKey()).thenReturn("timestamp_field");
    Schema bqSchema = Schema.of(
            Field.newBuilder("timestamp_field", LegacySQLTypeName.TIMESTAMP).build()
    );

    BQTableDefinition bqTableDefinition = new BQTableDefinition(bqConfig);
    StandardTableDefinition tableDefinition = bqTableDefinition.getTableDefinition(bqSchema);

    Schema returnedSchema = tableDefinition.getSchema();
    assertEquals(returnedSchema.getFields().size(), bqSchema.getFields().size());
    assertEquals(returnedSchema.getFields().get(0).getName(), bqSchema.getFields().get(0).getName());
    assertEquals(returnedSchema.getFields().get(0).getMode(), bqSchema.getFields().get(0).getMode());
    assertEquals(returnedSchema.getFields().get(0).getType(), bqSchema.getFields().get(0).getType());
    assertEquals("timestamp_field", tableDefinition.getTimePartitioning().getField());
}
 

/** Example of listing table rows with schema. */
// [TARGET listTableData(TableId, Schema, TableDataListOption...)]
public FieldValueList listTableDataSchemaId() {
  // [START ]
  Schema schema =
      Schema.of(
          Field.of("word", LegacySQLTypeName.STRING),
          Field.of("word_count", LegacySQLTypeName.STRING),
          Field.of("corpus", LegacySQLTypeName.STRING),
          Field.of("corpus_date", LegacySQLTypeName.STRING));
  TableResult tableData =
      bigquery.listTableData(
          TableId.of("bigquery-public-data", "samples", "shakespeare"), schema);
  FieldValueList row = tableData.getValues().iterator().next();
  System.out.println(row.get("word").getStringValue());
  // [END ]
  return row;
}
 

/**
 * Returns {@code Table} after creating the table with no columns in BigQuery.
 *
 * @param tableId a TableId referencing the BigQuery table being requested.
 */
private Table createBigQueryTable(TableId tableId) {
  // Create Blank BigQuery Table
  List<Field> fieldList = new ArrayList<Field>();
  Schema schema = Schema.of(fieldList);

  StandardTableDefinition.Builder tableDefinitionBuilder =
      StandardTableDefinition.newBuilder().setSchema(schema);
  if (dayPartitioning) {
    tableDefinitionBuilder.setTimePartitioning(
        TimePartitioning.newBuilder(TimePartitioning.Type.DAY).build());
  }
  TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinitionBuilder.build()).build();
  Table table = bigquery.create(tableInfo);

  return table;
}
 

public static Schema createTestSchema() {
  return Schema.of(
      Field.of("a", LegacySQLTypeName.STRING),
      Field.of("b", LegacySQLTypeName.BYTES),
      Field.newBuilder("c", LegacySQLTypeName.INTEGER).setMode(Field.Mode.REPEATED).build(),
      Field.of("d", LegacySQLTypeName.FLOAT),
      Field.of("e", LegacySQLTypeName.BOOLEAN),
      Field.of("f", LegacySQLTypeName.TIMESTAMP),
      Field.of("g", LegacySQLTypeName.TIME),
      Field.of("h", LegacySQLTypeName.DATETIME),
      Field.of("i", LegacySQLTypeName.DATE),
      Field.of("j",
          LegacySQLTypeName.RECORD,
              Field.of("x", LegacySQLTypeName.STRING),
              Field.of("y",
                  LegacySQLTypeName.RECORD, Field.of("z", LegacySQLTypeName.STRING))
              )
  );
}
 

/** Example of creating a table in the dataset with schema and time partitioning. */
// [TARGET create(String, TableDefinition, TableOption...)]
// [VARIABLE “my_table”]
// [VARIABLE “my_field”]
public Table createTable(String tableName, String fieldName) {
  // [START ]
  Schema schema = Schema.of(Field.of(fieldName, LegacySQLTypeName.STRING));
  StandardTableDefinition definition =
      StandardTableDefinition.newBuilder()
          .setSchema(schema)
          .setTimePartitioning(TimePartitioning.of(TimePartitioning.Type.DAY))
          .build();
  Table table = dataset.create(tableName, definition);
  // [END ]
  return table;
}
 

public static void main(String... args) throws InterruptedException, TimeoutException {
  BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  TableId tableId = TableId.of("dataset", "table");
  Table table = bigquery.getTable(tableId);
  if (table == null) {
    System.out.println("Creating table " + tableId);
    Field integerField = Field.of("fieldName", LegacySQLTypeName.INTEGER);
    Schema schema = Schema.of(integerField);
    table = bigquery.create(TableInfo.of(tableId, StandardTableDefinition.of(schema)));
  }
  System.out.println("Loading data into table " + tableId);
  Job loadJob = table.load(FormatOptions.csv(), "gs://bucket/path");
  loadJob = loadJob.waitFor();
  if (loadJob.getStatus().getError() != null) {
    System.out.println("Job completed with errors");
  } else {
    System.out.println("Job succeeded");
  }
}
 

/** Example of creating a table. */
// [TARGET create(TableInfo, TableOption...)]
// [VARIABLE "my_dataset_name"]
// [VARIABLE "my_table_name"]
// [VARIABLE "string_field"]
public Table createTable(String datasetName, String tableName, String fieldName) {
  // [START bigquery_create_table]
  TableId tableId = TableId.of(datasetName, tableName);
  // Table field definition
  Field field = Field.of(fieldName, LegacySQLTypeName.STRING);
  // Table schema definition
  Schema schema = Schema.of(field);
  TableDefinition tableDefinition = StandardTableDefinition.of(schema);
  TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();
  Table table = bigquery.create(tableInfo);
  // [END bigquery_create_table]
  return table;
}
 

public static Schema schemaFromString(String schemaStr) {
    if (schemaStr == null) {
        return null;
    } else {
        Gson gson = new Gson();
        List<Map> fields = gson.fromJson(schemaStr, gsonSchemaType);
        return Schema.of(BigQueryUtils.listToFields(fields));
    }
}
 

public void upsertTable(List<Field> bqSchemaFields) throws BigQueryException {
    Schema schema = Schema.of(bqSchemaFields);
    TableDefinition tableDefinition = getTableDefinition(schema);
    TableInfo tableInfo = TableInfo.newBuilder(tableID, tableDefinition)
            .setLabels(bqConfig.getTableLabels())
            .build();
    upsertDatasetAndTable(tableInfo);
}
 

public Table createTable(String tableName, Field[] fields) {	
	TableId tableId = TableId.of(datasetName, tableName);
	Schema schema = Schema.of(fields);
	TableDefinition tableDefinition = StandardTableDefinition.of(schema);
	TableInfo tableInfo = TableInfo.newBuilder(tableId, tableDefinition).build();
	Table t = bigquery.create(tableInfo);
	System.err.println("created " + t.getTableId());
	return t;
}
 

@Test
public void testInsertAllAndListTableData() throws IOException, InterruptedException {
  String tableName = "test_insert_all_and_list_table_data";
  String fieldName1 = "booleanField";
  String fieldName2 = "bytesField";
  String fieldName3 = "recordField";
  String fieldName4 = "stringField";
  TableId tableId = TableId.of(DATASET, tableName);
  Schema schema =
      Schema.of(
          Field.of(fieldName1, LegacySQLTypeName.BOOLEAN),
          Field.of(fieldName2, LegacySQLTypeName.BYTES),
          Field.of(
              fieldName3,
              LegacySQLTypeName.RECORD,
              Field.of(fieldName4, LegacySQLTypeName.STRING)));
  TableInfo table = TableInfo.of(tableId, StandardTableDefinition.of(schema));
  assertNotNull(bigquery.create(table));
  InsertAllResponse response = bigquerySnippets.insertAll(DATASET, tableName);
  assertFalse(response.hasErrors());
  assertTrue(response.getInsertErrors().isEmpty());
  Page<FieldValueList> listPage = bigquerySnippets.listTableDataFromId(DATASET, tableName);
  while (Iterators.size(listPage.iterateAll().iterator()) < 1) {
    Thread.sleep(500);
    listPage = bigquerySnippets.listTableDataFromId(DATASET, tableName);
  }
  FieldValueList row = listPage.getValues().iterator().next();
  assertEquals(true, row.get(0).getBooleanValue());
  assertArrayEquals(new byte[] {0xA, 0xD, 0xD, 0xE, 0xD}, row.get(1).getBytesValue());
  assertEquals("Hello, World!", row.get(2).getRecordValue().get(0).getStringValue());

  listPage = bigquerySnippets.listTableDataSchema(DATASET, tableName, schema, fieldName1);
  row = listPage.getValues().iterator().next();
  assertNotNull(row.get(fieldName1));
  assertArrayEquals(new byte[] {0xA, 0xD, 0xD, 0xE, 0xD}, row.get(fieldName2).getBytesValue());

  bigquerySnippets.listTableDataSchemaId();

  assertTrue(bigquerySnippets.deleteTable(DATASET, tableName));
}
 

static Schema parseSchema(String[] args, int start, int end) {
  List<Field> schemaFields = new ArrayList<>();
  for (int i = start; i < end; i++) {
    String[] fieldsArray = args[i].split(":");
    if (fieldsArray.length != 2) {
      throw new IllegalArgumentException("Unrecognized field definition '" + args[i] + "'.");
    }
    String fieldName = fieldsArray[0];
    String typeString = fieldsArray[1].toLowerCase();
    LegacySQLTypeName fieldType;
    switch (typeString) {
      case "string":
        fieldType = LegacySQLTypeName.STRING;
        break;
      case "integer":
        fieldType = LegacySQLTypeName.INTEGER;
        break;
      case "timestamp":
        fieldType = LegacySQLTypeName.TIMESTAMP;
        break;
      case "float":
        fieldType = LegacySQLTypeName.FLOAT;
        break;
      case "boolean":
        fieldType = LegacySQLTypeName.BOOLEAN;
        break;
      case "bytes":
        fieldType = LegacySQLTypeName.BYTES;
        break;
      default:
        throw new IllegalArgumentException("Unrecognized field type '" + typeString + "'.");
    }
    schemaFields.add(Field.of(fieldName, fieldType));
  }
  return Schema.of(schemaFields);
}
 

/** Example of loading a newline-delimited-json file with textual fields from GCS to a table. */
// [TARGET create(JobInfo, JobOption...)]
// [VARIABLE "my_dataset_name"]
// [VARIABLE "my_table_name"]
public Long writeRemoteFileToTable(String datasetName, String tableName)
    throws InterruptedException {
  // [START bigquery_load_table_gcs_json]
  String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.json";
  TableId tableId = TableId.of(datasetName, tableName);
  // Table field definition
  Field[] fields =
      new Field[] {
        Field.of("name", LegacySQLTypeName.STRING),
        Field.of("post_abbr", LegacySQLTypeName.STRING)
      };
  // Table schema definition
  Schema schema = Schema.of(fields);
  LoadJobConfiguration configuration =
      LoadJobConfiguration.builder(tableId, sourceUri)
          .setFormatOptions(FormatOptions.json())
          .setCreateDisposition(CreateDisposition.CREATE_IF_NEEDED)
          .setSchema(schema)
          .build();
  // Load the table
  Job loadJob = bigquery.create(JobInfo.of(configuration));
  loadJob = loadJob.waitFor();
  // Check the table
  System.out.println("State: " + loadJob.getStatus().getState());
  return ((StandardTableDefinition) bigquery.getTable(tableId).getDefinition()).getNumRows();
  // [END bigquery_load_table_gcs_json]
}
 

public static Schema beamSchemaToBigQueryClientSchema(
    org.apache.beam.sdk.schemas.Schema tableSchema) {
  ArrayList<Field> bqFields = new ArrayList<>(tableSchema.getFieldCount());

  for (org.apache.beam.sdk.schemas.Schema.Field f : tableSchema.getFields()) {
    bqFields.add(beamFieldToBigQueryClientField(f));
  }
  return Schema.of(bqFields);
}
 

@Test (expected = BQPartitionKeyNotSpecified.class)
public void shouldThrowErrorIfPartitionFieldNotSet() {
    when(bqConfig.isBQTablePartitioningEnabled()).thenReturn(true);
    Schema bqSchema = Schema.of(
            Field.newBuilder("int_field", LegacySQLTypeName.INTEGER).build()
    );

    BQTableDefinition bqTableDefinition = new BQTableDefinition(bqConfig);
    StandardTableDefinition tableDefinition = bqTableDefinition.getTableDefinition(bqSchema);
    tableDefinition.getSchema();
}
 

@Test
public void shouldReturnTableDefinitionIfPartitionDisabled() {
    when(bqConfig.isBQTablePartitioningEnabled()).thenReturn(false);
    Schema bqSchema = Schema.of(
            Field.newBuilder("int_field", LegacySQLTypeName.INTEGER).build()
    );

    BQTableDefinition bqTableDefinition = new BQTableDefinition(bqConfig);
    StandardTableDefinition tableDefinition = bqTableDefinition.getTableDefinition(bqSchema);
    Schema returnedSchema = tableDefinition.getSchema();
    assertEquals(returnedSchema.getFields().size(), bqSchema.getFields().size());
    assertEquals(returnedSchema.getFields().get(0).getName(), bqSchema.getFields().get(0).getName());
    assertEquals(returnedSchema.getFields().get(0).getMode(), bqSchema.getFields().get(0).getMode());
    assertEquals(returnedSchema.getFields().get(0).getType(), bqSchema.getFields().get(0).getType());
}
 

@Test(expected = UnsupportedOperationException.class)
public void shouldThrowUnsupportedExceptionForRangePartition() {
    when(bqConfig.isBQTablePartitioningEnabled()).thenReturn(true);
    when(bqConfig.getBQTablePartitionKey()).thenReturn("int_field");

    Schema bqSchema = Schema.of(
            Field.newBuilder("int_field", LegacySQLTypeName.INTEGER).build()
    );

    BQTableDefinition bqTableDefinition = new BQTableDefinition(bqConfig);
    bqTableDefinition.getTableDefinition(bqSchema);
}
 

private TableDefinition getNonPartitionedTableDefinition(ArrayList<Field> bqSchemaFields) {
    Schema schema = Schema.of(bqSchemaFields);

    TableDefinition tableDefinition = StandardTableDefinition.newBuilder()
            .setSchema(schema)
            .build();
    return tableDefinition;
}
 

private TableDefinition getPartitionedTableDefinition(ArrayList<Field> bqSchemaFields) {
    TimePartitioning.Builder timePartitioningBuilder = TimePartitioning.newBuilder(TimePartitioning.Type.DAY);
    timePartitioningBuilder.setField(bqConfig.getBQTablePartitionKey())
            .setRequirePartitionFilter(true);

    Schema schema = Schema.of(bqSchemaFields);

    TableDefinition tableDefinition = StandardTableDefinition.newBuilder()
            .setSchema(schema)
            .setTimePartitioning(timePartitioningBuilder.build())
            .build();
    return tableDefinition;
}