Tracking: Load very large dataset into databend

[BohuTANG]

Summary

Tasks:

• allow executing COPY INTO in a cluster #6395 @RinChanNOWWW
• Stream COPY from a directory #6338 @lichuang
• feat: add PURGE option to COPY command #7443 @sundy-li
• Support COPY INTO ... (SELECT fields FROM @stage) #7228

Flow:

1. kafka/others sink to s3
2. A cron copies all the files from s3 to databend.
3. databend check the file has loaded or not, if loaded just skip, if not, load it and write the meta to metasrv, after load successful purge the file in the s3.
4. The cron runs again.

[flaneur2020]

we can consider make the cron in an external tool to focus on the file discovery in s3 directory.

there're different ways on the file discovery task:

• LIST by incremental (using the start-from in s3 LIST API in the lexicographic order)
  • it's easy to config and no other dependency, but might produce some cost on polling LIST api and takes longer to discover the new files (in minutes level)
• use the s3 notification by SQS
  • it can discover the new files in a quicker manner, but have more complex configurations and require a seperate dependency on SQS

[xudong963]

FYI, I'm testing tpch performance on our cloud but block at copy data into table.

copy into lineitem from @tpch_data files=('tpch100_single/lineitem.tbl') file_format=(type='CSV' field_delimiter='|' record_delimiter='\n');

The specific situation is that lineitem.tbl contains 76G data and can't copy data into lineitem table.

The above SQL return success, but no data is inserted. (It's better to return error?)

cc @BohuTANG

I don't know why can't even one row of data be inserted.

I think We should make sure that the large data can be copied in first, even if it's slow.

[BohuTANG]

I think there are some errors but not return to the client.

You can try:
copy into lineitem from @tpch_data files=('tpch100_single/lineitem.tbl') file_format=(type='CSV' field_delimiter='|' record_delimiter='\n') force=true;

[BohuTANG]

All works great now.