Amazon Redshift ストアドプロシージャでカラムの中のカンマ区切りデータを複数レコードに分解する

2019.05.30

はじめに

Amazon Redshiftは、LISTAGG()関数を用いてカラムの中のカンマ区切りデータを簡単に作成できます。しかし、カンマ区切りデータを元の複数レコードに分解するするにはGlueをはじめとするETLサービスやプログラムによる変換が必要でした。

今回は、先日導入されたストアドプロシージャを用いて、カラムの中のカンマ区切りデータを複数レコードに分解します。このサンプルを通じて、クエリを実行してレコードを取り出し、最終的に結果を渡す方法についても加えて解説します。

カラムの中のカンマ区切りデータ

今回は、以下のカンマ区切りデータを複数レコードに分解します。

cmdb=# SELECT * FROM public.cities ORDER BY 1;
 prefecture |                                           cities
------------+---------------------------------------------------------------------------------------------------
 三重       | 久喜,伊勢,四日市,松阪,桑名,白山,鈴鹿
 京都       | 亀岡,宇治,田辺
 佐賀       | 唐津,春日
 兵庫       | 三田,伊丹,加古川,姫路,尼崎,川西,芦屋,西宮,高砂市
 北海道     | 函館,北見,千歳,小樽,帯広,旭川,松前町,江別,苫小牧
 千葉       | 佐倉,八千代市,小見川,川口,市原,市川,成田,我孫子,木更津,松戸,流山,浦安,習志野,船橋,茂原,野田,鎌ヶ谷,館山
 和歌山     | 田辺
 埼玉       | 上尾,久喜,入間市,吉川,坂戸,富士見,岩槻,川口,川越,戸田,所沢,新座市,朝霞,東松山市,松山,越谷
 大分       | 佐伯,別府,四日市,松原
 大阪       | 八尾,堺,守口,富田林,岸和田,東大阪市,松原,枚方,池田,河内長野,泉,泉佐野市,箕面,羽曳野,茨城,門真,高槻
 奈良       | 柏原,生駒
 宮城       | 北上,名取,石巻
 宮崎       | 延岡,高岡,高鍋
 富山       | 立山,高岡
 山口       | 下関,今津,宇部,岩国,徳山
 山形       | 川西,酒田,長崎,鶴岡
 岐阜       | 可児,各務原,多治見,美濃,高山
 岡山       | 佐伯,倉敷,吉川,津山
 岩手県     | 一関,北上,宇部,戸田
 島根       | 出雲,大東,益田
 広島       | 三原,呉,尾道,東広島,福山,西条町
 徳島       | 半田市,池田
 愛媛       | 三島,今治,名取,対馬,新居浜,松山,野田
 愛知       | 刈谷,半田,安城,小牧,岡崎,春日井,東海市,津島,瀬戸,稲沢,西尾,豊橋,豊田
 新潟       | 柏崎,長岡
 東京       | 三鷹,国分寺,太田,小平,小金井,新宿,日野,武蔵野,立川
 栃木       | 今市,小山,福井,藤岡,足利,鹿沼
 沖縄       | 宜野湾,沖縄,浦添市
 滋賀       | 彦根,日野
 熊本       | 八代
 石川       | 小松,高松
 神奈川     | 伊勢原,厚木,大和南,小田原,川崎,平塚,座間,横須賀,海老名市,相模原,秦野,茅ヶ崎,藤沢,鎌倉
 福井       | 太田,福井
 福岡       | 久留米,古河,大牟田,川崎,春日市,芦屋
 福島       | 上田
 秋田       | 十和田,増田,横手
 群馬       | 三原,伊勢崎,堺,藤岡,高崎
 茨城       | つくば,取手,古河,土浦,坂井,日立
 長崎       | 佐世保,諫早,長崎
 長野       | 上田,松本,飯田
 青森       | 八戸,十和田,弘前,青森,黒石
 静岡       | 三島,富士,富士宮,掛川,沼津,浜松,焼津,磐田,藤枝,長岡
 香川       | 高松
 鳥取       | 米子,鳥取
 鹿児島     | 泉
(45 rows)

ストアドプロシジャの設計

結果を渡す方法

ストアドプロシジャの結果は、カーソルを返す方法と一時テーブルに出力する2つの方法が考えられます。今回は、結果を編集して複数レコードに分解するので、後者の一時テーブルに出力する方法を用います。一時テーブルは、接続を閉じると自動的に削除(クリーンナップ)されますが、同じ接続を使いまわした時を考慮して、一時テーブル作成前に削除する実装を追加しています。

:
  DROP TABLE IF EXISTS tmp_cities;
  CREATE TEMP TABLE tmp_cities (prefecture varchar(16), city varchar(32));
  :

