0

I have 15 GB csv file. It has two columns (trade_time and price).

This is how the data looks like.

trade_time,price
2020-01-01 00:00:01.481,7189.42
2020-01-01 00:00:01.708,7189.42
2020-01-01 00:00:06.290,7189.5
2020-01-01 00:00:06.291,7190.52
2020-01-01 00:00:07.161,7188.97
2020-01-01 00:00:08.274,7189.93
2020-01-01 00:00:09.277,7190.47
2020-01-01 00:00:09.384,7190.47
2020-01-01 00:00:09.630,7190.11
2020-01-01 00:00:09.848,7189.74
2020-01-01 00:00:10.098,7189.46
2020-01-01 00:00:10.197,7189.16
2020-01-01 00:00:10.351,7189.1

I would like to check whether price is up or down by 0.5%. If the price hits +0.5% first, the result is 1. If the price hits -0.5% first, then the result is 0.

At the moment, I'm using this python solution. If database can perform better for my use case, then I would like to move to the database solution.

  1. Since my task involves timeseries data, which database is better for my use case? SQL or NoSQL?
  2. Is it really possible to do percentage based comparison in databases? e.g. +0.5%
  3. How long the indexing process usually take for 1 billion rows?

Edit: dbfiddle can be found here.

CREATE TABLE Trades (
    trade_time datetime(3)  NOT NULL PRIMARY KEY,
    price      NUMERIC(7,2) NOT NULL
);

INSERT INTO Trades(trade_time,price) VALUES
 ('2020-01-01 00:00:01.481',7189.42)
,('2020-01-01 00:00:01.708',7189.42)
,('2020-01-01 00:00:06.290',7189.5)
,('2020-01-01 00:00:06.291',7190.52)
,('2020-01-01 00:00:07.161',7188.97)
,('2020-01-01 00:00:08.274',7189.93)
,('2020-01-01 00:00:09.277',7190.47)
,('2020-01-01 00:00:09.384',7190.47)
,('2020-01-01 00:00:09.630',7190.11)
,('2020-01-01 00:00:09.848',7189.74)

CSV Import to MySQL

LOAD DATA INFILE 'trades.csv' INTO TABLE Trades
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(trade_time, price);
18
  • @Vérace-СлаваУкраїні I don't have a database yet. I have only the csv file. So yes, I can go for version 8. I'm not sure what you mean by 3 servers. Could you elaborate?
    – John
    Jun 12 at 6:41
  • @Vérace-СлаваУкраїні I have updated my question with sample data. Thanks
    – John
    Jun 12 at 9:22
  • @Vérace-СлаваУкраїні I hope you are asking me how I'm loading the csv data into mysql. I use LOAD DATA INFILE. I have edited my question to add details. Please do understand that I'm a beginner. So if LOAD DATA INFILE is not a better solution, feel free to recommend the better solution. Thanks
    – John
    Jun 13 at 4:32
  • @Vérace-СлаваУкраїні My CSV file has billions of rows. So I'm worried, a disk based solution will be slow. I'm not seeking persistence here. I'm only looking for a way to finish my task faster. After finishing the task, I don't need the database. So even an in-memory solution is okay for use case. Thanks
    – John
    Jun 13 at 4:36
  • You're assuming that a disk-based solution will be slow! WIth partitioning and a decent ELT strategy, that needn't be the case. You have to look at the LAG() and LEAD() window functions. Also, you appear to be searching for a suitable database - may I suggest that you take a look at PostgreSQL - there are two systems (extensions) which use it and are specifically geared towards Time Series - TimescaleDB and Citus Data - you might want to take a look. After your LOAD DATA..., you can run an UPDATE using LAG/LEAD to track differences (absolute or %) between adjacent records! HTH... Jun 13 at 5:05

1 Answer 1

0

(Discussing a implementation using MySQL)

CREATE TABLE Trades (
    trade_time DATETIME(3) NOT NULL, -- millisecond resolution
    price DECIMAL(8,2) NOT NULL,     -- or possibly something else
    PRIMARY KEY(trade_time)          -- UNIQUE and an INDEX
) ENGINE=InnoDB

You seem to be concerned about only one stock, correct? (If not then PRIMARY KEY (stock_id, trade_time) in that order.)

($start and $start_ts come from your app that is building and running the query. They need to hold the beginning price and time.)

SELECT ts, price
     FROM Trades
     WHERE trade_time > $start_ts,
       AND ABS(price - $start) / $start > 0.005  -- up or down .5%
     ORDER BY trade_time ASC

to find the next time when the price has moved by 0.5%. Similarly, use < and DESC to find the previous time.

Because of the indexing and clustering, this query will take [typically] less than 1ms. (Of course, if the price takes a month to move by .5%, the query will take much longer.)

A 5-minute candle (with just lo, avg, hi) is probably done via:

SELECT  MIN(trade_time) as lo,
        AVG(trade_time) as avg,
        MAX(trade_time) as hi
    FROM Trades
    WHERE trade_time >= $start_ts
      AND trade_time  < $start_ts + INTERVAL 5 MINUTE

The candle query should be run once every 5 minutes and saved in another table with 4 columns: trade_time, lo, avg, hi. It will take time to catch up with the data you already have, but future data should be computed on a 5-minute EVENT or cron.

It will take time to get the PRIMARY KEY in place. But once in place, new data will arrive just as fast as before. It may take hour(s) to add the PK.

4
  • Thanks very much for the answer. I'm unable to upvote it due to my low reputation. I have a question though. You are using the variable $start. But I don't see anywhere it is being declared. Could you tell me how that variable works?
    – John
    yesterday
  • You are right. At the moment, I'm focusing only one stock. Thanks
    – John
    yesterday
  • @John - I annotated my Answer with mention of $start and $start_ts.
    – Rick James
    yesterday
  • Got it. Thanks.
    – John
    20 hours ago

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.