Constructor

Table

new Table(dataset, id)

Table objects are returned by methods such as BigQuery/dataset#table, BigQuery/dataset#createTable, and BigQuery/dataset#getTables.

Parameter

dataset

Dataset

Dataset instance.

id

string

The ID of the table.

Example

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

const table = dataset.table('my-table');

Property

createReadStream

Create a readable stream of the rows of data in your table. This method is simply a wrapper around Table#getRows.

See also

Tabledata: list API Documentation

Returns

ReadableStream 

Example

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

table.createReadStream(options)
  .on('error', console.error)
  .on('data', function(row) {})
  .on('end', function() {
    // All rows have been retrieved.
  });

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

Methods

copy

copy(destination, metadata, callback) returns Promise

Copy data from one table to another, optionally creating that table.

Parameter

destination

Table

The destination table.

metadata

Optional

object

Metadata to set with the copy operation. The metadata object should be in the format of the configuration.copy property of a Jobs resource.

callback

Optional

function()

The callback function.

Throws

Error 

If a destination other than a Table object is provided.

Returns

Promise 

Example

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

const table = dataset.table('my-table');
const yourTable = dataset.table('your-table');

table.copy(yourTable, function(err, apiResponse) {});

//-
// See the <a href="http://goo.gl/dKWIyS">`configuration.copy`</a> object for
// all available options.
//-
const metadata = {
  createDisposition: 'CREATE_NEVER',
  writeDisposition: 'WRITE_TRUNCATE'
};

table.copy(yourTable, metadata, function(err, apiResponse) {});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.copy(yourTable, metadata).then(function(data) {
  const apiResponse = data[0];
});

copyFrom

copyFrom(sourceTables, metadata, callback) returns Promise

Copy data from multiple tables into this table.

Parameter

sourceTables

(Table or Array of Table)

The source table(s) to copy data from.

metadata

Optional

object

Metadata to set with the copy operation. The metadata object should be in the format of the configuration.copy property of a Jobs resource.

callback

Optional

function()

The callback function.

Throws

Error 

If a source other than a Table object is provided.

Returns

Promise 

Example

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

const sourceTables = [
  dataset.table('your-table'),
  dataset.table('your-second-table')
];

table.copyFrom(sourceTables, function(err, apiResponse) {});

//-
// See the <a href="http://goo.gl/dKWIyS">`configuration.copy`</a> object for
// all available options.
//-
const metadata = {
  createDisposition: 'CREATE_NEVER',
  writeDisposition: 'WRITE_TRUNCATE'
};

table.copyFrom(sourceTables, metadata, function(err, apiResponse) {});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.copyFrom(sourceTables, metadata).then(function(data) {
  const apiResponse = data[0];
});

create

create(options, callback) returns Promise

Create a table.

Parameter

options

Optional

object

See Dataset#createTable.

callback

Optional

function()

Returns

Promise 

Example

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

const table = dataset.table('my-table');

table.create(function(err, table, apiResponse) {
  if (!err) {
    // The table was created successfully.
  }
});

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

createCopyFromJob

createCopyFromJob(sourceTables, metadata, callback) returns Promise

Copy data from multiple tables into this table.

Parameter

sourceTables

(Table or Array of Table)

The source table(s) to copy data from.

metadata

Optional

object

Metadata to set with the copy operation. The metadata object should be in the format of the configuration.copy property of a Jobs resource.

callback

Optional

function()

The callback function.

See also

Jobs: insert API Documentation

Throws

Error 

If a source other than a Table object is provided.

Returns

Promise 

Example

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

const sourceTables = [
  dataset.table('your-table'),
  dataset.table('your-second-table')
];

const callback = function(err, job, apiResponse) {
  // `job` is a Job object that can be used to check the status of the
  // request.
};

table.createCopyFromJob(sourceTables, callback);

//-
// See the <a href="http://goo.gl/dKWIyS">`configuration.copy`</a> object for
// all available options.
//-
const metadata = {
  createDisposition: 'CREATE_NEVER',
  writeDisposition: 'WRITE_TRUNCATE'
};

table.createCopyFromJob(sourceTables, metadata, callback);

//-
// If the callback is omitted, we'll return a Promise.
//-
table.createCopyFromJob(sourceTables, metadata).then(function(data) {
  const job = data[0];
  const apiResponse = data[1];
});

createCopyJob

createCopyJob(destination, metadata, callback) returns Promise

Copy data from one table to another, optionally creating that table.

Parameter

destination

Table

The destination table.

metadata

Optional

object

Metadata to set with the copy operation. The metadata object should be in the format of the configuration.copy property of a Jobs resource.

callback

Optional

function()

The callback function.

See also

Jobs: insert API Documentation

Throws

Error 

