Lakehouse SQL DML Statement Usage Guide

1. INSERT Statement Specification

1.1 Basic Syntax

INSERT INTO|OVERWRITE [TABLE] table_name [ PARTITION partition_spec] [ (column1, column2, ...)] {VALUES(value1 [,...],(value2 [,...]),...) | subquery}

1.2 Recommended Data Import Methods

Bulk Data Import - Preferred Approach

• Recommended: INSERT INTO...SELECT statement
• Recommended: COPY INTO command with Volume storage
• Recommended: Professional data import tools

-- ✅ Recommended: Use SELECT method INSERT INTO target_table SELECT col1, col2, col3 FROM source_table WHERE condition; -- ✅ Recommended: Use COPY INTO command COPY INTO target_table FROM VOLUME my_volume USING CSV OPTIONS ('header' = 'true');

Small Data Import - VALUES Method

-- ✅ Suitable for: Small data volume (recommended within 100 rows) INSERT INTO table_name VALUES (1, 'data1'), (2, 'data2'), (3, 'data3');

Advantages of Recommended Methods:

• Higher import performance and throughput
• Better resource utilization
• Transactional guarantee support
• Reduced network transmission overhead

1.3 Data Type Literal Syntax

Types Requiring Prefixes

date'2023-12-25' -- DATE type timestamp'2023-12-25 15:30:45' -- TIMESTAMP type timestamp'2023-12-25 15:30:45.123' -- Millisecond precision supported json'{"key": "value", "num": 123}' -- JSON type X'48656C6C6F' -- BINARY type (hexadecimal)

Types with Optional Suffixes

-- Numeric type suffixes are optional; both forms are valid 1 -- or 1l (BIGINT) 100 -- or 100s (SMALLINT) 200 -- INT type 89.5 -- or 89.5f (FLOAT) 3.14159 -- or 3.14159d (DOUBLE) 99.99 -- or 99.99bd (DECIMAL)

Composite Type Syntax

ARRAY(1,2,3) -- ARRAY type MAP('k1','v1','k2','v2') -- MAP type STRUCT(1, 'hello', 3.14) -- STRUCT type

1.4 INSERT OVERWRITE Behavior

• Partitioned Table: Overwrites matching partition data
• Non-partitioned Table: Overwrites entire table data
• Prerequisite: Target table must exist

1.5 Partition Operation Limits

• Partition Count Limit: Maximum 2048 partitions per task
• Exceeding Limit: Import in batches or optimize partition strategy
• Recommended Check: Count partitions before bulk import

1.6 Column Mapping and Type Matching

• Explicit Specification: Recommended to explicitly specify target column names
• Type Matching: Ensure precise data type correspondence
• NULL Handling: Unspecified columns will be filled with NULL values

2. UPDATE Statement Specification

2.1 Basic Syntax

UPDATE target_table SET column_name1 = new_value1 [, column_name2 = new_value2, ...] [ WHERE condition ] [ORDER BY ...] [LIMIT row_count]

2.2 WHERE Condition Requirements

• Necessity: Strongly recommended to use WHERE conditions to limit the update scope
• Precision: Use precise conditions to avoid mistaken operations
• Complex Queries: Subqueries and expressions are supported

2.3 Batch Update Optimization

• Batch Processing: Use ORDER BY + LIMIT for batched updates
• Determinism: ORDER BY ensures consistent update ordering
• Performance Control: LIMIT controls the number of rows updated per batch

2.4 Safe Operation Recommendations

• Test First: Validate in a test environment before production
• Data Backup: Create backups before important updates
• Rollback Preparation: Prepare a data recovery plan

3. DELETE Statement Specification

3.1 Basic Syntax

DELETE FROM table_name WHERE condition;

3.2 Safety Requirements

• WHERE Condition: Avoid omitting WHERE to prevent full table deletion
• Condition Validation: Verify condition accuracy before deletion
• Backup Protection: Back up important data before deletion

3.3 Performance Optimization

• Index Utilization: Fully leverage indexes in WHERE conditions
• Partition Filtering: Use partition columns for filtering on partitioned tables
• Batch Deletion: Consider batched execution for large-scale deletions

4. MERGE INTO Statement Specification

4.1 Basic Syntax

