Classifiers

Project description

Introduction

InfluxDB is a nice time series database, especially efficient in
performance for storing a large numbers of events. However the query
language provided with Influxdb has many shortcomings (no joins between
measurements, restrictive order by, syntax very specific in comparison
with standard SQL, …)

The basic idea of influxsql is to extend influxdb with a true, fully
fonctional query language compatible with Sqlite.

The design behind influxsql is to associate a database within Influxdb
with a Sqlite in-memory database and to realize a mapping between
measurements and virtual tables in Sqlite.

We define first a function (dict_factory) to specify the format of each
row returned by a query. Here we want to return a row as a dictionary.
This step is not mandatory. By default, apsw will return a tuple.

The Sqlite database associated with the session is an in-memory
database. This database can be queried as any sqlite database with the
handle “session.apsw”.

In our example, we have used “session.apsw.setrowtrace(dict_factory)”
to tell to apsw that we want each row in a specific format.

For each measurement to query with sqlite, we need to define a virtual
table. This is done in the statement
“session.attach(‘cpu_load_short’)”.

The statement “session.detach(‘cpu_load_short’)” has the side effect
to remove the virtual table within sqlite. If the description of the
measurement is static (tags and fields unchanged over time), the detach
method is not mandatory to be called. But if the description of the
measurement is dynamic (new tags or new fields), it’s necessary to call
detach and attach to get all charactéristics of the measurement.

Detach and attach methods could have been integrated to the sql method,
but for performance reasons, this has not been done in the current
version.

In this example, the request “select * from cpu_load_short” is very
simple. You can replace this query by any query compatible with the sql
provided by sqlite. You can create user fonctions in python and
reference them in the query.

You can of course, realize joins between measurements.

You can have access to all “order by” you want.

In addition to the methods described above, influxsql provides a method
named “imp” allowing to import to import a table coming from sqlite to a
measurement.

Example:

Suppose we have a Sqlite database in the file /tmp/music.db and we want
to upload a table named “artists” in influxdb…