Hudi-SparkSQL增删改查Hudi表
进入spark-sql shell
./spark-sql --master local[2] --jars /Users/FengZhen/Desktop/Hadoop/spark/spark-3.0.3-bin-hadoop2.7/jars/spark-avro_2.12-3.0.3.jar,/Users/FengZhen/Desktop/Hadoop/hudi/spark3.0.3/hudi-0.10.1/packaging/hudi-spark-bundle/target/hudi-spark3.0.3-bundle_2.12-0.10.1.jar --conf 'spark.serializer=org.apache.spark.serializer.KryoSerializer' --conf 'spark.sql.extensions=org.apache.spark.sql.hudi.HoodieSparkSessionExtension'
hudi默认upsert/insert/delete的并发度是1500，对于演示小规模数据集设置更小的并发度
set hoodie.upsert.shuffle.parallelism = 1; set hoodie.insert.shuffle.parallelism = 1; set hoodie.delete.shuffle.parallelism = 1;
设置不同步hudi表元数据
set hoodie.datasource.meta.sync.enable=false;
1.建表（带LOCATION表示为外部表）
创建分区表，表的类型为MOR，主键为id,分区字段为dt,合并字段为ts
CREATE TABLE test_hudi_table( id INT, name STRING, price DOUBLE, ts LONG, dt STRING ) USING hudi PARTITIONED BY (dt) OPTIONS( primaryKey = 'id', preCombineField = 'ts', type = 'mor' ) LOCATION 'hdfs://localhost:9000/hudi-warehouse/test_hudi_table';
根据已有表创建
create table hudi_existing_tbl1 using hudi partitioned by (dt) location 'hdfs://localhost:9000/hudi-warehouse/test_hudi_table';
用查询结果创建新表
CREATE TABLE test_hudi_ctas_cow_tbl USING hudi TBLPROPERTIES (primaryKey = 'id') AS SELECT 1 AS id, 'a1' AS name, 10 AS price;
用查询结果创建分区表
CREATE TABLE test_hudi_ctas_cow_pt_tbl USING hudi TBLPROPERTIES (type = 'cow', primaryKey = 'id', preCombineField = 'ts') partitioned by (dt) AS SELECT 1 AS id, 'a1' AS name, 10 AS price, 1000 AS ts, '2022-03-07' AS dt;
创建parquet数据表
CREATE TABLE test_hudi_parquet_cow_tbl USING parquet location 'hdfs://localhost:9000/hudi-warehouse/test_hudi_table/*.parquet';
利用数据进行分区
CREATE TABLE test_hudi_ctas_cow_pt_tbl1 USING hudi location 'hdfs://localhost:9000/hudi-warehouse/test_hudi_table' options ( type = 'cow', primaryKey = 'id', preCombineField = 'ts' ) partitioned by (datestr) AS SELECT * from parquet_mngd;
查看建表语句
show create table test_hudi_table; CREATE TABLE `default`.`test_hudi_table` ( `_hoodie_commit_time` STRING, `_hoodie_commit_seqno` STRING, `_hoodie_record_key` STRING, `_hoodie_partition_path` STRING, `_hoodie_file_name` STRING, `id` INT, `name` STRING, `price` DOUBLE, `ts` BIGINT, `dt` STRING) USING hudi PARTITIONED BY (dt) LOCATION 'hdfs://localhost:9000/hudi-warehouse/test_hudi_table' TBLPROPERTIES ( 'primaryKey' = 'id', 'type' = 'mor', 'preCombineField' = 'ts')
2.插入数据
INSERT INTO test_hudi_table SELECT 1 AS id, 'hudi' AS name, 10 AS price, 1000 as ts, '2022-03-07' as dt; INSERT INTO test_hudi_table SELECT 2 AS id, 'hudi_2' AS name, 9 AS price, 900 as ts, '2022-03-07' as dt; INSERT INTO test_hudi_table SELECT 3 AS id, 'hudi_3' AS name, 8 AS price, 800 as ts, '2022-03-07' as dt;
3.查询
表结构
spark-sql> desc test_hudi_table; _hoodie_commit_time string NULL _hoodie_commit_seqno string NULL _hoodie_record_key string NULL _hoodie_partition_path string NULL _hoodie_file_name string NULL id int NULL name string NULL price double NULL ts bigint NULL dt string NULL # Partition Information # col_name data_type comment dt string NULL Time taken: 0.341 seconds, Fetched 13 row(s)
数据
SELECT * FROM test_hudi_table; 20220307150033176 20220307150033176_0_1 id:1 dt=2022-03-07 9b4ae2d5-956b-488d-b796-829f4cdac7d2-0_0-27-15_20220307150033176.parquet 1 hudi 10.0 1000 2022-03-07 20220307150243202 20220307150243202_0_2 id:2 dt=2022-03-07 9b4ae2d5-956b-488d-b796-829f4cdac7d2-0_0-62-33_20220307150243202.parquet 2 hudi_2 9.0 900 2022-03-07 20220307150302352 20220307150302352_0_3 id:3 dt=2022-03-07 9b4ae2d5-956b-488d-b796-829f4cdac7d2-0_0-97-51_20220307150302352.parquet 3 hudi_3 8.0 800 2022-03-07
4.更新数据
UPDATE test_hudi_table SET price = 100.0 WHERE id = 1;
5.删除数据
DELETE FROM test_hudi_table WHERE id = 1;
6.MergeInfo
依据判断条件，决定对数据操作时，属于插入insert，更新update，还是删除delete
#按条件合并数据
MERGE INTO tableIdentifier AS target_alias USING (sub_query | tableIdentifier) AS source_alias ON <merge_condition> [ WHEN MATCHED [ AND <condition> ] THEN <matched_action> ] [ WHEN MATCHED [ AND <condition> ] THEN <matched_action> ] [ WHEN NOT MATCHED [ AND <condition> ] THEN <not_matched_action> ] /** <merge_condition> =A equal bool condition <matched_action> = DELETE | UPDATE SET * | UPDATE SET column1 = expression1 [, column2 = expression2 ...] <not_matched_action> = INSERT * | INSERT (column1 [, column2 ...]) VALUES (value1 [, value2 ...]) */
MERGE INTO test_hudi_table AS t0 USING( SELECT 4 AS id, 'hadoop' AS name, 10 AS price, 9000 as ts, '2022-03-07' as dt ) AS s0 ON t0.id = s0.id WHEN NOT MATCHED THEN INSERT *; MERGE INTO test_hudi_table AS t0 USING( SELECT 4 AS id, 'hadoop' AS name, 10 AS price, 9999 as ts, '2022-03-07' as dt ) AS s0 ON t0.id = s0.id WHEN MATCHED THEN UPDATE SET *; MERGE INTO test_hudi_table AS t0 USING( SELECT 4 AS id, 'hadoop' AS name, 10 AS price, 9999 as ts, '2022-03-07' as dt ) AS s0 ON t0.id = s0.id WHEN MATCHED THEN DELETE;
7.修改表相关信息
修改表名 ALTER TABLE oldTableName RENAME TO newTableName 例子 ALTER TABLE test_hudi_cow_tbl RENAME TO test_hudi_cow_tbl1; 增加列簇 ALTER TABLE tableIdentifier ADD COLUMNS(colAndType (,colAndType)*) 例子 ALTER TABLE test_hudi_cow_tbl1 add columns(desc string); 修改列簇类型 ALTER TABLE tableIdentifier CHANGE COLUMN colName colName colType 例子 ALTER TABLE test_hudi_cow_tbl1 change column id id bigint; 修改数据 ALTER TABLE tableIdentifier SET TBLPROPERTIES (key = 'value') 例子 alter table test_hudi_cow_tbl set tblproperties (hoodie.keep.max.commits = '10');
8.分区操作
显示分区 SHOW PARTITIONS tableIdentifier 例程 show partitions test_hudi_cow_pt_tbl; 删除分区 ALTER TABLE tableIdentifier DROP PARTITION ( partition_col_name = partition_col_val [ , ... ] ) 例子 alter table test_hudi_cow_pt_tbl drop partition (dt='2022-03-07');
