Examples with QueryRequest com.google.cloud.bigquery.QueryRequest used on opensource projects

Example 6 with QueryRequest

use of com.google.cloud.bigquery.QueryRequest 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;
}

Example 7 with QueryRequest

use of com.google.cloud.bigquery.QueryRequest 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);
    }
}

Example 8 with QueryRequest

use of com.google.cloud.bigquery.QueryRequest 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()));
}

Example 9 with QueryRequest

use of com.google.cloud.bigquery.QueryRequest 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());
}

Example 10 with QueryRequest

use of com.google.cloud.bigquery.QueryRequest in project google-cloud-java by GoogleCloudPlatform.

the class ITBigQueryTest method testQuery.

@Test
public void testQuery() throws InterruptedException {
    String query = new StringBuilder().append("SELECT TimestampField, StringField, BooleanField FROM ").append(TABLE_ID.getTable()).toString();
    QueryRequest request = QueryRequest.newBuilder(query).setDefaultDataset(DatasetId.of(DATASET)).setMaxWaitTime(60000L).setPageSize(1000L).build();
    QueryResponse response = queryAndWaitForResponse(request);
    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);
    Job queryJob = bigquery.getJob(response.getJobId());
    JobStatistics.QueryStatistics statistics = queryJob.getStatistics();
    assertNotNull(statistics.getQueryPlan());
}