0
# SQL DDL and Table Operations
1

2
Core SQL operations for creating HBase table mappings, defining column families and qualifiers, and managing table schemas with proper data type mappings.
3

4
## Capabilities
5

6
### CREATE TABLE Statement
7

8
Creates a logical table mapping to an existing HBase table, defining column families, qualifiers, and data types.
9

10
```sql { .api }
11
CREATE TABLE table_name (
12
    rowkey STRING,                                  -- Required: Row key column
13
    column_family ROW<qualifier DATA_TYPE, ...>,   -- Column family with nested qualifiers
14
    PRIMARY KEY (rowkey) NOT ENFORCED              -- Required: Primary key constraint
15
) WITH (
16
    'connector' = 'hbase-2.2',                     -- Required: Connector identifier
17
    'table-name' = 'hbase_table_name',             -- Required: Actual HBase table name
18
    -- Additional configuration options
19
);
20
```
21

22
**Requirements**:
23
- Row key column must be defined and included in PRIMARY KEY
24
- Column families must use ROW type with named qualifiers
25
- PRIMARY KEY constraint must specify row key column with NOT ENFORCED
26
- HBase table must exist before creating the Flink table mapping
27

28
**Usage Examples**:
29

30
```sql
31
-- Simple table with single column family
32
CREATE TABLE users (
33
    user_id STRING,
34
    info ROW<name STRING, age INT, email STRING>,
35
    PRIMARY KEY (user_id) NOT ENFORCED
36
) WITH (
37
    'connector' = 'hbase-2.2',
38
    'table-name' = 'users_table',
39
    'zookeeper.quorum' = 'localhost:2181'
40
);
41

42
-- Table with multiple column families
43
CREATE TABLE product_catalog (
44
    product_id STRING,
45
    basic_info ROW<name STRING, category STRING, price DECIMAL(10,2)>,
46
    inventory ROW<stock_count INT, warehouse_location STRING>,
47
    metadata ROW<created_at TIMESTAMP(3), updated_at TIMESTAMP(3)>,
48
    PRIMARY KEY (product_id) NOT ENFORCED
49
) WITH (
50
    'connector' = 'hbase-2.2',
51
    'table-name' = 'products',
52
    'zookeeper.quorum' = 'zk1:2181,zk2:2181,zk3:2181',
53
    'zookeeper.znode.parent' = '/hbase'
54
);
55
```
56

57
### INSERT Operations (UPSERT)
58

59
Inserts data into HBase table using UPSERT semantics (creates new record or updates existing).
60

61
```sql { .api }
62
INSERT INTO table_name VALUES (
63
    'row_key_value',
64
    ROW(qualifier1_value, qualifier2_value, ...),
65
    ROW(qualifier3_value, qualifier4_value, ...)
66
);
67

68
INSERT INTO table_name (rowkey, column_family, ...)
69
VALUES ('row_key_value', ROW(...), ...);
70

71
INSERT INTO table_name
72
SELECT rowkey, column_family, ...
73
FROM source_table;
74
```
75

76
**Usage Examples**:
77

78
```sql
79
-- Direct value insertion
80
INSERT INTO users VALUES (
81
    'user123',
82
    ROW('Alice Johnson', 28, 'alice@example.com')
83
);
84

85
-- Column-specific insertion
86
INSERT INTO product_catalog (product_id, basic_info, inventory)
87
VALUES (
88
    'prod_001',
89
    ROW('Laptop Computer', 'Electronics', 999.99),
90
    ROW(50, 'Warehouse_A')
91
);
92

93
-- Batch insertion from another table
94
INSERT INTO users
95
SELECT 
96
    user_id,
97
    ROW(full_name, age, email_address)
98
FROM staging_users
99
WHERE status = 'active';
100
```
101

102
### SELECT Operations
103

104
Queries data from HBase table with support for projection, filtering, and complex expressions.
105

106
```sql { .api }
107
SELECT rowkey, column_family.qualifier, ...
108
FROM table_name
109
WHERE condition;
110

111
SELECT rowkey, column_family.qualifier AS alias
112
FROM table_name
113
WHERE column_family.qualifier operator value;
114
```
115

