2.X 主键模型 使用stream 导入数据无法指定__op

💬 StarRocks 用户问答
#1

【StarRocks版本】2.1.3
【集群规模】1FE + 7BE
建表语句：
create table test.pageview(
uid varchar(64) not null,
visit_date date not null,
url varchar(64),
duration int
)PRIMARY KEY (uid,visit_date)
PARTITION BY RANGE(visit_date)(
PARTITION p20220201 VALUES [(‘2022-02-01’), (‘2022-02-02’)),
PARTITION p20220202 VALUES [(‘2022-02-02’), (‘2022-02-03’)),
PARTITION p20220203 VALUES [(‘2022-02-03’), (‘2022-02-04’)),
PARTITION p20220204 VALUES [(‘2022-02-04’), (‘2022-02-05’)),
PARTITION p20220205 VALUES [(‘2022-02-05’), (‘2022-02-06’)),
PARTITION p20220206 VALUES [(‘2022-02-06’), (‘2022-02-07’)),
PARTITION p20220207 VALUES [(‘2022-02-07’), (‘2022-02-08’)),
PARTITION p20220208 VALUES [(‘2022-02-08’), (‘2022-02-09’)),
PARTITION p20220209 VALUES [(‘2022-02-09’), (‘2022-02-10’)),
PARTITION p20220210 VALUES [(‘2022-02-10’), (‘2022-02-11’)),
PARTITION p20220211 VALUES [(‘2022-02-11’), (‘2022-02-12’)),
PARTITION p20220212 VALUES [(‘2022-02-12’), (‘2022-02-13’)),
PARTITION p20220213 VALUES [(‘2022-02-13’), (‘2022-02-14’)),
PARTITION p20220214 VALUES [(‘2022-02-14’), (‘2022-02-15’)),
PARTITION p20220215 VALUES [(‘2022-02-15’), (‘2022-02-16’)),
PARTITION p20220216 VALUES [(‘2022-02-16’), (‘2022-02-17’)),
PARTITION p20220217 VALUES [(‘2022-02-17’), (‘2022-02-18’)),
PARTITION p20220218 VALUES [(‘2022-02-18’), (‘2022-02-19’)),
PARTITION p20220219 VALUES [(‘2022-02-19’), (‘2022-02-20’)),
PARTITION p20220220 VALUES [(‘2022-02-20’), (‘2022-02-21’)),
PARTITION p20220221 VALUES [(‘2022-02-21’), (‘2022-02-22’)),
PARTITION p20220222 VALUES [(‘2022-02-22’), (‘2022-02-23’)),
PARTITION p20220223 VALUES [(‘2022-02-23’), (‘2022-02-24’)),
PARTITION p20220224 VALUES [(‘2022-02-24’), (‘2022-02-25’)),
PARTITION p20220225 VALUES [(‘2022-02-25’), (‘2022-02-26’)),
PARTITION p20220226 VALUES [(‘2022-02-26’), (‘2022-02-27’)),
PARTITION p20220227 VALUES [(‘2022-02-27’), (‘2022-02-28’)),
PARTITION p20220228 VALUES [(‘2022-02-28’), (‘2022-03-01’))
)
DISTRIBUTED BY HASH(uid) BUCKETS 4;

