ローカルにmetabase環境を構築する時に便利な小話

2020.08.06に
【データ可視化】Metabase Online Meetup
https://metabase.connpass.com/event/181967/
で登壇した内容です。

1. We Make Innova+on Happen. We Find. We Connect. NINESIGMA ϩʔΧϧʹ.FUBCBTFΛ
   ߏங͢Δ࣌ʹศརͳখ࿩ Europe - Asia - North America 
   
   φΠϯγάϚɹࡾӜࠀߒ
2. © Copyright, NineSigma φΠϯγάϚͷ͝঺հ Ϗδϣϯ 2 ۀ຿಺༰ اۀͷݚڀ։ൃɺ৽نࣄۀ૑ग़ΛՃ଎ͤ͞Δ ٕज़ɺΞΠσΞɺύʔτφʔͷ঺հΛ௨ͨ͡ ΦʔϓϯΠϊϕʔγϣϯࢧԉ ΦʔϓϯΠϊϕʔγϣϯࢧԉͷضख ੈք800ࣾҎ্ɺࠃ಺280ࣾҎ্ͷސ٬ ▪ࠃ಺ݚڀ։ൃඅτοϓ100ࣾத80ࣾ ▪ࣗಈंɺΤϨΫτϩχΫεɺICTɺػցɺࡐྉɺ ੡ༀɺҩྍػثɺ৯඼ɺΠϯϑϥɺిྗɾΨε ੈք໿6,000݅ɺࠃ಺໿1,400݅ͷϚονϯάࢧԉ ࣮੷ ▪͋ΒΏΔٕज़෼໺ʹରԠ
 ʢࡐྉɺՃ޻ٕज़ɺσόΠεɺγεςϜɺݕࠪɾ ධՁٕज़ɺ૑ༀɺ੡ࡎ…ʣ ๛෋ͳ࣮੷ φΠϯγάϚɾϗʔϧσΟϯάε ୅දऔక໾ࣾ௕ɿਡ๚ڿ඙ NineSigma Europe φΠϯγάϚɾ ΞδΞύγϑΟοΫ NineSigma North America ʢ2017೥9݄15೔ʙʣ ʢ2006೥ʙʣ 2018೥φΠϯγάϚɾδϟύϯΑΓ໊ࣾมߋ ʢ2008೥ʙʣ ʢ2000೥ʙʣ ମ੍ ސ໰ͷ੕໺͕ࣥචͨ͠ʰΦʔϓϯɾ ΠϊϕʔγϣϯͷڭՊॻʱʢμΠϠ Ϟϯυࣾൃߦʣ͸Harvard Business ReviewಡऀʹΑΔϕετ ܦӦॻτοϓ20ʹબग़ɻɹ We Make Innovation Happen
3. ࣗݾ঺հ ໊લɿࡾӜ
   ࠀߒ
   5XJUUFS
   *%ɿ!.JVSB,BUTV
   ࢓ࣄɿ'JSTU
   &OHJOFFS
   ޷͖ͳϑϨʔϜϫʔΫɿ -BSBWFMɺ4BJMTKT
   Ӝ࿨Ϩοζɿ
4. .FUBCBTFྺ̎ϲ݄
5. ϩʔΧϧ؀ڥʹ.FUBCBTF ͍ΖΜͳχʔζ͕͋Δͱࢥ͍·͢ɻ w ͱΓ͋͑ͣ৮ͬͯΈ͍ͨ
   w ࣾ಺༻.FUBCBTFαʔόͷ ։ൃ؀ڥ
   w ࣾ಺ʹଞʹ࢖ͬͯ͘Εͦ͏ ͳਓ͕͍ͳ͍
   w ࣾ಺ʹଞʹਓ͕͍ͳ͍
6. w %PDLFSͰϩʔΧϧ؀ڥΛ࡞Δ࿩
   w "84ͷ3%4ʹ઀ଓͤ͞Δ࿩
   w ύεϫʔυ๨ΕͪΌͬͨ࿩
   w ϩʔΧϧͷ%#͕ཉ͘͠ͳͬͪΌ͏࿩ ΞδΣϯμ
7. %PDLFSͰϩʔΧϧ؀ڥ ɾެࣜͷ%PDLFSΠϝʔδ͕༻ҙ͞Ε͍ͯΔͷͰɺα Ϋοͱɻ https://www.metabase.com/start/docker.html
8. MPDBMIPTUTFVQ .FUBCBTF΁Α͏ͦ͜
9. ຖճ4FUVQʹͳͬͪΌ͏ͷͰӬଓԽ ͜Ε΋ެࣜͷυΩϡϝϯτ https://www.metabase.com/docs/v0.36.3/operations-guide/running-metabase-on-docker.html
10. EPDLFSDPNQPTFͷಋೖ $cat docker-compose.yml version: '3' services: metabase: image : metabase/metabase hostname : 'metabase' container_name: 'metabase' volumes: - ./metabase/metabase:/metabase.db:cached ports: - "3000:3000" $docker-compose -up d
11. w %PDLFSͰϩʔΧϧ؀ڥΛ࡞Δ࿩
    w "84ͷ3%4ʹ઀ଓͤ͞Δ࿩
    w ύεϫʔυ๨ΕͪΌͬͨ࿩
    w ϩʔΧϧͷ%#͕ཉ͘͠ͳͬͪΌ͏࿩ ΞδΣϯμ
