BigQuery

BigQuery

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.

Constructor

new BigQuery(options)

Parameters:
Name Type Description
options BigQueryOptions

Constructor options.

Source:
See:
Examples

Install the client library with npm:

npm install @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'
});

include:samples/quickstart.js

region_tag:bigquery_quickstart
Full quickstart example:

Members

createQueryStream

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

Source:
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.

Source:
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.

Source:
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

Source:

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.

Parameters:
Name Type Description
value object | string

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

Properties
Name Type Description
year string | number

Four digits.

month string | number

One or two digits.

day string | number

One or two digits.

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

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

Parameters:
Name Type Description
value object | 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.

Properties
Name Type Attributes Description
year string | number

Four digits.

month string | number

One or two digits.

day string | number

One or two digits.

hours string | number <optional>

One or two digits (00 - 23).

minutes string | number <optional>

One or two digits (00 - 59).

seconds string | number <optional>

One or two digits (00 - 59).

fractional string | number <optional>

Up to six digits for microsecond precision.

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

(static) geography(value)

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

Parameters:
Name Type Description
value string

The geospatial data.

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

(static) time(value)

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

Parameters:
Name Type Description
value object | 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.

Properties
Name Type Attributes Description
hours string | number <optional>

One or two digits (00 - 23).

minutes string | number <optional>

One or two digits (00 - 59).

seconds string | number <optional>

One or two digits (00 - 59).

fractional string | number <optional>

Up to six digits for microsecond precision.

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

(static) timestamp(value)

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

Parameters:
Name Type Description
value Date | string

The time.

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

createDataset(id, optionsopt, callbackopt) → {Promise}

Create a dataset.

Parameters:
Name Type Attributes Description
id string

ID of the dataset to create.

options object <optional>

See a Dataset resource.

callback function <optional>

The callback function.

Properties
Name Type Attributes Description
err error <nullable>

An error returned while making this request

dataset Dataset

The newly created dataset

apiResponse object

The full API response.

Source:
See:
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(options, callbackopt) → {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.

Parameters:
Name Type Attributes Description
options object

Object in the form of a Job resource;

Properties
Name Type Attributes Description
jobId string <optional>

Custom job id.

jobPrefix string <optional>

Prefix to apply to the job id.

location string <optional>

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

callback function <optional>

The callback function.

Properties
Name Type Attributes Description
err error <nullable>

An error returned while making this request.

job Job

The newly created job.

apiResponse object

The full API response.

Source:
See:
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(options, callbackopt) → {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.

Parameters:
Name Type Attributes Description
options object | 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.

Properties
Name Type Attributes Default Description
destination Table <optional>

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

dryRun boolean <optional>

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

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

jobId string <optional>

Custom job id.

jobPrefix string <optional>

Prefix to apply to the job id.

query string

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

useLegacySql boolean <optional>
false

Option to use legacy sql syntax.

callback function <optional>

The callback function.

Properties
Name Type Attributes Description
err error <nullable>

An error returned while making this request.

job Job

The newly created job for your query.

apiResponse object

The full API response.

Source:
See:
Throws:
  • If a query is not specified.

    Type
    Error
  • If a Table is not provided as a destination.

    Type
    Error
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
// 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 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(id, optionsopt) → {Dataset}

Create a reference to a dataset.

Parameters:
Name Type Attributes Description
id string

ID of the dataset.

options object <optional>

Dataset options.

Properties
Name Type Attributes Description
location string <optional>

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

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

date(value)

Parameters:
Name Type Description
value object | string

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

Properties
Name Type Description
year string | number

Four digits.

month string | number

One or two digits.

day string | number

One or two digits.

Source:
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(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.

Parameters:
Name Type Description
value object | 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.

Properties
Name Type Attributes Description
year string | number

Four digits.

month string | number

One or two digits.

day string | number

One or two digits.

hours string | number <optional>

One or two digits (00 - 23).

minutes string | number <optional>

One or two digits (00 - 59).

seconds string | number <optional>

One or two digits (00 - 59).

fractional string | number <optional>

Up to six digits for microsecond precision.

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

geography(value)

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

Parameters:
Name Type Description
value string

The geospatial data.

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

getDatasets(optionsopt, callbackopt) → {Promise}

List all or some of the datasets in your project.

Parameters:
Name Type Attributes Description
options object <optional>

Configuration object.

Properties
Name Type Attributes Description
all boolean <optional>

List all datasets, including hidden ones.

autoPaginate boolean <optional>

Have pagination handled automatically. Default: true.

maxApiCalls number <optional>

Maximum number of API calls to make.

maxResults number <optional>

Maximum number of results to return.

pageToken string <optional>

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

callback function <optional>

The callback function.

Properties
Name Type Attributes Description
err error <nullable>

An error returned while making this request

datasets Array.<Dataset>

The list of datasets in your project.

Source:
See:
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(optionsopt, callbackopt) → {Promise}

Get all of the jobs from your project.

Parameters:
Name Type Attributes Description
options object <optional>

Configuration object.

Properties
Name Type Attributes Description
allUsers boolean <optional>

Display jobs owned by all users in the project.

autoPaginate boolean <optional>

Have pagination handled automatically. Default: true.

maxApiCalls number <optional>

Maximum number of API calls to make.

maxResults number <optional>

Maximum number of results to return.

pageToken string <optional>

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

projection string <optional>

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

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

callback function <optional>

The callback function.

Properties
Name Type Attributes Description
err error <nullable>

An error returned while making this request

jobs Array.<Job>

The list of jobs in your project.

Source:
See:
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(id, optionsopt) → {Job}

Create a reference to an existing job.

Parameters:
Name Type Attributes Description
id string

ID of the job.

options object <optional>

Configuration object.

Properties
Name Type Attributes Description
location string <optional>

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

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

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

query(query, optionsopt, callbackopt) → {Promise}

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

Parameters:
Name Type Attributes Description
query string | object

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

Properties
Name Type Attributes Default Description
location string <optional>

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

jobId string <optional>

Custom id for the underlying job.

jobPrefix string <optional>

Prefix to apply to the underlying job id.

params object | Array.<*>

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 <optional>
false

Option to use legacy sql syntax.

options object <optional>

Configuration object for query results.

Properties
Name Type Attributes Description
maxResults number <optional>

Maximum number of results to read.

timeoutMs number <optional>

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

The callback function.

Properties
Name Type Attributes Description
err error <nullable>

An error returned while making this request

rows array

The list of results from your query.

Source:
See:
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 BigQuery#date, BigQuery#datetime,
// BigQuery#time, and BigQuery#timestamp.
//-

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

time(value)

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

Parameters:
Name Type Description
value object | 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.

Properties
Name Type Attributes Description
hours string | number <optional>

One or two digits (00 - 23).

minutes string | number <optional>

One or two digits (00 - 59).

seconds string | number <optional>

One or two digits (00 - 59).

fractional string | number <optional>

Up to six digits for microsecond precision.

Source:
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(value)

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

Parameters:
Name Type Description
value Date | string

The time.

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