CODEWITHSUNDEEP
google-sheetsgoogle-drive-apigoogle-bigquery
719016
719016

Reputation: 10441

bigQuery Error while reading data, error message: Row 1 has only 1 columns, while 2 is needed

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"}
    ]
  }
}

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'

I get this error:

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?

Upvotes: 0

Views: 3692

Answers (2)

Li Tan
Li Tan

Reputation: 81

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.

Upvotes: 1

enle lin
enle lin

Reputation: 1714

I think the problem is to have empty cells in Google Sheets. I was able to run the same command after add some string in col4. Notice that there is allowJaggedRows option for loading configuration in Bigquery to accept missing trailing optional columns but it only apply to CSV and in this document about Google Sheets reading process states

Empty trailing rows and columns are omitted.

I think the best solution for this case is to substitute empty cells to some other values. For instance, 'null'.

Upvotes: 1

Related Questions