new BigQuery(options)

Examples

<caption>Install the client library with <a
href="https://www.npmjs.com/">npm</a>:</caption> npm install --save

Import the client library

const {BigQuery} = require('@google-cloud/bigquery');
<caption>Create a client that uses <a
href="https://cloud.google.com/docs/authentication/production#providing_credentials_to_your_application">Application
Default Credentials (ADC)</a>:</caption> const bigquery = new BigQuery();
<caption>Create a client with <a
href="https://cloud.google.com/docs/authentication/production#obtaining_and_providing_service_account_credentials_manually">explicit
credentials</a>:</caption> const bigquery = new BigQuery({ projectId:
'your-project-id', keyFilename: '/path/to/keyfile.json'
});

include:samples/quickstart.js

region_tag:bigquery_quickstart
Full quickstart example:

Parameter

Name Type Optional Description

options

 

 

Constructor options.

See also

What is BigQuery?

Properties

createQueryStream

Run a query scoped to your project as a readable object 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();
  });

Parameter

Name Type Optional Description

query

object

 

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

Returns

stream 

getDatasetsStream

List all or some of the Dataset objects in your project as a readable object 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();
  });

Parameter

Name Type Optional Description

options

object

Yes

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

Returns

stream 

getJobsStream

List all or some of the Job objects in your project as a readable object 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();
  });

Parameter

Name Type Optional Description

options

object

Yes

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

Returns

stream 

location  string

Methods

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.

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

Parameters

Name Type Optional Description

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:

Name Type Optional Description

year

(string or number)

 

Four digits.

month

(string or number)

 

One or two digits.

day

(string or number)

 

One or two digits.

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.

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

Parameters

Name Type Optional Description

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:

Name Type Optional Description

year

(string or number)

 

Four digits.

month

(string or number)

 

One or two digits.

day

(string or number)

 

One or two digits.

hours

(string or number)

Yes

One or two digits (00 - 23).

minutes

(string or number)

Yes

One or two digits (00 - 59).

seconds

(string or number)

Yes

One or two digits (00 - 59).

fractional

(string or number)

Yes

Up to six digits for microsecond precision.

static

geography(value)

A geography value represents a surface area on the Earth in Well-known Text (WKT) format.

Example

const {BigQuery} = require('@google-cloud/bigquery');
const geography = BigQuery.geography('POINT(1, 2)');

Parameter

Name Type Optional Description

value

string

 

The geospatial data.

static

time(value)

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

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

Parameters

Name Type Optional Description

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:

Name Type Optional Description

hours

(string or number)

Yes

One or two digits (00 - 23).

minutes

(string or number)

Yes

One or two digits (00 - 59).

seconds

(string or number)

Yes

One or two digits (00 - 59).

fractional

(string or number)

Yes

Up to six digits for microsecond precision.

static

timestamp(value)

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

Example

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

Parameter

Name Type Optional Description

value

(Date or string)

 

The time.

createDataset(id[, options][, callback]) → Promise

Create a dataset.

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

Parameters

Name Type Optional Description

id

string

 

ID of the dataset to create.

options

object

Yes

See a Dataset resource.

callback

function()

Yes

The callback function.

Values in callback have the following properties:

Name Type Optional Description

err

error

 

An error returned while making this request

Value can be null.

dataset

Dataset

 

The newly created dataset

apiResponse

object

 

The full API response.

See also

Datasets: insert API Documentation

Returns

Promise 

createJob(options[, callback]) → 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.

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

Parameters

Name Type Optional Description

options

object

 

Object in the form of a Job resource;

Values in options have the following properties:

Name Type Optional Description

jobId

string

Yes

Custom job id.

jobPrefix

string

Yes

Prefix to apply to the job id.

location

string

Yes

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

callback

function()

Yes

The callback function.

Values in callback have the following properties:

Name Type Optional Description

err

error

 

An error returned while making this request.

Value can be null.

job

Job

 

The newly created job.

apiResponse

object

 

The full API response.

See also

Jobs Overview

Jobs: insert API Documentation

Returns

Promise 

createQueryJob(options[, callback]) → 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.

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

Parameters

Name Type Optional Description

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:

Name Type Optional Description

destination

Table

Yes

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

dryRun

boolean

Yes

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

string

Yes

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

jobId

string

Yes

Custom job id.

jobPrefix

string

Yes

Prefix to apply to the job id.

query

string

 

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

useLegacySql

boolean

Yes

Option to use legacy sql syntax.

Defaults to false.

callback

function()

