/** 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] }
bq.query(QueryJobConfiguration.newBuilder(request).setUseLegacySql(false).build());
/** 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] }
bq.query(QueryJobConfiguration.newBuilder(request).setUseLegacySql(false).build());
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; }
/** * Use standard SQL syntax for queries. * See: https://cloud.google.com/bigquery/sql-reference/ * * @param query the standard (non legacy) SQL statement * * @return a {@link BigQueryOperation} instance to be executed by the {@link BigQueryOperator} */ public BigQueryOperation<T> query(String query) { final QueryJobConfiguration queryConfig = newBuilder(query) .setUseLegacySql(false) .build(); final JobId jobId = JobId.of(UUID.randomUUID().toString()); return job(JobInfo.newBuilder(queryConfig).setJobId(jobId).build()); }
.setUseLegacySql(false) .build();
QueryJobConfiguration.newBuilder("SELECT * FROM " + tableName) .setDefaultDataset(DatasetId.of(DATASET)) .setUseLegacySql(true) .build(); TableResult result = bigquery.query(config);
+ tableName) .setDefaultDataset(DatasetId.of(DATASET)) .setUseLegacySql(true) .build(); TableResult result = bigquery.query(config);
QueryJobConfiguration.newBuilder(query) .setDefaultDataset(DatasetId.of(DATASET)) .setUseLegacySql(false) .addPositionalParameter(stringParameter) .addPositionalParameter(timestampParameter)
@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())); }