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.
This guide is applicable for QuestDB version 6.5 and higher.
Preparation is key. Import is a multi-step process, which consists of:
- Export the existing database as CSV files
- Enable and configure
COPYcommand to be optimal for the system
- Prepare target schema in QuestDB
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.
The way to concatenate files depends on whether the CSV files have headers.
For CSV files without headers, concatenation is straightforward:
- Windows PowerShell
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:
Things to know about
COPYis disabled by default, as a security precaution. Configuration is required.
COPYis more efficient when source and target disks are different.
COPYis parallel when target table is partitioned.
COPYis serial when target table is non-partitioned, out-of-order timestamps will be rejected
COPYcannot import data into non-empty table.
COPYindexes CSV file; reading indexed CSV file benefits hugely from disk IOPS. We recommend using NVME.
COPYimports one file at a time; there is no internal queuing system yet.
COPYdirectories to suit your server.
cairo.sql.copy.rootmust be set for
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:
Extract the first 1000 line to
test_file.csv(assuming both files are in the
cairo.sql.copy.rootdirectory):head -1000 weather.csv > test_file.csv
Use a simple
COPYcommand to import
test_file.csvand define the table name:COPY weather from 'test_file.csv' WITH HEADER true;
weather is created and it quickly returns an id of asynchronous import process running in the background:
In the Web Console right click table and select
Copy Schema to Clipboard- this copies the schema generated by the input file analysis.
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);
Identify the correct schema:SELECT * FROM sys.text_import_log WHERE id = '5179978a6d7a1772' ORDER BY ts DESC;
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.
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;
Clean up: Once all the errors are resolved, copy the final schema, drop the small table.
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;
Ready for import: Create an empty table using the final schema.
Once an empty table is created in QuestDB using the correct schema, import can be initiated with:
It quickly returns id of asynchronous import process running in the background:
COPY returns an id for querying the log table (
sys.text_import_log), to monitor progress of ongoing import:
Looking at the log from the newest to the oldest might be more convenient:
Once import successfully ends the log table should contain row with 'null' phase and 'finished' status :
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:
The log table contains only coarse-grained, top-level data. Import phase run times vary a lot (e.g.
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:
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:
- In the data import phase, if an error occurs, the log references the offset as related to the start of the file.
The errored rows can then be extracted for further investigation.
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:
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
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.work.root are valid paths pointing to existing directories.
I'm getting " 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:
Results in the " 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
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:
or import into another empty table and then use
INSERT INTO SELECT:
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.
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:
and recreate the table or change the table name in the
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:
or wait until the current import is finished.
Import finished but table is (almost) empty
Please check the latest entries in log table:
If "errors" column is close to number of records in the input file then it may mean:
COPYcommand or auto-detected format doesn't match timestamp column data in file
- Other column(s) can't be parsed and
ON ERROR SKIP_ROWoption 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:
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
ALTER TABLE command: