The BigQuery API requires all requests to be authorized by an authenticated user or a service account. This guide describes how to authorize access to BigQuery in various application scenarios.

Contents

1. Getting Started
2. 1. Access and refresh tokens
   2. Client IDs and client secrets
3. Authorizing access from web server applications
4. Authorizing access from installed and desktop applications
5. Authorizing access using client-side JavaScript
6. Server to server authorization
1. Using service accounts
2. Using App Engine service accounts

Getting Started

We strongly recommend that your application use the OAuth 2.0 protocol to authorize requests to the BigQuery API. For more information about OAuth 2.0, read Google's OAuth 2.0 documentation.

Authorizing access to the Google BigQuery API requires use of one of the following scopes:

Access and Refresh Tokens

After successfully authorizing access to the BigQuery API using the OAuth 2.0 web server flow with offline access enabled or service account flow , your application will be given two tokens: an access token and a refresh token. An access token authorizes your application to access BigQuery, but this grant only lasts for an hour before authorization expires. Rather than re-prompting the user for authorization, you can use the refresh token to request a new access token using a server-to-server API call. Refresh tokens don't expire unless they have been revoked and can be used as many times as necessary to request new access tokens.

Client IDs and client secrets

Both the web server and the installed application flow (each described in detail below) require that an application provides a client ID and client secret. To generate an OAuth 2.0 client ID and secret,

1. Visit the Google APIs Console .
2. Create a new project, or select an existing one.
3. Under the Services tab, make sure that the BigQuery API service is activated.
4. Click on API Access on the left, then Create an OAuth2.0 client ID .
5. Enter a product name for your application. You may leave the logo empty for development, but you should supply it for your production application.
6. Select the appropriate application type: either Web application or Installed application .
7. Authorized redirect URIs:
   1. For Web applications, enter the URL of your application's OAuth 2.0 callback handler. For example: https://<your-application-url>/oauth2callback .
   2. For installed applications, the authorized redirect URI will be automatically set to urn:ietf:wg:oauth:2.0:oob .
8. Click "Create client ID" .
9. Copy the client ID and client secret values for use in your application code.

If you are using the Google APIs Client Libraries , we recommend providing the client ID and client secret to your application using a client_secrets.json file. This file is an extensible format for storing OAuth 2.0 client secrets. It should contain all the information an OAuth 2.0 client would need to request a grant from a system. Copy and paste the client ID and client secret into your application's client_secrets.json file (examples below).

The client_secrets.json file for a web server application should follow this format:

{
    "web": {
        "client_id": "XXXXXXXXXXXXXXXXXXXX.apps.googleusercontent.com",
        "client_secret": "XXXXXXXXXXXXXXXXXXXX",
        "redirect_uris": ["http://localhost/oauth2callback",
                          "https://www.example.com/oauth2callback"],
        "auth_uri": "https://accounts.google.com/o/oauth2/auth",
        "token_uri": "https://accounts.google.com/o/oauth2/token"
    }
}

Installed application client_secrets.json files have a slightly different format - note the use of the "out of band" ( urn:ietf:wg:oauth:2.0:oob ) redirect URI:

{
    "installed": {
        "client_id": "XXXXXXXXXXXXXXXXXXXX.apps.googleusercontent.com",
        "client_secret": "XXXXXXXXXXXXXXXXXXXX",
        "redirect_uris": ["urn:ietf:wg:oauth:2.0:oob"],
        "auth_uri": "https://accounts.google.com/o/oauth2/auth",
        "token_uri": "https://accounts.google.com/o/oauth2/token"
    }
}

When building web applications, the client secret should be kept in a secure location such that it is not exposed to the end-user or non-trusted personnel. This provides stronger security, and is important when refresh tokens for many users are stored in one place. Although Google still issues client secrets for installed applications, we recognize that they are not fully secret when included in an application binary. However, in the case of an installed application, only a single refresh token is generally stored.

Authorizing access from web server applications

A web server authorization flow should be used if your application is web-based (deployed to a web server and accessed via web browser) and requires a user to authorize access to the BigQuery API at some point. This authorization flow requires an application-specific client ID and client secret .

If requesting offline access , web server applications may store the refresh token acquired during their authorization flow, in order to prevent the need for additional authorization events. For more information about using refresh tokens, read the access and refresh tokens section above.

It is also possible to build web applications that use an application service account and secret key to authorize API access, and thus do not require a browser-based user authorization event. This "server to server" authorization flow requires that an application-specific secret key can be securely stored. This authorization flow is described in the server to server authorization section below.

