一、doris的表
略
二、创建一张简单的表
CREATE TABLE example_db.table_hash
(
k1 TINYINT,
k2 DECIMAL(10, 2) DEFAULT "10.5",
k3 CHAR(10) COMMENT "string column",
k4 INT NOT NULL DEFAULT "1" COMMENT "int column"
)
COMMENT "my first table"
DISTRIBUTED BY HASH(k1) BUCKETS 32
三、创建catlog
创建oracle数据源的外部表
CREATE EXTERNAL TABLE `ext_oracle_demo` (
`k1` decimal(9, 3) NOT NULL COMMENT "",
`k2` char(10) NOT NULL COMMENT "",
`k3` datetime NOT NULL COMMENT "",
`k5` varchar(20) NOT NULL COMMENT "",
`k6` double NOT NULL COMMENT ""
) ENGINE=ODBC
COMMENT "ODBC"
PROPERTIES (
"odbc_catalog_resource" = "oracle_test_odbc",
"database" = "oracle",
"table" = "baseall"
);
创建mysql的catalog
CREATE TABLE `external_crm_config_member_price_coupon_relation` (
`id` bigint(20) NOT NULL COMMENT '主键',
`merchant_id` varchar(64) NOT NULL COMMENT '商户id',
`member_price_id` varchar(36) DEFAULT NULL COMMENT '等级模板id',
`level_id` varchar(64) DEFAULT NULL COMMENT '等级id',
`coupon_code` varchar(64) DEFAULT NULL COMMENT '券码',
`start_time` datetime DEFAULT NULL COMMENT '活动开始时间',
`end_time` datetime DEFAULT NULL COMMENT '活动结束时间',
`create_time` datetime NOT NULL COMMENT '创建时间'
) ENGINE=ODBC
COMMENT "external_crm_config_member_price_coupon_relation"
PROPERTIES (
"odbc_catalog_resource" = "mysql_crm",
"database" = "crm_config",
"table" = "member_price_coupon_relation"
);
四、创建带分区和桶的表
CREATE TABLE `data_integration_source_bs_kfc_alipay_3d` (
`batch_id` bigint NULL,
`pdate` date NOT NULL,
`msg_type` varchar(50) NULL,
`msg_source` varchar(20) NULL,
`batch_hours` tinyint NULL,
`batch_time` datetime NULL,
`msg_topic_name` varchar(50) NULL,
`kafka_offset` varchar(50) NULL,
`msg_time` varchar(20) NULL,
`msg_time_format` varchar(50) NULL,
`rectime` varchar(20) NULL,
`rectime_format` varchar(50) NULL,
`sl` varchar(20) NULL,
`sl_format` varchar(50) NULL,
`l` varchar(20) NULL,
`l_format` varchar(50) NULL,
`record_properties` text NULL,
`profile_data` text NULL,
`behavior_index` smallint NULL,
`behavior_data` text NULL,
`kafka_source` varchar(10) NULL,
`appkey` varchar(100) NULL,
`tid` varchar(100) NULL,
`uid` varchar(100) NULL
) ENGINE=OLAP
DUPLICATE KEY(`batch_id`, `pdate`, `msg_type`, `msg_source`, `batch_hours`)
PARTITION BY RANGE(`pdate`)
(PARTITION p20250301 VALUES [('2025-03-01'), ('2025-03-02'))("storage_policy" = "cdp_traffic_s3_policy"),
PARTITION p20250302 VALUES [('2025-03-02'), ('2025-03-03'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250303 VALUES [('2025-03-03'), ('2025-03-04'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250304 VALUES [('2025-03-04'), ('2025-03-05'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250305 VALUES [('2025-03-05'), ('2025-03-06'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250306 VALUES [('2025-03-06'), ('2025-03-07'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250307 VALUES [('2025-03-07'), ('2025-03-08'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250308 VALUES [('2025-03-08'), ('2025-03-09'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250309 VALUES [('2025-03-09'), ('2025-03-10'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250310 VALUES [('2025-03-10'), ('2025-03-11'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250311 VALUES [('2025-03-11'), ('2025-03-12'))("storage_policy" = "cdp_traffic_s3_policy"),PARTITION p20250312 VALUES [('2025-03-12'), ('2025-03-13'))("storage_policy" = "cdp_traffic_s3_policy"))DISTRIBUTED BY HASH(`batch_id`, `pdate`, `msg_type`, `msg_source`, `batch_hours`) BUCKETS 32PROPERTIES ("replication_allocation" = "tag.location.cdptraffic: 3","min_load_replica_num" = "-1","is_being_synced" = "false","dynamic_partition.enable" = "true","dynamic_partition.time_unit" = "day","dynamic_partition.time_zone" = "Asia/Shanghai","dynamic_partition.start" = "-10","dynamic_partition.end" = "1","dynamic_partition.prefix" = "p","dynamic_partition.replication_allocation" = "tag.location.cdptraffic: 3","dynamic_partition.buckets" = "32","dynamic_partition.create_history_partition" = "true","dynamic_partition.history_partition_num" = "-1","dynamic_partition.hot_partition_num" = "0","dynamic_partition.reserved_history_periods" = "NULL","dynamic_partition.storage_policy" = "","storage_medium" = "ssd","storage_format" = "V2","inverted_index_storage_format" = "V1","compression" = "ZSTD","light_schema_change" = "true","storage_policy" = "cdp_traffic_s3_policy","compaction_policy" = "time_series","time_series_compaction_goal_size_mbytes" = "1024","time_series_compaction_file_count_threshold" = "2000","time_series_compaction_time_threshold_seconds" = "3600","time_series_compaction_empty_rowsets_threshold" = "5","time_series_compaction_level_threshold" = "1","disable_auto_compaction" = "false","enable_single_replica_compaction" = "false","group_commit_interval_ms" = "10000","group_commit_data_bytes" = "134217728");五、创建range分区表
-- Range Partition
CREATE TABLE IF NOT EXISTS example_range_tbl
(
`user_id` LARGEINT NOT NULL COMMENT "用户id",
`date` DATE NOT NULL COMMENT "数据灌入日期时间",
`timestamp` DATETIME NOT NULL COMMENT "数据灌入的时间戳",
`city` VARCHAR(20) COMMENT "用户所在城市",
`age` SMALLINT COMMENT "用户年龄",
`sex` TINYINT COMMENT "用户性别",
`last_visit_date` DATETIME REPLACE DEFAULT "1970-01-01 00:00:00" COMMENT "用户最后一次访问时间",
`cost` BIGINT SUM DEFAULT "0" COMMENT "用户总消费",
`max_dwell_time` INT MAX DEFAULT "0" COMMENT "用户最大停留时间",
`min_dwell_time` INT MIN DEFAULT "99999" COMMENT "用户最小停留时间"
)
ENGINE=OLAP
AGGREGATE KEY(`user_id`, `date`, `timestamp`, `city`, `age`, `sex`)
PARTITION BY RANGE(`date`)
(
PARTITION `p201701` VALUES LESS THAN ("2017-02-01"),
PARTITION `p201702` VALUES LESS THAN ("2017-03-01"),
PARTITION `p201703` VALUES LESS THAN ("2017-04-01"),
PARTITION `p2018` VALUES [("2018-01-01"), ("2019-01-01"))
)
DISTRIBUTED BY HASH(`user_id`) BUCKETS 16
PROPERTIES
(
"replication_num" = "1"
);
range分区表
CREATE TABLE example_db.table_hash
(
k1 DATE,
k2 DECIMAL(10, 2) DEFAULT "10.5",
k3 CHAR(10) COMMENT "string column",
k4 INT NOT NULL DEFAULT "1" COMMENT "int column"
)
DUPLICATE KEY(k1, k2)
COMMENT "my first table"
PARTITION BY RANGE(k1)
(
PARTITION p1 VALUES LESS THAN ("2020-02-01"),
PARTITION p2 VALUES LESS THAN ("2020-03-01"),
PARTITION p3 VALUES LESS THAN ("2020-04-01")
)
DISTRIBUTED BY HASH(k1) BUCKETS 32
PROPERTIES (
"replication_num" = "1"
);
