com.google.cloud.bigquery.Table Java Examples

/**
 * 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;
}
 

/** 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");
  }
}
 

private Table createBigQueryTable(TableId tableId) {
  // Create Blank BigQuery Table
  LOG.info(String.format("Creating Table: %s", tableId.toString()));

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

private Table getBigQueryTable(TableId tableId) {
  String tableName = tableId.toString();
  Table table = tables.get(tableName);

  // Checks that table existed in tables map
  // If not pull table from API
  // TODO we need logic to invalidate table caches
  if (table == null) {
    LOG.info("Pulling Table from API");
    table = bigquery.getTable(tableId);
  }
  // Check that table exists, if not create empty table
  // the empty table will have columns automapped during updateBigQueryTable()
  if (table == null) {
    table = createBigQueryTable(tableId);
  }
  tables.put(tableName, table);

  return table;
}
 

/** 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;
}
 

/**
 * Extracts and applies new column information to BigQuery by comparing the TableRow against the
 * BigQuery Table.
 *
 * @param tableId a TableId referencing the BigQuery table to be loaded to.
 * @param row a TableRow with the raw data to be loaded into BigQuery.
 * @param inputSchema The source schema lookup to be used in mapping.
 */
private void updateTableIfRequired(
    TableId tableId, TableRow row, Map<String, LegacySQLTypeName> inputSchema) {
  Table table = getOrCreateBigQueryTable(tableId);
  FieldList tableFields = table.getDefinition().getSchema().getFields();

  Set<String> rowKeys = row.keySet();
  Boolean tableWasUpdated = false;
  List<Field> newFieldList = new ArrayList<Field>();
  for (String rowKey : rowKeys) {
    // Check if rowKey (column from data) is in the BQ Table
    try {
      Field tableField = tableFields.get(rowKey);
    } catch (IllegalArgumentException e) {
      tableWasUpdated = addNewTableField(tableId, row, rowKey, newFieldList, inputSchema);
    }
  }

  if (tableWasUpdated) {
    LOG.info("Updating Table");
    updateBigQueryTable(tableId, table, tableFields, newFieldList);
  }
}
 

private Table createBigQueryTable(BigQueryAction action) {
  TableDefinition definition = StandardTableDefinition.of(
      BigQuerySchemaUtils.beamSchemaToBigQueryClientSchema(action.tableSchema));

  TableId tableId = TableId.of(action.projectId, action.dataset, action.tableName);
  TableInfo tableInfo = TableInfo.newBuilder(tableId, definition).build();

  LOG.info("Creating a new BigQuery table: {}", tableInfo);

  try {
    return bigQueryClient.create(tableInfo);
  } catch (BigQueryException e) {
    if (e.getMessage().startsWith("Already Exists")) {
      return null;
    } else {
      throw e;
    }
  }
}
 

@Test
public void testWriteRemoteJsonToTable() throws InterruptedException {
  String tableName = "us_states";
  Table table = bigquery.getTable(DATASET, tableName);
  assertNull(table);

  Long result = bigquerySnippets.writeRemoteFileToTable(DATASET, tableName);
  table = bigquery.getTable(DATASET, tableName);
  assertNotNull(table);
  ArrayList<String> tableFieldNames = new ArrayList<>();
  for (Field field : table.getDefinition().getSchema().getFields()) {
    tableFieldNames.add(field.getName());
  }
  bigquery.delete(table.getTableId());
  assertEquals(Long.valueOf(50), result);
}
 

private List<SchemaTableName> listTablesWithTypes(ConnectorSession session, Optional<String> schemaName, TableDefinition.Type... types)
{
    if (schemaName.isPresent() && schemaName.get().equalsIgnoreCase(INFORMATION_SCHEMA)) {
        return ImmutableList.of();
    }
    Set<String> schemaNames = schemaName.map(ImmutableSet::of)
            .orElseGet(() -> ImmutableSet.copyOf(listSchemaNames(session)));

    ImmutableList.Builder<SchemaTableName> tableNames = ImmutableList.builder();
    for (String datasetId : schemaNames) {
        for (Table table : bigQueryClient.listTables(DatasetId.of(projectId, datasetId), types)) {
            tableNames.add(new SchemaTableName(datasetId, table.getTableId().getTable()));
        }
    }
    return tableNames.build();
}
 

/**
 * Verifies that the given table has the rows inserted by InsertTestRows().
 *
 * @param checkTable the table to query
 */
