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

2015.07.08

はじめに

今年の春に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 ビット版の2種類のドライバが提供されています。

Connect Client からダウンロード

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

redshift-download-odbcdriver-1.png

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

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 が表示されています。

redshift-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では1行ですが、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)

31万レコードの取得(デフォルト)

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

  • 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%に達するとリモートデスクトップ切れてしまい、何もできなくなり計測中断。EC2インスタンスの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.

再び、カーソルサイズを大きく取りリトライ。サーバで結果セットを500レコードずつ返してくれると予想していましたが、共にメモリの使用率が徐々に増加し、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がデフォルトになりこれまで以上に使いやすく、パフォーマンスチューニングの選択肢が増えますので。システムの更新タイミングや新規開発ではぜひご活用ください。