Amazon Redshift カスタムODBCドライバを試してみました

#Amazon Redshift

#AWS

石川覚

2015.07.08

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

はじめに

今年の春にAmazon Redshiftに最適化したカスタムODBCドライバがリリースされました。従来のPostgreSQLのODBCドライバとの違いやパフォーマンスについてご報告します。

【AWS発表】カスタムODBC/JDBCドライバとQuery Visualization for Amazon Redshift より

ODBCドライバはLinux、WindowsとMac OS Xでご利用頂けます。このドライバはODBC 3.8をサポートしており、Unicodeデータやパスワードの扱いが向上しており、標準でkeep aliveが有効でDECLARE/FETCHを使用するよりもメモリ効率が良いsingle-rowモードを実装しています。ODBC 2.xとも互換性をもっており、Unicodeと64-bitアプリケーションをサポートしています。このドライバはOpenSSL Projectで開発されたOpenSSL Toolkitを使用しています。

ODBCドライバーの入手

Amazon Redshift console の Connect Client から取得する方法とサイトからダウンロードする方法の2種類あります。また、Amazon Redshift カスタムODBCドライバは、32ビット版と64 ビット版の２種類のドライバが提供されています。

Connect Client からダウンロード

Amazon Redshift console の [Connect Client] ページからダウンロードする方法が簡単です。

サイトからダウンロード＆インストール・設定

Redshiftの管理ガイドからダウンロードすることも可能です。以下のサイトでは各プラットフォームOS毎のインストールや設定方法の記載もありますのでご確認ください。

Microsoft Windows オペレーティングシステムに Amazon Redshift ODBC ドライバーをインストールして設定する

Linux オペレーティングシステムに Amazon Redshift ODBC ドライバーをインストールする

Mac OS X に Amazon Redshift ODBC ドライバーをインストールする

しかし、最新のドライバを入手したい場合は、英語サイトも念のため確認して下さい。例えば、執筆時点（2015/7時点）では、日本語サイトは、Version 1.0.1.00.00 であるのに対して、英語サイトは Version 1.2.1.1001 です。

Install and Configure the Amazon Redshift ODBC Driver on Microsoft Windows Operating Systems

Install the Amazon Redshift ODBC Driver on Linux Operating Systems

Install the Amazon Redshift ODBC Driver on Mac OS X

ODBCドライバで接続する

入手したODBCドライバを使って、Redshiftに接続してみます。

ODBC接続文字列の取得

AWS マネジメントコンソールのAmazon Redshift console を開き、[Clusters] ページに ODBC URL が表示されています。

接続の例

以下の例では、Amazon Redshift (x64) 1.02.01.1001で「サーバー証明書ありでSSL接続」する場合のC#のサンプルコードです。

            try
            {
                //Redshift ODBC Driver - 64 bits
                string connString = "Driver={Amazon Redshift (x64)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4};SSL=true;Sslmode=Require",
                    server, DBName, masterUsername,
                    masterUserPassword, port);
                /*
                //Redshift ODBC Driver - 32 bits
                string connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4};SSL=true;Sslmode=Require",
                    server, DBName, masterUsername,
                    masterUserPassword, port);
                */
                // Make a connection using the psqlODBC provider.
                OdbcConnection conn = new OdbcConnection(connString);
                conn.Open();

                // Try a simple query.
                string sql = query;
                OdbcDataAdapter da = new OdbcDataAdapter(sql, conn);
                da.Fill(ds);
                dt = ds.Tables[0];
                foreach (DataRow row in dt.Rows)
                {
                    Console.WriteLine(row["lo_orderkey"]);
                }
                conn.Close();
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.Error.WriteLine(ex.Message);
                Console.ReadKey();
            }
        }

設定オプション

設定オプションはRedshiftに接続するときに ODBC URL に付加します。