12. "84͋Δ͋Δߏ੒ ɾ3%4͸άϩʔόϧ͔ΒΞΫηεͰ͖ͳ͍
    ɾ&$ͷ౿Έ୆αʔό͕༻ҙ͞Ε͍ͯͯɺͦ͜ܦ༝ͰΞΫηεͰ͖Δ ͍ͭ͜ʹͭͳ͍͛ͨʂʂ
13. Α͋͘Δ%#΁ͷ઀ଓઃఆ 44)Ͱ઀ଓ͢Δ৔߹ 44)
    LFZ
    pMF
    ͷࢦఆ͕Ͱ͖Δ
14. .FUBCBTFͷ৔߹ VTFSOBNFQBTTXPSE͡Όͳ͍ͱͰ͖ͳ͍ʂ
15. "84
    ͋Δ͋Δߏ੒ &$΁ͷ44)઀ଓ͸QFNϑΝΠϧ͕഑ΒΕΔ $ssh ec2-user@bastion -i ~/.ssh/bation.pem ͍ͭ͜ʹ͸VTFSOBNFQBTTXPSEͰ͸ͭͳ͛ͳ͍ʂʂ
16. ࣗ෼Ͱ۷Δ͔͠ͳ͍ɻ44)τϯωϧΛ
17. ͜͜Ͱɺ+VQZUFS-BC σʔλ෼ੳʹศརͳ
    +VQZUFS
    OPUFCPPLͷޙܧ QZUIPOͷ࣮ߦ؀ڥΛ8FCͰૢ࡞Ͱ͖Δ
18. ެࣜͷ%PDLFSʹͪΐ͍଍͠ $cat jupyter/Dockerfile FROM jupyter/scipy-notebook RUN conda install -y sshtunnel $cat docker-compose.yml jupyter: build : ./jupyter hostname : 'jupyter' container_name: 'jupyter' volumes : - ~/.ssh:/home/jovyan/.ssh ports : - "8888:8888" command : jupyter lab --ip=0.0.0.0 --allow-root --NotebookApp.token='' --port=8888 ϗετ1$ͷTTILFZ ͕อଘͯ͋͠Δ৔ॴΛ %PDLFSଆͰϚ΢ϯτ QZUIPOͷTTIUVOOFM ΛΠϯετʔϧ +VQZUFS-BCΛ Ͱىಈ͢ΔίϚϯυ
19. -PDBMIPTUMBC +VQZUFS-BC͕ىಈ͞Εͯ·͢ $POTPMFͰ1ZUIPOͷ࣮ߦ؀ڥ
20. ͜Μͳײ͡ͷίʔυΛషΓ෇͚·͢ 4IJGU &OUFSͰ࣮ߦͰ͢ɻ
21. ίʔυͷઆ໌ import sshtunnel import socket hostname = socket.gethostname() ip_address = socket.gethostbyname(hostname) bastion = sshtunnel.SSHTunnelForwarder( ('x.x.x.x',22), ssh_host_key = None, ssh_username = 'ec2-user', ssh_password = None, ssh_pkey = '~/.ssh/bastions.pem', local_bind_address = (ip_address,33336), remote_bind_address = ('your-rds.amazonaws.com',3306) ) bastion.start() ౿Έ୆αʔόͷΞυϨε TTILFZͷ %PDLFS಺ͷQBUI ϩʔΧϧͰ࢖͍͍ͨϙʔτ
22. .FUBCBTFଆͷઃఆ QZUIPOͷίʔυͰࢦఆͨ͠ϙʔτ EPDLFSDPNQPTFZNMͰࢦఆ໊ͨ͠લ
23. .FUBCBTFଆͷઃఆ ௨ৗͷ%#ͷઃఆͰ0,Ͱ͢ɻ KVQZUFSͰτϯωϧ۷ͬͯΔͷͰ0''Ͱʂ
24. σʔλϕʔε͕௥Ճ͞Ε·ͨ͠ʂ
25. w %PDLFSͰϩʔΧϧ؀ڥΛ࡞Δ࿩
    w "84ͷ3%4ʹ઀ଓͤ͞Δ࿩
    w ύεϫʔυ๨ΕͪΌͬͨ࿩
    w ϩʔΧϧͷ%#͕ཉ͘͠ͳͬͪΌ͏࿩ ΞδΣϯμ
