{ "version": "https:\/\/jsonfeed.org\/version\/1", "title": "LEFT JOIN: blog on analytics, visualisation & data science, posts tagged: clickhouse", "home_page_url": "https:\/\/en.leftjoin.ru\/tags\/clickhouse\/", "feed_url": "https:\/\/en.leftjoin.ru\/tags\/clickhouse\/json\/", "icon": "https:\/\/en.leftjoin.ru\/user\/userpic@2x.jpg", "author": { "name": "Nikolay Valiotti", "url": "https:\/\/en.leftjoin.ru\/", "avatar": "https:\/\/en.leftjoin.ru\/user\/userpic@2x.jpg" }, "items": [ { "id": "65", "url": "https:\/\/en.leftjoin.ru\/all\/altinity-clickhouse-training-101\/", "title": "Clickhouse Training 101 by Altinity", "content_html": "

Just recently I have completed a Clickhouse Training by Altinity (101 Series Training). <\/a> For those who are just getting to know Clickhouse, Altinity offers free basic training: Data Warehouse Basics<\/a>. I recommend starting with it if you are planning to dive into learning.<\/p>\n

\n\"\"\n
Certification by Altinity<\/div>\n<\/div>\n

I would like to share my experience on the training as well as the training notes<\/a>.
\nThe training costs $500 and lasts 4 days for 2 hours. It is carried out in the evenings Moscow time (starting from 19:00 GMT +3).<\/p>\n

Session # 1.<\/h2>\n

The first day mostly revises everything covered in Data Warehouse Basics, but it has several new ideas on how to get useful information on queries from system tables.<\/p>\n

For example, this query will show which commands are running and their status.<\/p>\n

SELECT command, is_done\r\nFROM system.mutations\r\nWHERE table = 'ontime'<\/code><\/pre>
Besides, for me it was useful to learn about column compression with the use of codecs:<\/p>\n
ALTER TABLE ontime\r\n MODIFY COLUMN TailNum LowCardinality(String) CODEC(ZSTD(1))<\/code><\/pre>
\n\"\"\n<\/div>\n
For those who are just starting with Clickhouse, the first day will be super useful as it will help in understanding table engines and syntax for their creation, partitions, inserting data (for example directly from S3).<\/p>\n
INSERT INTO sdata\r\nSELECT * FROM s3(\r\n 'https:\/\/s3.us-east-1.amazonaws.com\/d1-altinity\/data\/sdata*.csv.gz',\r\n 'aws_access_key_id',\r\n 'aws_secret_access_key',\r\n 'Parquet',\r\n 'DevId Int32, Type String, MDate Date, MDatetime\r\nDateTime, Value Float64')<\/code><\/pre>
Session # 2.<\/h2>\n
I found the second day the most intense and useful as within this session Robert from Altinity talks about aggregate functions and materialized views ( detailed scheme for the creation of materialized views<\/a> ) in Clickhouse in more detail.<\/p>\n
\n\"\"\n<\/div>\n
It was super useful for me to learn about index types in Clickhouse.<\/p>\n
\n\"\"\n<\/div>\n
Session # 3.<\/h2>\n
During the third day, colleagues share their knowledge on how to work with Kafka and JSON objects stored in the tables.
\nIt was interesting to find out that working with arrays in Clickhouse is very similar to arrays in Python:<\/p>\n
WITH [1, 2, 4] AS array\r\nSELECT\r\n array[1] AS First,\r\n array[2] AS Second,\r\n array[3] AS Third,\r\n array[-1] AS Last,\r\n length(array) AS Length<\/code><\/pre>
When working with arrays, there is a great feature called ARRAY JOIN which “unrolls” arrays to rows.<\/p>\n
\n\"\"\n<\/div>\n
Clickhouse allows you to efficiently interact with JSON objects stored in a table:<\/p>\n
-- Get a JSON string value\r\nSELECT JSONExtractString(row, 'request') AS request\r\nFROM log_row LIMIT 3\r\n-- Get a JSON numeric value\r\nSELECT JSONExtractInt(row, 'status') AS status\r\nFROM log_row LIMIT 3<\/code><\/pre>
This piece of code is an example of how to extract the elements of the JSON array “request” and “status” separately.<\/p>\n
ALTER TABLE log_row\r\n ADD COLUMN\r\nstatus Int16 DEFAULT\r\n JSONExtractInt(row, 'status')\r\nALTER TABLE log_row\r\nUPDATE status = status WHERE 1 = 1<\/code><\/pre>
Session # 4.<\/h2>\n
The most difficult topic from my point of view was saved for the last day – building sharding and replication patterns <\/a> and building queries on distributed Clickhouse servers.<\/p>\n
\n
\n\"\"\n\"\"\n<\/div>\n<\/div>\n
Special respect to Altinity for an excellent collection of labs during the training.<\/p>\n
Links<\/b>:<\/p>\n