Query a public dataset with the BigQuery Java client library

Open the Cloud Shell Editor

1. In Cloud Shell, create a new Java project using Apache Maven:

    mvn  
   archetype:generate  
    \ 
     
   -DgroupId = 
   com.google.app  
    \ 
     
   -DartifactId = 
   bigquery-java-quickstart  
    \ 
     
   -DinteractiveMode = 
    false 
    
   

   This command creates a Maven project that's named bigquery-java-quickstart .

   The output is similar to the following. Several lines are omitted to simplify the output.

    [INFO] Scanning for projects...
   ...
   [INFO] Building Maven Stub Project (No POM) 1
   ...
   [INFO] BUILD SUCCESS
   ... 
   

   There are many dependency management systems that you can use other than Maven. For more information, learn how to set up a Java development environment to use with client libraries.

2. Rename the App.java file that Maven creates by default:

    mv  
    \ 
     
   bigquery-java-quickstart/src/main/java/com/google/app/App.java  
    \ 
     
   bigquery-java-quickstart/src/main/java/com/google/app/SimpleApp.java 
   

3. Open the Cloud Shell Editor:

    cloudshell  
   workspace  
   bigquery-java-quickstart 
   

4. If you are prompted whether to synchronize the Java classpath or configuration, click Always.

   If you are not prompted and encounter an error that is related to the classpath during this walkthrough, do the following:

   1. Click File > Preferences > Open Settings (UI).
   2. Click Extensions > Java.
   3. Scroll to Configuration: Update Build Configurationand select automatic.

To learn how to prepare dependencies for queries, click Next.

Prepare dependencies for queries

1. In the Explorerpane, locate your BIGQUERY-JAVA-QUICKSTART project.

2. Click the pom.xml file to open it.

3. Inside the <dependencies> tag, add the following dependency after any existing ones. Do not replace any existing dependencies.

    <dependency>  
   <groupId>com.google.cloud</groupId>  
   <artifactId>google-cloud-bigquery</artifactId>
   </dependency> 
   

4. On the line following the closing tag ( </dependencies> ), add the following:

    <dependencyManagement>  
   <dependencies>  
   <dependency>  
   <groupId>com.google.cloud</groupId>  
   <artifactId>libraries-bom</artifactId>  
   <version>26.1.5</version>  
   <type>pom</type>  
   <scope>import</scope>  
   </dependency>  
   </dependencies>
   </dependencyManagement> 
   

To learn how to query a public dataset in BigQuery, click Next.

Query a public dataset in BigQuery

1. In the Explorerpane, in your BIGQUERY-JAVA-QUICKSTART project, click src > main/java/com/google/app > SimpleApp.java. The file opens.

2. To create a query against the bigquery-public-data.stackoverflow dataset, leave the first line of the file ( package com.google.app; ), and replace the remaining contents of the file with the following code:

     import 
     
    com.google.cloud.bigquery. BigQuery 
    
    ; 
    import 
     
    com.google.cloud.bigquery. BigQueryException 
    
    ; 
    import 
     
    com.google.cloud.bigquery. BigQueryOptions 
    
    ; 
    import 
     
    com.google.cloud.bigquery. FieldValueList 
    
    ; 
    import 
     
    com.google.cloud.bigquery. Job 
    
    ; 
    import 
     
    com.google.cloud.bigquery. JobId 
    
    ; 
    import 
     
    com.google.cloud.bigquery. JobInfo 
    
    ; 
    import 
     
    com.google.cloud.bigquery. QueryJobConfiguration 
    
    ; 
    import 
     
    com.google.cloud.bigquery. TableResult 
    
    ; 
    public 
     
    class 
    SimpleApp 
     
    { 
     
    public 
     
    static 
     
    void 
     
    main 
    ( 
    String 
    ... 
     
    args 
    ) 
     
    throws 
     
    Exception 
     
    { 
     
    // TODO(developer): Replace these variables before running the app. 
     
    String 
     
    projectId 
     
    = 
     
    "MY_PROJECT_ID" 
    ; 
     
    simpleApp 
    ( 
    projectId 
    ); 
     
    } 
     
    public 
     
    static 
     
    void 
     
    simpleApp 
    ( 
    String 
     
    projectId 
    ) 
     
    { 
     
    try 
     
    { 
     
     BigQuery 
    
     
    bigquery 
     
    = 
     
     BigQueryOptions 
    
    . 
    getDefaultInstance 
    (). 
    getService 
    (); 
     
     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 view_count DESC " 
     
    + 
     
    "LIMIT 10" 
    ) 
     
    // Use standard SQL syntax for queries. 
     
    // See: https://cloud.google.com/bigquery/sql-reference/ 
     
    . 
    setUseLegacySql 
    ( 
    false 
    ) 
     
    . 
    build 
    (); 
     
     JobId 
    
     
    jobId 
     
    = 
     
     JobId 
    
    . 
    newBuilder 
    (). 
    setProject 
    ( 
    projectId 
    ). 
    build 
    (); 
     
     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 
    (). 
     getExecutionErrors 
    
    () 
     
    != 
     
    null 
    && 
    queryJob 
    . 
    getStatus 
    (). 
    getExecutionErrors 
    (). 
    size 
    () 
    > 
    0 
    ) 
     
    { 
     
    // TODO(developer): Handle errors here. An error here do not necessarily mean that the job 
     
    // has completed or was unsuccessful. 
     
    // For more details: https://cloud.google.com/bigquery/troubleshooting-errors 
     
    throw 
     
    new 
     
    RuntimeException 
    ( 
    "An unhandled error has occurred" 
    ); 
     
    } 
     
    // Get the results. 
     
     TableResult 
    
     
    result 
     
    = 
     
    queryJob 
    . 
     getQueryResults 
    
    (); 
     
    // Print all pages of the results. 
     
    for 
     
    ( 
     FieldValueList 
    
     
    row 
     
    : 
     
    result 
    . 
     iterateAll 
    
    ()) 
     
    { 
     
    // String type 
     
    String 
     
    url 
     
    = 
     
    row 
    . 
    get 
    ( 
    "url" 
    ). 
    getStringValue 
    (); 
     
    String 
     
    viewCount 
     
    = 
     
    row 
    . 
    get 
    ( 
    "view_count" 
    ). 
    getStringValue 
    (); 
     
    System 
    . 
    out 
    . 
    printf 
    ( 
    "%s : %s views\n" 
    , 
     
    url 
    , 
     
    viewCount 
    ); 
     
    } 
     
    } 
     
    catch 
     
    ( 
     BigQueryException 
    
     
    | 
     
    InterruptedException 
     
    e 
    ) 
     
    { 
     
    System 
    . 
    out 
    . 
    println 
    ( 
    "Simple App failed due to error: \n" 
     
    + 
     
    e 
    . 
    toString 
    ()); 
     
    } 
     
    } 
    }