ActiveRecordを使ってRedshiftから大量のデータを効率的に読み出す

こんにちは、トレンド調査ラボの井上寛之（@inohiro）です。 普段は、クックパッドの検索ログを基にした法人向けデータサービス「たべみる」の開発や、 広告事業周辺のデータ分析などを担当しています。

Amazon Redshiftなどのデータベースに蓄積されたログなどの大量のデータに対して、 日次や週次などの単位でバッチ処理を行っている方は多くいらっしゃると思います。 ログなどを扱うバッチ処理では、処理対象が膨大であるとアプリケーションが使うメモリが増大し、 枯渇してしまう恐れもあるため、データの扱いに気をつける必要があります。 データベース内で完結するバッチ処理ならばそこまで気にする必要は無いかもしれませんが、 外部のプログラムからデータを読み出して処理する場合は特に注意が必要です。

そこで考えられる一つの工夫として、処理対象を分割して、繰り返して処理を行う方法が挙げられます。 一般的なRDBMSが備えるカーソルと呼ばれる機能を利用することで、処理対象を分割して少しずつ処理することができます。

本稿では、特に Rails（ActiveRecord）を使って書かれたAmazon Redshiftを利用するようなバッチ処理において、 カーソル機能を簡単に利用できる "redshift_cursor" gem を紹介します。

まずカーソルについて、もう少し詳しく説明したいと思います。

そもそもカーソルって？

カーソルはデータベースからデータを得るする際に、一度にすべてのデータを読み出すのではなく、 ある程度の単位（行数）に分けて読み出すための仕組みです。イテレータのように動作することで、 アプリケーション側のメモリの枯渇を防ぐことができます。

PostgreSQLやMySQLなどの、一般的なRDBMSにはカーソル機能が備わっていて、すぐに使うことができます。 もちろんRedshiftにもあります。それぞれのRDBMSのカーソルについては以下を参照してください。

以下は、PostgreSQLで、カーソルを使って大きな結果から10行ずつ読む例です。

begin ; -- カーソルはトランザクションの中で使う
declare sample_cursor cursor for -- カーソルを宣言
    select title
    from recipes
    where title like '%トマト%' ;

fetch 10 from sample_cursor ; -- 最初の10件を得る
fetch 10 from sample_cursor ; -- 次の10件を得る
-- 必要なだけ繰り返す

close sample_cursor ; -- カーソルを閉じる
commit ;

Railsにおけるカーソル的な処理

大量のクエリ結果を少しずつ取り出して処理を行う場合、 Railsだと ActiveRecord::Batches の .find_each や .find_in_batches を利用する方も多いのではないでしょうか。 .find_each および .find_in_batches の詳しい説明は割愛しますが、 これらのメソッドを使う際は、以下の点で注意が必要です。

  • ソートカラムが指定できない（プライマリキー（大抵 id カラム）でソートされる）
    • 特にログ系のテーブルだと id カラムが付いてなかったり、そもそもプライマリーキーが設定されていないこともある
    • また、日付カラムがソートキーとなっている可能性が高く、意図しないキーで大量の行をソートしてしまうおそれがある
  • プライマリキーが必ず数値型である必要がある
  • チャンク毎にクエリが何度も再実行される
    • カーソルはクエリを一度だけ実行し、結果をチャンクに分けて返す

ソートカラムが明示的に指定されていない状態で、チャンク毎にクエリが再実行されると、 得られた結果が正しくない可能性も考えられます。 またチャンク数分、同じようなクエリが発行されるので非効率とも言えます。 以上のことから、カーソルを利用する方がパフォーマンスや信頼性の面で良いと言えます。

redshift_cursor

さて、カーソルについて簡単に説明しましたが、ここからが本題です。 今回紹介するredshift_cursor gemは、 Rails（ActiveRecord）でRedshiftに接続して大量の行を得るような場合に、 カーソルの構文を覚えなくても、カーソルを透過的に利用できるようにするgemです。 redshift_cursorは実際にクックパッドの一部のバッチジョブで、ログの集計やユーザーの抽出に利用されています。

以下、この gem の使い方を簡単に説明します。

まず Gemfile に記述して bundle install します。

# Gemfile
gem 'redshift_cursor'

すると、各モデルで.each_row, .each_instance, .each_row_by_sql, .each_instance_by_sql などのメソッドが使えるようになります。 .each_row, .each_row_by_sql は結果をハッシュの配列で、.each_instance, .each_instance_by_sql は結果をレシーバークラスのインスタンスの配列で返します。

Recipe.where(id: 3199605).each_row.fitst
=> {"id"=>"3199605", "title"=> "簡単 生地なし！キヌアキッシュ", ... }

Recipe.where(id: 3199605).each_instance.first
=> #<Recipe:0x007fe5260eeaa8 id: 3199605, title: "簡単 生地なし！キヌアキッシュ", ...>

これらのメソッドは Enumerable を返すので、結果に対して .map や .each など使うこともできます。 以下、利用例です。

# タイトルが「ほうれん草」にマッチするようなレシピ
Recipe.where('title like ?', '%ほうれん草%').each_insntace.map {|recipe| recipe.title }

# 必要なカラムがタイトルだけなら
Recipe.where('title like ?', '%トマト%').select(:title).each_row.map {|recipe| recipe['title'] ... }

# 条件や順序をSQLで書く
Recipe.each_instance_by_sql('select * from recipe where ... order ...').map {|recipe| recipe.created_at }

# ヒアドキュメントでSQLを書く
SearchLog.each_row_by_sql(<<~SQL
    select
        title
        , count(*) as pv
    from
        search_logs
    where
        keyword like '%ズッキーニ%'
        and log_time between ...
    group by
        title
SQL
).each {|log| log['pv'] ... }

（.each_row_by_sql, .each_instance_by_sql では Array Condition が使えないことに注意）

上記のコードは、それぞれカーソルを使ったクエリに書き換えられ、 利用者はカーソルの構文や仕組みを新たに覚えなくても、大量のデータを効率的に扱うことができます。

実装

実はredshift_cursorの大部分は、postgresql_cursor gemを利用しています。 redshift_cursorは、activeRecord4-redshift-adapterを使ってRailsからRedshiftに接続している時に、postgresql_cursorが正しく使えるように互換性を追加しています。

まとめ

本稿では、バッチ処理等で大量のデータを読み込む際にアプリケーション側の負荷やパフォーマンスを改善する カーソルについて説明しました。

またRails（ActiveRecord）を使って書かれたAmazon Redshiftを利用するようなバッチ処理において、 カーソル機能を簡単に利用できる "redshift_cursor" gem を紹介しました。 「postgresql_cursor」というよく出来たPostgreSQL向けのgemをRedshiftでも使えるようにしたgemです。

Gemfileに追加して、ActiveRecordのメソッドとよく似たメソッドで、 カーソルを使ったクエリを簡単に発行することができます。 ぜひログなどの大量のデータをバッチ処理するときにご活用ください。

追記（2016/07/12）

（ブコメでもご指摘いただいておりますが、）10万行を超えるような結果を読み出すならば、 カーソルではなく UNLOAD コマンドを使いましょう。

社内でもこのポリシーで運用しています。