Doris常用操作

CREATE TABLE ods.`ods_source_db_source_order` (

`pt` date NOT NULL COMMENT '分区pt',
`id` bigint NOT NULL COMMENT '主键',
`head_id` bigint NULL COMMENT '上个版本的数据code',
`tail_id` bigint NULL COMMENT '下个版本的数据code',
`pur_code` text NULL COMMENT '业务code',
`title` text NULL COMMENT '标题',
`pur_version` decimal(30,10) NULL COMMENT '1.00 默认 整数位递增为大版本 小数位为小版本',
`purchaser_id` bigint NULL COMMENT '租户ID'
)
DUPLICATE KEY(`pt`)
AUTO partition by range(date_trunc(`pt`, 'day')) ()
DISTRIBUTED BY RANDOM BUCKETS AUTO
PROPERTIES (
-- 分区构建
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-7",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.create_history_partition" = "true",
"compression"="LZ4",
"replication_num"="2", --
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

 

--抽取数据
/opt/module/datax/bin/datax.py -p "-Dmysql_host=rm-8vbo4m43i19019hpa.mysql.zhangbei.rds.aliyuncs.com -Dmysql_port=3306 -Dmysql_db=source_db -Dmysql_user=opdev_rw -Dmysql_passwd=1234\!qwer -Ddoris_host=172.16.1.13 -Dck_port=9030 -Ddoris_user=root -Ddoris_passwd=123456 -Dbizdate=2025-02-27" /opt/module/test/source_order.json

/opt/module/datax/bin/datax.py -p "-Dmysql_host=rm-8vbo4m43i19019hpa.mysql.zhangbei.rds.aliyuncs.com -Dmysql_port=3306 -Dmysql_db=source_db -Dmysql_user=opdev_rw -Dmysql_passwd=1234\!qwer -Ddoris_host=172.16.1.13 -Dck_port=9030 -Ddoris_user=root -Ddoris_passwd=123456 -Dbizdate=2025-02-27" /opt/module/test/source_order_item.json

-- 删除分区
SHOW PARTITIONS FROM ods.ods_source_db_source_order; -- 查看指定表的所有分区
alter table ods.ods_source_db_source_order drop partition if exists p20250220000000;

--clickhouse删除模板
alter table {materialized_table_name} on cluster sunyur_cluster drop partition '{current_date_str}';

--doris删除模板
alter table {materialized_table_name} drop partition if exists 'p{current_date_str}000000';

 

--构建UDF，并测试使用
--参数类型 https://doris.apache.org/zh-CN/docs/query-data/udf/java-user-defined-function
>>创建语句
CREATE GLOBAL FUNCTION FindMax(array<String>) RETURNS String PROPERTIES (
"file"="file:////opt/module/doris-3.0.3/custom_lib/DorisUDF-1.0.jar",
"symbol"="com.sunyur.udf.FindMax",
"always_nullable"="true",
"type"="JAVA_UDF"
);

CREATE GLOBAL FUNCTION FindMin(array<String>) RETURNS String PROPERTIES (
"file"="file:////opt/module/doris-3.0.3/custom_lib/DorisUDF-1.0.jar",
"symbol"="com.sunyur.udf.FindMin",
"always_nullable"="true",
"type"="JAVA_UDF"
);

 

show functions; --查看udf
show global functions; --查看全局udf
drop function findMax(int,int); --删除udf
select findMax(8,2); --使用udf

 

删除语句:
DROP GLOBAL function if exists FindMax(array<String>);

--Doris分区写入测试
CREATE TABLE analysis_dw.test (
`pt` DATE not null COMMENT '分区pt',
test_id BIGINT comment '测试id',
`test_name` STRING COMMENT '测试名称'
)
DUPLICATE KEY(`pt`)
AUTO partition by range(date_trunc(`pt`, 'day')) ()
DISTRIBUTED BY RANDOM BUCKETS AUTO
PROPERTIES (
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.start" = "-7",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.create_history_partition" = "true",
"replication_num"="2", --
"min_load_replica_num" = "-1",
"is_being_synced" = "false",
"storage_medium" = "hdd",
"storage_format" = "V2",
"inverted_index_storage_format" = "V2",
"light_schema_change" = "true",
"disable_auto_compaction" = "false",
"enable_single_replica_compaction" = "false",
"group_commit_interval_ms" = "10000",
"group_commit_data_bytes" = "134217728"
);

insert into analysis_dw.test --插入测试数据
select STR_TO_DATE('2025-02-26', '%Y-%m-%d') pt ,2,'test'

insert into analysis_dw.test --插入测试数据
select STR_TO_DATE('2025-02-27', '%Y-%m-%d') pt ,2,'test3'

insert overwrite table analysis_dw.test partition(p20250227) --覆盖当天分区,分区设置为动态
select current_date() pt ,3,'test4';

 

-- 配置全局变量
set global enable_decimal256 = true;

show global VARIABLES like '%enable_decimal256%'; --查看配置

-- 给function创建别名，用于CK和Doris有差异校验
CREATE ALIAS FUNCTION aaa(VARCHAR, INT, INT) WITH PARAMETER(str, start, length) AS substr(str, start, length);