MERGE INTO target_table USING source_table ON merge_condition { WHEN MATCHED [AND matched_condition] THEN matched_action | WHEN NOT MATCHED [AND not_matched_condition] THEN not_matched_action } ...

4.2 Statement Order Requirements

WHEN MATCHED must precede WHEN NOT MATCHED

-- ✅ Correct order MERGE INTO target USING source ON target.key = source.key WHEN MATCHED THEN UPDATE SET target.col1 = source.col1 WHEN NOT MATCHED THEN INSERT (col1, col2) VALUES (source.col1, source.col2);

4.3 Match Condition Design

• Uniqueness: Ensure the ON condition produces one-to-one matching
• Determinism: Avoid multiple source rows matching the same target row
• Filter Support: AND conditions are supported for additional filtering

4.4 Operation Types

• MATCHED Operations: UPDATE SET or DELETE
• NOT MATCHED Operations: INSERT statement
• Conditional Execution: Multiple WHEN clauses are executed in the specified order

5. TRUNCATE Statement Specification

5.1 Basic Syntax

TRUNCATE TABLE [IF EXISTS] table_name;

5.2 Operational Characteristics

• Data Clearing: Deletes all records but retains the table structure
• Performance Advantage: More efficient than DELETE FROM
• Irrecoverable: Data cannot be directly recovered after the operation

5.3 Usage Recommendations

• IF EXISTS: Use the IF EXISTS clause to avoid errors
• Permission Check: Ensure you have the appropriate operation permissions
• Backup Protection: Back up data before operating on important tables

6. Dynamic Table DML Specification

6.1 Parameter Configuration

-- Recommended to explicitly enable DML operations set cz.optimizer.incremental.backfill.enabled=true;

6.2 Supported Operations

-- ✅ Fully supported INSERT INTO dynamic_table VALUES (1, 'data', 100); INSERT OVERWRITE dynamic_table SELECT * FROM source; DELETE FROM dynamic_table WHERE condition; TRUNCATE TABLE dynamic_table;

6.3 Operation Limitations

• UPDATE Limitation: UPDATE operations have technical limitations; using DELETE + INSERT as a workaround is recommended
• Refresh Impact: DML operations may cause the next refresh to switch to full mode
• Performance Consideration: Full refresh incurs higher overhead than incremental refresh

7. Performance Optimization Strategies

7.1 Partition Design Principles

• Partition Size: Follow industry standards; avoid overly small partitions that affect query performance
• Partition Count: Control the total number of partitions, balancing storage and query efficiency
• Filter Optimization: Partition columns should be commonly used filter conditions

7.2 Bucketing Configuration Strategy

• Bucket Column Selection: Choose high-cardinality, evenly-distributed columns
• Bucket Count: Determine a reasonable number based on data volume and query patterns
• Sort Optimization: Choose frequently queried columns for SORTED BY

7.3 Index Optimization

• BLOOM FILTER Index: Suitable for equality queries and high-cardinality columns
• INVERTED Index: Suitable for full-text search; requires specifying an analyzer
• VECTOR Index: Suitable for vector similarity search scenarios

7.4 Small File Management

-- Auto-compaction configuration SET cz.sql.compaction.after.commit = true; -- Manual compaction command OPTIMIZE table_name [WHERE predicate] [OPTIONS ('key' = 'value')];

8. Data Type Conversion

8.1 Conversion Methods

-- CAST function CAST(expression AS type) -- Conversion operator expression::type -- TYPE function (returns NULL on conversion failure) TYPE(expr)

8.2 Conversion Rules

• Numeric Widening: Conversions that expand precision are supported
• String Conversion: Conversions that increase length are supported
• Date Conversion: Bidirectional conversion between strings and date types
• Overflow Handling: Be aware of numeric conversion overflow risks

8.3 TIMESTAMP Handling

• Format Support: Standard format, millisecond precision, ISO 8601
• Timezone Handling: Default TIMESTAMP_LTZ type
• Precision Support: Up to microsecond precision supported

9. Transaction and Version Control

9.1 Historical Version Query

View Table History

-- View complete operation history of a table DESCRIBE HISTORY table_name;

Returned information includes:

• version: Version number
• time: Operation time
• total_rows: Total row count for that version
• operation: Operation type (CREATE, INSERT_INTO, UPDATE, DELETE, TRUNCATE, etc.)
• user: Executing user
• job_id: Job ID

