Skip to content

Instantly share code, notes, and snippets.

@ChrisPenner
Created June 3, 2018 00:31
Show Gist options
  • Save ChrisPenner/2525b29f49cdb6613175cda8c85cb585 to your computer and use it in GitHub Desktop.
Save ChrisPenner/2525b29f49cdb6613175cda8c85cb585 to your computer and use it in GitHub Desktop.
Use Google Sheet as BigQuery Dataset

Generating BQ schema from google sheet header row

To generate a new schema:

  • Copy the ID header row from your google sheet

  • pbpaste | python make_schema.py

  • There's your BQ schema!

  • Add a new dataset to bigquery

    • Use your spreadsheet link as the file location
    • Paste your schema in the box after clicking "Edit as Text"
import sys
import re
txt = sys.stdin.read()
headers = txt.split('\t')
print ','.join([
'{}:STRING'.format(re.sub(r'_+', '_', re.sub(r'[^a-z]', '_', h.lower())))
for h in headers
if h
])
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment