Schema definition generated online Schema-Generator is not accepted by BigQuery while using Load Table API

Question

Any relevant help will be appreciated.

I have several different JSON docs whic need to be inserted into BigQuery. Now to avoid generating schema manually, I am using the help of online Json Schema Generation tools available. But the schema generated by them are not being accepted by BigQuery Load Data wizard. For eaxmple: for a Json data like this:

{"_id":100,"actor":"KK","message":"CCD is good in Pune",
"comment":[{"actor":"Subho","message":"CCD is not as good in Kolkata."},        
{"actor":"bisu","message":"CCD is costly too in Kolkata"}]
}

the generated schema by online tool is:

{
"$schema": "http://json-schema.org/draft-04/schema#",
"description": "Generated from c:jsonccd.json with shasum a003286a350a6889b152
 b3e33afc5458f3771e9c",
 "type": "object",
 "required": [
 "_id",
 "actor",
 "message",
 "comment"
 ],
 "properties": {
 "_id": {
   "type": "integer"
  },
 "actor": {
  "type": "string"
  },
  "message": {
  "type": "string"
  },
  "comment": {
  "type": "array",
  "minItems": 1,
  "uniqueItems": true,
  "items": {
    "type": "object",
    "required": [
      "actor",
      "message"
    ],
    "properties": {
      "actor": {
        "type": "string"
      },
      "message": {
        "type": "string"
       }
     }
   }
  }
 }
}

But when I put it into BigQuery in the Load Data wizard, it fails with errors.

How can this be mitigated?

Thanks.

This doesn't look at all BigQuery schema, it's some other schema and you got confused. What generator did you used? — Pentium10
@Pentium 10 - you probably not have read my question thoroughly. The schema I have printed here has been generated from a Online Json Schema Generator. And this is valid. But BigQuery does not accept this format. Now is here any tool or utility programme that generates Schema according to BigQuery acceptable format ? — Subhayu
Invalid format error. Unable to parse the schema. So it is not creating the table even. — Subhayu
That tool generates you some Json Schema, but it's not a format recognized by BQ. Read the BQ documentation how to define table structures in BQ. There is no generator yet to do these. — Pentium10

Felipe Hoffa Felipe Hoffa · Accepted Answer · 2014-09-03T19:17:31

The schema generated by that tool is way more complex than what BigQuery requires.

Look at the sample in the docs:

"schema": {
        "fields": [
          {"name":"f1", "type":"STRING"},
          {"name":"f2", "type":"INTEGER"}
        ]
      },

https://developers.google.com/bigquery/loading-data-into-bigquery?hl=en#loaddatapostrequest

Meanwhile the tool mentioned in the question adds fields like $schema, description, type, required, properties that are not necessary and confusing to the BigQuery schema parser.

Schema definition generated online Schema-Generator is not accepted by BigQuery while using Load Table API

1 Answers