Java Code Examples for com.google.cloud.bigquery.Job#waitFor()

/** Example of copying multiple tables to a destination. */
public void copyTables(String datasetId, String destinationTableId) throws InterruptedException {
  generateTableWithDdl(datasetId, "table1");
  generateTableWithDdl(datasetId, "table2");

  // [START bigquery_copy_table_multiple_source]
  TableId destinationTable = TableId.of(datasetId, destinationTableId);
  CopyJobConfiguration configuration =
      CopyJobConfiguration.newBuilder(
              destinationTable,
              Arrays.asList(TableId.of(datasetId, "table1"), TableId.of(datasetId, "table2")))
          .build();

  // Copy the tables.
  Job job = bigquery.create(JobInfo.of(configuration));
  job = job.waitFor();

  // Check the table
  StandardTableDefinition table = bigquery.getTable(destinationTable).getDefinition();
  System.out.println("State: " + job.getStatus().getState());
  System.out.printf("Copied %d rows.\n", table.getNumRows());
  // [END bigquery_copy_table_multiple_source]
}
 

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 copying the table to a destination table. */
// [TARGET copy(String, String, JobOption...)]
// [VARIABLE "my_dataset"]
// [VARIABLE "my_destination_table"]
public Job copy(String datasetName, String tableName) {
  // [START ]
  Job job = table.copy(datasetName, tableName);
  // Wait for the job to complete.
  try {
    Job completedJob =
        job.waitFor(
            RetryOption.initialRetryDelay(Duration.ofSeconds(1)),
            RetryOption.totalTimeout(Duration.ofMinutes(3)));
    if (completedJob != null && completedJob.getStatus().getError() == null) {
      // Job completed successfully
    } else {
      // Handle error case
    }
  } catch (InterruptedException e) {
    // Handle interrupted wait
  }
  // [END ]
  return job;
}
 

/** Example copying the table to a destination table. */
// [TARGET copy(TableId, JobOption...)]
// [VARIABLE "my_dataset"]
// [VARIABLE "my_destination_table"]
public Job copyTableId(String dataset, String tableName) throws BigQueryException {
  // [START bigquery_copy_table]
  TableId destinationId = TableId.of(dataset, tableName);
  JobOption options = JobOption.fields(JobField.STATUS, JobField.USER_EMAIL);
  Job job = table.copy(destinationId, options);
  // Wait for the job to complete.
  try {
    Job completedJob =
        job.waitFor(
            RetryOption.initialRetryDelay(Duration.ofSeconds(1)),
            RetryOption.totalTimeout(Duration.ofMinutes(3)));
    if (completedJob != null && completedJob.getStatus().getError() == null) {
      // Job completed successfully.
    } else {
      // Handle error case.
    }
  } catch (InterruptedException e) {
    // Handle interrupted wait
  }
  // [END bigquery_copy_table]
  return job;
}
 

/** Example of loading a parquet file from GCS to a table. */
public void loadTableGcsParquet(String datasetName) throws InterruptedException {
  // [START bigquery_load_table_gcs_parquet]
  String sourceUri = "gs://cloud-samples-data/bigquery/us-states/us-states.parquet";
  TableId tableId = TableId.of(datasetName, "us_states");
  LoadJobConfiguration configuration =
      LoadJobConfiguration.builder(tableId, sourceUri)
          .setFormatOptions(FormatOptions.parquet())
          .build();
  // Load the table
  Job loadJob = bigquery.create(JobInfo.of(configuration));
  loadJob = loadJob.waitFor();
  // Check the table
  StandardTableDefinition destinationTable = bigquery.getTable(tableId).getDefinition();
  System.out.println("State: " + loadJob.getStatus().getState());
  System.out.printf("Loaded %d rows.\n", destinationTable.getNumRows());
  // [END bigquery_load_table_gcs_parquet]
}
 

/** Example extracting data to single Google Cloud Storage file. */
// [TARGET extract(String, String, JobOption...)]
// [VARIABLE "CSV"]
// [VARIABLE "gs://my_bucket/filename.csv"]
public Job extractSingle(String format, String gcsUrl) {
  // [START bigquery_extract_table]
  Job job = table.extract(format, gcsUrl);
  // Wait for the job to complete
  try {
    Job completedJob =
        job.waitFor(
            RetryOption.initialRetryDelay(Duration.ofSeconds(1)),
            RetryOption.totalTimeout(Duration.ofMinutes(3)));
    if (completedJob != null && completedJob.getStatus().getError() == null) {
      // Job completed successfully
    } else {
      // Handle error case
    }
  } catch (InterruptedException e) {
    // Handle interrupted wait
  }
  // [END bigquery_extract_table]
  return job;
}
 