测试数据：
3ffb5441-e7f8-476a-8dc7-ecf0e90ac777,2022-02-25,www.qq.com,23500,0
d80d7f3d-c8fe-496e-922c-32aa7d49f071,2022-02-12,www.163.com,135191,0
8bd31306-322f-4c24-9b97-fd324496b18c,2022-02-11,www.qq.com,246363,0
444631fb-46d7-4154-a9f2-8f0d1f467fb2,2022-02-05,www.163.com,102227,0
cd42f612-476e-4800-84c7-1af162b7a759,2022-02-04,www.ff.com.cn,35030,0
7f23f42d-453c-4ad0-84f0-bc4556cdd01a,2022-02-18,www.fe.com,129691,0
50654f54-f848-46de-b255-d6cc08141c36,2022-02-01,www.ff.com.cn,155038,0
d77569be-bbf7-4f98-b8e5-86787ad08bf4,2022-02-11,www.163.com,157983,1
0d3dd19f-6edd-4781-8a18-b3757f0d1fb4,2022-02-14,www.ff.com.cn,37941,0
f34ba686-e33a-4728-909f-5a10b178979e,2022-02-04,www.163.com,74563,0
10185be8-0fe2-4cde-9b95-a98084058015,2022-02-13,www.163.com,17986,1
68e31c26-7df2-4522-a5e3-0c968d5f406b,2022-02-24,www.ff.com,19188,0
b902f04b-2202-46cd-9200-c00cde7b79cd,2022-02-20,www.163.com,199253,0

表中有4列，测试数据有5列，最后一列标识upsert或者delete，使用stream load导入数据失败：
curl --location-trusted -u root:Xy123 -T /opt/test_data.log -H "label:a123a" -H "column_separator:," -H "cloumns:uid,visit_date,url,duration,tmp,__op=tmp" http://ip:8030/api/test/pageview/_stream_load

Error: Value count does not match column count. Expect 5, but got 4. Row: 68e31c26-7df2-4522-a5e3-0c968d5f406b,2022-02-24,www.ff.com,19188,0

将表增加一列：
create table test.pageview(
uid varchar(64) not null,
visit_date date not null,
url varchar(64),
duration int,
op_type int
)PRIMARY KEY (uid,visit_date)
PARTITION BY RANGE(visit_date)(
PARTITION p20220201 VALUES [(‘2022-02-01’), (‘2022-02-02’)),
PARTITION p20220202 VALUES [(‘2022-02-02’), (‘2022-02-03’)),
PARTITION p20220203 VALUES [(‘2022-02-03’), (‘2022-02-04’)),
PARTITION p20220204 VALUES [(‘2022-02-04’), (‘2022-02-05’)),
PARTITION p20220205 VALUES [(‘2022-02-05’), (‘2022-02-06’)),
PARTITION p20220206 VALUES [(‘2022-02-06’), (‘2022-02-07’)),
PARTITION p20220207 VALUES [(‘2022-02-07’), (‘2022-02-08’)),
PARTITION p20220208 VALUES [(‘2022-02-08’), (‘2022-02-09’)),
PARTITION p20220209 VALUES [(‘2022-02-09’), (‘2022-02-10’)),
PARTITION p20220210 VALUES [(‘2022-02-10’), (‘2022-02-11’)),
PARTITION p20220211 VALUES [(‘2022-02-11’), (‘2022-02-12’)),
PARTITION p20220212 VALUES [(‘2022-02-12’), (‘2022-02-13’)),
PARTITION p20220213 VALUES [(‘2022-02-13’), (‘2022-02-14’)),
PARTITION p20220214 VALUES [(‘2022-02-14’), (‘2022-02-15’)),
PARTITION p20220215 VALUES [(‘2022-02-15’), (‘2022-02-16’)),
PARTITION p20220216 VALUES [(‘2022-02-16’), (‘2022-02-17’)),
PARTITION p20220217 VALUES [(‘2022-02-17’), (‘2022-02-18’)),
PARTITION p20220218 VALUES [(‘2022-02-18’), (‘2022-02-19’)),
PARTITION p20220219 VALUES [(‘2022-02-19’), (‘2022-02-20’)),
PARTITION p20220220 VALUES [(‘2022-02-20’), (‘2022-02-21’)),
PARTITION p20220221 VALUES [(‘2022-02-21’), (‘2022-02-22’)),
PARTITION p20220222 VALUES [(‘2022-02-22’), (‘2022-02-23’)),
PARTITION p20220223 VALUES [(‘2022-02-23’), (‘2022-02-24’)),
PARTITION p20220224 VALUES [(‘2022-02-24’), (‘2022-02-25’)),
PARTITION p20220225 VALUES [(‘2022-02-25’), (‘2022-02-26’)),
PARTITION p20220226 VALUES [(‘2022-02-26’), (‘2022-02-27’)),
PARTITION p20220227 VALUES [(‘2022-02-27’), (‘2022-02-28’)),
PARTITION p20220228 VALUES [(‘2022-02-28’), (‘2022-03-01’))
)
DISTRIBUTED BY HASH(uid) BUCKETS 4;