ODBC オプション	一致する PostgreSQL オプションの有無	デフォルト値	説明
BoolsAsChar	はい	0	このオプションが有効（1）になっていると、ドライバーはブール値を長さが 5 文字の SQL_VARCHAR データ型として公開します。このオプションが無効（0）になっていると、ドライバーはブール値を SQL_BIT データ型として公開します。
Database	はい		接続が確立されるときに使用するデータベースの名前。
Driver	はい		Amazon Redshift ODBC ドライバーの共有オブジェクトファイルの場所。
Fetch	はい	100	UseDeclareFetch が有効になっているときにドライバーが返す行数。
KeepAlive	いいえ。キープアライブが TCP/IP レベルで無効になっている場合、KeepAliveTime と KeepAliveInterval は 0 に設定されます。	1	このオプションが有効（1）になっていると、ドライバーは TCP キープアライブを使用して接続がタイムアウトしないようにします。このオプションが無効（0）になっていると、ドライバーは TCP キープアライブを使用しません。
KeepAliveCount	いいえ	0	接続が切断されていると見なされる前に失うことが許容される TCP キープアライブパケットの数。このオプションが 0 に設定されていると、ドライバーはこの設定の代わりに TCP/IP システムデフォルトを使用します。
KeepAliveTime	はい	0	ドライバーが TCP キープアライブパケットを送信する前にアイドル状態である秒数。このオプションが 0 に設定されていると、ドライバーはこの設定の代わりに TCP/IP システムデフォルトを使用します。
KeepAliveInterval	はい	0	TCP キープアライブを再送信する間隔の秒数。
Locale	いいえ	en-US	エラーメッセージに使用するロケール。
MaxBytea	はい	255	BYTEA 列の最大データ長（バイト単位）。
MaxLongVarChar	はい	8190	LONG VARCHAR 列の最大データ長（UTF-8 コード単位）。
MaxVarchar	はい	255	VARCHAR 列の最大データ長（UTF-8 コード単位）。
Port	はい		Redshift サーバー上の接続先のポート。 Note デフォルトでは、Amazon Redshift はポート 5439 を使用します。
PWD または Password	はい		Amazon Redshift サーバーに接続するために使用するパスワード。
Server または Servername	はい		Amazon Redshift サーバーの IP アドレスまたはホスト名。
SingleRowMode	いいえ	0	このオプションが有効（1）になっていると、ドライバーはクエリの結果を一度に 1 行ずつ返します。このオプションは、大規模な結果のクエリを実行するため、結果全体をメモリに取り込みたくない場合に有効にします。このオプションと UseDeclareFetch の両方が無効（0）になっていると、ドライバーはクエリの結果全体をメモリに取り込みます。 Note UseDeclareFetch が有効（1）になっていると、それは SingleRowMode よりも優先されます。SingleRowMode が有効（1）になっており、UseDeclareFetch が無効（0）になっていると、SingleRowMode は UseMultipleStatements よりも優先されます。
SSLMode	はい	require	接続するときに使用する SSL 証明書認証モード。使用できる SSL モードの詳細については、「ODBC で SSL サーバー証明書を使用する」を参照してください。
TextAsLongVarchar	はい	0	このオプションが有効（1）になっていると、ドライバーは LONG VARCHAR データとして TEXT 列を返します。このオプションが無効（0）になっていると、ドライバーは TEXT データとして TEXT 列を返します。
UID	はい		Amazon Redshift サーバーに接続するために使用するユーザー名。
UseDeclareFetch	はい	0	このオプションが有効（1）になっていると、ドライバーは一度に特定の数の行を返します。行数を設定するには、Fetch オプションを使用します。このオプションが無効（0）になっており、SingleRowMode が有効（1）になっていると、ドライバーはクエリの結果を一度に 1 行ずつ返します。SingleRowMode も無効（0）になっていると、ドライバーはクエリの結果全体をメモリに取り込みます。 Note UseDeclareFetch が有効になっていると、UseDeclareFetch は SingleRowMode および UseMultipleStatements よりも優先されます。
UseMultipleStatements	いいえ	0	このオプションが有効（1）になっていると、ドライバーは複数のステートメントに分割されたクエリを実行できます。このオプションが無効（0）になっていると、ドライバーはクエリを 1 つのステートメントとして実行します。 Note UseDeclareFetch が有効（1）になっていると、UseDeclareFetch は UseMultipleStatements よりも優先されます。UseDeclareFetch が無効（0）になっており、SingleRowMode が有効（1）になっていると、SingleRowMode は UseMultipleStatements よりも優先されます。
Username	はい		UID (Amazon Redshift サーバーに接続するために使用するユーザー名) と同じ情報。UID が定義されていると、UID はユーザー名よりも優先されます。
UseUnicode	いいえ	0	このオプションが有効（1）になっていると、ドライバーはデータを Unicode 文字型として返します。 CHAR は SQL_WCHAR として返されます。 VARCHAR は SQL_WVARCHAR として返されます。テキストは SQL_WLONGVARCHAR として返されます。このオプションが無効（0）になっていると、ドライバーはデータを通常の SQL の型として返します。 CHAR は SQL_CHAR として返されます。 VARCHAR は SQL_VARCHAR として返されます。 TEXT は SQL_LONGVARCHAR として返されます。

