我使用的是Google Bigquery V2 Java API。我无法找到以JSON格式获取查询结果的方法。在Bigquery Web UI中,我们可以看到结果的JSON和表格形式。请参见scrrenshot。
有没有办法使用Java API将GetQueryResultsResponse转换为JSON?
发布于 2017-05-24 15:32:21
一种选择是将TO_JSON_STRING
function应用于查询结果。例如,
#standardSQL
SELECT TO_JSON_STRING(t)
FROM (
SELECT x, y
FROM YourTable
WHERE z = 10
) AS t;
如果您希望表的所有列都是JSON,则可以使用更简单的表单:
#standardSQL
SELECT TO_JSON_STRING(t)
FROM YourTable AS t
WHERE z = 10;
发布于 2018-07-13 17:57:48
我使用服务帐户访问JSON以获取BigQuery格式的响应。
为了使用服务帐户,您必须转到凭据(https://console.cloud.google.com/apis/credentials)并选择一个项目。
您将看到如下所示的下拉列表:
为您的项目创建一个Service帐户,并下载JSON格式的秘密文件。将JSON文件保存在您的文件系统中,并设置它的路径。检查下图以设置文件路径:
因此,现在您所要做的就是使用JAVA客户端API来使用Big Query REST api。
这是我在我的项目中使用的一个简单的解决方案。
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;
}
}
以下是Github代码的链接:https://github.com/vslala/BigQueryRestSample
这只是一个从BQ REST API获取JSON数据的演示项目。不要直接在项目中使用它。如果你有任何问题,请告诉我。
https://stackoverflow.com/questions/44150064
复制相似问题