Pipeline using replace duplicate

Off-Topic
1

Pipeline with duplicate key.

I am trying to create pipeline with replace duplicate key but it it not allowing to do and throwing and error. I am using version 6.

Please suggest.

2

Could you please provide your table schema and pipeline definition?

3

Thanks for the reply, please find my statement.

create or replace pipeline testload03
As loaddata fs’/testpipe/test_extract_*.txt’
Batch interval 2500 max_partitions_per_batch 31
Enable out_of_order optimization
Replace duplicate key errors
Into table testgroup(attribute details)
Fields terminated by ‘,’ lines terminated by ‘\n’;

If I change ‘ Replace duplicate key errors’ to ‘ skip duplicate key errors’ it is working fine.

My interest is if duplicate it should replace.

Please suggest.

4

And can you paste the table schema?

5

I think the syntax you’re looking for is replace, rather than replace duplicate key errors.

MySQL [db]> create table t(a int, b int, primary key(a));
MySQL [db]> insert into t values (1, 0);
MySQL [db]> create pipeline p as load data fs "/tmp/1.csv" replace into table t fields terminated by ",";
MySQL [db]> start pipeline p foreground;
MySQL [db]> select * from t;
+---+------+
| a | b    |
+---+------+
| 1 |    4 |
+---+------+

 ~: cat /tmp/1.csv 
1, 1
1, 2
1, 3
1, 4
6

Thank you, it is working.

I wanted to load one field with null using pipeline, how can I load nulls using pipeline approach.

For your table can we have data in pipeline file as below.

9,NULL
8,NULL

I tried with NULL, it is loading as string.

7

As well, suppose I have the date field and I gave invalid date like 2020-20-90, instead of rejecting record, pipeline loading with 0001-01-01, please let me know how can the record be rejected with out loading with pipeline approach.

8

Any help please on above 2 posts.

9

This is a separate question not related to the thread title. Do you mind posting a separate question?