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, 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, or Ruby.

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

    If you are developing with Java, Maven is a useful way to manage and organize dependencies. The Google Cloud 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 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

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.

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. 

QueryRequest queryRequest =
    QueryRequest
        .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();
QueryResponse response = bigquery.query(queryRequest);

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 () {
  // Instantiates a client
  const bigquery = BigQuery();

  // 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
  return bigquery.query(options)
    .then((results) => {
      const rows = results[0];
      printResult(rows);
      return rows;
    });
}

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

while (result != null) {
  Iterator<List<FieldValue>> iter = result.iterateAll();

  while (iter.hasNext()) {
    List<FieldValue> row = iter.next();
    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 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.

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.QueryRequest;
import com.google.cloud.bigquery.QueryResponse;
import com.google.cloud.bigquery.QueryResult;

import java.util.Iterator;
import java.util.List;

public class SimpleApp {
  public static void main(String... args) throws Exception {
    BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();
    QueryRequest queryRequest =
        QueryRequest
            .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();
    QueryResponse response = bigquery.query(queryRequest);

    QueryResult result = response.getResult();

    while (result != null) {
      Iterator<List<FieldValue>> iter = result.iterateAll();

      while (iter.hasNext()) {
        List<FieldValue> row = iter.next();
        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. 

const BigQuery = require('@google-cloud/bigquery');

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 () {
  // Instantiates a client
  const bigquery = BigQuery();

  // 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
  return bigquery.query(options)
    .then((results) => {
      const rows = results[0];
      printResult(rows);
      return rows;
    });
}

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

This page
Documentation feedback
BigQuery Documentation
Product feedback