kafka拉取数据，如何获取整个json串

Viewed 43
3.0

CREATE TABLE iot_kafka (
node_id bigint NOT NULL COMMENT '点位 ID',
device_time DATETIME NOT NULL COMMENT '时间',
device_timestamp bigint NULL COMMENT '时间',
uuid varchar(30) NULL COMMENT 'uuid',
iot_json STRING NULL COMMENT 'iot_json',
create_time DATETIMEV2 NOT null default CURRENT_TIMESTAMP COMMENT '时间'
)
ENGINE=OLAP
DUPLICATE KEY(node_id,device_time,device_timestamp)
AUTO PARTITION BY RANGE (DATE_TRUNC(device_time, 'day'))()
DISTRIBUTED BY HASH(node_id) BUCKETS 10
PROPERTIES (
"dynamic_partition.time_unit"="day",
"dynamic_partition.start" = "-1",
"dynamic_partition.end" = "1",
"dynamic_partition.prefix" = "p",
"dynamic_partition.enable" = "true",
"dynamic_partition.replication_num" = "1"
);

----- 创建LOAD -------
STOP ROUTINE LOAD FOR iot_kafka_load;

CREATE ROUTINE LOAD iot_kafka_load ON iot_kafka
COLUMNS(node_id,device_time,device_timestamp,uuid,iot_json)
PROPERTIES(
"format"="json",
"jsonpaths"="["$.node_id","$.create_time","$.device_timestamp","$.uuid","$."]"
)
FROM KAFKA(
"kafka_broker_list" = "192.168.80.7:30092",
"kafka_topic" = "ddps.ts_kv_iot_main_kafka",
"property.kafka_default_offsets" = "OFFSET_END"
);

使用$. 获取整个json串，获取不到。用$也不行。
如何获取整个json串

cpt_doris19
1 Answers

一般是取json里的某个字段，可以通过jsonpaths来获取，对于某个不定长的json串，可以尝试用variant数据类型来导入 。

如果你的数据已经是分割好的json串，比如 {"a": 100,"b":200} | {"a": 300,"b":400}, 你只需要用csv格式来接收数据，同时columns指定对应字段接收整个json串即可，如果你还想要json串里的具体内容，可以这样做：COLUMNS=（temp1，a=json_extract(temp1, '$.[a]'))

黄海军@SelectDB4828