package com.bigquery;
import com.google.api.client.extensions.appengine.http.UrlFetchTransport;
import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory; // was com.google.api.client.json.jackson.JacksonFactory;
import com.google.api.client.util.Data;
import com.google.api.services.bigquery.Bigquery;
import com.google.api.services.bigquery.Bigquery.Datasets;
import com.google.api.services.bigquery.Bigquery.Jobs;
import com.google.api.services.bigquery.model.Dataset;
import com.google.api.services.bigquery.model.DatasetList;
import com.google.api.services.bigquery.model.GetQueryResultsResponse;
import com.google.api.services.bigquery.model.ProjectList;
import com.google.api.services.bigquery.model.ProjectList.Projects;
import com.google.api.services.bigquery.model.QueryRequest;
import com.google.api.services.bigquery.model.QueryResponse;
import com.google.api.services.bigquery.model.TableCell;
import com.google.api.services.bigquery.model.TableFieldSchema;
import com.google.api.services.bigquery.model.TableRow;
import com.google.api.services.bigquery.model.TableSchema;
import java.io.File;
import java.io.IOException;
import java.io.PrintWriter;
import java.math.BigInteger;
import java.security.GeneralSecurityException;
import java.util.Arrays;
import java.util.List;
import java.util.concurrent.TimeUnit;
import java.util.logging.Logger;
import javax.servlet.ServletConfig;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
// BigQuery App Engine servlet example
@SuppressWarnings("serial")
public class BigqueryServlet extends HttpServlet {
private static final Logger log = Logger.getLogger(BigqueryServlet.class.getName());
private static final String SCOPE = "https://www.googleapis.com/auth/bigquery";
private static final HttpTransport TRANSPORT = new UrlFetchTransport();
private static final JsonFactory JSON_FACTORY = new JacksonFactory();
static Bigquery bigquery = null;
static Jobs jobs = null;
static String projectid = null;
// Default query string. Define the query string in web.xml
static String query = "SELECT name, number FROM [company_employees.employees] LIMIT 1000";
static String keystorefilename = null;
static String serviceaccountid = null;
// get the projectid, serviceaccountid, query string from the web.xml file
// You MUST set at least projectid & serviceaccountid for this example to run
void getParams(ServletConfig config) {
// parameters stored in /war/WEB-INF/web.xml
keystorefilename = config.getInitParameter("keystorefilename");
projectid = config.getInitParameter("projectid");
serviceaccountid = config.getInitParameter("serviceaccountid");
log.info("PARAMS keystorefilename: " + keystorefilename + " projectid: " + projectid);
// CAUTION: DON'T DISPLAY THE SERVICE ACCOUNT ID, ONLY FOR DEBUGGING
// set .level = CONFIG in logging.properties to see them in the logs
log.config("PARAMS serviceaccountid: " + serviceaccountid);
String initquery = config.getInitParameter("query");
if (initquery != null) {
log.info("query string: " + initquery);
query = initquery;
} else {
log.info("no query string in web.xml using: " + query);
}
}
@Override
public void init(ServletConfig config) throws ServletException {
log.info("SERVLET init");
getParams(config);
// set .level = CONFIG in logging.properties to see the OAuth exchange in the logs
log.info("GoogleCredential requesting credential");
GoogleCredential credential = null;
// for debugging if the file isn't found
File keyfile = new File(keystorefilename);
String abspath = keyfile.getAbsolutePath();
log.info("keyfile absolute path: " + abspath);
try {
credential = new GoogleCredential.Builder().setTransport(TRANSPORT)
.setJsonFactory(JSON_FACTORY)
.setServiceAccountId(serviceaccountid)
.setServiceAccountScopes(Arrays.asList(new String[] {SCOPE}))
.setServiceAccountPrivateKeyFromP12File(keyfile)
.build();
} catch (GeneralSecurityException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
log.info("private key file '" + abspath + " exception: " + e);
}
log.info("GoogleCredential got credential");
// TODO: check if BigQuery instances are thread safe or if we need one instance per request
log.info("GoogleCredential getting BiqQuery object");
bigquery = new Bigquery.Builder(TRANSPORT, JSON_FACTORY, credential)
// TODO: find where this app name is defined in GAE or bigQuery
.setApplicationName("BigQuery-Service-Accounts/0.1") // any name seems to do it
.setHttpRequestInitializer(credential).build();
log.info("GoogleCredential got BiqQuery object");
jobs = getJobs(bigquery); // get it once for the servlet lifetime
// for debug, gets the projectId from BigQuery
projectid = getProjectid(bigquery);
// These BigQuery calls are just here as examples
log.info("Sending BigQuery request");
Datasets.List datasetRequest;
try {
datasetRequest = bigquery.datasets().list("publicdata");
DatasetList datasetList = datasetRequest.execute();
log.info("Got BigQuery request");
String list = datasetList.toPrettyString();
log.info("datasetList: " + list);
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
String projectid = getProjectid(this.bigquery);
String datasetid = getDatasets(projectid);
}
@Override
public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
log.info("SERVLET doGet");
// query = "select count(*) from [publicdata:samples.shakespeare]";
QueryResponse qr = executeQuickQuery(jobs, projectid, query);
String msg = qr.toPrettyString();
log.info("Query: '" + query + "'\nResponse: " + msg);
response.setContentType("text/plain");
response.setCharacterEncoding("UTF-8");
PrintWriter writer = response.getWriter();
writer.println(msg);
log.info("SERVLET doGet EXIT");
}
// Should only find one project, the one configured to be accessed through the serviceaccountid
String getProjectid(Bigquery bigquery) {
String r = null;
try {
log.info("BigQuery projects().list()");
Bigquery.Projects.List listrequest = bigquery.projects().list();
log.info("BigQuery listrequest.execute()");
ProjectList projectlist = listrequest.execute();
String list = projectlist.toPrettyString();
log.info("Project list: " + list);
java.util.List<Projects> projects = projectlist.getProjects();
for (Projects p : projects) {
String s = p.toPrettyString();
log.info("Project: " + s);
String projectid = p.getId(); // Get the project id
log.info("Projectid: " + projectid);
r = projectid;
}
} catch (IOException e) {
// Maybe OAuth token expired?
log.info("getProjects IOException: " + e);
}
return r;
}
String getDatasets(String projectid) {
String r = null;
try {
Bigquery.Datasets.List datasetrequest = bigquery.datasets().list(projectid);
DatasetList datasetlist = datasetrequest.execute();
String list = datasetlist.toPrettyString();
log.info("DatasetList list: " + list);
List<com.google.api.services.bigquery.model.DatasetList.Datasets> datasets = datasetlist.getDatasets();
if (datasets != null) {
for (com.google.api.services.bigquery.model.DatasetList.Datasets d : datasets) {
String s = d.toPrettyString();
log.info("Dataset: " + s);
String name = d.getFriendlyName(); // null
String id = d.getId(); // projectid:datasetid
String kind = d.getKind(); // bigquery#dataset
String datasetid = d.getDatasetReference().getDatasetId();
log.info("Dataset name: " + name + " project#id: " + id + " kind: " + kind + " id: " + datasetid);
r = datasetid;
}
} else {
log.info("NO DATASETS!");
}
} catch (IOException e) {
// Maybe OAuth token expired?
log.info("getDatasets IOException: " + e);
}
return r;
}
Dataset getDataset(String projectid, String datasetid) {
Dataset dataset = null;
try {
Bigquery.Datasets.Get getrequest = bigquery.datasets().get(projectid, datasetid);
dataset = getrequest.execute();
} catch (IOException e) {
log.info("getDataset exception: " + e);
}
return dataset;
}
Jobs getJobs(Bigquery bigquery) {
// no exception?
Jobs jobs = bigquery.jobs(); // An accessor for creating requests from the Jobs collection.
return jobs;
}
// Queries BigQuery without polling or paging.
// Use this function for small, quick queries that return less than 100 rows and take less than 5 seconds to process.
QueryResponse executeQuickQuery(Jobs jobs, String projectid, String query) throws IOException {
long starttime = System.nanoTime();
QueryRequest queryrequest = new QueryRequest().setQuery(query);
QueryResponse queryresponse = jobs.query(projectid, queryrequest).execute();
TableSchema schema = queryresponse.getSchema();
log.info("Table schema: " + schema.toPrettyString());
List<TableFieldSchema> fields = schema.getFields();
for (TableFieldSchema field : fields) {
String name = field.getName();
String type = field.getType();
String mode = field.getMode();
log.info("Field name: " + name + " type: " + " mode: " + mode);
}
BigInteger numberrows = queryresponse.getTotalRows();
log.info("Total rows: " + numberrows);
String pagetoken = queryresponse.getPageToken();
log.info("Page token: " + pagetoken);
String jobid = queryresponse.getJobReference().getJobId();
GetQueryResultsResponse results = jobs.getQueryResults(projectid, jobid).execute();
long endtime = 0;
if (results.getJobComplete()) {
endtime = System.nanoTime();
List<TableRow> rows = results.getRows();
if (rows != null) {
for (TableRow row : rows) {
log.info("TableRow");
List<TableCell> cells = row.getF();
for (TableCell cell : cells) {
String value = "null";
Object o = cell.getV();
if (!Data.isNull(o)) {
value = (String) cell.getV();
log.info("cell: value: " + value + " class: " + cell.getClass() + " pretty string: " + cell.toPrettyString());
}
} // for (TableRow row : rows)
} // for (TableRow row : rows)
} // if (rows != null)
} else { // if (results.getJobComplete())
log.info("???????? NOT results.getJobComplete");
}
long elapsedtime = endtime - starttime;
log.info("ELAPSED time: " + TimeUnit.NANOSECONDS.toMillis(elapsedtime) + " ms � " + elapsedtime + " ns");
return queryresponse;
}
}