Search in sources :

Example 1 with TableResult

use of com.google.cloud.bigquery.TableResult in project java-docs-samples by GoogleCloudPlatform.

the class QueryParametersSample method runNamed.

/**
 * Query the Shakespeare dataset for words with count at least {@code minWordCount} in the corpus
 * {@code corpus}.
 */
// [START bigquery_query_params]
private static void runNamed(final String corpus, final long minWordCount) throws InterruptedException {
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    String queryString = "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";
    QueryJobConfiguration queryRequest = QueryJobConfiguration.newBuilder(queryString).addNamedParameter("corpus", QueryParameterValue.string(corpus)).addNamedParameter("min_word_count", QueryParameterValue.int64(minWordCount)).setUseLegacySql(false).build();
    // Execute the query.
    TableResult result = bigquery.query(queryRequest);
    // Print all pages of the results.
    while (result != null) {
        for (List<FieldValue> row : result.iterateAll()) {
            System.out.printf("%s: %d\n", row.get(0).getStringValue(), row.get(1).getLongValue());
        }
        result = result.getNextPage();
    }
}
Also used : BigQuery(com.google.cloud.bigquery.BigQuery) TableResult(com.google.cloud.bigquery.TableResult) FieldValue(com.google.cloud.bigquery.FieldValue) QueryJobConfiguration(com.google.cloud.bigquery.QueryJobConfiguration)

Example 2 with TableResult

use of com.google.cloud.bigquery.TableResult in project java-docs-samples by GoogleCloudPlatform.

the class QueryParametersSample method runTimestamp.

// [END bigquery_query_params_arrays]
// [START bigquery_query_params_timestamps]
private static void runTimestamp() throws InterruptedException {
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    DateTime timestamp = new DateTime(2016, 12, 7, 8, 0, 0, DateTimeZone.UTC);
    String queryString = "SELECT TIMESTAMP_ADD(@ts_value, INTERVAL 1 HOUR);";
    QueryJobConfiguration queryRequest = QueryJobConfiguration.newBuilder(queryString).addNamedParameter("ts_value", QueryParameterValue.timestamp(// Timestamp takes microseconds since 1970-01-01T00:00:00 UTC
    timestamp.getMillis() * 1000)).setUseLegacySql(false).build();
    // Execute the query.
    TableResult result = bigquery.query(queryRequest);
    // Print all pages of the results.
    DateTimeFormatter formatter = ISODateTimeFormat.dateTimeNoMillis().withZoneUTC();
    while (result != null) {
        for (List<FieldValue> row : result.iterateAll()) {
            System.out.printf("%s\n", formatter.print(new DateTime(// but org.joda.time.DateTime constructor accepts times in milliseconds.
            row.get(0).getTimestampValue() / 1000, DateTimeZone.UTC)));
        }
        result = result.getNextPage();
    }
}
Also used : BigQuery(com.google.cloud.bigquery.BigQuery) TableResult(com.google.cloud.bigquery.TableResult) FieldValue(com.google.cloud.bigquery.FieldValue) QueryJobConfiguration(com.google.cloud.bigquery.QueryJobConfiguration) DateTimeFormatter(org.joda.time.format.DateTimeFormatter) DateTime(org.joda.time.DateTime)

Example 3 with TableResult

use of com.google.cloud.bigquery.TableResult in project java-docs-samples by GoogleCloudPlatform.

the class SimpleApp method main.

public static void main(String... args) throws Exception {
    // [START bigquery_simple_app_client]
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    // [END bigquery_simple_app_client]
    // [START bigquery_simple_app_query]
    QueryJobConfiguration queryConfig = QueryJobConfiguration.newBuilder("SELECT " + "CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, " + "view_count " + "FROM `bigquery-public-data.stackoverflow.posts_questions` " + "WHERE tags like '%google-bigquery%' " + "ORDER BY favorite_count DESC LIMIT 10").setUseLegacySql(false).build();
    // Create a job ID so that we can safely retry.
    JobId jobId = JobId.of(UUID.randomUUID().toString());
    Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());
    // Wait for the query to complete.
    queryJob = queryJob.waitFor();
    // Check for errors
    if (queryJob == null) {
        throw new RuntimeException("Job no longer exists");
    } else if (queryJob.getStatus().getError() != null) {
        // errors, not just the latest one.
        throw new RuntimeException(queryJob.getStatus().getError().toString());
    }
    // [END bigquery_simple_app_query]
    // [START bigquery_simple_app_print]
    // Get the results.
    QueryResponse response = bigquery.getQueryResults(jobId);
    TableResult result = queryJob.getQueryResults();
    // Print all pages of the results.
    for (FieldValueList row : result.iterateAll()) {
        String url = row.get("url").getStringValue();
        long viewCount = row.get("view_count").getLongValue();
        System.out.printf("url: %s views: %d%n", url, viewCount);
    }
