Constructor

BigQuery

new BigQuery(options)

In the following examples from this page and the other modules (Dataset, Table, etc.), we are going to be using a dataset from data.gov of higher education institutions.

We will create a table with the correct schema, import the public CSV file into that table, and query it for data.

Parameter

options

ClientConfig

Configuration options.

See also

What is BigQuery?

Examples

Install the client library with npm:

npm install --save @google-cloud/bigquery

Import the client library

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

Create a client that uses Application Default Credentials (ADC):

const bigquery = new BigQuery();

Create a client with explicit credentials:

const bigquery = new BigQuery({
  projectId: 'your-project-id',
  keyFilename: '/path/to/keyfile.json'
});

Full quickstart example:

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

// Your Google Cloud Platform project ID
const projectId = 'YOUR_PROJECT_ID';

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

// The name for the new dataset
const datasetName = 'my_new_dataset';

// Creates the new dataset
bigquery
  .createDataset(datasetName)
  .then(results => {
    const dataset = results[0];

    console.log(`Dataset ${dataset.id} created.`);
  })
  .catch(err => {
    console.error('ERROR:', err);
  });

Properties

Dataset

static

constructor

Dataset class.

See also
Dataset

Job

static

constructor

Job class.

See also
Job

Table

static

constructor

Table class.

See also
Table

createQueryStream

Run a query scoped to your project as a readable object stream.

Parameter

query

object

Configuration object. See Query for a complete list of options.

Returns

stream 

Example

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

const query = 'SELECT url FROM `publicdata:samples.github_nested` LIMIT 100';

bigquery.createQueryStream(query)
  .on('error', console.error)
  .on('data', function(row) {
    // row is a result from your query.
  })
  .on('end', function() {
    // All rows retrieved.
  });

//-
// If you anticipate many results, you can end a stream early to prevent
// unnecessary processing and API requests.
//-
bigquery.createQueryStream(query)
  .on('data', function(row) {
    this.end();
  });

getDatasetsStream

List all or some of the Dataset objects in your project as a readable object stream.

Parameter

options

Optional

object

Configuration object. See BigQuery#getDatasets for a complete list of options.

Returns

stream 

Example

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

bigquery.getDatasetsStream()
  .on('error', console.error)
  .on('data', function(dataset) {
    // dataset is a Dataset object.
  })
  .on('end', function() {
    // All datasets retrieved.
  });

//-
// If you anticipate many results, you can end a stream early to prevent
// unnecessary processing and API requests.
//-
bigquery.getDatasetsStream()
  .on('data', function(dataset) {
    this.end();
  });

getJobsStream

List all or some of the Job objects in your project as a readable object stream.

Parameter

options

Optional

object

Configuration object. See BigQuery#getJobs for a complete list of options.

Returns

stream 

Example

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

bigquery.getJobsStream()
  .on('error', console.error)
  .on('data', function(job) {
    // job is a Job object.
  })
  .on('end', function() {
    // All jobs retrieved.
  });

//-
// If you anticipate many results, you can end a stream early to prevent
// unnecessary processing and API requests.
//-
bigquery.getJobsStream()
  .on('data', function(job) {
    this.end();
  });

Methods

date

static

date(value)

Parameter

value

(object or string)

The date. If a string, this should be in the format the API describes: YYYY-[M]M-[D]D. Otherwise, provide an object.

Example

const BigQuery = require('@google-cloud/bigquery');
const date = BigQuery.date('2017-01-01');

//-
// Alternatively, provide an object.
//-
const date2 = BigQuery.date({
  year: 2017,
  month: 1,
  day: 1
});

datetime

static

datetime(value)

A DATETIME data type represents a point in time. Unlike a TIMESTAMP, this does not refer to an absolute instance in time. Instead, it is the civil time, or the time that a user would see on a watch or calendar.

Parameter

value

(object or string)

The time. If a string, this should be in the format the API describes: YYYY-[M]M-[D]D[ [H]H:[M]M:[S]S[.DDDDDD]]. Otherwise, provide an object.

Example

const BigQuery = require('@google-cloud/bigquery');
const datetime = BigQuery.datetime('2017-01-01 13:00:00');

//-
// Alternatively, provide an object.
//-
const datetime = BigQuery.datetime({
  year: 2017,
  month: 1,
  day: 1,
  hours: 14,
  minutes: 0,
  seconds: 0
});

time

static

time(value)

A TIME data type represents a time, independent of a specific date.

Parameter

value

(object or string)

The time. If a string, this should be in the format the API describes: [H]H:[M]M:[S]S[.DDDDDD]. Otherwise, provide an object.

Example

const BigQuery = require('@google-cloud/bigquery');
const time = BigQuery.time('14:00:00'); // 2:00 PM

