Insert data

This page shows how to insert data into QuestDB using different programming languages and tools.

InfluxDB Line Protocol is the recommended primary ingestion method in QuestDB and is recommended for high-performance applications.

For operational (ad-hoc) data ingestion the Web Console makes it easy to upload CSV files and insert via SQL statements. You can also perform these same actions via the HTTP REST API.

Applications that intend to insert via SQL programmatically should prefer the PostgreSQL wire protocol as it provides parameterized querys which avoid SQL injection issues.

In summary, these are the different options:

  • Web Console
    • CSV upload.
    • SQL INSERT statements.
  • InfluxDB Line Protocol
    • High performance.
    • Optional automatic timestamps.
    • Optional integrated authentication.
    • Client libraries in various programming languages.
  • PostgreSQL wire protocol
    • SQL INSERT statements, including parameterized queries.
    • Use psql on the command line.
    • Interoperability with third-party tools and libraries.
  • HTTP REST API
    • CSV upload.
    • SQL INSERT statements.
    • Use curl on the command line.

Web Console#

QuestDB ships with an embedded Web Console running by default on port 9000.

Creating a table and inserting some data
CREATE TABLE takeaway_order (ts TIMESTAMP, id SYMBOL, status SYMBOL)
TIMESTAMP(ts);
INSERT INTO takeaway_order VALUES (now(), 'order1', 'placed');
INSERT INTO takeaway_order VALUES (now(), 'order2', 'placed');

SQL statements can be written in the code editor and executed by clicking the Run button. Note that the web console runs a single statement at a time. You can also use the Web Console to upload CSV.

InfluxDB Line Protocol#

The InfluxDB Line Protocol (ILP) is a text protocol over TCP or UDP on port 9009.

It is a one-way protocol to insert data, focusing on simplicity and performance.

Here is a summary table is how it compares with ways to insert data that we support:

ProtocolRecord Insertion ReportingData Insertion Performance
InfluxDB Line ProtocolServer logs; Disconnect on errorBest
CSV upload via HTTP RESTConfigurableVery Good
SQL INSERT statementsTransaction-levelGood

This interface is the preferred ingestion method as it provides the following benefits:

  • high-throughput ingestion
  • robust ingestion from multiple sources into tables with dedicated systems for reducing congestion
  • configurable commit-lag for out-of-order data via server configuration settings

With sufficient client-side validation, the lack of errors to the client and confirmation isn't necessarily a concern: QuestDB will log out any issues and disconnect on error. The database will process any valid lines up to that point and insert rows.

On the InfluxDB line protocol page, you may find additional details on the message format, ports and authentication.

The Telegraf guide helps you configure a Telegraf agent to collect and send metrics to QuestDB via ILP.

tip

The ILP client libraries provide more user-friendly ILP clients for a growing number of languages.

Examples#

These examples send a few rows of input. These use client libraries as well as raw TCP socket connections, when a client library is not available.

// https://github.com/questdb/c-questdb-client
#include <questdb/ilp/line_sender.hpp>
#include <iostream>
using namespace questdb::ilp::literals;
int main()
{
try
{
questdb::ilp::line_sender sender{"localhost", 9009};
// We prepare all our table names and colum names in advance.
// If we're inserting multiple rows, this allows us to avoid
// re-validating the same strings over and over again.
auto table_name = "trades"_name;
auto name_name = "name"_name;
auto value_name = "value"_name;
sender
.table(trades_name)
.symbol(name_name, "test_ilp1"_utf8)
.column(value_name, 12.4)
.at_now();
sender
.table(trades_name)
.symbol(name_name, "test_ilp2"_utf8)
.column(value_name, 11.4)
.at_now();
sender.flush();
return 0;
}
catch (const questdb::ilp::line_sender_error& err)
{
std::cerr
<< "Error running example: "
<< err.what()
<< std::endl;
return 1;
}
}

Timestamps#

Providing a timestamp is optional. If one isn't provided, the server will automatically assign the server's system time as the row's timestamp value.

Timestamps are interpreted as the number of nanoseconds from 1st Jan 1970 UTC, unless otherwise configured. See cairo.timestamp.locale and line.tcp.timestamp configuration options.

ILP Datatypes and Casts#

Strings vs Symbols#

Strings may be recorded as either the STRING type or the SYMBOL type.

Inspecting a sample ILP we can see how a space ' ' separator splits SYMBOL columns to the left from the rest of the columns.

table_name,col1=symbol_val1,col2=symbol_val2 col3="string val",col4=10.5
โ”ฌ
โ•ฐโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€ separator

In this example, columns col1 and col2 are strings written to the database as SYMBOLs, whilst col3 is written out as a STRING.

SYMBOLs are strings with which are automatically interned by the database on a per-column basis. You should use this type if you expect the string to be re-used over and over, such as is common with identifiers.

For one-off strings use STRING columns which aren't interned.

Casts#

QuestDB types are a superset of those supported by ILP. This means that when sending data you should be aware of the performed conversions.

See:

Constructing well-formed messages#

Different library implementations will perform different degrees content validation upfront before sending messages out. To avoid encoutering issues follow these guidelines.

  • All strings must be UTF-8 encoded.

  • Columns should only appear once per row.

  • Symbol columns must be written out before other columns.

  • Table and column names can't have invalid characters. These should not contain ?, .,,, ', ", \, /, :, (, ), +, -, *, %, ~,' ' (space), \0 (nul terminator), ZERO WIDTH NO-BREAK SPACE.

  • Write timestamp column via designated API, or at the end of the message if you are using raw sockets. If you have multiple timestamp columns write additional ones as column values.

  • Don't change column type between rows.

  • Supply timestamps in order. These need to be at least equal to previous ones in the same table, unless using the out of order feature. This is not necessary if you use the out-of-order feature.

