Introduction to Common SQL Statements

Update write

• insert into SinkTable values()
• insert into SinkTable select * from SourceTable

It is used to integrate and process Hudi tables and update SinkTable data based on the primary key.

Overwrite

• insert overwrite SinkTable values()
• insert overwrite SinkTable select * from SourceTable

• If SinkTable is a non-partitioned table, the entire table is overwritten.
• If SinkTable is a partitioned table, data is overwritten by partition.

Overwrite

• insert overwrite table SinkTable values()
• insert overwrite table SinkTable select * from SourceTable

• If SinkTable is a non-partitioned table, the entire table is overwritten.
• If SinkTable is a partitioned table, data is overwritten by partition.

bulk_insert write

For details, see Common Parameters and Application Scenarios.

This method applies only to the first migration of Hudi tables.

Update write

• update SinkTable set field name=value where field name=value

It is usually used to quickly correct a small batch of data in Hudi and is not suitable for integrated processing.

merge into Syntax

• merge into SinkTable as t1 using (subquery) as t2 on t1. field = t2. field when matched then

Not recommended.

Deleting row-level data

• delete from SinkTable where field name=value
• delete from SinkTable where field name in (subquery)

The DELETE operation writes data. The larger the amount of data to be deleted, the slower the DELETE operation. This operation is applicable to deleting a small amount of data.

Querying the Hudi table structure

desc formatted SinkTable

Query all information about the Hudi table. This method is recommended.

Querying the Hudi table structure

show create table SinkTable

Query all information about the Hudi table. This method is not recommended because the readability is poor.

Deleting a partition

• Single-level partitioning

  alter table SinkTable drop partition (partition column = partition value)

• Multi-level partitioning

  alter table SinkTable drop partition (partition column 1 = partition value, partition column 2 = partition value, partition column 3...)

• To run this command on the MRS background client, you must source the component_env of Hudi.
• When executing this command on DataArts Studio, if the API connection is used, --conf spark.support.hudi=true must be added to the job running parameter.

Deleting a table

• drop table table name
• drop table table name purge

• Running the drop table table name command for an internal table will delete the table and data storage directory from Hive.
• Running the drop table table name command on a foreign table will delete only the table in Hive but not the data storage directory.
• Run the drop table table name purge command to completely clear the Hudi table. If you run this statement for a COW table, all tables and data directories in Hive will be cleared. If you run this statement for an MOR table, the three tables (primary table, rt table, and ro table) and data directories in Hive are all cleared.

Viewing the compaction plan of the Hudi table

show compaction on SinkTable

Check the value of action in the second column of the returned result. If the value is commit, the plan has been executed. If the value is compaction, the plan has not been executed.

Changing the schema

Hudi Schema Evolution

• When executing this command on DataArts Studio, if the API connection is used, --conf spark.support.hudi=true must be added to the job running parameter.
• For all alter commands, the following two commands must be executed at the same time:

  set hoodie.schema.evolution.enable=true;

  alter table SinkTable xxx;

Performing a compaction

run compaction on SinkTable

You cannot run a single command. For details, see Selecting a Proper Table Service Execution Mode and Common Parameters and Application Scenarios.

Performing the clean operation

run clean on SinkTable

You cannot run a single command. For details, see Selecting a Proper Table Service Execution Mode and Common Parameters and Application Scenarios.

Performing the archive operation

run archivelog on SinkTable

You cannot run a single command. For details, see Selecting a Proper Table Service Execution Mode and Common Parameters and Application Scenarios.