edonosotti

# ==================================================
# Create a BigQuery Data Transfer job with Terraform
# ==================================================
# This code was written for this article:
# https://medium.com/@edonosotti/importing-big-data-in-bigquery-with-data-transfer-service-and-terraform-dc55fdfdc821
#
# CSV file template: <<EOF
# customer_id,card_id,amount
# 1,0001,10.00
# 2,0002,2.00

edonosotti

# ========================================================
# Create Scheduled Queries for BigQuery in Google Cloud
# with Terraform
# ========================================================
#
# This script programmatically creates a Scheduled Query
# in BigQuery. It contains a workaround for the
# authentication issues that can occasionally occur
# when automating the resource creation in GCP.
#

edonosotti

-- Simulate a table containing the total sales for the last 7 days
WITH sales_total AS (
    SELECT 1000 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 1 DAY) AS day UNION ALL
    SELECT  850 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 2 DAY) AS day UNION ALL
    SELECT 1200 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 3 DAY) AS day UNION ALL
    SELECT  700 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 4 DAY) AS day UNION ALL
    SELECT 1050 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 5 DAY) AS day UNION ALL
    SELECT  900 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 6 DAY) AS day UNION ALL
    SELECT  800 AS total_sales, DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY) AS day
)

edonosotti

# ========================================================
# Create Google Cloud Projects from scratch with Terraform
# ========================================================
#
# This script is a workaround to fix an issue with the
# Google Cloud Platform API that prevents to fully
# automate the deployment of a project _from scratch_
# with Terraform, as described here:
# https://stackoverflow.com/questions/68308103/gcp-project-creation-via-api-doesnt-enable-service-usage-api
# It uses the `gcloud` CLI:

edonosotti

# -----------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - zipcodes
# -----------------------------------------------------------------

# Create a temporary, in memory table
WITH IconicUSLocations AS (
	SELECT 1 AS id, 'Empire State Building' AS name, 40.748170 AS lat, -73.985000 AS lon UNION ALL
	SELECT 2, 'Golden Gate Bridge', 37.810181, -122.477318 UNION ALL
	SELECT 3, 'Yosemite National Park', 37.8532261, -119.6911924 UNION ALL
	SELECT 4, 'Grand Canyon', 36.0997623, -112.1212394 UNION ALL

edonosotti

# ---------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - states
# ---------------------------------------------------------------

# Create a temporary, in memory table
WITH IconicUSLocations AS (
	SELECT 1 AS id, 'Empire State Building' AS name, 40.748170 AS lat, -73.985000 AS lon UNION ALL
	SELECT 2, 'Golden Gate Bridge', 37.810181, -122.477318 UNION ALL
	SELECT 3, 'Yosemite National Park', 37.8532261, -119.6911924 UNION ALL
	SELECT 4, 'Grand Canyon', 36.0997623, -112.1212394 UNION ALL

edonosotti

# -----------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - counties
# -----------------------------------------------------------------

# Create a temporary, in memory table
WITH IconicUSLocations AS (
	SELECT 1 AS id, 'Empire State Building' AS name, 40.748170 AS lat, -73.985000 AS lon UNION ALL
	SELECT 2, 'Golden Gate Bridge', 37.810181, -122.477318 UNION ALL
	SELECT 3, 'Yosemite National Park', 37.8532261, -119.6911924 UNION ALL
	SELECT 4, 'Grand Canyon', 36.0997623, -112.1212394 UNION ALL

edonosotti

# ---------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - cities
# ---------------------------------------------------------------

# Create a temporary, in memory table
WITH IconicUSLocations AS (
	SELECT 1 AS id, 'Empire State Building' AS name, 40.748170 AS lat, -73.985000 AS lon UNION ALL
	SELECT 2, 'Golden Gate Bridge', 37.810181, -122.477318 UNION ALL
	SELECT 3, 'Yosemite National Park', 37.8532261, -119.6911924 UNION ALL
	SELECT 4, 'Grand Canyon', 36.0997623, -112.1212394 UNION ALL

edonosotti

# -------------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - zipcodes
#
# WARNING: This query uses the OLD public datasets.
# Find the NEW version at:
# https://gist.github.com/edonosotti/faa1c9dc0ffd402bff6df50ff56cfa50
#
# The NEW version also fixed the `zipcode_geom` column in the
# `zipcode_area` table, incorrectly stored as a STRING type.
# It does not require to be casted to GEOMETRY on-the-fly with the

edonosotti

# -------------------------------------------------------------------
# Reverse US GeoCoding in BigQuery using public datasets - states
#
# WARNING: This query uses the OLD public datasets.
# Find the NEW version at:
# https://gist.github.com/edonosotti/f998aea189ad6f68c44e8a92387bcebf
# -------------------------------------------------------------------

# Create a temporary, in memory table
WITH IconicUSLocations AS (