• Skip to main content
  • Skip to primary sidebar
  • Home
  • About
  • Subscribe BMA
  • Contact Us!

Be My Aficionado

Inspire Affection

You are here: Home / Tutorials / Big Query: How to Fetch Data In JSON Format By Consuming BigQuery’s REST API(s)

Big Query: How to Fetch Data In JSON Format By Consuming BigQuery’s REST API(s)13 min read

August 19, 2018 by Varun Shrivastava 10 Comments

BigQuery is revolutionizing the way Big Data is maintained and processed. Its “server-less” architecture is not just enterprise friendly but also developer friendly as it takes care of all the hardware configuration and scalability matrices on its own.

As a developer you just need to know the process of data extraction and that is all. All the performance related hard-work have been taken care by Google’s BigQuery.

Before you begin, I would suggest you to read following article:

  • Big Query: Everything You Need to Jump Start Your Development

This article will give you the entire picture of Big Query and why you should be using it in the first place to deal with Big Data.

Topics Covered

  • Create a New Project
  • Understanding Service Account
    • How does Service Account maintain authentication and authorization?
    • How to Create Service Account
  • Environment Setup
  • JAVA Project Setup for BigQuery Data Access Using REST API(s)
    • Project Structure
    • Classes Used
  • Dataset.java
  • BigQueryResponseVO.java
  • LabelsSample.java
  • BigQueryDemo.java
      • Form Request Body
      • Send Request and Get Query Result
  • Conclusion

Create a New Project

To do anything, you first need to create a Project under Google Cloud Platform.

Navigate to the Google Credentials Page. And select your project from the top navbar. Look at the image below:

If your project is not visible, then you will have to create it.

Click on select project drop-down and  New Project. Take a look at the below snapshot.

Upon clicking the New Project button, a new page will open where you will be asked to enter project details and create new project.

Create New GCP Project

Once your project is created, navigate back to the Credentials Page and look for Manage service Account Link at the bottom. Your application should use Service Account to interact with different Google Services.

Before moving forward, let me give you a quick overview of Service Account and its significance to better understand the tutorial.

Understanding Service Account

The first thing I tell everyone: Do not confuse Service Account with User Account.

Service accounts are not user specific, in-fact, these are special type of Google Accounts that belongs to your application instead of a User. Service account is a way to access Google Services with proper authentication and authorization.

Google assume identity of a Service account while calling Google APIs. Service accounts are made with the sole purpose to eliminate User from directly accessing the resources.

How does Service Account maintain authentication and authorization?

Service account have special keys that are used to authenticate themselves to google while accessing google services.

When you create Service Account, you decide:

  • Which resources must be accessible by the service account?
  • What permissions does the Service Account need in order to access a specific resource?
  • Where will the code that assumes the identity of the service account be running: on Google Cloud Platform or on-premises?

Use below flowchart to figure out how to configure service account:

How to Create Service Account

Login to your Google Cloud Platform dashboard. Navigate to IAM & admin link on the left side bar and click on Service Accounts link.

Access Service Account Link in Google Cloud Platform

Alternatively, you can Click on Manage Service Account link and that will lead you to Service Account Creation page.

Create new service account

After selecting project you will see all the listed accounts in the list format. And on the top navbar there is an option to create new service account. Click on Create New Service Account and provide the necessary information.

Make sure to choose the BigQuery role under Project role option. Checkout the image below:

Service Account Creation Page Google cloud Platform
Service Account Creation Page Google cloud Platform

Do not forget to check the furnish new private key checkbox. This will provide you file for download which we will be using later to access data from BigQuery tables.

Click save and your Service account will be created.

Note: Download the private key and save it on your local machine.

Great, you are done with all the boring service account creation part. Now, let’s jump into the development part. Here, you will be fetching the data from the BigQuery tables in JSON format.

Environment Setup

Set the path to the Private Key file in your System Environment. Follow following steps:

  • Right Click on PC in windows explore and click on properties.
