Dataphin: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>;

• Enables insertion of data into specific columns: insert into table <table_name> [partition (<pt_spec>)] (<col_name>, <col_name> ...) <select_statement> from <from_statement>;

• Allows for sorting the data being inserted.

  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 Dynamic Partition Data (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 update operations to update single or multiple column data in Transactional tables or Delta tables to new values.

  -- 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 using 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 the merging of Transactional table files.

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

MERGE INTO

Yes

DROP COLUMNS (Delete Columns from Table)

Yes