google-bigquerygoogle-client

Unable to get clustered table definition using bq.py cli


I have a Google BigQuery clustered partition table. And I am trying to get Google BigQuery clustered partition table definition using bq.py cli tool. I get the json output but it does not have clustering information.

% bq version  

This is BigQuery CLI 2.0.69


% bq show \   
--schema \
--format=prettyjson \
 uk-kingdom-911:ld_cv_1_population.cv_performance_t1_agg3_auh_mvpart

 
[
  {
    "name": "hstamp",
    "type": "TIMESTAMP"
  },
  {
    "name": "account_id",
    "type": "INTEGER"
  },
 ....
.....
]

Json output does not have clustering information. Not sure what I am missing here.


Solution

  • That's because you've used the --schema flag. The --schema flag only shows the basic schema of the table and nothing else. Remove that flag and you should see everything:

    > bq show --format=prettyjson example_ds.test_table
    ==========================================================
    {
      "clustering": {
        "fields": [
          "second_col"
        ]
      },
      "creationTime": "1623325001027",
      "etag": "xxx",
      "id": "my-project:example_ds.test_table",
      "kind": "bigquery#table",
      "lastModifiedTime": "1623325001112",
      "location": "xxx",
      "numBytes": "0",
      "numLongTermBytes": "0",
      "numRows": "0",
      "schema": {
        "fields": [
          {
            "mode": "NULLABLE",
            "name": "first_col",
            "type": "DATE"
          },
          {
            "mode": "NULLABLE",
            "name": "second_col",
            "type": "INTEGER"
          }
        ]
      },
      "selfLink": "xxxx",
      "tableReference": {
        "datasetId": "example_ds",
        "projectId": "my-project",
        "tableId": "test_table"
      },
      "timePartitioning": {
        "type": "DAY"
      },
      "type": "TABLE"
    }