Create external table in BigQuery based on BigTable

Question

I have data in BigTable and want to do some checks on the quality.

How can i create an (external) table in BigQuery to do these checks, using SQL.

Answer 1

As always, the devil is in the details.

• make sure the last row in your bigtable_options or one of the dicts inside, don't have a comma at the end
• make sure you set type and encoding correctly in the columnFamilies. Right now I'm using the defaults.
• make sure to check the docs. See links below the code.

You can create your external table as follows:

CREATE EXTERNAL TABLE `your_project.your_dataset.your_table_name_bigtable_external_table`
OPTIONS (
  format = 'CLOUD_BIGTABLE',
  uris = ['https://googleapis.com/bigtable/projects/your_project/instances/your_instance_in_bigtable/tables/your_table_in_bigtable'],
  bigtable_options =
    """
    {
      columnFamilies: [
        {
          "familyId": "name_of_your_column_family",
          "onlyReadLatest": true,
          "columns": [
            {
              "qualifierString": "your_column_name",
              "fieldName": "your_column_name_if_it_contains_illegal_bq_characters"
            }
          ]
        }
      ],
      readRowkeyAsString: true
    }
    """
);

SELECT 
    *
FROM
    `your_project.your_dataset.your_table_name_bigtable_external_table`
LIMIT 100;

Info on how to create and query a bigtable external table:
https://cloud.google.com/bigquery/docs/create-bigtable-external-table#create-external-table

Info on bigtable_options can be found here:
https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#bigtableoptions

Info on option columnFamily can be found here:
https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#bigtablecolumnfamily

Info on columns of the columnFamily can be found here:
https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#bigtablecolumn

Related questions:

• GCP - BigTable to BigQuery
• GCP moving Bigtable data to BigQuery
• Querying BigTable data with BigQuery: how to make a correct table definition file