如何创建Doris数据表和Doris ROUTINE LOAD_创建doris 数据库

第一步：切换用户

su - doris

第二步：登录至目标服务器

mysql -h 10.68.90.11 -P 39030

第三步：选择要操作的目标数据库

use tonly_data

第四步：创建数据表

CREATE TABLE `test_tonly_location`
(
  `upload_time` datetime NULL COMMENT "",
  `vin` varchar(32) NULL COMMENT "",
  `tbox_vin` varchar(32) NULL COMMENT "",
  `gps_x` float NULL COMMENT "",
  `gps_y` float NULL COMMENT "",
  `bd_x` float NULL COMMENT "",
  `bd_y` float NULL COMMENT "",
  `speed` int(11) NULL COMMENT "",
  `angle` int(11) NULL COMMENT "",
  `quadrant` int(11) NULL COMMENT "",
  `altitude` int(11) NULL COMMENT "",
  `cgq_x` float NULL COMMENT "",
  `cgq_y` float NULL COMMENT "",
  `cgq_z` float NULL COMMENT "",
  `source_table` datetime NULL COMMENT ""
) ENGINE = OLAP DUPLICATE KEY(`upload_time`, `vin`)
PARTITION BY RANGE(`upload_time`)
(
START ("2023-08-25") END ("2023-09-05") EVERY (INTERVAL 1 day)
)
DISTRIBUTED BY HASH(`vin`) BUCKETS 32
PROPERTIES (
"replication_num" = "2",
"dynamic_partition.enable" = "true",
"dynamic_partition.time_unit" = "DAY",
"dynamic_partition.time_zone" = "Asia/Shanghai",
"dynamic_partition.start" = "-100",
"dynamic_partition.end" = "3",
"dynamic_partition.prefix" = "p",
"dynamic_partition.buckets" = "32",
"in_memory" = "false",
"storage_format" = "DEFAULT"
);

第五步：创建Doris ROUTINE LOAD

CREATE
ROUTINE LOAD
from_kafka_test_tonly_location ON test_tonly_location
COLUMNS TERMINATED BY ",",
COLUMNS (upload_time,
vin,
tbox_vin,
gps_x,
gps_y,
bd_x,
bd_y,
speed,
angle,
quadrant,
altitude,
cgq_x,
cgq_y,
cgq_z,
source_table)
PROPERTIES
(
    "desired_concurrent_number"="3",
    "max_error_number"="1000",
    "max_batch_interval"="60",
    "max_batch_rows"="200000"
)
FROM KAFKA
(
    "kafka_broker_list"= "10.68.90.11:9092,10.68.90.12:9092,10.68.90.13:9092",
    "kafka_topic" = "test_tonly_location",
    "property.group.id" = "test_tonly_location_group"
);

第六步：查看数据装入任务

show routine load;
 
show routine load for from_kafka_test_tonly_location \G