使用BigQuery查询BigTable外部表报错：表定义文件问题排查及正确编写方法

Hey there, let's break down the issues causing that parsing error you're stuck on. The error about data between close double quotes and field separators almost always traces back to invalid JSON in your table definition or incorrect BigTable URI formatting—here are the key fixes to apply:

1. Replace HTML-Escaped Quotes with Native JSON Quotes #

Your table definition uses " (HTML-escaped double quotes) instead of regular " characters. BigQuery expects valid, standard JSON, so these escaped quotes will break the parsing of your table configuration entirely.

Wrong snippet from your definition: #

{ "sourceFormat": "BIGTABLE", "sourceUris": [ "https://googleapis.com/bigtable/projects/ostabprj/instances/cryptorealtime/tables/cryptorealtime" ], ... }

Corrected approach: #

Use standard double quotes without HTML escaping them (you only need to escape quotes if you're embedding them inside a string value, which isn't necessary here).

2. Fix the BigTable Source URI Format #

You're using an HTTPS URL for the BigTable source, but BigQuery requires the bigtable:// protocol to connect to BigTable external tables. This specific URI format tells BigQuery how to properly interact with your BigTable instance.

Wrong URI: #

"https://googleapis.com/bigtable/projects/ostabprj/instances/cryptorealtime/tables/cryptorealtime"

Correct URI: #

"bigtable://projects/ostabprj/instances/cryptorealtime/tables/cryptorealtime"

Full Corrected Table Definition File #

Here's the fixed version of your def.json file:

{
  "sourceFormat": "BIGTABLE",
  "sourceUris": [
    "bigtable://projects/ostabprj/instances/cryptorealtime/tables/cryptorealtime"
  ],
  "bigtableOptions": {
    "columnFamilies": [
      {
        "familyId": "market",
        "type": "STRING",
        "encoding": "TEXT"
      }
    ]
  }
}

Steps to Recreate the Table #

1. Upload this corrected def.json back to your GCS bucket (gs://realtimecrypto-ostabprj/def.json).
2. Recreate the external table with the fixed definition:

bq mk --external_table_definition=gs://realtimecrypto-ostabprj/def.json test.test1

3. Try running your query again:

SELECT * FROM ostabprj.test.test1 LIMIT 1000

Additional Checks (If Issues Persist) #

• Double-check that the market column family exists exactly as named in your BigTable instance.
• Ensure none of the values in your BigTable cells contain unescaped double quotes that might conflict with parsing (your sample data looks clean, but it's worth confirming for all rows).

内容的提问来源于stack exchange，提问作者maesi