[Redshift] ASCII は廃止されたリーダーノード専用関数です

はい。残念ながら Redshift では、ASCII 文字を ASCII コードへ変換する ASCII 関数が廃止されています。

• ASCII 関数 - Amazon Redshift

とはいえ、データを DWH へロードしてからデータ変換を行う ELT 処理の場合、廃止されてしまっている関数を使用したいケースはままあるものと思います。
そんな時役に立つのがユーザ定義関数「UDF」です。

• ユーザー定義関数の作成 - Amazon Redshift

いくらかの制限はありますが、Python 言語によりある程度自由に関数を定義することができます。ASCII 関数は次のように定義したりします。

CREATE OR REPLACE FUNCTION f_ascii(c VARCHAR)
    RETURNS INTEGER
    STABLE AS $$
    if len(c) != 1: return None
    return ord(c)
    $$ LANGUAGE plpythonu;

ハイライトされている行が Python のコードになります。

これで「f_ascii('A')」などとすれば ASCII コードが取得できます。

SELECT f_ascii('A');

f_ascii
-------
     65

しかし、このように便利な UDF ですが、大量のレコードを処理する際にパフォーマンスの低下を引き起こす可能性があるとされています。
では UDF を使わずに ASCII 関数と同等の処理を行うにはどのようにすればよいか、そんな折にお客様より「ASCII テーブルを用意し結合を用いて変換してはどうか」というアイデアをいただきました。実証には至らなかったものの UDF とのパフォーマンス比較に興味があったので実際に比較してみました。

テスト概要

四つの ASCII 文字からなる文字列を、それぞれの文字について ASCII コード化し 16 進数表現として結合します。

"R26B" → 'R', '2', '6', 'B' → 82, 50, 54, 66 → 52, 32, 36, 42 → 52323642

変換対象のデータ数は 1000 万件としレコードを全件取得、変換後 result_codes テーブルへ挿入します。これを 5 回実施し平均値をとります。

準備

変換対象データの用意

こんな時に便利なのが Talend Open Studio です。

このようなジョブをさっと作成して実行すれば簡単にテストデータ入りのテーブルが作成できます。（ここでは 10 万件と 100 万件のテーブルを作成していますが、最終的に 1000 万件となりました。）
後日、このジョブについても記事化できればと思います。

ASCII テーブルの用意

