Amazon Redshift Spectrum ダブルクォーテーションで囲まれた文字列をクエリする方法

はじめに

Amazon Athena の Hive DDL がダブルクォーテーションマーク (") をサポートしているのに対して、Amazon Redshift はシングルクォーテーションマーク (') を使用します。そのため、Amazon Redshift Spectrumでダブルクォーテーションマーク (")が含まれているファイルをクエリーするとうまくパースできません。そこで、Amazon Redshift Spectrum ダブルクォーテーションで囲まれた文字列にクエリするワークアラウンドについて解説します。

Amazon Redshift で DDL Hive を使用する際の構文エラー

Amazon Redshift は、Hive DDL と類似した CREATE EXTERNAL TABLE 用のデータ定義言語 (DDL) をサポートしています。ただし、2 つの DDL タイプは常に代替可能であるとは限りません。Hive DDL をコピーして Amazon Redshift 外部テーブルを作成または変更した場合、構文エラーが発生する可能性があります。以下に Amazon Redshift と Hive DDL の違いの例を示します。

  • Hive DDL がダブルクォーテーションマーク (") をサポートしているのに対して、Amazon Redshift はシングルクォーテーションマーク (') を使用します。
  • Amazon Redshift は STRING データタイプをサポートしていません。代わりに VARCHAR を使用します。

引用:Amazon Redshift Spectrum のクエリのトラブルシューティング

ケース1:ダブルクォーテーションで括られた数値・日付データが認識できない

これは簡単です。Amazon Athena の場合と同じように OpenCSVSerdeを利用して、テーブル定義・クエリの実行することが可能です。

Amazon Athena 新しくサポートされた OpenCSVSerDeを使ってみた

では、実際にやってみましょう。以下のデータ(users_from_athena.csv)があるとします。

"id","name","created_at" "1","foo","2018-02-01" "2","bar","2018-02-02" "3","baz","2018-02-03"

以下のようにRedshiftに外部テーブルを定義します。

CREATE EXTERNAL TABLE <schemaname>.users_from_athena(
  id varchar(255), 
  name varchar(255), 
  created_at varchar(255)
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.OpenCSVSerde' 
WITH SERDEPROPERTIES ( 
  'escapeChar'='\\', 
  'quoteChar'='\"', 
  'separatorChar'=',') 
STORED AS TEXTFILE
  LOCATION 's3://<bucketname>/users_from_athena/'
TABLE PROPERTIES (
  'classification'='csv',
  'skip.header.line.count'='1',
  'typeOfData'='file')
;

以下のようにクエリを実行できます。

cmdb=> select * from <schemaname>.users_from_athena;
 id | name  | created_at |
----+-------+------------+
 1  | foo   | 2018-02-01 |
 2  | bar   | 2018-02-02 |
 3  | baz   | 2018-02-03 |
(3 rows)

Time: 1273.384 ms (00:01.273)

OpenCsvSerdeは、データ型は全て文字列型として定義しています。もし、数値型や日付型等の別のデータ型として利用したい場合は、上記のクエリをサブクエリーとして型変換すると良いでしょう。

ケース2:AthenaとSpectrumのRegexSerDeでパースの結果が異なる?!

デフォルトで登録されている Amazon Athenaのsampledbのelb_logsテーブルを Amazon Redshift Spectrumから参照したところ、うまくパースができないようで、カラムの内容が異なりました。下記のxx行目が途中から表示されています。

cmdb=> select * from sampledb.elb_logs limit 1;
-[ RECORD 1 ]---------+----------------------------------------------------------------------------
timestamp             | 2014-09-25T23:00:23.107160Z
elbname               | lb-demo
requestip             | 245.121.88.40
requestport           | 17141
backendip             | 244.193.242.133
backendport           | 8888
requestprocessingtime | 9e-05
backendprocessingtime | 0.050973
clientresponsetime    | 4.8e-05
elbresponsecode       | 200
backendresponsecode   | 200
receivedbytes         | 0
sentbytes             | 2
requestverb           | GET
url                   | ttp://www.abcxyz.com:80/jobbrowser/?format=json&state=running&user=9u45ok8
protocol              | TP/1.1"                                                                    +
                      |

テーブル定義を保存している AWS Glue Data Catalogは同じなので、テーブル定義はAthenaとSpectrumで共通なので同じ結果が得られるはず、、、と当初は想定していました。

  • Hive DDL がダブルクォーテーションマーク (") をサポートしているのに対して、Amazon Redshift はシングルクォーテーションマーク (') を使用します。

Redshift Spectrum は ダブルクォーテーションではなくシングルクオーテーションをサポートするなど、必ずしも Athena と同一の Hive DDLと同じ結果を得られるとは限りません。上記の仕様の差異により、Redshift Spectrum は、ダブルクォーテーションで囲まれた文字列を正常にパースできない状況が発生しているものとを考えられます。

ワークアラウンドとしては、Parquet に変換したデータを Reshift Spectrum から参照するか、もしくはダブルクォーテーションで囲まれた文字列を1つの列として扱うよう DDL を変更します。

ダブルクォーテーションで囲まれた文字列を1つの列とした場合のDDLは以下のようになります。requestがダブルクォーテーションで囲まれた文字列なので、正規表現で取り除きます。

CREATE EXTERNAL TABLE `elb_logs_for_spectrum`(
  `timestamp` string COMMENT '', 
  `elbname` string COMMENT '', 
  `requestip` string COMMENT '', 
  `requestport` int COMMENT '', 
  `backendip` string COMMENT '', 
  `backendport` int COMMENT '', 
  `requestprocessingtime` double COMMENT '', 
  `backendprocessingtime` double COMMENT '', 
  `clientresponsetime` double COMMENT '', 
  `elbresponsecode` string COMMENT '', 
  `backendresponsecode` string COMMENT '', 
  `receivedbytes` bigint COMMENT '', 
  `sentbytes` bigint COMMENT '', 
  `request` string COMMENT '')
ROW FORMAT SERDE 
  'org.apache.hadoop.hive.serde2.RegexSerDe' 
WITH SERDEPROPERTIES ( 
  'input.regex'='([^ ]*) ([^ ]*) ([^ ]*):([0-9]*) ([^ ]*):([0-9]*) ([.0-9]*) ([.0-9]*) ([.0-9]*) (-|[0-9]*) (-|[0-9]*) ([-0-9]*) ([-0-9]*) \"(.*)\"$') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT 
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://us-east-1.elasticmapreduce.samples/elb-access-logs/data'
TBLPROPERTIES (
  'transient_lastDdlTime'='1508595294')

クエリを実行してみると、requestがそのまま表示されています。

cmdb=> select * from mysampledb.elb_logs_for_spectrum limit 1;
-[ RECORD 1 ]---------+-----------------------------------------------------------------------------------------
timestamp             | 2014-09-25T21:28:40.726570Z
elbname               | lb-demo
requestip             | 242.183.64.11
requestport           | 67890
backendip             | 252.194.243.95
backendport           | 443
requestprocessingtime | 9e-05
backendprocessingtime | 0.047148
clientresponsetime    | 7.9e-05
elbresponsecode       | 200
backendresponsecode   | 400
receivedbytes         | 0
sentbytes             | 2
request               | GET http://www.abcxyz.com:80/jobbrowser/?format=json&state=running&user=9u45ok8 HTTP/1.1

最後に

ケース1で紹介した OpenCSVSerdeを利用すると、Athenaの実行結果のファイルも直接パースできますので、AthenaとRedshift Spectrumの連携などもおすすめです。Amazon Redshift はシングルクォーテーションマーク (') を使用するという仕様は、Hive DDLを使い慣れた人にとってはハマりポイントと感じました。ケース2の場合はダブルクォーテーションで囲まれた文字列を1つの列として扱い、Redshiftの関数でパースするというのも一つの方法ではないかと思います。