The following samples demonstrate how to create a user-authorized access flow to the BigQuery API from Google App Engine.

/*
 * BigQueryWebServerAuthDemo.java extends the AbstractAppEngineAuthorizationCodeServlet class available
 * in the Google OAuth Java library (http://code.google.com/p/google-oauth-java-client/). The first time
 * an end-user arrives at the page handled by this servlet, they will be redirected in the browser to a
 * Google BigQuery API authorization page.
 */
public class BigQueryWebServerAuthDemo extends AbstractAppEngineAuthorizationCodeServlet {

  @Override
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws IOException {
    response.setContentType("text/html");
    PrintWriter writer = response.getWriter();

    Bigquery bigquery = CredentialUtils.loadbigquery();

    Bigquery.Projects.List projectListRequest = bigquery.projects().list();
    ProjectList projectList = projectListRequest.execute();

    if (projectList.getProjects() != null) {

      List projects = projectList.getProjects();
      writer.println("<h3>BigQuery project list:</h3>");

      for (ProjectList.Projects project : projects) {
        writer.printf("%s<br />", project.getProjectReference().getProjectId());
      }

    }
  }

  @Override
  protected AuthorizationCodeFlow initializeFlow() throws ServletException, IOException {
    return CredentialUtils.newFlow();
  }

  @Override
  protected String getRedirectUri(HttpServletRequest request) throws ServletException, IOException {
    return CredentialUtils.getRedirectUri(request);
  }
}


/*
 * BigQueryWebServerAuthCallBack.java extends the AbstractAppEngineAuthorizationCodeCallbackServlet class
 * available in the Google OAuth Java library (http://code.google.com/p/google-oauth-java-client/). If the
 * logged in end-user grants authorization, they will be redirected to this servlet, and the onSuccess()
 * method will be called. In this example, the user will be redirected to the app's root URL.
 */
public class BigQueryWebServerAuthCallBack extends AbstractAppEngineAuthorizationCodeCallbackServlet {

  protected void onSuccess(HttpServletRequest req, HttpServletResponse resp, Credential credential)
      throws ServletException, IOException {
    resp.sendRedirect("/");
  }

  protected void onError(
      HttpServletRequest req, HttpServletResponse resp, AuthorizationCodeResponseUrl errorResponse)
      throws ServletException, IOException {
    String nickname = UserServiceFactory.getUserService().getCurrentUser().getNickname();
    resp.getWriter().print("<p>" + nickname + ", you've declined to authorize this application.</p>");
    resp.getWriter().print("<p><a href=\"/\">Visit this page</a> to try again.</p>");
    resp.setStatus(200);
    resp.addHeader("Content-Type", "text/html");
  }

  @Override
  protected AuthorizationCodeFlow initializeFlow() throws ServletException, IOException {
    return CredentialUtils.newFlow();
  }

  @Override
  protected String getRedirectUri(HttpServletRequest request) throws ServletException, IOException {
    return CredentialUtils.getRedirectUri(request);
  }

}


/*
 * CredentialUtils.java provides helper methods for generating a callback URI, handling
 * an API authorization code flow, and providing an authorized BigQuery API client.
 */
 public class CredentialUtils {

  static final HttpTransport HTTP_TRANSPORT = new UrlFetchTransport();
  static final JsonFactory JSON_FACTORY = new JacksonFactory();
  static final String RESOURCE_LOCATION = "client_secrets.json";
  private static GoogleClientSecrets clientSecrets = null;

  static String getRedirectUri(HttpServletRequest req) {
    GenericUrl url = new GenericUrl(req.getRequestURL().toString());
    url.setRawPath("/oauth2callback");
    return url.build();
  }

  static GoogleClientSecrets getClientCredential() throws IOException {
    if (clientSecrets == null) {
      InputStream inputStream = new FileInputStream(new File(RESOURCE_LOCATION));
      Preconditions.checkNotNull(inputStream, "Cannot open: %s" + RESOURCE_LOCATION);
      clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, inputStream);
    }
    return clientSecrets;
  }

  static GoogleAuthorizationCodeFlow newFlow() throws IOException {
    return new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY,
        getClientCredential(), Collections.singleton(BigqueryScopes.BIGQUERY)).setCredentialStore(
        new AppEngineCredentialStore()).setAccessType("offline").build();
  }

  static Bigquery loadbigquery() throws IOException {
    String userId = UserServiceFactory.getUserService().getCurrentUser().getUserId();
    Credential credential = newFlow().loadCredential(userId);
    return new Bigquery.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential).build();
  }

}
  

