Amazon Redshift Spectrum 新たにサポートされたDATE型について試してみました

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

はじめに

Amazon Redshift Spectrumを利用して、Amazon S3上ののテキストファイルやカラムナフォーマットファイル(Parquet)に保存されているDATE型のデータをクエリできるようになりました。AWSではリージョン毎にGlueデータカタログでテーブル定義を共有していますが、クエリエンジンやファイルフォーマット組み合わせによって、サポートしてしているデータ型が異なります。登録されたテーブル定義を共有するAmazon Athenaの関係についても解説します。検証したRedshiftのBuildは1/26に更新された1.0.1657です。(東京リージョンではまだデプロイされていないかもしれません。)

サポートしているDATE型・TIMESTAMP型の形式

まず、どんな形式の文字列をDATE型・TIMESTAMP型のデータとして読み取れるかについて解説します。Amazon Redshiftのデータのロード(COPYコマンド)では、一般的な形式は自動認識してロードできますが、Amazon Redshift Spectrum や Amazon Athenaはスキーマオンリードなので、形式が限定されるのはやむを得ません。もし認識できない形式の文字列の場合は値がNULLになります。

DATE型

DATE型がサポートしている形式は、ISO 8601の拡張形式(YYYY-MM-DD)です。ISO 8601の基本形式(YYYYMMDD)やExcelでよく見る形式(YYYY/MM/DD)はDATE型のデータとして読み込まれません。

TIMESTAMP型

TIMESTAMP型は、YYYY-MM-DD HH:MI:SS形式です。例えば2018-01-23 03:13:42は、ミリ秒が補完されて2018-01-23 03:13:42.000となります。2018-01-23 03:13:42.123456は、ミリ秒未満は切り捨てられて2018-01-23 03:13:42.123となります。

以降では、DATE型は2017-07-07、日時型(TIMESTAMP型)は2018-08-08 08:08:08を用いて検証します。

テキストファイル(タブ区切り)の検証

検証用データ

データファイルは以下のタブ区切り文字列(.tsv)です。

1   22.22   33.33   44.44   gogo0123456789  rokuroku    2017-07-07  2018-08-08 08:08:08

テーブル定義

このデータに対して、Amazon Athenaから以下のDDLでテーブル定義します。カラムに対してプリミティブなさまざまなデータ型設定しています。パーティションキーにもDATE型を設定しています。

CREATE EXTERNAL TABLE `test_datatypes`(
  `id` int, 
  `price` decimal(10,2), 
  `profit` double, 
  `discount` float, 
  `name_full` varchar(100), 
  `name_short` char(10), 
  `order_date` date, 
  `order_timestamp` timestamp)
PARTITIONED BY ( 
  `p_date` date)
ROW FORMAT DELIMITED 
  FIELDS TERMINATED BY '\t' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://my_bucket/test_datatypes'
TBLPROPERTIES (
  'classification'='csv', 
  'delimiter'='\t', 
  'transient_lastDdlTime'='1516946597', 
  'typeOfData'='file')

Amazon Redshift Spectrum からクエリを実行

従来のBuild1.0.1582では、「パーディションカラム以外DATE型はサポートしていないので、代わりにtimestamp型かvarchar型に定義してください」といったエラーが表示されます。

cmdb=> select version();
                                                         version
--------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1582
(1 row)

cmawsteamdb=> select * from my_schema_db.test_datatypes;
ERROR:  Invalid DataCatalog response for external table "my_schema_db"."test_datatypes": Redshift does not support data type "date" for non-partitioning column "order_date". Try defining the column as "timestamp" or "varchar" instead.

執筆時点の最新版、Build1.0.1657では、DATE型のカラム(order_date)が表示されています。パーティションカラム(p_date)もDATE型として参照できました。

cmawsteamdb=# select version();
                                                         version
--------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1657
(1 row)

cmawsteamdb=# select * from my_schema_db.test_datatypes limit 1;
 id | price | profit | discount |   name_full    | name_short | order_date |   order_timestamp   |   p_date
----+-------+--------+----------+----------------+------------+------------+---------------------+------------
  1 | 22.22 |  33.33 |    44.44 | gogo0123456789 | rokuroku   | 2018-01-23 | 2018-01-23 03:13:42 | 2018-09-09
(1 row)

Amazon Athena からクエリを実行

もちろん、全く問題なくクエリが実行できました。

カラムナフォーマットファイル(Parquet)の検証

データソース test_datatypesテーブルからParquetのファイルの変換はAWS GlueをJobで生成しました。生成したファイルを同じくGlueのクローラでtest_datatypes_parquetテーブルを定義しています。

テーブル定義

Glueのクローラが生成したDDL以下のとおりです。

-- null: decimal(10,2), order_date, order_timestamp
CREATE EXTERNAL TABLE `test_datatypes_parquet`(
  `id` int, 
  `price` decimal(10,2), 
  `profit` double, 
  `discount` float, 
  `name_full` string, 
  `name_short` string, 
  `order_date` date, 
  `order_timestamp` timestamp)
