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

石川覚

2018.03.12

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

はじめに

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

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

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

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

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

執筆時点では、日本語マニュアルには記載がありませんので、英語マニュアルの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)

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

検証１

以下の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 .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 .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 .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 .impressions_11 LIMIT 1;
ip
-------------
70.95.51.46
(1 row)

Time: 8400.512 ms (00:08.401)

検証２

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

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

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

cmdb=> CREATE EXTERNAL TABLE .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 .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 .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 .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 がサポートされたらまた改めて検証したいと思います。

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

はじめに

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

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

検証１

検証２

最後に

イベント

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

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

はじめに

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

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

検証１

検証２

最後に

イベント

EVENT【5/15（水）リモート】クラスメソッドの会社説明会を開催します

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会 〜フィンテック / リテール 業界案件特集〜 を開催します

EVENT【5/28（火）】AWSを最大活用するための1dayカンファレンス

EVENT【5/17（金）】認証機能の開発工数削減をデモで体験！次世代認証基盤サービス『Auth0 by Okta』導入実践ウェビナー

EVENT【5/14（火）】アノテーションの中途向けオンライン会社説明会を開催します

EVENT【5/23（木）】ユースケースに学ぶAWS運用のノウハウ～可視化から統制まで～

EVENT【5/16（木）】Snowflakeを触ってみよう！初めての方向けハンズオンセミナー

EVENT【5/10（金） 名古屋】クラスメソッドグループの会社説明会を開催します！

EVENT【4/25（木）リモート】Google Cloudに携わるエンジニアのキャリア～クラスメソッド会社説明会～

EVENT【4/30（火）リモート】クラスメソッドの会社説明会を開催します

関連記事

クラスメソッド データアナリティクス通信(AWSデータ分析編) – 2023年12月号

EVENT【5/8リモート】クラスメソッドのフリーランスエンジニア会社説明会〜フィンテック / リテール業界案件特集〜を開催します

EVENT【5/10（金）名古屋】クラスメソッドグループの会社説明会を開催します！

クラスメソッドデータアナリティクス通信(AWSデータ分析編) – 2023年12月号