BigQuery for DDDD #gcpug / 20171116

社内GCPUGの発表資料です。
refs. http://yuzutas0.hatenablog.com/entry/2018/06/21/083000

Transcript

1. BigQuery for DDDD 2017-11-16
 In-House GCPUG presented by @yuzutas0
 https://www.pexels.com/photo/close-up-of-computer-keyboard-257949/ɹhttps://www.pexels.com/photo/technology-computer-lines-board-50711/ɹhttps://www.pexels.com/photo/black-and-white-business-chart-computer-241544/ 
2.  1. Profile 2. Use Case 3. Benefits for Business 4. Tips to manage Data ɹOutline
3.  1. Profile 2. Use Case 3. Benefits for Business 4. Tips to manage Data ɹOutline
4. ɹ@yuzutas0 ɹɹ 
 
  Certificated Scrum Product Owner
 ɹɹprev; founder at VC-backed company “D4C”: Data Management Team
 ɹɹDating Data Driven Development Center
 Using BigQuery as Team Director
 (not only Software Engineer)
5. ɹWeekend Challenge I created a web app “in one day” with GCP (GAE + Cloud SQL) last Saturday 
6.  1. Profile 2. Use Case 3. Benefits for Business 4. Tips to manage Data ɹOutline
7.  Dating
 Data
 Driven
 Development
8.  Dating
 Data
 Driven
 Development
9.  ɹOnline Dating / Love Tech IUUQTXXXTUBOEBSEDPVLMJGFTUZMFMPOEPOMJGFFWFSZUIJOHZPVOFFEUPLOPXBCPVUVTJOHEBUJOHBQQTJOBIUNM
10. Enmusubi Koimusubi ϓϩμΫτ঺հʢࣾ಺ݶʣ 
11. Monthly Subscriptions
 except for female Koimusubi user ɹWay to use Message Like Match ϓϩμΫτͷ࢖͍ํʢࣾ಺ݶʣ 
12.  Dating
 Data
 Driven
 Development
13.  Dating
 Data
 Driven
 Development
14.  ɹAction in Many Teams Design Security Legal Infrastructure System Management (like SRE) App Arch Marketing Customer Support Data Science Machine Learning Feature Product
 Dev & Ops
 Team A Product
 Dev & Ops
 Team B Product
 Dev & Ops
 Team C PO PO PO Direction Public Relations
15. Revenue, churn rate, DAU…
 
 Daily notificationɹɹɹɹSpreadsheetɹɹɹɹDashboard  ɹɹɹBusiness metrics 1
16. From sign up to graduate (= partner found) FunnelɹɹɹɹɹɹɹɹɹJourney  ɹɹɹCustomer action 2
17. ɹɹ  ɹɹɹMeasure 3 Analysis about
 Split (AB) Testing Utilization of
 New feature
