Create A Simple Application With the API

This page provides a short exercise in building a simple command-line application (in C#, Go, Java, Node.js, PHP, Python, 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 C#, Go, Java, Node.js, PHP, Python, or Ruby.

    The BigQuery service provides a REST-based API that can be programmatically accessed using C#, Go, Java, Node.js, PHP, Python, or Ruby. In addition to a basic understand of how to develop C#, Go, Java, Node.js, PHP, Python, 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 Cloud Client Libraries.

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

Create an authorized BigQuery service object

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

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

Refer to BigQuery Client Libraries for how to install and create an authorized client.

Running queries

To run a query, insert a query job. Then, you can use the job ID to poll for the status of the query and retrieve the query results once it completes. For more information about different ways to query using BigQuery, see querying data.

Running the query

To run a 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 Shakespeare's works with the greatest number of distinct words. BigQuery uses SQL, which is described in our query reference guide.

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

var table = client.GetTable("bigquery-public-data", "samples", "shakespeare");

string query = $@"SELECT corpus AS title, COUNT(*) AS unique_words FROM `{table.FullyQualifiedId}` 
    GROUP BY title ORDER BY unique_words DESC LIMIT 42";
var result = client.ExecuteQuery(query);

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

// query returns a slice of the results of a query.
func query(proj string) (*bigquery.RowIterator, error) {
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, proj)
	if err != nil {
		return nil, err
	}

	query := client.Query(
		`SELECT
		 APPROX_TOP_COUNT(corpus, 10) as title,
		 COUNT(*) as unique_words
		 FROM ` + "`publicdata.samples.shakespeare`;")
	// Use standard SQL syntax for queries.
	// See: https://cloud.google.com/bigquery/sql-reference/
	query.QueryConfig.UseStandardSQL = true
	return query.Read(ctx)
}

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(
            "SELECT "
                + "APPROX_TOP_COUNT(corpus, 10) as title, "
                + "COUNT(*) as unique_words "
                + "FROM `publicdata.samples.shakespeare`;")
        // Use standard SQL syntax for queries.
        // See: https://cloud.google.com/bigquery/sql-reference/
        .setUseLegacySql(false)
        .build();

// Create a job ID so that we can safely retry.
JobId jobId = JobId.of(UUID.randomUUID().toString());
Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

// Wait for the query to complete.
queryJob = queryJob.waitFor();

// Check for errors
if (queryJob == null) {
  throw new RuntimeException("Job no longer exists");
} else if (queryJob.getStatus().getError() != null) {
  // You can also look at queryJob.getStatus().getExecutionErrors() for all
  // errors, not just the latest one.
  throw new RuntimeException(queryJob.getStatus().getError().toString());
}

// Get the results.
QueryResponse response = bigquery.getQueryResults(jobId);

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

const sqlQuery = `SELECT
  corpus, COUNT(*) as unique_words
FROM publicdata.samples.shakespeare
GROUP BY
  corpus
ORDER BY
  unique_words DESC LIMIT 10;`;

function queryShakespeare (projectId) {
  // Imports the Google Cloud client library
  const BigQuery = require('@google-cloud/bigquery');

  // The project ID to use, e.g. "your-project-id"
  // const projectId = "your-project-id";

  // Instantiates a client
  const bigquery = BigQuery({
    projectId: projectId
  });

  // Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
  const options = {
    query: sqlQuery,
    useLegacySql: false // Use standard SQL syntax for queries.
  };

  // Runs the query
  bigquery
    .query(options)
    .then((results) => {
      const rows = results[0];
      printResult(rows);
    })
    .catch((err) => {
      console.error('ERROR:', err);
    });
}

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

$query = 'SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words ' .
         'FROM [publicdata:samples.shakespeare]';
$options = ['useLegacySql' => true];
$queryResults = $bigQuery->runQuery($query, $options);

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

query_results = client.run_sync_query("""
    SELECT
        APPROX_TOP_COUNT(corpus, 10) as title,
        COUNT(*) as unique_words
    FROM `publicdata.samples.shakespeare`;""")

