Incomplete schema in query result from Bigquery API

Question

When querying a Bigquery table (simple SELECT * from [...]) via the API using Jobs.query I get this returned as the schema:

 "schema": {
  "fields": [
   {
    "name": "all",
    "type": "INTEGER",
    "mode": "NULLABLE"
   },
   {
    "name": "timestamp",
    "type": "TIMESTAMP",
    "mode": "NULLABLE"
   },
   {
    "name": "last_timestamp",
    "type": "TIMESTAMP",
    "mode": "NULLABLE"
   },
   {
    "name": "unique",
    "type": "INTEGER",
    "mode": "NULLABLE"
   },...

But the actual schema (which is correctly returned by Tables.get) is:

 "schema": {
  "fields": [
   {
    "name": "all",
    "type": "INTEGER",
    "mode": "REQUIRED",
    "description": "All searches"
   },
   {
    "name": "timestamp",
    "type": "TIMESTAMP",
    "mode": "REQUIRED",
    "description": "Time of processing"
   },
   {
    "name": "last_timestamp",
    "type": "TIMESTAMP",
    "mode": "REQUIRED",
    "description": "Last event"
   },
   {
    "name": "unique",
    "type": "INTEGER",
    "mode": "REQUIRED",
    "description": "Unique users"
   },

I'm using the query results in an App Script that populates a spreadsheet and I'd like to use the description fields from the schema as column names. I could use Table.get to get the real schema but I'd like to understand why I'm getting an incomplete schema with the first query.

Mikhail Berlyant Mikhail Berlyant · Accepted Answer · 2016-05-20T23:27:27

Jobs.query API gives you schema of result of your query (which is saved in temporary table in background). In this table - there is no description for fields even though their types, mode, etc. are derived from schema of involved in query table(s)

Tables.get API from the other side - actually returns table's schema and that is why you see all about this table including descriptions.

Incomplete schema in query result from Bigquery API

1 Answers