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

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

}
 

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

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