/** Example loading data from a single Google Cloud Storage file. */
// [TARGET load(FormatOptions, String, JobOption...)]
// [VARIABLE "gs://my_bucket/filename.csv"]
public Job loadSingle(String sourceUri) {
  // [START bigquery_load_table_gcs_csv]
  Job job = table.load(FormatOptions.csv(), sourceUri);
  // Wait for the job to complete
  try {
    Job completedJob =
        job.waitFor(
            RetryOption.initialRetryDelay(Duration.ofSeconds(1)),
            RetryOption.totalTimeout(Duration.ofMinutes(3)));
    if (completedJob != null && completedJob.getStatus().getError() == null) {
      // Job completed successfully
    } else {
      // Handle error case
    }
  } catch (InterruptedException e) {
    // Handle interrupted wait
  }
  // [END bigquery_load_table_gcs_csv]
  return job;
}
 

/** Example of partitioning data to a list of Google Cloud Storage files. */
// [TARGET extract(String, List, JobOption...)]
// [VARIABLE "CSV"]
// [VARIABLE "gs://my_bucket/PartitionA_*.csv"]
// [VARIABLE "gs://my_bucket/PartitionB_*.csv"]
public Job extractList(String format, String gcsUrl1, String gcsUrl2) {
  // [START ]
  List<String> destinationUris = new ArrayList<>();
  destinationUris.add(gcsUrl1);
  destinationUris.add(gcsUrl2);
  Job job = table.extract(format, destinationUris);
  // Wait for the job to complete
  try {
    Job completedJob =
        job.waitFor(
            RetryOption.initialRetryDelay(Duration.ofSeconds(1)),
            RetryOption.totalTimeout(Duration.ofMinutes(3)));
    if (completedJob != null && completedJob.getStatus().getError() == null) {
      // Job completed successfully
    } else {
      // Handle error case
    }
  } catch (InterruptedException e) {
    // Handle interrupted wait
  }
  // [END ]
  return job;
}
 

/** Example of writing a local file to a table. */
// [TARGET writer(WriteChannelConfiguration)]
// [VARIABLE "my_dataset_name"]
// [VARIABLE "my_table_name"]
// [VARIABLE FileSystems.getDefault().getPath(".", "my-data.csv")]
// [VARIABLE "us"]
public long writeFileToTable(String datasetName, String tableName, Path csvPath, String location)
    throws IOException, InterruptedException, TimeoutException {
  // [START bigquery_load_from_file]
  TableId tableId = TableId.of(datasetName, tableName);
  WriteChannelConfiguration writeChannelConfiguration =
      WriteChannelConfiguration.newBuilder(tableId).setFormatOptions(FormatOptions.csv()).build();
  // Generally, location can be inferred based on the location of the referenced dataset.
  // However,
  // it can also be set explicitly to force job execution to be routed to a specific processing
  // location.  See https://cloud.google.com/bigquery/docs/locations for more info.
  JobId jobId = JobId.newBuilder().setLocation(location).build();
  TableDataWriteChannel writer = bigquery.writer(jobId, writeChannelConfiguration);
  // Write data to writer
  try (OutputStream stream = Channels.newOutputStream(writer)) {
    Files.copy(csvPath, stream);
  } finally {
    writer.close();
  }
  // Get load job
  Job job = writer.getJob();
  job = job.waitFor();
  LoadStatistics stats = job.getStatistics();
  return stats.getOutputRows();
  // [END bigquery_load_from_file]
}
 

/** Example of undeleting a table. */
public void undeleteTable(String datasetId) throws InterruptedException {
  generateTableWithDdl(datasetId, "oops_undelete_me");

  // [START bigquery_undelete_table]
  // String datasetId = "my_dataset";
  String tableId = "oops_undelete_me";

  // Record the current time.  We'll use this as the snapshot time
  // for recovering the table.
  long snapTime = Instant.now().toEpochMilli();

  // "Accidentally" delete the table.
  bigquery.delete(TableId.of(datasetId, tableId));

  // Construct the restore-from tableID using a snapshot decorator.
  String snapshotTableId = String.format("%s@%d", tableId, snapTime);
  // Choose a new table ID for the recovered table data.
  String recoverTableId = String.format("%s_recovered", tableId);

  // Construct and run a copy job.
  CopyJobConfiguration configuration =
      CopyJobConfiguration.newBuilder(
              TableId.of(datasetId, recoverTableId), TableId.of(datasetId, snapshotTableId))
          .build();
  Job job = bigquery.create(JobInfo.of(configuration));
  job = job.waitFor();

  // Check the table
  StandardTableDefinition table =
      bigquery.getTable(TableId.of(datasetId, recoverTableId)).getDefinition();
  System.out.println("State: " + job.getStatus().getState());
  System.out.printf("Recovered %d rows.\n", table.getNumRows());
  // [END bigquery_undelete_table]
}
 

