BigQuery Federated Data Source: CSV options in command-line interface?

Question

When you create a federated data source using the command-line interface is it possible to specify load options like field delimiter, allow jagged rows etc.?

bq mk --external_table_definition=column_1:STRING,column_2:STRING@CSV=gs://my-bucket/my_files*.csv my_dataset.my_table

You have access to all of the csv options when you set up federated tables in the web UI or the API, have they just been left out of the command-line tool?

score 0 · Answer 1 · answered Aug 07 '18 at 08:45

You can specify these options in a table definition JSON file. You can find a lot of examples in this document. For what you asked in the question, your JSON should be similar as below:

{
  "schema": {
    "fields": [
      {
        "name": "column_1",
        "type": "STRING"
      },
      {
        "name": "column_2",
        "type": "STRING"
      }
    ]
  },
 "csvOptions": {
    "allowJaggedRows": true,
    "fieldDelimiter": "[DELIMITER]"
  },
  "sourceFormat": "CSV",
  "sourceUris": [
    "[BUCKET_URI]"
  ]
}

Then just run the same command and specifying JSON file path instead of inline schema:

bq mk --external_table_definition=[JSON_FILE_PATH] [YOUR_DATASET.YOUR_TABLE]

BigQuery Federated Data Source: CSV options in command-line interface?

1 Answers1