This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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 | |
) | |
" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
#!/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 |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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, |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
# 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); |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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" |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
google-cloud-bigquery-storage | |
pandas | |
pyarrow |
OlderNewer