Week of June 11 Detailed Log
SELECT * FROM bigquery-public-data.samples.shakespeare Limit 10
Enable GCP Shell
gcloud auth login
$ bq ls
$ bq ls publicdata:samples
$ bq mk spikeysales_dataset
$ bq ls
$ bq rm spikeysales_dataset
$ bq show publicdata:samples
$ bq show publicdata:samples.shakespeare
$ bq query “SELECT * FROM publicdata:samples.shakespeare LIMIT 10”
Creating and Editing Access to a Dataset
bq show –format=prettyjson spikeysales_dataset > dataset_spikeysales.json
bq update –source dataset_spikeysales.json spikeysales_dataset
bq show –format=prettyjson spikeysales_dataset
Creating and Querying Ingestion Time Partitioned Tables
bq show –schema –format=prettyjson spikeysales_dataset.e_commerce_data
[
{
“mode”: “NULLABLE”,
“name”: “RowID”,
“type”: “INTEGER”
},
{
“mode”: “NULLABLE”,
“name”: “OrderID”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “OrderDate”,
“type”: “DATE”
},
{
“mode”: “NULLABLE”,
“name”: “ShipDate”,
“type”: “DATE”
},
{
“mode”: “NULLABLE”,
“name”: “ShipMode”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “CustomerID”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “CustomerName”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Segment”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Address_Country”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Address_City”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Address_State”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Address_Postal_Code”,
“type”: “INTEGER”
},
{
“mode”: “NULLABLE”,
“name”: “Address_Region”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Product_ID”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Category”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Sub_Category”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Product_Name”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Sales”,
“type”: “FLOAT”
},
{
“mode”: “NULLABLE”,
“name”: “Quantity”,
“type”: “INTEGER”
},
{
“mode”: “NULLABLE”,
“name”: “Discount”,
“type”: “FLOAT”
},
{
“mode”: “NULLABLE”,
“name”: “Profit”,
“type”: “FLOAT”
},
{
“mode”: “NULLABLE”,
“name”: “row_number”,
“type”: “INTEGER”
}
]
Creating Column Based Partitioned Tables Using the Command Line
bq mk –table –schema OrderID:STRING,OrderDate:DATE,Quantity:INTEGER –time_partitioning_field OrderDate spikey-bq-279714:spikeysales_dataset.order_data
bq –location=US query –nouse_legacy_sql –destination_table=spikey-bq-279714:spikeysales_dataset.order_data ‘SELECT OrderID,OrderDate,Quantity FROM spikeysales_dataset.e_commerce_data WHERE OrderDate is NOT NULL LIMIT 1000’
bq show spikeysales_dataset.order_data
bq –location=US query –nouse_legacy_sql ‘SELECT * FROM `spikey-bq-279714.spikeysales_dataset.order_data` WHERE Orderdate=DATE(“2014-11-01”)’
Populating Data into a Table with Nested Fields Using STRUCT
bq show –schema –format=prettyjson spikeysales_dataset.e_commerce_partitioned
[
{
“mode”: “NULLABLE”,
“name”: “RowID”,
“type”: “INTEGER”
},
{
“mode”: “NULLABLE”,
“name”: “OrderID”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “OrderDate”,
“type”: “DATE”
},
{
“mode”: “NULLABLE”,
“name”: “ShipDate”,
“type”: “DATE”
},
{
“mode”: “NULLABLE”,
“name”: “ShipMode”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “CustomerID”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “CustomerName”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Segment”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Address_Country”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Address_City”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Address_State”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Address_Postal_Code”,
“type”: “INTEGER”
},
{
“mode”: “NULLABLE”,
“name”: “Address_Region”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Product_ID”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Category”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Sub_Category”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Product_Name”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Sales”,
“type”: “FLOAT”
},
{
“mode”: “NULLABLE”,
“name”: “Quantity”,
“type”: “INTEGER”
},
{
“mode”: “NULLABLE”,
“name”: “Discount”,
“type”: “FLOAT”
},
{
“mode”: “NULLABLE”,
“name”: “Profit”,
“type”: “FLOAT”
},
{
“mode”: “NULLABLE”,
“name”: “row_number”,
“type”: “INTEGER”
}
]
Design Schema with STRUCT
[
{
“mode”: “NULLABLE”,
“name”: “RowID”,
“type”: “INTEGER”
},
{
“mode”: “NULLABLE”,
“name”: “OrderInfo”,
“type”: “RECORD”,
“fields”: [
{
“mode”: “NULLABLE”,
“name”: “OrderID”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “OrderDate”,
“type”: “DATE”
},
{
“mode”: “NULLABLE”,
“name”: “Sales”,
“type”: “FLOAT”
},
{
“mode”: “NULLABLE”,
“name”: “Quantity”,
“type”: “INTEGER”
}
]
},
{
“mode”: “NULLABLE”,
“name”: “ShipDate”,
“type”: “DATE”
},
{
“mode”: “NULLABLE”,
“name”: “ShipMode”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “CustomerID”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “CustomerName”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Segment”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Address”,
“type”: “RECORD”,
“fields”: [
{
“mode”: “NULLABLE”,
“name”: “Country”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “City”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “State”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Postal_Code”,
“type”: “INTEGER”
},
{
“mode”: “NULLABLE”,
“name”: “Region”,
“type”: “STRING”
}
]
},
{
“mode”: “NULLABLE”,
“name”: “Product_ID”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Category”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Sub_Category”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Product_Name”,
“type”: “STRING”
},
{
“mode”: “NULLABLE”,
“name”: “Discount”,
“type”: “FLOAT”
},
{
“mode”: “NULLABLE”,
“name”: “Profit”,
“type”: “FLOAT”
},
{
“mode”: “NULLABLE”,
“name”: “row_number”,
“type”: “INTEGER”
}
]
Populate Data from existing Table
INSERT INTO
`spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
(ROWID,
OrderInfo,
ShipDate,
ShipMode,
CustomerID,
CustomerName,
Segment,
Address,
Product_ID,
Category,
Sub_Category,
Product_Name,
Discount,
Profit )
SELECT
RowID,
STRUCT (OrderID, OrderDate, Sales, Quantity),
ShipDate,
ShipMode,
CustomerID,
CustomerName,
Segment,
STRUCT (Address_Country,
Address_City,
Address_State,
Address_Postal_Code,
Address_Region),
Product_ID,
Category,
Sub_Category,
Product_Name,
Discount,
Profit
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_partitioned`
Working with Repeated Fields
INSERT INTO
`spikey-bq-279714.spikeysales_dataset.customer_order_details`
(customer_name,order_id, product_names)
VALUES
(“John”, 434, [‘Trousers’, ‘Notebook’, ‘Football’,’Avocado’])
Populating Tables with Repeated Fields Using ARRAY_AGG
INSERT INTO
`spikey-bq-279714.spikeysales_dataset.customer_product_price` (
CustomerID,
Products_Bought,
Price_Paid )
SELECT
CustomerID,
ARRAY_AGG(Product_Name) AS Products_Bought,
ARRAY_AGG(Sales) AS Price_Paid
FROM
`spikey-bq-279714.spikeysales_dataset.e_commerce_data`
GROUP BY
CustomerID
Using Nested and Repeated Fields Together
SELECT
Address_City,
ARRAY_AGG (STRUCT (CustomerID,
CustomerName)) AS CustomerInfo
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data`
GROUP BY
Address_City
ORDER BY
Address_City
SELECT
Address_State,
ARRAY_AGG (STRUCT (OrderDate,
OrderID,
Sales,
Quantity)) AS OrderInfoInfo
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data`
GROUP BY
Address_State
ORDER BY
Address_State
Using UNNEST to Query Repeated Fields
SELECT CustomerID, Products
FROM `spikey-bq-279714.spikeysales_dataset.customer_product_price`,UNNEST(Products_Bought) as Products
LIMIT 1000
SELECT CustomerID, Products, Price
FROM `spikey-bq-279714.spikeysales_dataset.customer_product_price`,UNNEST(Products_Bought) as Products, UNNEST(Price_paid) as
Price
LIMIT 1000
SELECT
Address_State,
Orders
FROM `spikey-bq-279714.spikeysales_dataset.orders_by_state` ,
UNNEST( OrderInfoInfo ) as Orders
LIMIT 1000
Aggregations
SELECT
ANY_VALUE(OrderInfo) AS Any_Order
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
WHERE
Address.State = “California”
LIMIT 1000
SELECT
CustomerID, CustomerName, AVG( OrderInfo.Sales) AS Average_Price
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
GROUP BY
CustomerID, CustomerName
ORDER BY
Average_Price DESC
SELECT
CustomerID, CustomerName, COUNTIF( OrderInfo.Sales>100) AS Num_Over_100
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
GROUP BY
CustomerID, CustomerName
ORDER BY
Num_Over_100 DESC
SELECT
STRING_AGG(CustomerName)
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
SELECT
FORMAT(“%T”, ARRAY_AGG(Address.City)) AS Cities
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
SELECT
FORMAT(“%T”, ARRAY_AGG(DISTINCT Address.City)) AS Cities
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
SELECT
FORMAT(“%T”, ARRAY_AGG(Address.City LIMIT 5)) AS Cities
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
Subqueries
SELECT
r.OrderInfo.OrderID,
r.Product_ID,
r.CustomerName
FROM (
SELECT * FROM
`spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`) As r
ORDER BY
OrderInfo.OrderDate ASC
SELECT
r.segment,
SUM(r.sales)
FROM (
SELECT * FROM
`spikey-bq-279714.spikeysales_dataset.e_commerce_data`
WHERE Address_State =”California”) As r
GROUP BY
Segment
SELECT
Address_State,
SUM(sales) AS Total_Sales
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data`
WHERE Address_State IN (
SELECT
Address_State
FROM
`spikey-bq-279714.spikeysales_dataset.e_commerce_data`
WHERE
Shipmode = ‘Same Day’)
GROUP BY Address_State
Performing Window Operations Using “Partition By” and “Order By”
SELECT
Segment, SUM(OrderInfo.Sales) OVER (PARTITION BY Segment)
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
SELECT DISTINCT
Segment, SUM(OrderInfo.Sales) OVER (PARTITION BY Segment)
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
SELECT CustomerID, CustomerName, OrderInfo.Sales,
Address.State,
RANK() OVER (PARTITION BY Address.State ORDER BY OrderInfo.Sales DESC)
FROM `spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
SELECT
state,
city,
Total_sales,
PERCENT_RANK() OVER (PARTITION BY State ORDER BY Total_Sales ASC) Percentile_Rank
FROM (
SELECT
Address.State AS State,
Address.City AS City,
SUM(OrderInfo.Sales) AS Total_Sales
FROM
`spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
GROUP BY
Address.State,
Address.City )
Windowing Operations Using a Window Range
SELECT
state,
city,
Total_sales,
SUM(Total_Sales) OVER (PARTITION BY State ORDER BY Total_Sales ASC RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
FROM (
SELECT
Address.State AS State,
Address.City AS City,
SUM(OrderInfo.Sales) AS Total_Sales
FROM
`spikey-bq-279714.spikeysales_dataset.e_commerce_data_nested`
GROUP BY
Address.State,
Address.City )
datalab create bq-datalab-vm
Day!
Day!
As the new week started, I was still basking in the radiance of this great coding victory. So, I decided to be a bit ambitious and move this gem 
to the cloud 
which would be like the crème de la crème of our learnings thus far. Cloud, Python/Pandas, Streaming market data, and Postgres all wrapped up in one! Complete and utter awesomeness! 
in our head, we spun up our Ubuntu Image and we followed along the well documented steps on AJ’s 
with gasoline 
! We cloned AJ’s Github repo, modified the code with our new changes, and executed the syntax and just as we were ready to declare victory… Stack overflow Error! 
Oh, the pain.
in the hole but with some trepidation that I wasn’t being too much of irritant.
was a dyspepsia on Ubuntu on Azure. It was certainly a mystery but one that can only be solved by simply rewriting the code.
Day!
So now where to turn? Who can help us navigate these uncharted territories? 
out for help to the community on 
victory lap or 15. To my amazement I actually set my best PR for a mile and ran and solid 7:02 per mile for 