import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.api.services.bigquery.*;

import java.io.IOException;
import java.io.PrintWriter;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/*
 * BigQueryWebServerAuthDemo.java extends the AbstractAuthorizationCodeServlet class available
 * in the Google OAuth Java library (http://code.google.com/p/google-oauth-java-client/). The first time
 * an end-user arrives at the page handled by this servlet, they will be redirected in the browser to a
 * Google BigQuery API authorization page.
 */
public class BigQueryWebServerAuthDemo extends AbstractAuthorizationCodeServlet {

  

  @Override
  protected void doGet(HttpServletRequest request, HttpServletResponse response)
      throws IOException {
    response.setContentType("text/html");
    PrintWriter writer = response.getWriter();

    Bigquery bigquery = CredentialUtils.loadbigquery();

    Bigquery.Projects.List projectListRequest = bigquery.projects().list();

    writer.println("Project.list raw response:");
    writer.println(projectListRequest.execute().toPrettyString());
  }

  @Override
  protected AuthorizationCodeFlow initializeFlow() throws ServletException, IOException {
    return CredentialUtils.newFlow();
  }

  @Override
  protected String getRedirectUri(HttpServletRequest request) throws ServletException, IOException {
    return CredentialUtils.getRedirectUri(request);
  }

  @Override
  protected String getUserId(HttpServletRequest request) throws ServletException, IOException {
    return null;
  }
}
/* End of BigQueryWebServerAuthDemo.java */

import com.google.api.client.auth.oauth2.AuthorizationCodeFlow;
import com.google.api.client.auth.oauth2.AuthorizationCodeResponseUrl;
import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.extensions.servlet.auth.oauth2.AbstractAuthorizationCodeCallbackServlet;

import java.io.IOException;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
/*
 * BigQueryWebServerAuthCallBack.java extends the
 * AbstractAuthorizationCodeCallbackServlet class available in the
 * Google OAuth Java library
 * (http://code.google.com/p/google-oauth-java-client/). If the logged in
 * end user grants authorization, they will be redirected to this servlet, and
 * the onSuccess() method will be called. In this example, the user will be
 * redirected to the app's root URL.
 */
public class BigQueryWebServerAuthCallBack extends AbstractAuthorizationCodeCallbackServlet {

  protected void onSuccess(HttpServletRequest req, HttpServletResponse resp, Credential credential)
      throws ServletException, IOException {
    resp.sendRedirect("/");
  }

  protected void onError(
      HttpServletRequest req, HttpServletResponse resp, AuthorizationCodeResponseUrl errorResponse)
      throws ServletException, IOException {
    resp.getWriter().print("<p>You've declined to authorize this application.</p>");
    resp.getWriter().print("<p><a href=\"/\">Visit this page</a> to try again.</p>");
    resp.setStatus(200);
    resp.addHeader("Content-Type", "text/html");
  }

  @Override
  protected AuthorizationCodeFlow initializeFlow() throws ServletException, IOException {
    return CredentialUtils.newFlow();
  }

  @Override
  protected String getRedirectUri(HttpServletRequest request) throws ServletException, IOException {
    return CredentialUtils.getRedirectUri(request);
  }

  @Override
  protected String getUserId(HttpServletRequest request) throws ServletException, IOException {
    return null;
  }

}
/* End of BigQueryWebServerAuthCallBack.java */

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.auth.oauth2.MemoryCredentialStore;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.http.GenericUrl;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson2.JacksonFactory;
import com.google.api.client.util.Preconditions;
import com.google.api.services.bigquery.*;

import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.util.Collections;

import javax.servlet.http.HttpServletRequest;

