Constructor

BigQuery

new BigQuery(options)

Parameter

options

BigQueryOptions

Constructor options.

See also

What is BigQuery?

Examples

Install the client library with npm:

npm install --save

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:

async function createDataset(
  datasetName = 'my_new_dataset' // Name for the new dataset
) {
  // Imports the Google Cloud client library
  const {BigQuery} = require('@google-cloud/bigquery');

  // Creates a client
  const bigquery = new BigQuery();

  // Create the dataset
  const [dataset] = await bigquery.createDataset(datasetName);
  console.log(`Dataset ${dataset.id} created.`);
}

Properties

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

location

string

Methods

date

static

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.

Values in value have the following properties:

Parameter

year

(string or number)

Four digits.

month

(string or number)

One or two digits.

day

(string or number)

One or two digits.

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

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.

Values in value have the following properties:

Parameter

year

(string or number)

Four digits.

month

(string or number)

One or two digits.

day

(string or number)

One or two digits.

hours

Optional

(string or number)

One or two digits (00 - 23).

minutes

Optional

(string or number)

One or two digits (00 - 59).

seconds

Optional

(string or number)

One or two digits (00 - 59).

fractional

Optional

(string or number)

Up to six digits for microsecond precision.

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.

Values in value have the following properties:

Parameter

hours

Optional

(string or number)

One or two digits (00 - 23).

minutes

Optional

(string or number)

One or two digits (00 - 59).

seconds

Optional

(string or number)

One or two digits (00 - 59).

fractional

Optional

(string or number)

Up to six digits for microsecond precision.

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 or string)

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.

Values in callback have the following properties:

Parameter

err

error

An error returned while making this request

Value may be null.

dataset

Dataset

The newly created dataset

apiResponse

object

The full API response.

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:

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;

Values in options have the following properties:

Parameter

jobId

Optional

string

Custom job id.

jobPrefix

Optional

string

Prefix to apply to the job id.

location

Optional

string

The geographic location of the job. Required except for US and EU.

callback

Optional

function()

The callback function.

Values in callback have the following properties:

Parameter

err

error

An error returned while making this request.

Value may be null.

job

Job

The newly created job.

apiResponse

object

The full API response.

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.

Values in options have the following properties:

Parameter

destination

Optional

Table

The table to save the query's results to. If omitted, a new table will be created.

dryRun

Optional

boolean

If set, don't actually run this job. A valid query will update the job with processing statistics. These can be accessed via job.metadata.

location

Optional

string

The geographic location of the job. Required except for US and EU.

jobId

Optional

string

Custom job id.

jobPrefix

Optional

string

Prefix to apply to the job id.

query

string

A query string, following the BigQuery query syntax, of the query to execute.

useLegacySql

Optional

boolean

Option to use legacy sql syntax.

callback

Optional

function()

The callback function.

Values in callback have the following properties:

Parameter

err

error

An error returned while making this request.

Value may be null.

job

Job

The newly created job for your query.

apiResponse

object

The full API response.

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) {
  const job = data[0];
  const apiResponse = data[1];

  return job.getQueryResults();
});

dataset

dataset(id, options) returns Dataset

Create a reference to a dataset.

Parameter

id

string

ID of the dataset.

options

Optional

object

Dataset options.

Values in options have the following properties:

Parameter

location

Optional

string

The geographic location of the dataset. Required except for US and EU.

Returns

Dataset 

Example

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

date

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.

Values in value have the following properties:

Parameter

year

(string or number)

Four digits.

month

(string or number)

One or two digits.

day

(string or number)

One or two digits.

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

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.

Values in value have the following properties:

Parameter

year

(string or number)

Four digits.

month

(string or number)

One or two digits.

day

(string or number)

One or two digits.

hours

Optional

(string or number)

One or two digits (00 - 23).

minutes

Optional

(string or number)

One or two digits (00 - 59).

seconds

Optional

(string or number)

One or two digits (00 - 59).

fractional

Optional

(string or number)

Up to six digits for microsecond precision.

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.

Values in options have the following properties:

Parameter

all

Optional

boolean

List all datasets, including hidden ones.

autoPaginate

Optional

boolean

Have pagination handled automatically. Default: true.

maxApiCalls

Optional

number

Maximum number of API calls to make.

maxResults

Optional

number

Maximum number of results to return.

pageToken

Optional

string

Token returned from a previous call, to request the next page of results.

callback

Optional

function()

The callback function.

Values in callback have the following properties:

Parameter

err

error

An error returned while making this request

Value may be null.

datasets

Array of Dataset

The list of datasets in your project.

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.

Values in options have the following properties:

Parameter

allUsers

Optional

boolean

Display jobs owned by all users in the project.

autoPaginate

Optional

boolean

Have pagination handled automatically. Default: true.

maxApiCalls

Optional

number

Maximum number of API calls to make.

maxResults

Optional

number

Maximum number of results to return.

pageToken

Optional

string

Token returned from a previous call, to request the next page of results.

projection

Optional

string

Restrict information returned to a set of selected fields. Acceptable values are "full", for all job data, and "minimal", to not include the job configuration.

stateFilter

Optional

string

Filter for job state. Acceptable values are "done", "pending", and "running". Sending an array to this option performs a disjunction.

callback

Optional

function()

The callback function.

Values in callback have the following properties:

Parameter

err

error

An error returned while making this request

Value may be null.

jobs

Array of Job

The list of jobs in your project.

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, options) returns Job

Create a reference to an existing job.

Parameter

id

string

ID of the job.

options

Optional

object

Configuration object.

Values in options have the following properties:

Parameter

location

Optional

string

The geographic location of the job. Required except for US and EU.

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.

Values in query have the following properties:

Parameter

location

Optional

string

The geographic location of the job. Required except for US and EU.

jobId

Optional

string

Custom id for the underlying job.

jobPrefix

Optional

string

Prefix to apply to the underlying job id.

params

(object or Array of any type)

For positional SQL parameters, provide an array of values. For named SQL parameters, provide an object which maps each named parameter to its value. The supported types are integers, floats, BigQuery#date objects, BigQuery#datetime objects, BigQuery#time objects, BigQuery#timestamp objects, Strings, Booleans, and Objects.

query

string

A query string, following the BigQuery query syntax, of the query to execute.

useLegacySql

Optional

boolean

Option to use legacy sql syntax.

options

Optional

object

Configuration object for query results.

Values in options have the following properties:

Parameter

maxResults

Optional

number

Maximum number of results to read.

timeoutMs

Optional

number

How long to wait for the query to complete, in milliseconds, before returning. Default is to return immediately. If the timeout passes before the job completes, the request will fail with a TIMEOUT error.

callback

Optional

function()

The callback function.

Values in callback have the following properties:

Parameter

err

error

An error returned while making this request

Value may be null.

rows

array

The list of results from your query.

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) {
  const 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.

Values in value have the following properties:

Parameter

hours

Optional

(string or number)

One or two digits (00 - 23).

minutes

Optional

(string or number)

One or two digits (00 - 59).

seconds

Optional

(string or number)

One or two digits (00 - 59).

fractional

Optional

(string or number)

Up to six digits for microsecond precision.

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 or string)

The time.

Example

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