Quickstart: Using Client Libraries

This page shows you how to get started with the Google BigQuery API in your favorite programming language using the Google Cloud Client Libraries.

Before you begin

  1. Sign in to your Google Account.

    If you don't already have one, sign up for a new account.

  2. Select or create a GCP project.

    Go to the Manage resources page

  3. Assurez-vous que la facturation est activée pour votre projet.

    En savoir plus sur l'activation de la facturation

  4. Enable the Google BigQuery API.

    Enable the API

  5. Set up authentication:

    1. Go to the Create service account key page in the GCP Console.

      Go to the Create Service Account Key page
    2. From the Service account drop-down list, select New service account.
    3. Enter a name into the Service account name field.

    4. From the Role drop-down list, select Project > Owner.

      Note: The Role field authorizes your service account to access resources. You can view and change this field later using GCP Console. If you are developing a production application, specify more granular permissions than Project > Owner. For more information, see granting roles to service accounts.
    5. Click Create. A JSON file that contains your key downloads to your computer.

  6. Set the environment variable GOOGLE_APPLICATION_CREDENTIALS to the file path of the JSON file that contains your service account key. This variable only applies to your current shell session, so if you open a new session, set the variable again.

Install the client library

C#

For more on setting up your C# development environment, refer to the C# Development Environment Setup Guide. 
Install-Package Google.Cloud.BigQuery.V2 -Pre

Go

go get -u cloud.google.com/go/bigquery

Java

For more on setting up your Java development environment, refer to the Java Development Environment Setup Guide. If you are using Maven, add this to your pom.xml file: 
<dependency>
  <groupId>com.google.cloud</groupId>
  <artifactId>google-cloud-bigquery</artifactId>
  <version>1.40.0</version>
</dependency>
If you are using Gradle, add this to your dependencies: 
compile 'com.google.cloud:google-cloud-bigquery:1.40.0'
If you are using SBT, add this to your dependencies: 
libraryDependencies += "com.google.cloud" % "google-cloud-bigquery" % "1.40.0"

Node.js

For more on setting up your Node.js development environment, refer to the Node.js Development Environment Setup Guide. 
npm install --save @google-cloud/bigquery

PHP

composer require google/cloud-bigquery

Python

For more on setting up your Python development environment, refer to the Python Development Environment Setup Guide. 
pip install --upgrade google-cloud-bigquery

Ruby

For more on setting up your Ruby development environment, refer to the Ruby Development Environment Setup Guide. 
gem install google-cloud-bigquery

Import the libraries

C#

For more information, see the BigQuery C# API reference documentation .

bigquery/api/BigquerySample/Program.cs 
using System;
using Google.Cloud.BigQuery.V2;

Go

For more information, see the BigQuery Go API reference documentation . 

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

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

Java

For more information, see the BigQuery Java API reference documentation . 

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FieldValueList;
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.TableResult;
import java.util.UUID;

Node.js

For more information, see the BigQuery Node.js API reference documentation . 

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

PHP

For more information, see the BigQuery PHP API reference documentation . 

use Google\Cloud\BigQuery\BigQueryClient;

Python

For more information, see the BigQuery Python API reference documentation . 

from google.cloud import bigquery

Ruby

For more information, see the BigQuery Ruby API reference documentation . 

require "google/cloud/bigquery"

Initialize a BigQuery Client

C#

Use the BigQueryClient.Create() function to create the BigQuery client.

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

ctx := context.Background()

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

Java

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

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

Node.js

Instantiate the BigQuery class to create the BigQuery client.

/**
 * TODO(developer): Uncomment the following lines before running the sample.
 */
// const projectId = "your-project-id";

// Creates a client
const bigquery = new BigQuery({
  projectId: projectId,
});

PHP

Instantiate the BigQueryClient class to create the BigQuery client.

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

Python

Instantiate the bigquery.Client class to create the BigQuery client.

client = bigquery.Client()

Ruby

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

# This uses Application Default Credentials to authenticate.
# @see https://cloud.google.com/bigquery/docs/authentication/getting-started
bigquery = Google::Cloud::Bigquery.new

Running queries

Query the Stack Overflow public dataset to find the most viewed questions tagged with google-bigquery.

SELECT
  CONCAT(
    'https://stackoverflow.com/questions/',
    CAST(id as STRING)) as url,
  view_count
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags like '%google-bigquery%'
ORDER BY view_count DESC
LIMIT 10

This query uses standard SQL syntax, which is described in the query reference guide. The client libraries default to standard SQL syntax. See Enabling standard SQL to change SQL dialects.

Running the query

Query using the authenticated BigQuery client.

C#

Define a query string and use the client.ExecuteQuery() function to submit the query and get the results.

string query = @"SELECT
    CONCAT(
        'https://stackoverflow.com/questions/',
        CAST(id as STRING)) as url, view_count
    FROM `bigquery-public-data.stackoverflow.posts_questions`
    WHERE tags like '%google-bigquery%'
    ORDER BY view_count DESC
    LIMIT 10";
