Amazon Redshift Spectrum がスカラーJSONおよびIonデータ型をサポートしたので試してみました

はじめに

Amazon Redshift Spectrumによって、Amazon S3の外部テーブルに格納されているスカラーJSONおよびIONデータ型を直接読み込むことができるようになりましたので、早速試してみました。クラスタバージョンは、1.0.1865 です。

スカラーJSONおよびIONデータ型

JSONは、非同期のブラウザとサーバー間の通信に一般的に使用されるオープンスタンダードのファイル形式です。Ionは、データを長期間保存するための明確なセマンティクスを提供するために、ほぼ10年前にAmazonによって開発されたデータ直列化形式です。Ionテキスト形式はJSONのスーパーセットです。したがって、有効なJSONドキュメントは有効なIonドキュメントでもあります。

JSONのシリアライズ-デシリアライズには、OpenX Technologies, IncJsonSerdeを用いています。

英語マニュアル通りを試してみるが...

執筆時点では、日本語マニュアルには記載がありませんので、英語マニュアルのCREATE EXTERNAL TABLEを参考にします。

example には、以下のcloudtrailのテーブル定義が記載されています。

create external table spectrum.cloudtrail_json (
event_version int
event_id bigint,
event_time timestamp,
event_type varchar(10),
awsregion varchar(20),
event_name varchar(max),
event_source varchar(max),
requesttime timestamp,
useragent varchar(max),
recipientaccountid bigint) 
row format serde 'org.openx.data.jsonserde.JsonSerDe'
with serdeproperties (
'dots.in.keys' = 'true',
'mapping.requesttime' = 'requesttimestamp'
) location 's3://mybucket/json/cloudtrail';

実際にcloudtrailのログファイルを指定すると、