116
**Projection Pushdown**: Column family and qualifier level filtering is pushed down to HBase for optimal performance.
117

118
**Usage Examples**:
119

120
```sql
121
-- Simple projection and filtering
122
SELECT user_id, info.name, info.age
123
FROM users
124
WHERE info.age > 25;
125

126
-- Complex nested field access
127
SELECT 
128
    product_id,
129
    basic_info.name AS product_name,
130
    basic_info.price,
131
    inventory.stock_count,
132
    metadata.created_at
133
FROM product_catalog
134
WHERE basic_info.category = 'Electronics'
135
  AND inventory.stock_count > 0;
136

137
-- Aggregation queries
138
SELECT 
139
    basic_info.category,
140
    COUNT(*) AS product_count,
141
    AVG(basic_info.price) AS avg_price
142
FROM product_catalog
143
GROUP BY basic_info.category;
144
```
145

146
### DELETE Operations
147

148
Removes records from HBase table by row key.
149

150
```sql { .api }
151
DELETE FROM table_name 
152
WHERE rowkey = 'row_key_value';
153

154
DELETE FROM table_name
155
WHERE rowkey IN ('key1', 'key2', 'key3');
156
```
157

158
**Note**: DELETE operations in HBase are performed at the row level only. Column-level deletions are not supported through SQL DDL.
159

160
**Usage Examples**:
161

162
```sql
163
-- Delete single record
164
DELETE FROM users WHERE user_id = 'user123';
165

166
-- Delete multiple records
167
DELETE FROM product_catalog 
168
WHERE product_id IN ('prod_001', 'prod_002', 'prod_003');
169
```
170

171
### Temporal Table Joins (Lookup)
172

173
Performs lookup joins using HBase table as a temporal table for enriching streaming data.
174

175
```sql { .api }
176
SELECT 
177
    stream_table.field,
178
    lookup_table.column_family.qualifier
179
FROM stream_table s
180
JOIN hbase_table FOR SYSTEM_TIME AS OF s.proc_time AS h
181
ON s.lookup_key = h.rowkey;
182
```
183

184
**Usage Examples**:
185

186
```sql
187
-- Enrich order stream with user information
188
SELECT 
189
    o.order_id,
190
    o.product_id,
191
    o.quantity,
192
    u.info.name AS customer_name,
193
    u.info.email AS customer_email
194
FROM order_stream o
195
JOIN users FOR SYSTEM_TIME AS OF o.proc_time AS u
196
ON o.user_id = u.user_id;
197

198
-- Product catalog lookup
199
SELECT 
200
    sale.transaction_id,
201
    sale.quantity,
202
    p.basic_info.name AS product_name,
203
    p.basic_info.price * sale.quantity AS total_amount
204
FROM sales_stream sale
205
JOIN product_catalog FOR SYSTEM_TIME AS OF sale.proc_time AS p
206
ON sale.product_id = p.product_id;
207
```
208

209
### Schema Evolution Considerations
210

211
**Column Family Management**:
212
- Adding new qualifiers: Modify the ROW type definition and recreate the table
213
- Column family structure changes require table recreation
214
- Data type changes may require custom conversion logic
215

216
**Backward Compatibility**:
217
- Existing HBase data remains accessible after schema changes
218
- Null handling for missing qualifiers is automatic
219
- Type mismatches will cause runtime serialization errors
220

221
**Usage Examples**:
222

223
```sql
224
-- Original schema
225
CREATE TABLE users_v1 (
226
    user_id STRING,
227
    info ROW<name STRING, age INT>,
228
    PRIMARY KEY (user_id) NOT ENFORCED
229
) WITH (...);
230

231
-- Evolved schema with additional fields
232
DROP TABLE users_v1;
233
CREATE TABLE users_v2 (
234
    user_id STRING,
235
    info ROW<name STRING, age INT, email STRING, phone STRING>,
236
    preferences ROW<language STRING, timezone STRING>,
237
    PRIMARY KEY (user_id) NOT ENFORCED
238
) WITH (...);
239
```