var result = client.ExecuteQuery(query, parameters: null);

Go

Use the bigquery.Query() function to define a query and Query.Read() function to submit the query and get the results.

query := client.Query(
	`SELECT
		CONCAT(
			'https://stackoverflow.com/questions/',
			CAST(id as STRING)) as url,
		view_count
	FROM ` + "`bigquery-public-data.stackoverflow.posts_questions`" + `
	WHERE tags like '%google-bigquery%'
	ORDER BY view_count DESC
	LIMIT 10;`)
return query.Read(ctx)

Java

Define the query with a QueryJobConfiguration instance. Start the query job with the BigQuery.create() method.

QueryJobConfiguration queryConfig =
    QueryJobConfiguration.newBuilder(
      "SELECT "
          + "CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, "
          + "view_count "
          + "FROM `bigquery-public-data.stackoverflow.posts_questions` "
          + "WHERE tags like '%google-bigquery%' "
          + "ORDER BY favorite_count DESC LIMIT 10")
        // 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());
}

Node.js

Use the BigQuery.query() method to start the query.

// The SQL query to run
const sqlQuery = `SELECT
  CONCAT(
    'https://stackoverflow.com/questions/',
    CAST(id as STRING)) as url,
  view_count
  FROM \`bigquery-public-data.stackoverflow.posts_questions\`
  WHERE tags like '%google-bigquery%'
  ORDER BY view_count DESC
  LIMIT 10`;

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

Create a query configuration and use the BigQueryClient.startQuery() method to start the query.

$query = <<<ENDSQL
SELECT
  CONCAT(
    'https://stackoverflow.com/questions/',
    CAST(id as STRING)) as url,
  view_count
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags like '%google-bigquery%'
ORDER BY view_count DESC
LIMIT 10;
ENDSQL;
$queryJobConfig = $bigQuery->query($query);
$queryResults = $bigQuery->runQuery($queryJobConfig);

Python

Use the Client.query() method to start the query.

query_job = client.query("""
    SELECT
      CONCAT(
        'https://stackoverflow.com/questions/',
        CAST(id as STRING)) as url,
      view_count
    FROM `bigquery-public-data.stackoverflow.posts_questions`
    WHERE tags like '%google-bigquery%'
    ORDER BY view_count DESC
    LIMIT 10""")

results = query_job.result()  # Waits for job to complete.

Ruby

Use the Google::Cloud::Bigquery::Project.query function to start a query and wait for the results.

sql     = "SELECT " +
          "CONCAT('https://stackoverflow.com/questions/', " +
          "       CAST(id as STRING)) as url, view_count " +
          "FROM `bigquery-public-data.stackoverflow.posts_questions` " +
          "WHERE tags like '%google-bigquery%' " +
          "ORDER BY view_count DESC LIMIT 10"
results = bigquery.query sql

For more examples of running BigQuery queries, see:

Displaying the query result

Display the query results.

{% include "_shared/widgets/_sample_tab_section.html" with lang="csharp" sample_hide_preface="true" project="dotnet-docs-samples" file="bigquery/api/BigquerySample/Program.cs" region_tag=sample_id adjust_indentation="16" #}

Go

Use the RowIterator.Next() function to load each row into a struct pointer.

type StackOverflowRow struct {
	URL       string `bigquery:"url"`
	ViewCount int64  `bigquery:"view_count"`
}

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

		fmt.Fprintf(w, "url: %s views: %d\n", row.URL, row.ViewCount)
	}
}

Java

Iterate over the QueryResponse to get all the rows in the results. The iterator automatically handles pagination. Each FieldList exposes the columns by numeric index or column name.

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

TableResult result = queryJob.getQueryResults();

// Print all pages of the results.
for (FieldValueList row : result.iterateAll()) {
  String url = row.get("url").getStringValue();
  long viewCount = row.get("view_count").getLongValue();
  System.out.printf("url: %s views: %d%n", url, viewCount);
}

Node.js

Query results are returned as a list of rows, where each row is a dictionary.

console.log('Query Results:');
rows.forEach(function(row) {
  let url = row['url'];
  let viewCount = row['view_count'];
  console.log(`url: ${url}, ${viewCount} views`);
});

PHP

Call the Job.queryResults() method to wait for the query to finish. Each row in the query results is an associative array.

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

Python

Iterate over the RowIterator to get all the rows in the results. The iterator automatically handles pagination. Each Row exposes the columns by numeric index, column name, or as Python attributes.

for row in results:
    print("{} : {} views".format(row.url, row.view_count))

Ruby

The Google::Cloud::Bigquery::Data class exposes each row as a hash.

results.each do |row|
  puts "#{row[:url]}: #{row[:view_count]} views"
end

Learn more about working with data rows in BigQuery:

Complete source code

Here is the complete source code for the sample.

