Connecting to Altinity.Cloud with the ClickHouse Web UI

Did you know that ClickHouse has a built-in Web UI? The Web UI is a simple but extremely useful feature Alexey Milovidov added to ClickHouse in October 2020. In this short article I’m going to show you how it works and how to connect to Altinity.Cloud, our managed ClickHouse service in Amazon.

Introducing the ClickHouse Web UI

The ClickHouse Web UI is available in ClickHouse builds starting in version 20.11.  It runs in any ClickHouse server where the HTTP Interface is enabled. If you have a 20.11 or greater ClickHouse server installed on your laptop, just open the following URL: 

http://localhost:8123/play

You should see a browser pane like the picture below. The first box on the left shows the ClickHouse server URL. The second box is the user name, here default.  The third box is the password and is blank.  

If you are using the default user in a fresh server installation, you can just start typing in queries.  Otherwise you’ll first need to provide a user and password. In the following example, I filled in user demo and a password before trying a query.

This is a bit boring, so let’s run a non-trivial query to look at table system.parts and check compression levels on tables in our installation of ClickHouse.  Here is the query for you to copy. 

SELECT database, table, count(*) AS parts,
    uniq(partition) AS partitions,
    sum(marks) AS marks,
    sum(rows) AS rows,
    formatReadableSize(sum(data_compressed_bytes)) AS compressed,
    formatReadableSize(sum(data_uncompressed_bytes)) AS uncompressed,
    round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100., 2) AS percentage
FROM system.parts WHERE active='1'
GROUP BY database, table ORDER BY database, table
LIMIT 50

Paste it into the main text box and press Run. You should see results like the following. 

As you can see the results window scrolls across and down.  You can view results for queries quite easily.  

The ClickHouse Web UI is new and evolving quickly. If you are using ClickHouse version 20.1 here are a couple of improvements that you may want to try out. 

1. You can specify the user in the URL.  To fill in ‘demo’ automatically, try a URL like this: http://localhost:8123/play?user=demo. You still have to type the password. 
2. The Web UI implements a simple history function by appending the base64 query to the URL.  You use the Back button to retrieve older queries.  This may be replaced by an implementation that uses local storage in the browser. 

There are also a few limitations. 

1. There is no way to set the default database.  This means you’ll need to use fully qualified table names.  
2. There is no way to add additional URL parameters. Parameters other than ‘user’ may be ignored or may cause the queries to fail. 
3. There is no progress indicator. 
4. Results are limited to 10,000 rows and 1M bytes to avoid overloading the browser.  

You can expect more improvements in short order, so try out new ClickHouse builds and submit issues and/or PRs if you have ideas for improvements. Meanwhile what’s there already is quite useful. 

Connecting to Altinity.Cloud

Altinity.Cloud instances open an encrypted HTTP interface on port 8443.  To test the ClickHouse Web UI you don’t need to have an Atinity.Cloud account–you can just connect to our public ClickHouse endpoint, which offers read-only access.  Open up the following URL In your browser and fill in user demo, password demo once the screen appears. 

https://github.demo.trial.altinity.cloud:8443/play

If you run a ‘show tables’ command you’ll see the following tables.

This shows three of our favorites tables: Alexey Milovidov’s dataset of all Github events, US airline on time performance data, and New York taxi and limousine trip data.  

The github_events table is over 3 billion rows, so it’s a nice target for checking performance.  Let’s try one of the first queries in Alexey’s write-up.  Here is the query so you can copy it easily. 

SELECT repo_name, count() AS stars 
FROM github_events WHERE event_type = 'WatchEvent' 
GROUP BY repo_name ORDER BY stars DESC 
LIMIT 10

Paste it in and you should see results like the following. 

Feel free to try out more Github queries.  Alexey’s Github article introduces many interesting questions that ClickHouse can answer quickly. Try out queries on airline and taxi data as well.  For example, you can now try out some of the many queries on these tables that we have demonstrated in Altinity webinars and blog articles.  For example, many of our “More Secrets of ClickHouse Query Performance webinar queries use the airline ontime data.

Altinity.Cloud is not the only place you can find public ClickHouse datasets. The Github data is also available in Yandex.Cloud at the following URL, which will log you in with the predefined user play. (There is no password.)

https://gh-api.clickhouse.tech/play?user=play

We have made these datasets public so that community users can try out ClickHouse easily. More public endpoints will appear over time, so watch this blog as well as the Example Datasets page in the ClickHouse community docs. 

What’s next?

The public Altinity.Cloud endpoint is read-only and rate-limited. If you want to add your own data, open up a free Altinity.Cloud Test Drive and give it a spin. Also, check out the Altinity.Cloud technical documentation for more information on specific capabilities. 

In the meantime, enjoy the ClickHouse Web UI. It’s a really handy feature for quick access to ClickHouse from your browser. Speaking of browsers and queries, we have a bunch of articles on web based BI tools coming in 2021 on the Altinity Blog.  Keep an eye out for them!

p.s. I would like to thank Alexey Milovidov for kind review and suggestions.