PC Properties in windows explorer
  • Click on Advanced System Settings
Advanced System Settings Windows
Advanced System Settings
  • Click on Environment Variables button and create new environment variable with the key GOOGLE_APPLICATION_CREDENTIALS.
Environment Variables
GOOGLE_APPLICATION_CREDENTIALS
  • You are done setting up the environment and now can make calls to Google BigQuery.

JAVA Project Setup for BigQuery Data Access Using REST API(s)

In this project, you will query the Hacker News comments from the BigQuery database. This dataset is uploaded to BigQuery publicly and is accessible by everyone. To give you the overview, below is the query that you will be firing on the Bigquery database:

BigQuery Hacker News Comments Query Result
Query result for Hacker News comments on 3.41 GB dataset

You will first need to setup JAVA project and include all the required dependencies to make HTTP calls and retrieve data from the BigQuery database. For this project you will be using the pom.xml file provided by Google in their sample projects. You can copy and paste the file contents from the following link: https://github.com/vslala/BigQueryRestSample/blob/master/pom.xml. The main dependencies included in the pom.xml file are:

  • Google Cloud BigQuery Library (1.35.0)
  • Google API Client (1.23.0)
  • Google HTTP Client (1.23.0)
  • Google OAuth Client (1.23.0)

You can explore pom.xml file later. Once you have created a project and imported all the dependencies into your classpath, then its time for some action 😉

Project Structure

For this project, I would suggest you to use simple structure and try out the application.

  1. I assume you have already created a project with all the required dependencies.
  2. Create Package Structure: com.example.bigquery
  3. For simplicity, keep all the code inside this package.

Classes Used

  • BigQueryDemo.java
  • BigQueryResponseVO.java
  • Dataset.java
  • LabelsSample.java

Let’s take each class one-by-one and see what it does.

Dataset.java

Go through the below code:

package com.example.bigquery;

import java.util.HashMap;
import java.util.Map;

public class Dataset {
	
	private Map<String, Object> labels;
	
	public Dataset() {
		labels = new HashMap<>();
	}
	
	public Dataset addLabel(String labelKey, String labelValue) {
		labels.put(labelKey, labelValue);
		return this;
	}
	
	public Dataset removeLabel(String labelKey) {
		labels.remove(labelKey);
		return this;
	}
	
	public Map<String, Object> getLabels() {
		return labels;
	}
}

BigQueryResponseVO.java

This class is Response Value Object for the BigQuery Response. Take a look at its properties:

package com.example.bigquery;

import java.util.List;

import org.codehaus.jackson.annotate.JsonProperty;

import com.google.api.services.bigquery.model.JobReference;
import com.google.cloud.bigquery.BigQueryError;
import com.google.cloud.bigquery.Schema;

public class BigQueryResponseVO {
	@JsonProperty
	String kind;
	@JsonProperty
	String etag;
	@JsonProperty
	Schema schema;
	@JsonProperty
	JobReference jobReference;
	@JsonProperty
	long totalRows;
	@JsonProperty
	String pageToken;
	@JsonProperty
	boolean cacheHit;
	@JsonProperty
	long totalBytesProcessed;
	@JsonProperty
	List<BigQueryError> errors;
	@JsonProperty
	long numDmlAffectedRows;
	
	
}

LabelsSample.java

This class is the bootstrap class for the project. It contains the main() method that gives call to the BigQueryDemo.implicit() method. 

Take a look at the class:

/*
 * Copyright 2016 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *     http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package com.example.bigquery;

import java.io.IOException;

/** Sample demonstrating labeling a BigQuery dataset or table. */
public class LabelsSample {

	public static void main(String[] args) throws IOException, InterruptedException {
		BigQueryDemo.implicit();
	}
}

BigQueryDemo.java

Go through the below code and then read further:

package com.example.bigquery;

import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.util.Arrays;

