Redshift、Athena(Presto)、Glue(Spark SQL) のSQL仕様比較

Amazon Redshift

• 歴史

Amazon Redshift debuted in 2012 as the first cloud data warehouse, and remains the most popular one today. But Redshift wasn't developed in-house. Amazon invested $20 million in a company called ParAccel, and in return gained the license to use code from ParAccel Analytic Database (PADB) for Redshift. PADB was notable because it was a columnar database that ran on commodity hardware, which made it a natural choice as a basis for a cloud-based analytic database platform.

While that deal might have seemed worthwhile for ParAccel at the time, it worked out even better for Amazon in the long run. In 2015 Amazon CTO Werner Vogels called Redshift "the fastest-growing service in AWS, ever." Meanwhile, ParAccel was acquired by Actian in 2013, and PADB was renamed Actian Matrix.

PADB was itself based on PostgreSQL – so to some extent, Redshift is based on PostgreSQL – but "based on" leaves a lot of room for difference. If you're familiar with PostgreSQL features and syntax, how easy will it be to get used to Redshift?

How Redshift differs from PostgreSQL

• PostgreSQL 8.0.2 ベース

Amazon Redshift は PostgreSQL 8.0.2 に基づいています。
（中略）
PostgreSQL 9.x には、Amazon Redshift によってサポートされていない機能が一部含まれています。さらに、Amazon Redshift SQL と PostgreSQL 8.0.2 との間には、認識しておく必要がある重要な違いがあります。このセクションでは、Amazon Redshift と PostgreSQL 8.0.2 との違いに焦点を当てるとともに、Amazon Redshift SQL 実装を十分に活用したデータウェアハウスを開発するためのガイダンスを提供します。

Amazon Redshift および PostgreSQL

• ANSI SQL 準拠

PostgreSQL supports complete repertoire of the ANSI SQL92 statements and partly also ANSI SQL2003.

Introduction to PostgreSQL SQL – PostgreSQL

• PL/pgSQL

Amazon Redshift のストアドプロシージャは PostgreSQL PL/pgSQL プロシージャ言語に基づいています。ただし、いくつかの重要な違いがあります。このリファレンスでは、Amazon Redshift で実装されている PL/pgSQL 構文の詳細を確認できます。PL/pgSQL の詳細については、PostgreSQL 8.0 ドキュメントの「PL/pgSQL - SQL 手続き言語」を参照してください。

PL/pgSQL 言語リファレンス

• ユーザー定義関数

カスタムスカラーのユーザー定義関数 (UDF) は、SQL SELECT 句または Python プログラムを使用して作成できます。新しい関数はデータベースに保存され、既存の Amazon Redshift 関数を実行する場合とほぼ同様に、十分な権限を持つすべてのユーザーが使用できます。

Python UDF の場合、標準の Python 機能を使用できるだけではなく、独自のカスタム Python モジュールをインポートできます。詳細については、「UDF のための Python 言語のサポート」を参照してください。

ユーザー定義関数の作成

• 実装方法が異なる機能
• サポートされていない PostgreSQL 機能
• サポートされていない PostgreSQL データ型
• サポートされていない PostgreSQL 関数
• サポートされている PL/pgSQL ステートメント

Amazon Athena

• Presto ベース

Athena DML クエリステートメントは Presto 0.172 に基づいています。これらの関数の詳細については、オープンソースの Presto ドキュメントの「Presto 0.172 関数と演算子」を参照してください。Presto 関数 トピックでは、そのドキュメントの特定のサブセクションへのリンクを提供しています。

Athena は Presto のすべての機能をサポートしているわけではなく、いくつかの大きな違いがあります。詳細については、このセクションの特定のステートメントのリファレンストピック、および「考慮事項と制約事項」を参照してください。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/functions-operators-reference-section.html

• ANSI SQL 準拠

Q: Amazon Athena ではどの種類のクエリがサポートされていますか?
Amazon Athena では、ANSI SQL クエリがサポートされています。Amazon Athena では、オープンソースでインメモリの分散型 SQL エンジンである Presto が使用されているため、大規模な結合、ウィンドウ関数、配列を含む複雑な分析を処理できます。

Amazon Athena のよくある質問

• HiveQL DDL

Athena クエリエンジンは、HiveQL DDL に基づいています。

Athena はすべての DDL ステートメントをサポートしていないため、HiveQL DDL と Athena DDL にはいくつかの違いがあります。詳細については、このセクションのリファレンストピック、および「サポートされない DDL」を参照してください。

https://docs.aws.amazon.com/ja_jp/athena/latest/ug/language-reference.html

Q: Athena で Hive クエリを実行できますか?
Amazon Athena では、Hive を DDL (データ定義言語) 用や、テーブルおよび/またはパーティションの作成、変更、削除にのみ使用します。サポートされているステートメントすべての一覧については、 こちらをクリックしてください。Amazon S3 で SQL クエリを実行する場合、Athena では Presto が使用されます。Amazon S3 のデータをクエリする場合、ANSI 準拠 SQL SELECT ステートメントを実行できます。

Amazon Athena のよくある質問

• ユーザー定義関数

