0
# View Operations
1

2
View operations provide view creation and management with Hive-specific properties and syntax.
3

4
## Capabilities
5

6
### View Creation
7

8
Create Hive views with custom properties and field lists.
9

10
```java { .api }
11
/**
12
 * CREATE VIEW statement for Hive dialect
13
 * Creates views with Hive-specific properties and syntax support
14
 */
15
public class SqlCreateHiveView extends SqlCreateView {
16
    /**
17
     * Creates a new Hive view creation statement
18
     * @param pos Parser position information
19
     * @param viewName Name of the view to create
20
     * @param fieldList List of view field names (optional)
21
     * @param query SELECT query that defines the view
22
     * @param ifNotExists Whether to use IF NOT EXISTS clause
23
     * @param comment View comment
24
     * @param properties View properties (TBLPROPERTIES)
25
     */
26
    public SqlCreateHiveView(SqlParserPos pos, SqlIdentifier viewName, SqlNodeList fieldList,
27
                            SqlNode query, boolean ifNotExists, SqlCharStringLiteral comment,
28
                            SqlNodeList properties);
29
}
30
```
31

32
**Usage Examples:**
33

34
```java
35
// Basic view creation
36
String basicViewSql = """
37
    CREATE VIEW sales_summary AS
38
    SELECT 
39
        year,
40
        month,
41
        SUM(amount) as total_sales,
42
        COUNT(*) as transaction_count
43
    FROM sales_data
44
    GROUP BY year, month
45
    """;
46

47
// View with explicit field list
48
String viewWithFieldsSql = """
49
    CREATE VIEW IF NOT EXISTS monthly_sales (
50
        sales_year,
51
        sales_month, 
52
        total_amount,
53
        num_transactions
54
    ) AS
55
    SELECT 
56
        year,
57
        month,
58
        SUM(amount),
59
        COUNT(*)
60
    FROM sales_data
61
    GROUP BY year, month
62
    """;
63

64
// View with comment and properties
65
String viewWithPropertiesSql = """
66
    CREATE VIEW customer_analytics
67
    COMMENT 'Customer analytics aggregated view'
68
    TBLPROPERTIES (
69
        'owner' = 'analytics_team',
70
        'refresh_frequency' = 'daily',
71
        'data_source' = 'customer_data'
72
    )
73
    AS
74
    SELECT 
75
        customer_id,
76
        COUNT(*) as total_transactions,
77
        SUM(amount) as total_spent,
78
        AVG(amount) as avg_transaction,
79
        MAX(transaction_date) as last_transaction
80
    FROM sales_data
81
    GROUP BY customer_id
82
    """;
83

84
// Programmatic view creation
85
SqlIdentifier viewName = new SqlIdentifier("product_summary", SqlParserPos.ZERO);
86

87
// Define field list
88
SqlNodeList fieldList = new SqlNodeList(SqlParserPos.ZERO);
89
fieldList.add(new SqlIdentifier("product_name", SqlParserPos.ZERO));
90
fieldList.add(new SqlIdentifier("total_sales", SqlParserPos.ZERO));
91
fieldList.add(new SqlIdentifier("avg_price", SqlParserPos.ZERO));
92

93
// Define properties
94
SqlNodeList properties = new SqlNodeList(SqlParserPos.ZERO);
95
properties.add(new SqlTableOption("owner", "product_team", SqlParserPos.ZERO));
96
properties.add(new SqlTableOption("update_frequency", "hourly", SqlParserPos.ZERO));
97

98
// Create query (would be parsed separately)
99
String queryString = """
100
    SELECT 
101
        product_name, 
102
        SUM(amount) as total_sales, 
103
        AVG(amount) as avg_price 
104
    FROM sales_data 
105
    GROUP BY product_name
106
    """;
107
SqlNode query = SqlParser.create(queryString).parseQuery();
108

109
SqlCreateHiveView createView = new SqlCreateHiveView(
110
    SqlParserPos.ZERO,
111
    viewName,
112
    fieldList,
113
    query,
114
    true, // IF NOT EXISTS
115
    SqlLiteral.createCharString("Product sales summary view", SqlParserPos.ZERO),
116
    properties
117
);
118
```
119

120
### View Properties Alteration
121

122
Change properties of an existing view.
123

124
```java { .api }
125
/**
126
 * ALTER VIEW SET TBLPROPERTIES statement  
127
 * Changes the properties of an existing view
128
 */
129
public class SqlAlterHiveViewProperties extends SqlAlterViewProperties {
130
    /**
131
     * Creates view properties alteration statement
132
     * @param pos Parser position information
133
     * @param tableName Name of view to alter (views use table name parameter)
134
     * @param propertyList New properties to set
135
     */
136
    public SqlAlterHiveViewProperties(SqlParserPos pos, SqlIdentifier tableName, 
137
                                     SqlNodeList propertyList);
138
}
139
```
140

141
**Usage Examples:**
142

