This repository was archived by the owner on May 16, 2026. It is now read-only.
Repository navigation
This repository was archived by the owner on May 16, 2026. It is now read-only.
Running a chain of statements that depend on each other #74
Description
DECLARE d DATE DEFAULT CURRENT_DATE();
CREATE TEMP FUNCTION Add(x INT64, y INT64) AS (x + y);
SELECT Add(3, 4);I'm having an issue running a chain of statements that depend on each other in a common session. I'm using a single connection and cursor behind the scene.
It appears that the statements are not chained and the session isn't persisted. Any clue on how to solve this?
Other DBAPI implementations allow this type of chaining, and using the same logic with this driver does not work properly.
Activity
tswast commentedon Nov 9, 2020
mistercrunch commentedon Nov 9, 2020
I'm not sure if you're asking about how we run it, or how the underlying DB-API in
google-cloud-bigquerydoes it (?). Assuming the former, we open a connection object and a cursor object andcursor.executethe series of statements.Now I'm realizing the issue may be in
google-cloud-bigquery, do you think I should open an issue there?tswast commentedon Nov 9, 2020
Yes, https://github.com/googleapis/python-bigquery would be the appropriate location for this issue.
Are you calling
executeonce? Or once per statement?If once per statement, are there any transaction semantics you're using? The BigQuery API doesn't hold onto cursor/connection state, so we'll need some kind of additional information in order for the client to batch these up into a single request.
mistercrunch commentedon Nov 10, 2020
We're calling execute for each statement, most other DB-API driver's
executeassumes a single statement. All other DB-API drivers we use in Apache Superset respond well to this approachWondering what that may be. I saw the notion of
job_idreading some source code ingoogle-cloud-bigquery, but unclear as to how to get this to work.mistercrunch commentedon Nov 10, 2020
I opened an issue on
python-bigquerywith more details, and really appreciate all the help so far!tswast commentedon Mar 23, 2021
Updating this issue from our discussion on the DB-API. Currently we workaround this by batching all statements in to a single query request. This triggers the BigQuery "scripting" path.
The ideal solution is for the BigQuery backend to support true sessions for scripting. If/when they add support for sessions, we can make the update to the DB-API and test it with this SQLAlchemy connector.
mattalbr commentedon Nov 6, 2024
Sorry to drag up an old issue, but this is where I was looking to figure out how to write such a query using bigquery sqlalchemy.
I'm assuming the SET/DECLARE statements need to be created using
text(), but is there a good way to combine these SET/DECLAREs with a selectable at the end without compiling the selectable?Relatedly, is there a way to do a
CREATE TEMP TABLE foo AS ()and input a selectable?Happy to file a new issue along the lines of
Document multi-statement query usageif that's easier!