Introduction
“Tabix” is an open-source SQL Editor and a lightweight business intelligence tool for ClickHouse. It also works from the browser, and installing it is no need.
This document aims to explain the configuration and usage of Tabix.
Installation and Configuration
For using Tabix in ClickHouse, you don’t have to install any extra components or tools. There are a variety of ways to use it.
This is:
- Hosted: If the ClickHouse Server is directly accessible by the internet, you can use Tabix just by going to http://ui.tabix.io/.
- Local: You can download and install the latest release from there.
- Embedded: By default, Tabix is embedded in the ClickHouse Server. You just need to uncomment one of the configuration parameters in the “config.xml” file.
- Compile from source: You can install and build the source code as described here.
- Docker: You can build and run a Docker image.
In the scope of this document, I’ll configure and use the “Embedded” Tabix server. To configure the embedded Tabix server, you just need to uncomment the beloved parameter in the “config.xml” file and restart the ClickHouse. (This file is located in “/etc/clickhouse-server” by default.)
<http_server_default_response><![CDATA[<html ng-app="SMI2"> <head><base href="http://ui.tabix.io/"></head> <body><div ui-view="" class="content-ui"></div><script src="http://loader.tabix.io/master.js"> </script></body></html>]]> </http_server_default_response>
Usage
To use Tabix, you just need a Chrome web browser(version 55+) that is able to connect to the ClickHouse Server.
If you don’t change http_port in the “config.xml” file or if there is no port mapping at the network level, ClickHouse’s default HTTP port is 8123. You can access the Tabix login screen from the HTTP address below.
http://<hostname>:<port> --> hostname:ClickHouse Server's hostname or IP. --> port: 8123 by default and not modified.
On the login page, just because I would like to connect to embedded Tabix, “DIRECT CH” should be chosen. Afterward, “host“, “port“, “Login” and “Password” information should be filled in with respect to the ClickHouse server.
After successfully login in to the database, firstly “SQL” screen welcomes us.
On the left-hand side, you can choose databases(or schemas), tables, and views as well. Also, on the right-hand side, you can write your own SQL sentence and run them.
Examples
In that case, I would like to show you that Tabix is not only the user interface but also a BI tool for ClickHouse. For that reason, I downloaded and installed the sample “Cell Towers” dataset, as explained here.
After installing the “Cell Towers” dataset, let’s have a look at the magic in the query editor. As you see from the query below, at the end of the query, I added the “DRAW_BAR{}” keywords just because of the drawbar chart of the given query.
select
radio,
count(*) as count
FROM
default.cell_towers
GROUP BY
radio DRAW_BAR { }
Now I can access the result set of the query above both in table and bar chart form. Here is the bar chart output of the query.
At this time, I would like to show some of the queries with different types of charts. The following query draws a calendar with the last 2 years’ average area data.
select
dt,
avg(area) as area
from
(
select
toDate(created) as dt,
area
from
default.cell_towers
)
where
dt > now() -(60 * 60 * 24 * 365 * 2)
group by
dt
order by
dt desc DRAW_CALENDAR { }
DRAW_GMAPS (Google Maps) is another interesting draw type. With the help of this graph, you can visualize any kind of longitude and latitude information.
SELECT
count(*) as CellTowers,
'Cell Towers in Turkey' as mytitle,
lon as xxlatitude,
lat as xxlongitude
FROM
default.cell_towers
where
--'TR': ('Turkey', (26.0433512713, 35.8215347357, 44.7939896991, 42.1414848903)),
(
xxlongitude between 26.0433512713
and 44.7939896991
)
and (
xxlatitude between 35.8215347357
and 42.1414848903
)
GROUP BY
lon,
lat
limit
10 DRAW_GMAPS
{
title :'mytitle',
count :'CellTowers',
longitude :'xxlongitude',
latitude :'xxlatitude'
}
At last, I would like to show you the chart drawing example. It is basically a line chart with one or more y-axis.
select
dt,
radio,
count(*) as radioCount
from
(
select
toDate(created) dt,
radio
FROM
default.cell_towers
where
mcc = 310
and dt > now() -(60 * 60 * 24 * 365 * 2)
)
GROUP BY
dt,
radio
order by
1 DRAW_CHART { }
Tabix has another branch for observing queries and system metrics called “Metrics & Processes“. With the help of this part, you can see running queries(even kill them) in real-time. It also draws charts from the system. metrics.
In this document, I would like to briefly explain the installation and usage of Tabix in ClickHouse. As explained above, Tabix is not only a user interface but also a lightweight graph and monitoring solution for ClickHouse.
For further detail about the usage of Tabix, please visit here.