つらくないマルチテナンシーを求めて: 全て見せます! SmartHR データベース移行プロジェクトの裏側 / builderscon 2018

606d4feb3bf3293d04816e3938941e66?s=47 Purintai
September 08, 2018

つらくないマルチテナンシーを求めて: 全て見せます! SmartHR データベース移行プロジェクトの裏側 / builderscon 2018

606d4feb3bf3293d04816e3938941e66?s=128

Purintai

September 08, 2018
Tweet

Transcript

  1. 4.
  2. 8.
  3. 10.
  4. 33.
  5. 39.

    BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU %#ίωΫγϣϯΛׂΓ౰ͯΒΕΔ <ύϒϦοΫ%#>
  6. 40.

    BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU ϦΫΤετϔομʔͷ)PTU͔ΒαϒυϝΠϯΛநग़ )PTUFYBNQMFTNBSUISKQ͔ΒFYBNQMFΛऔΓग़͢ <ύϒϦοΫ%#>
  7. 41.

    BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU αϒυϝΠϯFYBNQMFʹରԠ͢Δ ɹςφϯτͷଘࡏΛύϒϦοΫ%#্Ͱ֬ೝ͢Δ
  4&-&$5 '30.AUFOBOUTA 8)&3&ATVCEPNBJOAFYBNQMF <ύϒϦοΫ%#>
  8. 42.

    BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU αϒυϝΠϯFYBNQMFʹରԠ͢ΔϨίʔυΛฦ͢ <ύϒϦοΫ%#>
  9. 43.

    BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU Ϩίʔυʹσʔλϕʔεؚ໊͕·Ε͍ͯΔͷͰ ɹݱࡏ௫ΜͰ͍Δ%#઀ଓͷσʔλϕʔεΛ੾Γସ͑Δ
  VTFQSPEVDUJPO@FYBNQMF <FYBNQMF>
  10. 44.

    BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU BQBSUNFOU͸ϦΫΤετΛॲཧ͢Δؒ ੾Γସ͑தͷςφϯτΛ͓֮͑ͯ͘
  "QBSUNFOU5FOBOUDVSSFOUFYBNQMF <FYBNQMF> <FYBNQMF>
  11. 45.

    <FYBNQMF> BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic

    Beanstalk Amazon S3 BQBSUNFOU FY3FEJTͱ4͸੾Γସ͑தͷςφϯτ*%Λ ͦΕͧΕOBNFTQBDFσΟϨΫτϦύεʹར༻͠෼཭ <FYBNQMF>
  12. 46.

    BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU ϦΫΤετΛॲཧ͢Δ  ϓϥΠϕʔτ%#্ͰϨίʔυૢ࡞ <FYBNQMF> <FYBNQMF>
  13. 47.
  14. 48.

    BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU ݱࡏ௫ΜͰ͍Δ%#ίωΫγϣϯͷ ࢀরઌΛݩʹ໭͢
  VTFTNBSUIS@QSPEVDUJPO <ύϒϦοΫ%#>
  15. 49.

    BQBSUNFOUͷڍಈ 4NBSU)3ͷϚϧνςφϯγʔ ೥౰࣌ Amazon RDS Amazon ElastiCache AWS Elastic Beanstalk

    Amazon S3 BQBSUNFOU %#ίωΫγϣϯΛฦ٫ऴྃ͠ ࣍ͷ)551ϦΫΤετΛ଴ͭʢऴʣ
  16. 57.

    8):

  17. 59.
  18. 77.

    4NBSU)3Ϛϧνςφϯτ೥ද ܧଓར༻཰        

           ࣾ ར༻اۀ਺
  19. 78.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺
  20. 79.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄
  21. 80.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄
  22. 83.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛
  23. 89.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ
  24. 94.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ
  25. 95.
  26. 96.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ
  27. 102.
  28. 107.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ
  29. 112.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ ʁʁʁ
  30. 116.
  31. 117.
  32. 124.
  33. 137.

    ϚΠάϨʔγϣϯ ׬ྃςφϯτ܈ ༏ઌ౓தɾ௿܈ͷਓ͕ΞΫηεͯ͘͠Δࠒʹ͸ ϚΠάϨʔγϣϯ͸ऴΘ͍ͬͯΔʢϋζʣ ༏ઌϚΠάϨʔγϣϯ ະ ׬ NJHSBUJPO ίϯςφ ༏ઌ౓ߴ

    ༏ઌ౓த ༏ઌ౓௿ ৽͍͠ 8FCίϯςφ DPNNFOUT ςʔϒϧ͋Δ ৽͍͠ 8FCίϯςφ DPNNFOUT ςʔϒϧ͋Δ
  34. 150.
  35. 167.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ αʔϏεো֐ൃੜ ఆظϝϯςମ੍ ελʔτ
  36. 168.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ αʔϏεো֐ൃੜ ఆظϝϯςମ੍ ελʔτ %JTDPWFS 4PNFUIJOH/FX
  37. 179.
  38. 194.

    Ҡߦલ ҠߦલͱҠߦޙͷࠩ෼ 1PTUHSF42- $JUVT$MPVE QHHFN BDUJWFSFDPSENVMUJUFOBOUHFN ୯ҰεΩʔϚ ओΩʔ66*% 4ͷΩʔ66*% Ҡߦޙ

    .Z42- "843%4 NZTRMHFN BQBSUNFOUHFN ෼ࢄεΩʔϚ ओΩʔ࿈൪ 4ͷΩʔ࿈൪
  39. 197.
  40. 240.
  41. 243.
  42. 248.
  43. 249.
  44. 252.

    ετΞυϑΝϯΫγϣϯ࡞ͬͨΒ UFYUܥΛͻͨ͢ΒғΉ EPVCMFRVPUFͷΤεέʔϓ໰୊ SELECT t.uuid AS id, '<%= tenant_id %>'

    AS tenant_id, CONCAT('"', t.id, '"') AS old_id, CONCAT('"', DATE_FORMAT(t.created_at, '%Y-%m-%d %T'), '"') AS created_at, CONCAT('"', DATE_FORMAT(t.updated_at, '%Y-%m-%d %T'), '"') AS updated_at, sys.CSVSTR(t.country_number) AS country_number, sys.CSVSTR(t.zip_code) AS zip_code, sys.CSVSTR(t.pref) AS pref, sys.CSVSTR(t.city) AS city, sys.CSVSTR(t.street) AS street, sys.CSVSTR(t.building) AS building, sys.CSVSTR(t.literal_yomi) AS literal_yomi FROM <%= dbname %>.addresses AS t ORDER BY t.id ASC;
  45. 254.

    ফ͑ͨࢀরઌͷςʔϒϧ໊JEςφϯτJEΛ౉͢ͱ 66*%Λฦͯ͘͠ΕΔϋογϡؔ਺ ϦϨʔγϣϯઌͷϨίʔυ͕ͳ͍ DELIMITER // CREATE FUNCTION sys.GHOSTUUID(table_name TEXT, id

    BIGINT, tenant_id TEXT) RETURNS TEXT CHARSET utf8mb4 DETERMINISTIC BEGIN SET @sha2 = SHA2(CONCAT('table_name:',table_name,'::id:',id,'::tenant_id:',tenant_id), 512); SET @uuid = CONCAT( SUBSTR(@sha2 from 10 for 8), '-', SUBSTR(@sha2 from 18 for 4), '-4', SUBSTR(@sha2 from 22 for 3), '-', LOWER(CONV(CONV(SUBSTR(@sha2 from 26 for 1), 16, 10) & 3 | 8, 10, 16)), SUBSTR(@sha2 from 27 for 3), '-', SUBSTR(@sha2 from 40 for 12) ); RETURN @uuid; END; // DELIMITER ;
  46. 255.

    ଘࡏ͠ͳ͍͔΋͠Εͳ͍΍ͭ͸ ͱΓ͋͑ͣғΜͰ͓͜͏ʂ ϦϨʔγϣϯઌͷϨίʔυ͕ͳ͍ SELECT t.uuid AS id, '<%= tenant_id %>'

    AS tenant_id, CONCAT('"', t.id, '"') AS old_id, CONCAT('"', DATE_FORMAT(t.created_at, '%Y-%m-%d %T'), '"') AS created_at, CONCAT('"', DATE_FORMAT(t.updated_at, '%Y-%m-%d %T'), '"') AS updated_at, COALESCE(s.uuid, sys.GHOSTUUID('Payslip', t.payslip_id, '<%= tenant_id %>')) AS payslip_id, sys.CSVSTR(t.name) AS name, t.amount AS amount FROM <%= dbname %>.deductions AS t LEFT OUTER JOIN <%= dbname %>.payslips AS s ON t.payslip_id = s.id ORDER BY t.id ASC ;
  47. 258.
  48. 259.
  49. 260.
  50. 261.
  51. 263.
  52. 264.
  53. 265.
  54. 266.
  55. 267.
  56. 268.
  57. 269.
  58. 275.

    4NBSU)3Ϛϧνςφϯτ೥ද         

        ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ೥ 2 ར༻اۀ਺ 4NBSU)3ެ։ ೥݄ JOJUJBMDPNNJU ೥݄ ฒྻNJHSBUF ࢼ࡞൛ σϓϩΠ໰୊͕ ঃʑʹݦࡏԽ QVSJOUBJೖࣾ ଟஈσϓϩΠͷಋೖ ৳ͼଓ͚Δ σϓϩΠ࣌ؒ αʔϏεো֐ൃੜ ఆظϝϯςମ੍ ελʔτ $JUVTͱͷग़ձ͍ $JUVT$MPVE Ҡߦ1+ελʔτ ίʔυϑϦʔζ❄ $JUVT$MPVE Ҡߦ੒ޭʂ ͝ਗ਼ௌ ͋Γ͕ͱ͏͍͟͝·ͨ͠