20160730_dbstudychugoku_lt

○○SQL使いなのに
☓☓SQLを強いられている方へ
- 第16回 中国地方DB勉強会 in 岡山 懇親会LT -

AWS DMS(Database Migration Service) の紹介です！

Links:
- https://twitter.com/soudai1025/status/707360183857459200
- https://aws.amazon.com/jp/dms/
- https://aws.amazon.com/about-aws/whats-new/2016/07/aws-database-migration-service-now-supports-continuous-data-replication-with-high-availability-enables-ssl-endpoints-and-adds-support-for-sap-ase-formerly-sap-sybase-ase/
- http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_redshift-and-postgres-sql.html
- http://docs.aws.amazon.com/ja_jp/dms/latest/userguide/CHAP_Source.MySQL.html
- http://docs.aws.amazon.com/ja_jp/dms/latest/userguide/CHAP_Target.Redshift.html
- https://github.com/datacharmer/test_db
- http://dev.mysql.com/doc/employee/en/sakila-structure.html
- https://gist.github.com/twingo-b/c404b2b2d5e9e71d32c0a131c50ea8bc
- https://gist.github.com/twingo-b/96cc16b6479db9da89a46c3df3d9f6d8
- http://tinyurl.com/pgsql-slackin
- https://aws.amazon.com/jp/free/

Transcript

