Google Cloud Platform
BigQuery

Authenticating requests to the Google BigQuery API

The BigQuery API requires all requests to be authenticated as a user or a service account. This guide describes how to perform authentication in various application scenarios.

  1. Application Default Credentials
  2. Authenticating as an end user
    1. In web server applications
    2. In installed and desktop applications
    3. In client-side JavaScript

Application Default Credentials

Client libraries can use Application Default Credentials to easily authenticate with Google APIs and send requests to those APIs. With Application Default Credentials, you can test your application locally and deploy it without changing the underlying code. For more information, including code samples, see Google Cloud Platform Auth Guide.

The following code sample demonstrates authenticating BigQuery API Clients using Application Default Credentials:

Java

Python

C#

PHP

Authenticating as an end user

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 client IDs, client secrets, and refresh tokens, see Using OAuth 2.0 to Access Google APIs.

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

Java App Engine

This sample uses the Google APIs Client Library for Java.

/*
 * BigQueryWebServerAuthDemo.java extends the AbstractAppEngineAuthorizationCodeServlet class available
 * in the Google OAuth Java library (https://github.com/google/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 (https://github.com/google/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();
  }

}
  

Java servlet

This sample uses the Google APIs Client Library for Java.

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 (https://github.com/google/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
 * (https://github.com/google/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 */

Python

This sample uses the Google APIs Client Library for Python.

Installed and desktop applications

If your application needs to access BigQuery as the end user from 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-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 redirects the user to a Google-hosted webpage to request access to the BigQuery API on their behalf. 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, see Using OAuth 2.0 to Access Google APIs.

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 Google Cloud Platform Console.

Java

This sample uses the Google APIs Client Library for Java.

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

}
  

Python

This sample uses the Google APIs Client Library for Python.

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()
  

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 Setting up OAuth 2.0 and select Web application as the application type. For more information on how this authorization flow works, view the Google APIs Client Library for JavaScript auth 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 Google Cloud Platform Console.

JavaScript

This sample uses the Google APIs Client Library for JavaScript.

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