// [END bigquery_simple_app_print]
}
Also used : BigQuery(com.google.cloud.bigquery.BigQuery) TableResult(com.google.cloud.bigquery.TableResult) QueryResponse(com.google.cloud.bigquery.QueryResponse) FieldValueList(com.google.cloud.bigquery.FieldValueList) Job(com.google.cloud.bigquery.Job) QueryJobConfiguration(com.google.cloud.bigquery.QueryJobConfiguration) JobId(com.google.cloud.bigquery.JobId)

Example 4 with TableResult

use of com.google.cloud.bigquery.TableResult in project java-docs-samples by GoogleCloudPlatform.

the class QuerySample method runQuery.

// [END query_config_batch]
// [START run_query]
public static void runQuery(QueryJobConfiguration queryConfig) throws TimeoutException, InterruptedException {
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    // Create a job ID so that we can safely retry.
    JobId jobId = JobId.of(UUID.randomUUID().toString());
    Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());
    // Wait for the query to complete.
    queryJob = queryJob.waitFor();
    // Check for errors
    if (queryJob == null) {
        throw new RuntimeException("Job no longer exists");
    } else if (queryJob.getStatus().getError() != null) {
        // errors, not just the latest one.
        throw new RuntimeException(queryJob.getStatus().getError().toString());
    }
    // Get the results.
    TableResult result = queryJob.getQueryResults();
    // Print all pages of the results.
    while (result != null) {
        for (List<FieldValue> row : result.iterateAll()) {
            for (FieldValue val : row) {
                System.out.printf("%s,", val.toString());
            }
            System.out.printf("\n");
        }
        result = result.getNextPage();
    }
}
Also used : BigQuery(com.google.cloud.bigquery.BigQuery) TableResult(com.google.cloud.bigquery.TableResult) FieldValue(com.google.cloud.bigquery.FieldValue) Job(com.google.cloud.bigquery.Job) JobId(com.google.cloud.bigquery.JobId)

Example 5 with TableResult

use of com.google.cloud.bigquery.TableResult in project java-docs-samples by GoogleCloudPlatform.

the class QueryParametersSample method runArray.

// [END bigquery_query_params]
/**
 * Query the baby names database to find the most popular names for a gender in a list of states.
 */
// [START bigquery_query_params_arrays]
private static void runArray(String gender, String[] states) throws InterruptedException {
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    String queryString = "SELECT name, sum(number) as count\n" + "FROM `bigquery-public-data.usa_names.usa_1910_2013`\n" + "WHERE gender = @gender\n" + "AND state IN UNNEST(@states)\n" + "GROUP BY name\n" + "ORDER BY count DESC\n" + "LIMIT 10;";
    QueryJobConfiguration queryRequest = QueryJobConfiguration.newBuilder(queryString).addNamedParameter("gender", QueryParameterValue.string(gender)).addNamedParameter("states", QueryParameterValue.array(states, String.class)).setUseLegacySql(false).build();
    // Execute the query.
    TableResult result = bigquery.query(queryRequest);
    // Print all pages of the results.
    while (result != null) {
        for (List<FieldValue> row : result.iterateAll()) {
            System.out.printf("%s: %d\n", row.get(0).getStringValue(), row.get(1).getLongValue());
        }
        result = result.getNextPage();
    }
}
Also used : BigQuery(com.google.cloud.bigquery.BigQuery) TableResult(com.google.cloud.bigquery.TableResult) FieldValue(com.google.cloud.bigquery.FieldValue) QueryJobConfiguration(com.google.cloud.bigquery.QueryJobConfiguration)

Aggregations

TableResult (com.google.cloud.bigquery.TableResult)6 BigQuery (com.google.cloud.bigquery.BigQuery)5 QueryJobConfiguration (com.google.cloud.bigquery.QueryJobConfiguration)5 FieldValue (com.google.cloud.bigquery.FieldValue)4 Job (com.google.cloud.bigquery.Job)3 JobId (com.google.cloud.bigquery.JobId)3 FieldValueList (com.google.cloud.bigquery.FieldValueList)1 QueryResponse (com.google.cloud.bigquery.QueryResponse)1 CreateTimeSeriesRequest (com.google.monitoring.v3.CreateTimeSeriesRequest)1 TimeSeries (com.google.monitoring.v3.TimeSeries)1 ArrayList (java.util.ArrayList)1 DateTime (org.joda.time.DateTime)1 DateTimeFormatter (org.joda.time.format.DateTimeFormatter)1