❄️

Snowflake 不正な UTF-8 シーケンス完全攻略ガイド

2022/12/20に公開

SQL

この記事は Snowflake Advent Calendar 2022 の 20 日目です。

はじめに

Snowflake 上でサポートされている唯一の文字符号化方式は UTF-8 です。

すなわち、Snowflake 上のすべての文字列 (VARCHAR 型値) は UTF-8 として正しいバイトシーケンスである必要があります。

しかし、例えばインポートしようとしているログを書き出すときにプロセスが異常終了してしまって文字の途中で切れてしまったなど、UTF-8 として完全でないバイトシーケンスが混入することがあります。

今回は Snowflake 上でそんな不正な UTF-8 シーケンスが発生したときに、どう対処するべきかという話をします。

サンプル

今回はサンプルとして、以下のようなファイルを使います。

$ echo -en こんにちは|cut -b1-13 > invalid.csv
$ cat invalid.csv|xxd
00000000: e381 93e3 8293 e381 abe3 81a1 e30a       ..............

15 bytes ある こんにちは を cut -b で 13 bytes で切ることで、末尾に は の 1-byte 目 (e3) のみが書き込まれた状態にしています。

不正な UTF-8 シーケンスの発生ケース

COPY

COPY コマンドは、デフォルトで不正な UTF-8 シーケンスを検知すると Invalid UTF8 detected エラーで失敗します。

create or replace table t_invalid_utf8 (c1 varchar);

copy into t_invalid_utf8 from @ext/invalid.csv
file_format = (type = csv)
;
-- Invalid UTF8 detected in string 'こんにち0xE3' File 'invalid.csv', line 1, character 1 Row 1, column "T_INVALID_UTF8"["C1":1] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

このケースでの他の選択肢は、ファイルフォーマットで REPLACE_INVALID_CHARACTERS オプションを true に設定して、不正な UTF-8 シーケンスを REPLACEMENT CHARACTER (U+FFFE / UTF-8 では efbfbd) で置換する以外にありません。

REPLACE_INVALID_CHARACTERS = TRUE | FALSE
無効な UTF -8文字をUnicode置換文字（�）で置き換えるかどうかを指定するブール値。コピーオプションは、1対1の文字置換を実行します。
TRUE に設定すると、Snowflakeは無効な UTF-8文字をUnicode置換文字に置き換えます。
FALSE に設定すると、無効な UTF-8文字エンコードが検出されたときにロード操作でエラーが生成されます。
デフォルト: FALSE

つまり、COPY コマンドを使用したデータ投入では、不正な UTF-8 シーケンスを混入させることはできません。

DML

次に INSERT や UPDATE のような DML ですが、試しに下記のように　\xe3 を INSERT で挿入してみます。

create or replace table t_invalid_utf8 (c1 varchar);

insert into t_invalid_utf8 values ('こんにち\xe3');

すると、挿入された文字列は こんにちã となり、ぱっと見た感じ混入に成功したように見えます。

select c1 from t_invalid_utf8;
-- こんにちã

…が、実はこの ã は \xe3 ではなく、なんらかのエンコーディングにより解釈された上で UTF-8 に変換され、c3a3 つまり LATIN SMALL LETTER A WITH TILDE (U+00E3)になっています。

select c1 from t_invalid_utf8;
/*
C1	HEX_ENCODE(C1)
こんにちã	E38193E38293E381ABE381A1C3A3
*/

\xe3 が ã ということは Latin-1 かなと思い、Latin-1 の未使用領域である \x80 を挿入してみると…

create or replace table t_invalid_utf8 (c1 varchar);

insert into t_invalid_utf8 values ('こんにち\x80');

select c1 from t_invalid_utf8;
/*
C1	HEX_ENCODE(C1)
こんにち	E38193E38293E381ABE381A1C280
*/

環境によっては € に見えないためスクリーンショット

となり \x80 が c280 に変換されていて、かつ € として表示されています。

c280 は Unicode における U+0080、すなわち Latin-1 Supplements の範囲になるので、いわゆる IANA 名における ISO-8859-1 として認識した上で UTF-8 に変換している、という動作のように見えます。

その上で、Latin-1 Supplements では　<control> で、いわゆる C1 control code の範囲で printable な文字ではないはずですが、実際は € として表示されています。

また、スクリーンショットをよく見てもらうと、こんにち までと € が違うフォントであることがわかるかと思います。これは各ブラウザが、デフォルトのフォントで c280 を描画できないと判断し、フォントをフォールバックして描画しようとしたところ、フォールバック先のフォントが(おそらく Windows-1252 を意識して) c280 に € をマッピングしていたために、本来 € でないコードポイントが € として描画されてしまっている状況にあると考えられます。

試しに当該クエリを Google Chrome 上で実行して Developer Tools から Rendered Font を確認すると…