/*
 * CredentialUtils.java provides helper methods for generating a callback URI,
 * handling an API authorization code flow, and providing an authorized
 * BigQuery API client.
 */
 public class CredentialUtils {

  static final HttpTransport HTTP_TRANSPORT = new NetHttpTransport();
  static final JsonFactory JSON_FACTORY = new JacksonFactory();
  static final String RESOURCE_LOCATION = "client_secrets.json";
  private static GoogleClientSecrets clientSecrets = null;

  static String getRedirectUri(HttpServletRequest req) {
    GenericUrl url = new GenericUrl(req.getRequestURL().toString());
    url.setRawPath("/oauth2callback");
    return url.build();
  }

  static GoogleClientSecrets getClientCredential() throws IOException {
    if (clientSecrets == null) {
      InputStream inputStream = new FileInputStream(new File(RESOURCE_LOCATION));
      Preconditions.checkNotNull(inputStream, "Cannot open: %s" + RESOURCE_LOCATION);
      clientSecrets = GoogleClientSecrets.load(JSON_FACTORY, inputStream);
    }
    return clientSecrets;
  }

  static GoogleAuthorizationCodeFlow newFlow() throws IOException {
    return new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT, JSON_FACTORY,
        getClientCredential(), BigqueryScopes.all()).setCredentialStore(
        new MemoryCredentialStore()).setAccessType("offline").build();
  }

  static Bigquery loadbigquery() throws IOException {
    String userId = getUserId();
    Credential credential = newFlow().loadCredential(userId);
    return new Bigquery.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential).build();
  }

  private static String getUserId() {
    // Include your custom implementation for retrieval of a unique
    // user id string from your application.
    String userId = "";
    return userId;
  }

}
/* End of CredentialUtils.java */

import os
import json
import sys


from apiclient.discovery import build

from google.appengine.ext import webapp
from google.appengine.ext.webapp.util import run_wsgi_app

from oauth2client.appengine import OAuth2DecoratorFromClientSecrets

decorator = OAuth2DecoratorFromClientSecrets(
  os.path.join(os.path.dirname(__file__), 'client_secrets.json'),
  scope='https://www.googleapis.com/auth/bigquery')

PROJECTID = '<myproject_id>'




service = build('bigquery', 'v2')


class ListDatasets(webapp.RequestHandler):

  @decorator.oauth_required
  def get(self):
    http = decorator.http()

    datasets = service.datasets()
    try:
      response = datasets.list(projectId=PROJECTID).execute(http)

      self.response.out.write('<h3>Datasets.list raw response:</h3>')
      self.response.out.write('<pre>%s</pre>' % json.dumps(response,
                                                  sort_keys=True,
                                                  indent=4,
                                                  separators=(',', ': ')))
    except:
      e = str(sys.exc_info()[0]).replace('&', '&amp;'
                               ).replace('"', '&quot;'
                               ).replace("'", '&#39;'
                               ).replace(">", '&gt;'
                               ).replace("<", '&lt;')
      self.response.out.write( "<p>Error: %s</p>" % e )

application = webapp.WSGIApplication([
                ('/', ListDatasets),
                (decorator.callback_path, decorator.callback_handler()),
                ], debug=True)

def main():
  run_wsgi_app(application)

if __name__ == "__main__":
  main()

Authorizing access from installed and desktop applications

If you are creating an installed application, such as an application that is run from a desktop or mobile device, the BigQuery API authorization flow is slightly different than that of a web server based application. Applications that use an installed flow require that the application has access to the system web browser, or the ability to embed a browser control in the application. During the authorization flow, the application will redirect the user to a Google-hosted webpage to request access to the BigQuery API. If access is granted, the page will provide an access code that must be given to the application to complete the BigQuery authorization grant.

An example URL is shown below.

https://accounts.google.com/o/oauth2/auth?
scope=https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fuserinfo.email+https%3A%2F%2Fwww.googleapis.com%2Fauth%2Fuserinfo.profile&
redirect_uri=urn:ietf:wg:oauth:2.0:oob&
response_type=code&
client_id=812741506391-h38jh0j4fv0ce1krdkiq0hfvt6n5amrf.apps.googleusercontent.com

If the user logs in and grants access via a URL similar to the one shown above, the result will be a dialog similar to the following:

In addition to being displayed in the web browser window for copying and pasting, the access code is also included in the browser window title, where it can be programmatically retrieved by a client application on some operating systems. This provides a smoother user experience.

These applications may access a Google API while the user is present to authorize API access. If a refresh token is stored, these applications may continue to access a Google API without subsequent authorization steps by a user. For more information about using refresh tokens, read the access and refresh tokens section above.

The examples below demonstrate applications that use a browser-based user authorization step for access to the BigQuery API. After authorization is granted, the applications store the resulting refresh token so further authorization events are not required. To use these samples with your own BigQuery project, replace the PROJECT_NUMBER variable with the value from your Google APIs Console project .

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.util.Collections;
import java.util.List;
import java.util.Properties;

import com.google.api.client.auth.oauth2.Credential;
import com.google.api.client.auth.oauth2.TokenResponse;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeFlow;
import com.google.api.client.googleapis.auth.oauth2.GoogleAuthorizationCodeRequestUrl;
import com.google.api.client.googleapis.auth.oauth2.GoogleClientSecrets;
import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.client.googleapis.auth.oauth2.GoogleTokenResponse;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson.JacksonFactory;
import com.google.api.services.bigquery.Bigquery;
import com.google.api.services.bigquery.Bigquery.Datasets;
import com.google.api.services.bigquery.BigqueryScopes;
import com.google.api.services.bigquery.model.DatasetList;


