Skip to content

Instantly share code, notes, and snippets.

View agalea91's full-sized avatar

Alexander Galea agalea91

102 followers · 9 following
View GitHub Profile
@agalea91
agalea91 / sqlalchemy_execute_raw_sql.py
Last active February 19, 2021 15:55
Run raw SQL and read/write from SQL database with Pandas using Sqlalchemy
def get_engine():
import sqlalchemy
import os
# os.environ["DB_URL"] = "postgresql://USER:PASS@HOST:PORT/DATABASE"
engine = sqlalchemy.create_engine(os.environ["DB_URL"])
return engine
def close_engine(engine):
engine.dispose()
@agalea91
agalea91 / ssh_pd_read_csv.py
Last active January 5, 2021 00:17
Read CSV file on remote server over SSH, using local system RSA auth
SSH_HOST = ""
SSH_USER = ""
SSH_FILE_PATH = ""
COMPRESSION = "zip"
from paramiko import SSHClient
client = SSHClient()
client.load_system_host_keys()
client.connect(SSH_HOST, username=SSH_USER)
@agalea91
agalea91 / s3_gs_bigquery_upload.sh
Last active October 28, 2020 05:36
Upload from s3 to gs and then to bigquery
aws s3 cp s3://my_bucket/my_table /tmp/my_table --recursive \
&& gcloud config set account <name>@<project>.iam.gserviceaccount.com \
&& gcloud config set project <project> \
&& gcloud auth activate-service-account <name>@<project>.iam.gserviceaccount.com --key-file /my_path_to/google_credentials.json
&& gsutil cp -r /tmp/my_table gs://bucket_name/my_table \
&& bq mk --dataset my_dataset \
&& bq mk --table --schema my_table_schema.json my_dataset.my_table \
&& bq query --nouse_legacy_sql 'delete from my_dataset.my_table where <date_range_where_clause>;' \
&& bq load --source_format=CSV --quote "" -F='\t' my_dataset.my_table gs://bucket_name/my_table/0000_part_00 \
&& bq load --source_format=CSV --quote "" -F='\t' my_dataset.my_table gs://bucket_name/my_table/0001_part_00 \
@agalea91
agalea91 / redshift_unload_to_s3.sql
Last active October 24, 2020 02:21
Redshift unload to s3
unload ('
select * from my_table_name
where <date_range_where_clause>
')
to 's3://my-bucket/my_table_name/'
credentials 'aws_access_key_id=<key_id>;aws_secret_access_key=<secret_key>'
delimiter '\t'
allowoverwrite
;
@agalea91
agalea91 / fixed_dynamic_dag.py
Last active September 17, 2020 05:52
For medium blog post: Airflow Dynamic DAGs - Python Globals
for symbol in ("BTC", "ETH", "LTC", "XLM"):
dag = create_dag(symbol=symbol)
globals()["{}_dag".format(symbol.lower())] = dag
@agalea91
agalea91 / broken_dynamic_dag.py
Last active September 17, 2020 05:48
For medium blog post: Airflow Dynamic DAGs - Python Globals
def create_dag(symbol):
with DAG(
"email_{}_price".format(symbol.lower()),
default_args={"start_date": "2020-01-01"},
schedule_interval="0 0 * * *",
) as dag:
get_price_task = PythonOperator(
task_id="get_price",
python_callable=get_price,
op_kwargs=dict(
@agalea91
agalea91 / static_dag.py
Last active September 17, 2020 02:54
For medium blog post: Airflow Dynamic DAGs - Python Globals
from airflow import DAG
from airflow.operators.python_operator import PythonOperator
from crypto_prices import get_price, send_email
with DAG(
"email_bitcoin_price",
default_args={"start_date": "2020-01-01"},
schedule_interval="0 0 * * *",
) as dag:
@agalea91
agalea91 / github_deploy_key_setup.md
Created June 24, 2020 15:59
How to setup a deploy key for repo on shared server environment

GitHub Deploy Key Setup

  1. Generate key
ssh-keygen
...
cp ./key-name* ~/.ssh
  1. Go to GitHub repo and add key-name.pub
@agalea91
agalea91 / config
Created June 16, 2020 08:32
Example of an ~/.ssh/config file for github RSA deploy key auth for web app
# 1. Create new RSA key with "ssh-keygen"
# 2. Add as deploy key for repo on github
# 3. Add a new block to your ~/.ssh/config like below
# 4. Clone the repo with "git@github-repo-name:User/repo"
Host github.com
HostName github.com
IdentityFile /path/to/your/personal/github/private/key
Host github-repo-name
@agalea91
agalea91 / get_valid_slug_filename.py
Created May 22, 2020 16:28
def get_valid_filename(s):
"""
Return the given string converted to a string that can be used for a clean
filename. Remove leading and trailing spaces; convert other spaces to
underscores; and remove anything that is not an alphanumeric, dash,
underscore, or dot.
# https://github.com/django/django/blob/master/django/utils/text.py
"""
s = str(s).strip().replace(' ', '_')
s = re.sub(r'(?u)[^-\w.]', '', s)