Amazon Athena のユーザー定義関数 (UDF) を使用すると、レコードまたはレコードのグループを処理するカスタム関数を作成できます。UDF は、パラメータを受け入れ、作業を実行し、結果を返します。

Athena で UDF を使用するには、SQL クエリの SELECT ステートメントの前に USING FUNCTION 句を記述します。この SELECT ステートメントは UDF を参照し、クエリの実行時に、UDF に渡される変数を定義します。SQL クエリは、UDF を呼び出すときに Java ランタイムを使用して Lambda 関数を呼び出します。UDF は、Java デプロイパッケージのメソッドとして Lambda 関数内で定義されます。1 つの Lambda 関数に対して同じ Java デプロイパッケージで複数の UDF を定義できます。また、USING FUNCTION 句で Lambda 関数の名前も指定します。

Athena UDF の Lambda 関数をデプロイするための 2 つのオプションがあります。Lambda を使用して直接関数をデプロイするか、AWS Serverless Application Repository を使用できます。UDF の既存の Lambda 関数を検索するには、パブリック AWS Serverless Application Repository またはプライベートリポジトリを検索し、Lambda にデプロイします。また、Java ソースコードを作成または変更して、JAR ファイルにパッケージ化、Lambda またはAWS Serverless Application Repository を使用してデプロイすることもできます。開始するために、Java ソースコードとパッケージの例を提供します。Lambda の詳細については、「AWS Lambda Developer Guide」を参照してください。AWS Serverless Application Repository の詳細については、AWS Serverless Application Repository 開発者ガイド を参照してください。

ユーザー定義関数を使用したクエリ（プレビュー）

• 制約

• ストアドプロシージャはサポートされていません。
• CREATE TABLE AS SELECT (CTAS) ステートメントで作成できるパーティションの最大数は 100 です。詳細については、「CREATE TABLE AS」を参照してください。
• PREPARED ステートメントはサポートされていません。USING を指定して EXECUTE を実行することはできません。
• CREATE TABLE LIKE はサポートされていません。
• DESCRIBE INPUT および DESCRIBE OUTPUT はサポートされていません。
• EXPLAIN ステートメントはサポートされていません。
• Presto フェデレーションコネクタはサポートされていません。データソースを接続するために を使用します。詳細については、「 を使用する 」を参照してください。
• 複雑なデータ型 (array、map、struct) の列に対してクエリを実行する場合で、データの保存に Parquet を使用している場合、Athena は現行では、想定されているように指定された列のみを選択して読み取るのではなく、データの列全体を読み込みます。これは既知の問題です。

Amazon Athena での SQL クエリに関する考慮事項と制約事項

AWS Glue

• Spark SQL

Glue version	Supported Spark and Python versions	Changes in Functionality
Glue 0.9	Spark 2.2.1 Python 2.7	Jobs that were created without specifying a Glue version default to Glue 0.9.
Glue 1.0	Spark 2.4.3 Python 2.7 Python 3.6	You can maintain job bookmarks for Parquet and ORC formats in Glue ETL jobs (using Glue Version 1.0). Previously, you were only able to bookmark common Amazon S3 source formats such as JSON, CSV, Apache Avro and XML in AWS Glue ETL jobs.

AWS Glue Release Notes

SparkSql follows Hive style, so you can refer to Hive Syntax for better documentation.
The supported and unsupported Hive features by SparkSql can be found in the official documentation.

Where to find Spark SQL syntax reference? - Stack Overflow

This section provides a reference for Apache Spark SQL and Delta Lake, a set of example use cases, and information about compatibility with Apache Hive.

SQL — Databricks Documentation

• ANSI SQL 準拠

SQL
Spark 2.0 substantially improved SQL functionalities with SQL2003 support. Spark SQL can now run all 99 TPC-DS queries. More prominently, we have improved:

• A native SQL parser that supports both ANSI-SQL as well as Hive QL
• Native DDL command implementations
• Subquery support, including
  • Uncorrelated Scalar Subqueries
  • Correlated Scalar Subqueries
  • NOT IN predicate Subqueries (in WHERE/HAVING clauses)
  • IN predicate subqueries (in WHERE/HAVING clauses)
  • (NOT) EXISTS predicate subqueries (in WHERE/HAVING clauses)
• View canonicalization support

In addition, when building without Hive support, Spark SQL should have almost all the functionality as when building with Hive support, with the exception of Hive connectivity, Hive UDFs, and script transforms.

Spark Release 2.0.0 | Apache Spark

• ユーザー定義関数

ユーザー定義関数 API では、関数の処理に使用する AWS Glue データ型およびオペレーションについて説明します。

ユーザー定義関数 API

• Python ライブラリ

純粋な Python で書かれていれば、AWS Glue ETL スクリプトで Python 拡張モジュールおよびライブラリを使用できます。pandas などの C ライブラリは現在のところサポート外です。他の言語で書かれた拡張機能も同様です。

AWS Glue での Python ライブラリの使用

参考

• AWS Glueのジョブ同時実行数制限には気をつけた方がいいという話 ｜ Developers.IO
• https://docs.aws.amazon.com/ja_jp/general/latest/gr/aws_service_limits.html