com.google.cloud.bigquery.QueryJobConfiguration Java Examples

@Test
public void shouldRunQueryJobInTestMode() throws Exception {
  final TableId table = TableId.of("foo", "bar", "baz");

  final Task<TableId> task = Task.named("task")
      .ofType(TableId.class)
      .output(BigQueryOutput.create(table))
      .operator(BigQueryOperator.create())
      .process((stagingTable, bq) -> bq.job(
          JobInfo.of(QueryJobConfiguration.newBuilder("SELECT foo FROM input")
              .setDestinationTable(stagingTable.tableId())
              .build()))
          .success(response -> stagingTable.publish()));

  try (TestScope scope = FloTesting.scope()) {

    final TableId result = FloRunner.runTask(task).future()
        .get(30, SECONDS);

    assertThat(result, is(table));
    assertThat(BigQueryMocking.mock().tablePublished(table), is(true));
    assertThat(BigQueryMocking.mock().tableExists(table), is(true));
  }
}
 

/** Example of running a query with the cache disabled. */
public void runUncachedQuery() throws TimeoutException, InterruptedException {
  // [START bigquery_query_no_cache]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(query)
          // Disable the query cache to force live query evaluation.
          .setUseQueryCache(false)
          .build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_no_cache]
}
 

/** Example of running a query and saving the results to a table. */
public void runQueryPermanentTable(String destinationDataset, String destinationTable)
    throws InterruptedException {
  // [START bigquery_query_destination_table]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  // String destinationDataset = 'my_destination_dataset';
  // String destinationTable = 'my_destination_table';
  String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
  QueryJobConfiguration queryConfig =
      // Note that setUseLegacySql is set to false by default
      QueryJobConfiguration.newBuilder(query)
          // Save the results of the query to a permanent table.
          .setDestinationTable(TableId.of(destinationDataset, destinationTable))
          .build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_destination_table]
}
 

/** Example of running a Legacy SQL query. */
public void runLegacySqlQuery() throws InterruptedException {
  // [START bigquery_query_legacy]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String query = "SELECT corpus FROM [bigquery-public-data:samples.shakespeare] GROUP BY corpus;";
  QueryJobConfiguration queryConfig =
      // To use legacy SQL syntax, set useLegacySql to true.
      QueryJobConfiguration.newBuilder(query).setUseLegacySql(true).build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_legacy]
}
 

private TableResult queryWithLarge(BigQuery bigquery, QueryJobConfiguration queryRequest, String projectId,
                                   BigQuery.JobOption... options) {
    String tempDataset = genTempName("dataset");
    String tempTable = genTempName("table");
    bigquery.create(DatasetInfo.of(tempDataset));
    TableId tableId = TableId.of(projectId, tempDataset, tempTable);
    QueryJobConfiguration jobConfiguration = QueryJobConfiguration
            .newBuilder(queryRequest.getQuery())
            .setAllowLargeResults(true)
            .setUseLegacySql(queryRequest.useLegacySql())
            .setDestinationTable(tableId)
            .build();
    try {
        return query(bigquery, jobConfiguration, projectId, options);
    } finally {
        bigquery.delete(tableId);
    }
}
 

/** Example of creating a query job. */
// [TARGET create(JobInfo, JobOption...)]
// [VARIABLE "SELECT field FROM my_dataset_name.my_table_name"]
public Job createJob(String query) {
  // [START ]
  Job job = null;
  JobConfiguration jobConfiguration = QueryJobConfiguration.of(query);
  JobInfo jobInfo = JobInfo.of(jobConfiguration);
  try {
    job = bigquery.create(jobInfo);
  } catch (BigQueryException e) {
    // the job was not created
  }
  // [END ]
  return job;
}
 

/** Example of running a query. */
// [TARGET query(QueryJobConfiguration, JobOption...)]
public void runQuery() throws InterruptedException {
  // [START bigquery_query]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
  QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder(query).build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query]
}
 