private void generateTableWithDdl(String datasetId, String tableId) throws InterruptedException {
  String sql =
      String.format(
          "CREATE TABLE %s.%s "
              + "AS "
              + "SELECT "
              + "2000 + CAST(18 * RAND() as INT64) AS year, "
              + "IF(RAND() > 0.5,\"foo\",\"bar\") AS token "
              + "FROM "
              + "UNNEST(GENERATE_ARRAY(0,5,1)) AS r",
          datasetId, tableId);
  Job job = bigquery.create(JobInfo.of(QueryJobConfiguration.newBuilder(sql).build()));
  job.waitFor();
}
 

/** Example loading data from a list of Google Cloud Storage files. */
// [TARGET load(FormatOptions, List, JobOption...)]
// [VARIABLE "gs://my_bucket/filename1.csv"]
// [VARIABLE "gs://my_bucket/filename2.csv"]
public Job loadList(String gcsUrl1, String gcsUrl2) {
  // [START ]
  List<String> sourceUris = new ArrayList<>();
  sourceUris.add(gcsUrl1);
  sourceUris.add(gcsUrl2);
  Job job = table.load(FormatOptions.csv(), sourceUris);
  // Wait for the job to complete
  try {
    Job completedJob =
        job.waitFor(
            RetryOption.initialRetryDelay(Duration.ofSeconds(1)),
            RetryOption.totalTimeout(Duration.ofMinutes(3)));
    if (completedJob != null && completedJob.getStatus().getError() == null) {
      // Job completed successfully
    } else {
      // Handle error case
    }
  } catch (InterruptedException e) {
    // Handle interrupted wait
  }
  // [END ]
  return job;
}
 

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

@ProcessElement
public void process(ProcessContext c) throws InterruptedException {
  String statement = c.element();
  Job jobInfo = issueQueryToBQ(statement);
  LOG.info("Job Info for triggered job: {}", jobInfo);
  jobInfo = jobInfo.waitFor();
  LOG.info("Job Info for finalized job: {}", jobInfo);
}
 

@ProcessElement
public void process(ProcessContext c) throws InterruptedException {
  String statement = c.element();
  Job jobInfo = issueQueryToBQ(statement);
  LOG.info("Job Info for triggered job: {}", jobInfo);
  jobInfo = jobInfo.waitFor();
  LOG.info("Job Info for finalized job: {}", jobInfo);
}
 

Job waitForJob(Job job)
{
    try {
        return job.waitFor();
    }
    catch (InterruptedException e) {
        Thread.currentThread().interrupt();
        throw new BigQueryException(BaseServiceException.UNKNOWN_CODE, format("Job %s has been interrupted", job.getJobId()), e);
    }
}
 

@Test
public void testQueryDdlCreateView() throws InterruptedException {
  String projectId = bigquery.getOptions().getProjectId();
  String datasetId = DATASET;
  String tableId = "query_ddl_create_view";

  // [START bigquery_ddl_create_view]
  // import com.google.cloud.bigquery.*;
  // String projectId = "my-project";
  // String datasetId = "my_dataset";
  // String tableId = "new_view";
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().toBuilder()
  //     .setProjectId(projectId)
  //     .build().getService();

  String sql =
      String.format(
          "CREATE VIEW `%s.%s.%s`\n"
              + "OPTIONS(\n"
              + "  expiration_timestamp=TIMESTAMP_ADD(\n"
              + "    CURRENT_TIMESTAMP(), INTERVAL 48 HOUR),\n"
              + "  friendly_name=\"new_view\",\n"
              + "  description=\"a view that expires in 2 days\",\n"
              + "  labels=[(\"org_unit\", \"development\")]\n"
              + ")\n"
              + "AS SELECT name, state, year, number\n"
              + "  FROM `bigquery-public-data.usa_names.usa_1910_current`\n"
              + "  WHERE state LIKE 'W%%';\n",
          projectId, datasetId, tableId);

  // Make an API request to run the query job.
  Job job = bigquery.create(JobInfo.of(QueryJobConfiguration.newBuilder(sql).build()));

  // Wait for the query to finish.
  job = job.waitFor();

  QueryJobConfiguration jobConfig = (QueryJobConfiguration) job.getConfiguration();
  System.out.printf(
      "Created new view \"%s.%s.%s\".\n",
      jobConfig.getDestinationTable().getProject(),
      jobConfig.getDestinationTable().getDataset(),
      jobConfig.getDestinationTable().getTable());
  // [END bigquery_ddl_create_view]

  String got = bout.toString();
  assertTrue(got.contains("Created new view "));

  // Test that listing query result rows succeeds so that generic query
  // processing tools work with DDL statements.
  TableResult results = job.getQueryResults();
  List<FieldValueList> rows = Lists.newArrayList(results.iterateAll());
  assertEquals(rows.size(), 0);
}
 