PostgreSQL ODBC ドライバとの相違点

データ取得に関するパラメタは優先順位がありますのでご注意ください。ざっくりとした優先順位は以下のとおりです。

左側が有効（1）の場合は左のパラメタが優先されます。

UseDeclareFetch ＞ SingleRowMode > UseMultipleStatements

KeepAlive

PostgreSQLではデフォルトは無効（0）でしたが、Redshift Custom ドライバでは有効（1）になります。KeepAlive オプションに有効（1）を設定しないと、長いクエリ実行中に接続が切断されるトラブルに遭いましたが、そのような心配はありません。

KeepAliveCount

デフォルトでは、ドライバーはこの設定の代わりに TCP/IP システムデフォルトを使用します。

Locale

エラーメッセージに使用するロケールを指定できます。デフォルト英語（en-US）です。

SingleRowMode

このオプションが有効（1）になっていると、ドライバーはクエリの結果を一度に 1 行ずつ返します。DECLARE/FETCHを使用するよりもメモリ効率が良い Redshift Custom ドライバ独自の機能です。デフォルトは無効（0）です。

SingleRowMode と UseDeclareFetch の両方が無効（0）になっていると、ドライバーはクエリの結果全体をメモリに取り込みます。
UseDeclareFetch が有効（1）になっていると、それは SingleRowMode よりも優先されます。
SingleRowMode が有効（1）になっており、UseDeclareFetch が無効（0）になっていると、SingleRowMode は UseMultipleStatements よりも優先されます。

UseDeclareFetch

UseDeclareFetch が有効（1）になっていると、SingleRowMode や UseMultipleStatements よりも優先されます。

SingleRowModeでは１行ですが、UseDeclareFetchを有効（1）に設定して、Fetchで一度に取得するレコード数を指定することによって、大きなデータを高速に取得する方法として、Redshiftのパフォーマンスチューニングでよく使われるアプローチです。

UseMultipleStatements

デフォルトは無効（0）です。このオプションが有効（1）になっていると、複数のステートメントに分割されたクエリを実行できます。但し、UseDeclareFetch と SingleRowMode が共に無効（0）であるときに限られます。

UseUnicode

デフォルトは無効（0）です。このオプションが有効（1）になっていると、ドライバーはデータを Unicode 文字型として返します。

カスタムODBCドライバ vs PostgreSQL ODBCドライバ

2015/7現在、それぞれ最新のAmazon Redshift (x64) 1.02.01.1001（AmazonRedshiftODBC64-1.2.1.1001.msi）と PostgreSQL UNICODE(x64) 9.03.04.00（psqlodbc_09_03_0400.zip）で比較しました。

Redshiftはリーダーノードで受け付けたクエリーはC++のコードに変換され、コンパイルした結果を、コンピュートノードに配布して実行しますので、一般的な行指向のRDBと比較すると最初のリクエストのオーバーヘッドは大きい傾向があります。また、クライアントの処理性能を評価するので、クエリに条件指定や集計などサーバーに負荷がかかるようなクエリではなく、単純にテーブルの全件を取得する時間を計測します。

計測用のコード（C#）

        public static void Main(string[] args)
        {

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();

            // Endpoint
            string server = "my-redshift.abcdefghijkl.us-east-1.redshift.amazonaws.com";

            // Port
            string port = "5439";

            // Name
            string masterUsername = "cm_devel";

            // Password
            string masterUserPassword = "password";

            // DBName
            string DBName = "cmdb";

            // Query
            string query = "select * from lineorder_test;";

            try
            {
                DateTime start = DateTime.Now;
                // Create the ODBC connection string.

                //Redshift ODBC Driver - 64 bits
                string connString = "Driver={Amazon Redshift (x64)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, DBName, masterUsername,
                    masterUserPassword, port);

                /*
                //Redshift ODBC Driver - 32 bits
                string connString = "Driver={Amazon Redshift (x86)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4}",
                    server, DBName, masterUsername,
                    masterUserPassword, port);
                */

                // Make a connection using the psqlODBC provider.
                OdbcConnection conn = new OdbcConnection(connString);
                conn.Open();

                long cnt = 0;
                long totalcnt = 0;

                // Try a simple query.
                string sql = query;
                OdbcDataAdapter da = new OdbcDataAdapter(sql, conn);
                da.Fill(ds);
                dt = ds.Tables[0];
                foreach (DataRow row in dt.Rows)
                {
                    //Console.WriteLine(row["lo_orderkey"]);
                    cnt++;
                    if (cnt >= 1000)
                    {
                        totalcnt += cnt;
                        cnt = 0;
                        Console.WriteLine("progress : " + totalcnt);
                    }
                }
                totalcnt += cnt;
                conn.Close();
                Console.WriteLine("totalcnt : " + totalcnt);
                Console.WriteLine((DateTime.Now - start).TotalSeconds + "s");
                Console.ReadKey();
            }
            catch (Exception ex)
            {
                Console.Error.WriteLine(ex.Message);
                Console.ReadKey();
            }
        }

