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

doit-mattporter

# Using n2-standard-4
# Using Windows Server version 1809 Datacenter Core for Containers, built on 20200813

powershell

# Create local SSD file system and mount
Get-Disk
Get-Disk | Where-Object PartitionStyle –Eq 'RAW' | Initialize-Disk
# For NVMe:
#New-Partition –DiskNumber 0 -AssignDriveLetter –UseMaximumSize