cmdb=> create external table spectrum.cloudtrail_json (
cmdb(> event_version int,
cmdb(> event_id bigint,
cmdb(> event_time timestamp,
cmdb(> event_type varchar(10),
cmdb(> awsregion varchar(20),
cmdb(> event_name varchar(max),
cmdb(> event_source varchar(max),
cmdb(> requesttime timestamp,
cmdb(> useragent varchar(max),
cmdb(> recipientaccountid bigint)
cmdb-> row format serde 'org.openx.data.jsonserde.JsonSerDe'
cmdb-> with serdeproperties (
cmdb(> 'dots.in.keys' = 'true',
cmdb(> 'mapping.requesttime' = 'requesttimestamp'
cmdb(> ) location 's3://mybucket/cloudtrail-logs';
CREATE EXTERNAL TABLE
Time: 707.142 ms
cmdb=> select * from spectrum.cloudtrail_json limit 1;
ERROR:  S3 Query Exception (Fetch)
DETAIL:
  -----------------------------------------------
  error:  S3 Query Exception (Fetch)
  code:      15001
  context:   Task failed due to an internal error. Error occured during Ion/JSON extractor match: IERR_UNEXPECTED_EOF

  query:     82836
  location:  dory_util.cpp:682
  process:   query0_112 [pid=17599]
  -----------------------------------------------

Time: 1783.787 ms (00:01.784)

マニュアル通りではうまくいかないようなので、以降、試行錯誤してみました。

検証1

以下のAWSが公開しているサンプルデータ試してみました。timersがネストしたJSONファイルになっています。

$ aws s3 cp s3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions/dt=2009-04-12-13-00/ec2-0-51-75-39.amazon.com-2009-04-12-13-00.log - | head -n 1 | jq .
{
  "number": "92583",
  "referrer": "paipai.com",
  "processId": "1655",
  "adId": "FwTr8BJ6949s6V9a4Eb4bNhAkKrSCe",
  "browserCookie": "skxmlacilk",
  "userCookie": "EskxUTIcilSmJ3cg2ncsrRHSijGxCl",
  "requestEndTime": "1239541873000",
  "impressionId": "RsR89fR0BJNbuEFBiw3dq4EJBu8B0I",
  "userAgent": "Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.1) Gecko/20090715 Firefox/3.5.1",
  "timers": {
    "modelLookup": "0.3498",
    "requestTime": "0.666"
  },
  "threadId": "14",
  "ip": "23.231.246.62",
  "modelId": "bxxiuxduab",
  "hostname": "ec2-0-51-75-39.amazon.com",
  "sessionId": "2wnKWfrVuDgF7BqWlVgNpD4rsjDv3f",
  "requestBeginTime": "1239541873000"
}

Amazon Athenaのテーブル定義を参考に、テーブル定義してみるとstruct<でエラーが発生します。CREATE EXTERNAL TABLEのシンタックスにSTRUCT、MAP、ARRAY、UNIONなどはありませんので、ネストされたJSONファイル形式はサポートされていないことが確認できます。

cmdb=> CREATE EXTERNAL TABLE <external_schemaname>.impressions_10(
cmdb(>   requestbegintime varchar(max),
cmdb(>   adid varchar(max),
cmdb(>   impressionid varchar(max),
cmdb(>   referrer varchar(max),
cmdb(>   useragent varchar(max),
cmdb(>   usercookie varchar(max),
cmdb(>   ip varchar(max),
cmdb(>   number varchar(max),
cmdb(>   processid varchar(max),
cmdb(>   browsercookie varchar(max),
cmdb(>   requestendtime varchar(max),
cmdb(>   timers struct<modellookup:varchar(max),requesttime:varchar(max)>,
cmdb(>   threadid varchar(max),
cmdb(>   hostname varchar(max),
cmdb(>   sessionid varchar(max))
cmdb-> PARTITIONED BY (
cmdb(>   dt varchar(max))
cmdb-> ROW FORMAT SERDE
cmdb->   'org.openx.data.jsonserde.JsonSerDe'
cmdb-> WITH SERDEPROPERTIES (
cmdb(>   'paths'='requestBeginTime, adId, impressionId, referrer, userAgent, userCookie, ip')
cmdb-> STORED AS INPUTFORMAT
cmdb->   'org.apache.hadoop.mapred.TextInputFormat'
cmdb-> OUTPUTFORMAT
cmdb->   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
cmdb-> LOCATION
cmdb->   's3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions'
cmdb-> ;
ERROR:  syntax error at or near "<"
LINE 13:   timers struct<modellookup:varchar(max),requesttime:varchar...
                        ^
Time: 303.305 ms

更にtimersを文字列としてテーブル定義してみると、テーブル定義は成功しました。

cmdb=> CREATE EXTERNAL TABLE <external_schemaname>.impressions_11(
cmdb(>   requestbegintime varchar(max),
cmdb(>   adid varchar(max),
cmdb(>   impressionid varchar(max),
cmdb(>   referrer varchar(max),
cmdb(>   useragent varchar(max),
cmdb(>   usercookie varchar(max),
cmdb(>   ip varchar(max),
cmdb(>   number varchar(max),
cmdb(>   processid varchar(max),
cmdb(>   browsercookie varchar(max),
cmdb(>   requestendtime varchar(max),
cmdb(>   timers varchar(max),
cmdb(>   threadid varchar(max),
cmdb(>   hostname varchar(max),
cmdb(>   sessionid varchar(max))
cmdb-> PARTITIONED BY (
cmdb(>   dt varchar(max))
cmdb-> ROW FORMAT SERDE
cmdb->   'org.openx.data.jsonserde.JsonSerDe'
cmdb-> WITH SERDEPROPERTIES (
cmdb(>   'paths'='requestBeginTime, adId, impressionId, referrer, userAgent, userCookie, ip')
cmdb-> STORED AS INPUTFORMAT
cmdb->   'org.apache.hadoop.mapred.TextInputFormat'
cmdb-> OUTPUTFORMAT
cmdb->   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
cmdb-> LOCATION
cmdb->   's3://us-east-1.elasticmapreduce/samples/hive-ads/tables/impressions'
cmdb-> ;
CREATE EXTERNAL TABLE
Time: 463.905 ms

データファイルはカラム名ありパーティション形式なので、Amazon AthenaからMSCK REPAIR TABLEを実行して、パーティションを自動認識させます。ここまではうまくいきました。

データを参照してみましたが、Nested datatype is not supported for this file format.とある通り、ネストされたデータ型はサポートできないことが確認できました。

cmdb=> SELECT * FROM <external_schemaname>.impressions_11 LIMIT 1;
ERROR:  S3 Query Exception (Fetch)
DETAIL:
  -----------------------------------------------
  error:  S3 Query Exception (Fetch)
  code:      15001
  context:   Task failed due to an internal error. Nested datatype is not supported for this file format.

  query:     83016
  location:  dory_util.cpp:682
  process:   query1_119 [pid=17131]
  -----------------------------------------------

Time: 3120.225 ms (00:03.120)

一方、ネストされていないカラムは問題なく表示できました。

cmdb=> SELECT ip FROM <external_schemaname>.impressions_11 LIMIT 1;
     ip
-------------
 70.95.51.46
(1 row)

Time: 8400.512 ms (00:08.401)

検証2

ネストされたJSONファイルは、サポートしていないようなので以下のフラットなJSONファイルを作成しました。

{"id":"1","name":"foo","age":"10"}
{"id":"2","name":"bar","age":"20"}
{"id":"3","name":"baz","age":"30"}

全て文字列型のカラムとしてテーブル定義します。

cmdb=> CREATE EXTERNAL TABLE <external_schemaname>.users_flatjson(
cmdb(>   id varchar(max),
cmdb(>   name varchar(max),
cmdb(>   age varchar(max)
cmdb(> )
cmdb-> ROW FORMAT SERDE
cmdb->   'org.openx.data.jsonserde.JsonSerDe'
cmdb-> -- WITH SERDEPROPERTIES (
cmdb-> --   'paths'='id, name, age')
cmdb-> STORED AS INPUTFORMAT
cmdb->   'org.apache.hadoop.mapred.TextInputFormat'
cmdb-> OUTPUTFORMAT
cmdb->   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
cmdb-> LOCATION
cmdb->   's3://mybucket/users_flatjson/'
cmdb-> ;
CREATE EXTERNAL TABLE
Time: 486.978 ms

やっと、何のエラーもなくクエリが成功しました。

cmdb=> SELECT * FROM <external_schemaname>.users_flatjson;
 id | name | age
----+------+-----
 1  | foo  | 10
 2  | bar  | 20
 3  | baz  | 30
(3 rows)

Time: 3148.414 ms (00:03.148)

なお、idカラムを INT型で定義すると、以下のようなエラーが表示されました。恐らく文字列型にのみ定義できるようでした。

cmdb=> CREATE EXTERNAL TABLE <external_schemaname>.users_flatjson_2(
cmdb(>   id int,
cmdb(>   name varchar(max),
cmdb(>   age int
cmdb(> )
cmdb-> ROW FORMAT SERDE
cmdb->   'org.openx.data.jsonserde.JsonSerDe'
cmdb-> STORED AS INPUTFORMAT
cmdb->   'org.apache.hadoop.mapred.TextInputFormat'
cmdb-> OUTPUTFORMAT
cmdb->   'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
cmdb-> LOCATION
cmdb->   's3://mybucket/users_flatjson/'
cmdb-> ;
CREATE EXTERNAL TABLE
Time: 604.839 ms
cmdb=> SELECT * FROM <external_schemaname>.users_flatjson_2;
ERROR:  S3 Query Exception (Fetch)
DETAIL:
  -----------------------------------------------
  error:  S3 Query Exception (Fetch)
  code:      15001
  context:   Task failed due to an internal error. In file https://s3.amazonaws.com/mybucket/users_flatjson/users_flatjson.json declared column type INT for column STRING is incompatible

  query:     83410
  location:  dory_util.cpp:682
  process:   query0_69 [pid=17373]
  -----------------------------------------------

Time: 3918.127 ms (00:03.918)

最後に

スカラーJSONおよびIonデータ型をサポートは、ネストしていないフラットなJSONのみのサポートであることがわかりました。re:Invent2017で発表のあった Nested Data Supportかと期待しましたが、今回のアップデートはこれとは違うようです。Nested Data Support がサポートされたらまた改めて検証したいと思います。