{"id":15428,"date":"2021-01-05T09:13:43","date_gmt":"2021-01-05T08:13:43","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/"},"modified":"2021-01-05T09:13:43","modified_gmt":"2021-01-05T08:13:43","slug":"google-spanner-sql-compatibility","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/","title":{"rendered":"Google Spanner &#8211; SQL compatibility"},"content":{"rendered":"<h2>By Franck Pachot<\/h2>\n<p>.<br \/>\nI have posted, a long time ago, about Google Spanner (<a href=\"https:\/\/www.dbi-services.com\/blog\/google-cloud-spanner-inserting-data\/\" target=\"_blank\" rel=\"noopener noreferrer\">inserting data<\/a> and <a href=\"https:\/\/www.dbi-services.com\/blog\/google-cloud-spanner-no-decimal-numeric-data-types\/\" target=\"_blank\" rel=\"noopener noreferrer\">no decimal numeric data types<\/a>) but many things have changed in this area. There is now a NUMERIC data type and many things have improved in this distributed SQL database, improving a bit the SQL compatibility.<\/p>\n<h3>gcloud<\/h3>\n<p>I can use the Cloud Shell, which is very easy &#8211; one click fron the console &#8211; but here I&#8217;m showing how to install the gcloud CLI temporarily in a OEL environement (I&#8217;ve explained in a <a href=\"https:\/\/www.dbi-services.com\/blog\/always-free-always-up-tmux-in-the-oracle-cloud-with-ksplice-updates\/\" target=\"_blank\" rel=\"noopener noreferrer\">previous post<\/a> how the OCI free tier is my preferred home)<\/p>\n<pre><code>\ncurl https:\/\/dl.google.com\/dl\/cloudsdk\/channels\/rapid\/downloads\/google-cloud-sdk-321.0.0-linux-x86_64.tar.gz | tar -C \/var\/tmp -zxf -\n\/var\/tmp\/google-cloud-sdk\/install.sh --usage-reporting true --screen-reader false --rc-path \/tmp\/gcloud.tmp --command-completion true --path-update true --override-components\n<\/code><\/pre>\n<p>This downloads and unzips the Google Cloud SDK for Linux (there are other options like a YUM repo). I put it in a temporary directory here under \/var\/tmp. install.sh is interactive or you can supply all information on command line. I don&#8217;t want it to update my .bash_profile or .bashrc but want to see what it puts there, so just providing a temporary \/tmp\/gcloud.sh to have a lookt at it<\/p>\n<pre><code>\n[opc@a ~]$ \/var\/tmp\/google-cloud-sdk\/install.sh --usage-reporting true --screen-reader false --rc-path \/tmp\/gcloud.sh --command-completion true --path-update true --override-components\n\nWelcome to the Google Cloud SDK!\n\nYour current Cloud SDK version is: 321.0.0\nThe latest available version is: 321.0.0\n\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502                                                 Components                                                 \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502     Status    \u2502                         Name                         \u2502            ID            \u2502   Size   \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n...\n\u2502 Installed     \u2502 BigQuery Command Line Tool                           \u2502 bq                       \u2502  &lt; 1 MiB \u2502\n\u2502 Installed     \u2502 Cloud SDK Core Libraries                             \u2502 core                     \u2502 15.9 MiB \u2502\n\u2502 Installed     \u2502 Cloud Storage Command Line Tool                      \u2502 gsutil                   \u2502  3.5 MiB \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n\n[opc@a ~]$ cat \/tmp\/gcloud.tmp\n\n# The next line updates PATH for the Google Cloud SDK.\nif [ -f '\/var\/tmp\/google-cloud-sdk\/path.bash.inc' ]; then . '\/var\/tmp\/google-cloud-sdk\/path.bash.inc'; fi\n\n# The next line enables shell command completion for gcloud.\nif [ -f '\/var\/tmp\/google-cloud-sdk\/completion.bash.inc' ]; then . '\/var\/tmp\/google-cloud-sdk\/completion.bash.inc'; fi\n<\/code><\/pre>\n<p>As you can see, I mentioned nothing for &#8211;override-components and the default is gsutil, core and bq<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-23-132315.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignright size-full wp-image-46286\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-23-132315.jpg\" alt=\"\" width=\"180\" height=\"184\" \/><\/a><br \/>\nThis is the most simple cloud CLI I ever seen. Just type: gcloud init<br \/>\n(\/var\/tmp\/google-cloud-sdk\/bin\/gcloud in my temporary installation) and it gives you an URL where you can get the verification code, using the web console authentication. Then you pick the cloud project and, optionally, a default region and zone. Those default informations are stored in: ~\/.config\/gcloud\/configurations\/config_default<br \/>\nand the credentials are in a sqllite database in ~\/.config\/gcloud\/credentials.db<\/p>\n<h3>Sample SQL data<\/h3>\n<p>Gerald Venzl has recently published some <a href=\"http:\/\/free to use data about the world's countries\" target=\"_blank\" rel=\"noopener noreferrer\">free to use data about the world&#8217;s countries<\/a>, capitals, and currencies. What I like with this data set is that, in addition to providing the CSV, Gerald have managed to provide a unique DDL + DML to create this data in SQL databases. And this works in the most common databases despite the fact that, beyond the SQL standard, data types and syntax is different in each engine.<\/p>\n<blockquote class=\"twitter-tweet\" data-width=\"500\" data-dnt=\"true\">\n<p lang=\"en\" dir=\"ltr\">Christmas comes early for <a href=\"https:\/\/twitter.com\/hashtag\/data?src=hash&amp;ref_src=twsrc%5Etfw\">#data<\/a> geeks! Thanks to the help of my lovely wife, I present you with <a href=\"https:\/\/twitter.com\/hashtag\/free?src=hash&amp;ref_src=twsrc%5Etfw\">#free<\/a> to use, <a href=\"https:\/\/twitter.com\/creativecommons?ref_src=twsrc%5Etfw\">@creativecommons<\/a> licensed data about our world&#39;s countries, capitals, and currencies! We hope you enjoy and wish you Happy Holidays!<a href=\"https:\/\/t.co\/zsRpZ4PXP2\">https:\/\/t.co\/zsRpZ4PXP2<\/a> <a href=\"https:\/\/t.co\/yQDTmB6BMv\">pic.twitter.com\/yQDTmB6BMv<\/a><\/p>\n<p>&mdash; Gerald Venzl \ud83d\ude80 (@GeraldVenzl) <a href=\"https:\/\/twitter.com\/GeraldVenzl\/status\/1338327177788411905?ref_src=twsrc%5Etfw\">December 14, 2020<\/a><\/p><\/blockquote>\n<p><script async src=\"https:\/\/platform.twitter.com\/widgets.js\" charset=\"utf-8\"><\/script><\/p>\n<p>Google Spanner has a SQL-like API but I cannot run this without a few changes. But just a few, thanks to many recent improvements. And loading this data set will be the occasion to show those new features.<\/p>\n<h3>Primary key<\/h3>\n<p>Here is the DDL to create the first table, REGIONS:<\/p>\n<pre><code>\n\/*********************************************\/\n\/***************** REGIONS *******************\/\n\/*********************************************\/\nCREATE TABLE regions\n(\n  region_id     VARCHAR(2)   NOT NULL,\n  name          VARCHAR(13)  NOT NULL,\n  CONSTRAINT regions_pk\n    PRIMARY KEY (region_id)\n);\n<\/code><\/pre>\n<p>In order to run this in Google Spanner, I change VARCHAR to <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/data-types#string_type\" target=\"_blank\" rel=\"noopener noreferrer\">STRING<\/a> and I move the PRIMARY KEY declaration out of the relational properties:<\/p>\n<pre><code>\nCREATE TABLE regions\n(\n  region_id     STRING(2)   NOT NULL,\n  name          STRING(13)  NOT NULL\n)\n    PRIMARY KEY (region_id)\n;\n<\/code><\/pre>\n<p>It may be surprising to have the <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/schema-and-data-model#primary_keys\" target=\"_blank\" rel=\"noopener noreferrer\">PRIMARY KEY<\/a> declaration at this place but, because Google Spanner is a distributed database, the primary key is also a storage attribute. And it is mandatory as sharding is done by range partitioning on the primary key. Well, I would prefer to stay compatible with SQL and have, if needed, an additional organization clause. But Spanner is one of the first database trying to bring SQL to NoSQL and was originally designed to be used internally (like Google object storage matadata), providing SQL database benefits (SQL, ACID, joins,&#8230;) with the same scalability as distributed NoSQL databases. So compatibility with other SQL databases was probably not a priority.<\/p>\n<p>Note that the NOT NULL constraint is allowed and we will see more about columns constraints later.<\/p>\n<p>I have removed the comments because this is not allowed in Google Spanner. I don&#8217;t understand that, but remember that it takes its roots in NoSQL where the API calls are embedded in the code, and not in scripts, and the code has its own comments.<\/p>\n<h3>Foreign key<\/h3>\n<p>The second table is COUNTRIES and a country belongs to a region from the REGION table.<\/p>\n<pre><code>\n\/*********************************************\/\n\/**************** COUNTRIES ******************\/\n\/*********************************************\/\nCREATE TABLE countries\n(\n  country_id    VARCHAR(3)     NOT NULL,\n  country_code  VARCHAR(2)     NOT NULL,\n  name          VARCHAR(100)   NOT NULL,\n  official_name VARCHAR(200),\n  population    NUMERIC(10),\n  area_sq_km    NUMERIC(10,2),\n  latitude      NUMERIC(8,5),\n  longitude     NUMERIC(8,5),\n  timezone      VARCHAR(40),\n  region_id     VARCHAR(2)     NOT NULL,\n  CONSTRAINT countries_pk\n    PRIMARY KEY (country_id),\n  CONSTRAINT countries_regions_fk001\n    FOREIGN KEY (region_id) REFERENCES regions (region_id)\n);\n\nCREATE INDEX countries_regions_fk001 ON countries (region_id);\n\n<\/code><\/pre>\n<p>On the datatypes, I&#8217;ll change VARCHAR to STRING and now we have a NUMERIC datatype in Spanner (was only <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/data-types#floating_point_types\" target=\"_blank\" rel=\"noopener noreferrer\">IEEE 754 float<\/a>) but <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/data-types#numeric_type\" target=\"_blank\" rel=\"noopener noreferrer\">NUMERIC<\/a> has a fixed scale and precision (38,9) I&#8217;ll probably come back to it in another post. But there are still many limitations with NUMERIC (cannot create index on it, not easy to map when importing,&#8230;). It is definitely not a good choice here for areas, latitude and longitude. But I keep it just for DDL compatibility.<\/p>\n<p>I move the PRIMARY KEY definition. But the most important here is actually about not changing the referential constraint at all:<\/p>\n<pre><code>\nCREATE TABLE countries\n(\n  country_id    STRING(3)     NOT NULL,\n  country_code  STRING(2)     NOT NULL,\n  name          STRING(100)   NOT NULL,\n  official_name STRING(200),\n  population    NUMERIC,\n  area_sq_km    NUMERIC,\n  latitude      NUMERIC,\n  longitude     NUMERIC,\n  timezone      STRING(40),\n  region_id     STRING(2)     NOT NULL,\n  CONSTRAINT countries_regions_fk001\n    FOREIGN KEY (region_id) REFERENCES regions (region_id)\n)\nPRIMARY KEY (country_id)\n;\n<\/code><\/pre>\n<p>Before <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/release-notes#March_05_2020\" target=\"_blank\" rel=\"noopener noreferrer\">March 2020<\/a> The only possible referential integrity way actually a storage clause (at the same place as the PRIMARY KEY declaration) because referential integrity is not something easy in a distributed database. Because you distribute to scale and that works well only when your transaction is single-shard. This is why, initially, referential integrity was not a FOREIGN KEY but a compound PRIMARY KEY interleaved with the parent PRIMARY KEY. But we will see INTERLEAVE IN PARENT later. Here, COUTRIES has its own primary key, and then its own sharding scheme. That&#8217; also mean that inserting a new country may have to check the parent key (region) in another shard. We will look at performance in another post.<\/p>\n<p>When we declare a foreign key in Google Spanner, an index on it is created, then I didn&#8217;t copy the CREATE INDEX statement.<\/p>\n<h3>Check constraints<\/h3>\n<pre><code>\n\/*********************************************\/\n\/***************** CITIES ********************\/\n\/*********************************************\/\n\nCREATE TABLE cities\n(\n  city_id       VARCHAR(7)    NOT NULL,\n  name          VARCHAR(100)  NOT NULL,\n  official_name VARCHAR(200),\n  population    NUMERIC(8),\n  is_capital    CHAR(1)       DEFAULT 'N' NOT NULL,\n  latitude      NUMERIC(8,5),\n  longitude     NUMERIC(8,5),\n  timezone      VARCHAR(40),\n  country_id    VARCHAR(3)    NOT NULL,\n  CONSTRAINT cities_pk\n    PRIMARY KEY (city_id),\n  CONSTRAINT cities_countries_fk001\n    FOREIGN KEY (country_id) REFERENCES countries (country_id),\n  CONSTRAINT cities_is_capital_Y_N_check001\n    CHECK (is_capital IN ('Y','N'))\n);\n\nCREATE INDEX cities_countries_fk001 ON cities (country_id);\n<\/code><\/pre>\n<p>In addition to the datatypes we have seen earlier I&#8217;ll transform CHAR to STRING, but I have to remove the DEFAULT declaration. Spanner recently introduced <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/release-notes#October_13_2020\" target=\"_blank\" rel=\"noopener noreferrer\">generated columns<\/a> but those are always calculated, they cannot substitute to DEFAULT.<br \/>\nWe declare check constraints since <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/release-notes#October_13_2020\" target=\"_blank\" rel=\"noopener noreferrer\">Oct. 2020<\/a> and I keep the same declaration. As far as I know they are not used by the optimizer but only to validate the data ingested.<\/p>\n<p>I have a foreign key to COUNTRIES which I keep as non-interleaved. Because the COUNTRY_ID is not part of the CITIES primary key, and maybe because my data model may have to cope with cities changing to another country (geopolitical immutability).<\/p>\n<pre><code>\nCREATE TABLE cities\n(\n  city_id       STRING(7)    NOT NULL,\n  name          STRING(100)  NOT NULL,\n  official_name STRING(200),\n  population    NUMERIC,\n  is_capital    STRING(1)       NOT NULL,\n  latitude      NUMERIC,\n  longitude     NUMERIC,\n  timezone      STRING(40),\n  country_id    STRING(3)    NOT NULL,\n  CONSTRAINT cities_countries_fk001\n    FOREIGN KEY (country_id) REFERENCES countries (country_id),\n  CONSTRAINT cities_is_capital_Y_N_check001\n    CHECK (is_capital IN ('Y','N'))\n)\n    PRIMARY KEY (city_id)\n;\n<\/code><\/pre>\n<p>Again, the index on COUNTRY_ID is created implicitely.<\/p>\n<h3>Interleaved<\/h3>\n<pre><code>\n\/*********************************************\/\n\/***************** CURRENCIES ****************\/\n\/*********************************************\/\nCREATE TABLE currencies\n(\n  currency_id       VARCHAR(3)    NOT NULL,\n  name              VARCHAR(50)   NOT NULL,\n  official_name     VARCHAR(200),\n  symbol            VARCHAR(18)   NOT NULL,\n  CONSTRAINT currencies_pk\n    PRIMARY KEY (currency_id)\n);\n\/*********************************************\/\n\/*********** CURRENCIES_COUNTRIES ************\/\n\/*********************************************\/\nCREATE TABLE currencies_countries\n(\n  currency_id    VARCHAR(3)   NOT NULL,\n  country_id     VARCHAR(3)   NOT NULL,\n  CONSTRAINT currencies_countries_pk\n    PRIMARY KEY (currency_id, country_id),\n  CONSTRAINT currencies_countries_currencies_fk001\n    FOREIGN KEY (currency_id) REFERENCES currencies (currency_id),\n  CONSTRAINT currencies_countries_countries_fk002\n    FOREIGN KEY (country_id)  REFERENCES countries(country_id)\n);\n<\/code><\/pre>\n<p>The CURRENCIES_COUNTRIES is the implementation of many-to-many relationship as a country may have multiple currencies and a currency can be used in multiple country. The primary key is a concatenation of the foreign keys. Here I&#8217;ll decide that referential integrity is a bit stronger and the list of currencies will be stored in each country, like storing it pre-joined for performance reason. If you come from Oracle, you may see this INTERLEAVE IN PARENT as a <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/21\/cncpt\/tables-and-table-clusters.html#GUID-04AADD81-E5C2-498B-B857-DF2A37DD3520\" target=\"_blank\" rel=\"noopener noreferrer\">table CLUSTER<\/a> but on a partitioned table. If you come from DynamoDB you may see the of it as the <a href=\"https:\/\/docs.aws.amazon.com\/amazondynamodb\/latest\/developerguide\/bp-adjacency-graphs.html\" target=\"_blank\" rel=\"noopener noreferrer\">Adjacency lists<\/a> modeling in the single-table design.<\/p>\n<pre><code>\nCREATE TABLE currencies\n(\n  currency_id       STRING(3)    NOT NULL,\n  name              STRING(50)   NOT NULL,\n  official_name     STRING(200),\n  symbol            STRING(18)   NOT NULL,\n)\n    PRIMARY KEY (currency_id)\n;\n\nCREATE TABLE currencies_countries\n(\n  currency_id    STRING(3)   NOT NULL,\n  country_id     STRING(3)   NOT NULL,\n  CONSTRAINT currencies_countries_countries_fk002\n    FOREIGN KEY (country_id)  REFERENCES countries(country_id)\n)\nPRIMARY KEY (currency_id, country_id)\n,  INTERLEAVE IN PARENT currencies ON DELETE CASCADE;\n<\/code><\/pre>\n<p>Here, the many-to-many association between CURRENCIES and COUNTRIES is materialized as a composition with CURRENCIES, stored with it (INTERLEAVE), and removed with it (ON DELETE CASCADE). Note that I did that for the demo because CURRENCY_ID was first in the primary key declaration, but you may think more about data distribution and lifecycle when deciding on interleaving. Google Spanner partitions by range, and this means that all CURRENCIES_COUNTRIES associations will be stored together, in the same shard (called &#8220;split&#8221; in Spanner) for the same CURRENCY_ID.<\/p>\n<h3>Multi-region instance<\/h3>\n<p>There are many new multi-region configurations, within the same continent or distributed over multiple ones:<\/p>\n<pre><code>\ngcloud spanner instance-configs list\n\nNAME                              DISPLAY_NAME\nasia1                             Asia (Tokyo\/Osaka\/Seoul)\neur3                              Europe (Belgium\/Netherlands)\neur5                              Europe (London\/Belgium\/Netherlands)\neur6                              Europe (Netherlands, Frankfurt)\nnam-eur-asia1                     United States, Europe, and Asia (Iowa\/Oklahoma\/Belgium\/Taiwan)\nnam10                             United States (Iowa\/Salt Lake\/Oklahoma)\nnam11                             United States (Iowa, South Carolina, Oklahoma)\nnam3                              United States (Northern Virginia\/South Carolina)\nnam6                              United States (Iowa\/South Carolina\/Oregon\/Los Angeles)\nnam7                              United States (Iowa, Northern Virginia, Oklahoma)\nnam8                              United States (Los Angeles, Oregon, Salt Lake City)\nnam9                              United States (Northern Virginia, Iowa, South Carolina, Oregon)\nregional-asia-east1               asia-east1\nregional-asia-east2               asia-east2\nregional-asia-northeast1          asia-northeast1\nregional-asia-northeast2          asia-northeast2\nregional-asia-northeast3          asia-northeast3\nregional-asia-south1              asia-south1\nregional-asia-southeast1          asia-southeast1\nregional-asia-southeast2          asia-southeast2\nregional-australia-southeast1     australia-southeast1\nregional-europe-north1            europe-north1\nregional-europe-west1             europe-west1\nregional-europe-west2             europe-west2\nregional-europe-west3             europe-west3\nregional-europe-west4             europe-west4\nregional-europe-west6             europe-west6\nregional-northamerica-northeast1  northamerica-northeast1\nregional-southamerica-east1       southamerica-east1\nregional-us-central1              us-central1\nregional-us-east1                 us-east1\nregional-us-east4                 us-east4\nregional-us-west1                 us-west1\nregional-us-west2                 us-west2\nregional-us-west3                 us-west3\nregional-us-west4                 us-west4\n<\/code><\/pre>\n<p>I&#8217;ll use the latest dual-region in my continent, eur6, added on <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/release-notes#December_17_2020\" target=\"_blank\" rel=\"noopener noreferrer\">Dec. 2020<\/a> which has two read-write regions (Netherlands, Frankfurt) and the witness region in Zurich. Yes, this neutral position of Switzerland is a perfect fit in the distributed quorum, isn&#8217;t it? \ud83d\ude09<\/p>\n<pre><code>\ntime gcloud spanner instances create franck --config eur6 --nodes=3 --description Franck\n\nCreating instance...done.\n\nreal    0m5.128s\nuser    0m0.646s\nsys     0m0.079s\n<\/code><\/pre>\n<p>The instance is created, and we can create multiple database in it (there is no schemas, so database is the right logical isolation between database objects)<\/p>\n<pre><code>\ntime gcloud spanner databases create test --instance=franck --ddl \"\nCREATE TABLE regions\n(\n  region_id     STRING(2)   NOT NULL,\n  name          STRING(13)  NOT NULL\n)\n    PRIMARY KEY (region_id)\n;\nCREATE TABLE countries\n(\n  country_id    STRING(3)     NOT NULL,\n  country_code  STRING(2)     NOT NULL,\n  name          STRING(100)   NOT NULL,\n  official_name STRING(200),\n  population    NUMERIC,\n  area_sq_km    NUMERIC,\n  latitude      NUMERIC,\n  longitude     NUMERIC,\n  timezone      STRING(40),\n  region_id     STRING(2)     NOT NULL,\n  CONSTRAINT countries_regions_fk001\n    FOREIGN KEY (region_id) REFERENCES regions (region_id)\n)\nPRIMARY KEY (country_id)\n;\nCREATE TABLE cities\n(\n  city_id       STRING(7)    NOT NULL,\n  name          STRING(100)  NOT NULL,\n  official_name STRING(200),\n  population    NUMERIC,\n  is_capital    STRING(1)       NOT NULL,\n  latitude      NUMERIC,\n  longitude     NUMERIC,\n  timezone      STRING(40),\n  country_id    STRING(3)    NOT NULL,\n  CONSTRAINT cities_countries_fk001\n    FOREIGN KEY (country_id) REFERENCES countries (country_id),\n  CONSTRAINT cities_is_capital_Y_N_check001\n    CHECK (is_capital IN ('Y','N'))\n)\n    PRIMARY KEY (city_id)\n;\nCREATE TABLE currencies\n(\n  currency_id       STRING(3)    NOT NULL,\n  name              STRING(50)   NOT NULL,\n  official_name     STRING(200),\n  symbol            STRING(18)   NOT NULL,\n)\n    PRIMARY KEY (currency_id)\n;\nCREATE TABLE currencies_countries\n(\n  currency_id    STRING(3)   NOT NULL,\n  country_id     STRING(3)   NOT NULL,\n  CONSTRAINT currencies_countries_countries_fk002\n    FOREIGN KEY (country_id)  REFERENCES countries(country_id)\n)\nPRIMARY KEY (currency_id, country_id)\n,  INTERLEAVE IN PARENT currencies ON DELETE CASCADE;\n\"\n\nERROR: (gcloud.spanner.databases.create) INVALID_ARGUMENT: Error parsing Spanner DDL statement: \\n : Syntax error on line 1, column 1: Encountered \\'EOF\\' while parsing: ddl_statement\n- '@type': type.googleapis.com\/google.rpc.LocalizedMessage\n  locale: en-US\n  message: |-\n    Error parsing Spanner DDL statement:\n     : Syntax error on line 1, column 1: Encountered 'EOF' while parsing: ddl_statement\n<\/code><\/pre>\n<p>No idea why it didn&#8217;t parse my DDL. The same in the console works, so this is how I&#8217;ve created those tables. Now re-reading this and the problem may be only that I start with an newline here before the first CREATE.<\/p>\n<p>My database is ready for DDL and DML, provisioned in a few seconds.<\/p>\n<h3>Foreign Key indexes<\/h3>\n<p>You mave seen that I removed the CREATE INDEX that was in the original DDL. This is because an index is created automatically on the foreign key (which makes sense, we want to avoid full table scans at all prize in a distributed database).<\/p>\n<pre><code>\ngcloud spanner databases ddl update test --instance=franck --ddl=\"CREATE INDEX countries_regions_fk001 ON countries (region_id)\"\n\nSchema updating...failed.\nERROR: (gcloud.spanner.databases.ddl.update) Duplicate name in schema: countries_regions_fk001.\n<\/code><\/pre>\n<p>Trying to create an index with the same name as the foreign key is rejected as &#8220;duplicate name&#8221;. However, when looking at the ddl:<\/p>\n<pre><code>\ngcloud spanner databases ddl describe test --instance=franck | grep -i \"CREATE INDEX\"\n<\/code><\/pre>\n<p>Shows no index. And trying to query explicitly through this index with this name is rejected:<\/p>\n<pre><code>\ngcloud spanner databases execute-sql test --instance=franck --query-mode=PLAN \\\n --sql \"select count(region_id) from countries@{FORCE_INDEX=countries_regions_fk001}\"\n\nERROR: (gcloud.spanner.databases.execute-sql) INVALID_ARGUMENT: The table countries does not have a secondary index called countries_regions_f\nk001\n<\/code><\/pre>\n<p>However this index exists, with another name. Let&#8217;s do an explain plan when querying this column only:<\/p>\n<pre><code>\ngcloud spanner databases execute-sql test --instance=franck --query-mode=PLAN \\\n --sql \"select count(region_id) from countries\"\n\n RELATIONAL Serialize Result\n    |\n    +- RELATIONAL Aggregate\n    |  call_type: Global, iterator_type: Stream, scalar_aggregate: true\n    |   |\n    |   +- RELATIONAL Distributed Union\n    |   |  subquery_cluster_node: 3\n    |   |   |\n    |   |   +- RELATIONAL Aggregate\n    |   |   |  call_type: Local, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |\n    |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |  call_type: Local, subquery_cluster_node: 5\n    |   |   |   |   |\n    |   |   |   |   \\- RELATIONAL Scan\n    |   |   |   |      Full scan: true, scan_target: IDX_countries_region_id_1D2B1A686087F93F, scan_type: IndexScan\n    |   |   |   |\n    |   |   |   \\- SCALAR Function\n    |   |   |      COUNT(1)\n    |   |   |       |\n    |   |   |       \\- SCALAR Constant\n    |   |   |          1\n    |   |   |\n    |   |   \\- SCALAR Constant\n    |   |      true\n    |   |\n    |   \\- SCALAR Function\n    |      COUNT_FINAL($v1)\n    |       |\n    |       \\- SCALAR Reference\n    |          $v1\n    |\n    \\- SCALAR Reference\n       $agg1\n<\/code><\/pre>\n<p>The foreign key index name is: IDX_countries_region_id_1D2B1A686087F93F<\/p>\n<pre><code>\ngcloud spanner databases execute-sql test --instance=franck --query-mode=PLAN \\\n --sql \"select count(region_id) from countries@{FORCE_INDEX=IDX_countries_region_id_1D2B1A686087F93F}\" \\\n | grep scan\n\n    |   |   |   |      Full scan: true, scan_target: IDX_countries_region_id_1D2B1A686087F93F, scan_type: IndexScan\n<\/code><\/pre>\n<p>I can use this index name here. Apparently, the index that is implicitly created with the foreign key has an internal name, but also reserves the constraint name in the index namespace. Let&#8217;s validate that the initial error was about the name of the index and not the same columns:<\/p>\n<pre><code>\ngcloud spanner databases ddl update test --instance=franck \\\n --ddl=\"CREATE INDEX a_silly_redundant_index ON countries (region_id)\"\n\nSchema updating...done.\n<\/code><\/pre>\n<p>This works, I can create a secondary index on the foreign key columns<\/p>\n<pre><code>\ngcloud spanner databases execute-sql test --instance=franck --query-mode=PLAN \\\n --sql \"select count(region_id) from countries\"\\\n | grep scan\n\n    |   |   |   |      Full scan: true, scan_target: a_silly_redundant_index, scan_type: IndexScan\n<\/code><\/pre>\n<p>Querying without mentioning the index shows that it can be used instead of the implicit one. Yes, Google Spanner has now an optimizer that can decide to use a secondary index. But I&#8217;ll come back to that in a future blog post. Why using this new index rather than the one created implicitly, as they have the same cost? I don&#8217;t know but I&#8217;ve started it with an &#8220;a&#8221; in case alphabetical order wins&#8230;<\/p>\n<pre><code>\ngcloud spanner databases ddl describe test --instance=franck | grep -i \"CREATE INDEX\"\n\nCREATE INDEX a_silly_redundant_index ON countries(region_id);\n\ngcloud spanner databases ddl update test --instance=franck \\\n --ddl=\"DROP INDEX a_silly_redundant_index\"\n\nSchema updating...done.\n<\/code><\/pre>\n<p>I remove this index which has no reason to be there.<\/p>\n<h3>insert<\/h3>\n<p>Executing multiple DML statements at once is not possible. It is possible to insert multiple rows into one table like this:<\/p>\n<pre><code>\nINSERT INTO regions (region_id, name) VALUES\n   ('AF', 'Africa')\n , ('AN', 'Antarctica')\n , ('AS', 'Asia')\n , ('EU', 'Europe')\n , ('NA', 'North America')\n , ('OC', 'Oceania')\n , ('SA', 'South America')\n;\n<\/code><\/pre>\n<p>However, the initial SQL file doesn&#8217;t insert into the same number of columns, like:<\/p>\n<pre><code>\nINSERT INTO cities (city_id, name, population, is_capital, latitude, longitude, country_id) VALUES ('AFG0001', 'Kabul', 4012000, 'Y', 34.52813, 69.17233, 'AFG');\nINSERT INTO cities (city_id, name, official_name, population, is_capital, latitude, longitude, country_id) VALUES ('ATG0001', 'Saint John''s', 'Saint John\u2019s', 21000, 'Y', 17.12096, -61.84329, 'ATG');\nINSERT INTO cities (city_id, name, population, is_capital, latitude, longitude, country_id) VALUES ('ALB0001', 'Tirana', 476000, 'Y', 41.3275, 19.81889, 'ALB');\n<\/code><\/pre>\n<p>and then a quick replace is not easy. On this example you see something else. There are double quotes, the SQL way to escape quotes, but Spanner doesn&#8217;t like it.<\/p>\n<p>Then, without writing a program to insert in bulk, here is what I did to keep the SQL statements from the source:<\/p>\n<pre><code>\ncurl -s https:\/\/raw.githubusercontent.com\/gvenzl\/sample-data\/master\/countries-cities-currencies\/install.sql \\\n | awk \"\/^INSERT INTO \/{gsub(\/''\/,quote);print}\" quote=\"\\\\\\\\\\\\\\'\" \\\n | while read sql ; do echo \"$sql\" ; gcloud spanner databases execute-sql test --instance=franck --sql=\"$sql\" ; done \\\n | ts | tee insert.log\n<\/code><\/pre>\n<p>I just replaced the quote escaping &#8221; by \/\/. It is slow row-by-row but all rows are inserted with the original SQL.<\/p>\n<p><a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-46346\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg\" alt=\"\" width=\"2560\" height=\"1442\" \/><\/a><\/p>\n<p>I have 3 nodes running, two in read-write in two regions (europe-west3 is &#8220;Frankfurt&#8221;, europe-west4 is &#8220;Netherlands&#8221;) and one acting as witness for the quorum (europe-west6 in Z\u00fcrich).<br \/>\n<a href=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-235720.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-46350\" src=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-235720.jpg\" alt=\"\" width=\"2472\" height=\"988\" \/><\/a><\/p>\n<h3>select<\/h3>\n<p>With the sample data provided by Gerald, there&#8217;s a query to check the number of rows:<\/p>\n<pre><code>\ngcloud spanner databases execute-sql test --instance=franck --query-mode=NORMAL --sql \"SELECT 'regions' AS Table, 7 AS provided,\nCOUNT(1) AS actual FROM regions                                                                                                               \nUNION ALL\nSELECT 'countries' AS Table, 196 AS provided, COUNT(1) AS actual FROM countries\nUNION ALL\nSELECT 'cities' AS Table, 204 AS provided, COUNT(1) AS actual FROM cities\nUNION ALL\nSELECT 'currencies' AS Table, 146 AS provided, COUNT(1) AS actual FROM currencies\nUNION ALL\nSELECT 'currencies_countries' AS Table, 203 AS provided, COUNT(1) AS actual FROM currencies_countries;\n\"\n\nTable                 provided  actual\nregions               7         7\ncountries             196       196\ncities                204       204\ncurrencies            146       146\ncurrencies_countries  203       203\n<\/code><\/pre>\n<p>The result is correct. By curiosity, I&#8217;m running it with execution plan and statistics (query mode &#8220;PROFILE&#8221;):<\/p>\n<pre><code>\n\ngcloud spanner databases execute-sql test --instance=franck --query-mode=PROFILE --sql \"SELECT 'regions' AS Table, 7 AS provided,\n COUNT(1) AS actual FROM regions\n UNION ALL\n SELECT 'countries' AS Table, 196 AS provided, COUNT(1) AS actual FROM countries\n UNION ALL\n SELECT 'cities' AS Table, 204 AS provided, COUNT(1) AS actual FROM cities\n UNION ALL\n SELECT 'currencies' AS Table, 146 AS provided, COUNT(1) AS actual FROM currencies\n UNION ALL\n SELECT 'currencies_countries' AS Table, 203 AS provided, COUNT(1) AS actual FROM currencies_countries;\n \"\n\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 TOTAL_ELAPSED_TIME \u2502  CPU_TIME  \u2502 ROWS_RETURNED \u2502 ROWS_SCANNED \u2502 OPTIMIZER_VERSION \u2502\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 14.7 msecs         \u2502 4.92 msecs \u2502 5             \u2502 756          \u2502 2                 \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n RELATIONAL Serialize Result\n (1 execution, 0.45 msecs total latency)\n    |\n    +- RELATIONAL Union All\n    |  (1 execution, 0.44 msecs total latency)\n    |   |\n    |   +- RELATIONAL Union Input\n    |   |   |\n    |   |   +- RELATIONAL Compute\n    |   |   |   |\n    |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |  (1 execution, 0.06 msecs total latency)\n    |   |   |   |  call_type: Global, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |\n    |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |  (1 execution, 0.06 msecs total latency)\n    |   |   |   |   |  subquery_cluster_node: 6\n    |   |   |   |   |   |\n    |   |   |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |   |   |  (1 execution, 0.04 msecs total latency)\n    |   |   |   |   |   |  call_type: Local, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |   |   |  (1 execution, 0.04 msecs total latency)\n    |   |   |   |   |   |   |  call_type: Local, subquery_cluster_node: 8\n    |   |   |   |   |   |   |   |\n    |   |   |   |   |   |   |   \\- RELATIONAL Scan\n    |   |   |   |   |   |   |      (1 execution, 0.03 msecs total latency)\n    |   |   |   |   |   |   |      Full scan: true, scan_target: regions, scan_type: TableScan\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   \\- SCALAR Function\n    |   |   |   |   |   |      COUNT(1)\n    |   |   |   |   |   |       |\n    |   |   |   |   |   |       \\- SCALAR Constant\n    |   |   |   |   |   |          1\n    |   |   |   |   |   |\n    |   |   |   |   |   \\- SCALAR Constant\n    |   |   |   |   |      true\n    |   |   |   |   |\n    |   |   |   |   \\- SCALAR Function\n    |   |   |   |      COUNT_FINAL($v1)\n    |   |   |   |       |\n    |   |   |   |       \\- SCALAR Reference\n    |   |   |   |          $v1\n    |   |   |   |\n    |   |   |   +- SCALAR Constant\n    |   |   |   |  'regions'\n    |   |   |   |\n    |   |   |   \\- SCALAR Constant\n    |   |   |      7\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $Table\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $provided\n    |   |   |\n    |   |   \\- SCALAR Reference\n    |   |      $actual\n    |   |\n    |   +- RELATIONAL Union Input\n    |   |   |\n    |   |   +- RELATIONAL Compute\n    |   |   |   |\n    |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |  (1 execution, 0.1 msecs total latency)\n    |   |   |   |  call_type: Global, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |\n    |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |  (1 execution, 0.1 msecs total latency)\n    |   |   |   |   |  subquery_cluster_node: 23\n    |   |   |   |   |   |\n    |   |   |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |   |   |  (1 execution, 0.09 msecs total latency)\n    |   |   |   |   |   |  call_type: Local, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |   |   |  (1 execution, 0.09 msecs total latency)\n    |   |   |   |   |   |   |  call_type: Local, subquery_cluster_node: 25\n    |   |   |   |   |   |   |   |\n    |   |   |   |   |   |   |   \\- RELATIONAL Scan\n    |   |   |   |   |   |   |      (1 execution, 0.08 msecs total latency)\n    |   |   |   |   |   |   |      Full scan: true, scan_target: IDX_countries_region_id_1D2B1A686087F93F, scan_type: IndexScan\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   \\- SCALAR Function\n    |   |   |   |   |   |      COUNT(1)\n    |   |   |   |   |   |       |\n    |   |   |   |   |   |       \\- SCALAR Constant\n    |   |   |   |   |   |          1\n    |   |   |   |   |   |\n    |   |   |   |   |   \\- SCALAR Constant\n    |   |   |   |   |      true\n    |   |   |   |   |\n    |   |   |   |   \\- SCALAR Function\n    |   |   |   |      COUNT_FINAL($v2)\n    |   |   |   |       |\n    |   |   |   |       \\- SCALAR Reference\n    |   |   |   |          $v2\n    |   |   |   |\n    |   |   |   +- SCALAR Constant\n    |   |   |   |  'countries'\n    |   |   |   |\n    |   |   |   \\- SCALAR Constant\n    |   |   |      196\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $Table_1\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $provided_1\n    |   |   |\n    |   |   \\- SCALAR Reference\n    |   |      $actual_1\n    |   |\n    |   +- RELATIONAL Union Input\n    |   |   |\n    |   |   +- RELATIONAL Compute\n    |   |   |   |\n    |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |  (1 execution, 0.12 msecs total latency)\n    |   |   |   |  call_type: Global, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |\n    |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |  (1 execution, 0.11 msecs total latency)\n    |   |   |   |   |  subquery_cluster_node: 40\n    |   |   |   |   |   |\n    |   |   |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |   |   |  (1 execution, 0.11 msecs total latency)\n    |   |   |   |   |   |  call_type: Local, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |   |   |  (1 execution, 0.1 msecs total latency)\n    |   |   |   |   |   |   |  call_type: Local, subquery_cluster_node: 42\n    |   |   |   |   |   |   |   |\n    |   |   |   |   |   |   |   \\- RELATIONAL Scan\n    |   |   |   |   |   |   |      (1 execution, 0.09 msecs total latency)\n    |   |   |   |   |   |   |      Full scan: true, scan_target: IDX_cities_country_id_35A7C9365B4BF943, scan_type: IndexScan\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   \\- SCALAR Function\n    |   |   |   |   |   |      COUNT(1)\n    |   |   |   |   |   |       |\n    |   |   |   |   |   |       \\- SCALAR Constant\n    |   |   |   |   |   |          1\n    |   |   |   |   |   |\n    |   |   |   |   |   \\- SCALAR Constant\n    |   |   |   |   |      true\n    |   |   |   |   |\n    |   |   |   |   \\- SCALAR Function\n    |   |   |   |      COUNT_FINAL($v3)\n    |   |   |   |       |\n    |   |   |   |       \\- SCALAR Reference\n    |   |   |   |          $v3\n    |   |   |   |\n    |   |   |   +- SCALAR Constant\n    |   |   |   |  'cities'\n    |   |   |   |\n    |   |   |   \\- SCALAR Constant\n    |   |   |      204\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $Table_2\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $provided_2\n    |   |   |\n    |   |   \\- SCALAR Reference\n    |   |   |\n    |   |   \\- SCALAR Reference\n    |   |      $actual_2\n    |   |\n    |   +- RELATIONAL Union Input\n    |   |   |\n    |   |   +- RELATIONAL Compute\n    |   |   |   |\n    |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |  (1 execution, 0.08 msecs total latency)\n    |   |   |   |  call_type: Global, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |\n    |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |  (1 execution, 0.08 msecs total latency)\n    |   |   |   |   |  subquery_cluster_node: 57\n    |   |   |   |   |   |\n    |   |   |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |   |   |  (1 execution, 0.07 msecs total latency)\n    |   |   |   |   |   |  call_type: Local, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |   |   |  (1 execution, 0.07 msecs total latency)\n    |   |   |   |   |   |   |  call_type: Local, subquery_cluster_node: 59\n    |   |   |   |   |   |   |   |\n    |   |   |   |   |   |   |   \\- RELATIONAL Scan\n    |   |   |   |   |   |   |      (1 execution, 0.06 msecs total latency)\n    |   |   |   |   |   |   |      Full scan: true, scan_target: currencies, scan_type: TableScan\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   \\- SCALAR Function\n    |   |   |   |   |   |      COUNT(1)\n    |   |   |   |   |   |       |\n    |   |   |   |   |   |       \\- SCALAR Constant\n    |   |   |   |   |   |          1\n    |   |   |   |   |   |\n    |   |   |   |   |   \\- SCALAR Constant\n    |   |   |   |   |      true\n    |   |   |   |   |\n    |   |   |   |   \\- SCALAR Function\n    |   |   |   |      COUNT_FINAL($v4)\n    |   |   |   |       |\n    |   |   |   |       \\- SCALAR Reference\n    |   |   |   |          $v4\n    |   |   |   |          $v4                                                                                                       [49\/9050]\n    |   |   |   |\n    |   |   |   +- SCALAR Constant\n    |   |   |   |  'currencies'\n    |   |   |   |\n    |   |   |   \\- SCALAR Constant\n    |   |   |      146\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $Table_3\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $provided_3\n    |   |   |\n    |   |   \\- SCALAR Reference\n    |   |      $actual_3\n    |   |\n    |   +- RELATIONAL Union Input\n    |   |   |\n    |   |   +- RELATIONAL Compute\n    |   |   |   |\n    |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |  (1 execution, 0.08 msecs total latency)\n    |   |   |   |  call_type: Global, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |\n    |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |  (1 execution, 0.08 msecs total latency)\n    |   |   |   |   |  subquery_cluster_node: 74\n    |   |   |   |   |   |\n    |   |   |   |   |   +- RELATIONAL Aggregate\n    |   |   |   |   |   |  (1 execution, 0.08 msecs total latency)\n    |   |   |   |   |   |  call_type: Local, iterator_type: Stream, scalar_aggregate: true\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   +- RELATIONAL Distributed Union\n    |   |   |   |   |   |   |  (1 execution, 0.07 msecs total latency)\n    |   |   |   |   |   |   |  call_type: Local, subquery_cluster_node: 76\n    |   |   |   |   |   |   |   |\n    |   |   |   |   |   |   |   \\- RELATIONAL Scan\n    |   |   |   |   |   |   |      (1 execution, 0.07 msecs total latency)\n    |   |   |   |   |   |   |      Full scan: true, scan_target: IDX_currencies_countries_country_id_89DC8690B5CAF5C1, scan_type: IndexScan\n    |   |   |   |   |   |   |\n    |   |   |   |   |   |   \\- SCALAR Function\n    |   |   |   |   |   |      COUNT(1)\n    |   |   |   |   |   |       |\n    |   |   |   |   |   |       \\- SCALAR Constant\n    |   |   |   |   |   |          1\n    |   |   |   |   |   |\n    |   |   |   |   |   \\- SCALAR Constant\n    |   |   |   |   |   \\- SCALAR Constant                                                                                            [2\/9050]\n    |   |   |   |   |      true\n    |   |   |   |   |\n    |   |   |   |   \\- SCALAR Function\n    |   |   |   |      COUNT_FINAL($v5)\n    |   |   |   |       |\n    |   |   |   |       \\- SCALAR Reference\n    |   |   |   |          $v5\n    |   |   |   |\n    |   |   |   +- SCALAR Constant\n    |   |   |   |  'currencies_countries'\n    |   |   |   |\n    |   |   |   \\- SCALAR Constant\n    |   |   |      203\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $Table_4\n    |   |   |\n    |   |   +- SCALAR Reference\n    |   |   |  $provided_4\n    |   |   |\n    |   |   \\- SCALAR Reference\n    |   |      $actual_4\n    |   |\n    |   +- SCALAR Reference\n    |   |  input_0\n    |   |\n    |   +- SCALAR Reference\n    |   |  input_1\n    |   |\n    |   \\- SCALAR Reference\n    |      input_2\n    |\n    +- SCALAR Reference\n    |  $Table_5\n    |\n    +- SCALAR Reference\n    |  $provided_5\n    |\n    \\- SCALAR Reference\n       $actual_5\n\nTable                 provided  actual\n\nregions               7         7\ncountries             196       196\ncities                204       204\ncurrencies            146       146\ncurrencies_countries  203       203\n\n<\/code><\/pre>\n<p>That&#8217;s a long one: scans, joins, concatenation, with some operations run in parallel, projection, aggregation. But basically, it scans each table, or the index when available as we can count the rows from the index which is smaller.<\/p>\n<h3>delete instance<\/h3>\n<p>This Google Spanner instance costs me $9 per hour (the pricing <a href=\"https:\/\/cloud.google.com\/spanner\/pricing\" target=\"_blank\" rel=\"noopener noreferrer\">https:\/\/cloud.google.com\/spanner\/pricing<\/a> for this eur6 configuration is $3 per node per hour and $50 per 100GB per month). As the provisioning takes 1 minute, and I can copy-paste everything from this blog post, I terminate the instance when I&#8217;ve finished my tests.<\/p>\n<p>However, in order to avoid to re-insert those rows, I would be nice to have a backup. Let&#8217;s try:<\/p>\n<pre><code>\ntime gcloud spanner backups create sample-data --retention-period=30d --database test --instance=franck\nCreate request issued for: [sample-data]\nWaiting for operation [projects\/disco-abacus-161115\/instances\/franck\/backups\/sample-data\/operations\/_auto_op_05f73852e07ae49f] to complete...\n\u281b\ndone.\nCreated backup [sample-data].\nreal    2m37.593s\nuser    0m0.908s\nsys     0m0.175s\n\ngcloud spanner instances delete franck\nDelete instance [franck]. Are you sure?\n\nDo you want to continue (Y\/n)?  Y\n\nERROR: (gcloud.spanner.instances.delete) FAILED_PRECONDITION: Cannot delete instance projects\/disco-abacus-161115\/instances\/franck because it contains backups. Please delete the backups before deleting the instance.\n\n<\/code><\/pre>\n<p>Yes, be careful, a backup is not a database backup here. Just a copy of the tables within the same instance, to be restored to the same region. This is <a href=\"https:\/\/cloud.google.com\/spanner\/docs\/backup#backup\" target=\"_blank\" rel=\"noopener noreferrer\">documented<\/a> of course: <i>Backups reside in the same instance as their source database and cannot be moved.<\/i> But you know how I find misleading to call that &#8220;backup&#8221; (a database is living and all the transactions must be backed-up, see <a href=\"https:\/\/www.dbi-services.com\/blog\/what-is-a-database-backup-back-to-the-basics\/\" target=\"_blank\" rel=\"noopener noreferrer\">What is a database backup<\/a>). Anyway, this copy cannot help to save money by deleting the instance. The database is gone if I delete the instance and I cannot stop it. The solution is probably to export to the object storage, like a dump, but this will be for a future blog post, maybe.<\/p>\n<pre><code>\ngcloud spanner backups delete sample-data --instance=franck\nYou are about to delete backup [sample-data]\n\nDo you want to continue (Y\/n)?  Y\n\nDeleted backup [sample-data].\n\nreal    0m7.744s\nuser    0m0.722s\nsys     0m0.056s\n[opc@a tmp]$ gcloud spanner instances delete franck --quiet   \n<\/code><\/pre>\n<p>The &#8211;quiet argument bypasses the interactive confirmation.<\/p>\n<p>This test was with small volume, simple schema, from a SQL script that works as-is on the most common SQL databases. Here I had to adapt a few things, but the compatibility with RDBMS has improved a lot in the past year as we can have full referential integrity (foreign key not limited to hierarchical interleave) and logical independence where a secondary index can be used without explicitly mentioning it. Google Spanner is probably the most efficient distributed database when you develop with a NoSQL approach but want to benefit from some SQL features to get a more agile data model and easier integrity and transaction management. However, I don&#8217;t think you can port any existing SQL application easily, or even consider it as a general purpose SQL database. For this, you may look at YugaByteDB which uses the PostgreSQL upper layer on distributed database similar to Spanner. However, Spanner has probably the lowest latency for multi-shard operations as it has some cloud-vendor specific optimizations like TrueTime, for software and hardware synchronization.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>By Franck Pachot . I have posted, a long time ago, about Google Spanner (inserting data and no decimal numeric data types) but many things have changed in this area. There is now a NUMERIC data type and many things have improved in this distributed SQL database, improving a bit the SQL compatibility. gcloud I [&hellip;]<\/p>\n","protected":false},"author":89,"featured_media":15430,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[955],"tags":[2204,1402,98],"type_dbi":[],"class_list":["post-15428","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-cloud","tag-google","tag-spanner","tag-sql"],"acf":[],"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v27.2 (Yoast SEO v27.2) - https:\/\/yoast.com\/product\/yoast-seo-premium-wordpress\/ -->\n<title>Google Spanner - SQL compatibility - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Google Spanner - SQL compatibility\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . I have posted, a long time ago, about Google Spanner (inserting data and no decimal numeric data types) but many things have changed in this area. There is now a NUMERIC data type and many things have improved in this distributed SQL database, improving a bit the SQL compatibility. gcloud I [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2021-01-05T08:13:43+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"2048\" \/>\n\t<meta property=\"og:image:height\" content=\"1154\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Cloud Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Cloud Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"23 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/\"},\"author\":{\"name\":\"Cloud Team\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/92adc1c969d57f2b2f51e970b15b2f70\"},\"headline\":\"Google Spanner &#8211; SQL compatibility\",\"datePublished\":\"2021-01-05T08:13:43+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/\"},\"wordCount\":2198,\"commentCount\":0,\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg\",\"keywords\":[\"Google\",\"Spanner\",\"SQL\"],\"articleSection\":[\"Cloud\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/\",\"name\":\"Google Spanner - SQL compatibility - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg\",\"datePublished\":\"2021-01-05T08:13:43+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/92adc1c969d57f2b2f51e970b15b2f70\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#primaryimage\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg\",\"contentUrl\":\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg\",\"width\":2048,\"height\":1154},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Google Spanner &#8211; SQL compatibility\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/92adc1c969d57f2b2f51e970b15b2f70\",\"name\":\"Cloud Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/1fd4abaa3d2f79eb3c430f8ababb33014273b4168e2652ca915d59c6ef5e8cec?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/1fd4abaa3d2f79eb3c430f8ababb33014273b4168e2652ca915d59c6ef5e8cec?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/1fd4abaa3d2f79eb3c430f8ababb33014273b4168e2652ca915d59c6ef5e8cec?s=96&d=mm&r=g\",\"caption\":\"Cloud Team\"},\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/cloud\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Google Spanner - SQL compatibility - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/","og_locale":"en_US","og_type":"article","og_title":"Google Spanner - SQL compatibility","og_description":"By Franck Pachot . I have posted, a long time ago, about Google Spanner (inserting data and no decimal numeric data types) but many things have changed in this area. There is now a NUMERIC data type and many things have improved in this distributed SQL database, improving a bit the SQL compatibility. gcloud I [&hellip;]","og_url":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/","og_site_name":"dbi Blog","article_published_time":"2021-01-05T08:13:43+00:00","og_image":[{"width":2048,"height":1154,"url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg","type":"image\/jpeg"}],"author":"Cloud Team","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Cloud Team","Est. reading time":"23 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/"},"author":{"name":"Cloud Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/92adc1c969d57f2b2f51e970b15b2f70"},"headline":"Google Spanner &#8211; SQL compatibility","datePublished":"2021-01-05T08:13:43+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/"},"wordCount":2198,"commentCount":0,"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg","keywords":["Google","Spanner","SQL"],"articleSection":["Cloud"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/","url":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/","name":"Google Spanner - SQL compatibility - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#primaryimage"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#primaryimage"},"thumbnailUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg","datePublished":"2021-01-05T08:13:43+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/92adc1c969d57f2b2f51e970b15b2f70"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#primaryimage","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/Screenshot-2020-12-27-225459-scaled-1.jpg","width":2048,"height":1154},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/google-spanner-sql-compatibility\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Google Spanner &#8211; SQL compatibility"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/92adc1c969d57f2b2f51e970b15b2f70","name":"Cloud Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1fd4abaa3d2f79eb3c430f8ababb33014273b4168e2652ca915d59c6ef5e8cec?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1fd4abaa3d2f79eb3c430f8ababb33014273b4168e2652ca915d59c6ef5e8cec?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1fd4abaa3d2f79eb3c430f8ababb33014273b4168e2652ca915d59c6ef5e8cec?s=96&d=mm&r=g","caption":"Cloud Team"},"url":"https:\/\/www.dbi-services.com\/blog\/author\/cloud\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15428","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/89"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=15428"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/15428\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media\/15430"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=15428"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=15428"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=15428"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=15428"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}