There are three methods for CSV Import:
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.
For partitioned tables, the best
COPY performance can be achieved only on a
machine with a local, physically attached SSD. It is possible to use a network
block storage, such as an AWS EBS volume to perform the operation, with the
- Users need to configure the maximum IOPS and throughput setting values for the volume.
- The required import time is likely to be 5-10x longer.
Import via COPY SQL is not available within QuestDB Cloud.
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 the 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.
COPYand configure the
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
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) partition 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 (
monitor the 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 a row with a 'null' phase and 'finished' status :
Import into non-partitioned tables uses single-threaded implementation (serial
import) that reports only start and finish records in the status table. Given an
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.
partition_import often takes 80% of the whole import
execution time), and therefore
the server log provides an
alternative to follow more details of import:
ON ERROR option is set to
import stops on the 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.
COPY on a table with symbol columns is very slow. How can I speed it up?
256 as the default symbol capacity. If the number of distinct
symbol values exceeds this default significantly, the
COPY performance will
suffer. Make sure that you specify symbol capacities when creating the table
before running the
Here is an example:
Refer to the symbol type documentation for more information on configuring the symbol capacity.
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 before any partitions being attached, the import should not be affected.
I'm getting "COPY is disabled ['cairo.sql.copy.root' is not set?]" error message
cairo.sql.copy.root setting, restart the instance and try again.
I'm getting "could not create temporary import work directory [path='somepath', errno=-1]" error message
Please make sure that the
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
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
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:
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 with the
ALTER TABLE command:
The REST API provides an
/imp endpoint exposed on port
9000 by default. This
endpoint allows streaming tabular text data directly into a table, supporting
CSV, TAB and pipe (
|) delimited inputs with optional headers. Data types and
structures are detected automatically, but additional configurations can be
provided to improve automatic detection.
The REST API is better suited when the following conditions are true:
- Regular uploads of small batches of data into the same table.
- The file batches do not contain overlapping periods (they contain distinct days/weeks/months). Otherwise, the import performance will be impacted.
It is possible to upload compressed files directly without decompression:
data=@- value instructs
curl to read the file contents from
schema JSON object can be provided with POST requests to
creating tables via CSV import. This allows for more control over user-defined
patterns for timestamps, or for explicitly setting types during column-creation.
The following example demonstrates basic usage, in this case, that the
ticker_name column should be parsed as
SYMBOL type instead of
If a timestamp column (
ts) in this CSV file has a custom or non-standard
timestamp format, this may be included with the call as follows:
For nanosecond-precision timestamps such as
2021-06-22T12:08:41.077338934Z, a pattern can be provided in the following
More information on the patterns for timestamps can be found on the date and time functions page.
schema object must precede the
data object in calls to this REST
endpoint. For example:
QuestDB uses a
text_loader.json configuration file which can be placed in the
conf directory. This file does not exist by default, but has the
following implicit settings:
Given a CSV file which contains timestamps in the format
yyyy-MM-dd - HH:mm:ss.SSSUUU, the following text loader configuration will
provide the correct timestamp parsing:
The CSV data can then be loaded via POST request, for example, using cURL:
For more information on the
/imp entry point, refer to the
REST API documentation.