yujihamaguchi

-- refer: http://dev.classmethod.jp/cloud/aws/amazon-redshift-tutorial-tuning-table-design/
-- システム生成の列を除外する為にWHERE句にcol < 6がある
select col, max(blocknum)
from stv_blocklist b, stv_tbl_perm p
where (b.tbl=p.id) and name ='<table name>'
and col < 6
group by name, col
order by col;

yujihamaguchi

SET SEARCH_PATH TO <schema name>;
SELECT
  stv_tbl_perm.name AS table
  ,COUNT(*) AS mb
FROM
  stv_blocklist,
  stv_tbl_perm
WHERE
  stv_blocklist.tbl = stv_tbl_perm.id
  AND stv_blocklist.slice = stv_tbl_perm.slice

yujihamaguchi

=OFFSET(INDIRECT("Sheet2!A2"),0,0,COUNTA(INDIRECT("Sheet2!$A$2:$A$30")),1)

yujihamaguchi

SELECT
  owner
  ,name
  ,type
  ,referenced_link_name
  ,dependency_type
FROM
  dba_dependencies
WHERE
  referenced_owner = UPPER('&owner')

yujihamaguchi

-- Haskell
import System
import Text.ParserCombinators.Parsec
main = do args <- getArgs
          run parens (head args)
run :: Show a => Parser a -> String -> IO()
run p cs = case (parse p "" cs) of
             Left e -> print e
             Right x -> print x
parens :: Parser Int

yujihamaguchi

--[grep] 標準入力について、文字列パターンに一致する部分を持つ行を出力する。
-- [Usage] grep {pattern(regex)}
import System
import Text.Regex.Posix
main = do cs <- getContents
          args <- getArgs
          putStr $ unlines $ filter (¥str -> contain (head args) str) $ lines cs
contain :: String -> String -> Bool
contain cs cs' = cs' =‾ cs :: Bool

yujihamaguchi

SELECT
  nspname
  ,relname
  ,conname
  ,attnum
  ,attname
  ,conkey
FROM
  pg_constraint r1
  ,pg_namespace r2

yujihamaguchi

COPY <table name> FROM 's3://<bucket name>/<dir name>/'
credentials 'aws_access_key_id=<access key>;aws_secret_access_key=<secret key>'
NULL AS 'This is null value'
GZIP

COPY <table name> FROM 's3://<bucket name>/<dir name>/<file name>.csv'
credentials 'aws_access_key_id=<access key>;aws_secret_access_key=<secret key>' CSV DATEFORMAT 'YYYYMMDD' TRIMBLANKS

yujihamaguchi

UNLOAD ('SELECT * FROM <table name>')
TO 's3://<bucket name>/<dir name>/'
CREDENTIALS 'aws_access_key_id=<access key>;aws_secret_access_key=<secret key>'
NULL AS 'This is null value'
GZIP;

-- One File
UNLOAD ('SELECT * FROM <table name>')
TO 's3://<bucket name>/<dir name>/'
CREDENTIALS 'aws_access_key_id=<access key>;aws_secret_access_key=<secret key>'

yujihamaguchi

SELECT
  (SELECT relname FROM pg_class r2 WHERE r2.oid = r1.conrelid) AS table_name
  ,conname AS constraint_name
FROM
  pg_constraint r1