@Test
public void runQuery() throws Exception {
  QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder("SELECT * FROM [sample:table] LIMIT 1000")
      .setUseQueryCache(true)
      .setUseLegacySql(useLegacySql)
      .build();

  TableResult mockQueryResponse = mock(TableResult.class);
  Job mockJob = mock(Job.class);
  JobStatus mockJobStatus = mock(JobStatus.class);

  // First pretend we haven't finished running the query, second time around its completed.
  when(mockJob.isDone()).thenReturn(false).thenReturn(true);
  when(mockJob.getJobId()).thenReturn(jobId);
  when(mockJobStatus.getError()).thenReturn(null);
  when(mockJob.getStatus()).thenReturn(mockJobStatus);

  when(mockBigquery.create((JobInfo)any())).thenReturn(mockJob);
  when(mockBigquery.cancel(jobId)).thenReturn(true);
  when(mockJob.getQueryResults()).thenReturn(mockQueryResponse);

  BigQueryDelegate delegate = new BigQueryDelegate(mockBigquery, useLegacySql);
  delegate.runQuery(queryConfig, 1000, 1000);
}
 

@Test
public void testLoadBytes() throws ExecutionException, InterruptedException {
	byte[] byteArray =
			"CountyId,State,County\n1001,Alabama,Autauga County\n".getBytes();
	ByteArrayInputStream byteStream = new ByteArrayInputStream(byteArray);

	ListenableFuture<Job> bigQueryJobFuture =
			bigQueryTemplate.writeDataToTable(TABLE_NAME, byteStream, FormatOptions.csv());

	Job job = bigQueryJobFuture.get();
	assertThat(job.getStatus().getState()).isEqualTo(JobStatus.State.DONE);

	QueryJobConfiguration queryJobConfiguration = QueryJobConfiguration
			.newBuilder("SELECT * FROM test_dataset.template_test_table").build();
	TableResult result = this.bigQuery.query(queryJobConfiguration);

	assertThat(result.getTotalRows()).isEqualTo(1);
	assertThat(
			result.getValues().iterator().next().get("State").getStringValue()).isEqualTo("Alabama");
}
 

@Test
public void testLoadFile() throws IOException, ExecutionException, InterruptedException {
	ListenableFuture<Job> bigQueryJobFuture =
			bigQueryTemplate.writeDataToTable(TABLE_NAME, dataFile.getInputStream(), FormatOptions.csv());

	Job job = bigQueryJobFuture.get();
	assertThat(job.getStatus().getState()).isEqualTo(JobStatus.State.DONE);

	QueryJobConfiguration queryJobConfiguration = QueryJobConfiguration
			.newBuilder("SELECT * FROM test_dataset.template_test_table").build();
	TableResult result = this.bigQuery.query(queryJobConfiguration);

	assertThat(result.getTotalRows()).isEqualTo(1);
	assertThat(
			result.getValues().iterator().next().get("State").getStringValue()).isEqualTo("Alabama");
}
 

@Test
public void testLoadFile_sync() throws InterruptedException {
	this.messageHandler.setSync(true);

	HashMap<String, Object> messageHeaders = new HashMap<>();
	messageHeaders.put(BigQuerySpringMessageHeaders.TABLE_NAME, TABLE_NAME);
	messageHeaders.put(BigQuerySpringMessageHeaders.FORMAT_OPTIONS, FormatOptions.csv());

	Message<File> message = MessageBuilder.createMessage(
			new File("src/test/resources/data.csv"),
			new MessageHeaders(messageHeaders));

	Job job = (Job) this.messageHandler.handleRequestMessage(message);
	assertThat(job).isNotNull();

	QueryJobConfiguration queryJobConfiguration = QueryJobConfiguration
			.newBuilder("SELECT * FROM test_dataset.test_table").build();
	TableResult result = this.bigquery.query(queryJobConfiguration);
	assertThat(result.getTotalRows()).isEqualTo(1);
}
 

