cloudspannerecosystem / spanner-dump Goto Github PK

I am working on a schema comparison tool for Spanner and I wonder if we can convert the dumper to a library, so I can utilize it rather than having to reimplement something similar myself.

It looks data are dumped from tables in alphabetic orders therefore sometimes created dump cannot be imported.

CREATE TABLE consumer (
  id STRING(36) NOT NULL,
  merchant_id STRING(36) NOT NULL,
  company_id STRING(36) NOT NULL,
  external_id STRING(36) NOT NULL,
  type STRING(10) NOT NULL,
  status STRING(8),
  created_at TIMESTAMP NOT NULL,
) PRIMARY KEY(id);
CREATE TABLE account (
  id STRING(36) NOT NULL,
  merchant_id STRING(36) NOT NULL,
  company_id STRING(36) NOT NULL,
  type STRING(10) NOT NULL,
  status STRING(8),
  currency STRING(3) NOT NULL,
  balance FLOAT64 DEFAULT (0.0),
  available_amount FLOAT64 DEFAULT (0.0),
  activated_amount FLOAT64 DEFAULT (0.0),
  blocked_amount FLOAT64 DEFAULT (0.0),
  created_at TIMESTAMP NOT NULL,
) PRIMARY KEY(id);
CREATE TABLE account_owner (
  consumer_id STRING(36) NOT NULL,
  account_id STRING(36) NOT NULL,
  FOREIGN KEY(consumer_id) REFERENCES consumer(id),
  FOREIGN KEY(account_id) REFERENCES account(id),
) PRIMARY KEY(consumer_id, account_id);
INSERT INTO `account` (`company_id`, `balance`, `created_at`, `type`, `available_amount`, `id`, `status`, `activated_amount`, `merchant_id`, `currency`, `blocked_amount`) VALUES ("d2297748-10c6-11ee-be56-0242ac120002", 99.99, TIMESTAMP "2023-07-10T11:41:23.3128747Z", "PERSONAL", 0, "282de495-94eb-4d0c-88cc-eabfd1ab246f", "ACTIVE", 0, "2c2f18de-10c6-11ee-be56-0242ac120002", "EUR", 0);
INSERT INTO `account_owner` (`account_id`, `consumer_id`) VALUES ("282de495-94eb-4d0c-88cc-eabfd1ab246f", "791c5120-f641-438a-890e-c0c2686ae9b6");
INSERT INTO `consumer` (`merchant_id`, `status`, `company_id`, `created_at`, `external_id`, `id`, `type`) VALUES 
("2c2f18de-10c6-11ee-be56-0242ac120002", "ACTIVE", "d2297748-10c6-11ee-be56-0242ac120002", TIMESTAMP "2023-07-10T11:41:23.2741788Z", "USER1", "791c5120-f641-438a-890e-c0c2686ae9b6", "INDIVIDUAL");

As you see tables itself are created correctly
consumer, account, account_owner
But data of those tables are exported in alphabetic order of tables:
account, account_owner, consumer
This leads to SQL problems during import because data from account_owner is trying to set foreign key to consumer table record, but this record doesn't exists yet

Hi,

I'm wondering if you can provide docker image were spanner-dump is already installed?

I'm not go-lang developer. I'm trying to install it using instructions from readme file but it doesn't seem work for me. All I get is an error/warning during installation and spanner doesn't seem to be installed.

I have also installed newer Go (1.20) and used following command:

go install github.com/cloudspannerecosystem/spanner-dump@latest

This time installation ended up successfully but spanner-dump command is still unavailable 😞

Just like mysqldump did I want to dump only my specified tables.
Because dump all tables is a waste of time when the size of some unnecessary tables is huge

https://cloud.google.com/spanner/docs/data-types#floating_point_type

When working with floating point numbers, there are special non-numeric values that need to be considered: NaN and +/-inf

FLOAT64 columns can contain NaN, Inf and -Inf value but it is not correctly handled by spanner-dump.

$ spanner-cli
spanner> CREATE TABLE floats(pk INT64, col FLOAT64) PRIMARY KEY(pk);
Query OK, 0 rows affected (11.29 sec)

spanner> INSERT INTO floats(pk, col) VALUES(1, CAST("nan" AS FLOAT64)),(2, CAST("inf" AS FLOAT64)),(3, CAST("-inf" AS FLOAT64));
Query OK, 3 rows affected (0.04 sec)

spanner> SELECT * FROM floats;
+----+------+
| pk | col  |
+----+------+
| 1  | NaN  |
| 2  | +Inf |
| 3  | -Inf |
+----+------+
3 rows in set (31.4 msecs)

$ spanner-dump
...
INSERT INTO `floats` (`pk`, `col`) VALUES (1, NaN), (2, +Inf), (3, -Inf);

This line is not valid DML because Nan and Inf are not Cloud Spanner keywords.

It should be dumped the same value with FORMAT('%T', f)
https://cloud.google.com/spanner/docs/string_functions?hl=en#t_and_t_behavior

Looks like it might be a timezone issue. See the log below:

 go test ./...
--- FAIL: TestDecodeColumn (0.00s)
    --- FAIL: TestDecodeColumn/date (0.00s)
        decoder_test.go:282: DecodeColumn(2018-01-22) = "DATE \"2018-01-22\"", want = "DATE \"2018-01-23\""
    --- FAIL: TestDecodeColumn/array_date (0.00s)
        decoder_test.go:282: DecodeColumn([2018-01-22 2018-01-23]) = "[DATE \"2018-01-22\", DATE \"2018-01-23\"]", want = "[DATE \"2018-01-23\", DATE \"2018-01-24\"]"
FAIL
FAIL	github.com/cloudspannerecosystem/spanner-dump	0.191s
FAIL

I'd like to use the spanner-dump to migrate my schema and data from my production database to the emulator? I'd be great to be able to set the SPANNER_EMULATOR_HOST and be able to import to the emulator.

Unable to exclude version retention settings with DDL export. Is there a work-around for this? I'm unable to import Version Retention settings using spanner-cli.

Generated columns cannot be inserted or updated so there is no reason to dump them. So DML should not contain fields for these columns

https://cloud.google.com/spanner/docs/reference/standard-sql/data-definition-language#create_table

Direct writes to generated columns are not allowed.

CREATE TABLE (
  Name STRING(256) NOT NULL,
  NameLow STRING(256) AS (LOWER(Name)) STORED,
  ...
);

ERROR: spanner: code = "InvalidArgument", desc = "Cannot INSERT value on non-writable column: NameLow