CODEWITHSUNDEEP
google-bigquery
user11897475
user11897475

Reputation: 31

Is it possible to include Table Description in JSON schema file?

I am trying to create JSON file to use with bq -mk command to create biqquery table. The table definition includes description, labels, clustering and partitioning. Is it possible to include parameters like table description, clustering, labels and time partitioning in JSON file or those need to be specified outside of JSON file as bq parameters?

I have created table in BQ GUI with comments, labels etc. and exported JSON schema as:

bq show --format=prettyjson [project]:[dataset].sample_table

... which gives me following:

{
  "clustering": {
    "fields": [
      "f1"
    ]
  },
  "description": "Test Table Description",
  "labels": {
    "subject_area": "test subject area"
  },
  "schema": {
    "fields": [
      {
        "description": "f1_description",
        "mode": "REQUIRED",
        "name": "f1",
        "type": "INTEGER"
      },
      {
        "name": "f2",
        "type": "STRING"
      },
     {
        "name": "f5",
        "type": "DATE"
      }
    ]
  },
  "timePartitioning": {
    "expirationMs": "31622400000",
    "field": "f5",
    "type": "DAY"
  }
}

When I save JSON above into sample_table_test.json file and try to create new table such as:

bq mk --schema sample_table_test.json --table [project]:[dataset].sample_table_test

I get following error:

BigQuery error in mk operation: Error in "sample_table_test.json": Table schemas must be specified as JSON lists.

Is what I am trying to do even possible, given that you can specify description, labels etc via bq mk switches?

Upvotes: 3

Views: 1544

Answers (1)

asbovelw
asbovelw

Reputation: 572

Nowadays, specifying this information in the JSON is not possible, as per Google's documentation.

The only way to specify table description, labels, clustering and partitioning is with the bq mk command flags, like this and this.

I have created a feature request so the BigQuery engineering team may evaluate the possibility of adding this functionality. Note that there are no ETAs or guarantees of implementation for feature requests. All communication regarding this feature request is to be done there.

A part from this, you are getting that error because you are not uploading the expected JSON, which has to be like this; the expected JSON is a subset of the one you get with the bq show command: the schema.fields array. In your case, it should be this:

   [
      {
        "description": "f1_description",
        "mode": "REQUIRED",
        "name": "f1",
        "type": "INTEGER"
      },
      {
        "name": "f2",
        "type": "STRING"
      },
     {
        "name": "f5",
        "type": "DATE"
      }
   ]

Upvotes: 2

Related Questions