Class BigQuery (5.12.0)

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.

See What is BigQuery?

Inheritance

Service > BigQuery

Package

@google-cloud/bigquery

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

Constructs a new instance of the BigQuery class

Parameter

Name	Description
options	`BigQueryOptions`

Properties

location

  location 
 ?: 
  
 string 
 ;

Methods

createDataset(id, options)

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

Create a dataset.

See Datasets: insert API Documentation

Parameters

Name	Description
id	`string` ID of the dataset to create.
options	`DatasetResource` See a Dataset resource .

Returns

Type	Description
Promise < DatasetResponse >

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

createDataset(id, options, callback)

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

Parameters

Name	Description
id	`string`
options	`DatasetResource`
callback	`DatasetCallback`

Returns

Type	Description
void

createDataset(id, callback)

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

Parameters

Name	Description
id	`string`
callback	`DatasetCallback`

Returns

Type	Description
void

createJob(options)

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

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.

See Jobs Overview See Jobs: insert API Documentation

Parameter

Name	Description
options	`JobOptions` Object in the form of a Job resource ;

Returns

Type	Description
Promise < JobResponse >

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

createJob(options, callback)

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

Parameters

Name	Description
options	`JobOptions`
callback	`JobCallback`

Returns

Type	Description
void

createQueryJob(options)

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

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 Jobs: insert API Documentation

Parameter

Name	Description
options	`Query \| 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.

Returns

Type	Description
Promise < JobResponse >

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 
 ) 
  
 { 
  
 const 
  
 job 
  
 = 
  
 data 
 [ 
 0 
 ]; 
  
 const 
  
 apiResponse 
  
 = 
  
 data 
 [ 
 1 
 ]; 
  
 return 
  
  job 
 
 . 
  getQueryResults 
 
 (); 
 });

createQueryJob(options, callback)

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

Parameters

Name	Description
options	`Query \| string`
callback	`JobCallback`

Returns

Type	Description
void

createQueryStream(options)

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

Parameter

Name	Description
options	`Query \| string`

Returns

Type	Description
ResourceStream < RowMetadata >

dataset(id, options)

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

Create a reference to a dataset.

Parameters

Name	Description
id	`string` ID of the dataset.
options	`DatasetOptions` Dataset options.

Returns

Type	Description
Dataset

Example

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

date(value)

  date 
 ( 
 value 
 : 
  
 BigQueryDateOptions 
  
 | 
  
 string 
 ) 
 : 
  
 BigQueryDate 
 ;

Parameter

Name	Description
value	`BigQueryDateOptions \| string` The date. If a string, this should be in the format the API describes: `YYYY-[M]M-[D]D` . Otherwise, provide an object.

Returns

Type	Description
BigQueryDate

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

date(value)

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

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

Name	Description
value	`BigQueryDateOptions \| string` The date. If a string, this should be in the format the API describes: `YYYY-[M]M-[D]D` . Otherwise, provide an object.

Returns

Type	Description
BigQueryDate

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

  datetime 
 ( 
 value 
 : 
  
 BigQueryDatetimeOptions 
  
 | 
  
 string 
 ) 
 : 
  
 BigQueryDatetime 
 ;

Parameter

Name	Description
value	`BigQueryDatetimeOptions \| string`

Returns

Type	Description
BigQueryDatetime

datetime(value)

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

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

Name	Description
value	`BigQueryDatetimeOptions \| 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.

Returns

Type	Description
BigQueryDatetime

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

decodeIntegerValue_(value)

  static 
  
 decodeIntegerValue_ 
 ( 
 value 
 : 
  
 IntegerTypeCastValue 
 ) 
 : 
  
 number 
 ;

Convert an INT64 value to Number.

Parameter

Name	Description
value	`IntegerTypeCastValue` The INT64 value to convert.

Returns

Type	Description
number

