RからAmazon Redshiftに接続する

Amazon Redshiftは、高速でフルマネージドでスケーラブルにペタバイトのデータを扱えるデータウェアハウス(DWH)です。AWSのお客様は、分析処理の一部をオフロードするため、もしくは完全にクラウドでDWHを管理するために、Amazon Redshfitに大量の構造データを移しています。ビジネスインテリジェンス/分析のチームは、InfomaticaやTableauといったお気に入りのツールからJDBCやODBCで接続してデータのインポート、読み込み、分析を行えます。

 

Rはオープンソースのプログラミング言語であり、統計計算、可視化、データ分析のソフトウェア環境として設計されています。柔軟なパケージシステムと強力な統計エンジンにより、Rは大量のデータを管理・処理する手法とテクノロジーをもたらします。Rは世界で最も速く成長している分析プラットフォームであり、そのロバストネス性、信頼性、正確性は、アカデミアとビジネスのどちらでも定評があります。AWSにRをインストールして利用するTipsについては、AWS上でRを実行するを参照ください。

 

本記事では、統計ソフトウェアのRを皆さんのコンピュータやAmazon EC2上で実行して、Amazon Redshiftにあるデータを効率的に分析するベストプラクティスを紹介します。

 

準備

 

2ノードのdc1.largeで構成されるAmazon Redshiftクラスターを起動し(ステップ2:Amazon Redshiftサンプルクラスターの起動)、Publicly Accessibleのフィールドを Yes にしてパブリックIPをクラスタに付与してください。Amazon Redshiftの本番用クラスタを起動しているなら、このオプションは選択しないでしょう。利用可能なセキュリティのメカニズムについての議論は、別のブログポストに分けておきたいと思います。それまでは、セキュリティ、VPC、データ暗号化については、Amazon Redshiftドキュメント(Amazon Redshiftのセキュリティの概要)を参照ください

クラスタを使うには、以下の接続情報が必要です：

• Endpoint <ENDPOINT>
• Database name <DBNAME>
• Port <PORT>
• (Master) username <USER> と password <PW>
• JDBC URL <JDBCURL>

 

AWS ConsoleにログインしAmazon Redshiftを選択し、該当のクラスタを選択することで、これらのフィールドにアクセスできます。

 

サンプルのデータセット

