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.
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).
Package
@google-cloud/bigqueryExamples
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
Properties
location
location
?:
string
;
setLogFunction
static
setLogFunction
:
typeof
setLogFunction
;
universeDomain
get
universeDomain
()
:
string
;
Methods
createDataset(id, options)
createDataset
(
id
:
string
,
options
?:
DatasetResource
)
:
Promise<DatasetResponse>
;
Create a dataset.
id
string
ID of the dataset to create.
options
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
;
void
createDataset(id, callback)
createDataset
(
id
:
string
,
callback
:
DatasetCallback
)
:
void
;
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.
Promise
< JobResponse_2
>
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
;
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.
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.
Promise
< JobResponse_2
>
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
;
void
createQueryStream(options)
createQueryStream
(
options
?:
Query
|
string
)
:
ResourceStream<RowMetadata>
;
dataset(id, options)
dataset
(
id
:
string
,
options
?:
DatasetOptions
)
:
Dataset
;
Create a reference to a dataset.
const
{
BigQuery
}
=
require
(
' @google-cloud/bigquery
'
);
const
bigquery
=
new
BigQuery
();
const
dataset
=
bigquery
.
dataset
(
'higher_education'
);
date(value)
date
(
value
:
BigQueryDateOptions
|
string
)
:
BigQueryDate
;
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.
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.
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.
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
;
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.
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.
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.
number
geography(value)
geography
(
value
:
string
)
:
Geography
;
value
string
geography(value)
static
geography
(
value
:
string
)
:
Geography
;
A geography value represents a surface area on the Earth in Well-known Text (WKT) format.
value
string
The geospatial data.
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 a project.
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
;
void
getDatasets(callback)
getDatasets
(
callback
:
DatasetsCallback
)
:
void
;
void
getDatasetsStream(options)
getDatasetsStream
(
options
?:
GetDatasetsOptions
)
:
ResourceStream<Dataset>
;
getJobs(options)
getJobs
(
options
?:
GetJobsOptions
)
:
Promise<GetJobsResponse>
;
Get all of the jobs from your project.
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
;
void
getJobs(callback)
getJobs
(
callback
:
GetJobsCallback
)
:
void
;
void
getJobsStream(options)
getJobsStream
(
options
?:
GetJobsOptions
)
:
ResourceStream<Job>
;
getTypeDescriptorFromProvidedType_(providedType)
static
getTypeDescriptorFromProvidedType_
(
providedType
:
string
|
ProvidedTypeStruct
|
ProvidedTypeArray
)
:
ValueType
;
Return a value's provided type.
getTypeDescriptorFromValue_(value)
static
getTypeDescriptorFromValue_
(
value
:
unknown
)
:
ValueType
;
Detect a value's type.
value
unknown
The value.
int(value, typeCastOptions)
int
(
value
:
string
|
number
|
IntegerTypeCastValue
,
typeCastOptions
?:
IntegerTypeCastOptions
)
:
BigQueryInt
;
int(value, typeCastOptions)
static
int
(
value
:
string
|
number
|
IntegerTypeCastValue
,
typeCastOptions
?:
IntegerTypeCastOptions
)
:
BigQueryInt
;
A BigQueryInt wraps 'INT64' values. Can be used to maintain precision.
value
typeCastOptions
IntegerTypeCastOptions
Configuration to convert value. Must provide an integerTypeCastFunction
to handle conversion.
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.
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
|
undefined
,
rows
:
TableRow
[],
options
:
{
wrapIntegers
:
boolean
|
IntegerTypeCastOptions
;
selectedFields
?:
string
[];
parseJSON
?:
boolean
;
})
:
any
[];
Merge a rowset returned from the API with a table schema.
schema
TableSchema
| TableField
| undefined
rows
TableRow
[]
options
{
wrapIntegers: boolean | IntegerTypeCastOptions
;
selectedFields?: string[];
parseJSON?: boolean;
}
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 .
query
string
A string SQL query or configuration object. For all available options, see Jobs: query request body .
options
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
;
void
query(query, options, callback)
query
(
query
:
Query
,
options
:
QueryOptions
,
callback
?:
SimpleQueryRowsCallback
)
:
void
;
void
query(query, callback)
query
(
query
:
string
,
callback
?:
QueryRowsCallback
)
:
void
;
void
query(query, callback)
query
(
query
:
Query
,
callback
?:
SimpleQueryRowsCallback
)
:
void
;
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.
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.
value
string
The range API string or start/end with dates/datetimes/timestamp ranges.
elementType
string
The range element type - DATE|DATETIME|TIMESTAMP
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
;
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
value
string | BigQueryRangeOptions
The range API string or start/end with dates/datetimes/timestamp ranges.
elementType
string
The range element type - DATE|DATETIME|TIMESTAMP
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
;
A TIME
data type represents a time, independent of a specific date.
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.
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
;
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.
value
Date
| PreciseDate
| string | number
The time.
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
;
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.
BigQuery.timestamp
value
Date
| PreciseDate
| string | number
The time.
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
;
Convert a value into a queryParameter
object.
value
any
The value.
providedType
bigquery.IQueryParameter
{object} A properly-formed queryParameter
object.
