Clickhouse utilites

clickhouse.sql

-- Make japanese candles from raw trades
SELECT t.tm AS time, a.price AS open, t.lp AS low, t.hp AS high, b.price AS close
  FROM (
    SELECT toStartOfHour(time) AS tm, MIN(price) AS lp, MAX(price) AS hp, MIN(id) AS lid, MAX(id) AS hid
      FROM binance_cfx_usdt_trades GROUP BY toStartOfHour(time)
  ) AS t
  INNER JOIN binance_cfx_usdt_trades AS a ON a.id = t.lid 
  INNER JOIN binance_cfx_usdt_trades AS b ON b.id = t.hid
  ORDER BY t.tm
   
-- Create view wich shows table partitions with sizes
CREATE VIEW meta.parts
(
    `database` String,
    `table` String,
    `partition` String,
    `disk_used` String,
    `rows` UInt64
) AS
SELECT
    database,
    table,
    partition,
    formatReadableSize(sum(bytes_on_disk)) AS disk_used,
    sum(rows) AS rows
FROM system.parts
WHERE database != 'system'
GROUP BY
    database,
    table,
    partition
ORDER BY
    database ASC,
    table ASC,
    partition ASC