If a destination other than a Table object is provided.

Returns

Promise 

Example

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

const yourTable = dataset.table('your-table');
table.createCopyJob(yourTable, function(err, job, apiResponse) {
  // `job` is a Job object that can be used to check the status of the
  // request.
});

//-
// See the <a href="http://goo.gl/dKWIyS">`configuration.copy`</a> object for
// all available options.
//-
const metadata = {
  createDisposition: 'CREATE_NEVER',
  writeDisposition: 'WRITE_TRUNCATE'
};

table.createCopyJob(yourTable, metadata, function(err, job, apiResponse) {});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.createCopyJob(yourTable, metadata).then(function(data) {
  const job = data[0];
  const apiResponse = data[1];
});

createExtractJob

createExtractJob(destination, options, callback)

Export table to Cloud Storage.

Parameter

destination

(string or File)

Where the file should be exported to. A string or a File object.

options

Optional

object

The configuration object.

callback

function()

The callback function.

See also

Jobs: insert API Documentation

Throws

Error 

If destination isn't a File object.

Error 

If destination format isn't recongized.

Example

const Storage = require('@google-cloud/storage');
const BigQuery = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const dataset = bigquery.dataset('my-dataset');
const table = bigquery.table('my-table');

const storage = new Storage({
  projectId: 'grape-spaceship-123'
});
const extractedFile = storage.bucket('institutions').file('2014.csv');

function callback(err, job, apiResponse) {
  // `job` is a Job object that can be used to check the status of the
  // request.
}

//-
// To use the default options, just pass a {@link https://cloud.google.com/nodejs/docs/reference/storage/latest/File File} object.
//
// Note: The exported format type will be inferred by the file's extension.
// If you wish to override this, or provide an array of destination files,
// you must provide an `options` object.
//-
table.createExtractJob(extractedFile, callback);

//-
// If you need more customization, pass an `options` object.
//-
const options = {
  format: 'json',
  gzip: true
};

table.createExtractJob(extractedFile, options, callback);

//-
// You can also specify multiple destination files.
//-
table.createExtractJob([
  storage.bucket('institutions').file('2014.json'),
  storage.bucket('institutions-copy').file('2014.json')
], options, callback);

//-
// If the callback is omitted, we'll return a Promise.
//-
table.createExtractJob(extractedFile, options).then(function(data) {
  const job = data[0];
  const apiResponse = data[1];
});

createLoadJob

createLoadJob(source, metadata, callback) returns Promise

Load data from a local file or Storage File.