private void verifyTestRows(Table checkTable) throws InterruptedException {
  List<FieldValueList> rows = waitForTableRows(checkTable, 2);
  // Verify that the table data matches what it's supposed to.
  Set<List<?>> values =
      FluentIterable.from(rows)
          .transform(
              new Function<FieldValueList, List<?>>() {
                @Override
                public List<?> apply(FieldValueList row) {
                  return ImmutableList.of(
                      row.get(0).getStringValue(), row.get(1).getBooleanValue());
                }
              })
          .toSet();
  assertEquals(ImmutableSet.of(ROW2, ROW1), values);
}
 

TableInfo createTableFromQuery()
{
    TableId destinationTable = bigQueryClient.createDestinationTable(table);
    log.debug("destinationTable is %s", destinationTable);
    JobInfo jobInfo = JobInfo.of(
            QueryJobConfiguration
                    .newBuilder(query)
                    .setDestinationTable(destinationTable)
                    .build());
    log.debug("running query %s", jobInfo);
    Job job = waitForJob(bigQueryClient.create(jobInfo));
    log.debug("job has finished. %s", job);
    if (job.getStatus().getError() != null) {
        throw convertToBigQueryException(job.getStatus().getError());
    }
    // add expiration time to the table
    TableInfo createdTable = bigQueryClient.getTable(destinationTable);
    long expirationTime = createdTable.getCreationTime() +
            TimeUnit.HOURS.toMillis(config.viewExpirationTimeInHours);
    Table updatedTable = bigQueryClient.update(createdTable.toBuilder()
            .setExpirationTime(expirationTime)
            .build());
    return updatedTable;
}
 

/** Example of listing the tables in a dataset. */
// [TARGET listTables(DatasetId, TableListOption...)]
// [VARIABLE "my_project_id"]
// [VARIABLE "my_dataset_name"]
public Page<Table> listTablesFromId(String projectId, String datasetName) {
  // [START bigquery_list_tables]
  DatasetId datasetId = DatasetId.of(projectId, datasetName);
  Page<Table> tables = bigquery.listTables(datasetId, TableListOption.pageSize(100));
  for (Table table : tables.iterateAll()) {
    // do something with the table
  }
  // [END bigquery_list_tables]
  return tables;
}
 

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

/**
 * Example of fetching the table's latest information, specifying particular table fields to get.
 */
// [TARGET reload(TableOption...)]
// [VARIABLE TableField.LAST_MODIFIED_TIME]
// [VARIABLE TableField.NUM_ROWS]
public Table reloadTableWithFields(TableField field1, TableField field2) {
  // [START ]
  Table latestTable = table.reload(TableOption.fields(field1, field2));
  if (latestTable == null) {
    // the table was not found
  }
  // [END ]
  return latestTable;
}
 

/** Example of updating the table's information. */
// [TARGET update(TableOption...)]
public Table update() {
  // [START ]
  Table updatedTable = table.toBuilder().setDescription("new description").build().update();
  // [END ]
  return updatedTable;
}
 

/** Example of updating a table by changing its description. */
public Table updateTableDescription(String datasetName, String tableName, String newDescription) {
  // [START bigquery_update_table_description]
  // String datasetName = "my_dataset_name";
  // String tableName = "my_table_name";
  // String newDescription = "new_description";

  Table beforeTable = bigquery.getTable(datasetName, tableName);
  TableInfo tableInfo = beforeTable.toBuilder().setDescription(newDescription).build();
  Table afterTable = bigquery.update(tableInfo);
  // [END bigquery_update_table_description]
  return afterTable;
}
 

/** Example of updating a table by changing its expiration. */
// [TARGET update(TableInfo, TableOption...)]
// [VARIABLE "my_dataset_name"]
// [VARIABLE "my_table_name"]
public Table updateTableExpiration(String datasetName, String tableName) {
  // [START bigquery_update_table_expiration]
  Table beforeTable = bigquery.getTable(datasetName, tableName);

  // Set table to expire 5 days from now.
  long expirationMillis = Instant.now().plus(5, ChronoUnit.DAYS).toEpochMilli();
  TableInfo tableInfo = beforeTable.toBuilder().setExpirationTime(expirationMillis).build();
  Table afterTable = bigquery.update(tableInfo);
  // [END bigquery_update_table_expiration]
  return afterTable;
}
 

/** Example of listing the tables in a dataset, specifying the page size. */
// [TARGET listTables(String, TableListOption...)]
// [VARIABLE "my_dataset_name"]
public Page<Table> listTables(String datasetName) {
  // [START ]
  Page<Table> tables = bigquery.listTables(datasetName, TableListOption.pageSize(100));
  for (Table table : tables.iterateAll()) {
    // do something with the table
  }
  // [END ]
  return tables;
}
 