Yes

The callback function.

Values in callback have the following properties:

Name Type Optional Description

err

error

 

An error returned while making this request.

Value can 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 

dataset(id[, options]) → Dataset

Create a reference to a dataset.

Example

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

Parameters

Name Type Optional Description

id

string

 

ID of the dataset.

options

object

Yes

Dataset options.

Values in options have the following properties:

Name Type Optional Description

location

string

Yes

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

Returns

Dataset 

date(value)

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

Parameters

Name Type Optional Description

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:

Name Type Optional Description

year

(string or number)

 

Four digits.

month

(string or number)

 

One or two digits.

day

(string or number)

 

One or two digits.

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.

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

Parameters

Name Type Optional Description

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:

Name Type Optional Description

year

(string or number)

 

Four digits.

month

(string or number)

 

One or two digits.

day

(string or number)

 

One or two digits.

hours

(string or number)

Yes

One or two digits (00 - 23).

minutes

(string or number)

Yes

One or two digits (00 - 59).

seconds

(string or number)

Yes

One or two digits (00 - 59).

fractional

(string or number)

Yes

Up to six digits for microsecond precision.

geography(value)

A geography value represents a surface area on the Earth in Well-known Text (WKT) format.

Example

const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const geography = bigquery.geography('POINT(1, 2)');

Parameter

Name Type Optional Description

value

string

 

The geospatial data.

getDatasets([options][, callback]) → Promise

List all or some of the datasets in your project.

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

Parameters

Name Type Optional Description

options

object

Yes

Configuration object.

Values in options have the following properties:

Name Type Optional Description

all

boolean

Yes

List all datasets, including hidden ones.

autoPaginate

boolean

Yes

Have pagination handled automatically. Default: true.

maxApiCalls

number

Yes

Maximum number of API calls to make.

maxResults

number

Yes

Maximum number of results to return.

pageToken

string

Yes

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

callback

function()

Yes

The callback function.

Values in callback have the following properties:

Name Type Optional Description

err

error

 

An error returned while making this request

Value can be null.

datasets

Array of Dataset

 

The list of datasets in your project.

See also

Datasets: list API Documentation

Returns

Promise 

getJobs([options][, callback]) → Promise

Get all of the jobs from your project.

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

Parameters

Name Type Optional Description

options

object

Yes

Configuration object.

Values in options have the following properties:

Name Type Optional Description

allUsers

boolean

Yes

Display jobs owned by all users in the project.

autoPaginate

boolean

Yes

Have pagination handled automatically. Default: true.

maxApiCalls

number

Yes

Maximum number of API calls to make.

maxResults

number

Yes

Maximum number of results to return.

pageToken

string

Yes

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

projection

string

Yes

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

string

Yes

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

callback

function()

Yes

The callback function.

Values in callback have the following properties:

Name Type Optional Description

err

error

 

An error returned while making this request

Value can be null.

jobs

Array of Job

 

The list of jobs in your project.

See also

Jobs: list API Documentation

Returns

Promise 

job(id[, options]) → Job

Create a reference to an existing job.

Example

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

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

Parameters

Name Type Optional Description

id

string

 

ID of the job.

options

object

Yes

Configuration object.

Values in options have the following properties:

Name Type Optional Description

location

string

Yes

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

Returns

Job 

query(query[, options][, callback]) → Promise

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

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

Parameters

Name Type Optional Description

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:

Name Type Optional Description

location

string

Yes

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

jobId

string

Yes

Custom id for the underlying job.

jobPrefix

string

Yes

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

boolean

Yes

Option to use legacy sql syntax.

Defaults to false.

options

object

Yes

Configuration object for query results.

Values in options have the following properties:

Name Type Optional Description

maxResults

number

Yes

Maximum number of results to read.

timeoutMs

number

Yes

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

function()

Yes

The callback function.

Values in callback have the following properties:

Name Type Optional Description

err

error

 

An error returned while making this request

Value can be null.

rows

array

 

The list of results from your query.

See also

Jobs: query API Documentation

Returns

Promise 

time(value)

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

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

Parameters

Name Type Optional Description

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:

Name Type Optional Description

hours

(string or number)

Yes

One or two digits (00 - 23).

minutes

(string or number)

Yes

One or two digits (00 - 59).

seconds

(string or number)

Yes

One or two digits (00 - 59).

fractional

(string or number)

Yes

Up to six digits for microsecond precision.

timestamp(value)

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

Example

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

Parameter

Name Type Optional Description

value

(Date or string)

 

The time.