· clickhouse til

ClickHouse: Configure default output format

When running queries with ClickHouse Local, the results are rendered back to the screen in a table format in blocks. This default format is called PrettyCompact and most of the time this works fine, but sometimes you can end up with multiple mini-tables. In this blog post, we’re going to learn how to change the default format so that all the results show in one table.

But first, let’s see how the problem manifests. I’ve been playing around with the Kaggle Singaporean Cryptocurrency / Crypto Dataset and I wrote the following query after downloading the ZIP file that contains all the data.

from file('data/Singapore Crypto.zip :: metadata.csv', CSVWithNames)
select *
LIMIT 10;
Output
┌─Coin Pair Name──────┬─Coin Pair Symbol─┬─Filename──────┐
│ Bitcoin SGD         │ BTC-SGD          │ BTC-SGD.csv   │
│ Ethereum SGD        │ ETH-SGD          │ ETH-SGD.csv   │
│ Tether USDt SGD     │ USDT-SGD         │ USDT-SGD.csv  │
│ BNB SGD             │ BNB-SGD          │ BNB-SGD.csv   │
│ USD Coin SGD        │ USDC-SGD         │ USDC-SGD.csv  │
│ XRP SGD             │ XRP-SGD          │ XRP-SGD.csv   │
│ Lido Staked ETH SGD │ STETH-SGD        │ STETH-SGD.csv │
│ Cardano SGD         │ ADA-SGD          │ ADA-SGD.csv   │
│ Dogecoin SGD        │ DOGE-SGD         │ DOGE-SGD.csv  │
└─────────────────────┴──────────────────┴───────────────┘
┌─Coin Pair Name───┬─Coin Pair Symbol─┬─Filename─────┐
│ Wrapped TRON SGD │ WTRX-SGD         │ WTRX-SGD.csv │
└──────────────────┴──────────────────┴──────────────┘

You can see that the results are rendered in two different tables. We can fix that by specifying FORMAT PrettyMonoBlock at the end of the query, like this:

from file('data/Singapore Crypto.zip :: metadata.csv', CSVWithNames)
select *
LIMIT 10
FORMAT PrettyMonoBlock;

We then get a single table result:

Output
┏━━━━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━━━━┳━━━━━━━━━━━━━━━┓
┃ Coin Pair Name      ┃ Coin Pair Symbol ┃ Filename      ┃
┡━━━━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━━━━╇━━━━━━━━━━━━━━━┩
│ Bitcoin SGD         │ BTC-SGD          │ BTC-SGD.csv   │
├─────────────────────┼──────────────────┼───────────────┤
│ Ethereum SGD        │ ETH-SGD          │ ETH-SGD.csv   │
├─────────────────────┼──────────────────┼───────────────┤
│ Tether USDt SGD     │ USDT-SGD         │ USDT-SGD.csv  │
├─────────────────────┼──────────────────┼───────────────┤
│ BNB SGD             │ BNB-SGD          │ BNB-SGD.csv   │
├─────────────────────┼──────────────────┼───────────────┤
│ USD Coin SGD        │ USDC-SGD         │ USDC-SGD.csv  │
├─────────────────────┼──────────────────┼───────────────┤
│ XRP SGD             │ XRP-SGD          │ XRP-SGD.csv   │
├─────────────────────┼──────────────────┼───────────────┤
│ Lido Staked ETH SGD │ STETH-SGD        │ STETH-SGD.csv │
├─────────────────────┼──────────────────┼───────────────┤
│ Cardano SGD         │ ADA-SGD          │ ADA-SGD.csv   │
├─────────────────────┼──────────────────┼───────────────┤
│ Dogecoin SGD        │ DOGE-SGD         │ DOGE-SGD.csv  │
├─────────────────────┼──────────────────┼───────────────┤
│ Wrapped TRON SGD    │ WTRX-SGD         │ WTRX-SGD.csv  │
└─────────────────────┴──────────────────┴───────────────┘

So far, so good. But how can we make PrettyMonoBlock the default format?

When using ClickHouse Local or the ClickHouse Client, we can provide a config file in XML or YAML format. The setting we need to define is format and the value should be PrettyMonoBlock:

config-local.xml
<?xml version="1.0" ?>
<clickhouse>
        <format>PrettyMonoBlock</format>
</clickhouse>

or

config-local.yaml
format: PrettyMonoBlock

We can then have ClickHouse Local/Client use the config by passing the file using the -C parameter.

./clickhouse local -m -C config-local.xml

or

./clickhouse client -m -C config-local.yaml

In addition, if you’re using ClickHouse Local, it will automatically use a config file named config.xml.

Once we’ve got that config setup, we can re-run the query and it will return us a single table without needing to specify the format in the query.

  • LinkedIn
  • Tumblr
  • Reddit
  • Google+
  • Pinterest
  • Pocket