Feature: support deduplication on stage attachment api

[ZhiHanZ]

Summary
To ensure data ingestion idempotency, databend has already support to deduplicate DML through deduplication label
https://databend.rs/doc/sql-commands/setting-cmds/set-var

Here for cross-language driver integration, we could add a rest api field for the label

[akoshchiy]

@ZhiHanZ Hi! Can I try to fix this? As I understood, we should extend the HttpQueryRequest with a new field and then pass it to the QueryContext settings on the http query creation.

[ZhiHanZ]

That is perfect, I think we do not need to add additional field on the HttpRequest, we could bring QueryID Header

databend/src/query/service/src/servers/http/v1/http_query_handlers.rs

Line 41 in 7dd2a99

const HEADER_QUERY_ID: &str = "X-DATABEND-QUERY-ID";

for deduplication, which is mentioned on previous issue:
#11591.

Expected Behavior:

CREATE TABLE sample
(
    Id      INT,
    City    VARCHAR,
    Score   INT,
);

sample.csv

1,'Los Angeles',100
2,'Irvine',80
3,'San Diego',60
4,'Palo alto',70
5,'San Jose',55
6,'Milipitas',99

curl -s -u root: -XPOST "http://localhost:${QUERY_HTTP_HANDLER_PORT}/v1/query" --header 'Content-Type: application/json'  --header 'X-DATABEND-QUERY-ID:  insert1' -d '{"sql": "insert into sample (Id, City, Score) values (?,?,?)", "stage_attachment": {"location": "@s1/sample.csv", "copy_options": {"purge": "true"}}}' | jq -r '.stats.scan_progress.bytes, .error'

1,'Los Angeles',100
2,'Irvine',80
3,'San Diego',60
4,'Palo alto',70
5,'San Jose',55
6,'Milipitas',99

curl -s -u root: -XPOST "http://localhost:${QUERY_HTTP_HANDLER_PORT}/v1/query" --header 'Content-Type: application/json'  --header 'X-DATABEND-QUERY-ID:  insert1' -d '{"sql": "insert into sample (Id, City, Score) values (?,?,?)", "stage_attachment": {"location": "@s1/sample.csv", "copy_options": {"purge": "true"}}}' | jq -r '.stats.scan_progress.bytes, .error'

No more inserted rows because of deduplication based on query id insert1
1,'Los Angeles',100
2,'Irvine',80
3,'San Diego',60
4,'Palo alto',70
5,'San Jose',55
6,'Milipitas',99

[akoshchiy]

Does it mean, that we also should use provided X-DATABEND-QUERY-ID as query_id instead of generating it?

[ZhiHanZ]

Does it mean, that we also should use provided X-DATABEND-QUERY-ID as query_id instead of generating it?

exactly