use of com.google.cloud.bigquery.QueryRequest in project google-cloud-java by GoogleCloudPlatform.
the class ITBigQueryTest method testCreateViewTable.
@Test
public void testCreateViewTable() throws InterruptedException {
String tableName = "test_create_view_table";
TableId tableId = TableId.of(DATASET, tableName);
ViewDefinition viewDefinition = ViewDefinition.of("SELECT TimestampField, StringField, BooleanField FROM " + DATASET + "." + TABLE_ID.getTable());
TableInfo tableInfo = TableInfo.of(tableId, viewDefinition);
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);
assertEquals(createdTable.getTableId(), remoteTable.getTableId());
assertTrue(remoteTable.getDefinition() instanceof ViewDefinition);
Schema expectedSchema = Schema.newBuilder().addField(Field.newBuilder("TimestampField", Field.Type.timestamp()).setMode(Field.Mode.NULLABLE).build()).addField(Field.newBuilder("StringField", Field.Type.string()).setMode(Field.Mode.NULLABLE).build()).addField(Field.newBuilder("BooleanField", Field.Type.bool()).setMode(Field.Mode.NULLABLE).build()).build();
assertEquals(expectedSchema, remoteTable.getDefinition().getSchema());
QueryRequest request = QueryRequest.newBuilder("SELECT * FROM " + 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);
}
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(remoteTable.delete());
}
use of com.google.cloud.bigquery.QueryRequest in project google-cloud-java by GoogleCloudPlatform.
the class ITBigQueryTest method testPositionalQueryParameters.
@Test
public void testPositionalQueryParameters() throws InterruptedException {
String query = new StringBuilder().append("SELECT TimestampField, StringField, BooleanField FROM ").append(TABLE_ID.getTable()).append(" WHERE StringField = ?").append(" AND TimestampField > ?").append(" AND IntegerField IN UNNEST(?)").append(" AND IntegerField < ?").append(" AND FloatField > ?").toString();
QueryParameterValue stringParameter = QueryParameterValue.string("stringValue");
QueryParameterValue timestampParameter = QueryParameterValue.timestamp("2014-01-01 07:00:00.000000+00:00");
QueryParameterValue intArrayParameter = QueryParameterValue.array(new Integer[] { 3, 4 }, Integer.class);
QueryParameterValue int64Parameter = QueryParameterValue.int64(5);
QueryParameterValue float64Parameter = QueryParameterValue.float64(0.5);
QueryRequest request = QueryRequest.newBuilder(query).setDefaultDataset(DatasetId.of(DATASET)).setMaxWaitTime(60000L).setPageSize(1000L).setUseLegacySql(false).addPositionalParameter(stringParameter).addPositionalParameter(timestampParameter).addPositionalParameter(intArrayParameter).addPositionalParameter(int64Parameter).addPositionalParameter(float64Parameter).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.QueryRequest in project google-cloud-java by GoogleCloudPlatform.
the class ITBigQueryTest method testBytesParameter.
@Test
public void testBytesParameter() throws Exception {
String query = new StringBuilder().append("SELECT BYTE_LENGTH(@p) AS length").toString();
QueryParameterValue bytesParameter = QueryParameterValue.bytes(new byte[] { 1, 3 });
QueryRequest request = QueryRequest.newBuilder(query).setDefaultDataset(DatasetId.of(DATASET)).setMaxWaitTime(60000L).setPageSize(1000L).setUseLegacySql(false).addNamedParameter("p", bytesParameter).build();
QueryResponse response = queryAndWaitForResponse(request);
int rowCount = 0;
for (List<FieldValue> row : response.getResult().getValues()) {
rowCount++;
assertEquals(2, row.get(0).getLongValue());
}
assertEquals(1, rowCount);
}
use of com.google.cloud.bigquery.QueryRequest in project google-cloud-java by GoogleCloudPlatform.
the class BigQuerySnippets method runQueryWithParameters.
/**
* Example of running a query with query parameters.
*/
// [TARGET query(QueryRequest)]
// [VARIABLE "SELECT distinct(corpus) FROM `bigquery-public-data.samples.shakespeare` where word_count > @wordCount"]
public QueryResponse runQueryWithParameters(String query) throws InterruptedException {
// [START runQueryWithParameters]
QueryRequest request = QueryRequest.newBuilder(query).setUseLegacySql(// standard SQL is required to use query parameters
false).addNamedParameter("wordCount", QueryParameterValue.int64(5)).build();
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 runQueryWithParameters]
return response;
}
use of com.google.cloud.bigquery.QueryRequest in project google-cloud-java by GoogleCloudPlatform.
the class BigQuerySnippets method queryResults.
/**
* Example of getting the results of query.
*/
// [TARGET getQueryResults(JobId, QueryResultsOption...)]
// [VARIABLE "SELECT unique(corpus) FROM [bigquery-public-data:samples.shakespeare]"]
public QueryResponse queryResults(final String query) throws InterruptedException {
// [START queryResults]
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 queryResults]
return response;
}
Aggregations