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

石川覚

2019.05.30

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

はじめに

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

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

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

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

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

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

結果を渡す方法

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

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

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

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

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

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

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

２行目では、RAISE INFOで取り出したデータを表示します。
３行目では、citiesカラムの中のカンマ区切りされた文字列の数を取得します。
４〜６行目では、レコードをからカラムを取り出し、編集して、一時テーブルに出力します。

:
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)