Analytics DB
About Analytics DB
Section titled “About Analytics DB”Nodely DataWarehouse is based on open source ClickHouse DW.
Interfaces
Section titled “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
Section titled “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
Section titled “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
Section titled “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
Section titled “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
Section titled “Working with external data”Default settings for end-users
Section titled “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 | |