Redshiftに空間データをCOPY文でロードしてみた

こんにちは！DA事業本部の大高です。

Amazon Redshiftの空間データサポートに関して、以下のAWSブログの記事では、COPYでのデータ取り込みについても記載があります。

Amazon Redshift で空間的なデータを活用 | Amazon Web Services ブログ

従来の COPY を使って、行で区切られたテキストファイルから地理的データを取り込むこともできます。このファイルは、地理的データの表現のために標準的に使用される形式である、16 進数の Extended Well-Known Binary (EWKB) 形式での記述を想定しています。

今回は、こちらを実際に試してみたいと思います。

前提条件

空間データのサポートはクラスタバージョンの1.0.11262以上で利用可能となっています。今回は、現時点での最新バージョンである1.0.11420で試してみます。

テストデータの投入

まずは、以下のようにテストデータとテーブルを作成してテストデータを投入します。ここでは、まだ普通のデータをCOPY文でロードしているだけです。

1,秋葉原オフィス（本社）,POINT(35.697295 139.774764)
2,岩本町オフィス 1,POINT(35.694027 139.777609)
3,岩本町オフィス 2,POINT(35.693838 139.777139)

DROP TABLE IF EXISTS ootaka_sandbox.office;
CREATE TABLE ootaka_sandbox.office(
    id INTEGER
  , name VARCHAR(128)
  , wkt VARCHAR(512)
  , PRIMARY KEY(id)
);

COPY
  ootaka_sandbox.office
FROM
  's3://foobar/ootaka_sandbox/spatial'
IAM_ROLE
  'arn:aws:iam::999999999999:role/redshift-role'
DELIMITER
  ','
;

空間データをCOPY文でロードするには、Extended Well-Known Binary (EWKB) 形式のデータが必要となるので、このサンプルデータから以下のクエリでEWKB形式のデータを取得します。

SELECT
    id
  , name
  , wkt
  , ST_AsEWKB(ST_GeomFromText(wkt))
FROM
  ootaka_sandbox.office
;

id	name	wkt	st_asewkb
1	秋葉原オフィス（本社）	POINT(35.697295 139.774764)	010100000008556AF640D94140C843DFDDCA786140
2	岩本町オフィス 1	POINT(35.694027 139.777609)	01010000003FC571E0D5D841406902452CE2786140
3	岩本町オフィス 2	POINT(35.693838 139.777139)	01010000006C5CFFAECFD8414047AE9B52DE786140

このSELECT文では、以下の2つの空間関数を利用しています。

ST_GeomFromText - Amazon Redshift ST_AsEWKB - Amazon Redshift

WKT文字列を一旦、GEOMETRY型に変換し、その後EWKBとして取り出しています。

この結果を元に、改めてGEOMETRYのCOPY文でのロードを試してみます。

GEOMETRYのCOPY文でのロード

以下のようにCOPYするデータとテーブルを作成してデータを投入します。

1,秋葉原オフィス（本社）,POINT(35.697295 139.774764),010100000008556AF640D94140C843DFDDCA786140
2,岩本町オフィス 1,POINT(35.694027 139.777609),01010000003FC571E0D5D841406902452CE2786140
3,岩本町オフィス 2,POINT(35.693838 139.777139),01010000006C5CFFAECFD8414047AE9B52DE786140

DROP TABLE IF EXISTS ootaka_sandbox.office_geo;
CREATE TABLE ootaka_sandbox.office_geo(
    id INTEGER
  , name VARCHAR(128)
  , wkt VARCHAR(512)
  , geo GEOMETRY
  , PRIMARY KEY(id)
);

COPY
  ootaka_sandbox.office_geo
FROM
  's3://foobar/ootaka_sandbox/spatial-ewkb'
IAM_ROLE
  'arn:aws:iam::999999999999:role/redshift-role'
DELIMITER
  ','
;

COPYしたら、結果を確認します。確認としては、GEOMETRY型をWKTに直して元のWKTと同じか確認します。WKTに直す際にはST_AsTextを利用しています。

ST_AsText - Amazon Redshift

SELECT
    id
  , name
  , wkt
  , ST_AsText(geo)
FROM
  ootaka_sandbox.office_geo
;

id	name	wkt	st_astext
1	秋葉原オフィス（本社）	POINT(35.697295 139.774764)	POINT(35.697295 139.774764)
2	岩本町オフィス 1	POINT(35.694027 139.777609)	POINT(35.694027 139.777609)
3	岩本町オフィス 2	POINT(35.693838 139.777139)	POINT(35.693838 139.777139)

想定どおり、COPYできていますね。

おまけ：EWKTでCOPYできないか

PostGISでは、EWKTの文字列でならコピーできるので、以下のデータでも試してみました。

1,秋葉原オフィス（本社）,POINT(35.697295 139.774764),SRID=4326;POINT(35.697295 139.774764)
2,岩本町オフィス 1,POINT(35.694027 139.777609),SRID=4326;POINT(35.694027 139.777609)
3,岩本町オフィス 2,POINT(35.693838 139.777139),SRID=4326;POINT(35.693838 139.777139)

結果、エラーが発生しstl_load_errorsテーブルでエラー内容を確認すると以下のようにHexの文字列（すなわちEWKB）じゃなきゃダメだよというエラーでした。残念…。

Compass I/O exception: Invalid hexadecimal character(s) found

まとめ

以上、「Redshiftに空間データをCOPY文でロードしてみた」でした。

実際に試してみて感じたのは、現時点の用途としては「GEOMETRYを含むテーブルデータをUNLOADしたものを、COPYで取り込む」といった流れを想定しているのかな、という感じでした。多くの場合には、COPY元のデータは緯度経度データになるかと思われるので、一旦緯度経度の座標(NUMERIC)としてデータをロードし、その後改めてGEOMETRY型としてデータの変換・登録を行い、必要に応じてUNLOADやCOPYで別テーブルや別環境にロードする流れになるのかなと思います。

どなたかのお役に立てば幸いです。それでは！