Cloud Spanner Schema Migration

Transcript

1. Cloud Spanner Schema Migration Yuki ITO
2. Cloud Spanner ɾFully Managed ɾScalable ɾRelational Database
3. Schema Migration Example Table CREATE TABLE Users ( UserId STRING(36), Name STRING(1024), ) PRIMARY KEY (UserId);
4. Schema Migration After Migration CREATE TABLE Users ( UserId STRING(36), Name STRING(1024), NickName STRING(1024) NOT NULL, ) PRIMARY KEY (UserId);
5. Schema Migration ALTER TABLE Users ADD COLUMN NickName STRING(1024) NOT NULL; That’s all …?
6. Schema Migration Cloud Spanner doesn’t support implicit DEFAULT value
7. Schema Migration Cloud Spanner doesn’t support implicit DEFAULT value We can’t use `NOT NULL` with `ADD COLUMN`
8. Schema Migration Strategy 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore.
9. Schema Migration ɾWrench (Migration tool made by us) ɾSpinnaker (OSS CD tool) Using 2 Tools
10. Schema Migration Wrench ɾPartitioned-DML support ɾSchema version management ɾCreate/Drop/Load Databases ɾCLI tool Written in Go
11. Schema Migration Wrench ɾSchema version management ./db/migrations !"" 000001.sql !"" 000002.sql !"" 000003.sql #"" 000004.sql
12. Schema Migration Wrench ɾSchema version management $ wrench migrate create db/migrations/000005.sql is created
13. Schema Migration Wrench ɾSchema version management ./db/migrations !"" 000001.sql !"" 000002.sql !"" 000003.sql !"" 000004.sql #"" 000005.sql
14. Schema Migration Wrench ɾSchema version management $ wrench migrate up 5/up
15. Schema Migration Wrench CREATE TABLE SchemaMigrations ( Version INT64 NOT NULL, Dirty BOOL NOT NULL, ) PRIMARY KEY(Version); ɾSchema version management
16. Schema Migration Wrench Version Dirty 4 FALSE SchemaMigrations
17. Schema Migration Wrench Version Dirty 5 FALSE SchemaMigrations
18. Schema Migration Continuous Delivery Platform
19. Schema Migration Spinnaker ɾApply Kubernetes manifests automatically – Deployment – Service – Job ɾHook pushing container to registry
20. # Create Tag Cloud Build Hook Container Registry Cloud Spanner Kubernetes Engine Watch Push Container Migration Deploy
21. # Cloud Build Hook Container Registry Kubernetes Engine Watch Push Container Migration Deploy Create Tag Cloud Spanner
22. Schema Migration Migration Deploy Pipeline Cloud Spanner Kubernetes Engine
23. Schema Migration Migration CREATE TABLE Users ( UserId STRING(36), Name STRING(1024), NickName STRING(1024) NOT NULL, ) PRIMARY KEY (UserId);
24. Schema Migration Strategy 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore.
25. Schema Migration UserId Name aaaaaaaa-… Alice bbbbbbbb-… Bob … … Users
26. Schema Migration Strategy 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore.
27. Schema Migration Wrench ./db/migrations #"" 000001.sql # Add column $ wrench migrate create
28. Schema Migration ALTER TABLE Users ADD COLUMN NickName STRING(1024);
29. Schema Migration Migration Deploy Pipeline Cloud Spanner Kubernetes Engine
30. Kubernetes Engine Cloud Spanner ᶃ Fetch `Job` Manifest ᶅ Exec `wrench migrate` ᶄ Run Migration Job
31. Schema Migration apiVersion: batch/v1 kind: Job metadata: name: awesome-job namespace: awesome-namespace spec: activeDeadlineSeconds: 3600 backoffLimit: 1 completions: 1 parallelism: 1 template: spec: containers: - command: - /db/migrate.sh env: - name: SPANNER_INSTANCE_ID value: xxx - name: SPANNER_DATABASE_ID value: xxx - name: GCP_PROJECT_ID value: xxx - name: GOOGLE_APPLICATION_CREDENTIALS value: xxx image: gcr.io/xxxx…
32. Schema Migration #!/bin/sh set -euxo pipefail /wrench \ --project "${GCP_PROJECT_ID}" \ --instance "${PARTNER_SERVICE_SPANNER_INSTANCE_ID}" \ --database "${PARTNER_SERVICE_SPANNER_DATABASE_ID}" \ --directory /db \ migrate up
33. Schema Migration ALTER TABLE Users ADD COLUMN NickName STRING(1024);
34. Schema Migration UserId Name NickName “aaaaaaaa-…” “Alice” (NULL) “bbbbbbbb-…” “Bob” (NULL) … … … Users
35. Schema Migration UserId Name NickName “aaaaaaaa-…” “Alice” (NULL) “bbbbbbbb-…” “Bob” (NULL) “cccccccc-…” “Carol” (NULL) … … … Users New
36. Schema Migration Strategy 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore.
37. Schema Migration Strategy 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore.
38. Schema Migration Migration Deploy Pipeline Cloud Spanner Kubernetes Engine
39. Kubernetes Engine ᶃ Fetch `Deployment` Manifest ᶄ Apply Deployment Manifest (Deploy New Container)
40. Schema Migration UserId Name NickName “aaaaaaaa-…” “Alice (NULL) “bbbbbbbb-…” “Bob” (NULL) “cccccccc-…” “Carol” (NULL) “dddddddd-…” “Dany” “Ddd” … … … Users New
41. Schema Migration UserId Name NickName “aaaaaaaa-…” “Alice (NULL) “bbbbbbbb-…” “Bob” (NULL) “cccccccc-…” “Carol” (NULL) “dddddddd-…” “Dany” “Ddd” … … … Users New
42. Schema Migration Strategy 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore.
43. Schema Migration Strategy 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore.
44. Schema Migration Wrench $ wrench migrate create ./db/migrations !"" 000001.sql # Add column !"" 000002.sql # Update existing rows #"" 000003.sql # Add NULL constraint
45. Schema Migration Wrench UPDATE Users SET NickName = “” WHERE NickName IS NULL;
46. Schema Migration Wrench UPDATE Users SET NickName = “” WHERE NickName IS NULL; as Partitioned-DML
47. Schema Migration Partitioned-DML https://cloud.google.com/spanner/docs/dml-partitioned#partitionable-idempotent “enables large-scale, database-wide operations with minimal impact on concurrent transaction processing
48. Schema Migration Partitioned-DML https://cloud.google.com/spanner/docs/dml-partitioned#partitionable-idempotent
49. Schema Migration Wrench ɾPartitioned-DML support ɾSchema version management ɾCreate/Drop/Load Databases ɾWritten in Go
50. Schema Migration Migration Deploy Pipeline Cloud Spanner Kubernetes Engine
51. Schema Migration UserId Name NickName “aaaaaaaa-…” “Alice “” “bbbbbbbb-…” “Bob” “” “cccccccc-…” “Carol” “” “dddddddd-…” “Dany” “Ddd” … … … Users New
52. Schema Migration Strategy 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore.
53. Schema Migration Strategy 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore.
54. Schema Migration Wrench $ wrench migrate create ./db/migrations !"" 000001.sql # Add column !"" 000002.sql # Update existing rows #"" 000003.sql # Add NULL constraint
55. Schema Migration Wrench ALTER TABLE Users ALTER COLUMN NickName STRING(1024) NOT NULL;
56. Schema Migration Migration Deploy Pipeline Cloud Spanner Kubernetes Engine
57. Schema Migration Complete CREATE TABLE Users ( UserId STRING(36), Name STRING(1024), NickName STRING(1024) NOT NULL, ) PRIMARY KEY (UserId);
58. Schema Migration 3 Phase QA 1. ADD COLUMN without NOT NULL 3. Fill the new columns of existing rows with non NULL value 4. ALTER COLUMN with NOT NULL 2. Deploy application which doesn’t insert new rows with NULL value anymore. QA 1 QA 2 QA 3
59. # Cloud Build Hook Container Registry Kubernetes Engine Watch Push Container Migration Deploy Create Tag Cloud Spanner
60. Kubernetes Engine Cloud Spanner ᶃ Fetch `Job` Manifest ᶅ Exec `wrench migrate` ᶄ Run Migration Job
61. Thank you!!!