建表DDL：
CREATE TABLE dim_ip_info_copy (
code varchar(512) NULL COMMENT 'SKU',
code_name varchar(512) NULL COMMENT 'SKU名称',
ip1_name varchar(255) NULL COMMENT 'ip1',
ip2_name varchar(255) NULL COMMENT 'iP2',
ip3_name varchar(255) NULL COMMENT 'ip3'
) ENGINE=OLAP
DUPLICATE KEY(code)
COMMENT 'ip'
DISTRIBUTED BY HASH(code) BUCKETS 1
PROPERTIES (
"replication_allocation" = "tag.location.default: 3",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false"
);
插入数据：
insert into center_dim.dim_ip_info_copy
select i.code,
replace(i.name, ' ', '') AS item_name,
substring_index(group_concat(sub_dir.name order by find_in_set(sub_dir.id, d.index_path)), ',', 1) as ip1_name,
substring_index(substring_index(group_concat(sub_dir.name order by find_in_set(sub_dir.id, d.index_path)), ',', 2), ',', -1)
as ip2_name,
substring_index(group_concat(sub_dir.name order by find_in_set(sub_dir.id, d.index_path)), ',', -1) as ip3_name
FROM paimon_catalog.paimon_ods.ods_it_item_dg i
inner join paimon_catalog.paimon_ods.ods_it_dir_dg d ON i.dir_id = d.id
left join paimon_catalog.paimon_ods.ods_it_dir_dg sub_dir ON find_in_set(sub_dir.id, d.index_path)
group by i.code, i.name
order by i.code, i.name;
发现很多的字段都为空
select count(1) from center_dim.dim_ip_info_copy where (ip1_name is null or ip2_name is null or ip3_name is null)
