Skip to content

Instantly share code, notes, and snippets.

Avatar

Rob Cowie robcowie

View GitHub Profile
@robcowie
robcowie / rounded_div_function.sql
Created Oct 12, 2020
Postgresql division with rounding function
View rounded_div_function.sql
-- Handle zero-division by returning null. Round result to N decimal places
CREATE OR REPLACE FUNCTION rounded_safe_div(a numeric, b numeric, dp int = 2) RETURNS numeric AS
$$ SELECT round($1::numeric / nullif($2, 0), $3) $$
LANGUAGE SQL
IMMUTABLE
RETURNS NULL ON NULL INPUT;
@robcowie
robcowie / amazon_allowed_chars.py
Created Jul 22, 2020
Allowed characters for Amazon entities and keywords
View amazon_allowed_chars.py
# From https://advertising.amazon.com/API/docs/en-us/get-started/how-to-use-api#Keyword-bid-constraints-by-marketplace
# Broken decoding fixed, mostly. We've lost a few characters but they seem obscure.
# See http://www.i18nqa.com/debug/utf8-debug.html for explanation
ALLOWED_ENTITY_CHARS = {
"'",
' ',
'!',
'"',
'#',
@robcowie
robcowie / local_cloud_function.py
Created May 7, 2020
Tiny wrapper to serve a cloud function handler locally with Flask
View local_cloud_function.py
import os, sys
from pathlib import Path
from flask import Flask, request
sys.path.append(Path(".").absolute().as_posix())
from main import handler
app = Flask("name")
@robcowie
robcowie / dag_duration.sql
Created May 4, 2020
Airflow Task & DAG Duration Queries
View dag_duration.sql
-- duration of task instances
select
date(execution_date) as dt,
avg(duration) as avg_duration,
min(duration) as min_duration,
max(duration) as max_duration
from
task_instance
group by
date(execution_date)
@robcowie
robcowie / random_strings.sql
Created Apr 30, 2020
Generate random strings in Postgresql
View random_strings.sql
-- Fixed-length ascii strings
CREATE OR REPLACE FUNCTION pg_temp.random_string(int) RETURNS text
AS $$ SELECT
array_to_string(ARRAY(SELECT chr(ascii('B') + round(random() * 25)::integer)
FROM
generate_series(1, $1)), '') $$
LANGUAGE sql;
select pg_temp.random_string(8);
@robcowie
robcowie / gcloud_configurations.md
Created Apr 20, 2020
gcloud named configurations
View gcloud_configurations.md

Manage Local GCP Config

To see help for gcloud named configurations

gcloud topic configurations

To list & describe configurations

@robcowie
robcowie / postgresql_management_queries.sql
Last active Sep 23, 2020
Useful Postgresql Management Queries
View postgresql_management_queries.sql
-----------------------------------------------------------------------------------------------------------------------------------
-- USEFUL POSTGRESQL MANAGEMENT QUERIES --
-- See https://medium.com/compass-true-north/dealing-with-significant-postgres-database-bloat-what-are-your-options-a6c1814a03a5 --
-----------------------------------------------------------------------------------------------------------------------------------
-- SHOW RUNNING QUERIES
SELECT
state,
pid,
@robcowie
robcowie / fix_unicode.py
Created Mar 22, 2020
Fix unicode. Python.
View fix_unicode.py
# -*- coding: utf-8 -*-
"""OLD AND UNTESTED"""
import unicodedata
def fix_bad_unicode(text):
u"""
Something you will find all over the place, in real-world text, is text
that's mistakenly encoded as utf-8, decoded in some ugly format like
@robcowie
robcowie / pg_vacuuming_queries.md
Last active Feb 18, 2020
PostgreSQL vacuuming monitoring queries
View pg_vacuuming_queries.md

Postgresql Vacuuming

To monitor progress of autovacuum

SELECT
    heap_blks_scanned/cast(heap_blks_total as numeric) * 100 as heap_blks_percent,
    progress.*,
    activity.query
FROM
@robcowie
robcowie / marketplace.py
Created Nov 4, 2019
Amazon marketplaces with api endpoints
View marketplace.py
"""Amazon Marketplace and Region Info.
* [MWS docs](https://docs.developer.amazonservices.com/en_US/dev_guide/DG_Endpoints.html)
* [Advertising API docs](https://advertising.amazon.com/API/docs/en-us/get-started/how-to-use-api)
"""
from dataclasses import dataclass
@dataclass
You can’t perform that action at this time.