18. ɹɹɹRecommendation 4 Ϩίϝϯυը໘ʢࣾ಺ݶʣ 
19.  ɹɹɹSpam Filter 5
20.  ɹɹɹCustomer Support 6
21.  ɹɹɹAdvertisement 7
22. ɹɹɹPress Release 8 IUUQXXXSFDSVJUNQDPKQOFXTSFMFBTF@IUNM ϓϨεϦϦʔε࣮ྫʢࣾ಺ݶʣ 
23. Crash Rate, Response Time…  ɹɹɹSystem Monitoring 9 IUUQZV[VUBTIBUFOBCMPHDPNFOUSZ
24.  ɹɹɹSystem failure investigation 10
25. Velocity for each sprint, Lead time to complete tickets …  ɹɹɹTeam capacity 11
26.  ɹTeams use “same data store” Data Warehouse
27. like Ribbon  ɹMatching: data source and teams Data Warehouse
28.  ɹArchitecture ࣾ಺ݶ(স)
29.  ɹArchitecture ࣾ಺ݶ(স)
30.  ɹBased on BigQuery ɹɹɹɹɹɹBigQuery - Google Cloud Platform Source Layer Warehouse Layer App Layer
31.  1. Profile 2. Use Case 3. Benefits for Business 4. Tips to manage Data ɹOutline
32. Make everything easier 
 Enable to… • skip build & maintain Infrastructure
 • input & output every type of data
 • export data to another DB whenever we like  ɹWhy BigQuery?
33.  ɹPrice IUUQZBQDBTJBPSHUBMLTIPXDFBGBDFDBCEDB • Advantage of scale • Charge revision
34.  ɹPerformance IUUQZBQDBTJBPSHUBMLTIPXDFBGBDFDBCEDB • Columnar Storage • Parallel disk IO
35.  ɹPsychological safety Account management by GCPɾGsuite
 every member can use SQL Interface - just call query without regard for Infrastructure Standard SQL
 easy to transport
 easy to study
 easy to test Ecosystem
 supported by famous BI tools
 pandas.io.gbq → debuggable by Jupyter
36. 1. Focus on using data
 (not management data) 2. Attention to optimize Cost
 for Dev&Ops  ɹBenefit for Business
37. 1. Focus on using data
 (not management data) 2. Attention to optimize Cost
 for Dev&Ops  ɹBenefit for Business
38. like Ribbon  ɹMatching: data source and teams Data Warehouse
39.  ɹTechnologies & Seeds Oriented ? Data Warehouse
40.  ɹɹ- Dashboard nobody watches
 ɹɹ- Chatbot nobody talks with ɹAnti-Pattern
41.  ɹUsers & Needs Oriented ! Data Warehouse
42.  ɹFocus on using data (not management data)
43. Make data-management easier  ɹBigQuery is one of best solutions
44. 1. Focus on using data
 (not management data) 2. Attention to optimize Cost
 for Dev&Ops  ɹBenefit for Business
45.  ɹNo revenue by itself ɹɹɹɹCost CenterɹɹProfit Center Data Warehouse Value Market
46.  Cost Limit
 in the future IUUQTXXXQFYFMTDPNQIPUPCJMMTDBQJUBMDBTIDFOU
47.  ɹRecession Risk Budget depends on company’s overall sales
 Sales in many business areas depends on macro economy
 
 e.g. Human Resources, Housing, Bridal, Automobiles, Education, Life Style
 (This logic doesn’t apply to companies about utilities and necessities) =IUUQTXXXQFYFMTDPNQIPUPCMBDLBOEXIJUFCVTJOFTTDIBSUDPNQVUFS
48.  ɹTrough of Disillusionment IUUQOFXTNZOBWJKQOFXT
49.  ɹData democratization Data management
 gets to be “staff function”, 
 
 interested in cost reduction 
 (not growth)
50.  IUUQTXXXQFYFMTDPNQIPUPCJMMTDBQJUBMDBTIDFOU Needs for
 Cost Optimization
51. Make data-management easier  ɹBigQuery is one of best solutions
52. 1. Focus on using data
 (not management data) 2. Attention to optimize Cost
 for Dev&Ops  ɹBenefit for Business
53.  1. Profile 2. Use Case 3. Benefits for Business 4. Tips to manage Data ɹOutline
54. pipeline prevents data from
 flowing backward and making itself uncorrectable  ɹ3 layers ɹɹɹɹɹɹBigQuery - Google Cloud Platform Source
 copy from original data Warehouse
 Key Indicators, 
 Intermediate table App
 interface for tools
55. e.g. enmusubi__source__db two under scores `__` split above elements like BEM  ɹnaming rule about dataset Product Name source warehouse app Production DB Apache Log Adobe Analytics
56. Service Level ɹɹɹɹɹ >>>ɹ ɹɹɹ>  ɹPrivacy protection on-premise BigQuery personal information accounting,
 billing info others
57.  ɹPrivacy protection on-premise BigQuery EC2 masking
 view masking user production
 database
58. Design to make retry easier ɹɹɹɹɹ • Job divided
 ɹɹɹɹɹ • Partition divide
 ɹɹɹɹɹ • Records divided  ɹFrequent connection error
59.  ɹType of data; way to transfer 4UBUF
    EBUB &WFOU
    EBUB FYBNQMF MBTU
    TJHOJO
    EBUF
    VQEBUFE TJHOJO
    IJTUPSZ
    DSFBUFE VTBHF EJTQMBZ
    WJFX
    GPS
    DVTUPNFS
 
 zUIJT
    VTFS
    TJHOFE
    JO
    ZFTUFSEBZ BOBMZTJT
    GPS
    QSPWJEFS
 
 IPX
    GSFRVFOU
    VTFST
    TJHO
    JO DIBSBDUFSJTUJDT QFSGPSNBODF
    UVOJOH
 EFOPSNBMJ[BUJPO
    UP
    SFEVDF
    KPJO
    RVFSZ TBNF
    42-
    TBNF
    SFTVMUT
 
 DBOOPU
    TBNF
    SFTVMUT
 JG
    TZTUFN
    VQEBUFT
    XIFO
    VTFST
    TJHOFE
    JO USBOTGFS
    EBUB SFQMBDF
    BMM
    EBUB BEE
    POMZ
    lEJ⒎z
60. product growthɹ → ɹnew feature needs ER changeɹ → ɹBQ follows 
  ɹER Migration
61.  ɹJupyter notebook to debug 1. write script at local Jupyter 2. test; migrate only 1 day data 3. export .py file → deploy IUUQTXXXTIBSFJDPOOFUEPDVNFOUpMFQZ
62. → *needs to convert Python 2.7 • Dataflow; auto-scaled server
 
 • Datalab; paste jupyter codes ɹuse server on GCP ɹ • leased lines
 
 ɹ • same region — U.S.  ɹDeploy to GCP bottle-neck 1. network bottle neck 2. memory
63. We are looking for new tips
 to manage data by BQ, give me advices 
64.  1. Profile 2. Use Case 3. Benefits for Business 4. Tips to manage Data ɹOutline
65. Product
 x
 Technology
 ↓
 Pleasure  IUUQTXXXQFYFMTDPNQIPUPBEVMUTCFBDICFBDIXFEEJOHDPVQMF
66.  IUUQTXXXQFYFMTDPNQIPUPCMBDLBOEXIJUFDPOOFDUFEIBOETMPWF We're hiring part-time job
67. Thank you for
 your kind attention presented by @yuzutas0
 https://www.pexels.com/photo/close-up-of-computer-keyboard-257949/ɹhttps://www.pexels.com/photo/technology-computer-lines-board-50711/ɹhttps://www.pexels.com/photo/black-and-white-business-chart-computer-241544/