Open
Description
Please find below minimum reproducible code to run a muti statement select. The problem here is, Big Query runs the select statement as multiple statements, and the result contains dataset from the the first dataset only, everything else is discarded.
You can run this in a Big Query console and see the difference.
How can I get the result for all the statements executed.
from google.cloud import bigquery
def run_query(sql_query):
client = bigquery.Client()
query_job = client.query(sql_query)
# Wait for the job to complete
results = query_job.result()
print("Query complete!")
print(f"Results: {results.total_rows}")
for row in results:
print(row)
if __name__ == "__main__":
sql_query = """
BEGIN
FOR record IN
(
SELECT num FROM UNNEST(GENERATE_ARRAY(1, 5)) AS num
)
DO
WITH numbers AS (
SELECT num
FROM UNNEST(GENERATE_ARRAY(1, 100)) AS num -- Adjust the range as needed
)
SELECT num
FROM numbers
LIMIT 10;
END FOR;
END
"""
# Execute the demo query
run_query(sql_query)
Note: Th equery is just for demonstrative purpose of multi statement execution only and the correctness of its functionality is never intended.