BigQuery: Importing your data with SQL from Google cloud and drive storage

  • Bigtable
  • Cloud Storage
  • Drive

Basic code

# ***************************************************************
# From drive
# ***************************************************************
CREATE OR REPLACE EXTERNAL TABLE
project.testing.external_table
# ***************************************************************
# See the link for the options, however I have added the most common ones
# *****************************************************************
OPTIONS( format = 'CSV',
uris = ['https://docs.google.com/spreadsheets/d/xxxxxxxxxxxxxxxxxxxxxxxx'],
skip_leading_rows=1,
ignore_unknown_values = TRUE,
description= 'extracted from drive',
max_bad_records = 5
);
CREATE OR REPLACE TABLE
`project.testing.native_table`
#column names and descriptions
(criteria_id INT64 OPTIONS( description='Coded value' ),
name STRING OPTIONS( description='City name' ),
rec STRING OPTIONS( description='Grouping short name in upper case, 4 characters' ),
standardised_rec STRING OPTIONS( description='Grouping short name' ))
CLUSTER BY
rec,
name
#table options
OPTIONS ( description="Generate native table from external table",
labels=[("digital",
"lookup")]) AS
#SQL
SELECT
CAST (CID AS INT64) AS criteria_id,
name,
rec,
STD_REC AS standardised_rec
FROM
PROJECT.testing.external_table;
# *************************************************************
# drop external table #**************************************************************
DROP TABLE IF EXISTS PROJECT.testing.external_table;
  • Will the data be updated at anypoint
  • How extensive is the wrangling required and is your data structured
  • Who and how will this table be used
  • Is it a source supported by external tables

--

--

--

Lead data specialist, and programming obsessive. Specialising in data team development. Love statistical languages and focusing on the GCP platform on apps.

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

Use An API For Getting Chicago Soybean Prices

September 3 at 12:00 UTC for the long-awaited release of 1MillionNFTs

Control access across various data services in Hybrid Cloud

API Strategy — The iceberg below sea level

A Gentle Introduction to Big-Oh Notation Through Sorting Algorithms

The Anatomy of a Shiny Application

Structure of a Shiny web application

How to Setup a Python Virtual Environment

Our approach to digital verification

A black and white etching of of three men wearing Victorian era clothing looking into a microscope with three tubes.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Lace Chantelle Rogers

Lace Chantelle Rogers

Lead data specialist, and programming obsessive. Specialising in data team development. Love statistical languages and focusing on the GCP platform on apps.

More from Medium

Automating Big Query and Excel in Google Cloud Functions

BigQuery Pricing Model and Cost Optimization Recommendations

How to Pivot in Google BigQuery

Google Analytics and BigQuery: setting up your project