Skip to content

Instantly share code, notes, and snippets.

What would you like to do?
BigQuery JSON schema generator
require 'open-uri'
require 'zlib'
require 'yajl'
# References
# -
# -
def type(t)
return 'FLOAT' if t.is_a?(Float)
return 'INTEGER' if t.is_a?(Integer)
return 'STRING' if t.is_a?(String)
return 'BOOLEAN' if t.is_a?(TrueClass) || t.is_a?(FalseClass)
return 'RECORD' if t.is_a?(Hash)
return type(t.first) if t.is_a?(Array)
puts "Unknown type for #{t}, #{t.class}"
raise Exception
def mode(e)
if e.is_a? Array
def traverse(target, event)
event.each_pair do |k,v|
desc = target.find {|e| e['name'] == k} || {}
target << desc if desc.empty?
desc['name'] = k
# Note: we skip empty REPEATED fields until we encounter a non-empty one.
# This may result in empty REPEATED declarations, which will be rejected
# by BigQuery... You'll have to handle this on your own.
next if v.nil? || (v.is_a?(Array) && v.first.nil?)
desc['type'] = type(v)
desc['mode'] = mode(v)
if desc['type'] == 'RECORD'
desc['fields'] ||= []
v = [v] if desc['mode'] != 'REPEATED'
v.each do |e|
traverse(desc['fields'], e) unless e.nil?
@fields = []
file = ARGV[0]
data = open(file)
if File.extname(file) == '.gz'
data =
Yajl::Parser.parse(data) do |event|
traverse(@fields, event)
def check(target)
target.each do |field|
if !(field.has_key?('name') && !field['name'].nil? &&
field.has_key?('type') && !field['type'].nil?)
STDERR.puts "Warning: #{field} has an unknown type."
field['type'] = 'STRING'
field['mode'] = 'NULLABLE'
if field['fields']
puts Yajl::Encoder.encode(@fields, :pretty => true)
Copy link

qiuyij commented Jul 29, 2015

This is super helpful thank you very much!

Copy link

Great. I created something similar in Javascript:

Copy link

Here is a quick one - a little less sophisticated for python:

python <filename>

Works with gzipped files too!

Copy link


homes-MacBook-Air:bigquery-fluentd-docker-sample office_$ ruby json-bq-schema-generator.rb testbg.log
json-bq-schema-generator.rb:31:in traverse': undefined methodeach_pair' for [[{"n"=>"def", "v"=>"3000"}]]:Array (NoMethodError)
from json-bq-schema-generator.rb:50:in block (2 levels) in traverse' from json-bq-schema-generator.rb:49:ineach'
from json-bq-schema-generator.rb:49:in block in traverse' from json-bq-schema-generator.rb:31:ineach_pair'
from json-bq-schema-generator.rb:31:in traverse' from json-bq-schema-generator.rb:65:inblock in

from /Library/Ruby/Gems/2.0.0/gems/yajl-ruby-1.2.1/lib/yajl.rb:37:in call' from /Library/Ruby/Gems/2.0.0/gems/yajl-ruby-1.2.1/lib/yajl.rb:37:inparse'
from /Library/Ruby/Gems/2.0.0/gems/yajl-ruby-1.2.1/lib/yajl.rb:37:in parse' from json-bq-schema-generator.rb:64:in'
homes-MacBook-Air:bigquery-fluentd-docker-sample office_$

Any idea? Thanks !!!

Copy link

satrox28 commented May 6, 2017


I am facing this issue when trying to run it. I have installed yajl, yajl-ruby. May I know, what I am missing here, thanks

$ruby json-bq-schema-generator.rb data1.json
json-bq-schema-generator.rb:64:in `

': uninitialized constant Yajl::Parser (NameError)

Copy link

satrox28, I got the same problem, for me it worked changing:

require 'yajl'


require 'yajl/yajl'
require 'yajl'


Copy link

Having the same problem with Yajl::Paser. require 'yajl/yajl' does not work for me. Please advise.

Copy link

ahsandar commented Apr 18, 2019

I have created a JSON to BQ schema generator json2bqschema( packaged in a docker container using this gist for use from CLI. It has some changes , it is suppose to work on a single JSON object and not zip data sets. Also its using JSON module rather than yajl as the container size was 5x just to use yajl. As it is for running on a single json object the performance gains are not an issue. Anyone is more than welcome to fork and extend this package. thanks to @igrigorik for this gist

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment