apstndb

SELECT SUBSTR(TO_HEX(x), 0, 8), SUBSTR(TO_HEX(x), 0, 16)
FROM(SELECT SHA256(CAST(FirstName AS BYTES)) @{DISABLE_INLINE=FALSE} AS x FROM Singers LIMIT 1)

SELECT SUBSTR(TO_HEX(x), 0, 8), SUBSTR(TO_HEX(x), 0, 16)
FROM(SELECT SHA256(CAST(FirstName AS BYTES)) @{DISABLE_INLINE=TRUE} AS x FROM Singers LIMIT 1)

apstndb

https://issuetracker.google.com/issues/35905569#comment111

DECLARE v1 INT64 DEFAULT 123;
DECLARE v2 INT64 DEFAULT 123;
BEGIN
    SET v1 = @v1;
    SET v2 = @v2;
EXCEPTION WHEN ERROR THEN
 -- nop

apstndb

$ export PROJECT_ID=gcpug-public-spanner INSTANCE_ID=merpay-sponsored-instance DATABASE_ID=apstndb-sampledb-with-data-idx
$ sh show-schema-tree.sh
Concerts
Singers
  Albums
    Songs
    SongsBySingerAlbumSongNameDesc on Songs
SongGenres
AlbumsByAlbumTitle on Albums

apstndb

$ gcloud spanner databases execute-sql --project=${PROJECT_ID} --instance=${INSTANCE_ID} ${DATABASE_ID} \
     --sql "$(cat input.sql)" --query-mode=PLAN --format=json | jq -r -f plan.jq
 *0 Distributed Union
 *1   Distributed Cross Apply
  2     Create Batch
  3       Local Distributed Union
  4         Compute Struct
 *5           FilterScan
  6             Index Scan (Index: SongsBySongName)

apstndb

Unofficial Cloud Spanner operator collection

Query execution operators にはドキュメンテーションされていない operator もあり、 metadata やそれぞれの child links についてもほぼ解説されていないためここにまとめる。

対象: 実行計画を可視化や解析のために処理するツール作成者や、含まれる情報全てをクエリの理解に役立てたいと考えるユーザ

TODO: Metadata や ChildLinks の表の形式化を進める。

実行計画の構造

apstndb

$ ./go2go run main.go2
true

$ ./go2go translate main.go2
$ cat main.go
// Code generated by go2go; DO NOT EDIT.

apstndb

import "list"

a: [{id: 1}, {id: 2}]
b: [{id: 1}, {id: 1}]
c: [{id: 1}, {id: 2}, {id: 2}]

a_ids:: [x.id for x in a]
a_unique: list.UniqueItems(a_ids)

unique :: {

apstndb

CREATE TEMP FUNCTION typeof_literal(input STRING)
AS (
      CASE
        -- Process NUMERIC, DATE, DATETIME, TIME, TIMESTAMP,
        WHEN REGEXP_CONTAINS(input, r'^[A-Z]+ "') THEN REGEXP_EXTRACT(input, r'^([A-Z]+) "')
        WHEN REGEXP_CONTAINS(input, r'^-?[0-9]*$') THEN 'INT64'
        WHEN REGEXP_CONTAINS(input, r'^(-?[0-9]+[.e].*|CAST\("([^"]*)" AS FLOAT64\))$') THEN 'FLOAT64'
        WHEN input IN ('true', 'false') THEN 'BOOL'
        WHEN input LIKE '"%' THEN 'STRING'
        WHEN input LIKE 'b"%' THEN 'BYTES'

apstndb

• https://stackoverflow.com/a/49985190

apstndb

Start Cloud Spanner Emulator

gcloud beta emulators spanner start

Setup database(not idempotent and error prone)

SPANNER_EMULATOR_HOST_REST=localhost:9020