//-
// Alternatively, provide an object.
//-
const time = BigQuery.time({
  hours: 14,
  minutes: 0,
  seconds: 0
});

timestamp

static

timestamp(value)

A timestamp represents an absolute point in time, independent of any time zone or convention such as Daylight Savings Time.

Parameter

value

date

The time.

Example

const BigQuery = require('@google-cloud/bigquery');
const timestamp = BigQuery.timestamp(new Date());

createDataset

createDataset(id, options, callback) returns Promise

Create a dataset.

Parameter

id

string

ID of the dataset to create.

options

Optional

object

See a Dataset resource.

callback

Optional

function()

The callback function.

See also

Datasets: insert API Documentation

Returns

Promise 

Example

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

bigquery.createDataset('my-dataset', function(err, dataset, apiResponse) {});

//-
// If the callback is omitted, we'll return a Promise.
//-
bigquery.createDataset('my-dataset').then(function(data) {
  const dataset = data[0];
  const apiResponse = data[1];
});

createJob

createJob(options, callback) returns Promise

Creates a job. Typically when creating a job you'll have a very specific task in mind. For this we recommend one of the following methods:

  • BigQuery#createQueryJob
  • BigQuery/table#createCopyJob
  • BigQuery/table#createCopyFromJob
  • BigQuery/table#createExtractJob
  • BigQuery/table#createLoadJob

However in the event you need a finer level of control over the job creation, you can use this method to pass in a raw Job resource object.

Parameter

options

object

Object in the form of a Job resource;

callback

Optional

function()

The callback function.

See also

Jobs Overview

Jobs: insert API Documentation

Returns

Promise 

Example

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

const options = {
  configuration: {
    query: {
      query: 'SELECT url FROM `publicdata:samples.github_nested` LIMIT 100'
    }
  }
};

bigquery.createJob(options, function(err, job) {
  if (err) {
    // Error handling omitted.
  }

  job.getQueryResults(function(err, rows) {});
});

//-
// If the callback is omitted, we'll return a Promise.
//-
bigquery.createJob(options).then(function(data) {
  const job = data[0];

  return job.getQueryResults();
});

createQueryJob

createQueryJob(options, callback) returns Promise

Run a query as a job. No results are immediately returned. Instead, your callback will be executed with a Job object that you must ping for the results. See the Job documentation for explanations of how to check on the status of the job.

Parameter

options

(object or string)

The configuration object. This must be in the format of the configuration.query property of a Jobs resource. If a string is provided, this is used as the query string, and all other options are defaulted.

callback

Optional

function()

The callback function.

See also

Jobs: insert API Documentation

Throws

Error 

If a query is not specified.

Error 

If a Table is not provided as a destination.

Returns

Promise 

Example

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

const query = 'SELECT url FROM `publicdata:samples.github_nested` LIMIT 100';

//-
// You may pass only a query string, having a new table created to store the
// results of the query.
//-
bigquery.createQueryJob(query, function(err, job) {});

//-
// You can also control the destination table by providing a
// {@link Table} object.
//-
bigquery.createQueryJob({
  destination: bigquery.dataset('higher_education').table('institutions'),
  query: query
}, function(err, job) {});

//-
// After you have run `createQueryJob`, your query will execute in a job. Your
// callback is executed with a {@link Job} object so that you may
// check for the results.
//-
bigquery.createQueryJob(query, function(err, job) {
  if (!err) {
    job.getQueryResults(function(err, rows, apiResponse) {});
  }
});

//-
// If the callback is omitted, we'll return a Promise.
//-
bigquery.createQueryJob(query).then(function(data) {
  var job = data[0];
  var apiResponse = data[1];

  return job.getQueryResults();
});

dataset

dataset(id) returns Dataset

Create a reference to a dataset.

Parameter

id

string

ID of the dataset.

Returns

Dataset 

Example

const BigQuery = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const dataset = bigquery.dataset('higher_education');

date

date(value)

The DATE type represents a logical calendar date, independent of time zone. It does not represent a specific 24-hour time period. Rather, a given DATE value represents a different 24-hour period when interpreted in different time zones, and may represent a shorter or longer day during Daylight Savings Time transitions.

Parameter

value

(object or string)

The date. If a string, this should be in the format the API describes: YYYY-[M]M-[D]D. Otherwise, provide an object.

Example

const BigQuery = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const date = bigquery.date('2017-01-01');

//-
// Alternatively, provide an object.
//-
const date2 = bigquery.date({
  year: 2017,
  month: 1,
  day: 1
});

datetime

datetime(value)

A DATETIME data type represents a point in time. Unlike a TIMESTAMP, this does not refer to an absolute instance in time. Instead, it is the civil time, or the time that a user would see on a watch or calendar.

Parameter

value

(object or string)

