Creating sessions

This document describes how to create sessions in BigQuery. It is intended for users who are familiar with BigQuery sessions.

Create a session

If you would like to capture a group of your SQL activities, create a BigQuery session. After creating a session, you can run interactive queries in your session until the session terminates. All queries in the session are run (processed) in the location where the session was created.

Before you complete these steps, ensure you have the necessary permissions to create a session.

Console

In the Google Cloud console, each session is assigned to an editor tab.

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

    Go to BigQuery

  2. Click Compose new query. A new editor tab opens.

  3. Click More > Query settings. The Query settings panel appears.

  4. In the Session management section, click Use session mode to enable the session mode.

  5. In Additional settings > Data location, select the location. After the session is created, all queries in the session are restricted to this location and the location cannot be changed.

  6. Click Save.

  7. Write a query in the editor tab and run it. The new session is created after this first query is run.

bq

Open the Cloud Shell and enter the following bq query command:

bq query \
--nouse_legacy_sql \
--create_session
[--location 'SESSION_LOCATION'] \
'SQL_STATEMENT'

where:

  • SESSION_LOCATION: Bind the session to a physical location. Restrict all queries in the session to this location. Optional.
  • SQL_STATEMENT: The first SQL statement for your session.

Test your session

Create a session along with a temporary table with this command:

bq query \
  --nouse_legacy_sql \
  --create_session \
  --location 'asia-northeast1' \
  'SELECT 1;'

Make sure that results similar to these are produced:

In session: CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ

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",
    ["location": "SESSION_LOCATION"]
  },
  "configuration": {
    "query": {
      "query": "SQL_STATEMENT",
       "createSession": {
         "value": true
       }
    }
  }
}

where:

  • PROJECT_ID: The project for which to create the session.
  • JOB_ID: The job for which to create the session.
  • SESSION_LOCATION: Bind the session to a physical location. Restrict all queries in the session to this location. Optional.
  • SQL_STATEMENT: The first SQL statement for your session.

Test your session

Enter the following request body. Replace myProject and job_123 with the ID of a project and the ID of a job.

{
  "jobReference": {
    "projectId": "myProject",
    "jobId": "job_123"
  },
  "configuration": {
    "query": {
      "query": "SELECT 1;",
      "createSession": {
          "value": true
      }
    }
  }
}

Make sure that the response body 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",
  "createSession": true,
  ["location": "SESSION_LOCATION"]
}

where:

  • SQL_STATEMENT: The first SQL statement for your session.
  • SESSION_LOCATION: Bind the session to a physical location. Restrict all queries in the session to this location. Optional.

Test your session

Enter the following request body. Replace myProject and job_123 with the ID of a project and the ID of a job.

{
  "query": "SELECT 1;",
  "createSession": true
}

Make sure that the response body is similar to this:

{
  "jobReference": {
    "projectId": "myProject",
    "jobId": "job_123"
  },
  "statistics": {
    "sessionInfo": {
      "sessionId": "CgwKCmZhbGl1LXRlc3QQARokMDAzYjI0OWQtZ"
    }
  }
}

What's next