Skip to content

Instantly share code, notes, and snippets.

View doit-mattporter's full-sized avatar

Matthew Porter doit-mattporter

1 follower · 1 following
  • DoiT International
  • Eugene, OR
View GitHub Profile
@doit-mattporter
doit-mattporter / gsutil_s5cmd_linux_comparison.sh
Created September 2, 2020 05:49
# 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
@doit-mattporter
doit-mattporter / table_access_patterns.py
Last active July 14, 2023 23:41
This Python script uses the Google Cloud BigQuery API to query a specified BigQuery dataset's job history, returning information about the total number of queries made to each table, the users who made those queries, the number of queries each user made, their last query, and the timestamp of their last query, sorted by the number of total queri…
#!/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
@doit-mattporter
doit-mattporter / dataset_tables_metadata.py
Last active July 14, 2023 23:37
Utilizes the Google Cloud BigQuery API to retrieve and display metadata of all tables in a specified BigQuery dataset, including details such as table size, row count, creation time, last modified time, partitioning and clustering information, as well as an estimate of monthly storage cost
#!/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"
@doit-mattporter
doit-mattporter / connect_rpi_to_iot_core.py
Created June 16, 2020 14:19
Example showing how to connect a Raspberry Pi to IoT Core via a fleet provisioning template and a bootstrap certificate
#!/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
@doit-mattporter
doit-mattporter / pubsub_simulated_temps.py
Created June 16, 2020 15:18
Example of how to publish messages to an AWS IoT topic and print those messages back via a subscription to that topic
#!/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
@doit-mattporter
doit-mattporter / publish_temps.py
Created June 16, 2020 15:25
Example of how to publish DS18B20 temperature sensor values from a Raspberry Pi to AWS IoT
#!/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
@doit-mattporter
doit-mattporter / import_temperature_csv.sh
Created February 9, 2021 21:50
Import Kaggle-provided temperature dataset into BigQuery
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
doit-mattporter / open_window_alert_requirements.txt
Created February 7, 2021 04:17
Requirements for the open_window_alert.py gist
google-cloud-bigquery-storage
pandas
pyarrow
@doit-mattporter
doit-mattporter / open_window_alert.py
Created February 7, 2021 04:15
Cloud Function for sending an e-mail alert on a window being open too long
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
doit-mattporter / bigquery_ml_predict.sh
Created February 6, 2021 00:48
# Predict whether window was open in the last 600 seconds
# 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);