Skip to content

Instantly share code, notes, and snippets.

Avatar

Matthew Porter doit-mattporter

  • DoiT International
  • Eugene, OR
View GitHub Profile
@doit-mattporter
doit-mattporter / import_temperature_csv.sh
Created Feb 9, 2021
Import Kaggle-provided temperature dataset into BigQuery
View import_temperature_csv.sh
bq load \
--replace \
--skip_leading_rows 1 \
--source_format=CSV \
${PROJECT_ID}:sensordata.temperature \
gs://${BUCKET_NAME}/temperature.csv.gz \ timestamp_utc:DATETIME,timestamp_epoch:INTEGER,temp_f:FLOAT,temp_c:FLOAT,device_id:INTEGER
@doit-mattporter
doit-mattporter / open_window_alert_requirements.txt
Created Feb 7, 2021
Requirements for the open_window_alert.py gist
View open_window_alert_requirements.txt
google-cloud-bigquery-storage
pandas
pyarrow
@doit-mattporter
doit-mattporter / open_window_alert.py
Created Feb 7, 2021
Cloud Function for sending an e-mail alert on a window being open too long
View open_window_alert.py
import smtplib
from email.mime.multipart import MIMEMultipart
from email.mime.text import MIMEText
from google.cloud import bigquery
from google.cloud import bigquery_storage
def open_window_alert(_):
project_id = "YOUR_PROJECT_ID"
from_email = "YOUR_EMAIL"
@doit-mattporter
doit-mattporter / bigquery_ml_predict.sh
Created Feb 6, 2021
# Predict whether window was open in the last 600 seconds
View bigquery_ml_predict.sh
# Predict whether window was open in the last 30 seconds
cat >predict.sql << EOL
DECLARE now_time DATETIME;
DECLARE lookback_time DATETIME;
DECLARE monitor_window INT64;
DECLARE lookback_time_monitor_window DATETIME;
SET now_time = CURRENT_DATETIME();
SET lookback_time = DATETIME_SUB(now_time, INTERVAL 2400 SECOND);
SET monitor_window = 600; --The amount of time a window should be allowed to be open. Max value of 1800s.
SET lookback_time_monitor_window = DATETIME_SUB(now_time, INTERVAL monitor_window SECOND);
View temperature_automl_model.sh
# Construct AutoML model
bq query --nouse_legacy_sql \
"CREATE OR REPLACE MODEL ${PROJECT_ID}.sensordata.temperature_automl_model
OPTIONS(MODEL_TYPE=\"AUTOML_CLASSIFIER\",
BUDGET_HOURS=24.0,
INPUT_LABEL_COLS=[\"object_code\"])
AS SELECT * EXCEPT (timestamp_utc, timestamp_epoch) FROM ${PROJECT_ID}.sensordata.temperature_pivoted_timediff"
@doit-mattporter
doit-mattporter / temperature_pivoted_timediff.sh
Created Feb 5, 2021
Create a table with changes in temperature recorded from a device's current value to the value x seconds ago: 5, 15, 30, 60, 180, 300, and 600 seconds
View temperature_pivoted_timediff.sh
# Create a table with changes in temperature recorded from a device's current value to the value x seconds ago: 5, 15, 30, 60, 180, 300, and 600 seconds
bq query --nouse_legacy_sql \
"CREATE OR REPLACE TABLE ${PROJECT_ID}.sensordata.temperature_pivoted_timediff PARTITION BY DATE(timestamp_utc) CLUSTER BY timestamp_epoch AS (
SELECT
timestamp_utc,
timestamp_epoch,
object_code,
device_FrontOfLivingRoom,
device_LivingRoomCycleWindow,
device_LivingRoomKitchenCorner,
@doit-mattporter
doit-mattporter / temperature_pivoted.sh
Created Feb 5, 2021
Create a streaming temperature data pivot table such that all temps for any given second are recorded in one row, one column per device
View temperature_pivoted.sh
# Create a streaming temperature data pivot table such that all temps for any given second are recorded in one row, one column per device
# Also, exclude timepoints where not all three devices were powered
bq query --nouse_legacy_sql \
"CREATE OR REPLACE TABLE ${PROJECT_ID}.sensordata.temperature_pivoted PARTITION BY DATE(timestamp_utc) CLUSTER BY timestamp_epoch AS (
SELECT * FROM (
SELECT
t.timestamp_utc,
ANY_VALUE(t.timestamp_epoch) AS timestamp_epoch,
ANY_VALUE(IF(device_id = 2753085432003927, temp_f, null)) AS device_FrontOfLivingRoom,
ANY_VALUE(IF(device_id = 2582277131565505, temp_f, null)) AS device_LivingRoomCycleWindow,
@doit-mattporter
doit-mattporter / window_opened_closed.sh
Created Feb 5, 2021
Create a BQ table of open & closed window time frames from a CSV on GCS
View window_opened_closed.sh
#!/usr/bin/env bash
PROJECT_ID=iottempstreaming
DATA_BUCKET=iot-window-open-close-times
bq load \
--autodetect \
--source_format=CSV \
$PROJECT_ID:sensordata.window_opened_closed \
gs://$DATA_BUCKET/OpeningAndClosingTimes.csv
@doit-mattporter
doit-mattporter / window_opened_closed_utc.sh
Created Feb 5, 2021
Create a table of open & closed window timeframe datetimes in UTC
View window_opened_closed_utc.sh
# Convert table of open and closed window timeframes to UTC
bq query --nouse_legacy_sql \
"CREATE OR REPLACE TABLE ${PROJECT_ID}.sensordata.window_opened_closed_utc AS (
SELECT
DATETIME(TIMESTAMP(CONCAT(DayPST, \"T\", FORMAT_TIMESTAMP(\"%H:%M:%S\", PARSE_TIMESTAMP(\"%r\", StartTimePST))), \"America/Los_Angeles\"), \"UTC\") AS StartTime,
DATETIME(TIMESTAMP(CONCAT(DayPST, \"T\", FORMAT_TIMESTAMP(\"%H:%M:%S\", PARSE_TIMESTAMP(\"%r\", EndTimePST))), \"America/Los_Angeles\"), \"UTC\") AS EndTime,
ObjectCode
FROM ${PROJECT_ID}.sensordata.window_opened_closed
)
"
@doit-mattporter
doit-mattporter / publish_temps.py
Created Dec 15, 2020
Stream real-time RaspberryPi-derived (or simulated) temperature values to GCP IoT Core
View publish_temps.py
#!/usr/bin/env python3
import argparse
import configparser
import datetime
import json
import jwt
import paho.mqtt.client as mqtt
import random
import ssl
import time