import into from select

[D3Hunter]

Feature Request

Is your feature request related to a problem? Please describe:

“Insert into from select” statement do normal insert in one txn, the performance can not satisfy user expection(see #48637 too), so we add a similar IMPORT into from select which uses physical import same as lightning to speed up.
Describe the feature you'd like:

IMPORT into doesn't support transaction, and the target table must be a empty one. Selected rows will be encoded into kv, sorted and write to local disk, data will be ingest into tikv directly, just as what lightning physical mode does.

during prototype test, it will speed up 2~8 times compared to INSERT INTO depends on scenario and configured concurrency.

It also support import from snapshot, but only using as of timestamp 'xxx' inside the select-statement is supported, set tidb_snapshot is not.

the grammar:

IMPORT INTO table[column, column...] FROM select-statement [WITH thread=xx]

EXPLAIN import-into-statement

tasks:

• importinto: move post-process code from disttask pkg to importer pkg #49884
• local backend: abstract engine manager #49936
• importinto: import from select parser/planner part #49976
• importinto: executor part of import from select #50341
• importinto: add more test #50558
• importinto: support import from stale read #50852
• importinto: add disable_precheck option & change default threadcnt when import from select #51077

Describe alternatives you've considered:

Teachability, Documentation, Adoption, Migration Strategy:

[AndrewDi]

Could SELECT Statement support tidb_snapshot?

[D3Hunter]

Could SELECT Statement support tidb_snapshot?

not now, can you describe your usage scenario?

[AndrewDi]

Could SELECT Statement support tidb_snapshot?

not now, can you describe your usage scenario?

We need to replicate a table with specific timestamp to a new table

[D3Hunter]

Could SELECT Statement support tidb_snapshot?

not now, can you describe your usage scenario?

We need to replicate a table with specific timestamp to a new table

how frequent you do it?

and is it whole table replicate, i.e. select * from source_tbl?

what's the new table used for?

• as a backup? why not use backup&restore tool?
• as a persisted snapshot (avoid gc) that can be queried for analysis later?

[AndrewDi]

Could SELECT Statement support tidb_snapshot?

not now, can you describe your usage scenario?

We need to replicate a table with specific timestamp to a new table

how frequent you do it?

and is it whole table replicate, i.e. select * from source_tbl?

what's the new table used for?

• as a backup? why not use backup&restore tool?
• as a persisted snapshot (avoid gc) that can be queried for analysis later?

maybe everyday, we need to update this tables, so read only snapshot may not meet our requirement.

[D3Hunter]

Could SELECT Statement support tidb_snapshot?

you can try using nightly build, with import into dst from select * from src as of timestamp '2024-01-31 16:22:33.516399'; now

[D3Hunter]

released in 8.0