CSV import via COPY SQL

The COPY SQL command is the preferred way to import large CSV files into partitioned tables. It should be used to migrate data from another database into QuestDB. This guide describes the method of migrating data to QuestDB via CSV files. For the time being this is the only way to migrate data from other databases into QuestDB.

note

This guide is applicable for QuestDB version 6.5 and higher.

Prepare the import

Preparation is key. Import is a multi-step process, which consists of:

• Export the existing database as CSV files
• Enable and configure COPY command to be optimal for the system
• Prepare target schema in QuestDB

Export the existing database

Export data using one CSV file per table. Make sure to export a column, which can be used as timestamp. Data in CSV is not expected to be in any particular order. If it is not possible to export table as one CSV, export multiple files and concatenate these files before importing into QuestDB.

Concatenate multiple CSV files

The way to concatenate files depends on whether the CSV files have headers.

For CSV files without headers, concatenation is straightforward:

Linux

ls *.csv | xargs cat > singleFile.csv

macOS

ls *.csv | xargs cat > singleFile.csv

Windows PowerShell

$TextFiles = Get-Item C:\Users\path\to\csv\*.csv

# The files are moved to the same folder.

$TextFiles foreach { Add-Content -Value $(Get-Content $_) -Path C:\Users\path\to\csv\singleFile.csv}

For CSV files with headers, concatenation can be tricky. You could manually remove the first line of the files before concatenating, or use some smart command line to concatenate and remove the headers. A good alternative is using the open source tool csvstack.

This is how you can concatenate multiple CSV files using csvstack:

csvstack *.csv > singleFile.csv

Things to know about COPY

• COPY is disabled by default, as a security precaution. Configuration is required.

• COPY is more efficient when source and target disks are different.

• COPY is parallel when target table is partitioned.

• COPY is serial when target table is non-partitioned, out-of-order timestamps will be rejected

• COPY cannot import data into non-empty table.

• COPY indexes CSV file; reading indexed CSV file benefits hugely from disk IOPS. We recommend using NVME.

• COPY imports one file at a time; there is no internal queuing system yet.

• COPY reference

Configure COPY

• Enable COPY and configure COPY directories to suit your server.
• cairo.sql.copy.root must be set for COPY to work.

Create the target table schema

If you know the target table schema already, you can skip this section.

QuestDB could analyze the input file and "guess" the schema. This logic is activated when target table does not exist.

To have QuestDB help with determining file schema, it is best to work with a sub-set of CSV. A smaller file allows us to iterate faster if iteration is required.

Let's assume we have the following CSV:

"locationId","timestamp","windDir","windSpeed","windGust","cloudCeiling","skyCover","visMiles","tempF","dewpF","rain1H","rain6H","rain24H","snowDepth"

1,"2010-07-05T00:23:58.981263Z",3050,442,512,,"OBS",11.774906006761,-5,-31,58.228032196984,70.471606345673,77.938252342637,58

2,"2017-10-10T10:13:55.246046Z",900,63,428,5487,"BKN",4.958601701089,-19,-7,4.328016420894,36.020659549374,97.821114441800,41

3,"2010-03-12T11:17:13.727137Z",2880,299,889,371,"BKN",10.342717709226,46,81,9.149518425127,20.229637391479,20.074738007931,80

4,"2018-08-21T15:42:23.107543Z",930,457,695,4540,"OBS",13.359184086767,90,-47,33.346163208862,37.501996055160,58.316836760009,13

...

1. Extract the first 1000 line to test_file.csv (assuming both files are in the cairo.sql.copy.root directory):

   head -1000 weather.csv > test_file.csv

2. Use a simple COPY command to import test_file.csv and define the table name:

   COPY weather from 'test_file.csv' WITH HEADER true;

Table weather is created and it quickly returns an id of asynchronous import process running in the background:

id
5179978a6d7a1772

3. In the Web Console right click table and select Copy Schema to Clipboard - this copies the schema generated by the input file analysis.

4. Paste the table schema to the code editor:

   CREATE TABLE 'weather' (

   timestamp TIMESTAMP,

   windDir INT,

   windSpeed INT,

   windGust INT,

   cloudCeiling INT,

   skyCover STRING,

   visMiles DOUBLE,

   tempF INT,

   dewpF INT,

   rain1H DOUBLE,

   rain6H DOUBLE,

   rain24H DOUBLE,

   snowDepth INT

   );

5. Identify the correct schema:

   5.1. The generated schema may not be completely correct. Check the log table and log file to resolve common errors using the id (see also Track import progress and FAQ):

   SELECT * FROM sys.text_import_log WHERE id = '5179978a6d7a1772' ORDER BY ts DESC;

ts	id	table	file	phase	status	message	rows_handled	rows_imported	errors
2022-08-08T16:38:06.262706Z	5179978a6d7a1772	weather	test_file.csvtest_file.csv		finished		999	999	0
2022-08-08T16:38:06.226162Z	5179978a6d7a1772	weather	test_file.csvtest_file.csv		started				0

