Migrating from MySQL to InfluxDB

Store
1

Hi all,

I have this application where I store stock market data and also stored calculated indicator values on that data. I currently use MySQL 5.7 JSON columns to facilitate my storage needs but it is getting quite slow by now with millions and millions of rows. I explored InfluxDB a year of 2 back but went for MySQL back at the time because InfluxDB was still a lot more beta.

Now I have rewritten some code to write my stock market data and calculated indicator values to InfluxDB but can’t seem to find out how to accomplish something I need in my application.

I have measurements called "indicators"
Tags: market (aapl), interval (15minutes for example)
Fields: somewhere between 100-600 different indicator names and their values for example “ssl: 101” which then references a specific indicator configuration in my application.

I can successfully store all the information and am pretty happy with the write speeds. What I know what to do is to get the last 2 measurements of each market with a specific interval BUT also filter on the values like this in pseudo query code: WHERE measurement1.ssl > 100 AND measurement2.ssl < 100
Basically checking if the value of the field ssl changed from below 100 to above 100. This is something I’m currently able to do with MySQL but I have no idea how to do this with Influx. I want to prevent doing this kind of logic in my application because in the real world scenario I have about ~20 field values to check against conditions. Doing this in my application would require loading all data each time so I rather do it with an influxql query.

Forgive me if I’m using the incorrect terms here since I’m new to InfluxDB.

2 
SELECT last("ssl") FROM "measurement1", "measurement2" AS ssl1, ssl2  WHERE ssl1 > 100 AND ssl2 < 100

Should do what you’re asking for. That will select the last value for ‘ssl’ from each measurement, and pass them as ssl1 and ssl2 to the WHERE clause.

Best regards,
dg

3

Thank you very much for your response. It did give me a push in the right direction but its not yet giving me the possibilities I am looking for.

name: indicators
tags: market=usd-aapl
time                 market   cur_close prev_close cur_ssl    prev_ssl
----                 ------   --------- ---------- -------    --------
2018-01-21T23:00:00Z usd-aapl 177                  172.943385 
2018-01-18T23:00:00Z usd-aapl           178.46                172.787613

This is my result now for the query below:

SELECT market, cur_close, prev_close, cur_ssl, prev_ssl FROM  (SELECT  market, p_close AS "cur_close", p_open AS "cur_open",  ssl AS "cur_ssl" FROM  indicators  WHERE interval='i1d'  ORDER BY DESC  LIMIT 1),  (SELECT market, p_close AS "prev_close", p_open AS "prev_open",  ssl AS "prev_ssl" FROM  indicators  WHERE  interval='i1d'  ORDER BY DESC  LIMIT 1 OFFSET 1)  GROUP BY  market  ORDER BY DESC

However like this I can’t check if cur_ssl is above prev_ssl for example because it are “2 rows” in the resultset. Wrapping this in another query doesn’t seem to help because I seem to lose the ability to check those fields in a WHERE condition. I still feel I am doing this the wrong way.

Doing this:
SELECT LAST(market), SUM(cur_close) as cur_close, SUM(prev_close) as prev_close, SUM(cur_ssl) as cur_ssl FROM (SELECT market, p_close AS “cur_close”, p_open AS “cur_open”, ssl AS “cur_ssl”, rsi14 AS “cur_rsi14” FROM indicators WHERE interval=‘i1d’ ORDER BY DESC LIMIT 1), (SELECT p_close AS “prev_close”, p_open AS “prev_open”, ssl AS “prev_ssl”, rsi14 AS “prev_rsi14” FROM indicators WHERE interval=‘i1d’ ORDER BY DESC LIMIT 1 OFFSET 1) GROUP BY market ORDER BY DESC

Gives me everything in 1 “row” however I still can’t compare values in the WHERE and wrapping it in another SELECT seems very strange… There must be an easier way to do this I think.

4

Was looking into this myself as well and it’s frustratingly close to pulling off. With the table set up along the definition lines listed earlier in the thread, this is what we see…

> SELECT * FROM (SELECT market, p_close AS "cur_close", ssl AS "cur_ssl" FROM indicators ORDER BY 
time DESC LIMIT 1), (SELECT market, p_close AS "prev_close", ssl AS "prev_ssl" FROM indicators 
ORDER BY time DESC LIMIT 1 offset 1) order by time desc
name: indicators
time                cur_close cur_ssl market     prev_close prev_ssl
----                --------- ------- ------     ---------- --------
1518913919647387496 102       82      'usd_aapl'
1518913899749735569                   'usd_aapl' 101        81

From what I can tell the cur_close > prev_close is unable to complete because we’re comparing a non-nil value to a nil value. Thinking we need to fill this data somehow…

5

I haven’t tried this, so YMMV, but have you tried putting in a couple of WHERE clauses?

SELECT market, p_close AS "cur_close" WHERE p_close != nil

etc.

Again, I haven’t tried this, just thinking out loud here.

Best regards,
dg