Rendered Font

こんにち までの 4 グリフは、ブラウザのデフォルトフォントである Noto Sans JP で描画されており、最後の 1 グリフだけが Charter で描画されていることがわかります。

そのため、おそらく Charter では c280 が € として描画されるようになっていると考えられます。

というわけで、重要な観測事実として、

1 byte の UTF-8 として解釈できないバイトを \x エスケープシーケンスで文字列に含めると Latin-1 / Latin-1 Supplements として判断され、自動的に UTF-8 に変換される
その上で Latin-1 Supplements の C1 control code の範囲の文字は、多くのブラウザ^[1]はフォントをフォールバックして描画しようとし、フォントによっては別の文字として描画される

の 2 点がわかりました。

External Table

最後に External Table ですが、こいつは普通に素通りします。

create or replace external table et_invalid_utf8 (
  c1 varchar as (cast(get(value, 'c1') as varchar))
)
location = @ext
pattern = 'invalid.csv'
file_format = (type = csv)
;

select c1, hex_encode(c1)
from et_invalid_utf8;
/*
C1	HEX_ENCODE(C1)
こんにちã	E38193E38293E381ABE381A1E3
*/

スクリーンショット

Rendered Font

HEX_ENCODE の結果でも、末尾が E3 (0xe3) になっているので、c3a3 に変換されているわけではなく、ブラウザがフォントを Inter にフォールバックして ã を表示しているだけなことがわかります。

ちなみにこの　External Table から CTAS しても MV 作っても 0xe3 が挿入されたままになります。

create or replace table t_invalid_utf8 (c1 varchar) as
select c1 from et_invalid_utf8;

select c1, hex_encode(c1)
from t_invalid_utf8;
/*
C1	HEX_ENCODE(C1)
こんにちã	E38193E38293E381ABE381A1E3
*/

create or replace materialized view mv_invalid_utf8 as
select c1 from et_invalid_utf8;

select c1, hex_encode(c1)
from mv_invalid_utf8;
/*
C1	HEX_ENCODE(C1)
こんにちã	E38193E38293E381ABE381A1E3
*/

ということで、不正な UTF-8 シーケンスの混入が起きそうなメジャーな経路は External Table になりそうです。

不正な UTF-8 シーケンスの検知

さて、混入してしまった場合の対応策を考えます。

まず、対応するためには、不正な UTF-8　シーケンスを検知する必要がありますが、これは HEX_ENCODE と HEX_DECODE_STRING 関数を使用することで簡単に実現できます。

まず最初のステップとして、対象となるカラムに対して HEX_DECODE_STRING(HEX_ENCODE(<column-name>)) を実行します。

すると、不正な UTF-8 シーケンスが混入していた場合、下記のように Invalid UTF8 detected while decoding エラーが発生します。

select hex_decode_string(hex_encode(c1))
from t_invalid_utf8;
/*
Invalid UTF8 detected while decoding 'E38193E38293E381ABE381A1E3'
*/

これは HEX_ENCODE 関数は不正な UTF-8 シーケンスがあっても問題なく 16 進数文字列へのエンコードができるのに対して、逆関数である HEX_DECODE_STRING 関数は不正な UTF-8 シーケンスを検知すると上記の Invalid UTF8 detected while decoding エラーを発生させて失敗するため、これを重ねて実行することによって、不正な UTF-8 シーケンスを検知することができます。

検知する方法がわかったので、次のステップは「どの行が影響を受けているか」を抽出する部分になりますが、例えばエラーメッセージから素直に

select *
from t_invalid_utf8
where hex_encode(c1) = 'E38193E38293E381ABE381A1E3'
;

…のような形で抽出してもいいのですが、不正な UTF-8 シーケンスが複数行に含まれている場合、これを 1 行 1 行繰り返さなければならないため、非常にめんどくさいです。

そこで、不正な UTF-8 シーケンスに遭遇したときに、エラーではなく NULL を返す TRY_HEX_DECODE_STRING 関数を使用することで、一発ですべての対象行を抽出することができます。

例えば、下記のような 100 万行の中にランダムに 5 行だけ不正な UTF-8 シーケンスを含む行があるとします。

$ yes こんにちは|head -n999995 > invalid-1m.csv
$ echo -e 'こんにち\xe3' >> invalid-1m.csv
$ echo -e 'こんにち\xe3' >> invalid-1m.csv
$ echo -e 'こんにち\xe3' >> invalid-1m.csv
$ echo -e 'こんにち\xe3' >> invalid-1m.csv
$ echo -e 'こんにち\xe3' >> invalid-1m.csv

create or replace external table et_invalid_utf8_1m (
  c1 varchar as (cast(get(value, 'c1') as varchar))
)
location = @ext
pattern = 'invalid-1m.csv'
file_format = (type = csv)
;

