Search code examples
google-bigquerygoogle-cloud-bigtable

Querying Bigtable prefix from Bigquery

Using BigQuery's Bigtable external data source, I'd like to find rowkeys in Bigtable that start with a certain value. This is a very fast operation in cbt or other Bigtable-native tools, but seems slow in BigQuery. 

SELECT rowkey from blah.blah where rowkey LIKE '123%' group by rowkey

It seems to be processing a lot of data, perhaps even the whole Bigtable table, to find results, rather than using Bigtable's native prefix operation which is very fast.

Am I using the most efficient query in BigQuery for this?

Solution

  • Instead of:

    WHERE rowkey LIKE '123%'

    try:

    WHERE rowkey > '123'

    The connector between BigQuery and Bigtable could be smarter about this (internal discussion started), but in the meantime this should fix the issue.