adamantnz

/* Can be used to move a large amount of data from one table to another in chunks to keep transaction log and temp db under control */
CREATE PROCEDURE [SchemaName].[moveData]
AS
BEGIN
	DECLARE 
		 @row INT = 0
		,@step INT = 100000
		,@max INT

	SELECT @max = max(dv_keyid)

adamantnz

CREATE OR REPLACE VIEW dbo.vwdependencies 
AS
SELECT DISTINCT c_p.oid AS tbloid
	,n_p.nspname AS schemaname
	,c_p.relname AS NAME
	,n_c.nspname AS refbyschemaname
	,c_c.relname AS refbyname
	,c_c.oid AS viewoid
FROM pg_class c_p
JOIN pg_depend d_p ON c_p.relfilenode = d_p.refobjid

adamantnz

SELECT trim(pgdb.datname) AS DATABASE
	,trim(pgn.nspname) AS SCHEMA
	,trim(a.NAME) AS TABLE
	,b.mbytes
	,a.rows
FROM (
	SELECT db_id
		,id
		,NAME
		,sum(rows) AS rows

adamantnz

DECLARE
	 @SQL NVARCHAR(MAX) = NULL
	,@DB NVARCHAR(50) = ''
	,@newSchema NVARCHAR(50) = ''
	,@objectName NVARCHAR(50) = '' /* include existing schema */
BEGIN
	SET @SQL = 'USE '+@DB+' GO ALTER SCHEMA '+@newSchema+' TRANSFER '+@objectName+';'
	PRINT @SQL
	EXEC sp_executesql @SQL
END

adamantnz

/* show running queries */
select pid, user_name, starttime, query
from stv_recents
where lower(status) = 'running';

/* show recent completed queries */
select pid, user_name, starttime, query
from stv_recents
where lower(status) = 'done'
order by starttime desc;

adamantnz

get-content -tail 10 -wait -path (Get-ChildItem -Path C:\logs\...\ | Sort-Object LastAccessTime -Descending | Select-Object -First 1).FullName

adamantnz

from datetime import date, datetime, timedelta
import json
import boto3
from botocore.exceptions import ClientError
import os

ddbRegion = os.environ['AWS_DEFAULT_REGION']
now = datetime.now().strftime("-%Y-%m-%d-%H-%M-%S")
backupRetention = 7

adamantnz

/* 
    Dynamically creates Redshift "alter column" SQL for AWS "Cost and Usage Reports" based on the "tagmapping" data 
    Note: Redshift column names are case-sensitive so the first query renames the duplicate usertags (if any)
*/
update awsbillingYYYYMM_tagmapping
    set usertag = b.usertag || '_' || cast(sequence as varchar)
from awsbillingYYYYMM_tagmapping a
inner join (
    select row_number() over (
            partition by lower(usertag) order by usertag desc

adamantnz

import airflow
from airflow import DAG
from airflow.operators.python_operator import PythonOperator
from airflow.operators.bash_operator import BashOperator
from datetime import datetime, timedelta
from pathlib import Path


print(
    """This print is in the global state, so it will print this string 

adamantnz

import os

# this is in the global scope so will run each time the file is parsed :( 
folder = "my_folder"
if not os.path.exists(folder):
    os.makedirs(folder)

def create_folder(folder):
    # this will only run when the method is explicitly called, much better! :)
    if not os.path.exists(folder):