use of com.google.cloud.bigquery.QueryResponse in project google-cloud-java by GoogleCloudPlatform.
the class BigQuerySnippets method runQuery.
/**
* 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
}
QueryResult result = response.getResult();
for (List<FieldValue> row : result.iterateAll()) {
// do something with the data
}
// [END runQuery]
return response;
}
use of com.google.cloud.bigquery.QueryResponse in project google-cloud-java by GoogleCloudPlatform.
the class InsertDataAndQueryTable method main.
public static void main(String... args) throws InterruptedException {
// Create a service instance
BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
// Create a dataset
String datasetId = "my_dataset_id";
bigquery.create(DatasetInfo.newBuilder(datasetId).build());
TableId tableId = TableId.of(datasetId, "my_table_id");
// Table field definition
Field stringField = Field.of("StringField", Field.Type.string());
// Table schema definition
Schema schema = Schema.of(stringField);
// Create a table
StandardTableDefinition tableDefinition = StandardTableDefinition.of(schema);
bigquery.create(TableInfo.of(tableId, tableDefinition));
// Define rows to insert
Map<String, Object> firstRow = new HashMap<>();
Map<String, Object> secondRow = new HashMap<>();
firstRow.put("StringField", "value1");
secondRow.put("StringField", "value2");
// Create an insert request
InsertAllRequest insertRequest = InsertAllRequest.newBuilder(tableId).addRow(firstRow).addRow(secondRow).build();
// Insert rows
InsertAllResponse insertResponse = bigquery.insertAll(insertRequest);
// Check if errors occurred
if (insertResponse.hasErrors()) {
System.out.println("Errors occurred while inserting rows");
}
// Create a query request
QueryRequest queryRequest = QueryRequest.newBuilder("SELECT * FROM my_dataset_id.my_table_id").setMaxWaitTime(60000L).setPageSize(1000L).build();
// Request query to be executed and wait for results
QueryResponse queryResponse = bigquery.query(queryRequest);
while (!queryResponse.jobCompleted()) {
Thread.sleep(1000L);
queryResponse = bigquery.getQueryResults(queryResponse.getJobId());
}
// Read rows
System.out.println("Table rows:");
for (List<FieldValue> row : queryResponse.getResult().iterateAll()) {
System.out.println(row);
}
}
use of com.google.cloud.bigquery.QueryResponse in project google-cloud-java by GoogleCloudPlatform.
the class ITBigQueryTest method testNamedQueryParameters.
@Test
public void testNamedQueryParameters() throws InterruptedException {
String query = new StringBuilder().append("SELECT TimestampField, StringField, BooleanField FROM ").append(TABLE_ID.getTable()).append(" WHERE StringField = @stringParam").append(" AND IntegerField IN UNNEST(@integerList)").toString();
QueryParameterValue stringParameter = QueryParameterValue.string("stringValue");
QueryParameterValue intArrayParameter = QueryParameterValue.array(new Integer[] { 3, 4 }, Integer.class);
QueryRequest request = QueryRequest.newBuilder(query).setDefaultDataset(DatasetId.of(DATASET)).setMaxWaitTime(60000L).setPageSize(1000L).setUseLegacySql(false).addNamedParameter("stringParam", stringParameter).addNamedParameter("integerList", intArrayParameter).build();
QueryResponse response = queryAndWaitForResponse(request);
assertEquals(QUERY_RESULT_SCHEMA, response.getResult().getSchema());
assertEquals(2, Iterables.size(response.getResult().getValues()));
}
use of com.google.cloud.bigquery.QueryResponse in project google-cloud-java by GoogleCloudPlatform.
the class ITBigQueryTest method testQueryJob.
@Test
public void testQueryJob() throws InterruptedException, TimeoutException {
String tableName = "test_query_job_table";
String query = new StringBuilder().append("SELECT TimestampField, StringField, BooleanField FROM ").append(TABLE_ID.getTable()).toString();
TableId destinationTable = TableId.of(DATASET, tableName);
QueryJobConfiguration configuration = QueryJobConfiguration.newBuilder(query).setDefaultDataset(DatasetId.of(DATASET)).setDestinationTable(destinationTable).build();
Job remoteJob = bigquery.create(JobInfo.of(configuration));
remoteJob = remoteJob.waitFor();
assertNull(remoteJob.getStatus().getError());
QueryResponse response = bigquery.getQueryResults(remoteJob.getJobId());
while (!response.jobCompleted()) {
Thread.sleep(1000);
response = bigquery.getQueryResults(response.getJobId());
}
assertFalse(response.hasErrors());
assertEquals(QUERY_RESULT_SCHEMA, response.getResult().getSchema());
int rowCount = 0;
for (List<FieldValue> row : response.getResult().getValues()) {
FieldValue timestampCell = row.get(0);
FieldValue stringCell = row.get(1);
FieldValue booleanCell = row.get(2);
assertEquals(FieldValue.Attribute.PRIMITIVE, timestampCell.getAttribute());
assertEquals(FieldValue.Attribute.PRIMITIVE, stringCell.getAttribute());
assertEquals(FieldValue.Attribute.PRIMITIVE, booleanCell.getAttribute());
assertEquals(1408452095220000L, timestampCell.getTimestampValue());
assertEquals("stringValue", stringCell.getStringValue());
assertEquals(false, booleanCell.getBooleanValue());
rowCount++;
}
assertEquals(2, rowCount);
assertTrue(bigquery.delete(DATASET, tableName));
Job queryJob = bigquery.getJob(remoteJob.getJobId());
JobStatistics.QueryStatistics statistics = queryJob.getStatistics();
assertNotNull(statistics.getQueryPlan());
}
use of com.google.cloud.bigquery.QueryResponse in project google-cloud-java by GoogleCloudPlatform.
the class ITBigQueryTest method testCreateExternalTable.
@Test
public void testCreateExternalTable() throws InterruptedException {
String tableName = "test_create_external_table";
TableId tableId = TableId.of(DATASET, tableName);
ExternalTableDefinition externalTableDefinition = ExternalTableDefinition.of("gs://" + BUCKET + "/" + JSON_LOAD_FILE, TABLE_SCHEMA, FormatOptions.json());
TableInfo tableInfo = TableInfo.of(tableId, externalTableDefinition);
Table createdTable = bigquery.create(tableInfo);
assertNotNull(createdTable);
assertEquals(DATASET, createdTable.getTableId().getDataset());
assertEquals(tableName, createdTable.getTableId().getTable());
Table remoteTable = bigquery.getTable(DATASET, tableName);
assertNotNull(remoteTable);
assertTrue(remoteTable.getDefinition() instanceof ExternalTableDefinition);
assertEquals(createdTable.getTableId(), remoteTable.getTableId());
assertEquals(TABLE_SCHEMA, remoteTable.getDefinition().getSchema());
QueryRequest request = QueryRequest.newBuilder("SELECT TimestampField, StringField, IntegerArrayField, BooleanField FROM " + DATASET + "." + tableName).setDefaultDataset(DatasetId.of(DATASET)).setMaxWaitTime(60000L).setPageSize(1000L).build();
QueryResponse response = bigquery.query(request);
while (!response.jobCompleted()) {
response = bigquery.getQueryResults(response.getJobId());
Thread.sleep(1000);
}
long integerValue = 0;
int rowCount = 0;
for (List<FieldValue> row : response.getResult().getValues()) {
FieldValue timestampCell = row.get(0);
FieldValue stringCell = row.get(1);
FieldValue integerCell = row.get(2);
FieldValue booleanCell = row.get(3);
assertEquals(FieldValue.Attribute.PRIMITIVE, timestampCell.getAttribute());
assertEquals(FieldValue.Attribute.PRIMITIVE, stringCell.getAttribute());
assertEquals(FieldValue.Attribute.PRIMITIVE, integerCell.getAttribute());
assertEquals(FieldValue.Attribute.PRIMITIVE, booleanCell.getAttribute());
assertEquals(1408452095220000L, timestampCell.getTimestampValue());
assertEquals("stringValue", stringCell.getStringValue());
assertEquals(integerValue, integerCell.getLongValue());
assertEquals(false, booleanCell.getBooleanValue());
integerValue = ~integerValue & 0x1;
rowCount++;
}
assertEquals(4, rowCount);
assertTrue(remoteTable.delete());
}
Aggregations