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 | |
from google.cloud import bigquery | |
import pandas as pd | |
# Specify your dataset | |
project_id = "your_project_here" | |
dataset_id = "your_dataset_here" | |
# Specify how far back you want to look through your job history, up to 180 days | |
job_history_lookback_days = 2 |
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 | |
from google.cloud import bigquery | |
import pandas as pd | |
client = bigquery.Client() | |
# Specify your dataset | |
project_id = "your_project_here" | |
dataset_id = "your_dataset_here" |
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
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 |
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 |
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
# 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
# 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
# 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
# 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
#!/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 |
NewerOlder