[Feature] Implement Clickhouse backend for persistence

[petercool]

Feature Description: Implement ClickHouse Backend for Persistence

Background

Currently, we use a traditional database management system for handling our application data. Due to the complex nature of our data processing and analytics requirements, we are experiencing limitations with our current system especially in terms of real-time query processing, scalability, and resilience.

We're implementing ClickHouse as a new backend for persistence. ClickHouse is an open-source column-oriented database management system that allows generating analytical data reports in real time.

ClickHouse's superior performance for real-time query processing would be a boon for our application which requires frequent data read-write operations. ClickHouse servers can easily be scaled horizontally to process more queries and store more data.

Task

cryptofeed/backends/postgres.py - PostgreSQL backend cryptofeed/backends/mongo.py - MongoDB backend

You may refer the similar database backend for persisting the data

[linear[bot]]

DEV-148 [Feature] Implement Clickhouse backend for persistence

[petercool]

You can also try on GCP Pub/Sub

Core Concepts

1. Channel (Market Data)

   • Channel is a concept that is related to market data in financial trading. Each type of data in a typical trade market data feed can be considered a channel. Here are some examples:

   • L1_BOOK, L2_BOOK, L3_BOOK: These represent Level 1, 2, and 3 order books respectively. They provide information about market depth, with the level indicating the amount of information provided. Level 1 provides the best bid and ask price, Level 2 includes prices and volumes aggregated at a price level, and Level 3 gives the most detailed view with all individual market maker quotes.

   • TRADES: This channel tracks all the trades that are happening in the market.

   • TICKER: This provides the price and volume information that is updated every time a transaction occurs.

   • FUNDING: This pertains to the funding rate associated with perpetual contracts on some derivatives exchanges.

   • OPEN_INTEREST: This represents the total number of outstanding derivative contracts, such as options or futures that have not been settled.

   • LIQUIDATIONS: This highlights the forced closure of positions that happens when traders are unable to meet margin requirements for their trades.

   • INDEX: This deals with information pertaining to market indices.

   • UNSUPPORTED: This tag is used for data channels that the system currently does not support.

   • CANDLES: This channel provides candlestick chart data that graphical represents price movements.

2. Exchange

   • In the context of trading, an exchange is a marketplace where securities, commodities, derivatives and other financial instruments are traded. The core function of an exchange is to ensure fair and orderly trading and the efficient dissemination of price information for any securities trading on that exchange.

3. Backend Systems

   • These are technologies used for managing and storing data. In our context, we have:
     • PostgreSQL: This is an open-source relational database management system emphasizing extensibility and SQL compliance.
     • GCP Pub/Sub: This refers to Google Cloud Pub/Sub, a scalable event messaging service for real-time analytics and stream processing.
     • ClickHouse: This is an open-source column-oriented database management system for online analytical processing of queries.