Amazon Redshift内のデータを分析するRの計算性能と使いやすさをデモンストレーションするために、この記事では例として“Airline on-time performance” (http://stat-computing.org/dataexpo/2009/)を使います。このデータセットには、1987年10月から2008年4月までの米国内の全ての民間飛行機の離着陸の詳細を含んでいます。全部で約1億2000万レコードで、圧縮して1.6GB、非圧縮で12GBのサイズです。

データを Amazon Redshiftクラスタと同じリージョンのAmazon S3バケットにコピーし、以下のSQLコマンドでテーブルを作成しデータをロードします。

CREATE TABLE flights(
year integer   encode lzo,
month integer   encode lzo,
dayofmonth integer   encode delta DISTKEY,
dayofweek integer   encode delta,
deptime integer   encode delta32k,
crsdeptime integer   encode lzo,
arrtime integer   encode delta32k, 
crsarrtime integer   encode lzo,
uniquecarrier varchar(10)   encode lzo,
flightnum integer   encode lzo,
tailnum varchar(10)   encode lzo, 
actualelapsedtime integer   encode bytedict,
crselapsedtime integer   encode bytedict,
airtime varchar(5)   encode bytedict,
arrdelay integer   encode bytedict,
depdelay integer   encode bytedict, 
origin varchar(5)   encode RAW,
dest varchar(5)   encode lzo,
distance integer   encode lzo,
taxiin varchar(5)   encode bytedict,
taxiout varchar(5)   encode bytedict,
cancelled integer   encode lzo,
cancellationcode varchar(5)   encode lzo,
diverted integer   encode lzo,
carrierdelay varchar(5)   encode lzo,
weatherdelay varchar(5)   encode lzo,
nasdelay varchar(5)   encode lzo,
securitydelay varchar(5)   encode lzo,
lateaircraftdelay varchar(5)   encode lzo 
)
SORTKEY(origin,uniquecarrier);

COPY flights FROM 's3://data-airline-performance/' credentials 'aws_access_key_id=XXX;aws_secret_access_key=XXX
CSV DELIMITER ','
NULL 'NA'
ACCEPTINVCHARS
IGNOREHEADER 1;

SELECT count(*) FROM flights; /* 123.534.969 */

これらのステップでは、SQLWorkbench/JでAmazon Redshiftクラスタに接続することをおすすめします(SQL Workbench/Jを使用するクラスタに接続する)。ロード処理を高速化するために、COPYコマンドはAmazon S3にあるデータファイルを、並列にロードしている点にご留意ください。

R環境

Rコマンドを実行する前に、Rセッションをどこで実行するかを決める必要があります。Amazon Redshiftの設定によって、いくつかの選択肢があります：

 

Amazon Redshiftのインストール情報の詳細は、IT部門やデータベース管理者が教えてくれるでしょう。

AWS上でR実行するで説明したように、Amazon Linux AMIを使ったAmazon EC2インスタンス上でRを実行することをお薦めします。EC2インスタンスはAmazon Redshiftクラスタの近くにあり、JDBCコネクションのレイテンシを削減できます。

 

RJDBCを使ってAmazon Redshiftに接続する

Rセッションを起動してAmazon Redshiftにデータをロードしたらすぐに、接続できます。推奨する接続方法は、クライアントアプリケーションを使うか、PostgreSQL ODBC/JDBCドライバ経由でSQL文を実行する方法です。

Rでは、JDBCドライバをロードしてSQLクエリをAmazon Redshiftに送る方法として、RJDBCパッケージをインストールできます。RJDBCは適合するJDBCドライバを必要とします。AWSが提供しているJDBCドライバを選択してください(JDBC接続を設定する)。このドライバはPostgreSQL JDBCドライバをベースとしていますが、性能とメモリ管理を最適化しています。

install.packages("RJDBC")
library(RJDBC)

# download Amazon Redshift JDBC driver
download.file('http://s3.amazonaws.com/redshift-downloads/drivers/RedshiftJDBC41-1.1.6.1006.jar','RedshiftJDBC41-1.1.6.1006.jar')

# connect to Amazon Redshift
driver <- JDBC("com.amazon.redshift.jdbc41.Driver", "RedshiftJDBC41-1.1.6.1006.jar", identifier.quote="`")
# url <- "<JDBCURL>:<PORT>/<DBNAME>?user=<USER>&password=<PW>
url <- "jdbc:redshift://demo.ckffhmu2rolb.eu-west-1.redshift.amazonaws.com:5439/demo?user=XXX&password=XXX"
conn <- dbConnect(driver, url)

 

このURLは、AWSコンソールで表示される JDBC URLとユーザ、パスワードを引数として組み合わせたものです。クラスタに接続すると、SQLコマンドを実行してデータベースの情報を取得したり、別のSQLクエリでデータにアクセスできます。

# list tables
dbGetTables(conn)
dbGetQuery(conn, "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'")

# get some data from the Redshift table
dbGetQuery(conn, "select count(*) from flights")

# this is not a good idea – the table has more than 100 Mio. rows”
# fligths <- dbReadTable(conn, "flights")

# close connection
dbDisconnect(conn)

 

RJDBCパッケージは最も柔軟で最高の性能を提供します。Amazon Redshfitへの全てのSQLクエリをこのパッケージ経由で実行すべきです。RPostgreSQLやhttps://github.com/pingles/redshift-r は動作しますがAmazon Redshiftに最適化されたJDBCドライバを使っていません。大きなテーブルへSQLクエリを実行するような特殊な場合で、性能劣化するかもしれません。GithubにあるRedshiftRDBA はAmazon Redshiftにあるデータに対してクエリするものではなく、データベース管理者が Amazon Redshiftのシステムテーブルを分析し可視化しやすくするための便利なR関数です。

 

dplyr パッケージを使った効率的な分析

 dplyr パッケージは、メモリ内とデータベース上のオブジェクトの両方のデータフレームを扱う、高速で一貫したRパッケージです。Rセッションに全データをコピーしないようにでき、必要な量をAmazon Redshiftから直接ロードできます。このパッケージは多くのデータベースシステムに接続できます。RPostgreSQLパッケージ経由でAmazon Redshiftに接続する必要があります。

まず、Amazon Redshiftクラスタに接続します。

# now run analyses with the dplyr package on Amazon Redshift
install.packages("dplyr")
library(dplyr)
library(RPostgreSQL)
#myRedshift <- src_postgres("<DBNAME>",
#       host = "<ENDPOINT>,
#       port = <PORT>,
#       user = "<USER<",
#       password = "<PW>")
myRedshift <- src_postgres('demo',
host = 'redshiftdemo.ckffhmu2rolb.eu-west-1.redshift.amazonaws.com',
port = 5439,
user = "markus", 
password = "XXX")

その後、tbl()関数を使ってテーブル参照を作成します。 Amazon Redshiftクラスタ内のテーブルへの参照を示すRオブジェクトを作りますが、データはRメモリにロードしません。このRオブジェクトに対してR関数を実行すると、SQLクエリがバックグラウンドで実行されます。クエリ結果だけがRセッションにコピーされます。

# create table reference
flights <- tbl(myRedshift, "flights")

# simple and default R commands analyzing data frames
dim(flights)
colnames(flights)
head(flights)

#the summarize command reduces grouped data to a single row.
summarize(flights, avgdelay=mean(arrdelay))
summarize(flights, avgdelay=max(arrdelay))

 

Rに直接データをロードせずに、Rを使ってAmazon Redshift上で素晴らしい分析を実行できます。dplyrの欠点として、複数のデータを組み合わせて操作する時には、データを完全に読み込む必要があります。多くの場合、R関数の引数は、関数呼び出しから遠いです。 %>%演算子は dplyr関数の代替方法で、右から左にデータを読み込み、直感的に使えます。

 

では、高度な分析をしてみましょう。60分以上のフライトに興味があるとして、以下の例を使います：

 

flights %>%
filter(depdelay-arrdelay>60) %>%
select(tailnum, depdelay, arrdelay, dest)

数秒で結果を得ることができます。Amazon Redshiftのクエリ一覧を確認すると、いくつかのSQLコマンドが実行されたことを確認できます。

 

月毎や行き先毎で集計したフライトの遅延情報に興味があるかも知れません。新しい”res”オブジェクトにdplyr 操作を保存しても、Amazon Redshiftへのクエリは実行されていないことに留意してください。”res”オブジェクトを使う時に、クエリが実行されます。この方法で、Amazon Redshiftクラスタの実行時間と処理負荷を削減できます。

res <- flights %>%
group_by(month, origin) %>%
summarize(delay=mean(arrdelay))

dres <- as.data.frame(res)
ggplot(aes(month, delay, fill=origin), data=dres) + geom_bar(stat="identity", position="dodge")

 

“Airline on-time performance”から、いくつかの素晴らしい結果を見つけました。21年間の全期間に対して、月ごとの平均遅延時間を可視化したところ、3つの大きな空港(JFK, ORD, PHL)だけが表示されました。米国の大きな空港での1ヶ月の到着遅延は非常に似ており、地域による影響はとても低い事を意味します。しかも、冬に遅延が多いと予想しましたが、実際には、6月,7月,8月が最も遅延していました。

Amazon Redshift データベース管理者向けTips

R経由でAmazon Redshiftに接続するデータサイエンティスト用のクエリキューを別に定義しておくことは良い考えです(クエリキューの定義)。別々のキューにすることで、長時間のR SQLクエリが、本番用クエリの実行時間に影響しないようにできます。

さらに、Githubで公開しているAmazon Redshift Utils  (https://github.com/awslabs/amazon-redshift-utils)は、Amazon Redshiftから最高の性能を引き出すために管理者を支援するスクリプトとユーティリティを提供します。“perf_alter.sql”スクリプトはRクエリに関係する性能アラートを監視するのに有用でしょう。

 

この記事中のデモコードは、Amazon Redshiftを起動する際に作られたスーパーユーザーを使いました。セキュリティやデータ保護の観点から、全てのR開発者用のAmazon Redshiftユーザを個別に作り、必要な操作とアクセスするテーブルに基づいて権限を付与することをお勧めします(データベースセキュリティの管理）。

 

まとめ

統計ソフトウェアRは、マネージドされ高可用性がありスケーラブルなAmazon Redshiftデータウエアハウスにあるデータに対して高度な分析を行うことを可能にします。 RJDBC パッケージはSQLコマンドによる高速で効率的なアクセスを可能にし、dplyr パッケージはSQLの知識不要で効率的な分析を可能にします。

 

質問やご提案があれば、コメントをお願いします。

 

(翻訳: 松尾康博 原文:Connecting R with Amazon Redshift )

------------------------------------

関連記事:

AWS上でRを実行する