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

3
votes

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)
2
pythongoogle-cloud-platformgoogle-bigquery
Documentation has it in plain English - Listing tables in a dataset ! It didn't work for you?! Please let us know – Mikhail Berlyant
It's not allowed using only an API key, please consider using a service account instead. cloud.google.com/docs/authentication/api-keys – Christopher

2 Answers

11
votes

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))
0
votes

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))