Amazon Redshiftで良く使いそうなSQL文をスクリプトで作成してみた

2014.04.30

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

下記エントリではRedshiftで使うSQL文を自分なりにまとめてみましたが、当エントリではその姉妹編という形で、『良く使いそうなSQL文をスクリプトで作成してみた』ものをまとめたものになります。SQL編同様、『コレは!』と思ったものについては適宜追加・更新を行っていきたいと思います。ではではスタート。

目次

項目の最大バイト数を求める(代替策)

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 + ";";