PARTITIONED BY ( 
  `p_date` string)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://my_bucket/test_datatypes_parquet/'
TBLPROPERTIES (
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'UPDATED_BY_CRAWLER'='test_datatypes_parquet', 
  'averageRecordSize'='358', 
  'classification'='parquet', 
  'compressionType'='none', 
  'objectCount'='1', 
  'recordCount'='2', 
  'sizeKey'='4296', 
  'typeOfData'='file')

Amazon Redshift Spectrum からクエリを実行

従来のBuild1.0.1582では、「パーディションカラム以外DATE型はサポートしていないので、代わりにtimestamp型かvarchar型に定義してください」といったエラーが表示されます。

cmawsteamdb=> select version();
                                                         version
--------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.1582
(1 row)

cmawsteamdb=> select * from my_schema_db.test_datatypes_parquet limit 1;
ERROR:  Invalid DataCatalog response for external table "my_schema_db"."test_datatypes_parquet": Redshift does not support data type "date" for non-partitioning column "order_date". Try defining the column as "timestamp" or "varchar" instead.

執筆時点の最新版、Build1.0.1657では、エラーメッセージに変化がありましたが、Parquetではエラーが発生しています。アナウンスがあったビルドはBuild 1.0.1636なので、その後の更新で変化があったのかもしれません。

cmawsteamdb=# select * from my_schema_db.test_datatypes_parquet limit 1;
ERROR:  S3 Query Exception (Fetch)
DETAIL:
  -----------------------------------------------
  error:  S3 Query Exception (Fetch)
  code:      15001
  context:   Task failed due to an internal error. File 'https://s3-external-1.amazonaws.com/my_bucket/test_datatypes_parquet/p_date=2018-09-09/part-00000-b23fbb42-1628-43ba-9773-78130e375eef.snappy.parquet  has an incompatible Parquet schema for column 's3://
  query:     1902645
  location:  dory_util.cpp:678
  process:   query1_73 [pid=10900]
  -----------------------------------------------

なお、Redshiftから以下のDDLを実行してテーブルを作成して、クエリを実行しましたが、動作に変化はありません。

CREATE EXTERNAL TABLE my_schema_db.test_datatypes_parquet_0 (
  id integer, 
  price decimal(10,2),
  profit double precision, 
  discount float, 
  name_full varchar(100), 
  name_short char(10), 
  order_date date,
  order_timestamp timestamp
  )
PARTITIONED BY ( 
  p_date date)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.ql.io.parquet.serde.ParquetHiveSerDe' 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.parquet.MapredParquetOutputFormat'
LOCATION
  's3://my_bucket/test_datatypes_parquet/'
TABLE PROPERTIES (
  'CrawlerSchemaDeserializerVersion'='1.0', 
  'CrawlerSchemaSerializerVersion'='1.0', 
  'UPDATED_BY_CRAWLER'='test_datatypes_parquet', 
  'averageRecordSize'='304', 
  'classification'='parquet', 
  'compressionType'='none', 
  'objectCount'='1', 
  'recordCount'='2', 
  'sizeKey'='3388', 
  'typeOfData'='file')

Amazon Athena からクエリを実行

Amazon AthenaのクエリエンジンであるPrestoは、カラムの型にDATE型やTIMESTAMP型をサポートしていませんので、NULLになります。

最後に

DATE型についてまとめますと、

  • DATEのフォーマットは、YYYY-MM-DD形式
  • TIMESTAMPのフォーマットは、YYYY-MM-DD HH:MI:SS形式
  • CSVとTSVとParquetは、パーティションキーにDATE型のデータをAmazon Redshift Spectrum と Amazon Athenaで参照できる(従来通りの仕様)
  • CSVとTSVは、カラムのDATE型のデータをAmazon Redshift Spectrum と Amazon Athena から参照できる(新たにサポート)
  • Parquetは、カラムのDATE型のデータをAmazon Redshift Spectrum と Amazon Athena から参照できない(新たにサポートされているはずだが、確認できなかった)
  • 今回はCSVとTSVとParquet以外の他のファイルフォーマット(実際にはSerde)については検証していない

以上の結果から、新たにCSVとTSVは、カラムのDATE型を定義して、データをクエリできる事が確認できました。一方、残念ながら、アナウンスにあったParquetのカラムのDATE型は、データの参照ができませんでした。以下のフォーラムを見ると、一度は1.0.1636がリリースされたが、問題があり従来のBuild1.0.1582に戻され、その後1/26に今回検証した1.0.1657がリリースされたようです。そのような経緯があり1.0.1657は当初のリリースノートの内容とは異なるのかもしれません。今後サポートされるのを期待して待ちたいと思います。

以上の結果から、Amazon RedshiftのテーブルデータをCSVやTSVにUNLOADしたデータであれば、Amazon Redshift Spectrum や Amazon Athenaから共通で参照できる事も確認できました。今回のDATE型のサポートやヘッダ行のスキップのサポートによって、データレイクへのマイグレーションにはずみがつきそうです。