C#

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);
                string query = @"SELECT
                    CONCAT(
                        'https://stackoverflow.com/questions/',
                        CAST(id as STRING)) as url, view_count
                    FROM `bigquery-public-data.stackoverflow.posts_questions`
                    WHERE tags like '%google-bigquery%'
                    ORDER BY view_count DESC
                    LIMIT 10";
                var result = client.ExecuteQuery(query, parameters: null);
                Console.Write("\nQuery Results:\n------------\n");
                foreach (var row in result)
                {
                    Console.WriteLine($"{row["url"]}: {row["view_count"]} views");
                }
            }
            Console.WriteLine("\nPress any key...");
            Console.ReadKey();
        }
    }
}

Go

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

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


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
			CONCAT(
				'https://stackoverflow.com/questions/',
				CAST(id as STRING)) as url,
			view_count
		FROM ` + "`bigquery-public-data.stackoverflow.posts_questions`" + `
		WHERE tags like '%google-bigquery%'
		ORDER BY view_count DESC
		LIMIT 10;`)
	return query.Read(ctx)
}

type StackOverflowRow struct {
	URL       string `bigquery:"url"`
	ViewCount int64  `bigquery:"view_count"`
}

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

		fmt.Fprintf(w, "url: %s views: %d\n", row.URL, row.ViewCount)
	}
}

Java

import com.google.cloud.bigquery.BigQuery;
import com.google.cloud.bigquery.BigQueryOptions;
import com.google.cloud.bigquery.FieldValueList;
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.TableResult;
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 "
              + "CONCAT('https://stackoverflow.com/questions/', CAST(id as STRING)) as url, "
              + "view_count "
              + "FROM `bigquery-public-data.stackoverflow.posts_questions` "
              + "WHERE tags like '%google-bigquery%' "
              + "ORDER BY favorite_count DESC LIMIT 10")
            // 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);

    TableResult result = queryJob.getQueryResults();

    // Print all pages of the results.
    for (FieldValueList row : result.iterateAll()) {
      String url = row.get("url").getStringValue();
      long viewCount = row.get("view_count").getLongValue();
      System.out.printf("url: %s views: %d%n", url, viewCount);
    }
  }
}

Node.js

function printResult(rows) {
  console.log('Query Results:');
  rows.forEach(function(row) {
    let url = row['url'];
    let viewCount = row['view_count'];
    console.log(`url: ${url}, ${viewCount} views`);
  });
}

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

  /**
   * TODO(developer): Uncomment the following lines before running the sample.
   */
  // const projectId = "your-project-id";

  // Creates a client
  const bigquery = new BigQuery({
    projectId: projectId,
  });

  // The SQL query to run
  const sqlQuery = `SELECT
    CONCAT(
      'https://stackoverflow.com/questions/',
      CAST(id as STRING)) as url,
    view_count
    FROM \`bigquery-public-data.stackoverflow.posts_questions\`
    WHERE tags like '%google-bigquery%'
    ORDER BY view_count DESC
    LIMIT 10`;

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

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 stackoverflow.php YOUR_PROJECT_ID\n");
}

$projectId = $argv[1];

$bigQuery = new BigQueryClient([
    'projectId' => $projectId,
]);
$query = <<<ENDSQL
SELECT
  CONCAT(
    'https://stackoverflow.com/questions/',
    CAST(id as STRING)) as url,
  view_count
FROM `bigquery-public-data.stackoverflow.posts_questions`
WHERE tags like '%google-bigquery%'
ORDER BY view_count DESC
LIMIT 10;
ENDSQL;
$queryJobConfig = $bigQuery->query($query);
$queryResults = $bigQuery->runQuery($queryJobConfig);

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

Python

from google.cloud import bigquery


def query_stackoverflow():
    client = bigquery.Client()
    query_job = client.query("""
        SELECT
          CONCAT(
            'https://stackoverflow.com/questions/',
            CAST(id as STRING)) as url,
          view_count
        FROM `bigquery-public-data.stackoverflow.posts_questions`
        WHERE tags like '%google-bigquery%'
        ORDER BY view_count DESC
        LIMIT 10""")

    results = query_job.result()  # Waits for job to complete.

    for row in results:
        print("{} : {} views".format(row.url, row.view_count))


if __name__ == '__main__':
    query_stackoverflow()

Ruby

require "google/cloud/bigquery"

# This uses Application Default Credentials to authenticate.
# @see https://cloud.google.com/bigquery/docs/authentication/getting-started
bigquery = Google::Cloud::Bigquery.new

sql     = "SELECT " +
          "CONCAT('https://stackoverflow.com/questions/', " +
          "       CAST(id as STRING)) as url, view_count " +
          "FROM `bigquery-public-data.stackoverflow.posts_questions` " +
          "WHERE tags like '%google-bigquery%' " +
          "ORDER BY view_count DESC LIMIT 10"
results = bigquery.query sql

results.each do |row|
  puts "#{row[:url]}: #{row[:view_count]} views"
end

Congratulations! You've sent your first request to the BigQuery.

What's next

Cette page vous a-t-elle été utile ? Évaluez-la :

Envoyer des commentaires concernant…

Cette page
Commentaires sur la documentation
BigQuery
Commentaires sur le produit