Home » Solve “invalid table reference” when streaming into BigQuery

# 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:

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: , ग्राहक, 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 == []:
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!

### Say thanks, ask questions or give feedback

Technologies get updated, syntax changes and honestly… I make mistakes too. If something is incorrect, incomplete or doesn’t work, let me know in the comments below and help thousands of visitors.