Skip to content

Instantly share code, notes, and snippets.

@mikeharding
mikeharding / extract-sql-server-ddl.ps1
Last active October 24, 2023 15:54
Get SQL Server DDL for Snowflake
#
# version 2.1 Derrick Cole, Snowflake Computing
#
# see co-located Revision-History.txt for additional information
#
<#
.SYNOPSIS
@mikeharding
mikeharding / snowsql_cons.py
Last active October 17, 2023 23:02
Python module to read SnowSQL config file for Snowflake connection information
import configparser
from pathlib import Path
home = Path.home()
config_path = Path.home().joinpath( '.snowsql', 'config' )
def main():
print(f"Use as an import. User's home directory is {home}")
def retrieve(key):
@mikeharding
mikeharding / tracking.sql
Last active September 2, 2021 15:46
Instrument Snowflake Queries using a Query Tracker
alter session set QUERY_TAG = 'MY_TAG';
select
//QTR:Query_123//
start_station_id, end_station_id,
count(1) num_trips,
avg(datediff("minute", starttime, stoptime))::integer avg_duration_mins
from trips
group by 1, 2
order by 3 desc;
-- Load NYC Yellow Cab Taxi data into Snowflake
-- 09/01/2020
-- https://registry.opendata.aws/nyc-tlc-trip-records-pds/
create database nyc_taxi;
create or replace stage taxi_stage url='s3://nyc-tlc/';
list @taxi_stage/ pattern = '.*trip.data/yellow.*';
@mikeharding
mikeharding / streams_tasks.sql
Last active February 16, 2022 12:30
Snowflake Streams and Tasks
----------------------------------------------------------------
-- Streams - Change Data Capture (CDC) on Snowflake tables
-- Tasks - Schedule execution of a statement
-- MERGE - I/U/D based on second table or subquery
----------------------------------------------------------------
-- reset the example
drop table source_table;
drop table target_table;
drop stream source_table_stream;
@mikeharding
mikeharding / keybase.md
Last active October 31, 2024 22:29
keybase.md

Keybase proof

I hereby claim:

  • I am mikeharding on github.
  • I am mharding (https://keybase.io/mharding) on keybase.
  • I have a public key ASAIoVRwel6MuW7H3jtvUQLEAnvStzXr7jhzKjOVu7FglAo

To claim this, I am signing this object:

@mikeharding
mikeharding / 1_get_repos.sh
Created October 17, 2016 15:56 — forked from mfernest/0_prechecks.sh
CM Path B Installation
#!/bin/sh
source ./control.sh
function getMySQLRepo() {
cd /tmp
wget --quiet —-no-check-certificate https://dev.mysql.com/get/mysql-community-release-el6-5.noarch.rpm
yum -y localinstall mysql-community-release-el6-5.noarch.rpm
cd -
}
@mikeharding
mikeharding / MasterCalendar_v2.qvs
Last active April 1, 2019 21:37
Another Qlik Master Calendar example
//****************************************************************************************
// This script builds a master calendar table with both fiscal and calendar based fields
//****************************************************************************************
// This is the number of months that the fiscal periods are offset against the calendar
// periods. A value of 4 results in a April to March fiscal year.
SET vFiscalOffset = 4;
// Assign values directly just for SIB
//----------------------------------------------------------------------------------------
@mikeharding
mikeharding / SetAnalysis.qvs
Last active April 1, 2019 21:36
Commonly used Qlik set analysis expressions.
// Sum of last year Sales
Sum({$<Year = {$(=Max(Year)-1)}>} Sales)
// Sum of Sales for year = 2013
Sum({<Year = {2013}> Sales}
@mikeharding
mikeharding / MasterCalendar.qvs
Last active August 29, 2015 13:57
QlikView script to generate a master calendar
QuartersMap:
MAPPING LOAD * Inline [
Month, Q
1, Q1
2, Q1
3, Q1
4, Q2
5, Q2
6, Q2
7, Q3