Testing the Import of CSV Files Using Lightning

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: 使用lightning 导入csv文件的测试

| username: lmdb

[TiDB Usage Environment] Production Environment / Test / Poc
[TiDB Version] v6.6.0
[Reproduction Path] What operations were performed to cause the issue
[Encountered Issue: Phenomenon and Impact]
[Resource Configuration]
[Attachment: Screenshot/Log/Monitoring]

Tried multiple times, but failed at step 2 of the check. Could you please advise on the usage techniques for this?
Detailed information is attached below.

[tidb@tidb-mdb tidb-community-server-v6.6.0-linux-amd64]$
[tidb@tidb-mdb tidb-community-server-v6.6.0-linux-amd64]$ ./tidb-lightning -config …/tidb-lightning-csv1.toml
Verbose debug logs will be written to tidb-lightning-csv.log

±–±-----------------------------------------------------------------------------------------------------------------------------------±------------±-------+
| # | CHECK ITEM | TYPE | PASSED |
±–±-----------------------------------------------------------------------------------------------------------------------------------±------------±-------+
| 1 | Source csv files size is proper | performance | true |
±–±-----------------------------------------------------------------------------------------------------------------------------------±------------±-------+
| 2 | TiDB Lightning has failed last time. To prevent data loss, this run will stop now, policy.test failed in step(written), please | critical | false |
| | run command ./tidb-lightning-ctl --checkpoint-error-destroy=‘policy.test’ --config=…,You may also run ./tidb-lightning-ctl | | | | | --checkpoint-error-destroy=all --config=... to start from scratch,For details of this failure, read the log file from the PREVIO | | |
| | US run | | |
±–±-----------------------------------------------------------------------------------------------------------------------------------±------------±-------+
| 3 | the config [mydumper.csv.header] is set to false, and CSV header lines are really not detected in the data files | critical | true |
±–±-----------------------------------------------------------------------------------------------------------------------------------±------------±-------+
| 4 | Cluster version check passed | critical | true |
±–±-----------------------------------------------------------------------------------------------------------------------------------±------------±-------+
| 5 | Lightning has the correct storage permission | critical | true |
±–±-----------------------------------------------------------------------------------------------------------------------------------±------------±-------+

tidb lightning encountered error: [Lightning:PreCheck:ErrPreCheckFailed]tidb-lightning pre-check failed: TiDB Lightning has failed last time. To prevent data loss, this run will stop now, policy.test failed in step(written), please run command ./tidb-lightning-ctl --checkpoint-error-destroy=‘policy.test’ --config=…,You may also run ./tidb-lightning-ctl --checkpoint-error-destroy=all --config=... to start from scratch,For details of this failure, read the log file from the PREVIOUS run
[tidb@tidb-mdb tidb-community-server-v6.6.0-linux-amd64]$ cat …/tidb-lightning-csv1.toml
[lightning]
##log info
level = “info”
file = “tidb-lightning-csv.log”

##check tidb cluster requirements

check-requirements = true

##import mode ,such as physical import mode and logical import mode.

physical import mode : local

logical import mode : tidb

[tikv-importer]
#backend = “local”
backend = “tidb”

temporary directory for import key-value data, must be empty directory.

sorted-kv-dir = “/data/sorted-kv-dir”

#logical import mode operation

-replace

-ignore

-error

on-duplicate = “replace”

###source data directory
#[mydumper]

csv file

###source data directory
[mydumper]
data-source-dir = “/data/my_database/csv/”

[mydumper.csv]
separator = “\t”
delimiter = ‘’
header = false
not-null = false
null = ‘NULL’
backslash-escape = false

##ignore the default databases.
#filter = [‘.’, ‘!mysql.', '!sys.’, ‘!INFORMATION_SCHEMA.', '!PERFORMANCE_SCHEMA.’, ‘!METRICS_SCHEMA.', '!INSPECTION_SCHEMA.’]

##target cluster info
[tidb]
host = “192.168.2.151”
port = 4000
user = “root”
password = “root”
status-port = 10080
pd-addr = “192.168.2.151:2379”
log-level = “error”
[tidb@tidb-mdb tidb-community-server-v6.6.0-linux-amd64]$ cat /data/my_database/csv/policy.test.CSV
1,“East”,32
2,“South”,\N
3,“West”,10
4,“North”,39
[tidb@tidb-mdb tidb-community-server-v6.6.0-linux-amd64]$

| username: 海石花47 | Original post link

Looking at your CSV data format, shouldn’t the separator be a comma?

| username: 海石花47 | Original post link

null = ‘\N’ This should also be changed.

| username: 海石花47 | Original post link

It’s best to configure it as well, so that checkpointing and resuming can be enabled.

| username: lmdb | Original post link

Thank you for the guidance.

| username: 海石花47 | Original post link

It’s best to take a close look at the official documentation? Try making the changes I mentioned above and then test it again~ Generally, following the official documentation should not cause any issues.

| username: lmdb | Original post link

According to the toml file, Dropping table deleted 2 tables, and the import was successful again. The question is why drop the table?

| username: lmdb | Original post link

Is there a parameter setting similar to Oracle append? I haven’t found one yet.

| username: jansu-dev | Original post link

The question is why the table would be dropped.

Because some data has already been imported, but there was an error in the configuration in the toml file. Subsequent data imports reported errors, and it seems that the default setting for resuming from a checkpoint was enabled. The command prompt roughly means --checkpoint–error-destroy=all, which deletes some information from the checkpoints, and one of the steps includes the drop table operation. So, reconfigure the toml file and dropping the table should be okay.

| username: 海石花47 | Original post link

Checkpoint–error-destroy

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.