通过BigQuery命令行手动创建表架构时遇加载错误求助

Let's break down what's going wrong here and fix it step by step.

First, let's parse the key error message you're getting:

CSV table references column position 1, but line starting at position:0 contains only 1 columns

This means BigQuery expects multiple columns (since you defined a 3-column schema: qtr, sales, year), but it's only reading one column per row from your CSV file. Here are the most likely issues and fixes:

1. Mismatched CSV Field Delimiter #

BigQuery uses a comma (,) as the default field delimiter for CSV files. If your myfile1.csv uses a different separator (like tabs, semicolons, or pipes), BigQuery will treat the entire row as a single column, which conflicts with your 3-column schema.

Fix: #

Add the --field_delimiter flag to your command to match your actual CSV separator:

• For tab-separated files:

  bq load --source_format=CSV --field_delimiter="\t" EncoreMarketingTest.SchemaTest1231 D:/myfile1.csv qtr:STRING,sales:FLOAT,year:STRING
  

• For semicolon-separated files:

  bq load --source_format=CSV --field_delimiter=";" EncoreMarketingTest.SchemaTest1231 D:/myfile1.csv qtr:STRING,sales:FLOAT,year:STRING
  

2. Command-Line Schema Parsing Issues #

Passing the schema directly as a comma-separated string in the command line can sometimes cause parsing problems (especially if your shell interprets commas incorrectly). A more reliable approach is to define your schema in a JSON file.

Fix: #

1. Create a file named schema.json with this content:

   [
     {"name": "qtr", "type": "STRING"},
     {"name": "sales", "type": "FLOAT"},
     {"name": "year", "type": "STRING"}
   ]
   

2. Update your bq load command to reference this file:

   bq load --source_format=CSV --schema=schema.json EncoreMarketingTest.SchemaTest1231 D:/myfile1.csv
   

3. Unhandled Header Row #

If your CSV file starts with a header row (e.g., qtr,sales,year), BigQuery will try to read that header as a data row, which will cause a type mismatch (and might also contribute to column count issues if the header doesn't split correctly).

Fix: #

Add the --skip_leading_rows=1 flag to skip the header:

bq load --source_format=CSV --skip_leading_rows=1 EncoreMarketingTest.SchemaTest1231 D:/myfile1.csv qtr:STRING,sales:FLOAT,year:STRING

4. Corrupt CSV Rows #

Double-check your myfile1.csv to ensure every row has exactly 3 columns separated by your chosen delimiter. Missing delimiters, unescaped quotes, or extra newlines can cause BigQuery to misread rows as single columns.

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