class BigQueryInstalledAuthDemo {

  // Change this to your current project ID
  private static final String PROJECT_NUMBER = "XXXXXXXXXX";

  // Load Client ID/secret from client_secrets.json file.
  private static final String CLIENTSECRETS_LOCATION = "client_secrets.json";
  static GoogleClientSecrets clientSecrets = loadClientSecrets();

  private static final String REDIRECT_URI = "urn:ietf:wg:oauth:2.0:oob";

  // Objects for handling HTTP transport and JSON formatting of API calls
  private static final HttpTransport HTTP_TRANSPORT = new NetHttpTransport();
  private static final JsonFactory JSON_FACTORY = new JacksonFactory();

  private static GoogleAuthorizationCodeFlow flow = null;

  // BigQuery Client
  static Bigquery bigquery;


  public static void main(String[] args) throws IOException {

    // Attempt to Load existing Refresh Token
    String storedRefreshToken = loadRefreshToken();

    // Check to see if the an existing refresh token was loaded.
    // If so, create a credential and call refreshToken() to get a new
    // access token.
    if (storedRefreshToken != null) {

      // Request a new Access token using the refresh token.
      GoogleCredential credential = createCredentialWithRefreshToken(
          HTTP_TRANSPORT, JSON_FACTORY, new TokenResponse().setRefreshToken(storedRefreshToken));
      credential.refreshToken();

      bigquery = buildService(credential);

    // If there is no refresh token (or token.properties file), start the OAuth
    // authorization flow.
    } else {
      String authorizeUrl = new GoogleAuthorizationCodeRequestUrl(
          clientSecrets,
          REDIRECT_URI,
          Collections.singleton(BigqueryScopes.BIGQUERY)).setState("").build();

      System.out.println("Paste this URL into a web browser to authorize BigQuery Access:\n" + authorizeUrl);

      System.out.println("... and type the code you received here: ");
      BufferedReader in = new BufferedReader(new InputStreamReader(System.in));
      String authorizationCode = in.readLine();

      // Exchange the auth code for an access token and refesh token
      Credential credential = exchangeCode(authorizationCode);

      // Store the refresh token for future use.
      storeRefreshToken(credential.getRefreshToken());

      bigquery = buildService(credential);
    }

    // Make API calls using your client.
    listDatasets(bigquery, PROJECT_NUMBER);

  }


  /**
   *  Builds an authorized BigQuery API client.
   */
  private static Bigquery buildService(Credential credential) {
    return new Bigquery.Builder(HTTP_TRANSPORT, JSON_FACTORY, credential).build();
  }


  /**
   * Build an authorization flow and store it as a static class attribute.
   */
  static GoogleAuthorizationCodeFlow getFlow() {
    if (flow == null) {
      flow = new GoogleAuthorizationCodeFlow.Builder(HTTP_TRANSPORT,
          JSON_FACTORY,
          clientSecrets,
          Collections.singleton(BigqueryScopes.BIGQUERY))
      .setAccessType("offline").setApprovalPrompt("force").build();
    }
    return flow;
  }


  /**
   * Exchange the authorization code for OAuth 2.0 credentials.
   */
  static Credential exchangeCode(String authorizationCode) throws IOException  {
    GoogleAuthorizationCodeFlow flow = getFlow();
    GoogleTokenResponse response =
        flow.newTokenRequest(authorizationCode).setRedirectUri(REDIRECT_URI).execute();
    return flow.createAndStoreCredential(response, null);
  }


  /**
   * No need to go through OAuth dance, get an access token using the
   * existing refresh token.
   */
  public static GoogleCredential createCredentialWithRefreshToken(HttpTransport transport,
      JsonFactory jsonFactory, TokenResponse tokenResponse) {
    return new GoogleCredential.Builder().setTransport(transport)
        .setJsonFactory(jsonFactory)
        .setClientSecrets(clientSecrets)
        .build()
        .setFromTokenResponse(tokenResponse);
  }


  /**
   *  Helper to load client ID/Secret from file.
   */
  private static GoogleClientSecrets loadClientSecrets() {
    try {
      GoogleClientSecrets clientSecrets =
          GoogleClientSecrets.load(new JacksonFactory(),
              BigQueryInstalledAuthDemo.class.getResourceAsStream(CLIENTSECRETS_LOCATION));
      return clientSecrets;
    } catch (Exception e)  {
      System.out.println("Could not load clientsecrets.json");
      e.printStackTrace();
    }
    return clientSecrets;
  }