# Use standard SQL syntax for queries.
# See: https://cloud.google.com/bigquery/sql-reference/
query_results.use_legacy_sql = False

query_results.run()

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

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

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.

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

Console.Write("\nQuery Results:\n------------\n");
foreach (var row in result.GetRows())
{
    Console.WriteLine($"{row["title"]}: {row["unique_words"]}");
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

// printResults prints results from a query to the Shakespeare dataset.
func printResults(w io.Writer, iter *bigquery.RowIterator) error {
	for {
		var row []bigquery.Value
		err := iter.Next(&row)
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}

		fmt.Fprintln(w, "titles:")
		ts := row[0].([]bigquery.Value)
		for _, t := range ts {
			record := t.([]bigquery.Value)
			title := record[0].(string)
			cnt := record[1].(int64)
			fmt.Fprintf(w, "\t%s: %d\n", title, cnt)
		}

		words := row[1].(int64)
		fmt.Fprintf(w, "total unique words: %d\n", words)
	}
}

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

QueryResult result = response.getResult();

// Print all pages of the results.
while (result != null) {
  for (List<FieldValue> row : result.iterateAll()) {
    List<FieldValue> titles = row.get(0).getRepeatedValue();
    System.out.println("titles:");

    for (FieldValue titleValue : titles) {
      List<FieldValue> titleRecord = titleValue.getRecordValue();
      String title = titleRecord.get(0).getStringValue();
      long uniqueWords = titleRecord.get(1).getLongValue();
      System.out.printf("\t%s: %d\n", title, uniqueWords);
    }

    long uniqueWords = row.get(1).getLongValue();
    System.out.printf("total unique words: %d\n", uniqueWords);
  }

  result = result.getNextPage();
}

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

function printResult (rows) {
  console.log('Query Results:');
  rows.forEach(function (row) {
    let str = '';
    for (let key in row) {
      if (str) {
        str = `${str}\n`;
      }
      str = `${str}${key}: ${row[key]}`;
    }
    console.log(str);
  });
}

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