Time Travel Queries

-- Query historical data using relative time SELECT * FROM table_name TIMESTAMP AS OF (CURRENT_TIMESTAMP() - INTERVAL '1' HOUR); -- Query using absolute time (requires a precise timestamp) SELECT * FROM table_name TIMESTAMP AS OF '2025-06-18 10:30:45.123'; -- Use CAST function to specify timezone SELECT * FROM table_name TIMESTAMP AS OF CAST('2025-06-18 10:30:45 Asia/Shanghai' AS TIMESTAMP);

9.2 Data Recovery Operations

Table Data Recovery

-- Restore table to a specified point in time RESTORE TABLE table_name TO TIMESTAMP AS OF '2025-06-18 10:30:45'; -- Restore Dynamic Table RESTORE DYNAMIC TABLE table_name TO TIMESTAMP AS OF '2025-06-18 10:30:45';

Supported time formats:

• Full timestamp: '2025-06-18 10:30:45.123'
• Second-level precision: '2025-06-18 10:30:45'
• With timezone: '2025-06-18 10:30:45 Asia/Shanghai'
• Relative time: CURRENT_TIMESTAMP() - INTERVAL '1' DAY

Recover Dropped Objects

-- Recover a dropped table UNDROP TABLE table_name; -- Recover a dropped Dynamic Table UNDROP DYNAMIC TABLE table_name; -- Recover a dropped Materialized View UNDROP MATERIALIZED VIEW view_name;

9.3 Change Tracking Configuration

Enable Change Tracking

-- Enable change tracking for a table ALTER TABLE table_name SET PROPERTIES('change_tracking' = 'true');

Create Table Stream

-- Create a Table Stream in standard mode CREATE TABLE STREAM stream_name ON TABLE table_name WITH PROPERTIES ('TABLE_STREAM_MODE' = 'STANDARD'); -- Create a Table Stream in append-only mode CREATE TABLE STREAM stream_name ON TABLE table_name WITH PROPERTIES ('TABLE_STREAM_MODE' = 'APPEND_ONLY');

9.4 Data Retention Policy

Set Data Retention Period

-- Set the Time Travel data retention period (unit: days) ALTER TABLE table_name SET PROPERTIES('data_retention_days' = '7'); -- Set the data lifecycle (auto-clean historical data) ALTER TABLE table_name SET PROPERTIES('data_lifecycle' = '365');

Query Historical Load Records

-- View historical load records for a table SELECT * FROM load_history('schema.table_name');

10. System Parameter Configuration

10.1 DML-Related Parameters

-- Enable DML for Dynamic Tables set cz.optimizer.incremental.backfill.enabled=true; -- Auto-compaction for small files SET cz.sql.compaction.after.commit = true; -- Query tag setting SET query_tag = 'dml_operation'; -- Session timezone configuration SET timezone = 'Asia/Shanghai';

10.2 Workspace-Level Configuration

Auto Index Recommendation

-- Enable workspace-level auto index recommendation ALTER WORKSPACE workspace_name SET properties (auto_index='day[,150,5,100]');

Parameter description:

• day: Recommendation frequency (daily)
• 150: Query count threshold
• 5: Query duration threshold (seconds)
• 100: Index recommendation count limit

11. Error Handling Guide

11.1 Common Error Types

Data Type Conversion Error

Error message: implicit cast not allowed for 'colX': string not null to date/timestamp/json/binary Solution: Use the correct type prefix syntax

Partition Count Exceeded Error

Error message: The count of dynamic partitions exceeds the maximum number 2048 Solution: Import in batches or optimize the partition strategy

MERGE Statement Order Error

Error message: Syntax error at or near 'WHEN' Solution: Adjust the order of WHEN clauses

Dynamic Table UPDATE Limitation

Error message: Not support hidden column :MV__KEY Solution: Use DELETE + INSERT instead of UPDATE

11.2 Performance Diagnosis

-- Query execution plan EXPLAIN SELECT * FROM table_name WHERE condition; -- Check partition information SHOW PARTITIONS EXTENDED table_name;

12. Best Practices

12.1 Data Type Usage Specification

