Amazon Redshiftで良く使いそうなSQL文をスクリプトで作成してみた
下記エントリではRedshiftで使うSQL文を自分なりにまとめてみましたが、当エントリではその姉妹編という形で、『良く使いそうなSQL文をスクリプトで作成してみた』ものをまとめたものになります。SQL編同様、『コレは！』と思ったものについては適宜追加・更新を行っていきたいと思います。ではではスタート。
目次
- 項目の最大バイト数を求める[by Groovy]
- 作成済みのテーブル定義からCREATE TABLE文を生成する[by Groovy]
- Publicスキーマに対して参照権限のみ持つユーザーの新規作成を一発で行う(スクリプトを作成する)[by Groovy]
項目の最大バイト数を求める(代替策)
CSVデータを投入する際、項目桁数が明確となっていない場合は一旦ざっくり桁数を多目に取って投入、その後『実際の最大桁数でどれ位取ってるのか』等という形で項目桁数を確認する事もあるかと思います。全角文字等を含む場合はバイト数レベルで算出する必要が出てきます。
で、この関数。うってつけのものが用意されてるじゃ〜ん、と思いきや...
これはリーダーノード関数です。この関数は、ユーザー作成テーブル、STL または STV システムテーブル、SVV または SVL システムビューを参照する場合、エラーを返します。
ヽ(｀д´)ﾉ ｳﾜｧｧｧｧｧｧﾝ
なんてこったいw という訳で代替案として投入前のCSVに対してGroovyで長さをチェックするスクリプトを組んでみました。
test.csv
1,羽生結弦 2,浅田真央 3,Julia Viacheslavovna Lipnitskaia 4,エフゲニー・プルシェンコ
check.groovy
File file = new File("/path/to/test.csv");
int maxbytes = 0
String maxbytesString = "";
file.each {
println it
String[] array = it.tokenize(",");
name = array[1];
if (maxbytes < name.getBytes().size()) {
maxbytes = name.getBytes().size()
maxbytesString = name
}
}
println("項目最大値は[" + maxbytesString + "]の[" + maxbytes + "]バイトです");
結果はこうなります。桁数としては36バイト以上を用意しておけば良い、という形になりますね。
1,羽生結弦 2,浅田真央 3,Julia Viacheslavovna Lipnitskaia 4,エフゲニー・プルシェンコ 項目最大値は[エフゲニー・プルシェンコ]の[36]バイトです
作成済みのテーブル定義からCREATE TABLE文を生成する
CREATE TABLE文でテーブル作成した後、最適な設計を目指すべく分散キー(diskey)やソートキー(sortkey)、カラム毎の圧縮タイプ等を試行錯誤して『あれ、結局今定義どうなってるんだっけ？』となる事が時々あります。ソースコード管理してれば何ら問題無いですが、修正の都度コミットするのも手間っちゃぁ手間ですよね。と言う訳で現在のテーブル定義からCREATE SQL文をリバースエンジニアするプログラムを組んでみました。Groovyで書いてますが中身は大した事してないですw
以下プログラムです。SQLは上述のものをそのまま利用しています。参照対象のテーブル名及びスキーマ名を個別に指定して下さい。
import groovy.sql.Sql;
sql = Sql.newInstance(
"(Redshiftクラスタに接続する際のJDBC URL)",
"(接続ユーザー名)",
"(接続パスワード)",
"org.postgresql.Driver");
String tableName;
List<String> columnList = new ArrayList<String>();
List<String> columnCommentList = new ArrayList<String>();
List<String> typeList = new ArrayList<String>()
List<String> encodingList = new ArrayList();
String distkey;
Map<Integer, String> sortKeyMap = new HashMap<Integer,String>();
List<String> isNullList = new ArrayList();
sql.eachRow('''
SELECT
pg_table_def.schemaname AS schema_name,
pg_table_def.tablename AS table_name,
def_comment.table_comment,
pg_table_def.column,
def_comment.column_comment,
pg_table_def.type,
pg_table_def.encoding,
CASE pg_table_def.distkey
WHEN true THEN 'yes\'
WHEN false THEN \'\'
END AS is_distkey,
CASE pg_table_def.sortkey
WHEN 0 THEN \'\'
ELSE 'yes\'
END AS is_sortkey,
CASE pg_table_def.sortkey
WHEN 0 THEN NULL
ELSE pg_table_def.sortkey
END AS sortkey,
CASE pg_table_def.notnull
WHEN true THEN 'NOT NULL\'
WHEN false THEN \'\'
END AS is_not_null,
def_comment.default
FROM
pg_table_def,
(SELECT
pg_stat_user_tables.schemaname AS schema,
pg_stat_user_tables.relname AS tablename,
tablecom.description as table_comment,
pg_attribute.attname AS columnname,
colcom.description AS column_comment,
pg_attribute.attnum,
pg_attrdef.adsrc as default
FROM
pg_stat_user_tables,
pg_type,
pg_attribute
LEFT JOIN pg_description tablecom ON pg_attribute.attrelid = tablecom.objoid AND tablecom.objsubid = 0
LEFT JOIN pg_description colcom ON pg_attribute.attnum = colcom.objsubid AND pg_attribute.attrelid = colcom.objoid
LEFT JOIN pg_attrdef ON pg_attribute.attrelid = pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum
WHERE
pg_attribute.attrelid = pg_stat_user_tables.relid
AND pg_attribute.atttypid = pg_type.oid
AND pg_attribute.attnum > 0
AND pg_stat_user_tables.relname IN (
SELECT
pg_class.relname
FROM
pg_namespace JOIN pg_class ON pg_namespace.oid = pg_class.relnamespace
WHERE
pg_class.relkind = 'r\'
AND pg_class.relname NOT LIKE 'pg_%\'
AND pg_class.relname NOT LIKE 'sql_%' )
ORDER BY
pg_stat_user_tables.schemaname,
pg_stat_user_tables.relname,
pg_attribute.attnum
) def_comment
WHERE
pg_table_def.schemaname = 'public' /** 必要に応じてスキーマ名は変更 */
AND pg_table_def.tablename = def_comment.tablename
AND pg_table_def.column = def_comment.columnname
AND pg_table_def.tablename = 'medals' /** 必要に応じてテーブル名を変更 */
ORDER BY
def_comment.schema,
def_comment.tablename,
def_comment.attnum;
''', {
println it
tableName = it.table_name;
columnList.add(it.column);
columnCommentList.add(it.column_comment);
typeList.add(it.type);
encodingList.add(it.encoding);
if (it.is_distkey == 'yes') {
distkey = it.column
}
if (it.is_sortkey == 'yes') {
sortKeyMap.put(it.sortkey, it.column)
}
isNullList.add(it.is_not_null);
});
println("-----------------------");
StringBuilder tableDesignSQL = new StringBuilder();
tableDesignSQL.append("CREATE TABLE " + tableName + " (\n");
for(int index = 0; index < columnList.size(); index++) {
String typeDefStr;
String type = " " + typeList.get(index);
if (type.contains("varying")) {
typeDefStr = type.replaceAll("character varying","VARCHAR");
} else if (type.contains("character(")) {
typeDefStr = type.replaceAll("character","CHAR");
} else if (type.contains("smallint")) {
typeDefStr = type.toUpperCase();
} else if (type.contains("integer")) {
typeDefStr = type.toUpperCase();
} else if (type.contains("bigint")) {
typeDefStr = type.toUpperCase();
} else if (type.contains("numeric")) {
typeDefStr = type.replaceAll("numeric","DECIMAL");
} else if (type.contains("real")) {
typeDefStr = type.toUpperCase("real","REAL");
} else if (type.contains("double")) {
typeDefStr = type.toUpperCase("double precision","DOUBLE PRECISION");
} else if (type.contains("date")) {
typeDefStr = type.toUpperCase("date","DATE");
} else if (type.contains("timestamp")) {
typeDefStr = type.replaceAll("TIMESTAMP without time zone","TIMESTAMP");
} else if (type.contains("boolean")) {
typeDefStr = type.toUpperCase("boolean","BOOLEAN");
}
String isNullStr = "";
if (isNullList.get(index).equals("NOT NULL")) {
isNullStr = " " + isNullList.get(index);
}
String encodeStr = "";
if (encodingList.get(index) != "none") {
encodeStr = " encode " + encodingList.get(index);
}
tableDesignSQL.append(" " + columnList.get(index) + typeDefStr + isNullStr + encodeStr + ",\n");
}
tableDesignSQL.append(")\n")
if (distkey != "") {
tableDesignSQL.append("distkey(" + distkey + ")\n")
}
sortKeyMap.sort();
String sortkey = "";
if (sortKeyMap.size() > 0) {
StringBuilder sortkeyStr = new StringBuilder();
sortKeyMap.keySet().each {
sortkeyStr.append(",")
sortkeyStr.append(sortKeyMap.get(it))
}
sortkey = sortkeyStr.substring(1,sortkeyStr.length())
tableDesignSQL.append("sortkey(" + sortkey + ")")
}
tableDesignSQL.append(";")
println(tableDesignSQL);
println("-----------------------");
テーブルがこのような定義となっていた場合、
schema_name | table_name | table_comment | column | column_comment | type | encoding | is_distkey | is_sortkey | sortkey | is_not_null | default -------------+------------+---------------+-----------------------+-------------------------+-----------------------------+----------+------------+------------+---------+-------------+--------- public | medals | 五輪メダル数 | id | ID | integer | delta | | | | NOT NULL | public | medals | 五輪メダル数 | athelete | 選手名 | character varying(100) | text255 | | | | NOT NULL | public | medals | 五輪メダル数 | age | 年齢 | smallint | none | | | | NOT NULL | public | medals | 五輪メダル数 | gender | 性別 | character(1) | bytedict | | | | NOT NULL | public | medals | 五輪メダル数 | olympic_year | 五輪開催年 | smallint | delta | yes | | | | public | medals | 五輪メダル数 | closing_celemony_date | 閉会日 | date | delta | | yes | 1 | | public | medals | 五輪メダル数 | sport | 種目 | character varying(100) | text255 | | | | NOT NULL | public | medals | 五輪メダル数 | gold_medals | 金メダル数 | smallint | delta | | yes | 2 | NOT NULL | 0 public | medals | 五輪メダル数 | silver_medals | 銀メダル数 | integer | delta | | yes | 3 | NOT NULL | 0 public | medals | 五輪メダル数 | bronze_medals | 銅メダル数 | smallint | delta | | yes | 4 | NOT NULL | 0 public | medals | 五輪メダル数 | total_medals | 合計メダル数 | bigint | delta | | | | NOT NULL | 0 public | medals | 五輪メダル数 | item_decimal | 検証用項目(DECIMAL型) | numeric(12,3) | none | | | | | public | medals | 五輪メダル数 | item_real | 検証用項目(REAL型) | real | none | | | | | public | medals | 五輪メダル数 | item_double | 検証用項目(DOUBLE型) | double precision | none | | | | | public | medals | 五輪メダル数 | item_timestamp | 検証用項目(TIMESTAMP型) | timestamp without time zone | none | | | | | public | medals | 五輪メダル数 | item_boolean | 検証用項目(BOOLEAN型) | boolean | none | | | | | (16 行) #
上記Groovyスクリプトを実行する事で以下のような出力が得られます。
<CREATE TABLE文> CREATE TABLE medals ( id INTEGER NOT NULL encode delta, athelete VARCHAR(100) NOT NULL encode text255, age SMALLINT NOT NULL, gender CHAR(1) NOT NULL encode bytedict, olympic_year SMALLINT encode delta, closing_celemony_date DATE encode delta, sport VARCHAR(100) NOT NULL encode text255, gold_medals SMALLINT NOT NULL encode delta, silver_medals INTEGER NOT NULL encode delta, bronze_medals SMALLINT NOT NULL encode delta, total_medals BIGINT NOT NULL encode delta, item_decimal DECIMAL(12,3), item_real REAL, item_double DOUBLE PRECISION, item_timestamp timestamp without time zone, item_boolean BOOLEAN, ) distkey(olympic_year) sortkey(closing_celemony_date,gold_medals,silver_medals,bronze_medals); ----------------------- <COMMENT文> COMMENT ON TABLE medals IS '五輪メダル数'; COMMENT ON COLUMN medals.id IS 'ID'; COMMENT ON COLUMN medals.athelete IS '選手名'; COMMENT ON COLUMN medals.age IS '年齢'; COMMENT ON COLUMN medals.gender IS '性別'; COMMENT ON COLUMN medals.olympic_year IS '五輪開催年'; COMMENT ON COLUMN medals.closing_celemony_date IS '閉会日'; COMMENT ON COLUMN medals.sport IS '種目'; COMMENT ON COLUMN medals.gold_medals IS '金メダル数'; COMMENT ON COLUMN medals.silver_medals IS '銀メダル数'; COMMENT ON COLUMN medals.bronze_medals IS '銅メダル数'; COMMENT ON COLUMN medals.total_medals IS '合計メダル数'; COMMENT ON COLUMN medals.item_decimal IS '検証用項目(DECIMAL型)'; COMMENT ON COLUMN medals.item_real IS '検証用項目(REAL型)'; COMMENT ON COLUMN medals.item_double IS '検証用項目(DOUBLE型)'; COMMENT ON COLUMN medals.item_timestamp IS '検証用項目(TIMESTAMP型)'; COMMENT ON COLUMN medals.item_boolean IS '検証用項目(BOOLEAN型)';
Publicスキーマに対して参照権限のみ持つユーザーの新規作成を一発で行う(スクリプトを作成する)
『このユーザーは、このスキーマ内要素については参照権限のみ持たせたい』そんなケース、ありますよね。でも、PostgreSQLではテーブル単位に個別に権限を付与する必要があります。テーブルの数が多いといちいちSQL文実行するのメンドイですよね。適宜ユーザーが追加されるのであれば尚更です。
つうワケでGroovyスクリプト作りました。なんて事はありません。必要なパラメータを設定してSQL文を生成しているだけです。
String userName = "user_name";
String password = "password";
String schemaName = "myschema";
tableNameArray = [
"sample_table_a",
"sample_table_b",
"sample_table_c",
];
/** ユーザー作成 */
println "/** ---------------------------------- */";
println "/** ◯ユーザー作成 */";
println "CREATE USER " + userName + " WITH PASSWORD '" + password + "';";
println "";
/** 作成ユーザーのスキーマを作成 */
println "/** ◯作成ユーザーのスキーマ作成 */";
println "CREATE SCHEMA " + schemaName + " AUTHORIZATION " + userName + ";"
println "";
/** 作成ユーザーの既存スキーマに対する権限設定(public) */
println "/** ◯作成ユーザーへのGRANT文(for public) */"
tableNameArray.each { tableName ->
println "GRANT SELECT ON TABLE public." + tableName + " TO " + userName + ";";
}
/**
後始末
REVOKE SELECT ON TABLE public.sample_table_a FROM user_name;
REVOKE SELECT ON TABLE public.sample_table_b FROM user_name;
REVOKE SELECT ON TABLE public.sample_table_c FROM user_name;
:
commit;
*/
/** 後始末用SQL */
tableNameArray.each { tableName ->
println "/** ◯作成ユーザーへのREVOKE文(for public) */"
println "REVOKE SELECT ON TABLE public." + tableName + " FROM " + userName + ";";
}
println "DROP SCHEMA " + schemaName + " CASCADE;";
println "DROP USER " + userName + ";";