パフォーマンスの計測条件

クエリ性能検証リージョン：バージニアリージョン（us-east-1）
ノードタイプとノード数：dc1.large x 2
クライアントのEC2インスタンス：c3.2xlarge
VPC内同一subnet
データ：AWSが提供しているサンプルデータ（s3://awssampledbapnortheast1/ssbgz/lineorder）

３1万レコードの取得（デフォルト）

両者の性能は大きく変わりませんでした。

Amazon Redshift (x64) 1.02.01.1001：8765ミリ秒
PostgreSQL UNICODE(x64) 9.03.04.00：8452 ミリ秒

103万レコードの取得（デフォルト）

両者の性能は大きく変わりませんでしたが、PostgreSQLのドライバのほうがメモリの使用量が多く確保されていました。

Amazon Redshift (x64) 1.02.01.1001：31045ミリ秒
PostgreSQL UNICODE(x64) 9.03.04.00：28420 ミリ秒

1000万レコードの取得（デフォルト）

共にメモリの使用率が100%に達するとリモートデスクトップ切れてしまい、何もできなくなり計測中断。EC２インスタンスのrestartではなく、stop/startで復旧しました。一方、Redshiftには接続だけが取り残されてクエリーが実行されたままになっていたのでコネクションを強制切断しました。

ODBCドライバはデフォルトでこの使い方はありえないようです。Amazon JDBC ドライバはエラかったなー。

Amazon Redshift (x64) 1.02.01.1001：計測不能（リモートデスクトップ切れる）
PostgreSQL UNICODE(x64) 9.03.04.00：計測不能（リモートデスクトップ切れる）

1000万レコードの取得（UseDeclareFetch、Fetch=500）

UseDeclareFetchを有効（1）、Fetchのレコード数を500に設定して、リトライしました。

                //Redshift ODBC Driver - 64 bits
                string connString = "Driver={Amazon Redshift (x64)};" +
                    String.Format("Server={0};Database={1};" +
                    "UID={2};PWD={3};Port={4};UseDeclareFetch=1;Fetch=500",
                    server, DBName, masterUsername,
                    masterUserPassword, port);

しかし、初回はカーソルサイズが小さかったため、エラーとなりました。

ERROR [HY000] [Amazon][RedShift ODBC] (30) Error occurred while trying to execute a query: ERROR: exceeded the maximum size allowed for the result set of a cursor operation. current size: "8053063696". analyze the current configuration via stv_cursor_configuration, and consider increasing the value of the max_cursor_result_set_size configuration parameter.

再び、カーソルサイズを大きく取りリトライ。サーバで結果セットを５００レコードずつ返してくれると予想していましたが、共にメモリの使用率が徐々に増加し、95%に達したところで計測を断念しました。

Amazon Redshift (x64) 1.02.01.1001：計測不能（メモリの使用率が95%で断念）
PostgreSQL UNICODE(x64) 9.03.04.00：計測不能（メモリの使用率が95%で断念）

最後に

今回の検証は Amazon のカスタム ODBC ドライバと PostgreSQL ODBC ドライバの比較をしました。Amazon のカスタム ODBC ドライバの独自パラメタである SingleRowMode について今回は検証できませんでしたが、予測不能な大きなデータの取得には強い味方になりそうです。

ドライバを置き換えるだけで、keepaliveがデフォルトになりこれまで以上に使いやすく、パフォーマンスチューニングの選択肢が増えますので。システムの更新タイミングや新規開発ではぜひご活用ください。