public void dropStore(String storeName) {
	for (Table t : bigquery.listTables(datasetName).iterateAll()) {
		if (t.getTableId().getTable().startsWith(storeName)) {
			System.err.println("deleting " + t.getTableId());
			t.delete();
		}
	}
}
 

@Test
public void testReloadTableWithFields() {
  Table latestTable =
      tableSnippets.reloadTableWithFields(TableField.LAST_MODIFIED_TIME, TableField.NUM_ROWS);
  assertNotNull(latestTable);
  assertNotNull(latestTable.getLastModifiedTime());
}
 

/** Example of getting a table. */
// [TARGET getTable(String, String, TableOption...)]
// [VARIABLE "my_dataset_name"]
// [VARIABLE "my_table_name"]
public Table getTable(String datasetName, String tableName) {
  // [START ]
  Table table = bigquery.getTable(datasetName, tableName);
  // [END ]
  return table;
}
 

/** Example of getting a table. */
// [TARGET getTable(TableId, TableOption...)]
// [VARIABLE "my_project_id"]
// [VARIABLE "my_dataset_name"]
// [VARIABLE "my_table_name"]
public Table getTableFromId(String projectId, String datasetName, String tableName) {
  // [START bigquery_get_table]
  TableId tableId = TableId.of(projectId, datasetName, tableName);
  Table table = bigquery.getTable(tableId);
  // [END bigquery_get_table]
  return table;
}
 

/**
 * Waits for a specified number of rows to appear in the given table. This is used by
 * verifyTestRows to wait for data to appear before verifying.
 *
 * @param checkTable the table to query
 * @param numRows the expected number of rows
 * @return the rows from the table
 */
private List<FieldValueList> waitForTableRows(Table checkTable, int numRows)
    throws InterruptedException {
  // Wait for the data to appear.
  Page<FieldValueList> page = checkTable.list(TableDataListOption.pageSize(100));
  List<FieldValueList> rows = ImmutableList.copyOf(page.getValues());
  while (rows.size() != numRows) {
    Thread.sleep(1000);
    page = checkTable.list(TableDataListOption.pageSize(100));
    rows = ImmutableList.copyOf(page.getValues());
  }
  return rows;
}
 

@Test
public void testListTablesNotEmpty() {
  String expectedTableName = "test_table";

  dataset.create(expectedTableName, StandardTableDefinition.newBuilder().build());
  Page<Table> tables = datasetSnippets.list();
  Iterator<Table> iterator = tables.iterateAll().iterator();
  assertTrue(iterator.hasNext());

  Table actualTable = iterator.next();
  assertEquals(expectedTableName, actualTable.getTableId().getTable());
  assertFalse(iterator.hasNext());

  bigquery.delete(TableId.of(DATASET, expectedTableName));
}
 

TableInfo getTable(TableId tableId)
{
    TableId bigQueryTableId = tableIds.get(tableId);
    Table table = bigQuery.getTable(bigQueryTableId != null ? bigQueryTableId : tableId);
    if (table != null) {
        tableIds.putIfAbsent(tableId, table.getTableId());
        datasetIds.putIfAbsent(toDatasetId(tableId), toDatasetId(table.getTableId()));
    }
    return table;
}
 

@Test
public void testGetTable() {
  String expectedTableName = "test_table";

  dataset.create(expectedTableName, StandardTableDefinition.newBuilder().build());
  Table actualTable = datasetSnippets.getTable(expectedTableName);

  assertNotNull(actualTable);
  assertEquals(expectedTableName, actualTable.getTableId().getTable());

  bigquery.delete(TableId.of(DATASET, expectedTableName));
}
 

@Test
public void testCreateTable() {
  String expectedTableName = "test_table";
  String expectedFieldName = "test_field";

  Table actualTable = datasetSnippets.createTable(expectedTableName, expectedFieldName);
  assertNotNull(actualTable);
  assertEquals(expectedTableName, actualTable.getTableId().getTable());
  assertEquals(1, actualTable.getDefinition().getSchema().getFields().size());

  Field actualField = actualTable.getDefinition().getSchema().getFields().get(0);
  assertEquals(expectedFieldName, actualField.getName());

  bigquery.delete(TableId.of(DATASET, expectedTableName));
}
 

@Test
public void testDelete() {
  Table doomedTable =
      bigquery.create(TableInfo.of(DOOMED_TABLE_ID, StandardTableDefinition.of(SCHEMA)));
  TableSnippets doomedTableSnippets = new TableSnippets(doomedTable);
  assertTrue(doomedTableSnippets.delete());
}
 

@Override
public Table get() {
  if (this.expiryTimeNano < System.nanoTime()) {
    return null;
  }
  return this.table;
}