Skip to main content

Query INFLUXDB with SQL compatible with SQLITE.

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.

Installation

Install, upgrade and uninstall influxsql with the following commands:

$ pip install influxsql
$ pip install --upgrade influxsql
$ pip uninstall influxsql

Dependencies

The influxsql distribution is supported and tested on Python 2.7.

Main dependencies are:

  • influxdb package
  • apsw package

apsw is a python access to Sqlite supporting virtual tables.

Documentation

Influxdb documentation is available at http://influxdb-python.readthedocs.org

Apsw documentation is available at https://rogerbinns.github.io/apsw/

There is no specific documentation for influxsql except the examples below.

Examples

A typical influxsql session looks like:

$ python
>>> import influxsql
>>> session = influxsql.InfluxSQLClient()

A typical influxdb session looks like:

$ python
>>> import influxdb
>>> session = influxdb.InfluxDBClient()

influxsql.InfluxSQLClient is a class extending influxdb.InfluxDBClient

So everything available with InfluxDBClient is available with InfluxSQLclient.

Example:

$ python
>>> import influxsql
>>> session = influxsql.InfluxSQLClient()
>>> json_body = [ { "measurement": "cpu_load_short", "tags": { "host": "server01", "region": "us-west" }, "time": "2009-11-10T23:00:00Z", "fields": { "value": 0.64 } } ]
>>> session.create_database('example')
>>> session.swith_database('example')
>>> session.write_points(json_body)
>>> result = session.query('select value from cpu_load_short')
>>> print("Result: {0}".format(result))

In this example, no value added coming from influxsql in comparison with influxdb. This example only shows that everything possible with influxdb is also possible with influxsql.

Influxsql defines a method to query the influxdb database with a SQL request compatible with Sqlite. Example:

$ python
>>> def dict_factory(cursor, row):
...     d = dict()
...     for idx, col in enumerate(cursor.description): d[col[0]] = row[idx]
...     return d
...
>>> import influxsql
>>> session = influxsql.InfluxSQLClient()
>>> session.switch_database('example')
>>> session.apsw.setrowtrace(dict_factory)
>>> session.detach('cpu_load_short')
>>> session.attach('cpu_load_short')
>>> result = session.sql("select * from cpu_load_short")
>>> for row in result: print(row)

Explanation of this example:

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…

$ python
>>> import influxsql
>>> session = influxsql.InfluxSQLClient()
>>> session.switch_database('example')
>>> session.imp(file='/tmp/music.db',table='artists')

An additional field “time” will be created with the current time (precision microsecond)

Project details


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
influxsql-0.0.16.tar.gz (5.3 kB) Copy SHA256 hash SHA256 Source None

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging AWS AWS Cloud computing DataDog DataDog Monitoring Fastly Fastly CDN SignalFx SignalFx Supporter DigiCert DigiCert EV certificate StatusPage StatusPage Status page