How to get query result in Json form using Java API of Google Bigquery

Question

I am using Google Bigquery V2 Java API. I am not able to find a way to get query results in JSON format. In Bigquery Web UI we can see this JSON and Table form of results. see scrrenshot.

Is there any way to get the GetQueryResultsResponse as JSON, using Java API.

Elliott Brossard · Accepted Answer · 2017-06-02 20:33:49Z

3

One option is to apply the TO_JSON_STRING function to the results of your query. For example,

#standardSQL
SELECT TO_JSON_STRING(t)
FROM (
  SELECT x, y
  FROM YourTable
  WHERE z = 10
) AS t;

If you want all of the table's columns as JSON, you can use a simpler form:

#standardSQL
SELECT TO_JSON_STRING(t)
FROM YourTable AS t
WHERE z = 10;

edited Jun 2, 2017 at 20:33

answered May 24, 2017 at 7:32

Elliott Brossard

34k2 gold badges75 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

VVP Over a year ago

This does not solve my problem. Can we get query result in JSON format using JAVA API ??

VVP Over a year ago

How does Google show JSON results on Web UI. Does it explicitly converts result response to JSON form ?

Elliott Brossard Over a year ago

Can you describe why this doesn't solve your problem? The client libraries take the JSON responses from the BigQuery service and convert them into native representations for each language (Java, Python, etc.). If you want the results as JSON strings, you can use this function.

VVP Over a year ago

I am working at API level. For me I can not change user's query or don't want to force the user to use TO_JSON_STRING function. Is there any way to convert native representation of response to JSON string. similar to Bigquery Web UI.

Kewei Shang Over a year ago

I have the same question. I also want to avoid using TO_JSON_STRING function, because not only I have to modify the original SQL but also the json output format of this function is different than the json output format of exporting the table. The command bq query --format json --project_id myProject "select * from sandbox.keweiquerytable" can query a table and result in json format. So I suppose there should be a way to do that in REST or JAVA API. Anyone has any idea?

|

vs_lala · Accepted Answer · 2018-07-13 09:57:47Z

I'm using a service account to access the BigQuery REST API to get the response in JSON format.

In order to use a service account, you will have to go to credentials (https://console.cloud.google.com/apis/credentials) and choose a project.

You will get a drop down like this:

Create a Service account for your project and download the secret file in the JSON format. Keep the JSON file in your file system and set the path to it. Check below image to set the file path:

So, now all you have to do in is use JAVA client api to consume the Big Query REST API.

Here's is a simple solution that I've been using for my project.

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;
    }
}

Following is the Github link to the code: https://github.com/vslala/BigQueryRestSample

It is just a demo project to fetch JSON data from the BQ REST API. Do not use it in your project directly. Let me know if you have any questions.

Collectives™ on Stack Overflow

How to get query result in Json form using Java API of Google Bigquery

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related