0
# Database Operations
1

2
Database operations provide comprehensive database lifecycle management including creation, alteration, and property management for Hive databases.
3

4
## Capabilities
5

6
### Database Creation
7

8
Create new Hive databases with properties, comments, and locations.
9

10
```java { .api }
11
/**
12
 * CREATE DATABASE statement for Hive dialect
13
 * Supports database properties, comments, and location specifications
14
 */
15
public class SqlCreateHiveDatabase extends SqlCreateDatabase {
16
    /**
17
     * Creates a new Hive database creation statement
18
     * @param pos Parser position information
19
     * @param databaseName Name of the database to create
20
     * @param propertyList Database properties (DBPROPERTIES)
21
     * @param comment Database comment
22
     * @param location Database location URI
23
     * @param ifNotExists Whether to use IF NOT EXISTS clause
24
     * @throws ParseException if validation fails
25
     */
26
    public SqlCreateHiveDatabase(SqlParserPos pos, SqlIdentifier databaseName, 
27
                                SqlNodeList propertyList, SqlCharStringLiteral comment, 
28
                                SqlCharStringLiteral location, boolean ifNotExists) throws ParseException;
29
}
30
```
31

32
**Constants:**
33
```java { .api }
34
public static final String DATABASE_LOCATION_URI = "hive.database.location-uri";
35
```
36

37
**Usage Examples:**
38

39
```java
40
// Basic database creation
41
String createDbSql = "CREATE DATABASE sales_db";
42
SqlNode parsed = parser.parseStmt();
43

44
// Database with properties and location
45
String createDbWithPropsSql = """
46
    CREATE DATABASE IF NOT EXISTS analytics_db
47
    COMMENT 'Analytics database for reporting'
48
    LOCATION '/data/analytics'
49
    WITH DBPROPERTIES (
50
        'owner' = 'analytics_team',
51
        'department' = 'data_engineering'
52
    )
53
    """;
54

55
// Programmatic database creation
56
SqlIdentifier dbName = new SqlIdentifier("customer_db", SqlParserPos.ZERO);
57
SqlNodeList properties = new SqlNodeList(SqlParserPos.ZERO);
58
properties.add(new SqlTableOption("owner", "customer_team", SqlParserPos.ZERO));
59

60
SqlCreateHiveDatabase createDb = new SqlCreateHiveDatabase(
61
    SqlParserPos.ZERO,
62
    dbName,
63
    properties,
64
    SqlLiteral.createCharString("Customer data database", SqlParserPos.ZERO),
65
    SqlLiteral.createCharString("/data/customers", SqlParserPos.ZERO),
66
    true // IF NOT EXISTS
67
);
68
```
69

70
### Database Alteration Base
71

72
Abstract base class for all database alteration operations.
73

74
```java { .api }
75
/**
76
 * Base class for ALTER DATABASE operations
77
 * Provides common functionality for all database alterations
78
 */
79
public abstract class SqlAlterHiveDatabase extends SqlAlterDatabase {
80
    /**
81
     * Creates base database alteration statement
82
     * @param pos Parser position information
83
     * @param databaseName Name of database to alter
84
     * @param propertyList Properties associated with the alteration
85
     */
86
    public SqlAlterHiveDatabase(SqlParserPos pos, SqlIdentifier databaseName, SqlNodeList propertyList);
87
    
88
    /**
89
     * Types of database alteration operations supported
90
     */
91
    public enum AlterHiveDatabaseOp { 
92
        CHANGE_PROPS,    // Change database properties
93
        CHANGE_LOCATION, // Change database location
94
        CHANGE_OWNER     // Change database owner
95
    }
96
}
97
```
98

99
**Constants:**
100
```java { .api }
101
public static final String ALTER_DATABASE_OP = "hive.alter.database.op";
102
```
103

104
### Database Location Alteration
105

106
Change the location of an existing Hive database.
107

108
```java { .api }
109
/**
110
 * ALTER DATABASE SET LOCATION statement
111
 * Changes the location URI of an existing database
112
 */
113
public class SqlAlterHiveDatabaseLocation extends SqlAlterHiveDatabase {
114
    /**
115
     * Creates database location alteration statement
116
     * @param pos Parser position information
117
     * @param databaseName Name of database to alter
118
     * @param location New location URI for the database
119
     */
120
    public SqlAlterHiveDatabaseLocation(SqlParserPos pos, SqlIdentifier databaseName, 
121
                                       SqlCharStringLiteral location);
122
}
123
```
124

125
**Usage Examples:**
126

127
```java
128
// Change database location
129
String alterLocationSql = "ALTER DATABASE sales_db SET LOCATION '/new/data/sales'";
130

131
// Programmatic location change
132
SqlIdentifier dbName = new SqlIdentifier("analytics_db", SqlParserPos.ZERO);
133
SqlCharStringLiteral newLocation = SqlLiteral.createCharString("/data/analytics_v2", SqlParserPos.ZERO);
134

135
SqlAlterHiveDatabaseLocation alterLocation = new SqlAlterHiveDatabaseLocation(
136
    SqlParserPos.ZERO,
137
    dbName,
138
    newLocation
139
);
140
```
141

142
### Database Owner Alteration
143

144
Change the owner of an existing Hive database.
145

