Get list of tables in BigQuery dataset using python and BigQuery API

Question

How can I query a BigQuery dataset and get a list of all the tables in the dataset? As far as I know, I can only use the BigQuery API, but I cannot authenticate, despite passing an API key.

    url = f"https://bigquery.googleapis.com/bigquery/v2/projects/{params['project_id']}/datasets/{params['dataset_id']}/tables?key={params['api_key']}"
    response = requests.get(url)
    data = response.json()
    pprint.pprint(data)

Documentation has it in plain English - Listing tables in a dataset ! It didn't work for you?! Please let us know — Mikhail Berlyant
– Mikhail Berlyant, Commented Jun 18, 2019 at 20:59
It's not allowed using only an API key, please consider using a service account instead. cloud.google.com/docs/authentication/api-keys — Christopher
– Christopher, Commented Jun 19, 2019 at 6:40

John · Accepted Answer · 2019-06-19 19:41:17Z

22

As Mikhail said, its explained in the docs. Here is the answer:

from google.cloud import bigquery

# TODO(developer): Construct a BigQuery client object.
# client = bigquery.Client()

# TODO(developer): Set dataset_id to the ID of the dataset that contains
#                  the tables you are listing.
# dataset_id = 'your-project.your_dataset'

tables = client.list_tables(dataset_id)

print("Tables contained in '{}':".format(dataset_id))
for table in tables:
    print("{}.{}.{}".format(table.project, table.dataset_id, table.table_id))

answered Jun 19, 2019 at 19:41

John

1,0791 gold badge12 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ricardo Bindi · Accepted Answer · 2020-04-15 20:49:29Z

0

I could expand on John's answer and add:

from google.cloud import bigquery
client = bigquery.Client()
datasets = list(client.list_datasets())  # Make an API request.
project = client.project

if datasets:
    print("Datasets in project {}:".format(project))
    for dataset in datasets:
        print("\t{}".format(dataset.dataset_id))
        tables = client.list_tables(dataset.dataset_id)

        print("Tables contained in '{}':".format(dataset.dataset_id))
        for table in tables:
            print("{}.{}.{}".format(table.project, table.dataset_id, table.table_id))

else:
    print("{} project does not contain any datasets.".format(project))

answered Apr 15, 2020 at 20:49

Ricardo Bindi

12 bronze badges

Collectives™ on Stack Overflow

Get list of tables in BigQuery dataset using python and BigQuery API

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related