Create external table in BigQuery based on BigTable

Question

Create external table in BigQuery based on BigTable

108 views Asked by Sander van den Oord At 20 November 2023 at 14:24

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.

Original Q&A

There are 1 answers

**Sander van den Oord** · Answer 1 · 2023-11-20T14:24:54+00:00

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