対象テーブルからレコードを取り出す

対象テーブルからレコードをRECORD型の変数recに取り出し、LOOPしながら1行づつ取り出します。

:
  FOR rec IN SELECT prefecture,cities FROM cities
  LOOP
    :
    :
  END LOOP;
  :

カンマ区切りデータを分解して複数レコードに出力する

RECORD型の変数recからカラムのデータを取り出します。例えば、prefectureカラムであれば、rec.prefectureと指定します。

  • 2行目では、RAISE INFOで取り出したデータを表示します。
  • 3行目では、citiesカラムの中のカンマ区切りされた文字列の数を取得します。
  • 4〜6行目では、レコードをからカラムを取り出し、編集して、一時テーブルに出力します。
:
    RAISE INFO 'prefecture = % cities = %', rec.prefecture, rec.cities;
    count := (length(rec.cities) - length(replace(rec.cities, ',', ''))) / length(',');
    FOR i IN 1 .. count + 1 LOOP
      INSERT INTO tmp_cities(prefecture, city) VALUES(rec.prefecture, SPLIT_PART(rec.cities, ',', i));
    END LOOP;
    :

カラムの中のカンマ区切りデータを複数レコードに分解するストアドプロシージャ

上記の設計を考慮したプロシージャは以下のとおりです。

-- DROP PROCEDURE sp_cities_to_city();
CREATE OR REPLACE PROCEDURE sp_cities_to_city()
LANGUAGE plpgsql
AS $$
DECLARE
  rec RECORD;
  count INTEGER;
  i INTEGER;
BEGIN
  DROP TABLE IF EXISTS tmp_cities;
  CREATE TEMP TABLE tmp_cities (prefecture varchar(16), city varchar(32));
  FOR rec IN SELECT prefecture,cities FROM cities
  LOOP
    RAISE INFO 'prefecture = % cities = %', rec.prefecture, rec.cities;
    count := (length(rec.cities) - length(replace(rec.cities, ',', ''))) / length(',');
    FOR i IN 1 .. count + 1 LOOP
      INSERT INTO tmp_cities(prefecture, city) VALUES(rec.prefecture, SPLIT_PART(rec.cities, ',', i));
    END LOOP;
  END LOOP;
END;
$$;

上記のプロシージャを動かした結果は以下のとおりです。なお、入力データの表示をしない場合は、RAISE INFOの行をコメント化しててください。

cmdb=# CREATE OR REPLACE PROCEDURE sp_cities_to_city()
cmdb-# LANGUAGE plpgsql
cmdb-# AS $$
cmdb$# DECLARE
cmdb$#   rec RECORD;
cmdb$#   count INTEGER;
cmdb$#   i INTEGER;
cmdb$# BEGIN
cmdb$#   DROP TABLE IF EXISTS tmp_cities;
cmdb$#   CREATE TEMP TABLE tmp_cities (prefecture varchar(16), city varchar(32));
cmdb$#   FOR rec IN SELECT prefecture,cities FROM cities
cmdb$#   LOOP
cmdb$#     RAISE INFO 'prefecture = % cities = %', rec.prefecture, rec.cities;
cmdb$#     count := (length(rec.cities) - length(replace(rec.cities, ',', ''))) / length(',');
cmdb$#     FOR i IN 1 .. count + 1 LOOP
cmdb$#       INSERT INTO tmp_cities(prefecture, city) VALUES(rec.prefecture, SPLIT_PART(rec.cities, ',', i));
cmdb$#     END LOOP;
cmdb$#   END LOOP;
cmdb$# END;
cmdb$# $$;
CREATE PROCEDURE

