bigQuery读取数据时出错，错误消息:第1行只有1列，而第2行是必需的

问题描述

我创建了一个json文件来定义Google bigQuery中链接到Google表格电子表格的表格:

I created a json file to define a table in Google bigQuery that is linked to a Google Sheets spreadsheet:

{
  "autodetect": true,
  "sourceFormat": "GOOGLE_SHEETS",
  "sourceUris": [
    "https://docs.google.com/spreadsheets/d/1P1WH7cwVDaG6k-OQxKVXtnjBXI1NGFYvHD6IxCRFsZc"
  ],
  "maxBadRecords": 1,
  "googleSheetsOptions":
  {
    "range": "Sheet2!A1:B10",
    "skipLeadingRows": 0
  },
  "schema" : {
    "fields": [
{"name":"col3","type":"string"},
{"name":"col4","type":"string"}
    ]
  }
}

当我通过以下bq命令行查询它时:

When I query it with this bq command line:

bq query --external_table_definition="Sheet2::/home/avilella/LIMS/test.json" --format=csv --use_legacy_sql=false 'SELECT * FROM Sheet2'

我收到此错误:

BigQuery error in query operation: Error processing job 'cegx-test-project1:bqjob_r30ad5155bcd0a174_00000163bb575bcf_1': Error while reading table: Sheet2, error message: Sheets table encountered too many
errors, giving up. Rows: 2; errors: 2. Please look into the error stream for more details.
Failure details:
- 1P1WH7cwVDaG6k-OQxKVXtnjBXI1NGFYvHD6IxCRFsZc: Error while reading
data, error message: Row 1 has only 1 columns, while 2 is needed.
- 1P1WH7cwVDaG6k-OQxKVXtnjBXI1NGFYvHD6IxCRFsZc: Error while reading
data, error message: Row 2 has only 1 columns, while 2 is needed.

有什么主意我在做错什么吗?

Any ideas what am I doing wrong?

推荐答案

在测试电子表格的Sheet2中，我看到第二行和第三行都只有1列，因此这两行都是坏"行，因为该表模式具有2个字段，这些字段在外部表def json中指定.同样，由于maxBadRecords设置为1，查询最多可以有1行，但查询成功，但是由于有两个坏行，查询将按预期失败.

From the Sheet2 on your test spreadsheet, I saw there is only 1 column in both the 2nd and 3rd rows, so both are 'bad' rows because the table schema has 2 fields as specified in the external table def json. Also because the maxBadRecords is set to 1, query can succeed with at most 1 had row, but since you've got two bad rows, the query failed as expected.

这篇关于bigQuery读取数据时出错，错误消息:第1行只有1列，而第2行是必需的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！