Check rows_handled, rows_imported, and message for any errors and amend the schema as required.

5.2. Drop the table and re-import test_file.csv using the updated schema.

6. Repeat the steps to narrow down to a correct schema.

   The process may require either truncating:

   TRUNCATE TABLE table_name;

   or dropping the target table:

   DROP TABLE table_name;

7. Clean up: Once all the errors are resolved, copy the final schema, drop the small table.

8. Make sure table is correctly partitioned. The final schema in our example should look like this:

   CREATE TABLE 'weather' (

   timestamp TIMESTAMP,

   windDir INT,

   windSpeed INT,

   windGust INT,

   cloudCeiling INT,

   skyCover STRING,

   visMiles DOUBLE,

   tempF INT,

   dewpF INT,

   rain1H DOUBLE,

   rain6H DOUBLE,

   rain24H DOUBLE,

   snowDepth INT

   ) TIMESTAMP (timestamp) partitioned by DAY;

9. Ready for import: Create an empty table using the final schema.

Import CSV

Once an empty table is created in QuestDB using the correct schema, import can be initiated with:

COPY weather FROM 'weather.csv' WITH HEADER true TIMESTAMP 'timestamp' FORMAT 'yyyy-MM-ddTHH:mm:ss.SSSUUUZ';

It quickly returns id of asynchronous import process running in the background:

id
55020329020b446a

Track import progress

COPY returns an id for querying the log table (sys.text_import_log), to monitor progress of ongoing import:

SELECT * FROM sys.text_import_log WHERE id = '55020329020b446a';

ts	id	table	file	phase	status	message	rows_handled	rows_imported	errors
2022-08-03T14:00:40.907224Z	55020329020b446a	weather	weather.csv	null	started	null	null	null	0
2022-08-03T14:00:40.910709Z	55020329020b446a	weather	weather.csv	analyze_file_structure	started	null	null	null	0
2022-08-03T14:00:42.370563Z	55020329020b446a	weather	weather.csv	analyze_file_structure	finished	null	null	null	0
2022-08-03T14:00:42.370793Z	55020329020b446a	weather	weather.csv	boundary_check	started	null	null	null	0

Looking at the log from the newest to the oldest might be more convenient:

SELECT * FROM sys.text_import_log WHERE id = '55020329020b446a' ORDER BY ts DESC;

Once import successfully ends the log table should contain row with 'null' phase and 'finished' status :

ts	id	table	file	phase	status	message	rows_handled	rows_imported	errors
2022-08-03T14:10:59.198672Z	55020329020b446a	weather	weather.csv	null	finished		300000000	300000000	0

Import into non-partitioned tables uses single threaded implementation that reports only start and finish records in status table. Given an un-ordered CSV file weather1mil.csv, when importing, the log table shows:

ts	id	table	file	phase	status	message	rows_handled	rows_imported	errors
2022-08-03T15:00:40.907224Z	42d31603842f771a	weather_unordered	weather1mil.csv	null	started	null	null	null	0
2022-08-03T15:01:20.000709Z	42d31603842f771a	weather_unordered	weather1mil.csv	null	finished	null	999999	999999	0

