Clickhouse SQL IDE

ClickHouse is an open-source column-oriented DBMS for online analytical processing. qStudio is a free SQL IDE, it allows running SQL scripts, easy browsing of tables, charting and exporting of results. It works on every operating system, with every database including Clickhouse.

Start Clickhouse
Connecting to Clickhouse
Querying Clickhouse

Setting up a Clickhouse connection in qStudio

Start Clickhouse

Clickhouse can either be ran locally or you can use Clickhouse Cloud. To keep this tutorial simple we opted for cloud. Simply start a small service in your chosen region. The connection details should be provided, similar to shown:

Connecting to a Server

Clickhouse was in the past mostly provided by Yandex (Russian Google), increasingly it is supplied by Clickhouse.com. qStudio includes both drivers, for this demo make sure to use the Clickhouse.com driver.

When you first open qStudio click on the menu options Server->Add Server or on the add server button on the toolbar.
Then set the details:

Server Type: Clickhouse.com
Note for Host you MUST include https://
Host: https://tcez9f65x4.eu-west-1.aws.clickhouse.cloud
Port: 8443
Username: default
Password: XXXXXXXXXXX
Click Add

Sending Queries

CREATE DATABASE IF NOT EXISTS helloworld;

DROP TABLE IF EXISTS helloworld.my_first_table;
CREATE TABLE helloworld.my_first_table (
        user_id UInt32,
        message String,
        timestamp DateTime,
        metric Float32
    ) ENGINE = MergeTree() PRIMARY KEY (user_id, timestamp);
    
INSERT INTO helloworld.my_first_table (user_id, message, timestamp, metric) VALUES
    (101, 'Hello, ClickHouse!',                                 now(),       -1.0    ),
    (102, 'Insert a lot of rows per batch',                     yesterday(), 1.41421 ),
    (102, 'Sort your data based on your commonly-used queries', today(),     2.718   ),
    (101, 'Granules are the smallest chunks of data read',      now() + 5,   3.14159 );
SELECT * FROM helloworld.my_first_table;

You can run queries by highlighting selected code and pressing Ctrl + e to run that code. See Keyboard Shortcuts.

Command	Shortcut
Run highlighted text	Ctrl + e
Run Line	Ctrl + Enter

This will update your various results panels, result contains your table (excel export etc. supported), charts are generated based on your data and the console summarizes your recent commands

Clickhouse Play Database

The easiest way to play with Clickhouse is to use the Play Database.

Server Type: Clickhouse.com
Click URL
URL: jdbc:ch:https://play.clickhouse.com:443
Username: explorer
Click Add

SELECT * FROM covid WHERE date>'2020-03-11' AND location_key='NL_NH_0417' ORDER BY date LIMIT 2000;

select file_time,position from github_events LIMIT 10

select * from blogs.forex_usd LIMIT 10;

Well Done!

You have now got qStudio querying your database successfully.

We want to make qStudio the best SQL analysis tool. If you have any problems or want additional features, please post on our github repo.