/** 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 query and saving the results to a table. */ public void runQueryLargeResults(String destinationDataset, String destinationTable) throws InterruptedException { // [START bigquery_query_legacy_large_results] // 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 = // To use legacy SQL syntax, set useLegacySql to true. QueryJobConfiguration.newBuilder(query) .setUseLegacySql(true) // Save the results of the query to a permanent table. .setDestinationTable(TableId.of(destinationDataset, destinationTable)) // Allow results larger than the maximum response size. // If true, a destination table must be set. .setAllowLargeResults(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_large_results] }
@Override QueryJobConfiguration setProjectId(String projectId) { Builder builder = toBuilder(); if (getDestinationTable() != null && Strings.isNullOrEmpty(getDestinationTable().getProject())) { builder.setDestinationTable(getDestinationTable().setProjectId(projectId)); } if (getDefaultDataset() != null) { builder.setDefaultDataset(getDefaultDataset().setProjectId(projectId)); } return builder.build(); }
QueryJobConfiguration.newBuilder(query) .setAllowLargeResults(true) .setDestinationTable(destinationTable) .build(); JobOption jobOption = JobOption.fields();
private QueryResponse queryWithLarge(BigQuery bigquery, QueryRequest queryRequest, String projectId) { 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(); Job job = bigquery.create(JobInfo.of(jobConfiguration)); QueryResponse queryResponse = bigquery.getQueryResults(job.getJobId()); queryResponse = loopQueryResponse(bigquery, queryResponse); bigquery.delete(tableId); return queryResponse; }
private QueryResponse queryWithLarge(BigQuery bigquery, QueryRequest queryRequest, String projectId) { 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(); Job job = bigquery.create(JobInfo.of(jobConfiguration)); QueryResponse queryResponse = bigquery.getQueryResults(job.getJobId()); queryResponse = loopQueryResponse(bigquery, queryResponse); bigquery.delete(tableId); return queryResponse; }
@Override QueryJobConfiguration setProjectId(String projectId) { Builder builder = toBuilder(); if (getDestinationTable() != null && Strings.isNullOrEmpty(getDestinationTable().getProject())) { builder.setDestinationTable(getDestinationTable().setProjectId(projectId)); } if (getDefaultDataset() != null) { builder.setDefaultDataset(getDefaultDataset().setProjectId(projectId)); } return builder.build(); }
@Test public void testWaitForAndGetQueryResults() throws InterruptedException { QueryJobConfiguration jobConfig = QueryJobConfiguration.newBuilder("SELECT 1").setDestinationTable(TABLE_ID1).build(); QueryStatistics jobStatistics = QueryStatistics.newBuilder()
@Test public void testWaitForAndGetQueryResultsEmpty() throws InterruptedException { QueryJobConfiguration jobConfig = QueryJobConfiguration.newBuilder("CREATE VIEW").setDestinationTable(TABLE_ID1).build(); QueryStatistics jobStatistics = QueryStatistics.newBuilder()
QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setDestinationTable(destinationTable) .build(); Job remoteJob = bigquery.create(JobInfo.of(configuration));
@Test public void testQueryJobWithDryRun() throws InterruptedException, TimeoutException { String tableName = "test_query_job_table"; String query = "SELECT TimestampField, StringField, BooleanField FROM " + TABLE_ID.getTable(); TableId destinationTable = TableId.of(DATASET, tableName); QueryJobConfiguration configuration = QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setDestinationTable(destinationTable) .setDryRun(true) .build(); Job remoteJob = bigquery.create(JobInfo.of(configuration)); assertNull(remoteJob.getJobId().getJob()); assertEquals(DONE, remoteJob.getStatus().getState()); assertNotNull(remoteJob.getConfiguration()); }
@Test public void testCancelJob() throws InterruptedException, TimeoutException { String destinationTableName = "test_cancel_query_job_table"; String query = "SELECT TimestampField, StringField, BooleanField FROM " + TABLE_ID.getTable(); TableId destinationTable = TableId.of(DATASET, destinationTableName); QueryJobConfiguration configuration = QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setDestinationTable(destinationTable) .build(); Job remoteJob = bigquery.create(JobInfo.of(configuration)); assertTrue(remoteJob.cancel()); remoteJob = remoteJob.waitFor(); assertNull(remoteJob.getStatus().getError()); }
@Test public void testSetProjectIdDoNotOverride() { QueryJobConfiguration configuration = QUERY_JOB_CONFIGURATION .toBuilder() .setDestinationTable(TABLE_ID.setProjectId(TEST_PROJECT_ID)) .build() .setProjectId("update-only-on-dataset"); assertEquals("update-only-on-dataset", configuration.getDefaultDataset().getProject()); assertEquals(TEST_PROJECT_ID, configuration.getDestinationTable().getProject()); }