doit-mattporter

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

google-cloud-bigquery-storage
pandas
pyarrow

doit-mattporter

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

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

doit-mattporter

# 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

# 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

# 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

#!/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

# 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

#!/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