title: Query specific tables in Bigtable from BigQuery
body: <p>I have some data in Google BigTable over which I have built a BigQuery external table (as per <a href="https://cloud.google.com/bigquery/external-data-bigtable" rel="nofollow noreferrer">Querying Cloud Bigtable data</a> so that I can query the Bigtable table using conventional SQL (which I'm very familiar with).</p>
  <p>When I issue a <code>select *</code> I get this:
  <a href="https://i.stack.imgur.com/AYfik.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/AYfik.png" alt="enter image description here" /></a></p>
  <p>Now I would like to know the syntax for querying specific values in this nested data. For example, to get a list of <code>accountId</code>s I can do this:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT  ARRAY(SELECT timestamp FROM UNNEST(attributes.column[OFFSET(0)].cell)) AS timestamp,
          ARRAY(SELECT SAFE_CONVERT_BYTES_TO_STRING(value) FROM UNNEST(attributes.column[OFFSET(0)].cell)) AS values
  FROM `table` 
  where SAFE_CONVERT_BYTES_TO_STRING(rowkey) = 'XXXX'
  </code></pre>
  <p>which returns:<br />
  <a href="https://i.stack.imgur.com/T3XI2.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/T3XI2.png" alt="enter image description here" /></a></p>
  <p>which is, well, <em>kinda</em> handy.</p>
  <p>Similarly I can get <code>car#le11mcr#policyStartDate</code> by changing the OFFSET like so:</p>
  <pre class="lang-sql prettyprint-override"><code>SELECT  ARRAY(SELECT timestamp FROM UNNEST(attributes.column[OFFSET(6)].cell)) AS timestamp,
          ARRAY(SELECT SAFE_CONVERT_BYTES_TO_STRING(value) FROM UNNEST(attributes.column[OFFSET(6)].cell)) AS values
  FROM `table` 
  where SAFE_CONVERT_BYTES_TO_STRING(rowkey) = 'XXXX'
  </code></pre>
  <p><a href="https://i.stack.imgur.com/iJBpD.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iJBpD.png" alt="enter image description here" /></a></p>
  <p>However both of these queries require me to know what value to pass to <code>OFFSET()</code> and that value appears to depend on the alphabetical order of the Bigtable columns hence if another column whose name starts with (say) 'b' appears in the future my queries would no longer return the same thing.</p>
  <p>I need a better way of querying the table than using <code>OFFSET()</code>. Essentially I want to be able to say:</p>
  <blockquote>
  <p>select the cell values and timestamp values for the cell whose name is <code>accountId</code></p>
  </blockquote>
  <p>or</p>
  <blockquote>
  <p>select the cell values and timestamp values for the cell whose name is <code>car#le11mcr#policyStartDate</code></p>
  </blockquote>
  <p>Is there a way to do that? I'm not too familiar with BigQuery syntax for doing this.</p>