QuestDB uses a text_loader.json configuration file which can be placed in the server’s 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:

{
  "date": [
    {
      "format": "dd/MM/y"
    },
    {
      "format": "yyyy-MM-dd HH:mm:ss"
    },
    {
      "format": "yyyy-MM-ddTHH:mm:ss.SSSz",
      "locale": "en-US",
      "utf8": false
    {
    }
  ],
  "timestamp": [
    {
      "format": "yyyy-MM-ddTHH:mm:ss.SSSUUUz",
      "utf8": false
    },
    {
      "format": "yyyy-MM-dd - HH:mm:ss.SSSUUU",
      "utf8": false
    }
  ]
}

The CSV data can then be loaded via POST request, for example, using cURL:

Large datasets with out-of-order data

Using the lag and batch size parameters during INSERT AS SELECT statements is a convenient strategy to load and order large datasets from CSV in bulk when they contain out-of-order data.

The batch size specifies how many records to attempt to bulk insert at one time and the lag allows for specifying the expected lateness of out-of-order timestamp values (in microseconds):

SELECT * FROM unordered_table

Example

Given a large dataset with out-of-order records, an ordered table may be created using the following steps:

2. Create a table with the schema of the imported data and apply a partitioning strategy. Records are not yet inserted due to the use of WHERE 1 != 1. The timestamp column may be cast as a timestamp if the import did not automatically detect the correct format:

   CREATE TABLE weather AS (
   SELECT
     cast(timestamp AS timestamp) timestamp,
     windDir,
     windSpeed,
     windGust,
     rain1H,
     rain6H,
     rain24H
   FROM 'weather-unordered.csv' WHERE 1 != 1
   ) timestamp(timestamp) PARTITION BY DAY;

3. Insert the unordered records into the partitioned table and provide a lag and batch size:

To confirm that the table is ordered, the isOrdered() function may be used: