Does anyone know how to open another query within a same session in BigQuery using node.js?

Question

 public static async createSession(): Promise<any>{           
      const options = {
        query: `BEGIN TRANSACTION;`,
        createSession: true,
        location: "EU"
      }
      const [jobSession] = await bigquery.createQueryJob(options);
      let data = await jobSession.getMetadata()
      let sessionId = data[0].statistics.sessionInfo.sessionId
      this.queryInSession(sessionId);
  }



public static async queryInSession(sessionId: string): Promise<any> {
      const options = {
        "configuration": {
          "query": {
            "query": `UPDATE table SET ORGANISATION='new' WHERE ROWID='2'`,
            "connectionProperties": [{
              "key": "session_id",
              "value": sessionId
            }]
          }
        }
      }
      const [job] = await bigquery.createJob(options);
      const [rows] = await job.getQueryResults();
      return rows
  }

I am creating a session with the first function, and with the second I want to create a job within the same session. But this is giving me error "Use of 'session_id' connection property in legacy SQL is not supported" Is this approach correct? If yes, how to do it?

Answer 1

Add useLegacySql: false, in query.

It should be like this

public static async queryInSession(sessionId: string): Promise<any> {
const options = {
  "configuration": {
      "query": {
      "query": `UPDATE table SET ORGANISATION='new' WHERE ROWID='2'`,
      "connectionProperties": [{
        "key": "session_id",
        "value": sessionId
        }],
      useLegacySql: false,
    }
  }
}
const [job] = await bigquery.createJob(options);
const [rows] = await job.getQueryResults();
return rows
}