\nIn a previous post<\/a> I’ve created a Google Cloud Spanner database and inserted a few rows from the GUI. This is definitely not a solution fo many rows and here is a post about using the command line.
\n
\nIf I start the Google Shell from the icon on the Spanner page for my project, everything is set. But if I run it from elsewhere, using the https:\/\/console.cloud.google.com\/cloudshell<\/a> as I did in A free persistent Google Cloud service with Oracle XE<\/a> I have to set the project:<\/p>\n
\nfranck_pachot@cloudshell:~$ gcloud config set project superb-avatar-210409\nUpdated property [core\/project].\nfranck_pachot@superb-avatar-210409:~$ \n<\/code><\/pre>\nInstance<\/h3>\n
I create my Spanner instance with 3 nodes across the world:<\/p>\n
\u00a8\nfranck_pachot@superb-avatar-210409:~$ time gcloud spanner instances create franck --config nam-eur-asia1 --nodes=3 --description Franck \nCreating instance...done. \n \nreal 0m3.940s\nuser 0m0.344s\nsys 0m0.092s\n<\/code><\/pre>\nDatabase<\/h3>\n
and Spanner database – created in 6 seconds:<\/p>\n
\nfranck_pachot@superb-avatar-210409:~$ time gcloud spanner databases create test --instance=franck\nCreating database...done. \n&nbssp;\nreal 0m6.832s\nuser 0m0.320s\nsys 0m0.128s\n<\/code><\/pre>\nTable<\/h3>\n
The DDL for table creation can also be run from there:<\/p>\n
\nfranck_pachot@superb-avatar-210409:~$ gcloud spanner databases ddl update test --instance=franck --ddl='create table DEMO1 ( ID1 int64, TEXT string(max) ) primary key (ID1)'\nDDL updating...done. \n'@type': type.googleapis.com\/google.protobuf.Empty\n<\/code><\/pre>\nI’m now ready to insert one million rows. Here is my table:<\/p>\n
\nfranck_pachot@superb-avatar-210409:~$ gcloud spanner databases ddl describe test --instance=franck\n--- |-\n CREATE TABLE DEMO1 (\n ID1 INT64,\n TEXT STRING(MAX),\n ) PRIMARY KEY(ID1)\n<\/code><\/pre>\nInsert<\/h3>\n
The gcloud command line has a limited insert possibility:<\/p>\n
\nfranck_pachot@superb-avatar-210409:~$ time for i in $(seq 1 1000000) ; do gcloud beta spanner rows insert --table=DEMO1 --database=test --instance=franck --data=ID1=${i},TEXT=XXX${i} ; done\ncommitTimestamp: '2018-07-18T11:09:45.065684Z'\ncommitTimestamp: '2018-07-18T11:09:50.433133Z'\ncommitTimestamp: '2018-07-18T11:09:55.752857Z'\ncommitTimestamp: '2018-07-18T11:10:01.044531Z'\ncommitTimestamp: '2018-07-18T11:10:06.285764Z'\ncommitTimestamp: '2018-07-18T11:10:11.106936Z'\n^C\n<\/code><\/pre>\nOk, let’s stop there. Calling a service for each row is not efficient with a latency of 5 seconds.<\/p>\n
API<\/h3>\n
I’ll use the API from Python. Basically, a connection is a Spanner Client:<\/p>\n
\nfranck_pachot@superb-avatar-210409:~$ python3\nPython 3.5.3 (default, Jan 19 2017, 14:11:04) \n[GCC 6.3.0 20170118] on linux\nType \"help\", \"copyright\", \"credits\" or \"license\" for more information.\n>>> from google.cloud import spanner\n>>> spanner_client = spanner.Client()\n>>> instance = spanner_client.instance('franck')\n>>> database = instance.database('test')\n>>> \n<\/code><\/pre>\nBatch Insert<\/h3>\n
With this I can send a batch of rows to insert. Here is the full Python script I used to insert one million, by batch of 1000 rows:<\/p>\n
\nfrom google.cloud import spanner\nspanner_client = spanner.Client()\ninstance = spanner_client.instance('franck')\ndatabase = instance.database('test')\nfor j in range(1000):\n records=[]\n for i in range(1000):\n records.append((1+j*1000+i,u'XXX'+str(i)))\n with database.batch() as batch:\n batch.insert(table='DEMO1',columns=('ID1', 'TEXT',),values=records)\n<\/code><\/pre>\nThis takes 2 minutes:<\/p>\n
\nfranck_pachot@superb-avatar-210409:~$ time python3 test.py \n \nreal 2m52.707s\nuser 0m21.776s\nsys 0m0.668s\nfranck_pachot@superb-avatar-210409:~$ \n<\/code><\/pre>\nIf you remember my list of blogs on Variations on 1M rows insert<\/a> that’s not so fast. But remember that rows are distributed across 3 nodes in 3 continents but here inserting with constantly increasing value have all batched rows going to the same node. The PRIMARY KEY in Google Spanner is not only there to declare a constraint but also determines the organization of data.<\/p>\nQuery<\/h3>\n
The select can also be run from there from a read-only transaction called ‘Snapshot’ because it is doing MVCC consistent reads:<\/p>\n
\nfrank_pachot@superb-avatar-210409:~$ python3\nPython 3.5.3 (default, Jan 19 2017, 14:11:04) \n[GCC 6.3.0 20170118] on linux\nType \"help\", \"copyright\", \"credits\" or \"license\" for more information.\n>>> from google.cloud import spanner\n>>> with spanner.Client().instance('franck').database('test').snapshot() as snapshot:\n... results = snapshot.execute_sql('SELECT COUNT(*) FROM DEMO1')\n... for row in results:\n... print(row)\n... \n[1000000]\n<\/code><\/pre>\nThe advantage of the read-only transaction is that it can do consistent reads without locking. The queries executed in a read-write transaction have to acquire some locks in order to guarantee consistency when reading across multiple nodes.<\/p>\n
Interleave<\/h3>\n
So, you can look at the PRIMARY KEY as a partition by range, and we have also reference partitioning with INTERLEAVE IN PARENT. This reminds me of the Oracle CLUSTER segment that is so rarely used because storing the tables separately is finally the better compromise on performance and flexibility for a multi-purpose database. <\/p>\n
Here is my creation of DEMO2 where ID1 is a foreign key referencing DEMO1<\/p>\n
\nfranck_pachot@superb-avatar-210409:~$ time gcloud spanner databases ddl update test --instance=franck --ddl='create table DEMO2 ( ID1 int64, ID2 int64, TEXT string(max) ) primary key (ID1,ID2), interleave in parent DEMO1 on delete cascade'\nDDL updating...done. \n'@type': type.googleapis.com\/google.protobuf.Empty\n \nreal 0m24.418s\nuser 0m0.356s\nsys 0m0.088s\n<\/code><\/pre>\nI’m now inserting 5 detail rows per each parent row:<\/p>\n
\nfrom google.cloud import spanner\ndatabase = spanner.Client().instance('franck').database('test')\nfor j in range(1000):\n records=[]\n for i in range(1000):\n for k in range(5):\n records.append((1+j*1000+i,k,u'XXX'+str(i)+' '+str(k)))\n with database.batch() as batch:\n batch.insert(table='DEMO2',columns=('ID1','ID2','TEXT'),values=records)\n<\/code><\/pre>\nThis ran in 6 minutes.<\/p>\n
Join (Cross Apply)<\/h3>\n
Here is the execution plan for<\/p>\n
\nSELECT * FROM DEMO1 join DEMO2 using(ID1) where DEMO2.TEXT=DEMO1.TEXT\n<\/code><\/pre>\n
![\"CaptureSpannerCrossApply\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSpannerCrossApply.png\")
<\/a><\/p>\n![\"CaptureSpannerDistributedCrossApply\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2022\/04\/CaptureSpannerDistributedCrossApply.png\")
<\/a>