143
```java
144
// Change view properties
145
String alterViewPropsSql = """
146
    ALTER VIEW sales_summary SET TBLPROPERTIES (
147
        'last_updated' = '2023-12-01',
148
        'owner' = 'new_analytics_team',
149
        'retention_period' = '90'
150
    )
151
    """;
152

153
// Programmatic view properties change
154
SqlIdentifier viewName = new SqlIdentifier("customer_analytics", SqlParserPos.ZERO);
155
SqlNodeList newProperties = new SqlNodeList(SqlParserPos.ZERO);
156

157
newProperties.add(new SqlTableOption("refresh_frequency", "twice_daily", SqlParserPos.ZERO));
158
newProperties.add(new SqlTableOption("quality_score", "95", SqlParserPos.ZERO));
159
newProperties.add(new SqlTableOption("data_lineage", "sales_data,customer_profile", SqlParserPos.ZERO));
160

161
SqlAlterHiveViewProperties alterViewProps = new SqlAlterHiveViewProperties(
162
    SqlParserPos.ZERO,
163
    viewName,
164
    newProperties
165
);
166
```
167

168
## Advanced View Operations
169

170
### Complex View Queries
171

172
Views can contain complex queries with joins, subqueries, and window functions:
173

174
```java
175
// View with complex query
176
String complexViewSql = """
177
    CREATE VIEW customer_segments
178
    COMMENT 'Customer segmentation based on purchase behavior'
179
    TBLPROPERTIES (
180
        'business_logic' = 'customer_segmentation_v2',
181
        'depends_on' = 'sales_data,customer_profile'
182
    )
183
    AS
184
    SELECT 
185
        c.customer_id,
186
        c.customer_name,
187
        c.registration_date,
188
        s.total_spent,
189
        s.transaction_count,
190
        s.avg_transaction_amount,
191
        CASE 
192
            WHEN s.total_spent > 10000 THEN 'Premium'
193
            WHEN s.total_spent > 5000 THEN 'Gold' 
194
            WHEN s.total_spent > 1000 THEN 'Silver'
195
            ELSE 'Bronze'
196
        END as customer_segment,
197
        ROW_NUMBER() OVER (
198
            PARTITION BY 
199
                CASE 
200
                    WHEN s.total_spent > 10000 THEN 'Premium'
201
                    WHEN s.total_spent > 5000 THEN 'Gold'
202
                    WHEN s.total_spent > 1000 THEN 'Silver'
203
                    ELSE 'Bronze'
204
                END 
205
            ORDER BY s.total_spent DESC
206
        ) as segment_rank
207
    FROM customer_profile c
208
    JOIN (
209
        SELECT 
210
            customer_id,
211
            SUM(amount) as total_spent,
212
            COUNT(*) as transaction_count,
213
            AVG(amount) as avg_transaction_amount
214
        FROM sales_data
215
        WHERE transaction_date >= DATE_SUB(CURRENT_DATE, 365)
216
        GROUP BY customer_id
217
    ) s ON c.customer_id = s.customer_id
218
    """;
219
```
220

221
### Partitioned Views
222

223
Views can be created over partitioned tables:
224

225
```java
226
// View over partitioned table
227
String partitionedViewSql = """
228
    CREATE VIEW recent_sales
229
    COMMENT 'Sales data for the last 3 months'
230
    AS
231
    SELECT 
232
        id,
233
        customer_id,
234
        product_name,
235
        amount,
236
        transaction_date,
237
        year,
238
        month
239
    FROM sales_data
240
    WHERE year = YEAR(CURRENT_DATE)
241
      AND month >= MONTH(CURRENT_DATE) - 2
242
    """;
243
```
244

245
### View Dependencies and Lineage
246

247
Track view dependencies through properties:
248

249
```java
250
// View with dependency tracking
251
String dependencyTrackingViewSql = """
252
    CREATE VIEW sales_kpis
253
    COMMENT 'Key performance indicators for sales'
254
    TBLPROPERTIES (
255
        'depends_on' = 'sales_data,customer_profile,product_catalog',
256
        'created_by' = 'analytics_pipeline',
257
        'lineage_level' = '2',
258
        'refresh_dependencies' = 'sales_data:daily,customer_profile:weekly'
259
    )
260
    AS
261
    SELECT 
262
        DATE(transaction_date) as sales_date,
263
        COUNT(DISTINCT customer_id) as unique_customers,
264
        COUNT(*) as total_transactions,
265
        SUM(amount) as total_revenue,
266
        AVG(amount) as avg_transaction_value,
267
        COUNT(DISTINCT product_name) as unique_products_sold
268
    FROM sales_data s
269
    JOIN customer_profile c ON s.customer_id = c.customer_id
270
    JOIN product_catalog p ON s.product_name = p.product_name
271
    GROUP BY DATE(transaction_date)
272
    """;
273
```
274

275
### View Management Patterns
276

