0
Execute arbitrary query
One script reply has been approved by the moderators Verified

Execute arbitrary query on a bigquery table and return the result

Created by rubenfiszel 424 days ago Viewed 2494 times
0
Submitted by rubenfiszel Python3
Verified 424 days ago
1
#requirements:
2
#google-cloud-bigquery
3
#pandas
4
#db-dtypes
5

6
import os
7
import json
8
from google.cloud import bigquery
9

10
# try with a query such as
11
"""
12
    SELECT name, SUM(number) as total_people
13
    FROM `bigquery-public-data.usa_names.usa_1910_2013`
14
    WHERE state = 'TX'
15
    GROUP BY name, state
16
    ORDER BY total_people DESC
17
    LIMIT 20
18
"""
19

20
gcp_service_account = dict
21

22

23
def main(sa: gcp_service_account, query: str):
24

25
    sa_path = "/tmp/sa.json"
26
    f = open(sa_path, "a")
27
    f.write(json.dumps(sa))
28
    f.close()
29

30
    os.environ["GOOGLE_APPLICATION_CREDENTIALS"] = sa_path
31
    client = bigquery.Client()
32
    query_job = client.query(query)
33

34
    return query_job.to_dataframe().values.tolist()
35