DML operations

Feature

Supported

Insert or overwrite data (INSERT INTO | INSERT OVERWRITE)

• Supports insert {into|overwrite} table {table_name} [partition ({pt_spec})] {select_statement} from {from_statement};

• Supports inserting data into specific columns: insert into table {table_name} [partition ({pt_spec})] ({col_name} ,{col_name} ...) {select_statement} from {from_statement};

• Supports sorting data during insertion.

  insert {into|overwrite} table <table_name> [partition (<pt_spec>)] [(<col_name> [,<col_name> ...)]]
  <select_statement>
  from <from_statement>
  [zorder by <zcol_name> [, <zcol_name> ...]];

Insert or overwrite data in dynamic partitions (DYNAMIC PARTITION)

Yes

Multi insert

Yes

VALUES

Yes

LOAD

No

Common Table Expression (CTE)

• Supports with table as (select ...) insert into tab select ... from table

• Supports with table as (select ...) select ... from table

Update or delete data (UPDATE | DELETE)

• Supports the update operation to update data in one or more columns of a transactional table or Delta table.

  --Method 1
  update <table_name> set <col1_name> = <value1> [, <col2_name> = <value2> ...] [WHERE <where_condition>];
  
  --Method 2
  update <table_name> set (<col1_name> [, <col2_name> ...]) = (<value1> [, <value2> ...])[WHERE <where_condition>];
  
  --Method 3
  UPDATE <table_name>
         SET <col1_name> = <value1> [ , <col2_name> = <value2> , ... ]
          [ FROM <additional_tables> ]
          [ WHERE <where_condition> ]

• Supports the clear column command to clear columns in standard tables.

  ALTER TABLE <table_name> 
             [partition ( <pt_spec>[, <pt_spec>....] )] 
  					 CLEAR COLUMN column1[, column2, column3, ...]
  			                         [without touch];

• Supports merging files of transactional tables.

  alter table <table_name> [partition (<partition_key> = '<partition_value>' [, ...])] compact {minor|major};

MERGE INTO

Yes

DROP COLUMNS (Delete columns from a table)

Yes