dplyrで書けるRedshiftSQL!


この記事はリクルートライフスタイル Advent Calendar 2016の24日目の記事です。

データエンジニアリンググループ・ゆるふわAwesome機械学習エンジニアのtomomotoです。弊社はリア充が多く、24日だけカレンダーが空いている状態だったので、24日12時53分現在から数少ないリア充ではないエンジニアとしてQiita記事を書き始めています。

本記事では、JapanR2016で発表したLTネタのdplyrで書けるRedshiftSQLについて書きたいと思います。(新しいネタ考える時間なんてありません!)

dplyrで書けるRedshiftSQL

dplyrとは、R言語のライブラリで、データ処理を簡単にパイプ(%/%)を使って書くことができる神ライブラリです。実はこの神ライブラリをつかって、SQLを書かなくても直接RedshiftにSQLを投げることができるのです!イメージは下記の感じです。

使い方

コード書き方はこうです。ね、簡単でしょ?

connect_rs.R
library(dplyr)
library(RPostgreSQL)
myRedshift <- src_postgres(
 dbname="hoge",
    host = "XXX.XXX.XXX.XXX",
    port = XXX,
    user = "hoge_hoge",
    password = "hoge_hoge_hoge",
    options="-c search_path=hoge_schema_name")

rs_table <- tbl(myRedshift, from ="hoge_table")

rs_table %>% dplyr::filter(hoge_column = "abcd")

どんなSQLに変換されているのか?

では実際dplyrのコードがどのように変換されているのか調べてみましょう!


rs_table
SELECT * FROM hoge_table

rs_table %>% 
select(a)
SELECT a AS a FROM hoge_table

rs_table %>%
select(starts_with("a"))
SELECT a AS a, a_1 AS a_1
FROM hoge_table

rs_table %>%
filter(hoge_no="33")
SELECT * FROM hoge_table
WHERE (hoge_no = '33') 

rs_table %>%
head(3)
SELECT * FROM hoge_table
LIMIT 3 

rs_table %>%
select(a) %>%
head(3) 
SELECT * FROM
(SELECT a AS a FROM hoge_table) xauvngdbsq
LIMIT 3 

rs_table %>%
  group_by(a) %>%
  summarise(cnt=length(a)) 
SELECT a, LENGTH(a) AS cnt
FROM hoge_table
GROUP BY a 

rs_table %>%
  group_by(a) %>%
  summarise(cnt=length(a)) %>%
  arrange(desc(cnt)) 
SELECT * FROM
(SELECT a, LENGTH(a) AS cnt FROM hoge_table GROUP BY a)
hjvzhxrrld
ORDER BY cnt DESC

innerjoin(rs_table, rs_table, by=a)
SELECT * FROM (SELECT a AS a, ..... FROM hoge_table) uqhggwfohi
INNER JOIN
SELECT * FROM (SELECT a AS a, ..... FROM hoge_table) npoulssyev
USING (a)

rs_table %>%
mutate(row_number=row_number(desc(a)))
SELECT a, b, ...,
row_number() OVER (ORDER BY a DESC) AS row_number
FROM hoge_table

rs_table %>%
group_by(b) %>%
mutate(row_number=row_number(desc(a)))
SELECT a, b, ...,
row_number() OVER (PARTITION BY b ORDER BY a DESC) AS row_number
FROM hoge_table

まとめ

  • dplyrやっぱり神!(記述量減る)
  • データを減らす処理をなるべく手前に(サブクエリで分割してしまうので・・・、いけてないですね)
  • 関数系はRedshiftならだいたい担保(してそう)
  • Join処理のSQLは、結構雑。自分で書いた方がいいかもしれない。

最後に

現在13:11!意外と時間かからず書けるものですね。みなさんは事前に書いておいてください。明日は、私が前もって準備しておいた記事です。