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.

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

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

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:

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

Values in options have the following properties:

Parameter

jobPrefix

Optional

string

Prefix to apply to the job id.

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.

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

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

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

Values in query have the following properties:

Parameter

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

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

The time.

Example

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