By loading data this way, you create a load job that will run your data load asynchronously. If you would like instantaneous access to your data, insert it using {@liink Table#insert}.

Note: The file type will be inferred by the given file's extension. If you wish to override this, you must provide metadata.format.

Parameter

source

(string or File)

The source file to import. A string or a File object.

metadata

Optional

object

Metadata to set with the load operation. The metadata object should be in the format of the configuration.load property of a Jobs resource.

callback

Optional

function()

The callback function.

See also

Jobs: insert API Documentation

Throws

Error 

If the source isn't a string file name or a File instance.

Returns

Promise 

Example

const Storage = require('@google-cloud/storage');
const BigQuery = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const dataset = bigquery.dataset('my-dataset');
const table = bigquery.table('my-table');

//-
// Load data from a local file.
//-
const callback = function(err, job, apiResponse) {
  // `job` is a Job object that can be used to check the status of the
  // request.
};

table.createLoadJob('./institutions.csv', callback);

//-
// You may also pass in metadata in the format of a Jobs resource. See
// (http://goo.gl/BVcXk4) for a full list of supported values.
//-
const metadata = {
  encoding: 'ISO-8859-1',
  sourceFormat: 'NEWLINE_DELIMITED_JSON'
};

table.createLoadJob('./my-data.csv', metadata, callback);

//-
// Load data from a file in your Cloud Storage bucket.
//-
const storage = new Storage({
  projectId: 'grape-spaceship-123'
});
const data = storage.bucket('institutions').file('data.csv');
table.createLoadJob(data, callback);

//-
// Load data from multiple files in your Cloud Storage bucket(s).
//-
table.createLoadJob([
  storage.bucket('institutions').file('2011.csv'),
  storage.bucket('institutions').file('2012.csv')
], callback);

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

createQueryJob

createQueryJob()

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.

See BigQuery#createQueryJob for full documentation of this method.

createQueryStream

createQueryStream(query) returns stream

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

See BigQuery#createQueryStream for full documentation of this method.

Parameter

query

object

See BigQuery#createQueryStream for full documentation of this method.

Returns

stream 

See BigQuery#createQueryStream for full documentation of this method.

createWriteStream

createWriteStream(metadata) returns WritableStream

Load data into your table from a readable stream of JSON, CSV, or AVRO data.

Parameter

metadata

Optional

(string or object)

Metadata to set with the load operation. The metadata object should be in the format of the configuration.load property of a Jobs resource. If a string is given, it will be used as the filetype.

See also

Jobs: insert API Documentation

Throws

Error 

If source format isn't recognized.

Returns

WritableStream 

Example

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

//-
// Load data from a CSV file.
//-
const request = require('request');

const csvUrl = 'http://goo.gl/kSE7z6';

const metadata = {
  allowJaggedRows: true,
  skipLeadingRows: 1
};

request.get(csvUrl)
  .pipe(table.createWriteStream(metadata))
  .on('complete', function(job) {
    // `job` is a Job object that can be used to check the status of the
    // request.
  });

//-
// Load data from a JSON file.
//-
const fs = require('fs');

fs.createReadStream('./test/testdata/testfile.json')
  .pipe(table.createWriteStream('json'))
  .on('complete', function(job) {});

delete

delete(callback) returns Promise

Delete a table and all its data.

Parameter

callback

Optional

function()

See also

Tables: delete API Documentation

Returns

Promise 

Example

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

const table = dataset.table('my-table');

table.delete(function(err, apiResponse) {});

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

exists

exists(callback) returns Promise

Check if the table exists.

Parameter

callback

Optional

function()

Returns

Promise 

Example

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

const table = dataset.table('my-table');

table.exists(function(err, exists) {});

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

extract

extract(destination, options, callback) returns Promise

Export table to Cloud Storage.

Parameter

destination

(string or File)

Where the file should be exported to. A string or a File.

options

Optional

object

The configuration object.

callback

Optional

function()

The callback function.

Throws

Error 

If destination isn't a File object.

Error 

If destination format isn't recongized.

Returns

Promise 

Example

const Storage = require('@google-cloud/storage');
const BigQuery = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const dataset = bigquery.dataset('my-dataset');
const table = bigquery.table('my-table');

const storage = new Storage({
  projectId: 'grape-spaceship-123'
});
var extractedFile = storage.bucket('institutions').file('2014.csv');

//-
// To use the default options, just pass a {@link https://cloud.google.com/nodejs/docs/reference/storage/latest/File File} object.
//
// Note: The exported format type will be inferred by the file's extension.
// If you wish to override this, or provide an array of destination files,
// you must provide an `options` object.
//-
table.extract(extractedFile, function(err, apiResponse) {});

//-
// If you need more customization, pass an `options` object.
//-
var options = {
  format: 'json',
  gzip: true
};

table.extract(extractedFile, options, function(err, apiResponse) {});

//-
// You can also specify multiple destination files.
//-
table.extract([
  storage.bucket('institutions').file('2014.json'),
  storage.bucket('institutions-copy').file('2014.json')
], options, function(err, apiResponse) {});

//-
// If the callback is omitted, we'll return a Promise.
//-
table.extract(extractedFile, options).then(function(data) {
  var apiResponse = data[0];
});

get

get(options, callback) returns Promise

Get a table if it exists.

You may optionally use this to "get or create" an object by providing an object with autoCreate set to true. Any extra configuration that is normally required for the create method must be contained within this object as well.

Parameter

options

Optional

options

Configuration object.

callback

Optional

function()

Returns

Promise 

Example

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

const table = dataset.table('my-table');

table.get(function(err, table, apiResponse) {
  // `table.metadata` has been populated.
});

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

getMetadata

getMetadata(callback) returns Promise

Return the metadata associated with the Table.

Parameter

callback

Optional

function()

The callback function.

See also

Tables: get API Documentation

Returns

Promise 

Example

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

const table = dataset.table('my-table');

table.getMetadata(function(err, metadata, apiResponse) {});

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

getRows

getRows(options, callback) returns Promise

Retrieves table data from a specified set of rows. The rows are returned to your callback as an array of objects matching your table's schema.

Parameter

options

Optional

object

The configuration object.

callback

Optional

function()

The callback function.

See also

Tabledata: list API Documentation

Returns

Promise 

Example

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

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

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

table.getRows({
  autoPaginate: false
}, manualPaginationCallback);

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

insert

insert(rows, options, callback) returns Promise

Stream data into BigQuery one record at a time without running a load job.

There are more strict quota limits using this method so it is highly recommended that you load data into BigQuery using Table#import instead.

Parameter

rows

(object or Array of object)

The rows to insert into the table.

options

Optional

object

Configuration object.

callback

Optional

function()

The callback function.

See also

Tabledata: insertAll API Documentation

Troubleshooting Errors

Returns

Promise 

Example

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

//-
// Insert a single row.
//-
table.insert({
  INSTNM: 'Motion Picture Institute of Michigan',
  CITY: 'Troy',
  STABBR: 'MI'
}, insertHandler);

//-
// Insert multiple rows at a time.
//-
var rows = [
  {
    INSTNM: 'Motion Picture Institute of Michigan',
    CITY: 'Troy',
    STABBR: 'MI'
  },
  // ...
];

table.insert(rows, insertHandler);

//-
// Insert a row as according to the <a href="https://cloud.google.com/bigquery/docs/reference/v2/tabledata/insertAll">
// specification</a>.
//-
var row = {
  insertId: '1',
  json: {
    INSTNM: 'Motion Picture Institute of Michigan',
    CITY: 'Troy',
    STABBR: 'MI'
  }
};

var options = {
  raw: true
};

table.insert(row, options, insertHandler);

//-
// Handling the response. See <a href="https://developers.google.com/bigquery/troubleshooting-errors">
// Troubleshooting Errors</a> for best practices on how to handle errors.
//-
function insertHandler(err, apiResponse) {
  if (err) {
    // An API error or partial failure occurred.

    if (err.name === 'PartialFailureError') {
      // Some rows failed to insert, while others may have succeeded.

      // err.errors (object[]):
      // err.errors[].row (original row object passed to `insert`)
      // err.errors[].errors[].reason
      // err.errors[].errors[].message
    }
  }
}

//-
// If the callback is omitted, we'll return a Promise.
//-
table.insert(rows)
  .then(function(data) {
    var apiResponse = data[0];
  })
  .catch(function(err) {
    // An API error or partial failure occurred.

    if (err.name === 'PartialFailureError') {
      // Some rows failed to insert, while others may have succeeded.

      // err.errors (object[]):
      // err.errors[].row (original row object passed to `insert`)
      // err.errors[].errors[].reason
      // err.errors[].errors[].message
    }
  });

load

load(source, metadata, callback) returns Promise

Load data from a local file or Storage File.

By loading data this way, you create a load job that will run your data load asynchronously. If you would like instantaneous access to your data, insert it using Table#insert.

Note: The file type will be inferred by the given file's extension. If you wish to override this, you must provide metadata.format.

Parameter

source

(string or File)

The source file to import. A string or a File object.

metadata

Optional

object

Metadata to set with the load operation. The metadata object should be in the format of the configuration.load property of a Jobs resource.

callback

Optional

function()

The callback function.

Throws

Error 

If the source isn't a string file name or a File instance.

Returns

Promise 

Example

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

//-
// Load data from a local file.
//-
table.load('./institutions.csv', function(err, apiResponse) {});

//-
// You may also pass in metadata in the format of a Jobs resource. See
// (http://goo.gl/BVcXk4) for a full list of supported values.
//-
var metadata = {
  encoding: 'ISO-8859-1',
  sourceFormat: 'NEWLINE_DELIMITED_JSON'
};

table.load('./my-data.csv', metadata, function(err, apiResponse) {});

//-
// Load data from a file in your Cloud Storage bucket.
//-
var gcs = require('@google-cloud/storage')({
  projectId: 'grape-spaceship-123'
});
var data = gcs.bucket('institutions').file('data.csv');
table.load(data, function(err, apiResponse) {});

//-
// Load data from multiple files in your Cloud Storage bucket(s).
//-
table.load([
  gcs.bucket('institutions').file('2011.csv'),
  gcs.bucket('institutions').file('2012.csv')
], function(err, apiResponse) {});

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

query

query(query, callback) returns Promise

Run a query scoped to your dataset.

See BigQuery#query for full documentation of this method.

Parameter

query

object

See BigQuery#query for full documentation of this method.

callback

Optional

function()

See BigQuery#query for full documentation of this method.

Returns

Promise 

setMetadata

setMetadata(metadata, callback) returns Promise

Set the metadata on the table.

Parameter

metadata

object

The metadata key/value object to set.

callback

Optional

function()

The callback function.

See also

Tables: update API Documentation

Returns

Promise 

Example

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

const metadata = {
  name: 'My recipes',
  description: 'A table for storing my recipes.',
  schema: 'name:string, servings:integer, cookingTime:float, quick:boolean'
};

table.setMetadata(metadata, function(err, metadata, apiResponse) {});

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

setMetadata

setMetadata(metadata, callback) returns Promise

Set the metadata for this Table. This can be useful for updating table labels.

Parameter

metadata

object

Metadata to save on the Table.

callback

Optional

function()

The callback function.

See also

Tables: patch API Documentation

Returns

Promise 

Example

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

const table = dataset.table('my-table');

const metadata = {
  labels: {
    foo: 'bar'
  }
};

table.setMetadata(metadata, function(err, apiResponse) {});

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