Exploration

    Overview

    This document aims to enhance the reader's ability to navigate through Clickhouse using SQL and explore the available tables and their structures.

    List of Tables

    To get a list of all available tables, execute the following query:

    1
    SHOW TABLES

    You can filter the list of tables using a regex. For example, to get the list of all tables containing price in their name:

    1
    SHOW TABLES LIKE '%price%'

    Test in Queries

    Example output:

    1
    2
    3
    4
    ┌─name───────────────────┐
    │ asset_price_pairs_only │
    │ asset_prices_v3        │
    └────────────────────────┘

    The % at the beginning means that there could be other characters to the left. The % at the end means that there could be other characters to the right.

    Get Information About a Table

    To inspect the structure of a given table, execute the DESCRIBE statement:

    1
    DESCRIBE intraday_metrics

    Test in Queries

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    ┌─name───────────────┬─type─────────────┬─default_type─┬─default_expression─────────────┬─comment─┬─codec_expression─┬─ttl_expression─┐
    │ asset_id           │ UInt64           │              │                                │         │                  │                │
    │ computed_at        │ DateTime         │ DEFAULTnow()                          │         │                  │                │
    │ name               │ Nullable(String) │ DEFAULTCAST(NULL, 'Nullable(String)') │         │                  │                │
    │ versionDate             │              │                                │         │                  │                │
    │ asset_ref_id       │ UInt64           │              │                                │         │                  │                │
    │ ticker_slug        │ Nullable(String) │ DEFAULTCAST(NULL, 'Nullable(String)') │         │                  │                │
    │ decimals           │ UInt32           │ DEFAULTCAST(0, 'UInt32')              │         │                  │                │
    │ contract_addresses │ Array(String)    │              │                                │         │                  │                │
    │ specification      │ Nullable(String) │              │                                │         │                  │                │
    └────────────────────┴──────────────────┴──────────────┴────────────────────────────────┴─────────┴──────────────────┴────────────────┘

    To see how a table was created, execute the SHOW CREATE TABLE statement. This includes information about partitioning, ordering, table engine, and other settings. Knowing the ORDER BY helps create better and faster queries.

    1
    SHOW CREATE TABLE intraday_metrics

    Test in Queries

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    ┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
    │ CREATE TABLE default.intraday_metrics                                                                                  │
    │ (                                                                                                                      │
    │     `asset_id` UInt64 CODEC(DoubleDelta, LZ4),                                                                         │
    │     `metric_id` UInt64 CODEC(DoubleDelta, LZ4),                                                                        │
    │     `dt` DateTime CODEC(DoubleDelta, LZ4),                                                                             │
    │     `value` Float64,                                                                                                   │
    │     `computed_at` DateTime                                                                                             │
    │ )                                                                                                                      │
    │ ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/global/intraday_metrics_v2', '{hostname}', computed_at)      │
    │ PARTITION BY toYYYYMM(dt)                                                                                              │
    │ ORDER BY (asset_id, metric_id, dt)                                                                                     │
    │ SETTINGS index_granularity = 8192                                                                                      │
    └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

    Get data from a table

    After inspecting the structure of a given table, you can execute a few simple queries to obtain some data from the table to see how it looks. Most of the time, it makes more sense to select more recent data instead of data starting from the beginning, as it will be more relevant. To improve readability, you can apply functions for transforming the metric_id and asset_id to their names. The , * syntax allows you to select all fields but also add something else to the result.

    1
    2
    3
    4
    5
    6
    7
    SELECT
        get_asset_name(asset_id) AS slug,
        get_metric_name(metric_id) AS metric,
        *
    FROM daily_metrics_v2
    WHERE dt >= (now() - toIntervalDay(2))
    LIMIT 10

    Test in Queries

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    ┌─slug──────────────────────┬─metric──────────────────┬─metric_id─┬─asset_id─┬─────────dt─┬───────────────value─┬─────────computed_at─┐
    │ bnb-binance-usd           │ adjusted_daa_divergence │       681 │    41039 │ 2022-08-15 │ -1.5018654389124684 │ 2022-08-15 00:11:03 │
    │ bnb-tether                │ adjusted_daa_divergence │       681 │    41048 │ 2022-08-15 │ -2.3807976412934018 │ 2022-08-15 00:11:03 │
    │ bnb-usd-coin              │ adjusted_daa_divergence │       681 │    41051 │ 2022-08-15 │ -1.6207922927296166 │ 2022-08-15 00:11:03 │
    │ bnb-1inch                 │ payment_count           │       179 │    41038 │ 2022-08-15 │                   4 │ 2022-08-15 00:13:20 │
    │ bnb-chainlink             │ payment_count           │       179 │    41040 │ 2022-08-15 │                 103 │ 2022-08-15 00:13:20 │
    │ bnb-binance-usd           │ payment_count           │       179 │    41039 │ 2022-08-15 │                2688 │ 2022-08-15 00:13:20 │
    │ bnb-chromia               │ payment_count           │       179 │    41041 │ 2022-08-15 │                   3 │ 2022-08-15 00:13:20 │
    │ bnb-trust-wallet-token    │ payment_count           │       179 │    41049 │ 2022-08-15 │                   6 │ 2022-08-15 00:13:20 │
    │ bnb-green-metaverse-token │ payment_count           │       179 │    41042 │ 2022-08-15 │                  25 │ 2022-08-15 00:13:20 │
    │ bnb-uniswap               │ payment_count           │       179 │    41050 │ 2022-08-15 │                  15 │ 2022-08-15 00:13:20 │
    └───────────────────────────┴─────────────────────────┴───────────┴──────────┴────────────┴─────────────────────┴─────────────────────┘

    How to Interpret the Table Structure

    When looking at a table structure, there are several important things that the reader needs to pay attention to.

    Let's take a look at the following table structure:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    ┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
    │ CREATE TABLE default.intraday_metrics                                                                                  │
    │ (                                                                                                                      │
    │     `asset_id` UInt64 CODEC(DoubleDelta, LZ4),                                                                         │
    │     `metric_id` UInt64 CODEC(DoubleDelta, LZ4),                                                                        │
    │     `dt` DateTime CODEC(DoubleDelta, LZ4),                                                                             │
    │     `value` Float64,                                                                                                   │
    │     `computed_at` DateTime                                                                                             │
    │ )                                                                                                                      │
    │ ENGINE = ReplicatedReplacingMergeTree('/clickhouse/tables/global/intraday_metrics_v2', '{hostname}', computed_at)      │
    │ PARTITION BY toYYYYMM(dt)                                                                                              │
    │ ORDER BY (asset_id, metric_id, dt)                                                                                     │
    │ SETTINGS index_granularity = 8192                                                                                      │
    └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

    Table Name

    The complete table name is default.intraday_metrics, where default refers to the database and intraday_metrics is the table name. Since queries are executed in the default database, you can omit default when referencing the table.

    Columns

    The columns are represented by their names and corresponding data types. Data types may have modifiers that determine how the data is stored on disk (e.g., CODEC(DoubleDelta, LZ4)). These modifiers can be disregarded when examining a table.

    ORDER BY

    This should not be confused with ORDER BY clause used when you write an SQL query, which controls how the records in the result set are sorted. The ORDER BY examined here is a property of the database table and controls how the data is sorted on disk, acting like an index.

    This is a crucial field to understand and consider when writing a query. ClickHouse is a column-oriented database, meaning that the data for columns is stored continuously on-disk. This makes it more challenging and less efficient to use multiple indexes, so the column order in ORDER BY is essential for writing performant queries. If filters for the columns at the beginning of the list are present, the query will run faster.

    Engine

    The Table Engine controls how data is stored, updated, and accessed. If the engine is *MergeTree, then the FINAL keyword needs to be used. See the Writing SQL Queries page for more detailed examples and reasoning.

    Partition

    The partitioning has little to no effect on performance, making it safe to ignore.