Analytics DB
About Analytics DB
Nodely DataWarehouse is based on open source ClickHouse DW.
Interfaces
DW interface is exposed by default as:
- SQL REST API over HTTPS protocol at https://dw2api.allo.info
- SQL Editor/playground
- Apache Superset Self service business intelligence solution: https://dw2bi.allo.info
Custom endpoints/integrations include
- ODBC PowerBI DataSource
- Postgres or MySQL wire protocol endpoint
ClickHouse SQL
Clickhouse has powerful SQL language extensions and functions. It pays to learn about the join types, join orders, aggregations.
See: ClickHouse SQL documentation
Schema
Allo analytics schema consists of several databases.
Each customer has access to dedicated scratch (no backup) database and common databases each corresponding to the named network - mainnet, voitestnet with custom ledger schema. Scratch database is available only on primary cluster. Failover does not have dedicated scratch database by default. Manual setup of table replication or backup schedule is possible with PS.
[Allo.info DW schema](https://www.notion.so/Allo-info-DW-schema-ee721bd35e8648b6980ecaa9b70329b7?pvs=21)
Dictionaries
Dictionaries are in-memory maps that can quickly map between a Uint64 ID and a named value.
Dictionaries periodically refresh data from their source. Refreshes are atomic, no duplicated entries are to be expected.
Dictionaries have dict_ as a prefix in their names and are grouped in databases with _mem suffix:
- mainnet_mem
- testnet_mem
- betanet_mem
Each dictionary has corresponding JOIN Table that can be used for classical (yet instant) joins .
All tables with no dict_ prefix in *_mem databases can be used for instant joins.
Tables
All native (non dictionary based) tables are append only and can contain duplicated rows. Deduplication is eventual and nondeterministic.
To see dedupilcated view one must use FINAL keyword or user argMax functions to select only relevant rows.
Working with external data
Default settings for end-users
| Setting | Value | Description |
|---|---|---|
| optimize_move_to_prewhere_if_final | 1 | Optimize deduplication by applying WHERE filters before dedup phase. |
| final | 1 | Automatically appliesĀ https://clickhouse.com/docs/en/sql-reference/statements/select/from#final-modifierĀ modifier to all tables in a query |
| union_default_mode | DISTINCT | Default union behaviour when not specified |
| optimize_rewrite_aggregate_function_with_if | 1 | |
| allow_experimental_analyzer | 0 | |
| optimize_syntax_fuse_functions | 1 | |