Upgrade to Pro — share decks privately, control downloads, hide ads and more …

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

Avatar for MiuraKatsu MiuraKatsu
August 06, 2020

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

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

Avatar for MiuraKatsu

MiuraKatsu

August 06, 2020
Tweet

More Decks by MiuraKatsu

Other Decks in Technology

Transcript

  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. 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
  4. ެࣜͷ%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Λ Ͱىಈ͢ΔίϚϯυ
  5. ίʔυͷઆ໌ 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 ϩʔΧϧͰ࢖͍͍ͨϙʔτ
  6. ͜͜Ͱɺ.BJMIPH ؆қ4.51αʔόɻ։ൃ؀ڥͰͷϝʔϧςετʹ࢖ͬͨΓɻ $cat docker-compose.yml mailhog: image : mailhog/mailhog hostname :

    mailhog container_name: mailhog ports: - "8025:8025" ྫʹΑͬͯEPDLFSDPNQPTFʹެࣜͷJNBHFΛ௥Ճ
  7. ϩʔΧϧʹ΋%#͕ཉ͘͠ͳͬͪΌ͏ ͱΓ͋͑ͣ.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-ϑΝΠϧ
  8. ͳͥ%%-Λผ్ͳ͕ͨ͠ͷ͔ 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 */ ೔ຊޠ࢖Θͳ͍ͳΒ͍Βͳ͍͔΋ɻ
  9. ҰԠίʔυͷઆ໌΋͞Βͬͱ 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Λ౉͢