Redshiftハンズオン

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

1. 3FETIJGU
   )BOETPO 
   !LPOQZV
2. 3FETIJGUͱ͸  "84ͷϚωʔδυͷ%8) σʔλ΢ΣΞϋ΢ε αʔϏ ε
    3%#.4Ͱ͸ѻ͑ͳ͍େྔσʔλͷूܭॲཧ
    1#ن໛·Ͱ༰қʹ֦ுՄೳ
    1PTUHSFTޓ׵ͷυϥΠόͰ઀ଓՄೳ
    .11 .BTTJWFMZ
   1BSBMMFM
   1SPDFTTJOH
   ฒྻॲཧ
    Πϯελϯεىಈ࣌ؒͷैྔ՝ۚ
3. ྻࢦ޲%#ͱ͸  ྻํ޲ͷσʔλΛҰڍʹҾͬுͬͯ͘Δ࣌ʹύϑΥʔ Ϛϯε͕ग़ΔΑ͏ʹઃܭ͞Εͨ%#
    Ϗοάσʔλͷूܭʢ߹ܭɺฏۉͱ͔ʣ΍෼ੳॲཧΛ ߴ଎ʹߦ͑Δ
    ߋ৽΍࡟আॲཧ͸޲͔ͳ͍ʢຆͲߦΘͳ͍ʣ
    ಉ͡ΧϥϜ͸ಉ͡σʔλͷ܁Γฦ͕͠ଟ͘ى͜ΔͨΊ ѹॖΛޮ͔ͤ΍͍͢
   ˠ
   σΟεΫ*0͕ݮΒͤΔ $PMVNOBS
   %BUBCBTF
4. ͓୊ ͬͦ͘͞࢖ͬͯΈΔ  ͱ͋ΔϝσΟΞαΠτͷӾཡཤྺσʔλ͔ΒͲͷهࣄ ͕ԿճݟΒΕ͔ͨΛूܭͯ͠Έ·͠ΐ͏
   
   ಉ͘͡ɺϦϑΝϥΛूܭͯ͠Ͳ͔͜Βͷྲྀೖ͕ଟ͔ͬ ͔ͨΛूܭͯ͠Έ·͠ΐ͏
   
5. ඞཁͳ΋ͷ  "84ͷΞΧ΢ϯτͱ3FETIJGU͕ୟ͚Δ*".
   3PMF͕෇ Ճ͞ΕͨVTFS
    "84
   $MJ
    1PTUHSFTRM
   
6. BXT
   SFETIJGU
   DSFBUFDMVTUFS
   DMVTUFSJEFOUJpFS
   FYBNQMFDMVTUFS
   a
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   NBTUFSVTFSOBNF
   LPOQZV
   a
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   NBTUFSVTFSQBTTXPSE
   *LFIBTPOTIJ
   
   a
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   OPEFUZQF
   EDMBSHF
   a
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   DMVTUFSUZQF
   TJOHMFOPEF
   a
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   
   QSPpMF
   BXTLPOQZV "84
   $MJͰىಈ
7. 4FDVSJUZ
   (SPVQ ɾԼهϦϯΫΛࢀߟʹͯ͠4FDVSJUZ
   (SPVQΛઃఆ
   IUUQEPDTBXTBNB[PODPNKB@KQSFETIJGUMBUFTU HTHSTHTHBVUIPSJ[FDMVTUFSBDDFTTIUNM ˞
   ΋ͪΖΜɺQSPEVDUJPOͰ͸μϝ
8. QTRMͰϩάΠϯ QTRM
   I
   FYBNQMFDMVTUFSYYYYYYYYYYYBQ OPSUIFBTUSFETIJGUBNB[POBXTDPN
   6
   LPOQZV
   Q
   
   E
   EFW 
   DPOTPMF΋͘͠͸"84
   $MJ͔Βىಈͨ͠ϗετΛ֬ೝ͢Δ BXT
   SFETIJGU
   EFTDSJCFDMVTUFST
   QSPpMF
   BXTLPOQZV 
   QTRMܦ༝Ͱ઀ଓ σϑΥϧτͷ%#໊
   
   EFW
9. SFBE@IJTUPSJFTΛ࡞੒ $3&"5&
   5"#-&
   SFBE@IJTUPSJFT
   
   
   
   
   
   JE
   JOUFHFS
   /05
   /6--
   
   
   
   
   TFTTJPO@JE
   DIBSBDUFS
   WBSZJOH 
   
   
   
   
   OPUF@JE
   JOUFHFS
   
   
   
   
   DSFBUFE@EBUF
   QH@DBUBMPHEBUF
   
   
   
   
   OPUF@UZQF
   DIBSBDUFS
   WBSZJOH 
   
   
   
   
   VTFS@JE
   JOUFHFS
   
   
   
   
   VTFS@BHFOU
   DIBSBDUFS
   WBSZJOH 
   
   
   
   
   SFGFSSFS
   DIBSBDUFS
   WBSZJOH 
   
   
   
   
   DSFBUFE@BU
   UJNFTUBNQ
   XJUIPVU
   UJNF
   [POF
   
   
   
   
   VQEBUFE@BU
   UJNFTUBNQ
   XJUIPVU
   UJNF
   [POF
   
   
   
   
   DSFBUPS@VTFS@JE
   JOUFHFS
   /05
   /6--
    ˡTUSJOH ˡUFYU
10. QPTUHSFTͷجຊ 
    σʔλϕʔεҰཡ 
    TIPX
    EBUBCBTFT
    EFW
    aM
    
    
    ઀ଓ%#มߋ VTF
    TPNF@EC@OBNF
    EFW
    aD
    TPNF@EC@OBNF
    
    ςʔϒϧҰཡ 
    TIPX
    UBCMFT
    EFW
    aE
    
    ͱ͋ΔςʔϒϧͷεΩʔϚ֬ೝ
    EFW
    aE
    TPNF@UBCMF@OBNF
    
    
    
    TIFMM͔Βൈ͚Δ
    EFW
    aR
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[PO
    4
     "NB[PO
    &.3
     "NB[PO
    %ZOBNP%#
     ϦϞʔτϗετʢ44) ˡࠓճ͸T͔ΒΠϯϙʔτ͢Δ
13. σʔλΛ४උ͢Δ ద౰ʹEVNQ༻ͷεΫϦϓτΛॻ͍ͯɺ೔͝ͱͷDTW ʹՃ޻͠Tͷద౰ͳCVDLFUʹVQ͢Δ
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:ίϚϯυͰ౤ೖ DPQZ
    SFBE@IJTUPSJFT
    GSPN
    bTTDTWH[
    $3&%&/5*"-4
    bBXT@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