@Test
public void testCsvDataUpload() throws InterruptedException {
	LinkedMultiValueMap<String, Object> map = new LinkedMultiValueMap<>();
	map.add("csvText", "name,age,location\nBob,24,Wyoming");
	map.add("tableName", TABLE_NAME);

	HttpHeaders headers = new HttpHeaders();
	HttpEntity<LinkedMultiValueMap<String, Object>> request = new HttpEntity<>(map, headers);
	ResponseEntity<String> response =
			this.restTemplate.postForEntity("/uploadCsvText", request, String.class);
	assertThat(response.getStatusCode().is2xxSuccessful()).isTrue();

	QueryJobConfiguration queryJobConfiguration = QueryJobConfiguration
			.newBuilder("SELECT * FROM " + DATASET_NAME + "." + TABLE_NAME)
			.build();

	TableResult queryResult = this.bigQuery.query(queryJobConfiguration);
	assertThat(queryResult.getTotalRows()).isEqualTo(1);

	FieldValueList row = queryResult.getValues().iterator().next();
	assertThat(row.get(0).getStringValue()).isEqualTo("Bob");
	assertThat(row.get(1).getLongValue()).isEqualTo(24);
	assertThat(row.get(2).getStringValue()).isEqualTo("Wyoming");
}
 

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

private void checkResult(Iterable<List<String>> expected) throws Exception {
  final List<List<String>> sorted = ImmutableList.sortedCopyOf(ROW_COMPARATOR, expected);

  final String query = "SELECT * REPLACE (" //
      + "FORMAT_TIMESTAMP('%FT%R:%E*SZ', submission_timestamp) AS submission_timestamp, "
      + "CAST(payload AS STRING) AS payload), " //
      + "_TABLE_SUFFIX AS table " //
      + String.format("FROM `%s.%s.*`", bq.project, bq.dataset) //
      + " ORDER BY table";

  // allow retries to handle possible delayed availability of streaming insert
  List<List<String>> actual = null;
  for (int attempt = 0; attempt < 2; attempt++) {
    actual = StreamSupport
        .stream(bq.bigquery.query(QueryJobConfiguration.of(query)).iterateAll().spliterator(),
            false)
        .map(row -> row.stream().map(FieldValue::getStringValue).collect(Collectors.toList()))
        .collect(Collectors.toList());
    if (actual.size() == sorted.size()) {
      break;
    }
  }

  assertEquals(sorted, actual);
}
 

@Test
public void testFileUpload() throws InterruptedException, IOException {
	LinkedMultiValueMap<String, Object> map = new LinkedMultiValueMap<>();
	map.add("file", csvFile);
	map.add("tableName", TABLE_NAME);

	HttpHeaders headers = new HttpHeaders();
	headers.setContentType(MediaType.MULTIPART_FORM_DATA);

	HttpEntity<LinkedMultiValueMap<String, Object>> request = new HttpEntity<>(map, headers);
	ResponseEntity<String> response =
			this.restTemplate.postForEntity("/uploadFile", request, String.class);
	assertThat(response.getStatusCode().is2xxSuccessful()).isTrue();

	QueryJobConfiguration queryJobConfiguration = QueryJobConfiguration
			.newBuilder("SELECT * FROM " + DATASET_NAME + "." + TABLE_NAME)
			.build();

	TableResult queryResult = this.bigQuery.query(queryJobConfiguration);
	assertThat(queryResult.getTotalRows()).isEqualTo(3);

	List<String> names = StreamSupport.stream(queryResult.getValues().spliterator(), false)
			.map(valueList -> valueList.get(0).getStringValue())
			.collect(Collectors.toList());
	assertThat(names).containsExactlyInAnyOrder("Nathaniel", "Diaz", "Johnson");
}
 

@Test
public void testExists() throws Exception {
  JobConfiguration jobConfig =
      QueryJobConfiguration.newBuilder(QUERY).setUseLegacySql(false).build();
  JobInfo jobInfo = JobInfo.newBuilder(jobConfig).build();
  Job job = bigquery.create(jobInfo);
  JobSnippets jobSnippets = new JobSnippets(job);
  boolean result = jobSnippets.exists();
  assertTrue(result);
}
 

