Amazon Athena: Tipsと機能/サービス/SQLの制限事項 まとめ #reinvent

新サービス Amazon Athenaについて、マニュアルとこれまでの検証結果をベースに、利用するにあたり抑えておいたほうが良い思われる、Tipsや制限事項についてまとめました。

Tips

もしかすると役立つかもしれないTipsやトリックを紹介します。

アンダースコアで始まるテーブル名

アンダースコア（_）で始まるテーブル名にしたい場合はテーブル名をバッククォートで囲んでください。

例えば：

CREATE EXTERNAL TABLE IF NOT EXISTS `default._users` (
  `_id` int,
  `_name` string,
  `_created` date
)
...

しかし、このテーブルを参照するときはバッククォートを外さないと参照できないのでご注意を。

SELECT * FROM default._users;

なお、同じようにデータベース名に同じ指定で可能でしたが、そのデータベースの指定して作ったテーブルが「TABLES」に現れないので、使わないほうが良いでしょう。

LOCATION句の末尾はスラッシュ（/）に指定！

データソースの指定はLOCATION句を用いますが、LOCATION句はフォルダ指定なので、スラッシュ（/）で、終わるように記述してください。スラッシュ（/）で終わらなかったり、ワイルドカード指定としてアスタ（*）、ファイル名指定などはできません。要はS3のフォルダ指定のルールと一緒です。

正しい例：

s3://path_to_bucket/

間違った例：

s3://path_to_bucket
s3://path_to_bucket/*
s3://path_to_bucket/mySpecialFile.dat

テーブル名は大文字と小文字を区別しません

Apache Sparkから利用する場合は、テーブルの列名は小文字でなければなりません。Athenaは、大文字と小文字を区別しないですが、Apache Sparkは、小文字のテーブル名を必要とします。

なお、Athenaは大文字のデータベース名・テーブル名・カラム名を指定しても、小文字として作成されます。SELECTする時のカラム指定に大文字を指定してもエラーにならず、正しいレコードを得られますが、カラム名は大文字で取得されることを確認しています。

テーブル名は小文字英数字とアンダースコア文字のみ

テーブル名は、小文字英数字とアンダースコア（_）以外の特殊文字を含めることはできません。

機能の制限

• ユーザー定義関数（UDFまたはUDAFs）がサポートされていません。
• ストアドプロシージャはサポートされていません。
• Hive や Prestoで提供されているトランザクションを Athenaではサポートしていません。
• LZOはサポートされていません。代わりにSnappyの使用を推奨しています。(Snappy、Zlib、GZIPがサポートされている圧縮形式です。)

サービスの制限

以下のサービス上の制限がありますが、AWSサポート経由で上限緩和申請することができます。

• 一度に１つクエリーしか渡せません(補足1)
• １アカウントあたりの同時実行数： 5クエリー
• クエリータイムアウト： 30分
• データベースの数： 100
• テーブル： データベースごとに100
• パーティション数： テーブルあたり20K
• アカウントごとのS3バケット数の上限： 100 (補足2)
• 結果をログに記録する別のバケットも必要になることを考慮してください。(補足3)

補足1：「一度に１つクエリーしか渡せません」とは、例えば、複数のINSERTをまとめて実行するなどです。この点はSQL Workbench/Jなどで実行することで制限を回避できます。これは上限緩和申請できません。

補足2：「アカウントごとのS3バケット数の上限（100）」とは、Athenaの制限というよりはS3の制限です。必要であればAWSサポート経由でサクッと上限緩和申請してしまいましょう。

補足3：Athenaでクエリーを実行すると、初回にバケットが自動的に作成され、その下に年月日毎のフォルダ分けされでクエリーの実行結果が保存されます。なお、このログについては実行するたびに作成されるので、気がついたらS3にファイルが沢山！！なんてことがありえます。

なので、定期的に削除する、もしくはS3バケットにライフサイクル管理設定で定期的に削除する等の設定することをおすすめします。 ライフサイクル管理設定の設定方法は AWS再入門 Amazon S3編 の「ライフサイクル管理」を御覧ください。

SQLの制限

Amazon Athenaは Hive メタストア と Presto がベースとなっています。Athenaの構文は標準のANSIのSQLである、参照（SELECT）と結合（JOIN）とHiveQL DDL 構文によるメタデータの更新（CREATE、ALTER）の組み合わせで構成されています。

SQL Queries

• SELECT

DDL Statements

• ALTER DATABASE SET DBPROPERTIES
• ALTER TABLE ADD PARTITION
• ALTER TABLE DROP PARTITION
• ALTER TABLE RENAME PARTITION
• ALTER TABLE RENAME (table)
• ALTER TABLE SET LOCATION
• ALTER TABLE SET TBLPROPERTIES
• CREATE DATABASE
• CREATE TABLE
• CREATE TABLE LIKE
• DESCRIBE TABLE
• DROP DATABASE
• DROP TABLE
• MSCK REPAIR TABLE
• SHOW COLUMNS
• SHOW CREATE TABLE OR VIEW
• SHOW DATABASES
• SHOW PARTITIONS
• SHOW TABLES
• SHOW TBLPROPERTIES
• VALUES

Hive DDLsの中で Athena がサポートしていないDDL 構文

• CREATE ROLE
• CREATE VIEW
• DROP ROLE
• GRANT ROLE
• REVOKE ROLE
• SHOW GRANT
• SHOW ROLE GRANT
• SHOW PRINCIPALS
• SHOW ROLES
• SHOW CURRENT ROLES
• EXPORT TABLE
• IMPORT TABLE
• SHOW COMPACTIONS
• SHOW TRANSACTIONS
• SHOW INDEXES
• SHOW LOCKS
• CREATE INDEX
• DROP INDEX
• ALTER INDEX
• LOCK TABLE
• LOCK DATABASE
• UNLOCK TABLE
• UNLOCK DATABASE
• CREATE TEMPORARY MACRO
• DROP TEMPORARY MACRO
• ALTER TABLE table_name NOT CLUSTERED
• ALTER TABLE table_name CLUSTERED BY
• ALTER TABLE table_name NOT SORTED
• ALTER TABLE table_name SKEWED BY
• ALTER TABLE table_name NOT SKEWED
• ALTER TABLE table_name NOT STORED AS DIRECTORIES
• ALTER TABLE table_name SET SKEWED LOCATION
• ALTER TABLE table_name EXCHANGE PARTITION
• ALTER TABLE table_name ARCHIVE PARTITION
• ALTER TABLE table_name UNARCHIVE PARTITION
• ALTER TABLE table_name TOUCH
• ALTER TABLE table_name partitionSpec COMPACT
• ALTER TABLE table_name partitionSpec CONCATENATE
• ALTER TABLE table_name partitionSpec SET FILEFORMAT
• ALTER TABLE table_name partitionSpec ADD COLUMNS
• ALTER TABLE table_name partitionSpec CHANGE COLUMNS
• ALTER TABLE table_name partitionSpec REPLACE COLUMNS
• START TRANSACTION
• COMMIT
• ROLLBACK
• DESCRIBE DATABASE
• DFS
• DELETE FROM

関数

Athena の クエリでサポートされる関数は、Prestoのウェブサイトで参照できます。詳細については、Prestoの関数と演算子のマニュアルを御覧ください。

参考

• SQL and HiveQL Reference
• Known Limitations
• Service Limits

まとめ

2016/12/06時点のドキュメントと検証結果に基づいて記載しています。これらの前提を抑えれば、トラブルなく利用できるでしょう。これから進化を予感させるサービスですのでアップデートがあればお知らせします。