import org.apache.log4j.Logger;

import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.client.http.GenericUrl;
import com.google.api.client.http.HttpContent;
import com.google.api.client.http.HttpHeaders;
import com.google.api.client.http.HttpRequest;
import com.google.api.client.http.HttpRequestFactory;
import com.google.api.client.http.HttpResponse;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.http.json.JsonHttpContent;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.common.io.CharStreams;

public class BigQueryDemo {
	
	private static final String QUERY_URL_FORMAT = "https://www.googleapis.com/bigquery/v2/projects/%s/queries" + "?access_token=%s";

	private static final String QUERY = "query";

	private static final String QUERY_HACKER_NEWS_COMMENTS = "SELECT * FROM [bigquery-public-data:hacker_news.comments] LIMIT 1000";

	private static final Logger logger = Logger.getLogger(BigQueryDemo.class);
	
	static GoogleCredential credential = null;
	static final HttpTransport HTTP_TRANSPORT = new NetHttpTransport();
	static final JsonFactory JSON_FACTORY = new JacksonFactory();
	static {
		// Authenticate requests using Google Application Default credentials.
		try {
			credential = GoogleCredential.getApplicationDefault();
			credential = credential.createScoped(Arrays.asList("https://www.googleapis.com/auth/bigquery"));
			credential.refreshToken();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

	public static void implicit() {
		String projectId = credential.getServiceAccountProjectId();
		String accessToken = generateAccessToken();
		// Set the content of the request.
		Dataset dataset = new Dataset().addLabel(QUERY,	QUERY_HACKER_NEWS_COMMENTS);
		HttpContent content = new JsonHttpContent(JSON_FACTORY, dataset.getLabels());
		// Send the request to the BigQuery API.
		GenericUrl url = new GenericUrl(String.format(QUERY_URL_FORMAT, projectId, accessToken));
		logger.debug("URL: " + url.toString());
		String responseJson = getQueryResult(content, url);
		logger.debug(responseJson);
	}

	private static String getQueryResult(HttpContent content, GenericUrl url) {
		String responseContent = null;
		HttpRequestFactory requestFactory = HTTP_TRANSPORT.createRequestFactory();
		HttpRequest request = null;
		try {
			request = requestFactory.buildPostRequest(url, content);
			request.setParser(JSON_FACTORY.createJsonObjectParser());
			request.setHeaders(
					new HttpHeaders().set("X-HTTP-Method-Override", "POST").setContentType("application/json"));
			HttpResponse response = request.execute();
			InputStream is = response.getContent();
			responseContent = CharStreams.toString(new InputStreamReader(is));
		} catch (IOException e) {
			logger.error(e);
		}
		return responseContent;
	}

	private static String generateAccessToken() {
		String accessToken = null;
		if ((System.currentTimeMillis() > credential.getExpirationTimeMilliseconds())) {
			accessToken = credential.getRefreshToken();
		} else {
			accessToken = credential.getAccessToken();
		}
		System.out.println(accessToken);
		return accessToken;
	}
}

This is the main class that would do all the work.

The very first thing that executes in this class is the static block.

static {
		// Authenticate requests using Google Application Default credentials.
		try {
			credential = GoogleCredential.getApplicationDefault();
			credential = credential.createScoped(Arrays.asList("https://www.googleapis.com/auth/bigquery"));
			credential.refreshToken();
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

The static block gets the Secret Json File of Service Account from the system environment properties. Then it sends a request to Google OAuth service to get the refresh token.

This refresh token is used to access the BigQuery with a scope as `https://www.googleapis.com/auth/bigquery`.

Form Request Body

Once the static block has executed successfully, the implicit method will be invoked.

The starting point for this class is its implicit() method. This implicit method is called from the main() method of LabelsSample.java.

Next, you will have to create the request body that will contain the query and BigQuery post URL.

public static void implicit() {
		String projectId = credential.getServiceAccountProjectId();
		String accessToken = generateAccessToken();
		// Set the content of the request.
		Dataset dataset = new Dataset().addLabel(QUERY,	QUERY_HACKER_NEWS_COMMENTS);
		HttpContent content = new JsonHttpContent(JSON_FACTORY, dataset.getLabels());
		// Send the request to the BigQuery API.
		GenericUrl url = new GenericUrl(String.format(QUERY_URL_FORMAT, projectId, accessToken));
		logger.debug("URL: " + url.toString());
		String responseJson = getQueryResult(content, url);
		logger.debug(responseJson);
	}

Send Request and Get Query Result

Once you have formed the request content, it is time to call the BigQuery’s REST API and fetch the results in JSON format.

Use request = requestFactory.buildPostRequest(url, content) (Request Factory) to create post request and pass in the URL and Content to it.

Set the appropriate headers (mandatory) and call the execute method.

request.setHeaders(
    new HttpHeaders().set("X-HTTP-Method-Override", "POST").setContentType("application/json"));

The response is provided in the InputStream. Convert the InputStream into Characters to retrieve the response in JSON.

That is all you need to do in order to fetch the data from BigQuery in JSON format.

Conclusion

Great! I hope you were able to get the response by following this tutorial.

This article was only intended to fetch the JSON response from BigQuery. Do not use the example as it is. Try to play around and modify the code to suit your need.

I will be modifying the code to make it more friendly and act as a framework for your project. So, do check the master branch of the repository as well.

If you have any question or if there is anything that you do not understand then please do comment below. I would be more than happy to interact with you and solve your queries.

Share this:

  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Twitter (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • More
  • Click to print (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pocket (Opens in new window)
  • Click to share on Telegram (Opens in new window)
  • Click to share on WhatsApp (Opens in new window)
  • Click to share on Skype (Opens in new window)
  • Click to email this to a friend (Opens in new window)

Filed Under: Tutorials Tagged With: Big Query, Fetch Data, java, Rest API

Reader Interactions

Comments

  1. Kundan Kumar says

    December 4, 2019 at 6:13 pm

    Hi Varun,

    While converting json to java pojo class i am getting below error.

    Exception in thread “main” org.codehaus.jackson.map.JsonMappingException: No suitable constructor found for type [simple type, class com.google.cloud.bigquery.Schema]: can not instantiate from JSON object (need to add/enable type information?)
    at [Source: [email protected]; line: 4, column: 5] (through reference chain: BigQueryResponseVO[“schema”])

    Thanks,
    Kundan Kumar

    Reply
    • Varun Shrivastava says

      December 13, 2019 at 9:57 am

      You need a default constructor in your POJO class. Looks like Jackson cannot Initialise your class because the default constructor is not present.

      Reply
  2. Kundan Kumar says

    December 3, 2019 at 3:46 pm

    Hi Varun,

    Thanks for this tutorial. where are you converting json response to BigQueryResponseVO.? I am able to to print the json response but not able to convert json response into java object.

    Thanks,
    Kundan Kumar

    Reply
  3. Kundan Kumar says

    December 1, 2019 at 5:54 am

    Hi,
    I am getting this error.

    java.io.IOException: The Application Default Credentials are not available. They are available if running on Google App Engine, Google Compute Engine, or Google Cloud Shell. Otherwise, the environment variable GOOGLE_APPLICATION_CREDENTIALS must be defined pointing to a file defining the credentials. See https://developers.google.com/accounts/docs/application-default-credentials for more information.
    at com.google.api.client.googleapis.auth.oauth2.DefaultCredentialProvider.getDefaultCredential(DefaultCredentialProvider.java:98)
    at com.google.api.client.googleapis.auth.oauth2.GoogleCredential.getApplicationDefault(GoogleCredential.java:217)
    at com.google.api.client.googleapis.auth.oauth2.GoogleCredential.getApplicationDefault(GoogleCredential.java:195)
    at Test2Json.(Test2Json.java:39)
    at LabelsSample.main(LabelsSample.java:6)
    Exception in thread “main” java.lang.NullPointerException
    at Test2Json.implicit(Test2Json.java:48)
    at LabelsSample.main(LabelsSample.java:6)

    Thanks

    Reply
    • Varun Shrivastava says

      December 3, 2019 at 10:36 pm

      This error is regarding the credentials file that is needed to be present in order to authenticate and authorise your service for accessing the BigQuery.

      You should add `GOOGLE_APPLICATION_CREDENTIALS` in your System Environment and provide the path to your credentials file that you have downloaded from the GCP console.

      Check the snippet attached.
      https://uploads.disquscdn.com/images/dc672ab6e90a4769dbef45063008e60cd6a2873525866e376477bfdfe5cbaccb.png

      Reply
      • Kundan Kumar says

        December 4, 2019 at 12:59 pm

        Thanks Varun. This issue has been resolved now.

        Reply
      • Kundan Kumar says

        December 4, 2019 at 3:44 pm

        Why the query url is https://www.googleapis.com/bigquery/ not
        https://bigquery.googleapis.com/bigquery/v2/projects/{projectId}/jobs

        On google REST reference they have mention this url for querying using REST API see the below url.

        https://cloud.google.com/bigquery/docs/reference/rest/v2/jobs/insert

        Also can you add one example of REST API using jobs.

        Reply
        • Varun Shrivastava says

          December 13, 2019 at 9:57 am

          Sure… will try to add whenever I get time.
          Thanks for the feedback mate 🙂

          Reply
  4. Raghavendra Singh says

    December 15, 2018 at 11:30 pm

    How to upload JSON files into BigQuery from local drive using Java?

    Reply
    • Varun Shrivastava says

      December 16, 2018 at 9:56 am

      I’m not sure if you can do that from your local drive but you can definitely fo that from Google Cloud Storage.

      I think this will help you: https://cloud.google.com/bigquery/docs/loading-data-cloud-storage-json#limitations

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Primary Sidebar

Featured Posts

Time Complexity Comparison Sheet Of Elementary Sorting Algorithms

May 29, 2020 By Varun Shrivastava Leave a Comment

How to visit Shirdi Sai Baba Temple and have the best experience of your life

July 22, 2017 By Varun Shrivastava Leave a Comment

Partial Root Canal

May 19, 2016 By Varun Shrivastava Leave a Comment

Get a Personal Portfolio Website Now!

April 4, 2017 By Varun Shrivastava 2 Comments

How to Earn Money From Amazon Affiliate Network

May 6, 2018 By Varun Shrivastava Leave a Comment

Latest Posts

  • 3 Best Laptops (Mid-Range) For Every Use Case In India
  • Distributed System Architectural Patterns
  • The Power of being in the Present
  • Basic Calculator Leetcode Problem Using Object-Oriented Programming In Java
  • Study Abroad Destinations : Research and Review

Categories

  • Blogging (103)
  • Cooking (11)
  • Fashion (7)
  • Finance & Money (12)
  • Programming (51)
  • Reviews (4)
  • Technology (22)
  • Travelling (4)
  • Tutorials (12)
  • Web Hosting (8)
  • Wordpress N SEO (19)

Follow us on facebook

Follow us on facebook

Grab the Deal Now!

Hostgator Starting @$3.95/mo

DigitalOcean Free Credits

Trending

Affordable Hosting amazon aoc-2020 bad luck believe in yourself best database earn money blogging education experience fashion finance Financial Freedom food friends goals google india indian cuisine indian education system java javascript life life changing love make money microservices motivation oops poor education system principles of microservices problem-solving programmer programming reality search engines seo SSD Hosting success technology tips top 5 web web developer wordpress

Copyright © 2021 · BeMyAficionado by Varun Shrivastava · WordPress

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.