xb7Sa7qV

1-- CREATE TABLE rakam_raptor.demo.demotable (_device_id VARCHAR, _time TIMESTAMP) WITH(temporal_column = '_time', bucketed_on=ARRAY['_device_id'], bucket_count = 10, distribution_name='test');
2
3// Check if table exists
4SELECT t.table_id, t.distribution_id, d.distribution_name, d.bucket_count, t.temporal_column_id, t.organization_enabled
5FROM tables t
6LEFT JOIN distributions d ON (t.distribution_id = d.distribution_id)
7WHERE t.schema_name = ?
8  AND t.table_name = ?
9
10// Check if distribution exists exists
11SELECT distribution_id, distribution_name, column_types, bucket_count
12FROM distributions
13WHERE distribution_name = ?
14
15// Generate buckets and insert the initial bucket configuration
16INSERT INTO buckets (distribution_id, bucket_number, node_id)
17VALUES (?, ?, ?)
18
19
20SELECT b.bucket_number, n.node_identifier
21FROM buckets b
22JOIN nodes n ON (b.node_id = n.node_id)
23WHERE b.distribution_id = ?
24ORDER BY b.bucket_number
25
26SELECT schema_name, table_name, data
27FROM views
28WHERE (schema_name = ? OR ? IS NULL)
29  AND (table_name = ? OR ? IS NULL)
30ORDER BY schema_name, table_name
31
32// Generate transaction for CREATE TABLE
33INSERT INTO transactions (start_time) VALUES (CURRENT_TIMESTAMP)
34
35// Get distribution config
36SELECT distribution_id, distribution_name, column_types, bucket_count
37FROM distributions
38WHERE distribution_id = ?
39
40// Store table metadata
41INSERT INTO tables (
42  schema_name, table_name, compaction_enabled, organization_enabled, distribution_id,
43  create_time, update_time, table_version,
44  shard_count, row_count, compressed_size, uncompressed_size)
45VALUES (
46  ?, ?, ?, ?, ?,
47  ?, ?, 0,
48  0, 0, 0, 0)
49
50
51// Store column metadata
52INSERT INTO columns (table_id, column_id, column_name, ordinal_position, data_type, sort_ordinal_position, bucket_ordinal_position)
53VALUES (?, ?, ?, ?, ?, ?, ?)
54
55// Update temporal column
56UPDATE tables SET
57temporal_column_id = ?
58WHERE table_id = ?
59
60
61// Create shard BRIN index metadata table
62CREATE TABLE x_shards_t281 (
63  shard_id BIGINT NOT NULL,
64  shard_uuid BINARY(16) NOT NULL,
65  bucket_number INT NOT NULL
66,  c1_min varbinary(100),
67  c1_max varbinary(100),
68  c2_min bigint,
69  c2_max bigint,
70  PRIMARY KEY (bucket_number, shard_uuid),
71  UNIQUE (shard_id),
72  UNIQUE (shard_uuid),
73  UNIQUE (c2_max, c2_min, bucket_number, shard_id, shard_uuid)
74)
75
76// Finish up the transition
77UPDATE transactions SET
78  successful = ?
79, end_time = CURRENT_TIMESTAMP
80WHERE transaction_id = ?
81  AND successful IS NULL
82
83
84// Update table data after transaction
85SELECT table_id FROM tables WHERE table_id = ? FOR UPDATE
86
87UPDATE tables SET
88  shard_count = shard_count + ? 
89, row_count = row_count + ?
90, compressed_size = compressed_size + ?
91, uncompressed_size = uncompressed_size + ?
92WHERE table_id = ?
93
94UPDATE tables SET
95  update_time = ?
96, table_version = table_version + 1
97WHERE table_id = ?
98
99// Clean transaction data
100DELETE FROM created_shards WHERE transaction_id = ?