Errors in Server Logs#

QuestDB will always log any ILP errors in its server logs.

From version 6.3, QuestDB will disconnect on the first error encountered on a given TCP ILP connection.

Here is an example error from the server logs caused when a line attempted to insert a STRING into a SYMBOL column.

2022-04-13T13:35:19.784654Z E i.q.c.l.t.LineTcpConnectionContext [3968] could not process line data [table=bad_ilp_example, msg=cast error for line protocol string [columnWriterIndex=0, columnType=SYMBOL], errno=0]
2022-04-13T13:35:19.784670Z I tcp-line-server scheduling disconnect [fd=3968, reason=0]

Inserting NULL values#

To insert a NULL value, skip the column (or symbol) for that row.

For example:

table1 a=10.5 1647357688714369403
table1 b=1.25 1647357698714369403

Will insert as:

abtimestamp
10.5NULL2022-03-15T15:21:28.714369Z
NULL1.252022-03-15T15:21:38.714369Z

If you don't immediately see data#

If you don't see your inserted data, this is usually down to one of two things:

  • You prepared the messages, but forgot to call .flush() or similar in your client library, so no data was sent.

  • The internal timers and buffers within QuestDB did not commit the data yet. For development (and development only), you may want to tweak configuration settings to commit data more frequently.

    server.conf
    cairo.max.uncommitted.rows=1
    line.tcp.maintenance.job.interval=100

    Refer to ILP's commit strategy documentation for more on these configuration settings.

Authentication#

ILP can additionally provide authentication. This is an optional feature which is documented here.

Third-party Library Compatibility#

Use our own client libraries and/or protocol documentation: Clients intended to work with InfluxDB will not work with QuestDB.

PostgreSQL wire protocol#

QuestDB also supports the same wire protocol as PostgreSQL, allowing you to connect and query the database with various third-party pre-existing client libraries and tools.

You can connect to TCP port 8812 and use both INSERT and SELECT SQL queries.

tip

InfluxDB Line Protocol is the recommended primary ingestion method in QuestDB. SQL INSERT statements over the PostgreSQL offer feedback and error reporting, but have worse overall performance.

Here are a few examples demonstrating SQL INSERT queries:

Create the table:

psql -h localhost -p 8812 -U admin -d qdb \
-c "CREATE TABLE IF NOT EXISTS t1 (name STRING, value INT);"

Insert row:

psql -h localhost -p 8812 -U admin -d qdb -c "INSERT INTO t1 VALUES('a', 42)"

Query back:

psql -h localhost -p 8812 -U admin -d qdb -c "SELECT * FROM t1"

Note that you can also run psql from Docker without installing the client locally:

docker run -it --rm --network=host -e PGPASSWORD=quest \
postgres psql ....

HTTP REST API#

QuestDB exposes a REST API for compatibility with a wide range of libraries and tools. The REST API is accessible on port 9000 and has the following insert-capable entrypoints:

EntrypointHTTP MethodDescriptionAPI Docs
/impPOSTImport CSV dataReference
/exec?query=..GETRun SQL Query returning JSON result setReference

For details such as content type, query parameters and more, refer to the REST API docs.

/imp: Uploading Tabular Data#

tip

InfluxDB Line Protocol is the recommended primary ingestion method in QuestDB. CSV uploading offers insertion feedback and error reporting, but has worse overall performance.

See /imp's atomicity query parameter to customize behavior on error.

Let's assume you want to upload the following data via the /imp entrypoint:

data.csv
col1,col2,col3
a,10.5,True
b,100,False
c,,True

You can do so via the command line using cURL or programmatically via HTTP APIs in your scripts and applications.

By default, the response is designed to be human-readable. Use the fmt=json query argument to obtain a response in JSON. You can also specify the schema explicitly. See the second example in Python for these features.

This example imports a CSV file with automatic schema detection.

Basic import with table name
curl -F data=@data.csv http://localhost:9000/imp?name=table_name

This example overwrites an existing table and specifies a timestamp format and a designated timestamp column. For more information on the optional parameters to specify timestamp formats, partitioning and renaming tables, see the REST API documentation.

Providing a user-defined schema
curl \
-F schema='[{"name":"ts", "type": "TIMESTAMP", "pattern": "yyyy-MM-dd - HH:mm:ss"}]' \
-F data=@weather.csv 'http://localhost:9000/imp?overwrite=true&timestamp=ts'

/exec: SQL INSERT Query#

The /exec entrypoint takes a SQL query and returns results as JSON.

We can use this for quick SQL inserts too, but note that there's no support for parameterized queries that are necessary to avoid SQL injection issues.

tip

Prefer the PostgreSQL interface if you are generating sql programmatically.

Prefer ILP if you need high-performance inserts.

# Create Table
curl -G \
--data-urlencode "query=CREATE TABLE IF NOT EXISTS trades(name STRING, value INT)" \
http://localhost:9000/exec
# Insert a row
curl -G \
--data-urlencode "query=INSERT INTO trades VALUES('abc', 123456)" \
http://localhost:9000/exec