277
```java
278
public class HiveViewManager {
279
    private TableEnvironment tableEnv;
280
    private SqlParser parser;
281
    
282
    public HiveViewManager(TableEnvironment tableEnv) {
283
        this.tableEnv = tableEnv;
284
        this.parser = SqlParser.create("", 
285
            SqlParser.config().withParserFactory(FlinkHiveSqlParserImpl.FACTORY));
286
    }
287
    
288
    /**
289
     * Creates a view with standard properties
290
     */
291
    public void createStandardView(String viewName, String query, String owner, String description) {
292
        String createViewSql = String.format("""
293
            CREATE VIEW IF NOT EXISTS %s
294
            COMMENT '%s'
295
            TBLPROPERTIES (
296
                'owner' = '%s',
297
                'created_date' = '%s',
298
                'view_type' = 'analytical'
299
            )
300
            AS %s
301
            """, viewName, description, owner, LocalDate.now().toString(), query);
302
        
303
        try {
304
            tableEnv.executeSql(createViewSql);
305
            System.out.println("Created view: " + viewName);
306
        } catch (Exception e) {
307
            System.err.println("Failed to create view " + viewName + ": " + e.getMessage());
308
        }
309
    }
310
    
311
    /**
312
     * Updates view properties for maintenance
313
     */
314
    public void updateViewMaintenance(String viewName, String lastRefresh, String dataQuality) {
315
        String updatePropsSql = String.format("""
316
            ALTER VIEW %s SET TBLPROPERTIES (
317
                'last_refresh' = '%s',
318
                'data_quality_score' = '%s',
319
                'last_validated' = '%s'
320
            )
321
            """, viewName, lastRefresh, dataQuality, LocalDateTime.now().toString());
322
        
323
        try {
324
            tableEnv.executeSql(updatePropsSql);
325
            System.out.println("Updated maintenance properties for view: " + viewName);
326
        } catch (Exception e) {
327
            System.err.println("Failed to update view properties: " + e.getMessage());
328
        }
329
    }
330
    
331
    /**
332
     * Creates a materialized view pattern (using table + refresh logic)
333
     */
334
    public void createMaterializedViewPattern(String viewName, String tableName, String query) {
335
        // Create underlying table
336
        String createTableSql = String.format("""
337
            CREATE TABLE IF NOT EXISTS %s_materialized
338
            STORED AS PARQUET
339
            TBLPROPERTIES (
340
                'materialized_view' = 'true',
341
                'source_view' = '%s'
342
            )
343
            AS %s
344
            """, viewName, viewName, query);
345
        
346
        // Create view over materialized table
347
        String createViewSql = String.format("""
348
            CREATE VIEW IF NOT EXISTS %s
349
            COMMENT 'Materialized view backed by %s_materialized table'
350
            TBLPROPERTIES (
351
                'view_type' = 'materialized',
352
                'backing_table' = '%s_materialized'
353
            )
354
            AS SELECT * FROM %s_materialized
355
            """, viewName, viewName, viewName, viewName);
356
        
357
        try {
358
            tableEnv.executeSql(createTableSql);
359
            tableEnv.executeSql(createViewSql);
360
            System.out.println("Created materialized view pattern: " + viewName);
361
        } catch (Exception e) {
362
            System.err.println("Failed to create materialized view: " + e.getMessage());
363
        }
364
    }
365
}
366

367
// Usage
368
HiveViewManager viewManager = new HiveViewManager(tableEnv);
369

370
// Create standard analytical view
371
String salesAnalysisQuery = """
372
    SELECT 
373
        year, month,
374
        SUM(amount) as total_sales,
375
        COUNT(*) as transaction_count,
376
        COUNT(DISTINCT customer_id) as unique_customers
377
    FROM sales_data 
378
    GROUP BY year, month
379
    """;
380

381
viewManager.createStandardView(
382
    "monthly_sales_analysis", 
383
    salesAnalysisQuery, 
384
    "analytics_team",
385
    "Monthly sales aggregation for reporting"
386
);
387

388
// Update view maintenance properties
389
viewManager.updateViewMaintenance("monthly_sales_analysis", "2023-12-01", "98.5");
390

391
// Create materialized view for performance
392
String expensiveQuery = """
393
    SELECT 
394
        customer_id,
395
        COUNT(*) as lifetime_transactions,
396
        SUM(amount) as lifetime_value,
397
        MIN(transaction_date) as first_purchase,
398
        MAX(transaction_date) as last_purchase,
399
        DATEDIFF(MAX(transaction_date), MIN(transaction_date)) as customer_lifespan_days
400
    FROM sales_data
401
    GROUP BY customer_id
402
    """;
403

404
viewManager.createMaterializedViewPattern("customer_lifetime_value", "clv_table", expensiveQuery);
405
```
406

407
### View Security and Access Control
408

409
Views can implement row-level security and access control:
410

411
```java
412
// Security view with row-level filtering
413
String securityViewSql = """
414
    CREATE VIEW secure_sales_data
415
    COMMENT 'Sales data with row-level security'
416
    TBLPROPERTIES (
417
        'security_enabled' = 'true',
418
        'access_control' = 'row_level'
419
    )
420
    AS
421
    SELECT 
422
        id,
423
        customer_id,
424
        product_name,
425
        amount,
426
        transaction_date,
427
        year,
428
        month
429
    FROM sales_data
430
    WHERE year >= YEAR(CURRENT_DATE) - 1  -- Only recent data
431
      AND amount < 10000  -- Hide large transactions
432
    """;
433
```