26. ͏͔ͬΓαΠϯΞ΢τ ύεϫʔυ΋๨Εͯ͠·͍·ͨ͠ɻ
27. ؅ཧऀ΁͓໰͍߹Θ͍ͤͩ͘͞ ʮΦϨ΍ͬͭ͏ͷʯ
28. ͜͜Ͱɺ.BJMIPH ؆қ4.51αʔόɻ։ൃ؀ڥͰͷϝʔϧςετʹ࢖ͬͨΓɻ $cat docker-compose.yml mailhog: image : mailhog/mailhog hostname : mailhog container_name: mailhog ports: - "8025:8025" ྫʹΑͬͯEPDLFSDPNQPTFʹެࣜͷJNBHFΛ௥Ճ
29. .FUBCBTFଆͷઃఆ EPDLFSDPNQPTFZNMͰࢦఆ໊ͨ͠લ
30. ૹ৴ݩΞυϨε͸ඞਢͰ͢ɻ ϑΥʔϚοτ͕͍͋ͬͯΕ͹ՍۭͷΞυϨεͰ΋େৎ෉Ͱ͢ɻ
31. ύεϫʔυΛ͓๨Εͷ৔߹ Ϧηοτϝʔϧ͕ૹ৴Ͱ͖·͢ɻ
32. -PDBMIPTU ૹ৴͞Εͨϝʔϧ͕8FCͰࢀরͰ͖·͢
33. ύεϫʔυϦηοτͷϝʔϧ
34. ϦϯΫ͔ΒϦηοτͰ͖·͢ɻ ϝʔϧΞυϨε͸๨Εͳ͍Ͱ͍ͩ͘͞ɻ
35. w %PDLFSͰϩʔΧϧ؀ڥΛ࡞Δ࿩
    w "84ͷ3%4ʹ઀ଓͤ͞Δ࿩
    w ύεϫʔυ๨ΕͪΌͬͨ࿩
    w ϩʔΧϧͷ%#͕ཉ͘͠ͳͬͪΌ͏࿩ ΞδΣϯμ
36. ϩʔΧϧʹ΋%#͕ཉ͘͠ͳͬͪΌ͏ ͱΓ͋͑ͣ.Z42-Λެࣜͷ%PDLFSͰཱͯΔ $cat docker-compose.yml mysql: image : mysql:5.7 hostname : 'mysql' container_name: 'mysql' volumes: - ./mysql/mysql_data:/var/lib/mysql - ./mysql/docker-entrypoint-initdb.d/:/docker-entrypoint-initdb.d/ environment: #MYSQL_DATABASE : ‘dwh’ MYSQL_ROOT_HOST : ‘%' MYSQL_ROOT_PASSWORD: password ports: - '33061:3306' %#ͷӬଓԽ ϗετଆ͔ΒΞΫηε͢Δࡍͷϙʔτ ىಈ࣌ʹ࣮ߦ͢Δ42-ϑΝΠϧ
37. ͳͥ%%-Λผ్ͳ͕ͨ͠ͷ͔ EPDLFSDPNQPTFʹࢦఆ͢ΔͱMBUJOʹͳͬͪΌ͏͔Β mysql !"" docker-entrypoint-initdb.d !"" 001_create_database.sql $cat docker-entrypoint-initdb.d/001_create_database.sql CREATE DATABASE dwh CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci; >show create database doh CREATE DATABASE `dwh` /*!40100 DEFAULT CHARACTER SET latin1 */ ೔ຊޠ࢖Θͳ͍ͳΒ͍Βͳ͍͔΋ɻ
38. ϩʔΧϧʹ%#͕͋Δͱ QBOEBTͷ%BUBGSBNFΛ%#ʹ௥Ճ
39. ҰԠίʔυͷઆ໌΋͞Βͬͱ import sqlalchemy import pandas as pd mysql_user = 'root' mysql_passwd = 'password' mysql_database = 'dwh' mysql_address = 'mysql:3306' engine = sqlalchemy.create_engine( 'mysql://{}:{}@{}/{}?charset=utf8'.format( mysql_user , mysql_passwd , mysql_address , mysql_database , ), encoding = "utf-8", echo = True, pool_pre_ping = True ) df = pd.DataFrame([ ["0001", "John", "Engineer"], ["0002", "Lily", "Sales"]], columns=['id', 'name', 'job']) print(df) df.to_csv('sample.csv') df.to_sql('sample', con=engine, if_exists='fail', index=False) TRMBMDIFNZͰFOHJOF࡞੒ QBOEBTͷ%BUBGSBNF࡞੒ EGUP@TRM ʹςʔϒϧ໊ͱ ্Ͱ࡞ͬͨFOHJOFΛ౉͢
40. .FUBCBTFͰී௨ʹ%#Λ௥Ճ EPDLFSDPNQPTFZNMͰࢦఆ໊ͨ͠લ
41. σʔλϕʔε͕௥Ճ͞Ε·ͨ͠ʂ
42. ௥Ճͨ͠σʔλ͕ݟΕΔΑ͏ʹ
43. ࠷ऴతʹ͸͜Μͳߏ੒Ͱ΍ͬͯ·͢         
44. ͝ਗ਼ௌ
    ͋Γ͕ͱ͏͍͟͝·ͨ͠ https://github.com/MiuraKatsu/bi_tools_docker