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

Redshiftハンズオン

 Redshiftハンズオン

Redshiftをサクッと触ってみるハンズオン

3b0eb6111ab44c573d3111b034ec7dc9?s=128

KON YUICHI

June 23, 2016
Tweet

More Decks by KON YUICHI

Other Decks in Technology

Transcript

  1. 3FETIJGU )BOETPO !LPOQZV

  2. 3FETIJGUͱ͸  "84ͷϚωʔδυͷ%8) σʔλ΢ΣΞϋ΢ε αʔϏ ε  3%#.4Ͱ͸ѻ͑ͳ͍େྔσʔλͷूܭॲཧ  1#ن໛·Ͱ༰қʹ֦ுՄೳ

     1PTUHSFTޓ׵ͷυϥΠόͰ઀ଓՄೳ  .11 .BTTJWFMZ1BSBMMFM1SPDFTTJOH ฒྻॲཧ  Πϯελϯεىಈ࣌ؒͷैྔ՝ۚ
  3. ྻࢦ޲%#ͱ͸  ྻํ޲ͷσʔλΛҰڍʹҾͬுͬͯ͘Δ࣌ʹύϑΥʔ Ϛϯε͕ग़ΔΑ͏ʹઃܭ͞Εͨ%#  Ϗοάσʔλͷूܭʢ߹ܭɺฏۉͱ͔ʣ΍෼ੳॲཧΛ ߴ଎ʹߦ͑Δ  ߋ৽΍࡟আॲཧ͸޲͔ͳ͍ʢຆͲߦΘͳ͍ʣ 

    ಉ͡ΧϥϜ͸ಉ͡σʔλͷ܁Γฦ͕͠ଟ͘ى͜ΔͨΊ ѹॖΛޮ͔ͤ΍͍͢ˠσΟεΫ*0͕ݮΒͤΔ $PMVNOBS%BUBCBTF
  4. ͓୊ ͬͦ͘͞࢖ͬͯΈΔ  ͱ͋ΔϝσΟΞαΠτͷӾཡཤྺσʔλ͔ΒͲͷهࣄ ͕ԿճݟΒΕ͔ͨΛूܭͯ͠Έ·͠ΐ͏  ಉ͘͡ɺϦϑΝϥΛूܭͯ͠Ͳ͔͜Βͷྲྀೖ͕ଟ͔ͬ ͔ͨΛूܭͯ͠Έ·͠ΐ͏

  5. ඞཁͳ΋ͷ  "84ͷΞΧ΢ϯτͱ3FETIJGU͕ୟ͚Δ*".3PMF͕෇ Ճ͞ΕͨVTFS  "84$MJ  1PTUHSFTRM

  6. BXTSFETIJGUDSFBUFDMVTUFSDMVTUFSJEFOUJpFSFYBNQMFDMVTUFSa NBTUFSVTFSOBNFLPOQZVa NBTUFSVTFSQBTTXPSE*LFIBTPOTIJa OPEFUZQFEDMBSHFa DMVTUFSUZQFTJOHMFOPEFa QSPpMFBXTLPOQZV "84$MJͰىಈ

  7. 4FDVSJUZ(SPVQ ɾԼهϦϯΫΛࢀߟʹͯ͠4FDVSJUZ(SPVQΛઃఆ IUUQEPDTBXTBNB[PODPNKB@KQSFETIJGUMBUFTU HTHSTHTHBVUIPSJ[FDMVTUFSBDDFTTIUNM ˞΋ͪΖΜɺQSPEVDUJPOͰ͸μϝ

  8. QTRMͰϩάΠϯ QTRMIFYBNQMFDMVTUFSYYYYYYYYYYYBQ OPSUIFBTUSFETIJGUBNB[POBXTDPN6LPOQZVQEEFW DPOTPMF΋͘͠͸"84$MJ͔Βىಈͨ͠ϗετΛ֬ೝ͢Δ BXTSFETIJGUEFTDSJCFDMVTUFSTQSPpMFBXTLPOQZV QTRMܦ༝Ͱ઀ଓ σϑΥϧτͷ%#໊EFW

  9. SFBE@IJTUPSJFTΛ࡞੒ $3&"5&5"#-&SFBE@IJTUPSJFT  JEJOUFHFS/05/6--  TFTTJPO@JEDIBSBDUFSWBSZJOH   OPUF@JEJOUFHFS 

    DSFBUFE@EBUFQH@DBUBMPHEBUF  OPUF@UZQFDIBSBDUFSWBSZJOH   VTFS@JEJOUFHFS  VTFS@BHFOUDIBSBDUFSWBSZJOH   SFGFSSFSDIBSBDUFSWBSZJOH   DSFBUFE@BUUJNFTUBNQXJUIPVUUJNF[POF  VQEBUFE@BUUJNFTUBNQXJUIPVUUJNF[POF  DSFBUPS@VTFS@JEJOUFHFS/05/6--  ˡTUSJOH ˡUFYU
  10. QPTUHSFTͷجຊ σʔλϕʔεҰཡ TIPXEBUBCBTFT  EFWaM ઀ଓ%#มߋ VTFTPNF@EC@OBNF  EFWaDTPNF@EC@OBNF ςʔϒϧҰཡ

    TIPXUBCMFT  EFWaE ͱ͋ΔςʔϒϧͷεΩʔϚ֬ೝ EFWaETPNF@UBCMF@OBNF  TIFMM͔Βൈ͚Δ EFWaR
  11. ࢼ͠ʹ*/4&35 INSERT INTO read_histories (id,session_id, note_id, created_date,note_type, user_id, user_agent, referrer,

    created_at, updated_at, creator_user_id) VALUES (1,'389533523fe428f1f015401d1d89b487', 9999, '2016-06-18', 'TextNote', NULL, 'Mozilla/5.0 (iPhone; CPU iPhone OS 9_3_2 like Mac OS X) AppleWebKit/601.1.46 (KHTML, like Gecko) Version/9.0 Mobile/13F69 Safari/601.1', 'https://note.mu/konpyu', '2016-06-18 04:48:31', '2016-06-18 04:48:31', 2222); -[ RECORD 1 ]--- +--------------------------------------------------------------------------- id | 1 session_id | 389533523fe428f1f015401d1d89b487 note_id | 9999 created_date | 2016-06-18 note_type | TextNote user_id | user_agent | Mozilla/5.0 (iPhone; CPU iPho…………3F69 Safari/601.1 referrer | https://note.mu/konpyu created_at | 2016-06-18 04:48:31 updated_at | 2016-06-18 04:48:31 creator_user_id | 2222
  12. ΫϥελʹσʔλΛ౤ೖ  "NB[PO4  "NB[PO&.3  "NB[PO%ZOBNP%#  ϦϞʔτϗετʢ44) ˡࠓճ͸T͔ΒΠϯϙʔτ͢Δ

  13. σʔλΛ४උ͢Δ ద౰ʹEVNQ༻ͷεΫϦϓτΛॻ͍ͯɺ೔͝ͱͷDTW ʹՃ޻͠Tͷద౰ͳCVDLFUʹVQ͢Δ

  14. None
  15. None
  16. $01:ίϚϯυͰ౤ೖ TʹVQͨ͠DTWH[Λϩʔυ͢Δɻ$01:ίϚϯυΛ ༻͍ΔͱΑ͠ͳʹฒྻʹϩʔυͯ͘͠ΕΔͨΊߴ଎ʹ ॲཧ͕ਐΉ copy read_histories from 's3://stats/redshift/note/read_histories/ read_histories_2016-06-01.csv.gz' CREDENTIALS

    'aws_access_key_id=xxxxxxxxxxxxxx;aws_secret_access_key=yyyyyyyyyyyyyyyyyyyyyy' DELIMITER ',' NULL AS 'NULL' REMOVEQUOTES IGNOREHEADER 1 GZIP REGION 'ap-northeast-1';
  17. $01:ίϚϯυͰ౤ೖ DPQZSFBE@IJTUPSJFTGSPNbTTDTWH[ $3&%&/5*"-4bBXT@BDDFʜZZZZZZ` %&-*.*5&3  /6--"4/6-- 3&.07&2605&4 *(/03&)&"%&3 (;*1 3&(*0/BQOPSUIFBTU

    ˡΧϯϚ۠੾Γ ˡ/6--ͩͬͨΒz/6--zͱ͍͏จࣈྻͰຒΊ߹Θͤ ˡΞΠςϜ͕z΍`Ͱғ·ΕͯͨΒͦͷRVPUF͸औΓআ͘ ˡߦ໨ΛऔΓআ͘ʢϔομରԠʣ ˡH[JQͷղౚΛߦ͏
  18. ࢼ͠ʹूܭ dev=# select note_id, count(note_id) as cnt from read_histories group

    by note_id order by cnt desc limit 10; note_id | cnt ---------+------- 9999999 | 9999 9999998 | 8888 9999997 | 7777 9999996 | 6666 9999995 | 5555 9999994 | 4444 9999993 | 3333 9999992 | 2222 9999991 | 1111 9999990 | 1100 (10 rows)
  19. NBOJGFTUͰҰׅ౤ೖ େྔͷϑΝΠϧΛҰ౓ʹ3FETIJGUʹϩʔυ͢Δ࢓૊Έ KTPOʹMPBE͢ΔϑΝΠϧΛཏྻ͠ɺ-0"%࣌ʹͦΕΛ ࢦఆ͢Δ

  20. NBOJGFTUΛ༻͍ͯ$01: $ cat read_histories_201606.manifest { "entries": [ {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-01.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-02.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-03.csv.gz"},

    {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-04.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-05.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-06.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-07.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-08.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-09.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-10.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-11.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-12.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-13.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-14.csv.gz"}, {"url":"s3://stats/redshift/note/read_histories/read_histories_2016-06-15.csv.gz"} ] }
  21. NBOJGFTUΛ༻͍ͯ$01: $ aws s3 cp \ read_histories_201606.manifest \ s3://stats/redshift/note/read_histories/ ࡞ͬͨNBOJGFTUΛTʹసૹ

  22. NBOJGFTUΛࢦఆͯ͠ϩʔυ copy read_histories from 's3://stats/redshift/note/read_histories/read_histories_201606.manifest' CREDENTIALS 'aws_access_key_id=xxxxxxxxxxxxxx;aws_secret_access_key=yyyyyyyyyyyyyyyyyyyyyy' DELIMITER ',' NULL

    AS 'NULL' REMOVEQUOTES GZIP REGION 'ap-northeast-1' manifest; ⾬ίϨΛ௥Ճ
  23. ໰୊  ϦϑΝϥΛूܭͯ͠ɺྲྀೖܦ࿏ͷঢ়گʹ͍ͭͯ೺Ѳ͠ ·͠ΐ͏  ର৅GBDFCPPL UDP HPPHMF TNBSUOFXT 

    OFXTQJDLT IBUFOB
  24. ΫϥελΛఀࢭ aws redshift delete-cluster --cluster-identifier examplecluster \ --profile awskonpyu \

    --skip-final-cluster-snapshot