Create A Simple Application With the API

This page provides a short exercise in building a simple command-line application (in Java, Python, C#, PHP, or Ruby) with the Google BigQuery API. This simple application will run a query on one of the available Sample Datasets and display the result.

Before you begin

  1. An understanding of basic BigQuery concepts and terminology.

    See what is BigQuery for a list of basic terms and concepts to become familiar with, such as jobs and projects.

  2. Ability to write and run a simple application in Java, Python, C#, PHP, or Ruby.

    The BigQuery service provides a REST-based API that can be programmatically accessed using Java, Python, C#, PHP, or Ruby. In addition to a basic understand of how to develop Java, Python, C#, PHP, or Ruby applications, you should also be able to download and install additional libraries before attempting this tutorial.

  3. A Google Cloud Platform Console project with the BigQuery API enabled.

    Applications that use BigQuery must be associated with a Google Cloud Platform Console project with the BigQuery API enabled. This project provides authentication credentials you use in your application to identify it to Google and authorize its use of the BigQuery API.

    Follow these instructions to create a project, enable the BigQuery API for it, and setup your development environment with authentication credentials. Note down the project's ID, which you'll provide to the application later on.

  4. Installed Google API Client Libraries.

    Our samples use the Google APIs Client libraries to make calls to the BigQuery API. Download and install the appropriate library from the client libraries page.

    If you are developing with Java, Maven is a useful way to manage and organize dependencies. The Google APIs Client Libraries for Java are available in the central Maven repository.

Create an authorized BigQuery service object

In order to make authenticated requests to Google Cloud Platform APIs using the Google APIs Client libraries, you must:

  • Fetch the credential to use for requests.
  • Create a service object that uses that credential.

You can then make API calls by calling methods on the service object.

For this example, you'll fetch Application Default Credentials from the environment, and pass it as an argument to create the service object. For information about other types of credentials you can use, see Authenticating requests to the Google BigQuery API.

Java

This sample uses the Google APIs Client Library for Java.

/**
 * Creates an authorized Bigquery client service using Application Default Credentials.
 *
 * @return an authorized Bigquery client
 * @throws IOException if there's an error getting the default credentials.
 */
public static Bigquery createAuthorizedClient() throws IOException {
  // Create the credential
  HttpTransport transport = new NetHttpTransport();
  JsonFactory jsonFactory = new JacksonFactory();
  GoogleCredential credential = GoogleCredential.getApplicationDefault(transport, jsonFactory);

  // Depending on the environment that provides the default credentials (e.g. Compute Engine, App
  // Engine), the credentials may require us to specify the scopes we need explicitly.
  // Check for this case, and inject the Bigquery scope if required.
  if (credential.createScopedRequired()) {
    credential = credential.createScoped(BigqueryScopes.all());
  }

  return new Bigquery.Builder(transport, jsonFactory, credential)
      .setApplicationName("Bigquery Samples")
      .build();
}

Python

This sample uses the Google APIs Client Library for Python.

# Grab the application's default credentials from the environment.
credentials = GoogleCredentials.get_application_default()
# Construct the service object for interacting with the BigQuery API.
bigquery_service = build('bigquery', 'v2', credentials=credentials)

C#

This sample uses the Google APIs Client Library for .NET.

/// <summary>
/// Creates an authorized Bigquery client service using Application
/// Default Credentials.
/// </summary>
/// <returns>an authorized Bigquery client</returns>
public BigqueryService CreateAuthorizedClient()
{
    GoogleCredential credential =
        GoogleCredential.GetApplicationDefaultAsync().Result;
    // Inject the Bigquery scope if required.
    if (credential.IsCreateScopedRequired)
    {
        credential = credential.CreateScoped(new[]
        {
            BigqueryService.Scope.Bigquery
        });
    }
    return new BigqueryService(new BaseClientService.Initializer()
    {
        HttpClientInitializer = credential,
        ApplicationName = "DotNet Bigquery Samples",
    });
}

PHP

This sample uses the Google APIs Client Library for PHP.

$client = new Google_Client();
$client->useApplicationDefaultCredentials();
$client->addScope(Google_Service_Bigquery::BIGQUERY);

$bigquery = new Google_Service_Bigquery($client);

Ruby

This sample uses the Google APIs Client Library for Ruby.

require "gcloud"

gcloud = Gcloud.new project_id
bigquery = gcloud.bigquery

Node.js

This sample uses the gcloud-node Client Library for Node.js.

// By default, the client will authenticate using the service account file
// specified by the GOOGLE_APPLICATION_CREDENTIALS environment variable and use
// the project specified by the GCLOUD_PROJECT environment variable. See
// https://googlecloudplatform.github.io/gcloud-node/#/docs/google-cloud/latest/guides/authentication
var BigQuery = require('@google-cloud/bigquery');

// Instantiate a bigquery client
var bigquery = BigQuery();

Running queries

BigQuery provides two API methods for running queries. The synchronous query method involves a single API call, and waits to provide a response until the query is complete (unless you provide it with an optional timeout value). The asynchronous query method "inserts" a query job, and immediately returns an ID for that job. Then, you can use this job ID to poll for the status of the query, and retrieve the query result if complete. This example uses the synchronous query method. For more information about different ways to query using BigQuery, see querying data.

Running the query

To run a synchronous query, the application makes an API call that passes the query itself (as a string), along with the project number that the query will be run under for billing and quota purposes. The query in the example below finds the 10 of Shakespeare's works with the greatest number of distinct words. BigQuery uses a SQL-like syntax, which is described in our query reference guide.

Java

This sample uses the Google APIs Client Library for Java.

/**
 * Executes the given query synchronously.
 *
 * @param querySql the query to execute.
 * @param bigquery the Bigquery service object.
 * @param projectId the id of the project under which to run the query.
 * @return a list of the results of the query.
 * @throws IOException if there's an error communicating with the API.
 */
private static List<TableRow> executeQuery(String querySql, Bigquery bigquery, String projectId)
    throws IOException {
  QueryResponse query =
      bigquery.jobs().query(projectId, new QueryRequest().setQuery(querySql)).execute();

  // Execute it
  GetQueryResultsResponse queryResult =
      bigquery
          .jobs()
          .getQueryResults(
              query.getJobReference().getProjectId(), query.getJobReference().getJobId())
          .execute();

  return queryResult.getRows();
}

Python

This sample uses the Google APIs Client Library for Python.

query_request = bigquery_service.jobs()
query_data = {
    'query': (
        'SELECT TOP(corpus, 10) as title, '
        'COUNT(*) as unique_words '
        'FROM [publicdata:samples.shakespeare];')
}

query_response = query_request.query(
    projectId=project_id,
    body=query_data).execute()

C#

This sample uses the Google APIs Client Library for .NET.

/// <summary>
/// Executes the given query synchronously.
/// </summary>
/// <param name="querySql">the query to execute.</param>
/// <param name="bigquery">the BigquerService object.</param>
/// <param name="projectId">the id of the project under which to run the
/// query.</param>
/// <returns>a list of the results of the query.</returns>
public IList<TableRow> ExecuteQuery(string querySql,
    BigqueryService bigquery, string projectId)
{
    var request = new Google.Apis.Bigquery.v2.JobsResource.QueryRequest(
        bigquery, new Google.Apis.Bigquery.v2.Data.QueryRequest()
        {
            Query = querySql,
        }, projectId);
    var query = request.Execute();
    GetQueryResultsResponse queryResult = bigquery.Jobs.GetQueryResults(
        projectId, query.JobReference.JobId).Execute();
    return queryResult.Rows;
}

PHP

This sample uses the Google APIs Client Library for PHP.

// Pack a BigQuery request.
$request = new Google_Service_Bigquery_QueryRequest();
$request->setQuery('SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words ' .
    'FROM [publicdata:samples.shakespeare]');
$response = $bigquery->jobs->query($projectId, $request);
$rows = $response->getRows();

Ruby

This sample uses the Google APIs Client Library for Ruby.

sql = "SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words " +
      "FROM [publicdata:samples.shakespeare]"
results = bigquery.query sql

Node.js

This sample uses the gcloud-node Client Library for Node.js.

/**
 * Run an example query.
 *
 * @param {Function} callback Callback function.
 */
function queryExample (callback) {
  var query = 'SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words\n' +
    'FROM [publicdata:samples.shakespeare];';

  bigquery.query(query, function (err, rows) {
    if (err) {
      return callback(err);
    }

    printExample(rows);
    callback(null, rows);
  });
}

Displaying the query result

After the query has completed, the API returns the result set as a JSON object, which the SDK exposes as a native object. In addition to the actual query results, the JSON response contains metadata about the query job, including a unique job ID and the schema of the result set. The application parses the query response and displays the resulting values.

Java

This sample uses the Google APIs Client Library for Java.

/**
 * Prints the results to standard out.
 *
 * @param rows the rows to print.
 */
private static void printResults(List<TableRow> rows) {
  System.out.print("\nQuery Results:\n------------\n");
  for (TableRow row : rows) {
    for (TableCell field : row.getF()) {
      System.out.printf("%-50s", field.getV());
    }
    System.out.println();
  }
}

Python

This sample uses the Google APIs Client Library for Python.

print('Query Results:')
for row in query_response['rows']:
    print('\t'.join(field['v'] for field in row['f']))

C#

This sample uses the Google APIs Client Library for .NET.

/// <summary>Prints the results to standard out.</summary>
public void PrintResults(IList<TableRow> rows)
{
    Console.Write("\nQuery Results:\n------------\n");
    foreach (TableRow row in rows)
    {
        foreach (TableCell field in row.F)
        {
            Console.Write(String.Format("{0,-50}", field.V));
        }
        Console.WriteLine();
    }
}

PHP

This sample uses the Google APIs Client Library for PHP.

// Print the results to stdout in a human-readable way.
echo "\nQuery Results:\n------------\n";
foreach ($rows as $row) {
    foreach ($row['f'] as $field) {
        printf('%-30s', $field['v']);
    }
    echo "\n";
}

Ruby

This sample uses the Google APIs Client Library for Ruby.

results.each do |row|
  puts "#{row['title']}: #{row['unique_words']}"
end

Node.js

This sample uses the gcloud-node Client Library for Node.js.

function printExample (rows) {
  console.log('Query Results:');
  rows.forEach(function (row) {
    var str = '';
    for (var key in row) {
      if (str) {
        str += '\t';
      }
      str += key + ': ' + row[key];
    }
    console.log(str);
  });
}

Next steps

This tutorial covers only the most basic steps necessary to make calls to the BigQuery API from a command-line application. The BigQuery API also provides methods for running asynchronous queries, creating tables and datasets, listing projects, and more.

Complete source code

Here is the complete source code for the examples in this quick start guide.

Java

This sample uses the Google APIs Client Library for Java.

import com.google.api.client.googleapis.auth.oauth2.GoogleCredential;
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.services.bigquery.Bigquery;
import com.google.api.services.bigquery.BigqueryScopes;
import com.google.api.services.bigquery.model.GetQueryResultsResponse;
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.TableRow;

import java.io.IOException;
import java.util.List;
import java.util.Scanner;

/**
 * Example of authorizing with Bigquery and reading from a public dataset.
 *
 * Specifically, this queries the shakespeare dataset to fetch the 10 of Shakespeare's works with
 * the greatest number of distinct words.
 */
public class GettingStarted {
  /**
   * Creates an authorized Bigquery client service using Application Default Credentials.
   *
   * @return an authorized Bigquery client
   * @throws IOException if there's an error getting the default credentials.
   */
  public static Bigquery createAuthorizedClient() throws IOException {
    // Create the credential
    HttpTransport transport = new NetHttpTransport();
    JsonFactory jsonFactory = new JacksonFactory();
    GoogleCredential credential = GoogleCredential.getApplicationDefault(transport, jsonFactory);

    // Depending on the environment that provides the default credentials (e.g. Compute Engine, App
    // Engine), the credentials may require us to specify the scopes we need explicitly.
    // Check for this case, and inject the Bigquery scope if required.
    if (credential.createScopedRequired()) {
      credential = credential.createScoped(BigqueryScopes.all());
    }

    return new Bigquery.Builder(transport, jsonFactory, credential)
        .setApplicationName("Bigquery Samples")
        .build();
  }

  /**
   * Executes the given query synchronously.
   *
   * @param querySql the query to execute.
   * @param bigquery the Bigquery service object.
   * @param projectId the id of the project under which to run the query.
   * @return a list of the results of the query.
   * @throws IOException if there's an error communicating with the API.
   */
  private static List<TableRow> executeQuery(String querySql, Bigquery bigquery, String projectId)
      throws IOException {
    QueryResponse query =
        bigquery.jobs().query(projectId, new QueryRequest().setQuery(querySql)).execute();

    // Execute it
    GetQueryResultsResponse queryResult =
        bigquery
            .jobs()
            .getQueryResults(
                query.getJobReference().getProjectId(), query.getJobReference().getJobId())
            .execute();

    return queryResult.getRows();
  }

  /**
   * Prints the results to standard out.
   *
   * @param rows the rows to print.
   */
  private static void printResults(List<TableRow> rows) {
    System.out.print("\nQuery Results:\n------------\n");
    for (TableRow row : rows) {
      for (TableCell field : row.getF()) {
        System.out.printf("%-50s", field.getV());
      }
      System.out.println();
    }
  }

  /**
   * Exercises the methods defined in this class.
   *
   * In particular, it creates an authorized Bigquery service object using Application Default
   * Credentials, then executes a query against the public Shakespeare dataset and prints out the
   * results.
   *
   * @param args the first argument, if it exists, should be the id of the project to run the test
   *     under. If no arguments are given, it will prompt for it.
   * @throws IOException if there's an error communicating with the API.
   */
  public static void main(String[] args) throws IOException {
    Scanner sc;
    if (args.length == 0) {
      // Prompt the user to enter the id of the project to run the queries under
      System.out.print("Enter the project ID: ");
      sc = new Scanner(System.in);
    } else {
      sc = new Scanner(args[0]);
    }
    String projectId = sc.nextLine();

    // Create a new Bigquery client authorized via Application Default Credentials.
    Bigquery bigquery = createAuthorizedClient();

    List<TableRow> rows =
        executeQuery(
            "SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words "
                + "FROM [publicdata:samples.shakespeare]",
            bigquery,
            projectId);

    printResults(rows);
  }
}

Python

This sample uses the Google APIs Client Library for Python.

import argparse

from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from oauth2client.client import GoogleCredentials


def main(project_id):
    # Grab the application's default credentials from the environment.
    credentials = GoogleCredentials.get_application_default()
    # Construct the service object for interacting with the BigQuery API.
    bigquery_service = build('bigquery', 'v2', credentials=credentials)

    try:
        query_request = bigquery_service.jobs()
        query_data = {
            'query': (
                'SELECT TOP(corpus, 10) as title, '
                'COUNT(*) as unique_words '
                'FROM [publicdata:samples.shakespeare];')
        }

        query_response = query_request.query(
            projectId=project_id,
            body=query_data).execute()

        print('Query Results:')
        for row in query_response['rows']:
            print('\t'.join(field['v'] for field in row['f']))

    except HttpError as err:
        print('Error: {}'.format(err.content))
        raise err


if __name__ == '__main__':
    parser = argparse.ArgumentParser(
        description=__doc__,
        formatter_class=argparse.RawDescriptionHelpFormatter)
    parser.add_argument('project_id', help='Your Google Cloud Project ID.')

    args = parser.parse_args()

    main(args.project_id)

C#

This sample uses the Google APIs Client Library for .NET.

using Google.Apis.Auth.OAuth2;
using Google.Apis.Bigquery.v2;
using Google.Apis.Bigquery.v2.Data;
using Google.Apis.Services;
using System;
using System.Collections.Generic;
using System.Threading.Tasks;

namespace GoogleCloudSamples
{
    public class BigquerySample
    {
        const string usage = @"Usage:
BigquerySample <project_id>
";
        /// <summary>
        /// Creates an authorized Bigquery client service using Application
        /// Default Credentials.
        /// </summary>
        /// <returns>an authorized Bigquery client</returns>
        public BigqueryService CreateAuthorizedClient()
        {
            GoogleCredential credential =
                GoogleCredential.GetApplicationDefaultAsync().Result;
            // Inject the Bigquery scope if required.
            if (credential.IsCreateScopedRequired)
            {
                credential = credential.CreateScoped(new[]
                {
                    BigqueryService.Scope.Bigquery
                });
            }
            return new BigqueryService(new BaseClientService.Initializer()
            {
                HttpClientInitializer = credential,
                ApplicationName = "DotNet Bigquery Samples",
            });
        }

        /// <summary>
        /// Executes the given query synchronously.
        /// </summary>
        /// <param name="querySql">the query to execute.</param>
        /// <param name="bigquery">the BigquerService object.</param>
        /// <param name="projectId">the id of the project under which to run the
        /// query.</param>
        /// <returns>a list of the results of the query.</returns>
        public IList<TableRow> ExecuteQuery(string querySql,
            BigqueryService bigquery, string projectId)
        {
            var request = new Google.Apis.Bigquery.v2.JobsResource.QueryRequest(
                bigquery, new Google.Apis.Bigquery.v2.Data.QueryRequest()
                {
                    Query = querySql,
                }, projectId);
            var query = request.Execute();
            GetQueryResultsResponse queryResult = bigquery.Jobs.GetQueryResults(
                projectId, query.JobReference.JobId).Execute();
            return queryResult.Rows;
        }

        /// <summary>Prints the results to standard out.</summary>
        public void PrintResults(IList<TableRow> rows)
        {
            Console.Write("\nQuery Results:\n------------\n");
            foreach (TableRow row in rows)
            {
                foreach (TableCell field in row.F)
                {
                    Console.Write(String.Format("{0,-50}", field.V));
                }
                Console.WriteLine();
            }
        }

        private static void Main(string[] args)
        {
            BigquerySample sample = new BigquerySample();
            string projectId = null;
            if (args.Length == 0)
            {
                Console.WriteLine(usage);
            }
            else
            {
                projectId = args[0];
                // Create a new Bigquery client authorized via Application Default 
                // Credentials.
                BigqueryService bigquery = sample.CreateAuthorizedClient();

                IList<TableRow> rows = sample.ExecuteQuery(
                    "SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words " +
                    "FROM [publicdata:samples.shakespeare]", bigquery, projectId);

                sample.PrintResults(rows);
            }
            Console.WriteLine("\nPress any key...");
            Console.ReadKey();
        }
    }
}

PHP

This sample uses the Google APIs Client Library for PHP.

$client = new Google_Client();
$client->useApplicationDefaultCredentials();
$client->addScope(Google_Service_Bigquery::BIGQUERY);

$bigquery = new Google_Service_Bigquery($client);

$projectId = '';
if ($projectId) {
    // The programmer already set the projectId above.
} elseif ($argc > 1) {
    $projectId = $argv[1];
} else {
    echo 'Enter the project ID: ';
    $projectId = trim(fgets(STDIN));
}

// Pack a BigQuery request.
$request = new Google_Service_Bigquery_QueryRequest();
$request->setQuery('SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words ' .
    'FROM [publicdata:samples.shakespeare]');
$response = $bigquery->jobs->query($projectId, $request);
$rows = $response->getRows();

// Print the results to stdout in a human-readable way.
echo "\nQuery Results:\n------------\n";
foreach ($rows as $row) {
    foreach ($row['f'] as $field) {
        printf('%-30s', $field['v']);
    }
    echo "\n";
}

Ruby

This sample uses the Google APIs Client Library for Ruby.

# A short sample demonstrating making a BigQuery request
# This uses Application Default Credentials to authenticate.
# @see https://cloud.google.com/bigquery/bigquery-api-quickstart
class Shakespeare
  def unique_words project_id
    require "gcloud"

    gcloud = Gcloud.new project_id
    bigquery = gcloud.bigquery

    sql = "SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words " +
          "FROM [publicdata:samples.shakespeare]"
    results = bigquery.query sql

    results.each do |row|
      puts "#{row['title']}: #{row['unique_words']}"
    end
  end
end

if __FILE__ == $PROGRAM_NAME
  project_id = ARGV.shift
  Shakespeare.new.unique_words project_id
end

Node.js

This sample uses the gcloud-node Client Library for Node.js.

'use strict';

// By default, the client will authenticate using the service account file
// specified by the GOOGLE_APPLICATION_CREDENTIALS environment variable and use
// the project specified by the GCLOUD_PROJECT environment variable. See
// https://googlecloudplatform.github.io/gcloud-node/#/docs/google-cloud/latest/guides/authentication
var BigQuery = require('@google-cloud/bigquery');

// Instantiate a bigquery client
var bigquery = BigQuery();

function printExample (rows) {
  console.log('Query Results:');
  rows.forEach(function (row) {
    var str = '';
    for (var key in row) {
      if (str) {
        str += '\t';
      }
      str += key + ': ' + row[key];
    }
    console.log(str);
  });
}

/**
 * Run an example query.
 *
 * @param {Function} callback Callback function.
 */
function queryExample (callback) {
  var query = 'SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words\n' +
    'FROM [publicdata:samples.shakespeare];';

  bigquery.query(query, function (err, rows) {
    if (err) {
      return callback(err);
    }

    printExample(rows);
    callback(null, rows);
  });
}

Send feedback about...

BigQuery Documentation