![? Opt, Inc. All Rights Reserved.
CREATE EXTENSION postgres_fdw;
CREATE EXTENSION dblink;
CREATE SERVER [外部サーバ名]
FOREIGN DATA WRAPPER postgres_fdw OPTIONS (
host 'xxx.xxx.xxx.xxx', -- Redshiftのホスト
port 'nnnn', -- Redshiftのポート
dbname 'xxxx' -- Redshiftのデータベース
);
CREATE USER MAPPING FOR [ユーザ名]
SERVER [外部サーバ名] OPTIONS (
user 'user_id', -- Redshiftの接続ユーザ
password 'user_pswd' -- Redshiftの接続ユーザパスワード
);](https://image.slidesharecdn.com/random-171110093630/85/-47-320.jpg)
![? Opt, Inc. All Rights Reserved.
INSERT INTO [Postgresqlテーブル名]
SELECT *
FROM dblink('[外部サーバ名]',$REDSHIFT$
SELECT account_id,campaign_id,day,SUM(cost)
FROM [Redshiftテーブル名]
GROUP BY account_id,campaign_id,day
$REDSHIFT$
) AS t1 (
ac_id VARCHAR(255),cp_id BIGINT,rp_date DATE,cost
BIGINT);](https://image.slidesharecdn.com/random-171110093630/85/-49-320.jpg)
![? Opt, Inc. All Rights Reserved.
INSERT INTO [Postgresqlテーブル名]
SELECT *
FROM dblink('[外部サーバ名]',$REDSHIFT$
SELECT account_id,campaign_id,day,SUM(cost)
FROM [Redshiftテーブル名]
GROUP BY account_id,campaign_id,day
$REDSHIFT$
) AS t1 (
ac_id VARCHAR(255),cp_id BIGINT,rp_date DATE,cost BIGINT);$REDSHIFT$ で囲まれた文字列は、Redshiftに送られるクエリ
CREATE SERVERの名前と合わせる](https://image.slidesharecdn.com/random-171110093630/85/-50-320.jpg)
![? Opt, Inc. All Rights Reserved.
INSERT INTO [Postgresqlテーブル名]
SELECT *
FROM dblink('[外部サーバ名]',$REDSHIFT$
SELECT account_id,campaign_id,day,SUM(cost)
FROM [Redshiftテーブル名]
GROUP BY account_id,campaign_id,day
$REDSHIFT$
) AS t1 (
ac_id VARCHAR(255),cp_id BIGINT,rp_date
DATE,cost BIGINT);
Redshiftで実行されたクエリの結果セット（データ型など）を指定](https://image.slidesharecdn.com/random-171110093630/85/-51-320.jpg)
![? Opt, Inc. All Rights Reserved.
INSERT INTO [Postgresqlテーブル名]
SELECT *
FROM dblink('[外部サーバ名]',$REDSHIFT$
SELECT account_id,campaign_id,day,SUM(cost)
FROM [Redshiftテーブル名]
GROUP BY account_id,campaign_id,day
$REDSHIFT$
) AS t1 (
ac_id VARCHAR(255),cp_id BIGINT,rp_date DATE,cost
BIGINT);](https://image.slidesharecdn.com/random-171110093630/85/-52-320.jpg)
データマート対応した话
- 1. ? Opt, Inc. All Rights Reserved. データマート対応した话 株式会社オプト 仙台テクノロジー開発部 第2回タガヤス登壇資料
- 2. ? Opt, Inc. All Rights Reserved. 自己紹介 - 佐藤宏（不惑） - 株式会社オプト 仙台テクノロジー開発部 所属 - 2011年入社 - シニアエンジニア
- 3. ? Opt, Inc. All Rights Reserved. 不惑って（いきなり余談） 子曰、 吾十有五而志于学、 三十而立、 四十而不惑、 五十而知天命、 六十而耳順、 七十而従心所欲、不踰矩。
- 4. ? Opt, Inc. All Rights Reserved. Amazon Redshift 始めました（2016年） ※dc2.8xlargeを前提
- 5. ? Opt, Inc. All Rights Reserved. Redshift が向いている利用シーン 1. 大量データを扱う 2. 分散処理技術に詳しいインフラエンジニアがいな い?少ない
- 6. ? Opt, Inc. All Rights Reserved. Redshift が向いている利用シーン(1) - 大量データを扱う - 大量データを短時間で登録 - 大量データを分析するクエリが実行可 - テーブルへカラム追加が容易
- 7. ? Opt, Inc. All Rights Reserved. Redshift が向いている利用シーン(2) - 分散処理技術に詳しいインフラエンジニアがいな い?少ない - フルマネージド
- 8. ? Opt, Inc. All Rights Reserved. Redshift って凄い 思っている事が何でもできそう
- 9. ? Opt, Inc. All Rights Reserved. 魔法のシステムではない 得意?不得意がある
- 10. ? Opt, Inc. All Rights Reserved. Redshift の向いている処理って？
- 11. ? Opt, Inc. All Rights Reserved. 得意な事 - オンライン分析処理（OLAP） - 大量のデータを素早く集計する - 大量のデータを容易に取り込める（from S3）
- 12. ? Opt, Inc. All Rights Reserved. 不得意な事 - オンライントランザクション処理（OLTP） - オンラインサービスのバックエンドには向かない - 複雑なロジックを埋め込んだクエリ(経験則)
- 13. ? Opt, Inc. All Rights Reserved. Redshift の特徴が 分かったところで
- 14. ? Opt, Inc. All Rights Reserved. 素早いレスポンス速度とは？ あなたが感じる「速さ」とは...
- 15. ? Opt, Inc. All Rights Reserved. シンキングタイム...
- 16. ? Opt, Inc. All Rights Reserved. 例えば - 検索サイトでの応答速度 - ECサイトやSNSの画面表示 - 100MByteのファイルのDL時間
- 17. ? Opt, Inc. All Rights Reserved. 速度って - 速度だけだと抽象的な言葉 - 想定シーンで異なる - 個人の主観でも異なる
- 18. ? Opt, Inc. All Rights Reserved. 抽象的なままだとどうなるか - 主観が入り込む - 想定が異なる - 認識相違が生じやすい
- 19. ? Opt, Inc. All Rights Reserved. 例えば - 数千万単位のデータから集計するクエリを秒単位 で応答を返している
- 20. ? Opt, Inc. All Rights Reserved. 開発者の視点 - 通常のRDBだとこんなレスポンスはでない - めっちゃ速い
- 21. ? Opt, Inc. All Rights Reserved. 利用者の視点 - Webシステムで1,2秒レスポンスにかかったら遅 い - 裏側の仕組みなんて知ったことではない
- 22. ? Opt, Inc. All Rights Reserved. 立場が違うから仕方ない？
- 23. ? Opt, Inc. All Rights Reserved. 非機能要件 - 性能や信頼性，拡張性，セキュリティなど，機能 要件以外 - 具体的な数値を提示する
- 24. ? Opt, Inc. All Rights Reserved. 例えば 機能Xは、応答に10秒かかる
- 25. ? Opt, Inc. All Rights Reserved. 機能Xに10秒 1. 数百MByteのファイルをダウンロードする機能 2. 仙台の日ごと（1か月分）の最高気温が表示され る機能 - 結果は31件程度のデータ - 日本全国の観測地点の1か月分データ
- 26. ? Opt, Inc. All Rights Reserved. Redshiftの得意な事 - 大量のデータを扱うのは得意 - 集計したデータを別テーブルに用意しておけば、 さらに早くなるのでは？
- 27. ? Opt, Inc. All Rights Reserved. Redshiftの苦手な事 - 少量のデータでも時間がかかる - 1秒弱くらい - 内部的な処理でオーバーヘッドがかかる
- 28. ? Opt, Inc. All Rights Reserved. そもそも少量データならRedshift じゃない方がいい
- 29. ? Opt, Inc. All Rights Reserved. データマートを作ろう
- 30. ? Opt, Inc. All Rights Reserved. その前に…
- 31. ? Opt, Inc. All Rights Reserved. インターネットの広告って？
- 32. ? Opt, Inc. All Rights Reserved. こことか ここも これは、 ディスプレイ広告
- 33. ? Opt, Inc. All Rights Reserved. こことか キーワード広告。 サーチ、 リスティング広告 といいます
- 34. ? Opt, Inc. All Rights Reserved. Yahoo! 広告A 広告B 広告C Google 広告A 広告B 広告C Facebook 広告A 広告B 広告C Twitter 広告A 広告B 広告C 佐藤工務店
- 35. ? Opt, Inc. All Rights Reserved. Yahoo! KW1 KW2 KW3 Google KW1 KW2 KW3 Facebook 広告A 広告B 広告C Twitter 広告A 広告B 広告C 佐藤工務店
- 36. ? Opt, Inc. All Rights Reserved. 媒体（アカウント） キャンペーンキャンペーン 広告グループ広告グループ広告グループ KW1 KW2 KW3 広告A 広告B 広告C 広告グループ KW1 KW2 KW3 広告A 広告B 広告C
- 37. ? Opt, Inc. All Rights Reserved. データ種別 1か月分の日別レコード数 アカウント 8万～12万 キャンペーン 20万～60万 広告 600万～1200万 キーワード 1000万～2000万 ※概算のレコード数
- 38. ? Opt, Inc. All Rights Reserved. キャンペーン データマートを作ろう
- 39. ? Opt, Inc. All Rights Reserved. まずはRedshiftから データを出力しよう
- 40. ? Opt, Inc. All Rights Reserved. Redshiftからのデータ移行 - 基本はS3へのエクスポート - S3に出力したデータを他のDBに取り込む
- 41. ? Opt, Inc. All Rights Reserved. もう少しリアルタイムに連携した い
- 42. ? Opt, Inc. All Rights Reserved. dblink
- 43. ? Opt, Inc. All Rights Reserved. dblinkって - 他のデータベースをSQLから直接操作できるモ ジュール - 分散環境で複数のデータベースをまたがる処理 を行うことができる
- 44. ? Opt, Inc. All Rights Reserved. データマートに使うDBは？ - RedshiftはPostgreSQL 8.0.2から派生 - RedshiftがPostgreSQLのtcpプロトコルで接続で きるためdblinkで接続可 - PostgreSQL側からRedshiftへ接続できる - Redshiftのクエリ結果を受け取れる（データを そのまま登録できる）
- 45. ? Opt, Inc. All Rights Reserved. データマートに使うDBは？ - PostgreSQLで確定！！ - Amazon Aurora with PostgreSQL（まだ日本リージョンに は来ていないが）
- 46. ? Opt, Inc. All Rights Reserved. dblinkを使う準備
- 47. ? Opt, Inc. All Rights Reserved. CREATE EXTENSION postgres_fdw; CREATE EXTENSION dblink; CREATE SERVER [外部サーバ名] FOREIGN DATA WRAPPER postgres_fdw OPTIONS ( host 'xxx.xxx.xxx.xxx', -- Redshiftのホスト port 'nnnn', -- Redshiftのポート dbname 'xxxx' -- Redshiftのデータベース ); CREATE USER MAPPING FOR [ユーザ名] SERVER [外部サーバ名] OPTIONS ( user 'user_id', -- Redshiftの接続ユーザ password 'user_pswd' -- Redshiftの接続ユーザパスワード );
- 48. ? Opt, Inc. All Rights Reserved. dblinkを使ったクエリ
- 49. ? Opt, Inc. All Rights Reserved. INSERT INTO [Postgresqlテーブル名] SELECT * FROM dblink('[外部サーバ名]',$REDSHIFT$ SELECT account_id,campaign_id,day,SUM(cost) FROM [Redshiftテーブル名] GROUP BY account_id,campaign_id,day $REDSHIFT$ ) AS t1 ( ac_id VARCHAR(255),cp_id BIGINT,rp_date DATE,cost BIGINT);
- 50. ? Opt, Inc. All Rights Reserved. INSERT INTO [Postgresqlテーブル名] SELECT * FROM dblink('[外部サーバ名]',$REDSHIFT$ SELECT account_id,campaign_id,day,SUM(cost) FROM [Redshiftテーブル名] GROUP BY account_id,campaign_id,day $REDSHIFT$ ) AS t1 ( ac_id VARCHAR(255),cp_id BIGINT,rp_date DATE,cost BIGINT);$REDSHIFT$ で囲まれた文字列は、Redshiftに送られるクエリ CREATE SERVERの名前と合わせる
- 51. ? Opt, Inc. All Rights Reserved. INSERT INTO [Postgresqlテーブル名] SELECT * FROM dblink('[外部サーバ名]',$REDSHIFT$ SELECT account_id,campaign_id,day,SUM(cost) FROM [Redshiftテーブル名] GROUP BY account_id,campaign_id,day $REDSHIFT$ ) AS t1 ( ac_id VARCHAR(255),cp_id BIGINT,rp_date DATE,cost BIGINT); Redshiftで実行されたクエリの結果セット（データ型など）を指定
- 52. ? Opt, Inc. All Rights Reserved. INSERT INTO [Postgresqlテーブル名] SELECT * FROM dblink('[外部サーバ名]',$REDSHIFT$ SELECT account_id,campaign_id,day,SUM(cost) FROM [Redshiftテーブル名] GROUP BY account_id,campaign_id,day $REDSHIFT$ ) AS t1 ( ac_id VARCHAR(255),cp_id BIGINT,rp_date DATE,cost BIGINT);
- 53. ? Opt, Inc. All Rights Reserved. 最後に（まとめ）
- 54. ? Opt, Inc. All Rights Reserved. データマートの導入の前に - よく使われるデータで集計しておく - そもそものデータ量が減る
- 55. ? Opt, Inc. All Rights Reserved. データマートの導入結果 - 応答時間がミリ秒単位 - 小さいクエリを捌く事が得意なDBを使う
- 56. ? Opt, Inc. All Rights Reserved. データマートからの応答速度 - 複数媒体＆複数月のデータから返す場合 48秒(Redshift)→1秒弱(PostgreSQL) - 純粋に1媒体＆1か月のデータから返す場合 1秒(Redshift)→0.1秒(PostgreSQL)
- 57. ? Opt, Inc. All Rights Reserved. データ種別 1か月分の日別レコード数 アカウント 8万～12万 キャンペーン 20万～60万 広告グループ 300万～500万 広告 600万～1200万 キーワード 1000万～2000万 ※概算のレコード数
- 58. ? Opt, Inc. All Rights Reserved. 最後に - 大量のデータをデータマートに持ってきてもレスポ ンスは出ない
- 59. ? Opt, Inc. All Rights Reserved. ご清聴ありがとうございました