1. ◦◦SQL࢖͍ͳͷʹ ☓☓SQLΛڧ͍ΒΕ͍ͯΔํ΁ - ୈ16ճ தࠃ஍ํDBษڧձ in Ԭࢁ ࠙਌ձLT - 2016/07/30
2. @twingo_b ʮAWSΤΩεύʔτཆ੒ಡຊʯʹدߘ ʮAWSೝఆιϦϡʔγϣϯΞʔΩςΫτ - ϓ ϩϑΣογϣφϧʯऔಘ
3. JPUGதࠃࢧ෦௕ͳͷʹɺ࢓ࣄͰ͸ MySQL͔͠࢖͑ͳ͍ਓ͕͍ΔΒ͍͠ Ͱ͢Ͷ...
4. ͦ͏͍͑͹ɺ͜Μͳ͜ͱݴͬͯ·ͨ͠ https://twitter.com/soudai1025/status/707360183857459200
5. ͦΕɺ AWS DMS(Database Migration Service)Λ࢖͑͹Ͱ͖ΔΑʂ https://aws.amazon.com/jp/dms/
6. ྫ MySQL -> PostgreSQL Microsoft SQL Server -> Amazon Aurora Oracle -> Amazon Aurora Amazon Aurora -> Amazon RedShift
7. ࠓճ͸ʂ
8. Replicate ongoing changes(Change Data Capture:CDC)Λ࢖ͬͯɺ ࠩ෼సૹͯ͠ΈΔ RDS:MySQL5.7 -> DMS -> Redshift https://aws.amazon.com/about-aws/whats-new/2016/07/aws-database-migration-service-now-supports-continuous- data-replication-with-high-availability-enables-ssl-endpoints-and-adds-support-for-sap-ase-formerly-sap-sybase-ase/
9. Redshift? http://docs.aws.amazon.com/ja_jp/redshift/latest/dg/c_redshift-and-postgres-sql.html Amazon Redshift is based on PostgreSQL 8.0.2.
10. ManagementConsoleΛ࢖ͬͯɺ DMSΛηοτΞοϓ http://docs.aws.amazon.com/ja_jp/dms/latest/userguide/CHAP_Source.MySQL.html http://docs.aws.amazon.com/ja_jp/dms/latest/userguide/CHAP_Target.Redshift.html
11. RDS:MySQL5.7ͷαϯϓϧσʔλ͸͜Εʂ https://github.com/datacharmer/test_db http://dev.mysql.com/doc/employee/en/sakila-structure.html
12. ໿400ສϨίʔυɺॳظసૹ͸1෼΄Ͳ RDS5.7:db.t2.micro DMS:dms.t2.micro
13. Redshift΁ͷCOPY΋ࣗಈ Redshift:dc1.large
14. ࣮ࡍʹσʔλΛऔಘͯ͠ΈΔ ྫ୊: • 1990೥ʹޏͬͨਓͷݱࡏڅ༩ҰཡΛऔಘ • ෦ॺ͝ͱͷڅ༩TOP3Λऔಘ
15. MySQL —- 1990೥ʹޏͬͨਓͷݱࡏڅ༩Ұཡ SELECT departments.dept_name, employees.last_name AS manager_last_name, salaries.salary, titles.title, ninety_hire_man_employees.* FROM ( SELECT * FROM employees.employees WHERE hire_date between '1990-01-01' AND '1990-12-31' AND gender = 'M' ) AS ninety_hire_man_employees LEFT JOIN employees.salaries ON ninety_hire_man_employees.emp_no = salaries.emp_no AND salaries.to_date = '9999-01-01' LEFT JOIN employees.titles ON ninety_hire_man_employees.emp_no = titles.emp_no AND titles.to_date = '9999-01-01' LEFT JOIN employees.dept_emp ON ninety_hire_man_employees.emp_no = dept_emp.emp_no AND dept_emp.to_date = '9999-01-01' LEFT JOIN employees.departments ON dept_emp.dept_no = departments.dept_no LEFT JOIN employees.dept_manager ON dept_emp.dept_no = dept_manager.dept_no AND dept_manager.to_date = '9999-01-01' LEFT JOIN employees.employees ON dept_manager.emp_no = employees.emp_no WHERE salaries.salary IS NOT NULL ORDER BY departments.dept_name, salaries.salary DESC; https://gist.github.com/twingo-b/c404b2b2d5e9e71d32c0a131c50ea8bc
16. MySQL •෦ॺ͝ͱͷڅ༩TOP3Λऔಘ ->MySQLͷ୯ମSQLͰ͸࣮૷ࠔ೉ʼʻ
17. Redshift -- 1990೥ʹޏͬͨਓͷݱࡏڅ༩ҰཡΛऔಘ -- ෦ॺ͝ͱͷڅ༩TOP3Λऔಘ WITH ninety_hire_man_employees AS ( SELECT * FROM employees.employees WHERE hire_date BETWEEN '1990-01-01' AND '1990-12-31' AND gender = 'M' ), departments_and_manager AS ( SELECT departments.*, employees.last_name AS manager_last_name FROM employees.departments LEFT JOIN employees.dept_manager ON departments.dept_no = dept_manager.dept_no AND dept_manager.to_date = '9999-01-01' LEFT JOIN employees.employees ON dept_manager.emp_no = employees.emp_no ) SELECT * FROM ( SELECT departments_and_manager.dept_name, departments_and_manager.manager_last_name, RANK ( ) OVER ( PARTITION BY departments_and_manager.dept_name ORDER BY salaries.salary DESC ) AS saraly_rank, salaries.salary, titles.title, ninety_hire_man_employees.* FROM ninety_hire_man_employees LEFT JOIN employees.salaries ON ninety_hire_man_employees.emp_no = salaries.emp_no AND salaries.to_date = '9999-01-01' LEFT JOIN employees.titles ON ninety_hire_man_employees.emp_no = titles.emp_no AND titles.to_date = '9999-01-01' LEFT JOIN employees.dept_emp ON ninety_hire_man_employees.emp_no = dept_emp.emp_no AND dept_emp.to_date = '9999-01-01' LEFT JOIN departments_and_manager ON dept_emp.dept_no = departments_and_manager.dept_no WHERE salaries.salary IS NOT NULL ) WHERE saraly_rank <= 3; https://gist.github.com/twingo-b/96cc16b6479db9da89a46c3df3d9f6d8
18. Redshift • WITHͰINLINE VIEW΍ɺ෦ॺऔಘͷ෦෼ Λ·ͱΊΔ • WINDOWؔ਺ͷRANKΛ࢖ͬͯ෦ॺ͝ͱͷ TOP3Λऔಘ Thanks!: http://tinyurl.com/pgsql-slackin & @masudakz
19. DEMO?
20. DMS͍͍Ͱ͢ΑͶʂ Ͱ΋ɺ͓ߴ͍ΜͰ͠ΐ͏ʁ
21. ແྉ࿮͋Γ·͢Αʂ https://aws.amazon.com/jp/free/ ※DMS͸2016/7/30࣌఺Ͱهࡌ͕֬ೝͰ͖·ͤΜ͕ɺdms.t2.microͷར༻Ͱ͸՝ۚ͞Ε·ͤΜͰͨ͠ɻ
22. Enjoy!