  /**
   *  Helper to store a new refresh token in token.properties file.
   */
  private static void storeRefreshToken(String refresh_token) {
    Properties properties = new Properties();
    properties.setProperty("refreshtoken", refresh_token);
    System.out.println(properties.get("refreshtoken"));
    try {
      properties.store(new FileOutputStream("token.properties"), null);
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
  }


  /**
   *  Helper to load refresh token from the token.properties file.
   */
  private static String loadRefreshToken(){
    Properties properties = new Properties();
    try {
      properties.load(new FileInputStream("token.properties"));
    } catch (FileNotFoundException e) {
      e.printStackTrace();
    } catch (IOException e) {
      e.printStackTrace();
    }
    return (String) properties.get("refreshtoken");
  }


  /**
   *
   * List available Datasets.
   */
  public static void listDatasets(Bigquery bigquery, String projectId)
      throws IOException {
    Datasets.List datasetRequest = bigquery.datasets().list(projectId);
    DatasetList datasetList = datasetRequest.execute();
    if (datasetList.getDatasets() != null) {
      List<DatasetList.Datasets> datasets = datasetList.getDatasets();
      System.out.println("Available datasets\n----------------");
      for (com.google.api.services.bigquery.model.DatasetList.Datasets dataset : datasets) {
        System.out.format("%s\n", dataset.getDatasetReference().getDatasetId());
      }
    }
  }

}
  

import httplib2
import pprint
import sys

from apiclient.discovery import build
from apiclient.errors import HttpError

from oauth2client.client import OAuth2WebServerFlow
from oauth2client.client import AccessTokenRefreshError
from oauth2client.client import flow_from_clientsecrets
from oauth2client.file import Storage
from oauth2client.tools import run

# Enter your Google Developer Project number
PROJECT_NUMBER = 'XXXXXXXXXXXXX'
FLOW = flow_from_clientsecrets('client_secrets.json',
                               scope='https://www.googleapis.com/auth/bigquery')


def main():
  storage = Storage('bigquery_credentials.dat')
  credentials = storage.get()

  if credentials is None or credentials.invalid:
    credentials = run(FLOW, storage)

  http = httplib2.Http()
  http = credentials.authorize(http)

  bigquery_service = build('bigquery', 'v2', http=http)

  try:
    datasets = bigquery_service.datasets()
    listReply = datasets.list(projectId=PROJECT_NUMBER).execute()
    print 'Dataset list:'
    pprint.pprint(listReply)

  except HttpError as err:
    print 'Error in listDatasets:', pprint.pprint(err.content)

  except AccessTokenRefreshError:
    print ("Credentials have been revoked or expired, please re-run"
           "the application to re-authorize")

if __name__ == '__main__':
  main()
  

Authorizing access using client-side JavaScript

Browser-based client applications written in JavaScript may also make calls to the BigQuery API, but with a slightly different flow than that of web server applications . Because the JavaScript application will not be able to keep the client secret hidden, this authorization flow requires that a user always be present to authorize access to the BigQuery API. Applications that make calls to the BigQuery API using this authorization method require a client ID, but do not require either a client secret or an API key. In order to generate a client ID, follow the steps for "Web applications" instructions in the Client IDs and client secrets section above. For more information on how this authorization flow works, view the Google APIs Client Library for JavaScript authorization documentation .

If your application is a publicly accessible online dashboard, you'll need to securely proxy the requests through a server-side application, such as a Google App Engine application. BigQuery queries must be charged to a specific project, so the Google App Engine app should keep track of the requests being made to ensure they represent legitimate usage of your application.

The following code sample demonstrates how to use the Google APIs Client Library for JavaScript to authorize access to the BigQuery API. To use this sample with your own BigQuery project, replace the projectNumber and clientId variables with values from your Google APIs Console project .

<html>
  <head>
    <script src="https://apis.google.com/js/client.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
    <script>

      function auth() {
        gapi.auth.authorize(config, function() {
            gapi.client.load('bigquery', 'v2');
            $('#client_initiated').html('BigQuery client authorized');
            $('#auth_button').fadeOut();
            $('#dataset_button').fadeIn();
        });
      }

      // User Submitted Variables
      var projectNumber = 'XXXXXXXXXX';
      var clientId = 'XXXXXXXXXX.apps.googleusercontent.com';

