Class BigQuery (7.7.0)

Package

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

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

Constructors

(constructor)(options)

constructor(options?: BigQueryOptions);

Properties

location

location?: string;

setLogFunction

static setLogFunction: typeof setLogFunction;

universeDomain

get universeDomain(): string;

Methods

createDataset(id, options)

createDataset(id: string, options?: DatasetResource): Promise<DatasetResponse>;

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

createDataset(id, options, callback)

createDataset(id: string, options: DatasetResource, callback: DatasetCallback): void;

createDataset(id, callback)

createDataset(id: string, callback: DatasetCallback): void;

createJob(options)

createJob(options: JobOptions): Promise<JobResponse>;

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

createJob(options, callback)

createJob(options: JobOptions, callback: JobCallback): void;

createQueryJob(options)

createQueryJob(options: Query | string): Promise<JobResponse>;

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

createQueryJob(options, callback)

createQueryJob(options: Query | string, callback: JobCallback): void;

createQueryStream(options)

createQueryStream(options?: Query | string): ResourceStream<RowMetadata>;

dataset(id, options)

dataset(id: string, options?: DatasetOptions): Dataset;

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

date(value)

date(value: BigQueryDateOptions | string): BigQueryDate;

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

date(value)

static date(value: BigQueryDateOptions | string): BigQueryDate;

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

datetime(value: BigQueryDatetimeOptions | string): BigQueryDatetime;

datetime(value)

static datetime(value: BigQueryDatetimeOptions | string): BigQueryDatetime;

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

decodeIntegerValue_(value)

static decodeIntegerValue_(value: IntegerTypeCastValue): number;

geography(value)

geography(value: string): Geography;

geography(value)

static geography(value: string): Geography;

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

getDatasets(options)

getDatasets(options?: GetDatasetsOptions): Promise<DatasetsResponse>;

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

getDatasets(options, callback)

getDatasets(options: GetDatasetsOptions, callback: DatasetsCallback): void;

getDatasets(callback)

getDatasets(callback: DatasetsCallback): void;

getDatasetsStream(options)

getDatasetsStream(options?: GetDatasetsOptions): ResourceStream<Dataset>;

getJobs(options)

getJobs(options?: GetJobsOptions): Promise<GetJobsResponse>;

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

getJobs(options, callback)

getJobs(options: GetJobsOptions, callback: GetJobsCallback): void;

getJobs(callback)

getJobs(callback: GetJobsCallback): void;

getJobsStream(options)

getJobsStream(options?: GetJobsOptions): ResourceStream<Job>;

getTypeDescriptorFromProvidedType_(providedType)

static getTypeDescriptorFromProvidedType_(providedType: string | ProvidedTypeStruct | ProvidedTypeArray): ValueType;

getTypeDescriptorFromValue_(value)

static getTypeDescriptorFromValue_(value: unknown): ValueType;

int(value, typeCastOptions)

int(value: string | number | IntegerTypeCastValue, typeCastOptions?: IntegerTypeCastOptions): BigQueryInt;

int(value, typeCastOptions)

static int(value: string | number | IntegerTypeCastValue, typeCastOptions?: IntegerTypeCastOptions): BigQueryInt;

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

job(id, options)

job(id: string, options?: JobOptions): Job;

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

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

mergeSchemaWithRows_(schema, rows, options)

static mergeSchemaWithRows_(schema: TableSchema | TableField, rows: TableRow[], options: {
        wrapIntegers: boolean | IntegerTypeCastOptions;
        selectedFields?: string[];
        parseJSON?: boolean;
    }): any[];

query(query, options)

query(query: string, options?: QueryOptions): Promise<QueryRowsResponse>;

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

//-
// Providing types for SQL parameters is supported.
//-
bigquery.query({
  query: [
    'SELECT url',
    'FROM `publicdata.samples.github_nested`',
    'WHERE repository.owner = ?'
  ].join(' '),

  params: [
    null
  ],

  types: ['string']
}, 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];
});

query(query, options)

query(query: Query, options?: QueryOptions): Promise<SimpleQueryRowsResponse>;

query(query, options, callback)

query(query: string, options: QueryOptions, callback?: QueryRowsCallback): void;

query(query, options, callback)

query(query: Query, options: QueryOptions, callback?: SimpleQueryRowsCallback): void;

query(query, callback)

query(query: string, callback?: QueryRowsCallback): void;

query(query, callback)

query(query: Query, callback?: SimpleQueryRowsCallback): void;

queryAsStream_(query, callback)

queryAsStream_(query: Query, callback?: SimpleQueryRowsCallback): void;

range(value, elementType)

range(value: string, elementType?: string): BigQueryRange;

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

range(value, elementType)

static range(value: string | BigQueryRangeOptions, elementType?: string): BigQueryRange;

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

time(value)

time(value: BigQueryTimeOptions | string): BigQueryTime;

time(value)

static time(value: BigQueryTimeOptions | string): BigQueryTime;

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)

timestamp(value: Date | PreciseDate | string | number): BigQueryTimestamp;

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

timestamp(value)

static timestamp(value: Date | PreciseDate | string | number): BigQueryTimestamp;

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

valueToQueryParameter_(value, providedType)

static valueToQueryParameter_(value: any, providedType?: string | ProvidedTypeStruct | ProvidedTypeArray): bigquery.IQueryParameter;