146
```java { .api }
147
/**
148
 * ALTER DATABASE SET OWNER statement
149
 * Changes the owner of an existing database (user or role)
150
 */
151
public class SqlAlterHiveDatabaseOwner extends SqlAlterHiveDatabase {
152
    /**
153
     * Creates database owner alteration statement
154
     * @param pos Parser position information
155
     * @param databaseName Name of database to alter
156
     * @param ownerType Type of owner ("user" or "role")
157
     * @param ownerName Name of the new owner
158
     */
159
    public SqlAlterHiveDatabaseOwner(SqlParserPos pos, SqlIdentifier databaseName, 
160
                                    String ownerType, SqlIdentifier ownerName);
161
}
162
```
163

164
**Constants:**
165
```java { .api }
166
public static final String DATABASE_OWNER_NAME = "hive.database.owner.name";
167
public static final String DATABASE_OWNER_TYPE = "hive.database.owner.type";
168
public static final String USER_OWNER = "user";
169
public static final String ROLE_OWNER = "role";
170
```
171

172
**Usage Examples:**
173

174
```java
175
// Change database owner to user
176
String alterOwnerSql = "ALTER DATABASE sales_db SET OWNER USER john_doe";
177

178
// Change database owner to role
179
String alterRoleSql = "ALTER DATABASE analytics_db SET OWNER ROLE data_engineers";
180

181
// Programmatic owner change
182
SqlIdentifier dbName = new SqlIdentifier("customer_db", SqlParserPos.ZERO);
183
SqlIdentifier ownerName = new SqlIdentifier("analytics_team", SqlParserPos.ZERO);
184

185
SqlAlterHiveDatabaseOwner alterOwner = new SqlAlterHiveDatabaseOwner(
186
    SqlParserPos.ZERO,
187
    dbName,
188
    SqlAlterHiveDatabaseOwner.ROLE_OWNER,
189
    ownerName
190
);
191
```
192

193
### Database Properties Alteration
194

195
Change the properties (DBPROPERTIES) of an existing Hive database.
196

197
```java { .api }
198
/**
199
 * ALTER DATABASE SET DBPROPERTIES statement
200
 * Changes the properties of an existing database
201
 */
202
public class SqlAlterHiveDatabaseProps extends SqlAlterHiveDatabase {
203
    /**
204
     * Creates database properties alteration statement
205
     * @param pos Parser position information
206
     * @param databaseName Name of database to alter
207
     * @param propertyList New properties to set
208
     * @throws ParseException if properties validation fails
209
     */
210
    public SqlAlterHiveDatabaseProps(SqlParserPos pos, SqlIdentifier databaseName, 
211
                                    SqlNodeList propertyList) throws ParseException;
212
}
213
```
214

215
**Usage Examples:**
216

217
```java
218
// Change database properties
219
String alterPropsSql = """
220
    ALTER DATABASE sales_db SET DBPROPERTIES (
221
        'last_modified' = '2023-12-01',
222
        'department' = 'sales_operations',
223
        'retention_days' = '365'
224
    )
225
    """;
226

227
// Programmatic properties change
228
SqlIdentifier dbName = new SqlIdentifier("analytics_db", SqlParserPos.ZERO);
229
SqlNodeList properties = new SqlNodeList(SqlParserPos.ZERO);
230

231
properties.add(new SqlTableOption("owner", "new_owner", SqlParserPos.ZERO));
232
properties.add(new SqlTableOption("environment", "production", SqlParserPos.ZERO));
233
properties.add(new SqlTableOption("backup_enabled", "true", SqlParserPos.ZERO));
234

235
SqlAlterHiveDatabaseProps alterProps = new SqlAlterHiveDatabaseProps(
236
    SqlParserPos.ZERO,
237
    dbName,
238
    properties
239
);
240
```
241

242
### Reserved Properties
243

244
Database operations automatically validate against reserved properties:
245

246
```java
247
// Reserved database properties that cannot be set by users
248
Set<String> reservedProps = Set.of(
249
    "hive.alter.database.op",
250
    "hive.database.location-uri"
251
);
252

253
// Properties validation is performed automatically
254
try {
255
    SqlAlterHiveDatabaseProps alterProps = new SqlAlterHiveDatabaseProps(pos, dbName, props);
256
} catch (ParseException e) {
257
    // Thrown if trying to set reserved properties
258
    System.err.println("Cannot set reserved property: " + e.getMessage());
259
}
260
```
261

262
### Complete Database Lifecycle Example
263

264
```java
265
// 1. Create database
266
String createSql = """
267
    CREATE DATABASE IF NOT EXISTS company_data
268
    COMMENT 'Main company data warehouse'
269
    LOCATION '/data/warehouse/company'
270
    WITH DBPROPERTIES (
271
        'owner' = 'data_team',
272
        'environment' = 'production',
273
        'created_date' = '2023-01-01'
274
    )
275
    """;
276

277
// 2. Change database location
278
String relocateSql = "ALTER DATABASE company_data SET LOCATION '/data/warehouse/v2/company'";
279

280
// 3. Change database owner
281
String changeOwnerSql = "ALTER DATABASE company_data SET OWNER ROLE data_engineers";
282

283
// 4. Update database properties
284
String updatePropsSql = """
285
    ALTER DATABASE company_data SET DBPROPERTIES (
286
        'last_backup' = '2023-12-01',
287
        'backup_retention' = '90',
288
        'compliance_level' = 'high'
289
    )
290
    """;
291

292
// Parse and execute each statement
293
for (String sql : List.of(createSql, relocateSql, changeOwnerSql, updatePropsSql)) {
294
    try {
295
        SqlNode parsed = parser.create(sql).parseStmt();
296
        // Execute with Flink TableEnvironment
297
        tableEnv.executeSql(sql);
298
    } catch (SqlParseException e) {
299
        System.err.println("Failed to parse: " + sql + " - " + e.getMessage());
300
    }
301
}
302
```