Strategies to Edit Production Data

https://www.devopsdays.org/events/2018-new-york-city/program/julie-qiu/

At some point, we all find ourselves at a SQL prompt making edits to the production database. We know it’s a bad practice, and we always intend to put in place safer infrastructure before we need to do it again, but what does a better system actually look like?

This talk progresses through 5 strategies for teams using a Python stack to do SQL writes against a database, to achieve increasing safety and auditability:

1. Raw SQL queries
2. Local one-off scripts
3. Deploy and run scripts from an application server
4. Run scripts from Jenkins with command line arguments
5. Build a Script Runner application

We’ll talk about the pros and cons of each strategy, and help you determine which one is right for your specific needs.

By the end of this talk, you’ll be ready to start upgrading your infrastructure for making changes to your production database safely!

Transcript

1. Strategies to Edit Production Data Julie Qiu @jqiu25
2. Julie Qiu Senior Software Engineer, Spring @jqiu25
3. Why We Edit Production Data
4. Internal tools are not available.
5. Internal tools are not available. Edge cases exist.
6. Internal tools are not available. Edge cases exist. Fast and easy to make changes.
7. I want to run this query UPDATE products SET name=‘julies-product’ WHERE id=1
8. What I actually ran UPDATE products SET name=‘julies-product’
10. Let’s talk about strategies for safer editing.
11. Raw SQL Local scripts Existing server Task runner Script runner service Strategies for editing
12. How the strategy works What’s great What’s not great Things we’ll discuss
13. Strategy 1: Develop a Review Process for Manual Edits
14. I want to run this query UPDATE products SET name=‘julies-product’ WHERE id=1
15. 1. Add record to a spreadsheet Process for manual edits Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
16. Manual Queries Spreadsheet Name Julie Qiu Date 1/19/2018 Description Marketing team request Query UPDATE products SET name=‘juliies-product’ WHERE id=1 Reviewer Name Codanda Appachu Reviewer Status Approved / Changes Requested Reviewer Comments You spelled Julie wrong
17. 1. Add record to a spreadsheet 2. Reviewer approves the query Process for manual edits Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
19. 1. Add record to a spreadsheet 2. Reviewer approves the query 3. Run the query Process for manual edits Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
20. Run the query inside a transaction BEGIN; UPDATE products SET name=‘julies-product’ WHERE id=1; END; Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
21. • Easy to implement What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
22. • Easy to implement • Audit trail What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
23. • Easy to implement • Audit trail • Promotes the right behaviors What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
24. Data Editing Benefits Raw SQL
25. • Easy to make mistakes What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
26. • Easy to make mistakes • Audit trail is at will What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
27. • Easy to make mistakes • Audit trail is at will • Difficult to run long and complex logic What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
28. Varying this query UPDATE products SET name=‘julies-product’ WHERE id=1
29. UPDATE products SET name=‘julies-product’ FROM brands WHERE brands.name=‘julies-store‘ AND products.status=‘active‘ AND products.name IS NULL A more complex version
30. Strategy 2: Run Scripts Locally
31. 1. Write a script Run scripts locally Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
32. Write the script with your ORM of choice UPDATE products SET name=‘product-1’ WHERE id=1 Script Runner Service Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
33. 1. Write a script 2. Connect to remote database Run scripts locally Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
34. 1. Write a script 2. Connect to remote database 3. Run the script Run scripts locally Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
35. python update_product_name.py --product_name=‘julies-product’ --product_id=1 --dry-run Run the script (but first do a dry-run!) Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
36. python update_product_name.py --product_name=‘julies-product’ --product_id=1 --dry-run Run the script (but do a dry-run!) Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
37. • Reusable What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
38. • Reusable • Easy to manipulate outputs What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
39. • Reusable • Easy to manipulate outputs • Access to common code What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
40. Complex Logic Data Editing Benefits Raw SQL Local Scripts
41. • Easy to make mistakes What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
42. • Easy to make mistakes • Logs are only available locally What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
43. • Easy to make mistakes • Logs are only available locally • Network disconnections What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
44. Varying this query UPDATE products SET name=‘julies-product’ WHERE id=1
45. UPDATE products SET name=‘product-1’ WHERE id=1 UPDATE products SET name=‘product-2’ WHERE id=2 UPDATE products SET name=‘product-3’ WHERE id=3 Doing this 50 million times UPDATE …
47. Strategy 3: Run Scripts on an Existing Server
48. 1. Write a script Run on an existing server Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
49. 1. Write a script 2. Get the script onto a server Run on an existing server Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
50. 1. Write a script 2. Get the script onto a server 3. SSH and run inside a session Run on an existing server Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
55. • Ability to run long scripts What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
56. • Ability to run long scripts • Reliable network connectivity What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
57. • Ability to run long scripts • Reliable network connectivity • Infrastructure already exists What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
58. Long Scripts Complex Logic Data Editing Benefits Raw SQL Existing Server Local Scripts
59. • Scripts can affect resources on your server What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
60. • Scripts can affect resources on your server • Not user friendly What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
61. • Scripts can affect resources on your server • Not user friendly • No persistent audit trail What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
62. Strategy 4: Use a task runner
63. Task Runner Database Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
64. 1. Write a script Use a task runner Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
65. 1. Write a script 2. Code review and run tests Use a task runner Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
66. 1. Write a script 2. Code review and run tests 3. Input arguments and run Use a task runner Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
69. • Persistent audit logs What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
70. • Persistent audit logs • Code review and automated tests What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
71. • Persistent audit logs • Code review and automated tests • User interface What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
72. User Interface Audit Trail Code Review Long Scripts Complex Logic Data Editing Testing Benefits Raw SQL Existing Server Local Scripts Task Runner
73. • Hard to manage credentials What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
74. • Hard to manage credentials • Environments are not clearly separated What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
75. • Hard to manage credentials • Environments are not clearly separated • Inputs are not verified What’s not great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
77. Strategy 5: Build a Script Runner Service
78. User Interface Script Runner (dev) Database (dev) Script Runner (production) Database (production) Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
79. 1. Write a script Script runner service Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
80. 1. Write a script 2. Code review and run tests Script runner service Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
81. 1. Write a script 2. Code review and run tests 3. Choose environment and run Script runner service Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
83. • Centralized configuration management What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
84. • Centralized configuration management • Separation of environments What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
85. • Centralized configuration management • Separation of environments • User friendly interface What’s great Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
86. • Parallelize and scale More things we can do Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
87. • Parallelize and scale • Preview results More things we can do Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
88. • Parallelize and scale • Preview results • … up to you to customize! More things we can do Local Scripts Existing Server Task Runner Script Runner Service Local Scripts Raw SQL Raw SQL Raw SQL
89. Which strategy should I use?
90. Centralize Configs Separate Environ User Interface Audit Trail Code Review Long Scripts Complex Logic Data Editing Testing Benefits … Raw SQL Existing Server Local Scripts Script Runner Service Task Runner Task Runner
91. Make something useable* *because engineers are also people!
92. Invest the effort – it’s worth the cost.
93. Thanks! Julie Qiu @jqiu25