Description
Thanks for stopping by to let us know something could be better!
PLEASE READ: If you have a support contract with Google, please create an issue in the support console instead of filing on GitHub. This will ensure a timely response.
Please run down the following list and make sure you've tried the usual "quick fixes":
- Search the issues already opened: https://github.com/googleapis/python-bigquery-sqlalchemy/issues
- Search StackOverflow: https://stackoverflow.com/questions/tagged/google-cloud-platform+python
If you are still having issues, please be sure to include as much information as possible:
Filed this issue on main SQLalchemy Git project, but they think this is a BigQuery driver issue:
sqlalchemy/sqlalchemy#9319
Environment details
- OS type and version: Linux CentOS 7
- Python version: 3.10
sqlalchemy-bigqueryversion: 1.5.0
Steps to reproduce
Metadata.reflect function tries to get all table names in engine before using the "only" table list. This can cause issues if a user does not have read permissions on all tables/datasets under engine, and results in a permission denied error.
Check for all table names: https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/schema.py#L5487
Check for "only" tables after: https://github.com/sqlalchemy/sqlalchemy/blob/main/lib/sqlalchemy/sql/schema.py#L5507
Desired behavior is to only check on tables explicitly listed with "only" argument.
Code example
Simple example similar to my problem. Engine is configured to a certain BQ project. My "only" table list has multiple tables coming from different datasets withing the project. I also do not have permission to all tables/datasets within the project.
engine = sa.create_engine('bigquery://' + bq_project)
tbl_only_list = ['datasetA.tbl1', 'datasetB.tbl2']
metadata.reflect(engine, autoload_replace=False, extend_existing=True, only=tbl_only_list, views=True)Stack trace
Partially redacted stack and error message.
Permission bigquery.tables.list denied on dataset ************* (or it may not exist).
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/google/cloud/_http/__init__.py", line 494, in api_request
raise exceptions.from_http_response(response)
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/google/api_core/retry.py", line 191, in retry_target
return target()
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/google/api_core/retry.py", line 349, in retry_wrapped_func
return retry_target(
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/google/cloud/bigquery/client.py", line 789, in _call_api
return call()
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/google/cloud/bigquery/client.py", line 1532, in api_request
return self._call_api(
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/google/api_core/page_iterator.py", line 432, in _get_next_page_response
return self.api_request(
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/google/api_core/page_iterator.py", line 373, in _next_page
response = self._get_next_page_response()
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/google/api_core/page_iterator.py", line 244, in _page_iter
page = self._next_page()
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/google/api_core/page_iterator.py", line 208, in _items_iter
for page in self._page_iter(increment=False):
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/sqlalchemy_bigquery/base.py", line 857, in _get_table_or_view_names
for table in tables:
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/sqlalchemy_bigquery/base.py", line 1012, in get_table_names
return self._get_table_or_view_names(connection, item_types, schema)
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/sqlalchemy/engine/reflection.py", line 266, in get_table_names
return self.dialect.get_table_names(
File "/home/jlynch/miniconda3/envs/telem_dev/lib/python3.10/site-packages/sqlalchemy/sql/schema.py", line 4859, in reflect
available = util.OrderedSet(insp.get_table_names(schema))
Making sure to follow these steps will guarantee the quickest resolution possible.
Thanks!