Solve “invalid table reference” when streaming into BigQuery

Recently I decided to start streaming clickstream data (from this blog, indeed) into BigQuery using the insertAll method. It is a relatively cheap solution that requires almost no technical configuration. Until I bumped into an error. Let’s tackle it in this blog post.

The error I encountered is:

“google.api_core.exceptions.BadRequest: 400 POST […] /insertAll?prettyprint=false invalid table reference”

If you Google a little bit, you’ll find out quite a lot of people run into this error. And the weird thing is that there’s no real solution.

When you see this error there’s a good chance that your table name contains a dash (hyphen). If you consult the BigQuery documentation, you’ll see that this shouldn’t be a problem:

When you create a table in BigQuery, the table name must be unique per dataset. The table name can:

1. Contain up to 1,024 characters.
2. Contain Unicode characters in category L (letter), M (mark), N (number), Pc (connector, including underscore), Pd (dash), Zs (space). For more information, see General Category.
For example, the following are all valid table names: table-01, ग्राहक, 00_お客様, étudiant.

It isn’t a problem, because you can wrap your table in ` signs when you write your queries. However, when you want to use the insertAll method, that is not an option. In the following Python example, there is no way you can edit your table ID to escape the hyphen.

bq_client = bigquery.Client.from_service_account_json('creds.json')
table_id = 'my-project.web.event-stream'
errors = bq_client.insert_rows_json(table_id, [request_json])

if errors == []:
  print("New rows have been added.")
else:
  print("Encountered errors while inserting rows: {}".format(errors))

I tried the following solutions:

• Escaping the dash in every way possible.
• URL encoding the table name
• Using legacy notation
• Passed a TableReference instead of a string
• Replaced the hyphen with \x2D or \u002D

None of it works.

Since there’s no way that you can escape the hyphen in the table name, the only thing you can do is renaming the table. As you might be aware, renaming a table in BigQuery is impossible: you will have to copy it.

If you have found a better solution, please let me know in the comments below!