@Test
public void testCancel() {
  JobConfiguration jobConfig =
      QueryJobConfiguration.newBuilder(QUERY).setUseLegacySql(false).build();
  JobInfo jobInfo = JobInfo.newBuilder(jobConfig).build();
  Job job = bigquery.create(jobInfo);
  JobSnippets jobSnippets = new JobSnippets(job);
  boolean result = jobSnippets.cancel();
  assertTrue(result);
}
 

@Override
QueryJobConfiguration parse(String... args) throws Exception {
  String message;
  if (args.length == 1) {
    return QueryJobConfiguration.of(args[0]);
  } else if (args.length > 1) {
    message = "Too many arguments.";
  } else {
    message = "Missing required query.";
  }
  throw new IllegalArgumentException(message);
}
 

@Override
void run(BigQuery bigquery, QueryJobConfiguration queryConfig) throws Exception {
  System.out.println("Running query");
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    System.out.println(row);
  }
}
 

@Test
public void testWaitFor() throws Exception {
  JobConfiguration jobConfig =
      QueryJobConfiguration.newBuilder(QUERY).setUseLegacySql(false).build();
  JobInfo jobInfo = JobInfo.newBuilder(jobConfig).build();
  Job job = bigquery.create(jobInfo);
  JobSnippets jobSnippets = new JobSnippets(job);
  boolean result = jobSnippets.waitFor();
  assertTrue(result);
}
 

@Test
public void testWaitForWithOptions() throws Exception {
  JobConfiguration jobConfig =
      QueryJobConfiguration.newBuilder(QUERY).setUseLegacySql(false).build();
  JobInfo jobInfo = JobInfo.newBuilder(jobConfig).build();
  Job job = bigquery.create(jobInfo);
  JobSnippets jobSnippets = new JobSnippets(job);
  boolean result = jobSnippets.waitForWithOptions();
  assertTrue(result);
}
 

@Test
public void testReload() throws Exception {
  JobConfiguration jobConfig =
      QueryJobConfiguration.newBuilder(QUERY).setUseLegacySql(false).build();
  JobInfo jobInfo = JobInfo.newBuilder(jobConfig).build();
  Job job = bigquery.create(jobInfo);
  JobSnippets jobSnippets = new JobSnippets(job);
  JobStatus.State result = jobSnippets.reload();
  assertEquals(JobStatus.State.DONE, result);
}
 

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 of running a query with timestamp query parameters. */
public void runQueryWithTimestampParameters() throws InterruptedException {
  // [START bigquery_query_params_timestamps]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  ZonedDateTime timestamp = LocalDateTime.of(2016, 12, 7, 8, 0, 0).atZone(ZoneOffset.UTC);
  String query = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";
  // Note: Standard SQL is required to use query parameters.
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(query)
          .addNamedParameter(
              "ts_value",
              QueryParameterValue.timestamp(
                  // Timestamp takes microseconds since 1970-01-01T00:00:00 UTC
                  timestamp.toInstant().toEpochMilli() * 1000))
          .build();

  // Print the results.
  DateTimeFormatter formatter = DateTimeFormatter.ISO_INSTANT.withZone(ZoneOffset.UTC);
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    System.out.printf(
        "%s\n",
        formatter.format(
            Instant.ofEpochMilli(
                    // Timestamp values are returned in microseconds since 1970-01-01T00:00:00
                    // UTC,
                    // but org.joda.time.DateTime constructor accepts times in milliseconds.
                    row.get(0).getTimestampValue() / 1000)
                .atOffset(ZoneOffset.UTC)));
    System.out.printf("\n");
  }
  // [END bigquery_query_params_timestamps]
}
 

