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

# ***************************************************************
# 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;

--

--

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