IMPORT INTO: Support incremental ingestion into non-empty tables via on_duplicate_key='replace'
- 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
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