BigQuery bq show与MySQL show create table对比及可复用Schema格式需求

Got it, let's fix this for you. The default bq show output is a bit messy for direct reuse in the BigQuery console, but we can easily convert it into the exact format you need—either the plain text schema that works in the console's "Edit as text" mode, or the JSON schema that's great for automation/API use.

1. Plain Text Format (Directly Paste into BigQuery Console) #

This is the simplest option for manual table creation. Just split each field from your bq show output onto its own line, and you'll get a block ready to paste:

date: string
channel_id: string
video_id: string
asset_id: string
claimed_status: string
uploader_type: string
country_code: string

How to use: When creating a new table in the BigQuery console, navigate to the "Schema" section, click "Edit as text", and paste this entire block. It'll auto-populate all the schema fields instantly.

2. JSON Schema Format (For Automation/API) #

If you need a machine-readable format (or want to save the schema as a reusable file), convert it to BigQuery's standard JSON schema structure:

[
  {"name": "date", "type": "STRING", "mode": "NULLABLE"},
  {"name": "channel_id", "type": "STRING", "mode": "NULLABLE"},
  {"name": "video_id", "type": "STRING", "mode": "NULLABLE"},
  {"name": "asset_id", "type": "STRING", "mode": "NULLABLE"},
  {"name": "claimed_status", "type": "STRING", "mode": "NULLABLE"},
  {"name": "uploader_type", "type": "STRING", "mode": "NULLABLE"},
  {"name": "country_code", "type": "STRING", "mode": "NULLABLE"}
]

Pro Tip: Skip manual conversion entirely by using this bq command to pull the JSON schema directly from your existing table:

bq show --format=prettyjson your-project.your-dataset.your-table | jq '.schema.fields'

This gives you clean, ready-to-use JSON without dealing with the pipe-separated output.

3. Quick Bash One-Liner to Convert Pipe-Separated Output #

If you already have that messy pipe-separated string, run this command to instantly split it into the plain text format:

echo "date: string |- channel_id: string |- video_id: string |- asset_id: string |- claimed_status: string |- uploader_type: string |- country_code: string" | sed 's/ |- /\n/g'

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