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

  1. In the Google Cloud console, go to the BigQuery page.

    Go to BigQuery

  2. Click on the editor tab that contains the session.

  3. 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"
    }
  }
}

What's next