Data Type	Prefix Required	Syntax Example
DATE	Required	date'2023-12-25'
TIMESTAMP	Required	timestamp'2023-12-25 15:30:45'
JSON	Required	json'{"key": "value"}'
BINARY	Required	X'48656C6C6F'
BIGINT	Optional	1 or 1l
DECIMAL	Optional	99.99 or 99.99bd
FLOAT	Optional	89.5 or 89.5f
DOUBLE	Optional	3.14 or 3.14d

12.2 INSERT Statement Template

-- Recommended bulk data import INSERT INTO target_table SELECT col1, col2, col3 FROM source_table WHERE condition; -- Type-safe VALUES insertion INSERT INTO table_name ( bigint_col, decimal_col, date_col, timestamp_col, json_col, binary_col ) VALUES ( 1, 99.99, date'2023-12-25', timestamp'2023-12-25 15:30:45', json'{"key": "value"}', X'48656C6C6F' );

12.3 MERGE Statement Template

MERGE INTO target_table AS target USING source_table AS source ON target.key_column = source.key_column WHEN MATCHED THEN UPDATE SET target.col1 = source.col1, target.col2 = source.col2 WHEN NOT MATCHED THEN INSERT (key_column, col1, col2) VALUES (source.key_column, source.col1, source.col2);

12.4 Dynamic Table DML Template

-- Session configuration set cz.optimizer.incremental.backfill.enabled=true; -- Supported operations INSERT INTO dynamic_table VALUES (1, 'data', 100); DELETE FROM dynamic_table WHERE condition; -- Workaround for UPDATE DELETE FROM dynamic_table WHERE key_column = target_value; INSERT INTO dynamic_table VALUES (new_key, new_col1, new_col2);

12.5 Safe Operation Principles

1. Test First: Complete test validation before production operations
2. Backup Protection: Create backups before critical data operations
3. Least Privilege: Use the minimum necessary permissions for operations
4. Precise Conditions: Use precise WHERE conditions to limit operation scope
5. Monitor and Audit: Record execution logs of important DML operations

12.6 Performance Optimization Principles

1. Batch First: Prioritize batch operations for efficiency
2. Index Utilization: Fully leverage indexes to accelerate queries and DML
3. Partition Filtering: Use partition pruning to reduce data scanning
4. Resource Management: Properly configure compute resources and concurrency
5. File Management: Regularly perform small file compaction optimization

12.7 Version Control and Data Recovery Principles

1. Set Data Retention: Configure the data retention period based on business requirements
2. Enable Change Tracking: Enable change tracking for important tables to facilitate data auditing
3. Regular History Review: Periodically review table operation history to detect anomalies
4. Verify Recovery Operations: Validate recovery results in a test environment before executing
5. Time Travel Queries: Use relative time queries to avoid timezone issues

12.8 Table Stream Usage Principles

-- Recommended Stream creation and usage pattern -- 1. Enable change tracking ALTER TABLE source_table SET PROPERTIES('change_tracking' = 'true'); -- 2. Create Stream CREATE TABLE STREAM change_stream ON TABLE source_table WITH PROPERTIES ('TABLE_STREAM_MODE' = 'STANDARD'); -- 3. Query change data SELECT * FROM change_stream WHERE cz_stream_action IN ('INSERT', 'UPDATE', 'DELETE');

12.9 Historical Version and Data Recovery Template

-- View table history DESCRIBE HISTORY table_name; -- Time Travel query SELECT * FROM table_name TIMESTAMP AS OF (CURRENT_TIMESTAMP() - INTERVAL '1' HOUR); -- Data recovery RESTORE TABLE table_name TO TIMESTAMP AS OF '2025-06-18 10:30:45'; -- Recover dropped table UNDROP TABLE table_name; -- Enable change tracking ALTER TABLE table_name SET PROPERTIES('change_tracking' = 'true'); -- Create Table Stream CREATE TABLE STREAM stream_name ON TABLE table_name WITH PROPERTIES ('TABLE_STREAM_MODE' = 'STANDARD'); -- Set data retention period ALTER TABLE table_name SET PROPERTIES('data_retention_days' = '7');

---

Note: This document is compiled based on the Lakehouse product documentation as of June 2025. It is recommended to regularly check the official documentation for the latest updates. Before using in a production environment, always verify the correctness and performance impact of all operations in a test environment.