geography(value)

  geography 
 ( 
 value 
 : 
  
 string 
 ) 
 : 
  
 Geography 
 ;

Parameter

Name	Description
value	`string`

Returns

Type	Description
Geography

geography(value)

  static 
  
 geography 
 ( 
 value 
 : 
  
 string 
 ) 
 : 
  
 Geography 
 ;

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

Parameter

Name	Description
value	`string` The geospatial data.

Returns

Type	Description
Geography

Example

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

getDatasets(options)

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

List all or some of the datasets in your project.

See Datasets: list API Documentation

Parameter

Name	Description
options	`GetDatasetsOptions` Configuration object.

Returns

Type	Description
Promise < DatasetsResponse >

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

getDatasets(options, callback)

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

Parameters

Name	Description
options	`GetDatasetsOptions`
callback	`DatasetsCallback`

Returns

Type	Description
void

getDatasets(callback)

  getDatasets 
 ( 
 callback 
 : 
  
 DatasetsCallback 
 ) 
 : 
  
 void 
 ;

Parameter

Name	Description
callback	`DatasetsCallback`

Returns

Type	Description
void

getDatasetsStream(options)

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

Parameter

Name	Description
options	`GetDatasetsOptions`

Returns

Type	Description
ResourceStream < Dataset >

getJobs(options)

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

Get all of the jobs from your project.

See Jobs: list API Documentation

Parameter

Name	Description
options	`GetJobsOptions` Configuration object.

Returns

Type	Description
Promise < GetJobsResponse >

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

getJobs(options, callback)

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

Parameters

Name	Description
options	`GetJobsOptions`
callback	`GetJobsCallback`

Returns

Type	Description
void

getJobs(callback)

  getJobs 
 ( 
 callback 
 : 
  
 GetJobsCallback 
 ) 
 : 
  
 void 
 ;

Parameter

Name	Description
callback	`GetJobsCallback`

Returns

Type	Description
void

getJobsStream(options)

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

Parameter

Name	Description
options	`GetJobsOptions`

Returns

Type	Description
ResourceStream < Job >

getTypeDescriptorFromProvidedType_(providedType)

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

Return a value's provided type.

Parameter

Name	Description
providedType	`string \| ProvidedTypeStruct \| ProvidedTypeArray` The type.

Returns

Type	Description
ValueType	{string} The valid type provided.

getTypeDescriptorFromValue_(value)

  static 
  
 getTypeDescriptorFromValue_ 
 ( 
 value 
 : 
  
 unknown 
 ) 
 : 
  
 ValueType 
 ;

Detect a value's type.

Parameter

Name	Description
value	`unknown` The value.

Returns

Type	Description
ValueType	{string} The type detected from the value.

int(value, typeCastOptions)

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

Parameters

Name	Description
value	`string \| number \| IntegerTypeCastValue`
typeCastOptions	`IntegerTypeCastOptions`

Returns

Type	Description
BigQueryInt

int(value, typeCastOptions)

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

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

Parameters

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

job(id, options)

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

Create a reference to an existing job.

Parameters

Name	Description
id	`string` ID of the job.
options	`JobOptions` Configuration object.

Returns

Type	Description
Job

Example

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

mergeSchemaWithRows_(schema, rows, wrapIntegers, selectedFields)

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

Merge a rowset returned from the API with a table schema.

Parameters

Name	Description
schema	`TableSchema \| TableField`
rows	`TableRow []`
wrapIntegers	`boolean \| IntegerTypeCastOptions` Wrap values of 'INT64' type in BigQueryInt objects. If a `boolean` , this will wrap values in BigQueryInt objects. If an `object` , this will return a value returned by `wrapIntegers.integerTypeCastFunction` . Please see IntegerTypeCastOptions for options descriptions.
selectedFields	`string[]` List of fields to return. If unspecified, all fields are returned.

Returns

Type	Description
any[]	Fields using their matching names from the table's schema.

query(query, options)

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

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