导入数据可以成功：
curl --location-trusted -u root:X123 -T /opt/test_data.log -H "label:a123a" -H "column_separator:," -H "cloumns:uid,visit_date,url,duration,op_type" http://ip:8030/api/test/pageview/_stream_load

但是删除数据失败：
测试数据：
3ffb5441-e7f8-476a-8dc7-ecf0e90ac777,2022-02-25,www.qq.com,23500,0
d80d7f3d-c8fe-496e-922c-32aa7d49f071,2022-02-12,www.163.com,135191,0
8bd31306-322f-4c24-9b97-fd324496b18c,2022-02-11,www.qq.com,246363,0
444631fb-46d7-4154-a9f2-8f0d1f467fb2,2022-02-05,www.163.com,102227,0
cd42f612-476e-4800-84c7-1af162b7a759,2022-02-04,www.ff.com.cn,35030,1
7f23f42d-453c-4ad0-84f0-bc4556cdd01a,2022-02-18,www.fe.com,10000,0
50654f54-f848-46de-b255-d6cc08141c36,2022-02-01,www.ff.com.cn,155038,0
d77569be-bbf7-4f98-b8e5-86787ad08bf4,2022-02-11,www.163.com,157983,0
0d3dd19f-6edd-4781-8a18-b3757f0d1fb4,2022-02-14,www.ff.com.cn,37941,1
f34ba686-e33a-4728-909f-5a10b178979e,2022-02-04,www.163.com,74563,0
10185be8-0fe2-4cde-9b95-a98084058015,2022-02-13,www.163.com,17986,0
68e31c26-7df2-4522-a5e3-0c968d5f406b,2022-02-24,www.ff.com,19188,0
b902f04b-2202-46cd-9200-c00cde7b79cd,2022-02-20,www.163.com,199253,1

curl --location-trusted -u root:X123 -T /opt/test_data.log -H "label:a123a" -H "column_separator:," -H "cloumns:uid,visit_date,url,duration,op_type,__op=op_type" http://ip:8030/api/test/pageview/_stream_load

指定__op无效，无法删除，整个操作都是upsert。

PS:

表中有3列"c1, c2, c3" ，源文件中前3列一一对应，但是还有多余1列；那么需要指定-H “columns: c1, c2, c3, temp”，最后1列随意指定名称用于占位即可。

这个点用1.19.5 也测试也是出现上面的相同错误，是哪里配置有问题？还是我的命令写的有问题？

#2

你好，最后一列标识为upsert或者delete的话，这样配置就行
curl --location-trusted -u root:Xy123 -T /opt/test_data.log -H “label:a123a” -H “column_separator:,” -H “cloumns:uid,visit_date,url,duration**,__op**” http://ip:8030/api/test/pageview/_stream_load

#3

curl --location-trusted -u root:X123 -T /opt/test_data.log -H “label:a123g12” -H “column_separator:,” -H “cloumns:uid,visit_date,url,duration**,__op**” http://ip:8030/api/test/pageview/_stream_load

还是报一样的错误：

Error: Value count does not match column count. Expect 5, but got 4

#4

是不建表中加了这个字段？把这个字段删了

#5

测试的时候已经把这个字段删了

#6

https://github.com/StarRocks/starrocks/issues/4337 这个问题是另外一种简单重现

#7

占位符应该是temp不是tmp哈

#8

用temp也是一样的结果 :sob:

#9

确认了下，columns打错了导致的，这块我们看下怎么处理下报错的问题

image
image771×96 11.1 KB

#10

还真是，问题完美解决，谢谢~

京ICP备2022026421号-1