Java QueryResult类代码示例

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
/**
 * Example of getting the results of query.
 */
// [TARGET getQueryResults(JobId, QueryResultsOption...)]
// [VARIABLE "SELECT unique(corpus) FROM [bigquery-public-data:samples.shakespeare]"]
public QueryResponse queryResults(final String query) throws InterruptedException {
  // [START queryResults]
  QueryRequest request = QueryRequest.of(query);
  QueryResponse response = bigquery.query(request);
  // Wait for things to finish
  while (!response.jobCompleted()) {
    Thread.sleep(1000);
    response = bigquery.getQueryResults(response.getJobId());
  }
  if (response.hasErrors()) {
    // handle errors
  }
  QueryResult result = response.getResult();
  Iterator<List<FieldValue>> rowIterator = result.iterateAll();
  while (rowIterator.hasNext()) {
    List<FieldValue> row = rowIterator.next();
    // do something with the data
  }
  // [END queryResults]
  return response;
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
@Test
public void runQuery() throws Exception {
  QueryRequest queryRequest = QueryRequest.newBuilder("SELECT * FROM [sample:table] LIMIT 1000")
      .setPageSize(1000L)
      .setUseQueryCache(true)
      .setUseLegacySql(useLegacySql)
      .build();

  QueryResponse mockQueryResponse = mock(QueryResponse.class);
  // First pretend we haven't finished running the query, second time around its completed.
  when(mockQueryResponse.jobCompleted()).thenReturn(false).thenReturn(true);
  when(mockQueryResponse.getJobId()).thenReturn(jobId);
  when(mockQueryResponse.hasErrors()).thenReturn(false);

  when(mockBigquery.query(queryRequest)).thenReturn(mockQueryResponse);
  when(mockBigquery.getQueryResults(jobId)).thenReturn(mockQueryResponse);

  QueryResult mockQueryResult = mock(QueryResult.class);
  when(mockQueryResponse.getResult()).thenReturn(mockQueryResult);

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

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
/**
 * Example of running a query.
 */
// [TARGET query(QueryRequest)]
// [VARIABLE "SELECT unique(corpus) FROM [bigquery-public-data:samples.shakespeare]"]
public QueryResponse runQuery(String query) throws InterruptedException {
  // [START runQuery]
  QueryRequest request = QueryRequest.of(query);
  QueryResponse response = bigquery.query(request);
  // Wait for things to finish
  while (!response.jobCompleted()) {
    Thread.sleep(1000);
    response = bigquery.getQueryResults(response.getJobId());
  }
  if (response.hasErrors()) {
    // handle errors
  }
  System.out.println("A query command :\n" + query + "\nwas executed! Executing result lists below:");
  QueryResult result = response.getResult();
  Iterator<List<FieldValue>> rowIterator = result.iterateAll();
  while (rowIterator.hasNext()) {
    List<FieldValue> row = rowIterator.next();
    Integer columnsCount = row.size();
    StringBuilder sb = new StringBuilder();
    for(int i = 0; i < columnsCount; i++){
  	  sb.append(row.get(i).getStringValue() + ", ");
    }
    System.out.println(sb.toString());
  }
  // [END runQuery]
  return response;
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
/**
 * Example of running a query with query parameters.
 */
// [TARGET query(QueryRequest)]
// [VARIABLE "SELECT distinct(corpus) FROM `bigquery-public-data.samples.shakespeare` where word_count > @wordCount"]
public QueryResponse runQueryWithParameters(String query) throws InterruptedException {
  // [START runQueryWithParameters]
  QueryRequest request = QueryRequest.newBuilder(query)
      .setUseLegacySql(false) // standard SQL is required to use query parameters
      .addNamedParameter("wordCount", QueryParameterValue.int64(5))
      .build();
  QueryResponse response = bigquery.query(request);
  // Wait for things to finish
  while (!response.jobCompleted()) {
    Thread.sleep(1000);
    response = bigquery.getQueryResults(response.getJobId());
  }
  if (response.hasErrors()) {
    // handle errors
  }
  QueryResult result = response.getResult();
  Iterator<List<FieldValue>> rowIterator = result.iterateAll();
  while (rowIterator.hasNext()) {
    List<FieldValue> row = rowIterator.next();
    // do something with the data
  }
  // [END runQueryWithParameters]
  return response;
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
public static void runQuery(QueryJobConfiguration queryConfig)
    throws TimeoutException, InterruptedException {
  BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

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

  // Get the results.
  QueryResponse response = bigquery.getQueryResults(jobId);
  QueryResult result = response.getResult();

  // Print all pages of the results.
  while (result != null) {
    for (List<FieldValue> row : result.iterateAll()) {
      for (FieldValue val : row) {
        System.out.printf("%s,", val.toString());
      }
      System.out.printf("\n");
    }

    result = result.getNextPage();
  }
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
/**
 * Executes a query request and returns the results. A timeout is required to avoid
 * waiting for an indeterminate amount of time. If the query fails to complete within the timeout it is aborted.
 *
 * @param queryRequest request describing the query to execute
 * @param timeout maximum amount of time to wait for job completion before attempting to cancel
 * @return result of the query.
 * @throws StageException if the query does not complete within the requested timeout or there is an error
 * executing the query.
 */
public QueryResult runQuery(QueryRequest queryRequest, long timeout) throws
    StageException {
  checkArgument(timeout >= 1000, "Timeout must be at least one second.");
  Instant maxTime = Instant.now().plusMillis(timeout);

  QueryResponse response = bigquery.query(queryRequest);
  while(!response.jobCompleted()) {
    if (Instant.now(clock).isAfter(maxTime) || !ThreadUtil.sleep(100)) {
      if (bigquery.cancel(response.getJobId())) {
        LOG.info("Job {} cancelled successfully.", response.getJobId());
      } else {
        LOG.warn("Job {} not found", response.getJobId());
      }
      throw new StageException(Errors.BIGQUERY_00);
    }
    response = bigquery.getQueryResults(response.getJobId());
  }

  if (response.hasErrors()) {
    String errorMsg = response.getExecutionErrors().get(0).toString();
    throw new StageException(Errors.BIGQUERY_02, errorMsg);
  }

  // Get the results.
  return response.getResult();
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
@Before
public void setUp() throws Exception {
  mockBigquery = mock(BigQuery.class);
  jobId = JobId.of("test-project", "datacollector");
  mockResult = mock(QueryResult.class);

  mockStatic(ServiceAccountCredentials.class);
  mockStatic(GoogleCredentials.class);
  ServiceAccountCredentials serviceAccountCredentials = mock(ServiceAccountCredentials.class);
  when(ServiceAccountCredentials.fromStream(any())).thenReturn(serviceAccountCredentials);
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
public static void main(String... args) throws Exception {
  // [START bigquery_simple_app_client]
  BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
  // [END bigquery_simple_app_client]
  // [START bigquery_simple_app_query]
  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();

  // 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());
  }
  // [END bigquery_simple_app_query]

  // [START bigquery_simple_app_print]
  // Get the results.
  QueryResponse response = bigquery.getQueryResults(jobId);

  QueryResult result = response.getResult();

  // Print all pages of the results.
  for (FieldValueList row : result.iterateAll()) {
    String url = row.get("url").getStringValue();
    long viewCount = row.get("view_count").getLongValue();
    System.out.printf("url: %s views: %d%n", url, viewCount);
  }
  // [END bigquery_simple_app_print]
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
/**
 * Query the Shakespeare dataset for words with count at least {@code minWordCount} in the corpus
 * {@code corpus}.
 */
// [START bigquery_query_params]
private static void runNamed(final String corpus, final long minWordCount)
    throws InterruptedException {
  BigQuery bigquery =
      new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());

  String queryString =
      "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";
  QueryJobConfiguration queryRequest =
      QueryJobConfiguration.newBuilder(queryString)
          .addNamedParameter("corpus", QueryParameterValue.string(corpus))
          .addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount))
          // Standard SQL syntax is required for parameterized queries.
          // See: https://cloud.google.com/bigquery/sql-reference/
          .setUseLegacySql(false)
          .build();

  // Execute the query.
  QueryResponse response = bigquery.query(queryRequest);

  // Wait for the job to finish (if the query takes more than 10 seconds to complete).
  while (!response.jobCompleted()) {
    Thread.sleep(1000);
    response = bigquery.getQueryResults(response.getJobId());
  }

  // Check for errors.
  if (response.hasErrors()) {
    String firstError = "";
    if (response.getExecutionErrors().size() != 0) {
      firstError = response.getExecutionErrors().get(0).getMessage();
    }
    throw new RuntimeException(firstError);
  }

  // Print all pages of the results.
  QueryResult result = response.getResult();
  while (result != null) {
    for (List<FieldValue> row : result.iterateAll()) {
      System.out.printf("%s: %d\n", row.get(0).getStringValue(), row.get(1).getLongValue());
    }

    result = result.getNextPage();
  }
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
/**
 * Query the baby names database to find the most popular names for a gender in a list of states.
 */
// [START bigquery_query_params_arrays]
private static void runArray(String gender, String[] states) throws InterruptedException {
  BigQuery bigquery =
      new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());

  String queryString =
      "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;";
  QueryJobConfiguration queryRequest =
      QueryJobConfiguration.newBuilder(queryString)
          .addNamedParameter("gender", QueryParameterValue.string(gender))
          .addNamedParameter("states", QueryParameterValue.array(states, String.class))
          // Standard SQL syntax is required for parameterized queries.
          // See: https://cloud.google.com/bigquery/sql-reference/
          .setUseLegacySql(false)
          .build();

  // Execute the query.
  QueryResponse response = bigquery.query(queryRequest);

  // Wait for the job to finish (if the query takes more than 10 seconds to complete).
  while (!response.jobCompleted()) {
    Thread.sleep(1000);
    response = bigquery.getQueryResults(response.getJobId());
  }

  // Check for errors.
  if (response.hasErrors()) {
    String firstError = "";
    if (response.getExecutionErrors().size() != 0) {
      firstError = response.getExecutionErrors().get(0).getMessage();
    }
    throw new RuntimeException(firstError);
  }

  // Print all pages of the results.
  QueryResult result = response.getResult();
  while (result != null) {
    for (List<FieldValue> row : result.iterateAll()) {
      System.out.printf("%s: %d\n", row.get(0).getStringValue(), row.get(1).getLongValue());
    }

    result = result.getNextPage();
  }
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
private static void runTimestamp() throws InterruptedException {
  BigQuery bigquery =
      new BigQueryOptions.DefaultBigqueryFactory().create(BigQueryOptions.getDefaultInstance());

  DateTime timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeZone.UTC);

  String queryString = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";
  QueryJobConfiguration queryRequest =
      QueryJobConfiguration.newBuilder(queryString)
          .addNamedParameter(
              "ts_value",
              QueryParameterValue.timestamp(
                  // Timestamp takes microseconds since 1970-01-01T00:00:00 UTC
                  timestamp.getMillis() * 1000))
          // Standard SQL syntax is required for parameterized queries.
          // See: https://cloud.google.com/bigquery/sql-reference/
          .setUseLegacySql(false)
          .build();

  // Execute the query.
  QueryResponse response = bigquery.query(queryRequest);

  // Wait for the job to finish (if the query takes more than 10 seconds to complete).
  while (!response.jobCompleted()) {
    Thread.sleep(1000);
    response = bigquery.getQueryResults(response.getJobId());
  }

  // Check for errors.
  if (response.hasErrors()) {
    String firstError = "";
    if (response.getExecutionErrors().size() != 0) {
      firstError = response.getExecutionErrors().get(0).getMessage();
    }
    throw new RuntimeException(firstError);
  }

  // Print all pages of the results.
  QueryResult result = response.getResult();
  DateTimeFormatter formatter = ISODateTimeFormat.dateTimeNoMillis().withZoneUTC();
  while (result != null) {
    for (List<FieldValue> row : result.iterateAll()) {
      System.out.printf(
          "%s\n",
          formatter.print(
              new DateTime(
                  // 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, DateTimeZone.UTC)));
    }

    result = result.getNextPage();
  }
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
@VisibleForTesting
QueryResult runQuery(QueryRequest queryRequest) throws StageException {
  return delegate.runQuery(queryRequest, conf.timeout * 1000);
}
 

import com.google.cloud.bigquery.QueryResult; //导入依赖的package包/类
@Test
public void testProduce() throws Exception {
  File tempFile = File.createTempFile("gcp", "json");
  tempFile.deleteOnExit();

  BigQuerySourceConfig conf = new BigQuerySourceConfig();
  conf.credentials.projectId = "test";
  conf.credentials.path = tempFile.getAbsolutePath();
  conf.credentials.credentialsProvider = CredentialsProviderType.JSON_PROVIDER;
  conf.query = "SELECT * FROM [test:table]";

  QueryResponse mockQueryResponse = mock(QueryResponse.class);
  // First pretend we haven't finished running the query, second time around its completed.
  when(mockQueryResponse.jobCompleted()).thenReturn(false).thenReturn(true);
  when(mockQueryResponse.getJobId()).thenReturn(jobId);
  when(mockQueryResponse.hasErrors()).thenReturn(false);

  when(mockBigquery.query(any())).thenReturn(mockQueryResponse);
  when(mockBigquery.getQueryResults(jobId)).thenReturn(mockQueryResponse);

  QueryResult mockQueryResult = mock(QueryResult.class);
  when(mockQueryResponse.getResult()).thenReturn(mockQueryResult);

  List<List<FieldValue>> resultSet = new ArrayList<>(1);
  resultSet.add(TestBigQueryDelegate.createTestValues());

  when(mockResult.getSchema()).thenReturn(TestBigQueryDelegate.createTestSchema());
  when(mockResult.getValues()).thenReturn(resultSet);

  BigQuerySource bigquerySource = spy(new BigQuerySource(conf));
  doReturn(mockBigquery).when(bigquerySource).getBigQuery(any());
  doReturn(mockResult).when(bigquerySource).runQuery(any());

  SourceRunner runner = new SourceRunner.Builder(BigQueryDSource.class, bigquerySource)
      .addOutputLane("lane")
      .build();

  try {
    runner.runInit();

    StageRunner.Output output = runner.runProduce(null, 1000);
    List<Record> records = output.getRecords().get("lane");
    assertEquals(1, records.size());
    assertEquals(10, records.get(0).get().getValueAsListMap().size());
    assertEquals("nested string", records.get(0).get("/j/x").getValueAsString());
    assertEquals("z", records.get(0).get("/j/y/z").getValueAsString());
  } finally {
    runner.runDestroy();
  }
}