Data at FanDuel

How we use AWS’s ElasticMapReduce, and Redshift at FanDuel.

Transcript

1. Data at FanDuel AWS User Group Scotland, September 2014 1
2. John Sutherland @sneeu 2
4. EMR, Redshift & SQL 4
10. What was the original problem you were trying to solve? 10 “
11. SELECT `user_id`, `sport`, TO_DATE(`date`) AS `d`, SUM(IF(`fee` > 0, 0, 1)) AS `free_plays`, SUM(IF(`fee` > 0, 1, 0)) AS `paid_plays` FROM `entry` GROUP BY `user_id`, `sport`, `d` ; 11
12. Indexing 12
13. Full–table scan 13
14. MapReduce 14
15. Hadoop 15
16. Why AWS? 16
17. Why EMR? 17
18. ≈0 18
20. Why Redshift? 20
21. PostgreSQL–like 21
22. Columnar 22
23. 23 id email email_confirmed 1 john@fanduel.com TRUE 2 pete@example.com TRUE 3 lucy@example.org TRUE 4 ro@example.co.uk TRUE 5 jim@example.biz FALSE 6 gill@example.fr FALSE
24. Row–orientated 1,john@fanduel.com,TRUE\n↩ 2,pete@example.com,TRUE\n↩ 3,lucy@example.org,TRUE\n↩ 4,ro@example.co.uk,TRUE\n↩ 5,jim@example.biz,FALSE\n↩ 6,gill@example.fr,FALSE 24 Columnar 1,2,3,4,5,6 ! john@fanduel.com,pete@example.com,↩ lucy@example.org,ro@example.co.uk,↩ jim@example.biz,gill@example.fr ! TRUE,TRUE,TRUE,TRUE,FALSE,FALSE
25. 25 • Rows stored sequentially on disk • Find disk location from index • Seek to location, retrieve full row Row–orientated Columnar • Columns stored separately on disk • Read full column • Compression (run–length) is easier with columns of a single type
26. 26 SELECT * FROM user WHERE id = 21803; Row–orientated Columnar SELECT AVG(amount) FROM deposits WHERE completed = today;
27. How? 27
29. sqoop Bulk data transfer between RDBMSs & Hadoop 29
30. 30 SELECT MIN(c), MAX(c) FROM user; ! SELECT MIN(c), MAX(c) FROM user WHERE c > previous_max_c; ! SELECT MIN(c), MAX(c) FROM user WHERE updated_at > previous_max_updated_at;
31. Generate n MapReduce jobs 31
32. Partitioned “id–space” 32
33. Hive Write SQL; generate MapReduce jobs 33
34. SELECT COUNT(*) FROM user WHERE date_created > '2014-01-01'; 34
35. hive> SELECT COUNT(*) FROM action_log; Total MapReduce jobs = 1 Launching Job 1 out of 1 Number of reduce tasks determined at compile time: 1 In order to change the average load for a reducer (in bytes): set hive.exec.reducers.bytes.per.reducer=<number> In order to limit the maximum number of reducers: set hive.exec.reducers.max=<number> In order to set a constant number of reducers: set mapred.reduce.tasks=<number> Starting Job = job_1410783831919_0150, Tracking URL = http://172.18.0.152:9046/ proxy/application_1410783831919_0150/ Kill Command = /home/hadoop/bin/hadoop job -kill job_1410783831919_0150 Hadoop job information for Stage-1: number of mappers: 17; number of reducers: 1 2014-09-23 16:42:12,527 Stage-1 map = 0%, reduce = 0% 2014-09-23 16:42:16,648 Stage-1 map = 47%, reduce = 0%, Cumulative CPU 19.14 sec 2014-09-23 16:42:17,746 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 51.65 sec 2014-09-23 16:42:18,783 Stage-1 map = 100%, reduce = 0%, Cumulative CPU 51.65 sec 2014-09-23 16:42:19,818 Stage-1 map = 100%, reduce = 100%, Cumulative CPU 53.22 sec MapReduce Total cumulative CPU time: 53 seconds 220 msec Ended Job = job_1410783831919_0150 Counters: MapReduce Jobs Launched: Job 0: Map: 17 Reduce: 1 Cumulative CPU: 53.22 sec HDFS Read: 4459072001 HDFS Write: 8 SUCCESS Total MapReduce CPU Time Spent: 53 seconds 220 msec OK Lots Time taken: 20.462 seconds, Fetched: 1 row(s) 35
36. Impala Faster than Hive 36
37. Pig Write PigLatin; generate MapReduce jobs 37
38. PigPen Write Clojure; generate MapReduce jobs 38
39. S3, COPY 39
40. COPY "user_tmp" FROM 's3://bucket/user'; DROP TABLE "user"; ALTER TABLE "user_tmp" RENAME TO "user"; 40
41. Python 41
42. Fabric 42
43. DAG 43
44. Use–cases 44
45. Word count 45
46. Marketing 46
47. Log processing 47
48. Affiliate payments 48
49. Other batch jobs 49
50. Ad–hoc reporting 50
52. Django, celery & SQLAlchemy 52
53. Thanks. Questions? 53