View find_contraint_ref_fields.sql
WITH constraint_ref_fields AS ( | |
SELECT | |
unnest(con1.conkey) AS local_key, | |
unnest(con1.confkey) AS ref_key, | |
con1.conrelid, | |
con1.confrelid | |
FROM | |
pg_class cl | |
JOIN pg_namespace ns ON cl.relnamespace = ns.oid | |
JOIN pg_constraint con1 ON con1.conrelid = cl.oid |
View index_without_hit.sql
SELECT | |
relid::regclass AS table, | |
indexrelid::regclass AS index, | |
pg_size_pretty(pg_relation_size(indexrelid::regclass)) AS index_size, | |
idx_tup_read, | |
idx_tup_fetch, | |
idx_scan | |
FROM | |
pg_stat_user_indexes | |
JOIN pg_index USING (indexrelid) |
View overall_status_of_indexes.sql
SELECT | |
COUNT(*) FILTER (WHERE idx_scan = 0) AS no_hit, | |
COUNT(*) FILTER (WHERE idx_scan > 0) AS used, | |
COUNT(*) AS total | |
FROM | |
pg_stat_user_indexes s | |
JOIN pg_index i ON (i.indexrelid = s.indexrelid) | |
WHERE | |
NOT i.indisprimary; |
View idempotent_script.sql
--Table | |
CREATE TABLE IF NOT EXISTS person ( | |
id integer NOT NULL, | |
person_name character varying(40) NOT NULL, | |
updated_date date, | |
CONSTRAINT person_pkey PRIMARY KEY (id) | |
); | |
--Index | |
CREATE INDEX IF NOT EXISTS idx_person_name ON person (person_name); |
View parameter_bind.rb
# Usage: ruby parameter_bind.rb mylogfile.log | |
# Output: every sql command parsed with its parameters | |
sourceFileName = ARGV[0] | |
targetFile = File.open("#{sourceFileName}.sql", 'w') | |
lines = File.read(sourceFileName, encoding: 'utf-8').scan(/(duration:.*)\sexecute.+:\s(.+)(\n.+parameters:(.+))?/) | |
lines.each do |line| | |
if line[2] != nil then |
View DataCopyManager.java
package br.com.michelmilezzi.database.data; | |
import java.io.File; | |
import java.io.FileOutputStream; | |
import java.io.IOException; | |
import java.sql.Connection; | |
import java.sql.SQLException; | |
import java.util.zip.ZipEntry; | |
import java.util.zip.ZipOutputStream; |
View aws_glue_avoiding_duplicates.py
import sys | |
import pydevd | |
from awsglue.transforms import * | |
from awsglue.utils import getResolvedOptions | |
from pyspark.context import SparkContext | |
from awsglue.context import GlueContext | |
from awsglue.dynamicframe import DynamicFrame | |
from pyspark.sql.functions import col | |
from awsglue.job import Job |