      var config = {
        'client_id': clientId,
        'scope': 'https://www.googleapis.com/auth/bigquery'
      };

      function listDatasets() {
        var request = gapi.client.bigquery.datasets.list({
          'projectId':projectNumber
        });
        request.execute(function(response) {
            $('#result_box').html(JSON.stringify(response.result.datasets, null));
        });
      }
    </script>
  </head>

  <body>
    <button id="auth_button" onclick="auth();">Authorize</button>
    <div id="client_initiated"></div>
    <button id="dataset_button" style="display:none;" onclick="listDatasets();">Show datasets</button>
    <div id="result_box"></div>
  </body>
</html>
  

Server to server authorization

The BigQuery API can also be authorized through a service account flow, which provide certificate-based authentication for server-to-server interactions. This authorization flow requires that the application can securely store a secret key file. Typical use cases include command line applications hosted on secure machines that operate without explicit user authorization, or scheduled processes which regularly load data into BigQuery as background tasks or cron jobs. If you are developing an application using Google App Engine, and require this type of authorization, App Engine automatically handles much of the authorization and key management process. See the App Engine specific instructions below.

Using service accounts

In order to create a service account, and generate a PKCS12 (p12) certificate file for your application:

1. Visit the Google Developer console at Google APIs Console .
2. Create a new project, or select an existing one.
3. Under the Services tab, make sure that the BigQuery API service is activated.
4. Click on "API Access" on the left, then "Create an OAuth2.0 client ID".
5. Choose Service Accounts and click on Create Client ID .
6. Download your private key and store it in a secure area where your application can access it.
7. Note the service account Email address , which will be used in your code to identify your application.

The following samples demonstrate how to use the service account email address and private key generated in the steps above to make calls to the BigQuery API.

The service account flow uses cryptographic signatures to validate authorization. These signatures rely on an accurate computer clock. You should ensure that the time on your server is synchronized to a NTP server and is using the appropriate timezone. If the time is off by even a second or two, you could experience authorization failures.

import com.google.api.client.http.HttpTransport;
import com.google.api.client.http.javanet.NetHttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson.JacksonFactory;

import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.services.bigquery.Bigquery;
import com.google.api.services.bigquery.Bigquery.Datasets;
import com.google.api.services.bigquery.model.DatasetList;

import java.io.File;
import java.io.IOException;
import java.security.GeneralSecurityException;

public class JavaCommandLineServiceAccounts {

  private static final String SCOPE = "https://www.googleapis.com/auth/bigquery";
  private static final HttpTransport TRANSPORT = new NetHttpTransport();
  private static final JsonFactory JSON_FACTORY = new JacksonFactory();

  private static Bigquery bigquery;