See Jobs: query API Documentation

Parameters

Name	Description
query	`string` A string SQL query or configuration object. For all available options, see Jobs: query request body .
options	`QueryOptions` Configuration object for query results.

Returns

Type	Description
Promise < QueryRowsResponse >

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

Parameters

Name	Description
query	`Query`
options	`QueryOptions`

Returns

Type	Description
Promise < SimpleQueryRowsResponse >

query(query, options, callback)

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

Parameters

Name	Description
query	`string`
options	`QueryOptions`
callback	`QueryRowsCallback`

Returns

Type	Description
void

query(query, options, callback)

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

Parameters

Name	Description
query	`Query`
options	`QueryOptions`
callback	`SimpleQueryRowsCallback`

Returns

Type	Description
void

query(query, callback)

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

Parameters

Name	Description
query	`string`
callback	`QueryRowsCallback`

Returns

Type	Description
void

query(query, callback)

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

Parameters

Name	Description
query	`Query`
callback	`SimpleQueryRowsCallback`

Returns

Type	Description
void

queryAsStream_(query, callback)

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

This method will be called by createQueryStream() . It is required to properly set the autoPaginate option value.

Parameters

Name	Description
query	`Query`
callback	`SimpleQueryRowsCallback`

Returns

Type	Description
void

time(value)

  time 
 ( 
 value 
 : 
  
 BigQueryTimeOptions 
  
 | 
  
 string 
 ) 
 : 
  
 BigQueryTime 
 ;

Parameter

Name	Description
value	`BigQueryTimeOptions \| string`

Returns

Type	Description
BigQueryTime

time(value)

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

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

Parameter

Name	Description
value	`BigQueryTimeOptions \| 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.

Returns

Type	Description
BigQueryTime

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)

  timestamp 
 ( 
 value 
 : 
  
 Date 
  
 | 
  
 string 
 ) 
 : 
  
 BigQueryTimestamp 
 ;

Parameter

Name	Description
value	`Date \| string`

Returns

Type	Description
BigQueryTimestamp

timestamp(value)

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

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

Parameter

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

Returns

Type	Description
BigQueryTimestamp

Example

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

valueToQueryParameter_(value, providedType)

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

Convert a value into a queryParameter object.

See Jobs.query API Reference Docs (see `queryParameters`)

Parameters

Name	Description
value	`any` The value.
providedType	`string \| ProvidedTypeStruct \| ProvidedTypeArray` Provided query parameter type.

Returns

Type	Description
bigquery.IQueryParameter	{object} A properly-formed `queryParameter` object.

Class BigQuery (5.12.0) Stay organized with collections Save and categorize content based on your preferences.

Inheritance

Package

Examples

Constructors

(constructor)(options)

Properties

location

Methods

createDataset(id, options)

createDataset(id, options, callback)

createDataset(id, callback)

createJob(options)

createJob(options, callback)

createQueryJob(options)

createQueryJob(options, callback)

createQueryStream(options)

dataset(id, options)

date(value)

date(value)

datetime(value)

datetime(value)

decodeIntegerValue_(value)

geography(value)

geography(value)

getDatasets(options)

getDatasets(options, callback)

getDatasets(callback)

getDatasetsStream(options)

getJobs(options)

getJobs(options, callback)

getJobs(callback)

getJobsStream(options)

getTypeDescriptorFromProvidedType_(providedType)

getTypeDescriptorFromValue_(value)

int(value, typeCastOptions)

int(value, typeCastOptions)

job(id, options)

mergeSchemaWithRows_(schema, rows, wrapIntegers, selectedFields)

query(query, options)

query(query, options)

query(query, options, callback)

query(query, options, callback)

query(query, callback)

query(query, callback)

queryAsStream_(query, callback)

time(value)

time(value)

timestamp(value)

timestamp(value)

valueToQueryParameter_(value, providedType)

Class BigQuery (5.12.0)