In the following examples from this page and the other modules (Dataset,Table, etc.), we are going to be using a dataset fromdata.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.
This client supports enabling query-related preview features via environmental variables. By setting the environment variable QUERY_PREVIEW_ENABLED to the string "TRUE", the client will enable preview features, though behavior may still be controlled via the bigquery service as well. Currently, the feature(s) in scope include: stateless queries (query execution without corresponding job metadata).
SeeWhat is BigQuery?
Package
@google-cloud/bigqueryExamples
Install the client library withnpm:
npminstall@google-cloud/bigquery
Import the client library
const{BigQuery}=require('@google-cloud/bigquery');
Create a client that usesApplication Default Credentials (ADC):
constbigquery=newBigQuery();
Create a client withexplicit credentials:
constbigquery=newBigQuery({projectId:'your-project-id',keyFilename:'/path/to/keyfile.json'});
Full quickstart example:
// Imports the Google Cloud client libraryconst{BigQuery}=require('@google-cloud/bigquery');asyncfunctioncreateDataset(){// Creates a clientconstbigqueryClient=newBigQuery();// Create the datasetconst[dataset]=awaitbigqueryClient.createDataset(datasetName);console.log(`Dataset${dataset.id} created.`);}createDataset();
Constructors
(constructor)(options)
constructor(options?:BigQueryOptions);
Constructs a new instance of theBigQuery class
Properties
location
setLogFunction
staticsetLogFunction:typeofsetLogFunction;
universeDomain
getuniverseDomain():string;
Methods
createDataset(id, options)
createDataset(id:string,options?:DatasetResource):Promise<DatasetResponse>;
const{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();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){constdataset=data[0];constapiResponse=data[1];});
createDataset(id, options, callback)
createDataset(id:string,options:DatasetResource,callback:DatasetCallback):void;
| Returns |
|---|
| Type | Description |
void | |
createDataset(id, callback)
createDataset(id:string,callback:DatasetCallback):void;
| 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 rawJob resource object.
SeeJobs Overview SeeJobs: insert API Documentation
| Returns |
|---|
| Type | Description |
Promise<JobResponse_2> | |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();constoptions={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){constjob=data[0];returnjob.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 aJob object that you must ping for the results. See the Job documentation for explanations of how to check on the status of the job.
SeeJobs: 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_2> | |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();constquery='SELECT url FROM `publicdata.samples.github_nested` LIMIT100';//-// You may pass only a query string, having a new table created to storethe// 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){constjob=data[0];constapiResponse=data[1];returnjob.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
|
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. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();constdataset=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. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constdate=BigQuery.date('2017-01-01');//-// Alternatively, provide an object.//-constdate2=BigQuery.date({year:2017,month:1,day:1});
date(value)
staticdate(value:BigQueryDateOptions|string):BigQueryDate;
TheDATE 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. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();constdate=bigquery.date('2017-01-01');//-// Alternatively, provide an object.//-constdate2=bigquery.date({year:2017,month:1,day:1});
datetime(value)
datetime(value:BigQueryDatetimeOptions|string):BigQueryDatetime;
datetime(value)
staticdatetime(value:BigQueryDatetimeOptions|string):BigQueryDatetime;
ADATETIME data type represents a point in time. Unlike aTIMESTAMP, 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. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();constdatetime=bigquery.datetime('2017-01-01 13:00:00');//-// Alternatively, provide an object.//-constdatetime=bigquery.datetime({year:2017,month:1,day:1,hours:14,minutes:0,seconds:0});
decodeIntegerValue_(value)
staticdecodeIntegerValue_(value:IntegerTypeCastValue):number;
Convert an INT64 value to Number.
| Returns |
|---|
| Type | Description |
number | |
geography(value)
geography(value:string):Geography;
| Parameter |
|---|
| Name | Description |
value | string
|
geography(value)
staticgeography(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. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();constgeography=bigquery.geography('POINT(1, 2)');
getDatasets(options)
getDatasets(options?:GetDatasetsOptions):Promise<DatasetsResponse>;
const{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();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`.//-functionmanualPaginationCallback(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;
| Returns |
|---|
| Type | Description |
void | |
getDatasets(callback)
getDatasets(callback:DatasetsCallback):void;
| Returns |
|---|
| Type | Description |
void | |
getDatasetsStream(options)
getDatasetsStream(options?:GetDatasetsOptions):ResourceStream<Dataset>;
| Returns |
|---|
| Type | Description |
ResourceStream<Dataset> | |
getJobs(options)
getJobs(options?:GetJobsOptions):Promise<GetJobsResponse>;
const{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();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`.//-functionmanualPaginationCallback(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){constjobs=data[0];});
getJobs(options, callback)
getJobs(options:GetJobsOptions,callback:GetJobsCallback):void;
| Returns |
|---|
| Type | Description |
void | |
getJobs(callback)
getJobs(callback:GetJobsCallback):void;
| Returns |
|---|
| Type | Description |
void | |
getJobsStream(options)
getJobsStream(options?:GetJobsOptions):ResourceStream<Job>;
| Returns |
|---|
| Type | Description |
ResourceStream<Job> | |
getTypeDescriptorFromProvidedType_(providedType)
staticgetTypeDescriptorFromProvidedType_(providedType:string|ProvidedTypeStruct|ProvidedTypeArray):ValueType;
Return a value's provided type.
| Returns |
|---|
| Type | Description |
ValueType | {string} The valid type provided. |
getTypeDescriptorFromValue_(value)
staticgetTypeDescriptorFromValue_(value:unknown):ValueType;
| 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;
int(value, typeCastOptions)
staticint(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 anintegerTypeCastFunction to handle conversion. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();constlargeIntegerValue=Number.MAX_SAFE_INTEGER+1;constoptions={integerTypeCastFunction:value=>value.split(),};constbqInteger=bigquery.int(largeIntegerValue,options);constcustomValue=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 | |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();constmyExistingJob=bigquery.job('job-id');
mergeSchemaWithRows_(schema, rows, options)
staticmergeSchemaWithRows_(schema:TableSchema|TableField|undefined,rows:TableRow[],options:{wrapIntegers:boolean|IntegerTypeCastOptions;selectedFields?:string[];parseJSON?:boolean;}):any[];
Merge a rowset returned from the API with a table schema.
| Returns |
|---|
| Type | Description |
any[] | Fields using their matching names from the table's schema. |
query(query, options)
query(query:string,options?:QueryOptions):Promise<QueryRowsResponse>;
| Parameters |
|---|
| Name | Description |
query | string
A string SQL query or configuration object. For all available options, seeJobs: query request body. |
options | QueryOptions
Configuration object for query results. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();constquery='SELECT url FROM `publicdata.samples.github_nested` LIMIT100';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){constrows=data[0];});
query(query, options)
query(query:Query,options?:QueryOptions):Promise<SimpleQueryRowsResponse>;
query(query, options, callback)
query(query:string,options:QueryOptions,callback?:QueryRowsCallback):void;
| Returns |
|---|
| Type | Description |
void | |
query(query, options, callback)
query(query:Query,options:QueryOptions,callback?:SimpleQueryRowsCallback):void;
| Returns |
|---|
| Type | Description |
void | |
query(query, callback)
query(query:string,callback?:QueryRowsCallback):void;
| Returns |
|---|
| Type | Description |
void | |
query(query, callback)
query(query:Query,callback?:SimpleQueryRowsCallback):void;
| Returns |
|---|
| Type | Description |
void | |
queryAsStream_(query, callback)
queryAsStream_(query:Query,callback?:SimpleQueryRowsCallback):void;
This method will be called bycreateQueryStream(). It is required to properly set theautoPaginate option value.
| Returns |
|---|
| Type | Description |
void | |
range(value, elementType)
range(value:string,elementType?:string):BigQueryRange;
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 | Description |
value | string
The range API string or start/end with dates/datetimes/timestamp ranges. |
elementType | string
The range element type - DATE|DATETIME|TIMESTAMP |
| Returns |
|---|
| Type | Description |
BigQueryRange | |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();consttimestampRange=bigquery.range('[2020-10-01 12:00:00+08, 2020-12-31 12:00:00+08)','TIMESTAMP');
range(value, elementType)
staticrange(value:string|BigQueryRangeOptions,elementType?:string):BigQueryRange;
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.
BigQuery.range
| Parameters |
|---|
| Name | 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 |
| Returns |
|---|
| Type | Description |
BigQueryRange | |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');consttimestampRange=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)
statictime(value:BigQueryTimeOptions|string):BigQueryTime;
ATIME 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. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();consttime=bigquery.time('14:00:00');// 2:00 PM//-// Alternatively, provide an object.//-consttime=bigquery.time({hours:14,minutes:0,seconds:0});
timestamp(value)
timestamp(value:Date|PreciseDate|string|number):BigQueryTimestamp;
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 aDate orPreciseDate class. If passing as astring, it should be Timestamp literals: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#timestamp_literals. When passing anumber input, it should be epoch seconds in float representation.
| Parameter |
|---|
| Name | Description |
value | Date |PreciseDate | string | number
The time. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');constbigquery=newBigQuery();consttimestamp=bigquery.timestamp(newDate());
timestamp(value)
statictimestamp(value:Date|PreciseDate|string|number):BigQueryTimestamp;
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 aDate orPreciseDate class. If passing as astring, it should be Timestamp literals: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#timestamp_literals. When passing anumber input, it should be epoch seconds in float representation.
BigQuery.timestamp
| Parameter |
|---|
| Name | Description |
value | Date |PreciseDate | string | number
The time. |
Exampleconst{BigQuery}=require('@google-cloud/bigquery');consttimestamp=BigQuery.timestamp(newDate());
valueToQueryParameter_(value, providedType)
staticvalueToQueryParameter_(value:any,providedType?:string|ProvidedTypeStruct|ProvidedTypeArray):bigquery.IQueryParameter;
| Returns |
|---|
| Type | Description |
bigquery.IQueryParameter | {object} A properly-formedqueryParameter object. |
