BigQuery - Documentation

Constructor

new BigQuery(options)

Parameters:

Name	Type	Description
`options`	BigQueryOptions	Constructor options.

Examples

Install the client library with <a href="https://www.npmjs.com/">npm</a>:
```
npm install @google-cloud/bigquery

```

Import the client library
```
const {BigQuery} = require('@google-cloud/bigquery');

```

Create a client that uses <a href="https://cloud.google.com/docs/authentication/production#providing_credentials_to_your_application">Application Default Credentials (ADC)</a>:
```
const bigquery = new BigQuery();

```

Create a client with <a href="https://cloud.google.com/docs/authentication/production#obtaining_and_providing_service_account_credentials_manually">explicit credentials</a>:
```
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');

  async function createDataset() {
    // Creates a client
    const bigqueryClient = new BigQuery();

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

Members

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

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

Methods

buildQueryRequest_(query, options)

Check if the given Query can run using the jobs.query endpoint. Returns a bigquery.IQueryRequest that can be used to call jobs.query. Return undefined if is not possible to convert to a bigquery.IQueryRequest.

Parameters:

Name	Type	Description
`query`		string \| Query
`options`		QueryOptions

Returns:

Type	Description
	bigquery.IQueryRequest \| undefined

createQueryStream(query)

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

Parameters:

Name	Type	Description
`query`	object	Configuration object. See BigQuery.query for a complete list of options.

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

dataset(id, optionsopt)

Create a reference to a dataset.

Parameters:

Name Type Attributes Description

id

string

ID of the dataset.

options

object

Dataset options.

Properties

Name	Type	Attributes	Description
`projectId`	string	<optional>	The GCP project ID.
`location`	string	<optional>	The geographic location of the dataset. Required except for US and EU.

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.

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

job(id, optionsopt)

Create a reference to an existing job.

Parameters:

Name Type Attributes Description

id

string

ID of the job.

options

object

Configuration object.

Properties

Name	Type	Attributes	Description
`location`	string	<optional>	The geographic location of the job. Required except for US and EU.

Example

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

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

range(value, elementType)

A range represents contiguous range between two dates, datetimes, or timestamps. The lower and upper bound for the range are optional. The lower bound is inclusive and the upper bound is exclusive.

Parameters:

Name	Type	Description
`value`	string \| BigQueryRangeOptions	The range API string or start/end with dates/datetimes/timestamp ranges.
`elementType`	string	The range element type - DATE\|DATETIME\|TIMESTAMP

Example

```
const {BigQuery} = require('@google-cloud/bigquery');
const bigquery = new BigQuery();
const timestampRange = bigquery.range('[2020-10-01 12:00:00+08, 2020-12-31 12:00:00+08)', 'TIMESTAMP');
```

timestamp(value)

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

The recommended input here is a Date or PreciseDate class. If passing as a string, it should be Timestamp literals: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#timestamp_literals. When passing a number input, it should be epoch seconds in float representation.

Parameters:

Name	Type	Description
`value`	Date \| string \| string \| number	The time.

Example

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

(static) date(value)

The DATE type represents a logical calendar date, independent of time zone. It does not represent a specific 24-hour time period. Rather, a given DATE value represents a different 24-hour period when interpreted in different time zones, and may represent a shorter or longer day during Daylight Savings Time transitions.

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.

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.

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) datetime(value)

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.

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

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

Example

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

(static) int(value, typeCastOptions) → {BigQueryInt}

A BigQueryInt wraps 'INT64' values. Can be used to maintain precision.

Parameters:

Name	Type	Description
`value`	string \| number \| IntegerTypeCastValue	The INT64 value to convert.
`typeCastOptions`	IntegerTypeCastOptions	Configuration to convert value. Must provide an `integerTypeCastFunction` to handle conversion.

Returns:

Type	Description
BigQueryInt

Example

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

const largeIntegerValue = Number.MAX_SAFE_INTEGER + 1;

const options = {
  integerTypeCastFunction: value => value.split(),
};

const bqInteger = bigquery.int(largeIntegerValue, options);

const customValue = bqInteger.valueOf();
// customValue is the value returned from your `integerTypeCastFunction`.
```

(static) range(value, elementType)

A range represents contiguous range between two dates, datetimes, or timestamps. The lower and upper bound for the range are optional. The lower bound is inclusive and the upper bound is exclusive.

Parameters:

Name	Type	Description
`value`	string \| BigQueryRangeOptions	The range API string or start/end with dates/datetimes/timestamp ranges.
`elementType`	string	The range element type - DATE\|DATETIME\|TIMESTAMP

Example

```
const {BigQuery} = require('@google-cloud/bigquery');
const timestampRange = BigQuery.range('[2020-10-01 12:00:00+08, 2020-12-31 12:00:00+08)', 'TIMESTAMP');
```

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

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

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

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

Example

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