Introduction
- Java undoubtedly worlds most popular language and all the popular products provides SDK to access it via Java
- BigQuery is popular cloud data warehouse product in the market and in this blog we will access BigQuery and execute some queries in BigQuery Table with Java.
- Using Java SDK gives us opportunity to build apps on top of BigQuery , for example building some BI interface that connects APP with BigQuery.
Configuration
- As a first step lets setup dependencies in the pom.xml for BigQuery.
<dependencyManagement>
<dependencies>
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>libraries-bom</artifactId>
<version>26.0.0</version>
<type>pom</type>
<scope>import</scope>
</dependency>
</dependencies>
</dependencyManagement>
<dependencies>
<dependency>
<groupId>com.google.cloud</groupId>
<artifactId>google-cloud-bigquery</artifactId>
</dependency>
</dependencies>
BigQuery Permission
- First we need to get access to BigQuery project so that we can execute the query.
- Let consider that we have access to the BigQuery Instance and we already been provided service account to access the BigQuery.
- We have few options to provide this credential to our app so that we can access BigQuery.
- We can setup Credential object that reads the service_account.json file.
Credentials credentials = GoogleCredentials
.fromStream(new FileInputStream(DIR+"service_account.json"));
- Then we can configure BigQuery Object with this credential.
Initializing BigQuery Object
BigQuery bigquery = BigQueryOptions
.newBuilder()
.setCredentials(credentials)
.build()
.getService();
- Next, we can configure our Query using QueryJobConfiguration. Our Query is performing Select operation on BigQuery public dataset austin_bikeshare. We are querying bikeshare_stations table specifically.
- Once our queryConfig is ready we can use it as parameter for bigquery query method.
QueryJobConfiguration queryConfig = QueryJobConfiguration
.newBuilder(bqQuery)
.build();
TableResult results = bigquery.query(queryConfig);
- Now we have our result stored as TableResult. Once we have result , we can iterate over it and get whichever column we are interested in.
- Here i am printing station name from each bikeshare_stations.
Adding all the Code
- Let’s add all the logic mentioned above in BigQueryAPIAccess Class
- I have created method to get credentials and execute method to execute query agains BigQuery Service.
import com.google.auth.Credentials;
import com.google.auth.oauth2.GoogleCredentials;
import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.TableResult;
import java.io.FileInputStream;
import java.io.IOException;
public class BigQueryAPIAccess {
static final String DIR = "" ; // add directory of service account
public static void main(String[] args) throws InterruptedException, IOException {
String query = "SELECT * FROM `bigquery-public-data.austin_bikeshare.bikeshare_stations`";
BigQuery bqService = BigQueryOptions
.newBuilder()
.setCredentials(getCredentials())
.build()
.getService();
TableResult result = execute(query, bqService);
System.out.println("Station Names : ");
result.iterateAll()
.forEach(eacRecord -> System.out.println(eacRecord.get("name").getValue()));
}
/**
* Execute Query
* @param query
* @param bqService
* @return
* @throws IOException
* @throws InterruptedException
*/
private static TableResult execute(String query, BigQuery bqService) throws IOException, InterruptedException {
QueryJobConfiguration queryConfig = QueryJobConfiguration
.newBuilder(query)
.build();
TableResult results = bqService.query(queryConfig);
return results;
}
/**
* Get Credentials
* @return
* @throws IOException
*/
private static Credentials getCredentials() throws IOException {
return GoogleCredentials
.fromStream(new FileInputStream(DIR+"service_account.json"));
}
}
- We can now run main method , that will print station names.
Conclusion
- In this blog we have used BigQuery Java SDK to Query BigQuery from Java App.
- We can now use this code as template and pass any query as argument to the app from UI and execute Query then return result of the query to the UI as an output.
Bonus Tip
- If you want to upskill your Java, you should definitely check out this bestseller course