Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

How to get period separated table id from Table Object #354

Closed
JakeSummers opened this issue Oct 29, 2020 · 3 comments
Closed

How to get period separated table id from Table Object #354

JakeSummers opened this issue Oct 29, 2020 · 3 comments

Comments

@JakeSummers
Copy link

@JakeSummers JakeSummers commented Oct 29, 2020

I am attempting to build a query using the table id in a bigquery.Table object:

def do_query(client: bigquery.client, table: bigquery.Table):

	statement = f"""
		SELECT *
		FROM {table.full_table_id}
	"""
	query_job = client.query(statement, job_config=job_config)
	result = list(query_job.result())
	...

Unfortunately, this doesn't work. The output of table.full_table_id is: myProject:myDataSet.myTable but queries need to be formatted with period separated values, aka: myProject.myDataSet.myTable.

Question: How should I be constructing queries from a bigquery.Table object?

Right now the work around I am using is this:

statement = f"""
    SELECT *
    FROM {table.project}.{table.dataset_id}.{table.table_id}
"""

But this feels icky.

To search find this issue, this is the exception I was getting:

Traceback (most recent call last):
  ...
  File "me.py", line 74, in me
	result = list(query_job.result())
  File "/usr/local/lib/python3.7/site-packages/google/cloud/bigquery/job.py", line 3230, in result
	super(QueryJob, self).result(retry=retry, timeout=timeout)
  File "/usr/local/lib/python3.7/site-packages/google/cloud/bigquery/job.py", line 835, in result
	return super(_AsyncJob, self).result(timeout=timeout)
  File "/usr/local/lib/python3.7/site-packages/google/api_core/future/polling.py", line 130, in result
	raise self._exception
google.api_core.exceptions.BadRequest: 400 Syntax error: Unexpected ":" at [3:30]

I can see from issue #115 that this is the intended behaviour of full_table_id, but I am still curious if there is a cleaner solution to what I am doing.

@tswast
Copy link
Contributor

@tswast tswast commented Oct 29, 2020

We can probably add a standard_table_id property that does this format operation for you. Marking this as a feature request.

Loading

@tswast
Copy link
Contributor

@tswast tswast commented Oct 29, 2020

Note: If you're doing SELECT *, you can call client.list_rows(table) directly, which will save on time and query costs.

Loading

@tswast
Copy link
Contributor

@tswast tswast commented Nov 30, 2020

Just sent #405 which adds a __str__ method to TableReference as the inverse of from_string.

To use this with a Table object, call str(my_table.reference)

Loading

gcf-merge-on-green bot pushed a commit that referenced this issue Nov 30, 2020
)

This is the natural inverse of the `TableReference.from_string` method.

Thank you for opening a Pull Request! Before submitting your PR, there are a few things you can do to make sure it goes smoothly:
- [x] Make sure to open an issue as a [bug/issue](https://github.com/googleapis/python-bigquery/issues/new/choose) before writing your code!  That way we can discuss the change, evaluate designs, and agree on the general idea
- [x] Ensure the tests and linter pass
- [x] Code coverage does not decrease (if any source code was changed)
- [x] Appropriate docs were updated (if necessary)

Fixes #354 🦕
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Linked pull requests

Successfully merging a pull request may close this issue.

4 participants
@tswast @JakeSummers @steffnay and others