/** Example of running a query with array query parameters. */
public void runQueryWithArrayParameters() throws InterruptedException {
  // [START bigquery_query_params_arrays]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String gender = "M";
  String[] states = {"WA", "WI", "WV", "WY"};
  String query =
      "SELECT name, sum(number) as count\n"
          + "FROM `bigquery-public-data.usa_names.usa_1910_2013`\n"
          + "WHERE gender = @gender\n"
          + "AND state IN UNNEST(@states)\n"
          + "GROUP BY name\n"
          + "ORDER BY count DESC\n"
          + "LIMIT 10;";
  // Note: Standard SQL is required to use query parameters.
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(query)
          .addNamedParameter("gender", QueryParameterValue.string(gender))
          .addNamedParameter("states", QueryParameterValue.array(states, String.class))
          .build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_params_arrays]
}
 

/** Example of running a query with named query parameters. */
public void runQueryWithNamedParameters() throws InterruptedException {
  // [START bigquery_query_params_named]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String corpus = "romeoandjuliet";
  long minWordCount = 250;
  String query =
      "SELECT word, word_count\n"
          + "FROM `bigquery-public-data.samples.shakespeare`\n"
          + "WHERE corpus = @corpus\n"
          + "AND word_count >= @min_word_count\n"
          + "ORDER BY word_count DESC";
  // Note: Standard SQL is required to use query parameters.
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(query)
          .addNamedParameter("corpus", QueryParameterValue.string(corpus))
          .addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount))
          .build();

  // Print the results.
  for (FieldValueList row : bigquery.query(queryConfig).iterateAll()) {
    for (FieldValue val : row) {
      System.out.printf("%s,", val.toString());
    }
    System.out.printf("\n");
  }
  // [END bigquery_query_params_named]
}
 

@Test
public void testReloadStatus() throws Exception {
  JobConfiguration jobConfig =
      QueryJobConfiguration.newBuilder(QUERY).setUseLegacySql(false).build();
  JobInfo jobInfo = JobInfo.newBuilder(jobConfig).build();
  Job job = bigquery.create(jobInfo);
  JobSnippets jobSnippets = new JobSnippets(job);
  JobStatus.State result = jobSnippets.reloadStatus();
  assertEquals(JobStatus.State.DONE, result);
}
 

TableResult query(String sql)
{
    try {
        return bigQuery.query(QueryJobConfiguration.of(sql));
    }
    catch (InterruptedException e) {
        Thread.currentThread().interrupt();
        throw new BigQueryException(BaseHttpServiceException.UNKNOWN_CODE, format("Failed to run the query [%s]", sql), e);
    }
}
 

/** Example of running a batch query. */
public void runBatchQuery() throws TimeoutException, InterruptedException {
  // [START bigquery_query_batch]
  // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  String query = "SELECT corpus FROM `bigquery-public-data.samples.shakespeare` GROUP BY corpus;";
  QueryJobConfiguration queryConfig =
      QueryJobConfiguration.newBuilder(query)
          // Run at batch priority, which won't count toward concurrent rate
          // limit.
          .setPriority(QueryJobConfiguration.Priority.BATCH)
          .build();

  // Location must match that of the dataset(s) referenced in the query.
  JobId jobId = JobId.newBuilder().setRandomJob().setLocation("US").build();
  String jobIdString = jobId.getJob();

  // API request - starts the query.
  bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

  // Check on the progress by getting the job's updated state. Once the state
  // is `DONE`, the results are ready.
  Job queryJob =
      bigquery.getJob(JobId.newBuilder().setJob(jobIdString).setLocation("US").build());
  System.out.printf(
      "Job %s in location %s currently in state: %s%n",
      queryJob.getJobId().getJob(),
      queryJob.getJobId().getLocation(),
      queryJob.getStatus().getState().toString());
  // [END bigquery_query_batch]
}
 

@Test
public void testIsDone() throws Exception {
  JobConfiguration jobConfig =
      QueryJobConfiguration.newBuilder(QUERY).setUseLegacySql(false).build();
  JobInfo jobInfo = JobInfo.newBuilder(jobConfig).build();
  Job job = bigquery.create(jobInfo);
  JobSnippets jobSnippets = new JobSnippets(job);
  jobSnippets.isDone();
  assertTrue(job.isDone());
}