Spanner Google SQL

Posted on Edited on In

The reference of Google SQL language.

Here are some sample Spanner SQL for quick revisit.

Sample Interactive SQLs

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
-- Launch spanner interactive terminal with parameters
span sql --max_value_lines=300

-- help
help

-- Select DB in target scope
use /span/xxxx/cloud-example-domain:test;

-- For write and update operation
set dml_concurrency interactive_transactional;


-- Partial string match with like operator
select Device
from Devices
where Device.name like '%foo-%';

-- Use ltrim and cast
select device.region,device.area,device.name
from Devices
where device.id in ('bar','foo')
-- "apple23" will be trimmed to "23" and cast to number 23
order by region,area,cast(ltrim(d.name, 'apple') as int32)
desc
limit 3;

-- Use UNEST, in and limit
select distinct Device.reference_id
from Devices
where "example_id" in UNNEST(Device.ids)
limit 1;

-- To check timestamp change between a time period
-- it is UTC timestamp: current - 9 hour <= target time period < current - 8 hour
select Device.id,timestamp_seconds(Metadata.last_updated_timestamp.seconds) 
from Devices 
where Metadata.last_updated_timestamp.seconds 
          >= UNIX_SECONDS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 9 HOUR))
      and Metadata.last_updated_timestamp.seconds 
          < UNIX_SECONDS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 8 HOUR))

-- Use array_length for repeated proto field
select Device.id
from Devices
where array_length(Device.ids) > 1;

-- Use group by and count(*)
select Device.service_level,count(*)
from Devices
where Device.type='SWITCH'
group by Device.service_level;

-- Insert with proto
insert into ProjectMappings(ProjectMapping, Metadata)
values(
  {project_number: 1357913579,
  billing_account: "test-billing-account",
  project_state: "PROJECT_STATE_ACTIVE",
  alias: "test-alias"
  },
  {
  last_updated_timestamp: {seconds: 1689901201},
  version_id: 1555444333222111000,
  state: "LATEST",
  created_by: "example@gmail.com"
  }
);

-- Compare timestamp seconds
SELECT *
from PrivateCloudMetadata
WHERE PrivateCloud.region in ('us-east4', 'us-central1')
      AND Metadata.last_updated_timestamp.seconds 
          > UNIX_SECONDS(TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 5 DAY));

Redirect

1
2
3
4
5
6
7
8
-- Run sql from input.txt, absolute path
Source /home/user/chengdol/input.txt;

-- Capture into output.txt, absolute path
TEE /home/user/chengdol/output.txt;

-- cancel output redirect
NOTEE