  public static void main(String[] args) throws IOException, GeneralSecurityException {
    GoogleCredential credential = new GoogleCredential.Builder().setTransport(TRANSPORT)
        .setJsonFactory(JSON_FACTORY)
        .setServiceAccountId("[email protected]")
        .setServiceAccountScopes(SCOPE)
        .setServiceAccountPrivateKeyFromP12File(new File("my_file.p12"))
        .build();

    bigquery = new Bigquery.Builder(TRANSPORT, JSON_FACTORY, credential)
        .setApplicationName("BigQuery-Service-Accounts/0.1")
        .setHttpRequestInitializer(credential).build();

    Datasets.List datasetRequest = bigquery.datasets().list("publicdata");
    DatasetList datasetList = datasetRequest.execute();
    System.out.format("%s\n", datasetList.toPrettyString());
  }

}
  

import httplib2

from apiclient.discovery import build
from oauth2client.client import SignedJwtAssertionCredentials

# REPLACE WITH YOUR Project ID
PROJECT_NUMBER = 'XXXXXXXXXXX'
# REPLACE WITH THE SERVICE ACCOUNT EMAIL FROM GOOGLE DEV CONSOLE
SERVICE_ACCOUNT_EMAIL = '[email protected]'

f = file('key.p12', 'rb')
key = f.read()
f.close()

credentials = SignedJwtAssertionCredentials(
    SERVICE_ACCOUNT_EMAIL,
    key,
    scope='https://www.googleapis.com/auth/bigquery')

http = httplib2.Http()
http = credentials.authorize(http)

service = build('bigquery', 'v2')
datasets = service.datasets()
response = datasets.list(projectId=PROJECT_NUMBER).execute(http)

print('Dataset list:\n')
for dataset in response['datasets']:
  print("%s\n" % dataset['id'])
  

Using App Engine service accounts

The App Identity API provides a means for App Engine apps to identify themselves to Google for server to server API authorization. If you are creating an App Engine application, it's possible to authorize your application's service account to access Google APIs. Typical use cases include public data visualization or dashboard apps that do not require explicit user authorization before making calls to BigQuery.

To allow your App Engine app to make authorized service account calls to the BigQuery API:

1. Copy the App Engine service account name found under the Administration -> Application Settings tab (in the form of [email protected] ).
2. Visit your Google APIs Console project's Team tab, and add the service account name as a project teammate with " Can edit " permissions.

Google's API client libraries provide convenient methods for authorizing credentials for accessing Google APIs using App Engine service accounts. The following examples demonstrate how to make calls to the BigQuery API from authorized App Engine applications.

package com.google.cloud.helix.samples.appengine;

import com.google.api.client.extensions.appengine.http.UrlFetchTransport;
import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
import com.google.api.client.googleapis.extensions.appengine.auth.oauth2.AppIdentityCredential;
import com.google.api.client.http.HttpRequestInitializer;
import com.google.api.client.http.HttpTransport;
import com.google.api.client.json.JsonFactory;
import com.google.api.client.json.jackson.JacksonFactory;
import com.google.api.services.bigquery.Bigquery;
import com.google.api.services.bigquery.model.DatasetList;
import com.google.api.services.bigquery.model.QueryRequest;
import com.google.api.services.bigquery.model.QueryResponse;
import com.google.appengine.api.appidentity.AppIdentityServiceFactory;

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

import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

/**
 * Minimal example BigQuery App Engine servlet.
 */
public class BigqueryAppengineMiniServlet extends HttpServlet {

  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();
  // @VisibleForTesting
  static Bigquery bigquery = new Bigquery(TRANSPORT, JSON_FACTORY, getRequestInitializer());

  @Override
  public void doGet(HttpServletRequest request, HttpServletResponse response) throws IOException {
    response.setContentType("text/plain");
    response.setCharacterEncoding("UTF-8");
    DatasetList list = bigquery.datasets().list("publicdata").execute();
    response.getWriter().println(list);
    String query = "select count(*) from [publicdata:samples.shakespeare]";
    response.getWriter().println(query);
    response.getWriter().println("Response: " + queryBigqueryQuick(query).getRows().get(0));
  }

  /*
   * 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.
   */
  private QueryResponse queryBigqueryQuick(String query) throws IOException {
    return bigquery.jobs().query(getProjectId(), new QueryRequest().setQuery(query)).execute();
  }

  private String getProjectId() {
    // In App Engine each app has a BigQuery project id with the same name as the app.
    // You'll need to turn on the BigQuery API on the cloud console for this to work.
    return AppIdentityServiceFactory.getAppIdentityService().getServiceAccountName().split("@")[0];
  }

  private static HttpRequestInitializer getRequestInitializer() {
    if (System.getProperty("OAUTH_ACCESS_TOKEN") != null) {
      // Good for testing and localhost environments, where no AppIdentity is available.
      return new GoogleCredential().setAccessToken(System.getenv("OAUTH_ACCESS_TOKEN"));
    }
    return new AppIdentityCredential(Arrays.asList(new String[] {SCOPE}));
  }
}

import httplib2

from apiclient.discovery import build
from google.appengine.ext import webapp
from google.appengine.ext.webapp.util import run_wsgi_app
from oauth2client.appengine import AppAssertionCredentials

# BigQuery API Settings
SCOPE = 'https://www.googleapis.com/auth/bigquery'
PROJECT_NUMBER = 'XXXXXXXXXX' # REPLACE WITH YOUR Project ID

# Create a new API service for interacting with BigQuery
credentials = AppAssertionCredentials(scope=SCOPE)
http = credentials.authorize(httplib2.Http())
bigquery_service = build('bigquery', 'v2', http=http)


class ListDatasets(webapp.RequestHandler):
  def get(self):
    datasets = bigquery_service.datasets()
    listReply = datasets.list(projectId=PROJECT_NUMBER).execute()
    self.response.out.write('Dataset list:')
    self.response.out.write(listReply)


application = webapp.WSGIApplication(
                                     [('/listdatasets(.*)', ListDatasets)],
                                     debug=True)

def main():
  run_wsgi_app(application)

if __name__ == "__main__":
  main()
  

Except as otherwise noted, the content of this page is licensed under the Creative Commons Attribution 3.0 License , and code samples are licensed under the Apache 2.0 License . For details, see our Site Policies .