The time. If a string, this should be in the format the API describes: YYYY-[M]M-[D]D[ [H]H:[M]M:[S]S[.DDDDDD]]. Otherwise, provide an object.

Example

const BigQuery = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const datetime = bigquery.datetime('2017-01-01 13:00:00');

//-
// Alternatively, provide an object.
//-
const datetime = bigquery.datetime({
  year: 2017,
  month: 1,
  day: 1,
  hours: 14,
  minutes: 0,
  seconds: 0
});

getDatasets

getDatasets(options, callback) returns Promise

List all or some of the datasets in your project.

Parameter

options

Optional

object

Configuration object.

callback

Optional

function()

The callback function.

See also

Datasets: list API Documentation

Returns

Promise 

Example

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

bigquery.getDatasets(function(err, datasets) {
  if (!err) {
    // datasets is an array of Dataset objects.
  }
});

//-
// To control how many API requests are made and page through the results
// manually, set `autoPaginate` to `false`.
//-
function manualPaginationCallback(err, datasets, nextQuery, apiResponse) {
  if (nextQuery) {
    // More results exist.
    bigquery.getDatasets(nextQuery, manualPaginationCallback);
  }
}

bigquery.getDatasets({
  autoPaginate: false
}, manualPaginationCallback);

//-
// If the callback is omitted, we'll return a Promise.
//-
bigquery.getDatasets().then(function(datasets) {});

getJobs

getJobs(options, callback) returns Promise

Get all of the jobs from your project.

Parameter

options

Optional

object

Configuration object.

callback

Optional

function()

The callback function.

See also

Jobs: list API Documentation

Returns

Promise 

Example

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

bigquery.getJobs(function(err, jobs) {
  if (!err) {
    // jobs is an array of Job objects.
  }
});

//-
// To control how many API requests are made and page through the results
// manually, set `autoPaginate` to `false`.
//-
function manualPaginationCallback(err, jobs, nextQuery, apiRespose) {
  if (nextQuery) {
    // More results exist.
    bigquery.getJobs(nextQuery, manualPaginationCallback);
  }
}

bigquery.getJobs({
  autoPaginate: false
}, manualPaginationCallback);

//-
// If the callback is omitted, we'll return a Promise.
//-
bigquery.getJobs().then(function(data) {
  const jobs = data[0];
});

job

job(id) returns Job

Create a reference to an existing job.

Parameter

id

string

ID of the job.

Returns

Job 

Example

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

const myExistingJob = bigquery.job('job-id');

query

query(query, options, callback) returns Promise

Run a query scoped to your project. For manual pagination please refer to BigQuery#createQueryJob.

Parameter

query

(string or object)

A string SQL query or configuration object. For all available options, see Jobs: query request body.

options

Optional

object

Configuration object for query results.

callback

Optional

function()

The callback function.

See also

Jobs: query API Documentation

Returns

Promise 

Example

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

const query = 'SELECT url FROM `publicdata:samples.github_nested` LIMIT 100';

bigquery.query(query, function(err, rows) {
  if (!err) {
    // rows is an array of results.
  }
});

//-
// Positional SQL parameters are supported.
//-
bigquery.query({
  query: [
    'SELECT url',
    'FROM `publicdata.samples.github_nested`',
    'WHERE repository.owner = ?'
  ].join(' '),

  params: [
    'google'
  ]
}, function(err, rows) {});

//-
// Or if you prefer to name them, that's also supported.
//-
bigquery.query({
  query: [
    'SELECT url',
    'FROM `publicdata.samples.github_nested`',
    'WHERE repository.owner = @owner'
  ].join(' '),
  params: {
    owner: 'google'
  }
}, function(err, rows) {});

//-
// If you need to use a `DATE`, `DATETIME`, `TIME`, or `TIMESTAMP` type in
// your query, see {@link BigQuery#date}, {@link BigQuery#datetime},
// {@link BigQuery#time}, and {@link BigQuery#timestamp}.
//-

//-
// If the callback is omitted, we'll return a Promise.
//-
bigquery.query(query).then(function(data) {
  var rows = data[0];
});

time

time(value)

A TIME data type represents a time, independent of a specific date.

Parameter

value

(object or string)

The time. If a string, this should be in the format the API describes: [H]H:[M]M:[S]S[.DDDDDD]. Otherwise, provide an object.

Example

const BigQuery = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const time = bigquery.time('14:00:00'); // 2:00 PM

//-
// Alternatively, provide an object.
//-
const time = bigquery.time({
  hours: 14,
  minutes: 0,
  seconds: 0
});

timestamp

timestamp(value)

A timestamp represents an absolute point in time, independent of any time zone or convention such as Daylight Savings Time.

Parameter

value

date

The time.

Example

const BigQuery = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const timestamp = bigquery.timestamp(new Date());