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).
See What is 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
Properties
location
setLogFunction
static setLogFunction: typeof setLogFunction;
universeDomain
get universeDomain(): string;
Methods
createDataset(id, options)
createDataset(id: string, options?: DatasetResource): 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;
| 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 raw Job resource object.
See Jobs Overview See Jobs: insert API Documentation
| Returns |
|---|
| Type | Description |
Promise<JobResponse_2> | |
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_2> | |
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
|
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.
|
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.
|
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.
|
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;
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.
|
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.
| Returns |
|---|
| Type | Description |
number | |
geography(value)
geography(value: string): Geography;
| Parameter |
|---|
| Name | Description |
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.
| Parameter |
|---|
| Name | Description |
value |
string
The geospatial data.
|
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>;
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;
| 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>;
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;
| 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)
static getTypeDescriptorFromProvidedType_(providedType: string | ProvidedTypeStruct | ProvidedTypeArray): ValueType;
Return a value's provided type.
| Returns |
|---|
| Type | Description |
ValueType | {string} The valid type provided.
|
getTypeDescriptorFromValue_(value)
static getTypeDescriptorFromValue_(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)
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.
|
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, options)
static mergeSchemaWithRows_(schema: TableSchema | TableField, 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, see Jobs: query request body.
|
options |
QueryOptions
Configuration object for query results.
|
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>;
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 by createQueryStream(). It is required to properly set the autoPaginate option value.
| Returns |
|---|
| Type | Description |
void | |
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.
| 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.
|
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 | 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.
| Parameter |
|---|
| Name | Description |
value |
Date | PreciseDate | string | number
The time.
|
Example
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
| Parameter |
|---|
| Name | Description |
value |
Date | PreciseDate | string | number
The time.
|
Example
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;
| Returns |
|---|
| Type | Description |
bigquery.IQueryParameter | {object} A properly-formed queryParameter object.
|