INSERT INTO cm_inage_toru.ascii VALUES
(0, TO_HEX(0), CHR(0)), (1, TO_HEX(1), CHR(1)), (2, TO_HEX(2), CHR(2)), (3, TO_HEX(3), CHR(3)),
(4, TO_HEX(4), CHR(4)), (5, TO_HEX(5), CHR(5)), (6, TO_HEX(6), CHR(6)), (7, TO_HEX(7), CHR(7)),
(8, TO_HEX(8), CHR(8)), (9, TO_HEX(9), CHR(9)), (10, TO_HEX(10), CHR(10)), (11, TO_HEX(11), CHR(11)),
(12, TO_HEX(12), CHR(12)), (13, TO_HEX(13), CHR(13)), (14, TO_HEX(14), CHR(14)), (15, TO_HEX(15), CHR(15)),
(16, TO_HEX(16), CHR(16)), (17, TO_HEX(17), CHR(17)), (18, TO_HEX(18), CHR(18)), (19, TO_HEX(19), CHR(19)),
(20, TO_HEX(20), CHR(20)), (21, TO_HEX(21), CHR(21)), (22, TO_HEX(22), CHR(22)), (23, TO_HEX(23), CHR(23)),
(24, TO_HEX(24), CHR(24)), (25, TO_HEX(25), CHR(25)), (26, TO_HEX(26), CHR(26)), (27, TO_HEX(27), CHR(27)),
(28, TO_HEX(28), CHR(28)), (29, TO_HEX(29), CHR(29)), (30, TO_HEX(30), CHR(30)), (31, TO_HEX(31), CHR(31)),
(32, TO_HEX(32), CHR(32)), (33, TO_HEX(33), CHR(33)), (34, TO_HEX(34), CHR(34)), (35, TO_HEX(35), CHR(35)),
(36, TO_HEX(36), CHR(36)), (37, TO_HEX(37), CHR(37)), (38, TO_HEX(38), CHR(38)), (39, TO_HEX(39), CHR(39)),
(40, TO_HEX(40), CHR(40)), (41, TO_HEX(41), CHR(41)), (42, TO_HEX(42), CHR(42)), (43, TO_HEX(43), CHR(43)),
(44, TO_HEX(44), CHR(44)), (45, TO_HEX(45), CHR(45)), (46, TO_HEX(46), CHR(46)), (47, TO_HEX(47), CHR(47)),
(48, TO_HEX(48), CHR(48)), (49, TO_HEX(49), CHR(49)), (50, TO_HEX(50), CHR(50)), (51, TO_HEX(51), CHR(51)),
(52, TO_HEX(52), CHR(52)), (53, TO_HEX(53), CHR(53)), (54, TO_HEX(54), CHR(54)), (55, TO_HEX(55), CHR(55)),
(56, TO_HEX(56), CHR(56)), (57, TO_HEX(57), CHR(57)), (58, TO_HEX(58), CHR(58)), (59, TO_HEX(59), CHR(59)),
(60, TO_HEX(60), CHR(60)), (61, TO_HEX(61), CHR(61)), (62, TO_HEX(62), CHR(62)), (63, TO_HEX(63), CHR(63)),
(64, TO_HEX(64), CHR(64)), (65, TO_HEX(65), CHR(65)), (66, TO_HEX(66), CHR(66)), (67, TO_HEX(67), CHR(67)),
(68, TO_HEX(68), CHR(68)), (69, TO_HEX(69), CHR(69)), (70, TO_HEX(70), CHR(70)), (71, TO_HEX(71), CHR(71)),
(72, TO_HEX(72), CHR(72)), (73, TO_HEX(73), CHR(73)), (74, TO_HEX(74), CHR(74)), (75, TO_HEX(75), CHR(75)),
(76, TO_HEX(76), CHR(76)), (77, TO_HEX(77), CHR(77)), (78, TO_HEX(78), CHR(78)), (79, TO_HEX(79), CHR(79)),
(80, TO_HEX(80), CHR(80)), (81, TO_HEX(81), CHR(81)), (82, TO_HEX(82), CHR(82)), (83, TO_HEX(83), CHR(83)),
(84, TO_HEX(84), CHR(84)), (85, TO_HEX(85), CHR(85)), (86, TO_HEX(86), CHR(86)), (87, TO_HEX(87), CHR(87)),
(88, TO_HEX(88), CHR(88)), (89, TO_HEX(89), CHR(89)), (90, TO_HEX(90), CHR(90)), (91, TO_HEX(91), CHR(91)),
(92, TO_HEX(92), CHR(92)), (93, TO_HEX(93), CHR(93)), (94, TO_HEX(94), CHR(94)), (95, TO_HEX(95), CHR(95)),
(96, TO_HEX(96), CHR(96)), (97, TO_HEX(97), CHR(97)), (98, TO_HEX(98), CHR(98)), (99, TO_HEX(99), CHR(99)),
(100, TO_HEX(100), CHR(100)), (101, TO_HEX(101), CHR(101)), (102, TO_HEX(102), CHR(102)), (103, TO_HEX(103), CHR(103)),
(104, TO_HEX(104), CHR(104)), (105, TO_HEX(105), CHR(105)), (106, TO_HEX(106), CHR(106)), (107, TO_HEX(107), CHR(107)),
(108, TO_HEX(108), CHR(108)), (109, TO_HEX(109), CHR(109)), (110, TO_HEX(110), CHR(110)), (111, TO_HEX(111), CHR(111)),
(112, TO_HEX(112), CHR(112)), (113, TO_HEX(113), CHR(113)), (114, TO_HEX(114), CHR(114)), (115, TO_HEX(115), CHR(115)),
(116, TO_HEX(116), CHR(116)), (117, TO_HEX(117), CHR(117)), (118, TO_HEX(118), CHR(118)), (119, TO_HEX(119), CHR(119)),
(120, TO_HEX(120), CHR(120)), (121, TO_HEX(121), CHR(121)), (122, TO_HEX(122), CHR(122)), (123, TO_HEX(123), CHR(123)),
(124, TO_HEX(124), CHR(124)), (125, TO_HEX(125), CHR(125)), (126, TO_HEX(126), CHR(126)), (127, TO_HEX(127), CHR(127));

変換後データ挿入先テーブルの用意

CREATE TABLE cm_inage_toru.result_codes
(
    result_code CHAR(8)
);

比較テスト

下記の SQL を SQL Workbench 上で評価し、かかった時間を計測しました。

INSERT INTO cm_inage_toru.result_codes
SELECT TO_HEX(f_ascii (SUBSTRING(SAMPLE_CODE,1,1))) || TO_HEX(f_ascii (SUBSTRING(SAMPLE_CODE,2,1))) || TO_HEX(f_ascii (SUBSTRING(SAMPLE_CODE,3,1))) || TO_HEX(f_ascii (SUBSTRING(SAMPLE_CODE,4,1))) RESULT_CODE
FROM cm_inage_toru.sample_codes_10000000;
ORDER BY result_code;

INSERT INTO cm_inage_toru.result_codes
SELECT d1.hex || d2.hex || d3.hex || d4.hex RESULT_CODE
FROM cm_inage_toru.sample_codes_10000000 AS t
  INNER JOIN cm_inage_toru.ascii AS d1 ON (d1.char = SUBSTRING (t.SAMPLE_CODE,1,1))
  INNER JOIN cm_inage_toru.ascii AS d2 ON (d2.char = SUBSTRING (t.SAMPLE_CODE,2,1))
  INNER JOIN cm_inage_toru.ascii AS d3 ON (d3.char = SUBSTRING (t.SAMPLE_CODE,3,1))
  INNER JOIN cm_inage_toru.ascii AS d4 ON (d4.char = SUBSTRING (t.SAMPLE_CODE,4,1))
ORDER BY result_code;

	1	2	3	4	5	平均（秒）
UDF 版	15.38	15.3	15.39	15.4	15.42	15.378
ASCII テーブル結合版	3.62	3.61	3.75	3.68	3.75	3.682

随分と差が出ました。UDF では 4 倍ほど時間がかかっていますね。

まとめ

ASCII 関数を UDF で実現している SQL で、なんとかしてでもパフォーマンスを向上させたいという場合には、この「ASCII テーブルを結合する」方法は一考の余地ありと思います。