public void runQuery() throws InterruptedException {
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(
              "SELECT "
                  + "CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, "
                  + "view_count "
                  + "FROM `bigquery-public-data.stackoverflow.posts_questions` "
                  + "WHERE tags like '%google-bigquery%' "
                  + "ORDER BY favorite_count DESC LIMIT 10")
          // Use standard SQL syntax for queries.
          // See: https://cloud.google.com/bigquery/sql-reference/
          .setUseLegacySql(false)
          .build();

  List<TimeSeries> timeSeriesList = new ArrayList<>();

  long queryStartTime = System.currentTimeMillis();

  // Create a job ID so that we can safely retry.
  JobId jobId = JobId.of(UUID.randomUUID().toString());
  Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

  // Wait for the query to complete.
  queryJob = queryJob.waitFor();

  // Check for errors
  if (queryJob == null) {
    throw new RuntimeException("Job no longer exists");
  } else if (queryJob.getStatus().getError() != null) {
    // You can also look at queryJob.getStatus().getExecutionErrors() for all
    // errors, not just the latest one.
    throw new RuntimeException(queryJob.getStatus().getError().toString());
  }

  // Log the result metrics.
  TableResult result = queryJob.getQueryResults();

  long queryEndTime = System.currentTimeMillis();
  // Add query duration metric.
  timeSeriesList.add(prepareMetric(QUERY_DURATION_METRIC, queryEndTime - queryStartTime));

  // Add rows returned metric.
  timeSeriesList.add(prepareMetric(ROWS_RETURNED_METRIC, result.getTotalRows()));

  // Prepares the time series request
  CreateTimeSeriesRequest request =
      CreateTimeSeriesRequest.newBuilder()
          .setName(projectName)
          .addAllTimeSeries(timeSeriesList)
          .build();

  createMetricsIfNeeded();
  client.createTimeSeries(request);
  os.println("Done writing metrics.");

  mostRecentRunResult = result;
}
 

@NewSpan()
public <T> List<T> processQuery(String queryString, Class<T> t, boolean updateCache) throws Exception {

	QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(queryString).build();
	// Create a job ID so that we can safely retry.
	JobId jobId = JobId.of(UUID.randomUUID().toString());
	Job queryJob = bqInstance.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

	// Wait for the query to complete.
	queryJob = queryJob.waitFor();

	// Check for errors
	if (queryJob == null) {
		throw new RuntimeException("Job no longer exists");
	} else if (queryJob.getStatus().getError() != null) {
		// You can also look at queryJob.getStatus().getExecutionErrors() for all
		// errors, not just the latest one.
		throw new RuntimeException(queryJob.getStatus().getError().toString());
	}

	// Get the results.
	TableResult result = queryJob.getQueryResults();
	// init counters
	long count = 0;
	long total = result.getTotalRows();
	LOGGER.info("Fetched " + total + " products.");
	long start = System.currentTimeMillis();
	// Print all pages of the results.
	List<T> results = new ArrayList<T>();
	// filter
	String filter = "[^A-Za-z0-9 ()-]";
	while (result != null) {
		for (List<FieldValue> row : result.iterateAll()) {
			Object type = t.getConstructor().newInstance();
			String productName = null;
			// query for sku, name
			if (type instanceof Product) {
				productName = row.get(1).getValue() != null
						? row.get(1).getStringValue().replaceAll(filter, "").trim()
						: "";
				if (!updateCache) {
					Product product = new Product();
					product.setSku(row.get(0).getValue().toString());

					product.setName(productName);
					results.add(t.cast(product));
				}
				// query for name
			} else if (type instanceof String) {
				productName = row.get(0).getValue() != null
						? row.get(0).getStringValue().replaceAll(filter, "").trim()
						: "";
				if (!updateCache) {
					results.add(t.cast(productName));
				}
			}

			if (updateCache) {
				getZSetOps().add(productName.toLowerCase() + ":" + productName, 0);
			}
			count++;
		}
		LOGGER.info("Processed " + count + " records..");
		result = result.getNextPage();
	}
	if (updateCache) {
		long actual = getZSetOps().zCard();
		LOGGER.info("Indexing completed for " + count + " products.");
		LOGGER.info("Products in cache: " + actual);
		LOGGER.info("Duplicate product names: " + (count - actual));
		LOGGER.info("Time taken: " + (System.currentTimeMillis() - start) / 1000 + "s.");
	}

	return results;

}