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.
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
;
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.
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.
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.
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 your 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>
;
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>
;
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.
Job
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.
schema
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.
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
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
|
string
)
:
BigQueryTimestamp
;
value
Date
| string
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.
value
Date
| string
The time.
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.
value
any
The value.
providedType
bigquery.IQueryParameter
{object} A properly-formed queryParameter
object.