create or replace table t_invalid_utf8_1m (id int, msg varchar) as
select row_number() over (order by random()), c1
from et_invalid_utf8_1m
;

これについて TRY_HEX_DECODE_STRING 関数で対象行を抽出してみると、

select id, msg
from t_invalid_utf8_1m
where msg is not null and try_hex_decode_string(hex_encode(msg)) is null
order by id
;
/*
ID	MSG
514542	こんにちã
663963	こんにちã
717599	こんにちã
780188	こんにちã
833734	こんにちã
*/

という形で全行一気に抽出することができます。

これは TRY_HEX_DECODE_STRING 関数が NULL を返すパターンは

引数が NULL
引数が UTF-8 のバイトシーケンスとしてデコードできない

のいずれかになるため msg is not null で 1 つ目のパターンを潰してあげることで、不正な UTF-8 シーケンスを含む行のみを抽出することができる、というものになります。

不正な UTF-8 シーケンスの修正

最後に修正ですが、これには少し落とし穴があります。

今回、不正な UTF-8 シーケンスを含む文字列は、結果内で こんにちã と表示されていますが、ここで ã を素直に置換しようとしてもうまくいきません。

select id, msg, replace(msg, 'ã', 'は')
from t_invalid_utf8_1m
where msg is not null and try_hex_decode_string(hex_encode(msg)) is null
order by id
;
/*
ID	MSG	REPLACE(MSG, 'Ã', 'は')
514542	こんにちã	こんにちã
663963	こんにちã	こんにちã
717599	こんにちã	こんにちã
780188	こんにちã	こんにちã
833734	こんにちã	こんにちã
*/

これはなぜかというと、前セクションの観測結果から

ã として表示されている文字は内部的には e3 である
ã という文字は UTF-8 では e3 ではなく c3a3 である
ã として表示されているのは、ブラウザが Latin-1 / Latin-1 Supplements の範囲の文字を Windows-1252 に沿ってレンダリングしているからである

という状態になっており、REPLACE 関数の引数で指定されている文字 ã は c3a3 なので、MSG カラム文字列中の ã = e3 にはマッチせず、置換も発生しないからとなります。

同様に、\x エスケープシーケンスを使用しても、文字列として処理される段階で c3a3 に変換されてしまうため、置換することができません。

select id, msg, replace(msg, '\xe3', 'は')
from t_invalid_utf8_1m
where msg is not null and try_hex_decode_string(hex_encode(msg)) is null
order by id
;
/*
ID	MSG	REPLACE(MSG, '\XE3', 'は')
514542	こんにちã	こんにちã
663963	こんにちã	こんにちã
717599	こんにちã	こんにちã
780188	こんにちã	こんにちã
833734	こんにちã	こんにちã
*/

ではどうするかというと、16 進数文字列の状態で置換してあげることで、うまく実現することができます。

select id, msg, hex_decode_string(regexp_replace(hex_encode(msg), 'E3$', hex_encode('は')))
from t_invalid_utf8_1m
where msg is not null and try_hex_decode_string(hex_encode(msg)) is null
order by id
;
/*
ID	MSG	HEX_DECODE_STRING(REGEXP_REPLACE(HEX_ENCODE(MSG), 'E3$', HEX_ENCODE('は')))
514542	こんにちã	こんにちは
663963	こんにちã	こんにちは
717599	こんにちã	こんにちは
780188	こんにちã	こんにちは
833734	こんにちã	こんにちは
*/

上記のクエリでは、

MSG カラムの文字列を 16 進数文字列 E38193E38293E381ABE381A1E3 に変換する (HEX_ENCODE)
末尾の E3 を は の 16 進数文字列表現 E381AF に置換する (REGEXP_REPLACE + HEX_ENCODE)
置換後の 16 進数文字列を UTF-8 バイトシーケンスとしてデコードする (HEX_DECODE_STRING)

という手順で、正しい文字列に修復しています。

同様に、実際のテーブルデータを修正する場合も

update t_invalid_utf8_1m
set msg = hex_decode_string(regexp_replace(hex_encode(msg), 'E3$', hex_encode('は')))
where msg is not null and try_hex_decode_string(hex_encode(msg)) is null
;
/*
number of rows updated	number of multi-joined rows updated
5	0
*/

select id, msg
from t_invalid_utf8_1m
where msg is not null and try_hex_decode_string(hex_encode(msg)) is null
order by id
;
-- 0 row

select distinct msg
from t_invalid_utf8_1m
;
-- こんにちは

といった感じで、同じ式・同じ条件を UPDATE クエリに入れてあげることで、不正な UTF-8 シーケンスを含む行を正しいデータに更新することができます。

まとめ

文字コード楽しい。

脚注

Google Chrome でも Firefox でもそうでした ↩︎