Running queries in sessions
This document describes how to run queries in a BigQuery session. It is intended for users who already have a general understanding of sessions, have already created a session, and know the ID for the session they want to use.
Before you complete these steps, ensure you have the necessary permissions to create a session.
Run a query in a session
After you create a session, you can add a query to the session and run it. There are a few ways that you can do this:
Console
In the Google Cloud console, go to the BigQuery page.
Click on the editor tab that contains the session.
Add your query to the session and click Run.
Test your session
In the Google Cloud console, open an editor tab that contains a session you created and execute this SQL statement:
CREATE TEMP TABLE Temperature(degrees INT64) AS SELECT * FROM UNNEST([30,0,3,14,55]) AS a;
Delete or comment out the contents in the tab and execute this SQL statement:
SELECT * FROM Temperature;
Make sure that results similar to these are produced:
+---------+
| degrees |
+---------+
| 55 |
| 14 |
| 3 |
| 0 |
| 30 |
+---------+
bq
Open the Cloud Shell
and enter the following
bq query
command:
bq query \ --nouse_legacy_sql \ --session_id=SESSION_ID \ 'SQL_STATEMENT'
where:
- SESSION_ID: Replace this with the ID of the session you want to work with.
- SQL_STATEMENT: A SQL statement to run in your session.
Test your session
Create a temporary table called Temperature
in a
session you created. Replace the
session ID with your own.
bq query \
--nouse_legacy_sql \
--session_id=CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ \
'CREATE TEMP TABLE Temperature(degrees INT64) AS SELECT * FROM UNNEST([30,0,3,14,55]) AS a;'
Get a list of all values in the Temperature
table. Replace the
session ID with your own.
bq query \
--nouse_legacy_sql \
--session_id=CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ \
'SELECT * FROM Temperature;'
Make sure that results similar to these are produced:
+-------+
| total |
+-------+
| 30 |
| 0 |
| 3 |
| 14 |
| 55 |
+-------+
In session: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ
Save time by adding your session to .bigqueryrc
If you are going to run lots of queries with the Cloud Shell,
you can add your session ID to [query]
in
.bigqueryrc
so that you do not need to copy and paste the session ID into each command.
This is what a session ID looks like in .bigqueryrc
:
[query]
--session_id=CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ
After you've added the session ID to .bigqueryrc
, you can use this command
instead:
bq query \
--nouse_legacy_sql \
'SELECT * FROM Temperature;'
Be aware that if you want to use a different session or if a session
terminates, you must update .bigqueryrc
.
API
Call the jobs.insert method or jobs.query method with a defined jobs resource.
jobs.insert
Call the
jobs.insert
method with
the following parameters:
{ "jobReference": { "projectId": "PROJECT_ID", "jobId": "JOB_ID" }, "configuration": { "query": { "query": "SQL_STATEMENT", "connectionProperties": [{ "key": "session_id", "value": "SESSION_ID" }] } } }
where:
- PROJECT_ID: The project for which to create the session.
- JOB_ID: The job for which to create the session.
- SQL_STATEMENT: The first SQL statement for your session.
- SESSION_ID: The ID of the session.
Test your session
Enter the following request body to create a temporary table called
Temperature
in a session you created.
Replace the project ID, job ID, and session ID with your own.
{
"jobReference": {
"projectId": "myProject",
"jobId": "job_123"
},
"configuration": {
"query": {
"query": "CREATE TEMP TABLE Temperature(degrees INT64) AS SELECT * FROM UNNEST([30,0,3,14,55]) AS a;",
"connectionProperties": [{
"key": "session_id",
"value": "CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ"
}]
}
}
}
Enter the following request body to get a list of all values in the
Temperature
table in a session. Replace the project ID, job ID, and
session ID with your own.
{
"jobReference": {
"projectId": "myProject",
"jobId": "job_123"
},
"configuration": {
"query": {
"query": "SELECT * FROM Temperature;",
"connectionProperties": [{
"key": "session_id",
"value": "CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ"
}]
}
}
}
Make sure that the response body for both requests is similar to this:
{
"jobReference": {
"projectId": "myProject",
"jobId": "job_123"
},
"statistics": {
"sessionInfo": {
"sessionId": "CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ"
}
}
}
jobs.query
Call the
jobs.query
method with
the following parameters:
{ "query": "SQL_STATEMENT", "connectionProperties": [{ "key": "session_id", "value": "SESSION_ID" }] }
where:
- SQL_STATEMENT: The first SQL statement for your session.
- SESSION_ID: The ID of the session.
Test your session
Enter the following request body to create a temporary table called
Temperature
in a session you created.
Replace the project ID, job ID, and session ID with your own.
{
"query": "CREATE TEMP TABLE Temperature(degrees INT64) AS SELECT * FROM UNNEST([30,0,3,14,55]) AS a;",
"connectionProperties": [{
"key": "session_id",
"value": "CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ"
}]
}
Enter the following request body to get a list of all values in the
Temperature
table in a session. Replace the project ID, job ID, and
session ID with your own.
{
"query": "SELECT * FROM Temperature;",
"connectionProperties": [{
"key": "session_id",
"value": "CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ"
}]
}
Make sure that the response body for both requests is similar to this:
{
"jobReference": {
"projectId": "myProject",
"jobId": "job_123"
},
"statistics": {
"sessionInfo": {
"sessionId": "CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ"
}
}
}