Share
Embed
データ基盤を作るのに使うようなサービスは単発で使うだけでも便利 / Manage to look over unmanaged data with fully managed services
polamjag
January 23, 2019
Transcript
29 slides
-
σʔλج൫Λ࡞Δͷʹ͏Α͏ͳαʔϏε ୯ൃͰ͏͚ͩͰศར άϩʔεϋοΫͷจ຺Λఴ͑ͯ Hatena Engineer Seminar #11 2019-01-23 @ Tokyo
/ 2019-01-30 @ Kyoto id:polamjag -
whoami • id:polamjag ϙ ϥ Ϝ δ ϟ ά •
ͯͳϒϩά • ΞϓϦέʔγϣϯΤϯδχΞ -
• SQL Λී௨ʹୟ͘ • ෳͷσʔλϕʔεʹ·͕ͨͬͯूܭ͍ͨ͠? • SELECT COUNT(*) ʹ͔͔࣌ؒΔΑ͏ͳςʔϒϧͰͱʹ͔͘ू ܭ͍ͨ͠?
• ूܭΫΤϦΛߴԽ͢ΔͨΊʹՃͰ index ཉ͍͚͠Ͳ ͱΓ͋͑ͣݟͯΈ͍ͨϨϕϧͷूܭͷͨΊʹ ALTER TABLE Ͱ͖Δ? • ྺ࢙తܦҢͰγϦΞϥΠζ͞Εͨσʔλ͕ೖͬͯΔΧϥϜ͕͋Δ ͷΛΫΤϦ͍ͨ͠? -
• SQL Λී௨ʹୟ͘ • ୟ͍ͨ݁Ռͷڞ༗ՄࢹԽͱ͔Ͳ͏͢Δ? • ॻࣺͯεΫϦϓτΛॻ͍ͯूܭͨ͠ͷΛεϓϨου γʔτʹΞοϓϩʔυ • ͦ͏͍͏ͷΛόονͱͯ͠ఆظ࣮ߦ͢Δ
• ͦͷεϓϨουγʔτ୭͕ϝϯςφϯε͢Δ? • ………… -
!!! BigQuery !!! • ͏·͍͍͍҆ۜͷؙ • సૹʹ Embulk Λ͍ɺ DSL
ͷதͰσʔλΛ͔ͯ͠Β όϧΫϩʔσΟϯά͢Δ • ඞཁͳσʔλશ෦ूΊ͖ͯͨΒવ JOIN ͳͲͰ͖Δ • σʔλྔ͕͍ͨͨ͜͠ͱͳ͍[ͲΕ͘Β͍?]ͳΒߟ͑ͳ͍ͱ͍ ͚ͳ͍͜ͱҰͳ͍ -
ΛϫϯγϣοτͰ͏ • Embulk ͰσʔλૹΔ • 1ճ͖ΓͳΒߟ͑ͳ͍ͱ͍͚ͳ͍͜ͱগͳ͍ • (ఆظ࣮ߦɾߋ৽ͷ͘͠ΈɺΠϯΫϦϝϯλϧϩʔυɺ……) • BigQuery
ͰͳΜΒ͔ूܭ͢Δ • Google εϓϨουγʔτʹΤΫεϙʔτ͢ΔϘλϯ • σʔλϙʔλϧ (Data Studio) ʹಥͬࠐΉ -
BigQuery ʹΠϯϙʔτ͠ʹ͍͘ςʔϒϧ • ׂͷׂςʔϒϧΛ͍ʹ͍͘ςʔϒϧ • Ϩίʔυ͕ INSERT ͞Εͨ࣌ࠁɺΛද͢ΧϥϜ͕ແ͍ • ্Ґ֓೦ͷͦ͏͍͏ใΛ
JOIN ͖ͯͯͦ͠ΕͰύʔςΟγϣ ϯ͢Δ? • αʔϏεͷྺ࢙͕ 4000 (≒ 10.96 ) Ҏ্͋Δ • צͰύʔςΟγϣϯͷִؒΛؒҾ͘? • Α͘Α͘ߟ͑ͨΒຊ·ͩύʔςΟγϣχϯάඞཁͳ͍ͱ͔ -
Google BigQuery ྉۚ • ౦ژϦʔδϣϯͳΒετϨʔδ୯Ձ S3 ΑΓ͍҆ • 0.023USD /
GB / ݄ • ΫΤϦྉۚ 8.55 USD / 1 TB • ࠓͷҝସϨʔτͩͱ͍͍ͩͨ 1 GB ͋ͨΓ 1 ԁऑ • ͪΐͬͱ͍ͨ͠ํͳΒ΄ͱΜͲޡࠩϨϕϧ -
ͦͷϩάࠓͲ͜ʹ • Ͳ͔͜ͷαʔό্? • ͦΕͦ͜ BigQuery ͱ͔ RedShift ͱ͔? •
Amazon S3 ʹஔ͔Ε͍ͯΔ? • ࣗͷ߹ݟ͔ͨͬͨͷ͕ S3 ʹஔ͔Εͯ ͍ͨ -
S3 ʹஔ͍ͯ͋ΔͳΒ Amazon Athena Ͱ • S3 ʹஔ͍ͯ͋ΔϑΝΠϧΛΫΤϦͰ͖ΔϚωʔδυαʔϏε • த
Presto (https://prestodb.io/) • Presto Hive ͷ͕ࣝ͋Δͱ͍͜ͳͤΔͱ͍͏͜ͱ͕ଟ͍ • ϑϧϚωʔδυͳͷͰɺूܭͷͨΊʹετϨʔδͱωοτϫʔΫ ڧΊͷϚγϯΛ༻ҙͨ͠Γɺͱ͍ͬͨ͜ͱΛ͠ͳ͍͍ͯ͘ • ͓ΉΖʹ S3 ͷϑΝΠϧΛࢦఆͯ͠ CREATE TABLE ͯ͠ΫΤ Ϧ͢Δ͚ͩ -
• ҙͷϑΥʔϚοτΛύʔεͯ͠ SQL ͰΫΤϦ • CSV/TSV, JSONL, ֤छΧϥϜφ (ྻࢤ) ϑΝΠϧͳͲ
ඪ४αϙʔτ • LTSV ਖ਼نදݱͰύʔεͯ͠ΫΤϦͰ͖Δ • → ਖ਼نදݱͰύʔεͯ͠Λൈ͖ग़ͤΔϑΝΠϧͳ ΒԿͰΫΤϦͰ͖Δ • ύʔε͠ͳ͍͜ͱͰ grep as a service ͱͯ͠׆༻ -
-- LTSV Λ Athena ͰΫΤϦ͢ΔͨΊʹύʔεͯ͠ΈΑ͏ CREATE EXTERNAL TABLE IF NOT
EXISTS `sugoi-service-logs` ( `time` string, `host` string, `method` string, `path` string, `taken_sec` float, `ua` string, `referer` string ) PARTITIONED BY (date string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( 'serialization.format' = '1', 'input.regex' = '^time:([^\\t]*)\\thost:([^\\t]*)\\tmethod:([^\\t]*)\\turi: ([^\\t]*)\\ttaken_sec:([^\\t]*)\\tua:([^\\t]*)\\treferer:([^\\t]*)$' ) LOCATION 's3://your-logs-bucket/sugoi-service-logs/' TBLPROPERTIES ('has_encrypted_data'='false') -
-- LTSV Λ Athena ͰΫΤϦ͢ΔͨΊʹύʔεͯ͠ΈΑ͏ CREATE EXTERNAL TABLE IF NOT
EXISTS `sugoi-service-logs` ( `time` string, `host` string, `method` string, `path` string, `taken_sec` float, `ua` string, `referer` string ) PARTITIONED BY (date string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( 'serialization.format' = '1', 'input.regex' = '^time:([^\\t]*)\\thost:([^\\t]*)\\tmethod:([^\\t]*)\\turi: ([^\\t]*)\\ttaken_sec:([^\\t]*)\\tua:([^\\t]*)\\treferer:([^\\t]*)$' ) LOCATION 's3://your-logs-bucket/sugoi-service-logs/' TBLPROPERTIES ('has_encrypted_data'='false') -
-- LTSV Λ Athena ͰΫΤϦ͢ΔͨΊʹύʔεͯ͠ΈΑ͏ CREATE EXTERNAL TABLE IF NOT
EXISTS `sugoi-service-logs` ( `time` string, `host` string, `method` string, `path` string, `taken_sec` float, `ua` string, `referer` string ) PARTITIONED BY (date string) ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.RegexSerDe' WITH SERDEPROPERTIES ( 'serialization.format' = '1', 'input.regex' = '^time:([^\\t]*)\\thost:([^\\t]*)\\tmethod:([^\\t]*)\\turi: ([^\\t]*)\\ttaken_sec:([^\\t]*)\\tua:([^\\t]*)\\treferer:([^\\t]*)$' ) LOCATION 's3://your-logs-bucket/sugoi-service-logs/' TBLPROPERTIES ('has_encrypted_data'='false') …… -
ใ • S3 ʹͲΜͲΜϩάΛอଘ͢Δͱ͖ɺHive ܗࣜͷσΟϨΫτϦͰ อଘͯ͠ΔͱύʔςΟγϣχϯά͕ࣗಈͰߦΘΕͯศར • ྫ: s3://bucket/somelogs/year=2019/month=1/ day=23/engineer_seminar_001.log
• ͦ͏ͳͬͯͳͯ͘ symlink ͰͳΜͱ͔Ͱ͖Δͱ͍͏͋Δ • https://dev.classmethod.jp/cloud/aws/create-athena-table- with-symboliclink/ -
Amazon Athena ஈ • εΩϟϯͨ͠༰ྔʹରͯ͠՝ۚ (5 USD / 1 TB)
• gzip ѹॖࡁϑΝΠϧͦͷ··ΫΤϦͰ͖Δ • ྻࢤͰͳ͍ϑΝΠϧʹରͯ͠ΫΤϦ͢ΔͳΒɺूܭ ͢ΔΧϥϜΛߜͬͯྉۚઅʹͳΒͳ͍ • ج൫ͱͯ͠׆༻͍ͯ͘͠ײ͡ͳΒɺ ྻࢤϑΝΠϧʹม͢ΔϑΣʔζ͕ͳ͍ͱݫͦ͠͏