pingcap / pingcap/tidb

IMPORT INTO: Support incremental ingestion into non-empty tables via on_duplicate_key='replace'

Open
#69,471 1 comment 0 reactions 0 assignees View on GitHub
component/import contribution first-time-contributor type/feature-request
Dominant language
Go
Stars
40.5k
Forks
6.2k
PR merge metrics
PR metrics pending

Description

## Feature Request

**Is your feature request related to a problem? Please describe:**
`IMPORT INTO` currently enforces a hard constraint that the target table must be empty (`PreCheck failed: target table is not empty`). This prevents two critical incremental ingestion use cases:

1. **Importing into an empty partition of a non-empty table** — The table has data in other partitions, but the target partition is empty. Stock `IMPORT INTO` rejects the entire operation because the table-level check fails.

2. **Importing into a non-empty partition (overlapping data)** — The target partition already has rows, and the incoming CSV contains updated versions of some existing records (same PK, different non-PK column values). Stock `IMPORT INTO` has no mechanism to handle this.

Both scenarios are common in production incremental ETL pipelines where:
- Data arrives in periodic batches (daily/hourly feeds)
- Some records are updates to previously loaded data
- Separate partitions are loaded independently by different jobs
- Re-processing a batch (retry/backfill) must not require manual table truncation

The only current workaround is `LOAD DATA` or row-by-row `INSERT ... ON DUPLICATE KEY UPDATE`, which are 10-50x slower than SST-based physical ingestion.

**Describe the feature you'd like:**
Add an `on_duplicate_key='replace'` option to `IMPORT INTO` that:

1. **Skips the table-empty precheck** — allows ingestion into non-empty tables/partitions
2. **Detects within-batch duplicates** — using `DupDetectKeyAdapter` + `DupDetector` during sorted iteration in the local pebble engine (bulk O(n) streaming check, not per-record lookup)
3. **Resolves cross-batch duplicates via MVCC shadowing** — new SST data is ingested at a later commit timestamp, automatically superseding pre-existing rows with the same PK (no explicit delete/update needed)
4. **Tolerates `ErrFoundDuplicateKeys`** — instead of failing the import when duplicates are found, logs and continues

### Proposed Syntax

```sql
-- Import into a non-empty table; overlapping PKs are replaced with new data
IMPORT INTO my_table FROM '/path/to/data.csv' WITH on_duplicate_key='replace';

-- Works with partitioned tables (routes to correct partitions automatically)
IMPORT INTO partitioned_table FROM 's3://bucket/daily_feed/*.csv' WITH on_duplicate_key='replace';
```

**Describe alternatives you've considered:**

### Alternative A: Partition-Level Empty Check Only
Instead of table-level empty check, check only the target partition. This allows importing into empty partitions of non-empty tables but does NOT handle overlapping data:

```sql
-- Works if target partition is empty (even though table has data elsewhere)
IMPORT INTO t FROM '/data.csv'; -- with partition-scoped precheck
```

- ✅ Simpler implementation (just change precheck scope)
- ❌ Cannot handle updates/overlapping PKs
- ❌ Fails on re-processing scenarios where partition already has data

### Alternative B: `LOAD DATA` / `INSERT ... ON DUPLICATE KEY UPDATE`

- ✅ Already supported in TiDB
- ❌ 10-50x slower than SST ingest (row-by-row KV writes via Raft)
- ❌ Not viable for large-scale incremental loads (millions of rows per batch)

### Alternative C: Manual Truncate + Re-Import

```sql
TRUNCATE TABLE t PARTITION p0;
IMPORT INTO t FROM '/data.csv';
```

- ✅ Works with stock IMPORT INTO
- ❌ Data unavailable during truncate+reimport window
- ❌ Cannot preserve non-overlapping rows in the partition
- ❌ Requires coordination if multiple jobs write to the same partition

**Teachability, Documentation, Adoption, Migration Strategy:**
### User-Facing Documentation

The feature requires a single new option in the existing `IMPORT INTO` syntax:

```sql
IMPORT INTO table_name
FROM 'file_path'
WITH on_duplicate_key='replace';
```

Users familiar with MySQL's `LOAD DATA ... REPLACE` or `INSERT ... ON DUPLICATE KEY UPDATE` will find the semantics intuitive:
- Same PK in CSV and table → new data wins (replace)
- Different PK → normal insert
- No additional configuration needed

### Backward Compatibility

- Without `on_duplicate_key='replace'`, behavior is 100% identical to current IMPORT INTO
- No schema changes required; works with existing tables and partitioning schemes
- No cluster configuration changes; uses existing MVCC infrastructure and GC settings

Contributor guide

Open the contributing guide

Research direction

Start at the IMPORT INTO precheck and duplicate-handling paths, then read DupDetectKeyAdapter, DupDetector, the local pebble engine, and ErrFoundDuplicateKeys behavior. Done means the new option supports the described non-empty and partitioned imports with replace semantics, while existing behavior remains unchanged without it; validate the listed incremental and duplicate scenarios.

Written by the indexing model from the issue text.

Assessment

Tech stack
go, sql
Domain
databases
Issue type
Feature
Difficulty
5/5
Estimated time
Over a week
Activity status
Quiet
Clarity
Mostly clear
Newbie friendliness
32/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.