cmdb=# CALL sp_cities_to_city();
INFO:  Table "tmp_cities" does not exist and will be skipped
INFO:  prefecture = 三重 cities = 久喜,伊勢,四日市,松阪,桑名,白山,鈴鹿
INFO:  prefecture = 京都 cities = 亀岡,宇治,田辺
INFO:  prefecture = 富山 cities = 立山,高岡
INFO:  prefecture = 岐阜 cities = 可児,各務原,多治見,美濃,高山
INFO:  prefecture = 岡山 cities = 佐伯,倉敷,吉川,津山
INFO:  prefecture = 愛知 cities = 刈谷,半田,安城,小牧,岡崎,春日井,東海市,津島,瀬戸,稲沢,西尾,豊橋,豊田
INFO:  prefecture = 新潟 cities = 柏崎,長岡
INFO:  prefecture = 静岡 cities = 三島,富士,富士宮,掛川,沼津,浜松,焼津,磐田,藤枝,長岡
INFO:  prefecture = 佐賀 cities = 唐津,春日
INFO:  prefecture = 兵庫 cities = 三田,伊丹,加古川,姫路,尼崎,川西,芦屋,西宮,高砂市
INFO:  prefecture = 埼玉 cities = 上尾,久喜,入間市,吉川,坂戸,富士見,岩槻,川口,川越,戸田,所沢,新座市,朝霞,東松山市,松山,越谷
INFO:  prefecture = 大分 cities = 佐伯,別府,四日市,松原
INFO:  prefecture = 大阪 cities = 八尾,堺,守口,富田林,岸和田,東大阪市,松原,枚方,池田,河内長野,泉,泉佐野市,箕面,羽曳野,茨城,門真,高槻
INFO:  prefecture = 宮城 cities = 北上,名取,石巻
INFO:  prefecture = 山形 cities = 川西,酒田,長崎,鶴岡
INFO:  prefecture = 島根 cities = 出雲,大東,益田
INFO:  prefecture = 広島 cities = 三原,呉,尾道,東広島,福山,西条町
INFO:  prefecture = 愛媛 cities = 三島,今治,名取,対馬,新居浜,松山,野田
INFO:  prefecture = 熊本 cities = 八代
INFO:  prefecture = 福井 cities = 太田,福井
INFO:  prefecture = 茨城 cities = つくば,取手,古河,土浦,坂井,日立
INFO:  prefecture = 青森 cities = 八戸,十和田,弘前,青森,黒石
INFO:  prefecture = 北海道 cities = 函館,北見,千歳,小樽,帯広,旭川,松前町,江別,苫小牧
INFO:  prefecture = 千葉 cities = 佐倉,八千代市,小見川,川口,市原,市川,成田,我孫子,木更津,松戸,流山,浦安,習志野,船橋,茂原,野田,鎌ヶ谷,館山
INFO:  prefecture = 和歌山 cities = 田辺
INFO:  prefecture = 奈良 cities = 柏原,生駒
INFO:  prefecture = 山口 cities = 下関,今津,宇部,岩国,徳山
INFO:  prefecture = 岩手県 cities = 一関,北上,宇部,戸田
INFO:  prefecture = 徳島 cities = 半田市,池田
INFO:  prefecture = 東京 cities = 三鷹,国分寺,太田,小平,小金井,新宿,日野,武蔵野,立川
INFO:  prefecture = 栃木 cities = 今市,小山,福井,藤岡,足利,鹿沼
INFO:  prefecture = 石川 cities = 小松,高松
INFO:  prefecture = 福島 cities = 上田
INFO:  prefecture = 宮崎 cities = 延岡,高岡,高鍋
INFO:  prefecture = 沖縄 cities = 宜野湾,沖縄,浦添市
INFO:  prefecture = 滋賀 cities = 彦根,日野
INFO:  prefecture = 神奈川 cities = 伊勢原,厚木,大和南,小田原,川崎,平塚,座間,横須賀,海老名市,相模原,秦野,茅ヶ崎,藤沢,鎌倉
INFO:  prefecture = 福岡 cities = 久留米,古河,大牟田,川崎,春日市,芦屋
INFO:  prefecture = 秋田 cities = 十和田,増田,横手
INFO:  prefecture = 群馬 cities = 三原,伊勢崎,堺,藤岡,高崎
INFO:  prefecture = 長崎 cities = 佐世保,諫早,長崎
INFO:  prefecture = 長野 cities = 上田,松本,飯田
INFO:  prefecture = 香川 cities = 高松
INFO:  prefecture = 鳥取 cities = 米子,鳥取
INFO:  prefecture = 鹿児島 cities = 泉
CALL

cmdb=# -- 先頭の10件のみ表示する
cmdb=# SELECT * FROM tmp_cities order by 1 LIMIT 10;
 prefecture |  city
------------+--------
 三重       | 白山
 三重       | 桑名
 三重       | 久喜
 三重       | 鈴鹿
 三重       | 松阪
 三重       | 四日市
 三重       | 伊勢
 京都       | 亀岡
 京都       | 田辺
 京都       | 宇治
(10 rows)

cmdb=# -- 変換後のレコード数
cmdb=# SELECT count(*) FROM tmp_cities;
 count
-------
   236
(1 row)

最後に

LISTAGG()関数で作成したカンマ区切りデータは、ストアドプロシージャを用いることで元の複数レコードに分解できることがおわかりになったと思います。現状は実務で使えるレベルのサンプルコードが殆どありませんが、他のストアドプロシージャの開発経験があれば作成できるはずです。

なお、Amazon Athenaで カラムの中のカンマ区切りデータを複数レコードに分解するには、以下のブログをご覧ください。

Amazon Athena カラムの中のカンマ区切りデータを複数レコードに分解する

合わせて読みたい

Amazon Redshift PL/pgSQLのストアドプロシージャ開発入門