/** Example of running a query with timestamp query parameters. */ public void runQueryWithTimestampParameters() throws InterruptedException { // [START bigquery_query_params_timestamps] // BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService(); DateTime timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeZone.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.getMillis() * 1000)) .build(); // Print the results. DateTimeFormatter formatter = ISODateTimeFormat.dateTimeNoMillis().withZoneUTC(); for (FieldValueList row : bigquery.query(queryConfig).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))); System.out.printf("\n"); } // [END bigquery_query_params_timestamps] }
/** 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] } }
/** 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 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] }
/** 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] }
/** 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] }
/** 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] }
@Test public void testBytesParameter() throws Exception { String query = "SELECT BYTE_LENGTH(@p) AS length"; QueryParameterValue bytesParameter = QueryParameterValue.bytes(new byte[] {1, 3}); QueryJobConfiguration config = QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setUseLegacySql(false) .addNamedParameter("p", bytesParameter) .build(); TableResult result = bigquery.query(config); int rowCount = 0; for (FieldValueList row : result.getValues()) { rowCount++; assertEquals(2, row.get(0).getLongValue()); assertEquals(2, row.get("length").getLongValue()); } assertEquals(1, rowCount); }
@Test public void testNamedQueryParameters() throws InterruptedException { String query = "SELECT TimestampField, StringField, BooleanField FROM " + TABLE_ID.getTable() + " WHERE StringField = @stringParam" + " AND IntegerField IN UNNEST(@integerList)"; QueryParameterValue stringParameter = QueryParameterValue.string("stringValue"); QueryParameterValue intArrayParameter = QueryParameterValue.array(new Integer[] {3, 4}, Integer.class); QueryJobConfiguration config = QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setUseLegacySql(false) .addNamedParameter("stringParam", stringParameter) .addNamedParameter("integerList", intArrayParameter) .build(); TableResult result = bigquery.query(config); assertEquals(QUERY_RESULT_SCHEMA, result.getSchema()); assertEquals(2, Iterables.size(result.getValues())); }
/** 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] }
@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()); }
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(); }
@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()); }
/** * Returns a BigQuery Copy Job for the given the query to be run. Job's id is chosen by the * service. */ public static QueryJobConfiguration of(String query) { return newBuilder(query).build(); }
@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 testToBuilder() { compareQueryJobConfiguration( QUERY_JOB_CONFIGURATION, QUERY_JOB_CONFIGURATION.toBuilder().build()); QueryJobConfiguration job = QUERY_JOB_CONFIGURATION.toBuilder().setQuery("New BigQuery SQL").build(); assertEquals("New BigQuery SQL", job.getQuery()); job = job.toBuilder().setQuery(QUERY).build(); compareQueryJobConfiguration(QUERY_JOB_CONFIGURATION, job); }
@Test public void testQueryDryRun() throws Exception { // https://github.com/googleapis/google-cloud-java/issues/2479 EasyMock.replay(bigqueryRpcMock); thrown.expect(UnsupportedOperationException.class); options .toBuilder() .setRetrySettings(ServiceOptions.getDefaultRetrySettings()) .build() .getService() .query(QueryJobConfiguration.newBuilder("foo").setDryRun(true).build()); } }
@Test public void testToBuilderIncomplete() { QueryJobConfiguration job = QueryJobConfiguration.of(QUERY); compareQueryJobConfiguration(job, job.toBuilder().build()); }
@SuppressWarnings("unchecked") static QueryJobConfiguration fromPb( com.google.api.services.bigquery.model.JobConfiguration jobPb) { return new Builder(jobPb).build(); }