if ($queryResults->isComplete()) {
    $i = 0;
    $rows = $queryResults->rows();
    foreach ($rows as $row) {
        printf('--- Row %s ---' . PHP_EOL, ++$i);
        foreach ($row as $column => $value) {
            printf('%s: %s' . PHP_EOL, $column, $value);
        }
    }
    printf('Found %s row(s)' . PHP_EOL, $i);
} else {
    throw new Exception('The query failed to complete');
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

# Drain the query results by requesting a page at a time.
page_token = None

while True:
    rows, total_rows, page_token = query_results.fetch_data(
        max_results=10,
        page_token=page_token)

    for row in rows:
        print(row)

    if not page_token:
        break

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

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

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

C#

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

using System;
using Google.Cloud.BigQuery.V2;

namespace GoogleCloudSamples
{
    public class BigquerySample
    {
        const string usage = @"Usage:
BigquerySample <project_id>";

        private static void Main(string[] args)
        {
            string projectId = null;
            if (args.Length == 0)
            {
                Console.WriteLine(usage);
            }
            else
            {
                projectId = args[0];
                // By default, the Google.Cloud.BigQuery.V2 library client will authenticate 
                // using the service account file (created in the Google Developers 
                // Console) specified by the GOOGLE_APPLICATION_CREDENTIALS 
                // environment variable. If you are running on
                // a Google Compute Engine VM, authentication is completely 
                // automatic.
                var client = BigQueryClient.Create(projectId);
                var table = client.GetTable("bigquery-public-data", "samples", "shakespeare");

                string query = $@"SELECT corpus AS title, COUNT(*) AS unique_words FROM `{table.FullyQualifiedId}` 
                    GROUP BY title ORDER BY unique_words DESC LIMIT 42";
                var result = client.ExecuteQuery(query);
                Console.Write("\nQuery Results:\n------------\n");
                foreach (var row in result.GetRows())
                {
                    Console.WriteLine($"{row["title"]}: {row["unique_words"]}");
                }
            }
            Console.WriteLine("\nPress any key...");
            Console.ReadKey();
        }
    }
}

Go

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

// Copyright 2016 Google Inc. All rights reserved.
// Use of this source code is governed by the Apache 2.0
// license that can be found in the LICENSE file.

// Command simpleapp queries the Shakespeare sample dataset in Google BigQuery.
package main

import (
	"fmt"
	"io"
	"log"
	"os"

	"cloud.google.com/go/bigquery"
	"google.golang.org/api/iterator"

	"golang.org/x/net/context"
)

func main() {
	proj := os.Getenv("GCLOUD_PROJECT")
	if proj == "" {
		fmt.Println("GCLOUD_PROJECT environment variable must be set.")
		os.Exit(1)
	}

	rows, err := query(proj)
	if err != nil {
		log.Fatal(err)
	}
	if err := printResults(os.Stdout, rows); err != nil {
		log.Fatal(err)
	}
}

// query returns a slice of the results of a query.
func query(proj string) (*bigquery.RowIterator, error) {
	ctx := context.Background()

	client, err := bigquery.NewClient(ctx, proj)
	if err != nil {
		return nil, err
	}

	query := client.Query(
		`SELECT
		 APPROX_TOP_COUNT(corpus, 10) as title,
		 COUNT(*) as unique_words
		 FROM ` + "`publicdata.samples.shakespeare`;")
	// Use standard SQL syntax for queries.
	// See: https://cloud.google.com/bigquery/sql-reference/
	query.QueryConfig.UseStandardSQL = true
	return query.Read(ctx)
}

// printResults prints results from a query to the Shakespeare dataset.
func printResults(w io.Writer, iter *bigquery.RowIterator) error {
	for {
		var row []bigquery.Value
		err := iter.Next(&row)
		if err == iterator.Done {
			return nil
		}
		if err != nil {
			return err
		}

		fmt.Fprintln(w, "titles:")
		ts := row[0].([]bigquery.Value)
		for _, t := range ts {
			record := t.([]bigquery.Value)
			title := record[0].(string)
			cnt := record[1].(int64)
			fmt.Fprintf(w, "\t%s: %d\n", title, cnt)
		}

		words := row[1].(int64)
		fmt.Fprintf(w, "total unique words: %d\n", words)
	}
}

Java

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FieldValue;
import com.google.cloud.bigquery.Job;
import com.google.cloud.bigquery.JobId;
import com.google.cloud.bigquery.JobInfo;
import com.google.cloud.bigquery.QueryJobConfiguration;
import com.google.cloud.bigquery.QueryResponse;
import com.google.cloud.bigquery.QueryResult;

import java.util.List;
import java.util.UUID;

public class SimpleApp {
  public static void main(String... args) throws Exception {
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    QueryJobConfiguration queryConfig =
        QueryJobConfiguration.newBuilder(
                "SELECT "
                    + "APPROX_TOP_COUNT(corpus, 10) as title, "
                    + "COUNT(*) as unique_words "
                    + "FROM `publicdata.samples.shakespeare`;")
            // Use standard SQL syntax for queries.
            // See: https://cloud.google.com/bigquery/sql-reference/
            .setUseLegacySql(false)
            .build();

    // Create a job ID so that we can safely retry.
    JobId jobId = JobId.of(UUID.randomUUID().toString());
    Job queryJob = bigquery.create(JobInfo.newBuilder(queryConfig).setJobId(jobId).build());

    // Wait for the query to complete.
    queryJob = queryJob.waitFor();

    // Check for errors
    if (queryJob == null) {
      throw new RuntimeException("Job no longer exists");
    } else if (queryJob.getStatus().getError() != null) {
      // You can also look at queryJob.getStatus().getExecutionErrors() for all
      // errors, not just the latest one.
      throw new RuntimeException(queryJob.getStatus().getError().toString());
    }

    // Get the results.
    QueryResponse response = bigquery.getQueryResults(jobId);

    QueryResult result = response.getResult();

    // Print all pages of the results.
    while (result != null) {
      for (List<FieldValue> row : result.iterateAll()) {
        List<FieldValue> titles = row.get(0).getRepeatedValue();
        System.out.println("titles:");

        for (FieldValue titleValue : titles) {
          List<FieldValue> titleRecord = titleValue.getRecordValue();
          String title = titleRecord.get(0).getStringValue();
          long uniqueWords = titleRecord.get(1).getLongValue();
          System.out.printf("\t%s: %d\n", title, uniqueWords);
        }

        long uniqueWords = row.get(1).getLongValue();
        System.out.printf("total unique words: %d\n", uniqueWords);
      }

      result = result.getNextPage();
    }
  }
}

Node.js

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

function printResult (rows) {
  console.log('Query Results:');
  rows.forEach(function (row) {
    let str = '';
    for (let key in row) {
      if (str) {
        str = `${str}\n`;
      }
      str = `${str}${key}: ${row[key]}`;
    }
    console.log(str);
  });
}

const sqlQuery = `SELECT
  corpus, COUNT(*) as unique_words
FROM publicdata.samples.shakespeare
GROUP BY
  corpus
ORDER BY
  unique_words DESC LIMIT 10;`;

function queryShakespeare (projectId) {
  // Imports the Google Cloud client library
  const BigQuery = require('@google-cloud/bigquery');

  // The project ID to use, e.g. "your-project-id"
  // const projectId = "your-project-id";

  // Instantiates a client
  const bigquery = BigQuery({
    projectId: projectId
  });

  // Query options list: https://cloud.google.com/bigquery/docs/reference/v2/jobs/query
  const options = {
    query: sqlQuery,
    useLegacySql: false // Use standard SQL syntax for queries.
  };

  // Runs the query
  bigquery
    .query(options)
    .then((results) => {
      const rows = results[0];
      printResult(rows);
    })
    .catch((err) => {
      console.error('ERROR:', err);
    });
}

PHP

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

require __DIR__ . '/vendor/autoload.php';

use Google\Cloud\BigQuery\BigQueryClient;

// get the project ID as the first argument
if (2 != count($argv)) {
    die("Usage: php shakespeare.php YOUR_PROJECT_ID\n");
}

$projectId = $argv[1];

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$query = 'SELECT TOP(corpus, 10) as title, COUNT(*) as unique_words ' .
         'FROM [publicdata:samples.shakespeare]';
$options = ['useLegacySql' => true];
$queryResults = $bigQuery->runQuery($query, $options);

if ($queryResults->isComplete()) {
    $i = 0;
    $rows = $queryResults->rows();
    foreach ($rows as $row) {
        printf('--- Row %s ---' . PHP_EOL, ++$i);
        foreach ($row as $column => $value) {
            printf('%s: %s' . PHP_EOL, $column, $value);
        }
    }
    printf('Found %s row(s)' . PHP_EOL, $i);
} else {
    throw new Exception('The query failed to complete');
}

Python

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

from google.cloud import bigquery


def query_shakespeare():
    client = bigquery.Client()
    query_results = client.run_sync_query("""
        SELECT
            APPROX_TOP_COUNT(corpus, 10) as title,
            COUNT(*) as unique_words
        FROM `publicdata.samples.shakespeare`;""")

    # Use standard SQL syntax for queries.
    # See: https://cloud.google.com/bigquery/sql-reference/
    query_results.use_legacy_sql = False

    query_results.run()

    # Drain the query results by requesting a page at a time.
    page_token = None

    while True:
        rows, total_rows, page_token = query_results.fetch_data(
            max_results=10,
            page_token=page_token)

        for row in rows:
            print(row)

        if not page_token:
            break


if __name__ == '__main__':
    query_shakespeare()

Ruby

For more on installing and creating a BigQuery client, refer to BigQuery Client Libraries.

# 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 "google/cloud/bigquery"

    bigquery = Google::Cloud::Bigquery.new project: project_id

    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

Send feedback about...

BigQuery Documentation