The log table contains only coarse-grained, top-level data. Import phase run times vary a lot (e.g. partition_import often takes 80% of the whole import execution time), and therefore [the server log]((/docs/reference/configuration#logging) provides an alternative to follow more details of import:

import log

2022-08-03T14:00:40.907224Z I i.q.c.t.ParallelCsvFileImporter started [importId=5502031634e923b2, phase=analyze_file_structure, file=`C:\dev\tmp\weather.csv`, workerCount=10]

2022-08-03T14:00:40.917224Z I i.q.c.p.WriterPool >> [table=`weather`, thread=43]

2022-08-03T14:00:41.440049Z I i.q.c.t.ParallelCsvFileImporter finished [importId=5502031634e923b2, phase=analyze_file_structure, file=`C:\dev\tmp\weather.csv`, duration=0s, errors=0]

2022-08-03T14:00:41.440196Z I i.q.c.t.ParallelCsvFileImporter started [importId=5502031634e923b2, phase=boundary_check, file=`C:\dev\tmp\weather.csv`, workerCount=10]

2022-08-03T14:01:18.853212Z I i.q.c.t.ParallelCsvFileImporter finished [importId=5502031634e923b2, phase=boundary_check, file=`C:\dev\tmp\weather.csv`, duration=6s, errors=0]

2022-08-03T14:01:18.853303Z I i.q.c.t.ParallelCsvFileImporter started [importId=5502031634e923b2, phase=indexing, file=`C:\dev\tmp\weather.csv`, workerCount=10]

2022-08-03T14:01:18.853516Z I i.q.c.t.ParallelCsvFileImporter temporary import directory [path='E:\dev\tmp\weather\]

2022-08-03T14:01:42.612302Z I i.q.c.t.CsvFileIndexer finished chunk [chunkLo=23099021813, chunkHi=26948858785, lines=29999792, errors=0]

2022-08-03T14:01:42.791789Z I i.q.c.t.CsvFileIndexer finished chunk [chunkLo=11549510915, chunkHi=15399347885, lines=30000011, errors=0]

If the ON ERROR option is set to ABORT, import stops on first error and the error is logged. Otherwise, all errors are listed in the log.

The reference to the error varies depending on the phase of an import:

• In the indexing phase, if an error occurs, the absolute input file line is referenced:

2022-08-08T11:50:24.319675Z E i.q.c.t.CsvFileIndexer could not parse timestamp [line=999986, column=1]

• In the data import phase, if an error occurs, the log references the offset as related to the start of the file.

2022-08-08T12:19:56.828792Z E i.q.c.t.TextImportTask type syntax [type=INT, offset=5823, column=0, value='CMP2']

The errored rows can then be extracted for further investigation.

FAQ

What happens in a database crash or OS reboot?

If reboot/power loss happens while partitions are being attached, then table might be left with incomplete data. Please truncate table before re-importing with:

TRUNCATE TABLE table_name;

If reboot/power loss happens prior to any partitions are attached, the import should not be effected.

I'm getting "COPY is disabled ['cairo.sql.copy.root' is not set?]" error message

Please set cairo.sql.copy.root setting, restart instance and try again.

I'm getting "could not create temporary import directory [path='somepath', errno=-1]" error message

Please make sure that both cairo.sql.copy.root and cairo.sql.copy.work.root are valid paths pointing to existing directories.

I'm getting "[2] could not open read-only [file=somepath]" error message

Please check that import file path is valid and accessible to QuestDB instance users.

If you are running QuestDB using Docker, please check if the directory mounted for storing source CSV files is identical to the one cairo.sql.copy.root property or QDB_CAIRO_SQL_COPY_ROOT environment variable points to.

For example, the following command can start a QuestDB instance:

docker run -p 9000:9000 \

-v "/tmp/questdb:/var/lib/questdb" \

-v "/tmp/questdb/my_input_root:/tmp/questdb_import" \

-e QDB_CAIRO_SQL_COPY_ROOT=/tmp/questdb_wrong \

questdb/questdb

However, running:

COPY weather from 'weather_example.csv' WITH HEADER true;

Results in the "[2] could not open read-only [file=/tmp/questdb_wrong/weather_example.csv]" error message.

I'm getting "column count mismatch [textColumnCount=4, tableColumnCount=3, table=someTable]" error message

There are more columns in input file than in the existing target table. Please remove column(s) from input file or add them to the target table schema.

I'm getting "timestamp column 'ts2' not found in file header" error message

Either input file is missing header or timestamp column name given in COPY command is invalid. Please add file header or fix timestamp option.

I'm getting "column is not a timestamp [no=0, name='ts']" error message

Timestamp column given by the user or (if header is missing) assumed based on target table schema is of a different type.
Please check timestamp column name in input file header or make sure input file column order matches that of target table.

I'm getting "target table must be empty [table=t]" error message

COPY doesn't yet support importing into partitioned table with existing data.

Please truncate table before re-importing with:

TRUNCATE TABLE table_name;

or import into another empty table and then use INSERT INTO SELECT:

INSERT batch 100000 commitLag 180s

INTO table_name

SELECT * FROM other_table;

to copy data into original target table.

I'm getting "io_uring error" error message

It's possible that you've hit a IO_URING-related kernel error.
Please set cairo.iouring.enabled setting to false, restart QuestDB instance, and try again.

I'm getting "name is reserved" error message

The table you're trying import into is in bad state (metadata is incomplete).

Please either drop the table with:

DROP TABLE table_name;

and recreate the table or change the table name in the COPY command.

I'm getting "Unable to process the import request. Another import request may be in progress." error message

Only one import can be running at a time.

Either cancel running import with:

COPY 'paste_import_id_here' CANCEL;

or wait until the current import is finished.

Import finished but table is (almost) empty

Please check the latest entries in log table:

SELECT * FROM sys.text_import_log LIMIT -10;

If "errors" column is close to number of records in the input file then it may mean:

• FORMAT option of COPY command or auto-detected format doesn't match timestamp column data in file
• Other column(s) can't be parsed and ON ERROR SKIP_ROW option was used
• Input file is unordered and target table has designated timestamp but is not partitioned

If none of the above causes the error, please check the log file for messages like:

2022-08-08T11:50:24.319675Z E i.q.c.t.CsvFileIndexer could not parse timestamp [line=999986, column=1]

or

2022-08-08T12:19:56.828792Z E i.q.c.t.TextImportTask type syntax [type=INT, offset=5823, column=0, value='CMP2']

that should explain why rows were rejected. Note that in these examples, the former log message mentions the absolute input file line while the latter is referencing the offset as related to the start of the file.

Import finished but table column names are `f0`, `f1`, ...

Input file misses header and target table does not exist, so columns received synthetic names . You can rename them with the ALTER TABLE command:

ALTER TABLE table_name RENAME COLUMN f0 TO ts;