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 json | |
import shutil | |
import sys | |
from os import makedirs | |
from os.path import join | |
from time import sleep |
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 json | |
import random | |
import sys | |
from os.path import join | |
from time import sleep | |
from awscrt import io, mqtt |
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 time | |
from glob import glob | |
from os.path import join | |
from awscrt import io, mqtt |
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 Debian GNU/Linux 10 (buster) | |
# Create local SSD file system and mount | |
sudo mkfs.ext4 -F /dev/nvme0n1 | |
sudo mkdir -p /mnt/disks/nvme0n1 | |
sudo mount /dev/nvme0n1 /mnt/disks/nvme0n1 | |
sudo chmod a+w /mnt/disks/nvme0n1 | |
cd /mnt/disks/nvme0n1 | |
# Update |
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, |
OlderNewer