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. In addition to a basic understanding of how to develop 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 a BigQuery service object

This sample uses default credentials to authenticate and make authorized requests to BigQuery.

C#

Use the BigQueryClient.Create() function to create the BigQuery service object.

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

Go

Use the bigquery.NewClient() function to create the BigQuery service object.

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

Use the BigQueryOptions.getDefaultInstance() function to use the default authentication options. Use the BigQueryOptions.getService() function to create the BigQuery service object.

BigQuery bigquery = BigQueryOptions.getDefaultInstance().getService();

Node.js

Instantiate the @google-cloud/bigquery class to create the BigQuery service object.

// 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";

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

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

Instantiate the BigQueryClient class to create the BigQuery service object.

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);

Python

Instantiate the bigquery.Client class to create the BigQuery service object.

import uuid

from google.cloud import bigquery


def query_shakespeare():
    client = bigquery.Client()

Ruby

Use the Google::Cloud::Bigquery.new function to create the BigQuery service object.

require "google/cloud/bigquery"

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

Refer to the authentication guide if application default credentials do not fit your applications needs.

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, parameters: null);

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 `bigquery-public-data.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.

// 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";

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

// 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_job = client.run_async_query(str(uuid.uuid4()), """
    #standardSQL
    SELECT corpus AS title, COUNT(*) AS unique_words
    FROM `publicdata.samples.shakespeare`
    GROUP BY title
    ORDER BY unique_words DESC
    LIMIT 10""")

query_job.begin()
query_job.result()  # Wait for job to complete.

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 client libraries expose as a native object. Query results are written to a BigQuery table.

The application parses the query response, reads the results from the destination table, 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)
{
    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.

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.

destination_table = query_job.destination
destination_table.reload()
for row in destination_table.fetch_data():
    print(row)

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, parameters: null);
                Console.Write("\nQuery Results:\n------------\n");
                foreach (var row in result)
                {
                    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("GOOGLE_CLOUD_PROJECT")
	if proj == "" {
		fmt.Println("GOOGLE_CLOUD_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 `bigquery-public-data.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);
  });
}

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

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

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

import uuid

from google.cloud import bigquery


def query_shakespeare():
    client = bigquery.Client()
    query_job = client.run_async_query(str(uuid.uuid4()), """
        #standardSQL
        SELECT corpus AS title, COUNT(*) AS unique_words
        FROM `publicdata.samples.shakespeare`
        GROUP BY title
        ORDER BY unique_words DESC
        LIMIT 10""")

    query_job.begin()
    query_job.result()  # Wait for job to complete.

    destination_table = query_job.destination
    destination_table.reload()
    for row in destination_table.fetch_data():
        print(row)


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

Monitor your resources on the go

Get the Google Cloud Console app to help you manage your projects.

Send feedback about...