![202301-newsletter.png](/uploads/202301_newsletter_be26926eb6.png)

Happy New Year! Like many of you, we at ClickHouse took some time off and are using January to catch up and plan. To help you think about what you can do with ClickHouse in 2023, have a look at our joint webinars with dbt Labs and Grafana this month, the new features we released in version 22.12 just before the holidays, how to use encryption in ClickHouse and a reading list to get you started into the year.

By the way, if you’re reading this on our website, did you know you can receive every monthly newsletter as an email in your inbox? <a href="https://discover.clickhouse.com/newsletter.html?utm_source=clickhouse&amp;utm_medium=email&amp;utm_campaign=newsletter-202301" target="_blank">Sign up here</a>.

## Upcoming Events

Mark your calendars for the following virtual events:

**Webinar: Using dbt to Transform Data in ClickHouse**  
**When?** Tuesday, January 24 @ 2 pm GMT / 3 pm CET  
**How do I join?** <a href="https://clickhouse.com/company/events/2023-01-24-dbt-clickhouse-webinar?utm_source=clickhouse&amp;utm_medium=email&amp;utm_campaign=newsletter-202301" target="_blank">Register here</a>  
**Speakers:** dbt Labs, Datricks, ClickHouse

**ClickHouse v23.01 Release Webinar**  
**When?** Wednesday, January 25 @ 9 am PST / 6 pm CET  
**How do I join?** <a href="https://clickhouse.com/company/events/v23-01-release-webinar?utm_source=clickhouse&amp;utm_medium=email&amp;utm_campaign=newsletter-202301" target="_blank">Register here</a>

**Webinar: Real-time SQL analytics at scale: A story of open-source GitHub activity using ClickHouse and Grafana**  
**When?** Thursday, January 26 @ 6 pm CET / 9 am PST  
**How do I join?** <a href="https://grafana.com/go/webinar/clickhouse-and-grafana/?src=partner-clickhouse-email" target="_blank">Register here</a>  
**Speakers:** Grafana, ClickHouse

**Webinar: ClickHouse Cloud Onboarding**  
**When?** Thursday, January 26 @ 1 pm PST  
**How do I join?** <a href="https://clickhouse.com/company/events/2023-01-26-clickhouse-onboarding-workshop?utm_source=clickhouse&amp;utm_medium=email&amp;utm_campaign=newsletter-202301" target="_blank">Register here</a>

## ClickHouse v22.12
There was a lot of goodness in our December release. Have a look at the <a href="https://clickhouse.com/blog/clickhouse-release-22-12" target="_blank">blog post</a> as well:

1. <strong><a href="https://github.com/ClickHouse/ClickHouse/pull/38191" target="_blank">Grace hash join</a></strong> A new join type that splits the to-be-joined tables into buckets and joins those buckets one by one, vastly reducing memory usage (at the cost of slower speed). Give it a try, and also take a look at all the other join algorithms ClickHouse supports <a href="https://clickhouse.com/docs/en/operations/settings/settings#settings-join_algorithm" target="_blank">here</a>.
2. <strong><a href="https://github.com/ClickHouse/ClickHouse/pull/43304" target="_blank">Async insert deduplication</a></strong> Asynchronous inserts are now deduplicated, so if you send the same data twice ClickHouse will only accept one.
3. <strong><a href="https://github.com/ClickHouse/ClickHouse/pull/43719" target="_blank">Password complexity</a></strong> ClickHouse can now enforce password complexity rules for user passwords - how long a password has to be and whether it has to contain any specific character types. If you’re creating users in ClickHouse, you should probably configure this, so people don’t just set “asdf”.
4. <strong><a href="https://github.com/ClickHouse/ClickHouse/issues/37631" target="_blank">GROUP BY ALL</a></strong> Grouping by ALL will group by every column in a SELECT statement that is not part of an aggregation. A handy shorthand, so you don’t have to specify them manually.
5. <strong><a href="https://github.com/ClickHouse/ClickHouse/pull/43925" target="_blank">Numbers with underscores</a></strong> Sometimes it’s just easier to specify `1_000_000` rather than `1000000` (if you’re like me, you’re pausing shortly after typing the first three zeros to make sure you’re getting it right). Though in this particular case, you can also use `1e6`.
6. <strong><a href="https://github.com/ClickHouse/ClickHouse/pull/43260" target="_blank">Async reading from MergeTree</a></strong> On slower disks, especially remote stores like object storage (S3, etc.), reading data can take some time. Therefore it makes sense to send many parallel read requests to reduce the overall time it takes to gather all data for a query. New settings allow specifying how many parallel requests ClickHouse will make, allowing you to speed up some queries significantly, especially on smaller machines. This is especially useful when using ClickHouse Cloud, which is based on object storage.

Take a look at the <a href="https://presentations.clickhouse.com/release_22.12/" target="_blank">release webinar slides</a> and the <a href="https://youtu.be/sREupr6uc2k" target="_blank">recording</a>, and please upgrade unless you want to stay on a <a href="https://clickhouse.com/docs/en/faq/operations/production/#how-to-choose-between-clickhouse-releases" target="_blank">Long Term Support (LTS) release</a>. If you are using <a href="https://clickhouse.com/cloud" target="_blank">ClickHouse Cloud</a>, you are already using the new release.

## Query of the Month: Better safe than sorry

In this section, we like to explore lesser-known but potentially very useful features in ClickHouse. One such feature is built-in encryption and decryption.
Let’s say you have a customer table that contains potentially sensitive information such as customer addresses:
```
CREATE TABLE customers
(
   id String,
   address String
)
ENGINE = MergeTree
ORDER BY id
```
Instead of storing the addresses in plaintext, you can encrypt them like this:
```
INSERT INTO customers
SELECT 1, encrypt('aes-128-cbc', '35 Highcombe, London,SE7 7HT', 'donotusethisword')
```
The resulting data will be unreadable:
```
SELECT * FROM customers FORMAT Values


('1','��kg`�\0�\0"��́Xf����ĭ\n0�Ą.\f�')
```
Unless you have the correct key:
```
SELECT id, decrypt('aes-128-cbc', address, 'donotusethisword') 
FROM customers FORMAT Values


('1','35 Highcombe, London,SE7 7HT')
```
Ah, but I hear you say that if certain users shouldn’t be able to see some columns, it’s better to `REVOKE` their access to those columns. You’re certainly right, but maybe they should have access to some values but not others, depending on whether they have access to the key.
By default, if decryption fails, ClickHouse will throw an exception. If this is sometimes expected (when the user has a valid key for some values but not others), you can use `tryDecrypt` instead, which will return `NULL` for the values that cannot be decrypted and allow the query to continue.
```
INSERT INTO customers
SELECT 2, encrypt('aes-128-cbc', '31 Richmond Court, Ellesmere Port,CH65 9EA', 'usesomeotherword')


SELECT id, tryDecrypt('aes-128-cbc', address, 'usesomeotherword') address
FROM customers WHERE address IS NOT NULL


('2','31 Richmond Court, Ellesmere Port,CH65 9EA')
```
Native database encryption is a straightforward and low-cost way to improve data security. It is not as secure as using an external key management system, but those are not always available or easy to set up.

## New Year Reading List

Some reading material to start the year with.

1. [ClickHouse Release 22.12](https://clickhouse.com/blog/clickhouse-release-22-12) The release blog post for our awesome pre-holiday Christmas release. If you’re using joins, you should look into the new grace hash join!
2. [Announcing a new official ClickHouse Kafka Connector](https://clickhouse.com/blog/kafka-connect-connector-clickhouse-with-exactly-once) The official Kafka Connect Sink for ClickHouse with exactly-once delivery semantics is now in beta. Give it a try!
3. [Optimizing ClickHouse with Schemas and Codecs](https://clickhouse.com/blog/optimize-clickhouse-codecs-compression-schema) With a bit of work on your table definitions, you can often significantly reduce storage and speed up queries at the same time. Have a look at how we did it here.
4. [Super charging your ClickHouse queries](https://clickhouse.com/blog/clickhouse-faster-queries-with-projections-and-primary-indexes) Another way to increase query speed is to use projections and specify good sorting keys (primary indexes). Walk through an example with us here.
5. [System Tables and a window into the internals of ClickHouse](https://clickhouse.com/blog/clickhouse-debugging-issues-with-system-tables) Your first port of call when monitoring ClickHouse should be the system tables. There are a lot of them, and they give you detailed information about almost everything ClickHouse is doing. Read our primer here.
6. Essential monitoring queries: [Part 1 (INSERT)](https://clickhouse.com/blog/monitoring-troubleshooting-insert-queries-clickhouse) and [Part 2 (SELECT)](https://clickhouse.com/blog/monitoring-troubleshooting-select-queries-clickhouse). These articles will help you get started monitoring the two most important query types, inserts and selects.
7. [Building an Observability Solution with ClickHouse in 2023 - Part 1 - Logs](https://clickhouse.com/blog/storing-log-data-in-clickhouse-fluent-bit-vector-open-telemetry) More and more companies are choosing ClickHouse to store their observability data. In this first part of a longer series, we show you how to use ClickHouse for logs.
8. [Extracting, converting, and querying data in local files using clickhouse-local](https://clickhouse.com/blog/extracting-converting-querying-local-files-with-sql-clickhouse-local) Don’t want to run a database but still want to use ClickHouse? Use headless ClickHouse (clickhouse-local)! It’s one of the best tools out there to query data anywhere, including in local files. We think every data engineer should have it installed.
9. [Generating random data in ClickHouse](https://clickhouse.com/blog/generating-random-test-distribution-data-for-clickhouse) Ever needed to generate some random data? We had to many times, so ClickHouse has a lot of functionality built in to help with that. Have a read and outsource your data generation to ClickHouse.
10. [HIFI’s migration from BigQuery to ClickHouse](https://clickhouse.com/blog/hifis-migration-from-bigquery-to-clickhouse) HIFI is providing financial analytics to music creators worldwide. Initially using BigQuery, they were unhappy with its per-query pricing, so they switched to ClickHouse.

Thanks for reading, and we’ll see you next month.  
The ClickHouse Team






ClickHouse Newsletter January 2023: Better Safe Than Sorry

large_202301-newsletter.png

medium_202301-newsletter.png

small_202301-newsletter.png

thumbnail_202301-newsletter.png

202301-newsletter.png

![projector.jpg](/uploads/projector_0f1ea9ef72.jpg)

We’re all excited to share that the official managed service for ClickHouse, ClickHouse Cloud, is now generally available and production ready. Give it a spin if you’re looking to focus on using ClickHouse rather than managing it. As always, we are here to help you!

Keep reading to read about the new features in ClickHouse Cloud, upcoming events in the coming days and weeks, what is new in ClickHouse 22.11 and how projections can help you speed up queries without changing your queries.

And take a look at our holiday reading list. If you read only the most technical articles, we have you covered with [this](https://clickhouse.com/blog/clickhouse-just-in-time-compiler-jit) 25-page blog post about JIT compilation in ClickHouse!

By the way, if you’re reading this on our website, did you know you can receive every monthly newsletter as an email in your inbox? Sign up [here](https://discover.clickhouse.com/newsletter.html).

##ClickHouse Cloud is now Generally Available

As usual, we like to deliver things quickly. So here it is, the GA release of our ClickHouse Cloud managed service, just in time for Christmas. We’ve added a number of features including SOC 2 Type II compliance, uptime status and SLAs, availability in the AWS Marketplace, a lower priced development tier, an interactive SQL console, free [ClickHouse Academy](https://clickhouse.com/learn) training and we’ve enabled the PostgreSQL and MySQL engines, dictionaries and SQL UDFs.

If you’re just getting started with ClickHouse or need something to host your non-production development, staging and QA take a look at our new and affordable Development tier where prices can be as low as $50 per month.

Take a look at our [pricing](https://clickhouse.com/pricing) here, read the [release blog post](https://clickhouse.com/blog/clickhouse-cloud-generally-available) and sign up for a free trial at [https://clickhouse.cloud/signUp](https://clickhouse.cloud/signUp).

**Upcoming Events**

Mark your calendars for the following virtual and in-person events:

**6-hour ClickHouse Workshop (2 x 3 hours)**
<br />**What?** If you’re new to ClickHouse, this is for you. In two 3-hour sessions on two days we’ll take you through all the basics, from modeling data to query optimization
<br/> **When?**
1. Register [here](https://clickhouse.com/company/events/clickhouse-workshop) for Wednesday, Dec 14 @ 8 am PST / 5 pm CET and Thursday, Dec 15 @ 8 am PST / 5 pm CET.
2. Register [here](https://clickhouse.com/company/events/2023-01-04-clickhouse-workshop) for Wednesday, Jan 4 @ 9 am PST and Thursday, Jan 5 @ 9 am PST.
3. Register [here](https://clickhouse.com/company/events/2023-01-18-clickhouse-workshop) for Wednesday, Jan 18 @ 10 am GMT and Thursday, Jan 19 @ 10 am GMT

**ClickHouse v22.12 Release Webinar** 
 * **_When?_** Thursday, December 15 @ 9 am PST / 6 pm CET 
 * **_How do I join?_** Register [here](https://clickhouse.com/company/events/v22-12-release-webinar).

**Meetup Tel Aviv**
 * **When?** Monday, January 16 @ 6 pm (register [here](https://www.meetup.com/clickhouse-tel-aviv-user-group/events/289599423/))
 * **_Speakers:_** ServiceNow, Contentsquare, CHEQ, ClickHouse

**Meetup Seattle**
 * **When?** Wednesday, January 18 @ 11 am (register [here](https://www.meetup.com/clickhouse-seattle-user-group/events/290310025/))
 * **_Speakers:_** To be announced

##ClickHouse v22.11

Lots of new features in our regular monthly release - this month accompanied by a [dedicated blog post](https://clickhouse.com/blog/clickhouse-release-22-11).

1. **[Composite time intervals](https://github.com/ClickHouse/ClickHouse/pull/42195)** You can now combine different intervals in one go, for example `INTERVAL '1 HOUR 1 MINUTE 1 SECOND'` or `SELECT '2023-01-30'::Date + (INTERVAL 1 MONTH - INTERVAL 2 DAY)`. Can you guess the result of the second statement?
2. **[Recursive globs in paths](https://github.com/ClickHouse/ClickHouse/pull/42376)** Use `**` for recursive directory traversal in a filesystem or in S3. For example, to read all Parquet files in an S3 bucket use `FROM s3('https://mybucket.s3.us-east-1.amazonaws.com/**/*.parquet')`
3. **[Retry keeper during inserts](https://github.com/ClickHouse/ClickHouse/pull/42607)** Long-running inserts can sometimes fail when the keeper connection to ClickHouse Keeper or ZooKeeper is temporarily lost. We have a new setting `insert_keeper_max_retries` that enables retries for keeper operations to prevent this from failing the entire insert query.
4. **Hudi and DeltaLake** [table engines](https://github.com/ClickHouse/ClickHouse/pull/41054) and [table functions](https://github.com/ClickHouse/ClickHouse/pull/43080) to query and ingest data from “data lakes”.
5. **[Readonly S3 disks](https://github.com/ClickHouse/ClickHouse/pull/42628)** You can attach a MergeTree table from S3 directly in readonly mode. This is an experimental feature.
6. **[Object (JSON) inside types](https://github.com/ClickHouse/ClickHouse/pull/36969)** The experimental Object (also known as JSON) type can now be wrapped in other types, e.g. `Array(JSON)`.

Take a look at the [release webinar slides](https://presentations.clickhouse.com/release_22.11/) and the [recording](https://youtu.be/LR-fckOOaFo), and please upgrade unless you want to stay on a Long Term Support (LTS) release.

## Query of the Month: Project all your troubles away

Today we’re going to take a look at a very useful ClickHouse feature that you’re probably not using enough: Projections.

Projections are a more convenient alternative to materialized views when all the data is from the same table. Materialized view queries can contain subqueries or dictionary lookups so the destination table will contain data from multiple sources. Like a materialized view, a projection is defined as a query, but the result of this query is saved in the same table (but hidden).

When ClickHouse receives a query for data in a table with a projection, it will first analyze whether it should use the original data or the projection data to answer the query. Whichever requires less data to be read and processed will be chosen.

As an example, consider a simple table containing metric names and values:
```
CREATE TABLE metrics
(
	timestamp DateTime64 CODEC(Delta, Default),
	name LowCardinality(String),
	value Float64
) ENGINE = MergeTree()
ORDER BY (name, timestamp)
```

Take a note of two optimizations to the data types:

1. Instead of storing the `timestamp` as is we use delta encoding where only the difference between one value and the next are stored. We expect most values to be quite similar (same year, month, day, hour, etc.) so this will reduce the storage required for this column by a lot.
2. Similarly, instead of storing the names of our metrics as they are, we use the `LowCardinality` dictionary encoding to store a map of names and pointers to where they occur in the data. We only expect a limited number of unique metrics so this will also reduce storage by a lot.

Notice also how we order the data by metric name first and then by timestamp. This will make queries that look for individual metrics very fast. The following query finds the latest temperature reading:

```
SELECT name, argMax(value, timestamp) val_max FROM metrics
WHERE symbol = 'TEMP' GROUP BY symbol
 
┌─name───┬─val_max─┐
│ TEMP   │     179 │
└────────┴─────────┘
 
1 row in set. Elapsed: 0.009 sec. Processed 73.73 thousand rows, 1.38 MB (8.01 million rows/s., 150.32 MB/s.)
```
Requesting the latest values for all metrics is 100x slower:
```
SELECT name, argMax(value, timestamp) val_max
FROM metrics GROUP BY symbol
255 rows in set. Elapsed: 0.902 sec. Processed 220.01 million rows, 6.08 GB (259.38 million rows/s., 6.74 GB/s.)
```
To speed up this query we can add a projection that always stores the latest value for a metric
```
ALTER TABLE metrics ADD PROJECTION latest_values
(SELECT argMax(value, timestamp) GROUP BY name)
```
And to make the data available immediately we can materialize the projection like this:
```
ALTER TABLE metrics MATERIALIZE PROJECTION latest_values
SETTINGS mutations_sync = 1
```
Now let’s run the same query again:
```
SELECT name, argMax(value, timestamp) val_max
FROM metrics GROUP BY symbol
255 rows in set. Elapsed: 0.007 sec. Processed 255 rows, 156.13 KB (1.42 million rows/s., 84.33 MB/s.)
```
Getting the latest value for all metrics is faster than getting the latest value for a single metric was! All we had to do was add a projection. No need to change our query or our table structure even one bit.

Give projections a try! We bet there are some that will be useful on your data.

## Holiday Reading List

Some reading material for you:



1. [ClickHouse Cloud is now Generally Available](https://clickhouse.com/blog/clickhouse-cloud-generally-available) A very exciting time for ClickHouse users, the official ClickHouse managed service is generally available and fully production ready. Read all about it in this post. 

2. [User-defined functions in ClickHouse Cloud](https://clickhouse.com/blog/user-defined-functions-clickhouse-udfs) A refresher on UDFs from our team now that they are available in ClickHouse Cloud. 

3. [Using dictionaries to accelerate queries](https://clickhouse.com/blog/faster-queries-dictionaries-clickhouse) An in-depth explainer with examples of how to use dictionaries to speed up your queries in ClickHouse. 

4. [JIT in ClickHouse](https://clickhouse.com/blog/clickhouse-just-in-time-compiler-jit) One of the most technical posts we’ve read in a while, our own Maksim Kita dives deeply into JIT compilation and how it’s implemented in ClickHouse. 

5. [Window and array functions for Git commit sequences](https://clickhouse.com/blog/clickhouse-window-array-functions-git-commits) Some really good visual explainers of window and array functions in this blog post on analyzing Git commits. 

6. [How We Use ClickHouse to Analyze Trends Across Millions of Builds](https://www.buildbuddy.io/blog/clickhouse-build-trends/) BuildBuddy moved their Trends page from MySQL to ClickHouse - one query improved from taking 24 minutes  (!) to 0.317s in ClickHouse! 

7. [Using partitions in Clickhouse](https://medium.com/datadenys/using-partitions-in-clickhouse-3ea0decb89c4) A quick primer on using partitions and how they can speed up queries. Check out the other posts [here](https://medium.com/datadenys) as well!

Thanks for reading, and we’ll see you next year.

The ClickHouse Team






ClickHouse Newsletter December 2022: Project all your troubles away

large_projector.jpg

medium_projector.jpg

small_projector.jpg

thumbnail_projector.jpg

projector.jpg

Did you know ClickHouse is the world’s fastest tool for querying JSON files? [Here](https://clickhouse.com/blog/worlds-fastest-json-querying-tool-clickhouse-local) is the proof! 

Also, have you ever wondered what the ClickHouse binary would look like as a picture? Well, we have. It’s big, it’s beautiful - and gray? See it [here](https://youtu.be/sz9SES5-mdc?t=2319).

This month, read on for simplified pricing of ClickHouse Cloud, an entire “tour calendar” of upcoming events, new features in ClickHouse 22.10, some modern dataset alchemy, and your regular dose of light reading material.

By the way, if you’re reading this on our website, did you know you can receive every monthly newsletter as an email in your inbox? Sign up [here](https://discover.clickhouse.com/newsletter.html).

## ClickHouse Cloud - Simplified Pricing
Good news, we have simplified the pricing of our ClickHouse Cloud service. Now we only charge for two things: compute and storage. Head over to the [pricing page](https://clickhouse.com/pricing) to see the details. And for the next few days - until Nov 15, 2022 - we offer an additional $500 in credits.

On October 27, we broadcast our official launch webinar. We discussed why and how we built ClickHouse Cloud, showed a demo and answered your questions. Watch the recording [here](https://clickhouse.com/company/events/cloud-beta).

## Tour Calendar (aka “Upcoming Events”)
The next few weeks are going to be full of ClickHouse events. We hope to meet many of you in person, either virtually or in person. Take a look at where you can find other Clickhouse users (and us):

**ClickHouse v22.11 Release Webinar**  
 * **_When?_** Thursday, November 17 @ 9 am PST / 6 pm CEST
 * **_How do I join?_** Register [here](https://clickhouse.com/company/events/v22-11-release-webinar).

**ClickHouse Cloud Onboarding Workshops**  
 * **_What?_** Hear from our team on how to take the first steps with ClickHouse Cloud. We’ll discuss the architecture, data modeling, views, data ingest and performance tuning - with time for Q&A at the end.
 * **_When?_** Thursday, November 10 @ 10 am PST (AMER)

**Meetups** We’ll be holding a series of meetups with talks from ClickHouse users and experts. Come prepared with your curiosity and questions. Bring some colleagues and friends as well!

**Meetup Stockholm**
 * **_When?_** Thursday, December 1 @ 6 pm (register [here](https://www.meetup.com/clickhouse-stockholm-user-group/events/289492084/))
 * **_Speakers:_** RELEX, ClickHouse 
 
**Meetup Berlin**
 * **_When?_** Monday, December 5 @ 6 pm (register [here](https://www.meetup.com/clickhouse-berlin-user-group/events/289311596/))
 * **_Speakers:_** Deutsche Bank, BENOCS, ClickHouse
 
**Meetup New York City**
 * **_When?_** Tuesday, December 6 @ 6 pm (register [here](https://www.meetup.com/clickhouse-new-york-user-group/events/289403909/))
 * **_Speakers:_** Bloomberg, Disney, Prequel, Rokt, ClickHouse 

**Meetup Tel Aviv**
 * **_When?_** Monday, January 16 @ 6 pm (register [here](https://www.meetup.com/clickhouse-tel-aviv-user-group/events/289599423/))
 * **_Speakers:_** To be announced

## ClickHouse v22.10
This past month, ClickHouse received its 100,000th commit! You can look for it with `git log`, but first, let’s take a look at the new features in the October release:

1. [Backup to S3](https://clickhouse.com/docs/en/manage/backups/#configuring-backuprestore-to-use-an-s3-endpoint) ClickHouse can now back up your data to an S3 bucket. You can back up (and restore) individual tables and dictionaries or an entire database. Both full and incremental backups are supported.
2. [Reset setting](https://github.com/ClickHouse/ClickHouse/pull/42187) You can now reset a setting to its default value using `SET setting_name = DEFAULT`.
3. [Always merge old parts](https://github.com/ClickHouse/ClickHouse/pull/42423) You can specify that parts will be merged after a certain amount of time using `min_age_to_force_merge_seconds`. This way, you can ensure that after a set amount of time, data will be in the least number of files, improving query performance. It also helps when using the [`FINAL` modifier](https://clickhouse.com/docs/en/sql-reference/statements/select/from/#final-modifier) together with `do_not_merge_across_partitions_select_final` since ClickHouse will not have to merge these files at query time.
4. [Too many parts](https://github.com/ClickHouse/ClickHouse/pull/42002) We have relaxed the too many parts check. By default, ClickHouse will throw an exception if the number of active parts in a partition exceeds 300 (configurable with `parts_to_throw_insert`). With this change, no error will be thrown if the average part size is greater than 10 GiB, allowing very large partitions (100+ TB). It is configurable with the new setting `max_avg_part_size_for_too_many_parts` (default: 10 GiB).
5. [Random data](https://github.com/ClickHouse/ClickHouse/pull/42411) To help you generate random data that is more realistic than a uniform distribution, there are now 11 new `rand*` functions. For example, `randNormal` and `randExponential`.

And one preview feature:
1. **Kafka Connect Sink** We’ve started developing an official Kafka Connect sink for ClickHouse. It will support exactly-once semantics without any third-party dependencies. You can find the current source code [here](https://github.com/ClickHouse/clickhouse-kafka-connect). If you use ClickHouse with Kafka, give it a try and give us some feedback!

Take a look at the [release webinar slides](https://presentations.clickhouse.com/release_22.10/) and the [recording](https://youtu.be/sz9SES5-mdc), and please upgrade unless you want to stay on a Long Term Support (LTS) release.

## Query of the Month: The purifying effects of obfuscation

Useful datasets are like large diamonds: hard to find but very valuable. And when you have some, you typically lock them in a secure space - in a vault or in a database. Unfortunately, the best datasets often contain sensitive information about customers, proprietary data, or trade secrets. And so they only ever live in production databases, and even for internal development and testing purposes, other data has to be used, often synthetically generated test data.

However, synthetic data is often flawed. It usually does not accurately represent what the real data is like. Characteristics like the cardinality of dimensions, the range and distribution of numeric values, and the proportion of empty values will often differ significantly from the actual data. This is a major problem for developing and testing queries that scan a large amount of data to arrive at a result - exactly what ClickHouse is good at.

For example, for a query that contains `GROUP BY dimension`, there is a big difference in memory usage and performance between `dimension` having a cardinality in the hundreds or billions. A `GROUP BY` will be calculated using a hash map with one key for each distinct value. A few hundred key-value pairs will easily fit into memory, and the query will be lightning fast - whereas for billions of values, the hash map will have to be offloaded to disk resulting in a much slower query, or the query might even fail.

So how do we develop and test with data that is close to the actual data in characteristics (such as the cardinality of values in columns) - but is not the actual data?

One answer is obfuscation. And ClickHouse actually ships with a built-in obfuscator, either `clickhouse-obfuscator` (on Linux) or `clickhouse obfuscator` (on Mac). Docs are [here](https://clickhouse.com/docs/en/operations/utilities/clickhouse-obfuscator/).

The ClickHouse Obfuscator will read a dataset as its input and produce as its output a dataset that is very similar in characteristics but won’t contain the same actual values. Let’s look at how it works using the [UK Property Price Paid](https://clickhouse.com/docs/en/getting-started/example-datasets/uk-price-paid/) dataset.

First, a look at some high-level characteristics:

<pre style='background-color: #222222; border-radius: 8px; padding: 24px; color: #FFFFFF; display: flex; position: relative; white-space: pre-wrap;'>
<div style='color: #FFFFFF; margin-top: -24px; max-width: 98%;'>
SELECT count(), avg(price), max(price) FROM uk_price_paid

┌──count()─┬──────avg(price)─┬─max(price)─┐
│ 26763022 │ 208231.24594106 │  932540400 │
└──────────┴─────────────────┴────────────┘
</div>
</pre>
</p>

As we can see, the total number of real estate transactions in the dataset is 26.7 million, with an average price of about £208,000 and a maximum price ever paid of £932 million.
Let’s obfuscate this:
```
clickhouse obfuscator --structure "price Int64, date UInt16, postcode1 String, postcode2 String, type String, is_new UInt8, duration String, addr1 String, addr2 String, street String, locality String, town String, district String, county String, category String" --input-format Parquet --output-format Parquet --seed "${RANDOM}${RANDOM}${RANDOM}${RANDOM}" < uk_price_paid.parquet > obfuscated.parquet
```
Notice how we convert one Parquet file into another - the obfuscator works on non-ClickHouse data!
Let’s check what the same characteristics of the obfuscated dataset look like:

<pre style='background-color: #222222; border-radius: 8px; padding: 24px; color: #FFFFFF; display: flex; position: relative; white-space: pre-wrap;'>
<div style='color: #FFFFFF; margin-top: -24px; max-width: 98%;'>
SELECT count(), avg(price), max(price) FROM file('obfuscated.parquet')
 
┌──count()─┬─────────avg(price)─┬─max(price)─┐
│ 26763022 │ 223585.90603467726 │  994963827 │
└──────────┴────────────────────┴────────────┘
</div>
</pre>
</p>

The number of rows is the same - but the average and maximum prices have changed slightly.
Even the distribution of prices has stayed about the same. For example, the distribution of prices below £1M:

<pre style='background-color: #222222; border-radius: 8px; padding: 24px; color: #FFFFFF; display: flex; position: relative; white-space: pre-wrap;'>
<div style='color: #FFFFFF; margin-top: -24px; max-width: 98%;'>
SELECT band, q1.count AS real, q2.count AS obfuscated FROM
(SELECT floor(price, -5) band, count() count FROM file('uk_price_paid.parquet') GROUP BY band) q1
LEFT JOIN
(SELECT floor(price, -5) band, count() count FROM file('obfuscated.parquet') GROUP BY band) q2
USING band WHERE band < 1e6 ORDER BY band
 
┌───band─┬────real─┬─obfuscated─┐
│      0 │ 8728237 │    8803178 │
│ 100000 │ 9277684 │    7936176 │
│ 200000 │ 4422328 │    5148196 │
│ 300000 │ 1944389 │    1906225 │
│ 400000 │  975584 │    1493438 │
│ 500000 │  465029 │     361164 │
│ 600000 │  291360 │     221633 │
│ 700000 │  178879 │     249621 │
│ 800000 │  117307 │     113644 │
│ 900000 │   77406 │     166402 │
└────────┴─────────┴────────────┘
</div>
</pre>
</p>

It’s worth noting that the obfuscator does not encrypt or hash the data - so not all information is lost (on purpose, to preserve the characteristics). Therefore it is not automatically safe to share an obfuscated dataset with just anybody. But it should go a long way toward making sharing easier and should be especially useful for internal use, for example, for development and testing of an application.

Have fun with the ClickHouse Obfuscator - and maybe you can free up a useful dataset or two.

## Reading Corner
What we’ve been reading:

1. [**100x Faster: GraphQL Hive migration from Elasticsearch to ClickHouse**](https://clickhouse.com/blog/100x-faster-graphql-hive-migration-from-elasticsearch-to-clickhouse) The Guild tested InfluxDB, TimescaleDB and ClickHouse to replace their existing Elasticsearch setup. Unsurprisingly (at least to us), ClickHouse emerged as the clear winner - with query times of 100ms compared to 3 seconds for TimescaleDB and 5 seconds for InfluxDB. By switching from Elasticsearch to ClickHouse, The Guild can now ingest billions instead of only millions of events.

![average_read_time.png](/uploads/average_read_time_1e59bdec8f.png)

2. [**13 ClickHouse "Deadly Sins"**](https://clickhouse.com/blog/common-getting-started-issues-with-clickhouse) and how to avoid them If you are using ClickHouse, then this blog post is for you. Just before Halloween, we wrote up 13 common issues that ClickHouse users run into - and how to avoid them. Have a read, there are probably a few you have run into yourself (and maybe one or two you are about to).

3. [**The world’s fastest tool for querying JSON files**](https://clickhouse.com/blog/worlds-fastest-json-querying-tool-clickhouse-local) - ClickHouse takes on general-purpose tools like Spark SQL - as well as specialized tools like OctoSQL and SPyQL - and comes out ahead. 

4. [**Sending Kubernetes logs To ClickHouse with Fluent Bit**](https://clickhouse.com/blog/kubernetes-logs-to-clickhouse-fluent-bit) and [**Sending Nginx logs to ClickHouse**](https://clickhouse.com/blog/nginx-logs-to-clickhouse-fluent-bit) with Fluent Bit A two-part series on using the Fluent Bit log processor and forwarder to ingest logs into ClickHouse. You should give putting logs into ClickHouse a try - the data size on disk is likely to be much lower, and dashboards much faster than what you have today!

5. [**Visualizing Data with ClickHouse - Part 3 - Metabase**](https://clickhouse.com/blog/visualizing-data-with-metabase) - Part 3 of our series on visualizing data in ClickHouse using different user interfaces. Part 3 covers Metabase, while Part 1 covers Grafana and Part 2 Superset.

6. [**Optimizing star-schema queries with IN queries and denormalization**](https://medium.com/datadenys/optimizing-star-schema-queries-with-in-queries-and-denormalization-cc281bbe19a5) - Some neat tricks here on speeding up ClickHouse queries combining data from multiple tables. For example, using `IN` instead of `JOIN` reduced query time from 19 seconds to 130 milliseconds.

7. [**In-depth: ClickHouse vs PostgreSQL**](https://posthog.com/blog/clickhouse-vs-postgres) - Like many other companies, Posthog started with PostgreSQL and later migrated to ClickHouse. Here they go into some detail on their thinking and the benefits they achieved. Also, lots of cute hedgehogs.

Thanks for reading, and we’ll see you next month.

_The ClickHouse Team_

ClickHouse Newsletter November 2022: The purifying effects of obfuscation

large_hollie-santos-8Os_BEP-mxU-unsplash.jpg

medium_hollie-santos-8Os_BEP-mxU-unsplash.jpg

small_hollie-santos-8Os_BEP-mxU-unsplash.jpg

thumbnail_hollie-santos-8Os_BEP-mxU-unsplash.jpg

hollie-santos-8Os_BEP-mxU-unsplash.jpg

This month, let’s start with some important weather information, then dive into the September release and round it out with some thread work.

By the way, if you’re reading this on our website, did you know you can receive every monthly newsletter as an email in your inbox as well? Sign up [here](https://discover.clickhouse.com/newsletter.html).

## ClickHouse Cloud Beta
If you’re a frequent reader of this newsletter, then you know that we’ve been hard at work building our [ClickHouse Cloud managed service](https://clickhouse.com/cloud). On Tuesday, October 4, 2022, we made the beta version available to the world!

It’s not just a ClickHouse managed service. Today, when you run ClickHouse yourself you’re probably running it on some VMs or Kubernetes pods and configuring replication, sharding, clusters, distributed tables, etc. to achieve high availability and scalability. That’s not what we did with ClickHouse Cloud. Instead, we moved the data to shared object storage that all ClickHouse nodes can access. With that, there is no need to configure anything extra for replication and sharding - object storage is already replicated and it is practically infinitely scalable. In addition, object storage is several times cheaper than block storage.

Why does this matter? As a user, you benefit from the significantly lower price of storage and the ease of use of all data storage being taken care of for you. No more setting up replicas and shards and worrying about how to rebalance those when scaling a cluster up or down. No more worries about upgrades (we do those for you). No more worries about backups either (we do those for you, too). And of course, we monitor and support ClickHouse Cloud for you 24/7/365.

ClickHouse Cloud is the only official ClickHouse managed service built by the creators of ClickHouse and designed from the ground up for the cloud. Give it a try!

Here’s the [release blog post](https://clickhouse.com/blog/clickhouse-cloud-public-beta), a [Quick Start guide](https://clickhouse.com/docs/en/quick-start/), and when you are ready to go head over to [our website](https://clickhouse.com/) and hit the “Try it now” button. Keep in mind it’s still a beta service, so we really appreciate any feedback from you. Also make sure you sign up to our [Launch Webinar](https://clickhouse.com/company/events/cloud-beta) later this month to learn about ClickHouse Cloud directly from our team.

## Upcoming Events

Mark your calendars:

**ClickHouse v22.10 Release Webinar**  
 * **_When? CHANGED TO:_** Wednesday, October 26 @ 9 am PST / 6 pm CEST  
 * **_How do I join?_** Register [here](https://clickhouse.com/company/events/v22-10-release-webinar).  

**ClickHouse Cloud Launch Webinar**
 * **What?** Join us for the virtual unveiling of our managed service. We’ll walk through all the features and give you a peek behind the curtain as well. Can’t wait to tell you all about it! 
 * **When?** Thursday, October 27 @ 9 am PST / 6 pm CEST
 * **How do I join?** Register [here](https://clickhouse.com/company/events/cloud-beta). 
 
**AWS re:Invent**
 * **What?** A number of the ClickHouse team are going to be at re:Invent! Interested in meeting up with us, maybe grabbing a beverage, and talking about ClickHouse? Let us know!
 * **Where?** Las Vegas, NV 
 * **When?** November 29 - December 3, 2022

## ClickHouse v22.9

Our first autumn release this year, with some neat new features:

1. [Vector search (ANN)](https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/annindexes) ClickHouse has a new index type `annoy`. It implements a search index for Approximate Nearest Neighbor (ANN) search to quickly find the nearest neighbor in multidimensional space (vector search). This is an experimental feature.
2. [KeeperMap table engine](https://github.com/ClickHouse/ClickHouse/pull/39976) A new table engine `KeeperMap` allows storing small amounts of key-value data in ClickHouse Keeper or ZooKeeper. This is an experimental feature.
3. [Automatically select quorum](https://clickhouse.com/docs/en/operations/settings/settings/#settings-insert_quorum) You can now set `insert_quorum = auto` to have ClickHouse automatically select a majority of replicas to write an insert to before reporting back success to the client. Note: Using insert_quorum will make inserts take longer.
4. [Readonly users can now change settings](https://clickhouse.com/docs/en/operations/settings/constraints-on-settings/) It can be useful to allow read-only users to change certain settings, the number of threads or table filters, for example. The setting `changeable_in_readonly` allows you to do just that.
5. [INTERSECT DISTINCT and EXCEPT DISTINCT](https://github.com/ClickHouse/ClickHouse/pull/40792) When combining results from two queries, it’s now possible to remove duplicate rows when using INTERSECT (which returns only rows present in both queries) and EXCEPT (which returns only rows present in the first but not the second query).
6. [Official Node.js client](https://github.com/clickhouse/clickhouse-js) ClickHouse now has an official Node.js client written in Typescript. It supports both reading and writing data over HTTP(S). Give it a try!

Take a look at the [release webinar slides](https://presentations.clickhouse.com/release_22.9/), the [recording](https://youtu.be/rK2BsaaaOCA) and please upgrade unless you want to stay on a Long Term Support (LTS) release.

## Query of the Month: More threads are not always better

Many people, after they have used ClickHouse for a bit, will discover the magic of threads. Maybe loading data takes longer than you’d like or perhaps a query is slower than your boss wants it to be. So you do some googling and in an answer to an old StackOverflow question you read about [max_threads](https://clickhouse.com/docs/en/operations/settings/settings/#settings-max_threads) and how it can be used to speed everything up. 

And yet, as so often in life and technology, it’s not that simple!

By default, ClickHouse will try to determine the correct number of threads based on the number of available CPU cores. You can check what it is set to using `SELECT getSetting('max_threads')`. For example, on my MacBook Pro it returns 10, which is the number of cores in an Apple M1 chip (yes, ClickHouse works on ARM!).

You can set it to use more threads, but it won’t always work well. For example, try this (after you create the right table structure [see here](https://clickhouse.com/docs/en/getting-started/example-datasets/ontime/#creating-a-table)):

```
INSERT INTO ontime SELECT *
FROM s3('https://clickhouse-public-datasets.s3.amazonaws.com/ontime/csv_by_year/*.csv.gz', CSVWithNames)
SETTINGS max_threads = 100
```
 
Unless you have a really big machine, it will not complete – and use a lot of memory along the way. Effectively, every one of the 100 threads is a separate insert sub-query and requires its own memory to retrieve and write data.
 
So should you set `max_threads = 1`? Well, maybe - for me, the query above will insert data at about 120k rows per second when limited to a single thread. Without `max_threads` it will insert 320k rows per second. However, the optimal insert speed for me is with 4 threads - 428k rows per second, some 33% faster than with thread autodetection. Sometimes it pays to experiment.
 
Similarly, when reading data, more threads are not always better. Yes, more threads mean there are more CPU cycles available to a query. However, it also means there are more separate states that all need to be merged back together before the result can be returned. Most importantly, many threads per query will reduce the number of queries per second that can be run.
 
For example, using the [UK Property Price Paid dataset](https://clickhouse.com/docs/en/getting-started/example-datasets/uk-price-paid), we can check what the most popular street names are using:
 
```
SELECT count(), street FROM uk_price_paid
GROUP BY street ORDER BY count() DESC
```
 
The top 3: High Street, Station Road, London Road. In the next 7, there are Church Road, Church Street, and Church Lane - maybe not surprising in a monarchy where the King is both Head of State and Head of the Church.
 
This above query takes 0.2 seconds on my machine. If I set `max_threads = 1` it takes 0.5 seconds. So more threads are better, right?
 
In this case, yes - except if I want to run this query many times concurrently. I can execute 10 queries concurrently 10 times (100 queries in total) with `clickhouse-benchmark`:
 
```
clickhouse-benchmark -c 10 -i 100 -q "<query>”
```
 
On my machine, it takes about 27 seconds to run all 100 queries, just under 4 queries a second (`QPS: 3.776` according to the result).
 
With `max_threads = 1` it takes about 10 seconds,  or 13 queries per second (`QPS: 13.122`). Three times higher query throughput! And all we had to do was change the number of threads - but lower, not higher. Generally, if you expect a lot of concurrent queries you should consider explicitly lowering the thread count per query.

## Reading Corner
What we’ve been reading:

- [ClickHouse Cloud is now in Public Beta](https://clickhouse.com/blog/clickhouse-cloud-public-beta) The launch blog post for the new and only official ClickHouse managed service by us, the creators of ClickHouse.
- [Getting Data Into ClickHouse - Part 3 - Using S3](https://clickhouse.com/blog/getting-data-into-clickhouse-part-3-s3) Part 3 of a series on how to get data into ClickHouse. Check out Parts 1 and 2 ([here](https://clickhouse.com/blog/getting-data-into-clickhouse-part-1) and [here](https://clickhouse.com/blog/getting-data-into-clickhouse-part-2-json)) as well.
- [Visualizing Data with ClickHouse - Part 1 - Grafana](https://clickhouse.com/blog/visualizing-data-with-grafana) First in a series on visualizing data with ClickHouse. We start with Grafana and will follow with posts on Superset and Metabase. 

Thanks for reading, we’ll see you next month. And please test ClickHouse Cloud!

The ClickHouse Team



ClickHouse Newsletter October 2022: A cloud has arrived

Cloudy.avif

It’s been an exciting month at ClickHouse. We’ve released Lightweight Deletes, one of the most highly anticipated ClickHouse features of 2022. And we’re getting close to publicly launching ClickHouse Cloud (sign up for early access [here](https://clickhouse.com/cloud)).

Read on about snazzy features in our ClickHouse 22.8 LTS (Long Term Support) release, a simple example of the new DELETE query, and a roundup of ClickHouse stories for the last month.

By the way, if you’re reading this on our website, did you know you can receive every monthly newsletter as an email in your inbox as well? Sign up [here](https://discover.clickhouse.com/newsletter.html).

## Upcoming Events

Mark your calendar:

**ClickHouse v22.9 Release Webinar**  
 * **_When?_** Thursday, September 22 @ 9 am PST / 6 pm CEST  
 * **_How do I join?_** Register [here](https://clickhouse.com/company/events/v22-9-release-webinar).  

**Silicon Valley ClickHouse Meetup**
 * **_What?_** Come hang out with other users and hear what PostHog, Grafana and Barracuda are doing with ClickHouse. 
 * **_Where?_** San Jose, CA
 * **_When?_** Wednesday, September 28 @ 6 pm PST
 * **_How do I join?_** Register [here.](https://www.meetup.com/clickhouse-silicon-valley-meetup-group/events/288140358/) 

**AWS re:Invent**
 * **_What?_** A number of the ClickHouse team are going to be at re:Invent! Interested in meeting up with us, maybe grabbing a beverage, and talking about ClickHouse? Let us know! 
 * **_Where?_** Las Vegas, NV
 * **_When?_** November 29 - December 3, 2022

## ClickHouse v22.8 LTS

Our new Long Term Support release is out with many new features:

1. **[DELETE query](https://clickhouse.com/docs/en/sql-reference/statements/delete/)** It’s finally here! Lightweight deletes were one of the most requested features in our [2022 roadmap](https://github.com/ClickHouse/ClickHouse/issues/32513) and we delivered it with many months to spare. Whenever you are currently using `ALTER TABLE … DELETE` you should switch to `DELETE FROM … WHERE` in almost all cases. It is much cheaper to execute, though still asynchronous (set `mutations_sync = 2` to wait for the query to complete).
2. **[Extended date ranges](https://github.com/ClickHouse/ClickHouse/pull/39425)** `Date32` and `DateTime64` now support dates from 1900 to 2299 (1925 to 2283 before). 
3. **[Parallel distributed insert from S3](https://github.com/ClickHouse/ClickHouse/pull/39107)** ClickHouse can already insert data very quickly on a single machine (typically millions of rows per second). But if you have a cluster of machines, you can now insert from S3 in parallel on all of them. Maybe even billions of rows a second are possible? Somebody should try it.
4. **[JSON logging](https://github.com/ClickHouse/ClickHouse/pull/39277)** ClickHouse can now output its logs in JSON format. This should make it easier to ingest into and query in log management software. You can also ingest into ClickHouse, of course.
5. **[Infer dates and numbers](https://github.com/ClickHouse/ClickHouse/pull/39186** When using schema inference, you can now tell ClickHouse to try to infer dates and numbers from strings.
6. **[Query parameters](https://github.com/ClickHouse/ClickHouse/pull/39906)** can now be set in interactive mode. For example, to define a parameter named `user` just use `SET param_user = alexey`.
7. **[More Pretty formats](https://github.com/ClickHouse/ClickHouse/pull/39646)** Here are 7 more, bringing the total to almost 70. I personally like `PrettyMonoBlock`, but all formats are really “pretty”.

Take a  look at the [release webinar slides](https://presentations.clickhouse.com/release_22.8/), the [recording](https://youtu.be/yob7AnaBJz0) and please upgrade - it is a Long Term Support release.

## Query of the Month: Deleting data can make you feel better

For this month, let’s keep it simple. And tongue in cheek, just a little bit.

If there is one new feature in ClickHouse 22.8 that you absolutely must try it’s the new DELETE query. Up to now, the only way to delete specific rows in ClickHouse was to use an `ALTER TABLE table DELETE WHERE cond` statement. It would asynchronously rewrite all data files containing rows matching the condition. Since data files in ClickHouse can by default be up to 150 GB this was very expensive and could lead to significant CPU and memory usage.

The new DELETE query takes a different approach. Instead of physically deleting all data immediately it only marks the specified rows as deleted using a hidden column. The data is still there, but it is transparently filtered out of queries. Effectively, all queries are executed with an additional condition `WHERE _deleted = false`. Later, as ClickHouse merges files in the background it will drop any rows marked as deleted during the merge process.

Let’s see how useful the new DELETE query can be.

Just for fun, let’s create a table with a portfolio of stablecoins (cost is cost of purchase per coin on January 1, 2022, price is the value on 26 August 2022):

```
CREATE TABLE mymoney engine = MergeTree ORDER BY coin AS
SELECT 'USDC' AS coin, 1000 AS amount, 1.0002 AS cost, 0.9999 AS price
UNION ALL SELECT 'USDT', 1000, 1.0001, 1.00
UNION ALL SELECT 'USTC', 1000, 1.00, 0.0275
```

And let’s calculate our portfolio return:

```
SELECT sum(amount * cost) cost, sum(amount * price) value,
value - cost gain_loss, 1 - value / cost pct  FROM mymoney
```

We lost 32% of our money! What happened? Turns out TerraUSD (USTC) was not such a [“stable” stablecoin after all](https://www.investopedia.com/terrausd-crash-shows-risks-of-algorithmic-stablecoins-5272010).

But no matter, the DELETE query will take care of this:

```
SET allow_experimental_lightweight_delete = 1
DELETE FROM mymoney WHERE coin = 'USTC'
```

And now our portfolio return looks much better. If only it was that simple…

## Reading Corner

What we’ve been reading:

1. [Cloudflare Blog: Log analytics using ClickHouse](https://blog.cloudflare.com/log-analytics-using-clickhouse/) Besides using ClickHouse for serving real-time HTTP and DNS analytics ([link](https://blog.cloudflare.com/http-analytics-for-6m-requests-per-second-using-clickhouse/), [link](https://blog.cloudflare.com/how-cloudflare-analyzes-1m-dns-queries-per-second/)) Cloudflare is also using ClickHouse for storing internal logs. By moving from Elasticsearch to ClickHouse, they were able to remove sampling, and provide fast querying while saving costs. 

2. [ClickHouse Plugin for Grafana - 2.0 Release](https://clickhouse.com/blog/clickhouse-grafana-plugin-2.0) Version 2.0 of our popular ClickHouse plugin for Grafana, now supports HTTP connections and the JSON data type, among other changes. Check it out! 

3. [Exploring massive, real-world data sets: 100+ Years of Weather Records in ClickHouse](https://clickhouse.com/blog/real-world-data-noaa-climate-data) What loading more than a century of weather data into ClickHouse looks like and how you can use it. 
4. [Getting Data Into ClickHouse - Part 1](https://clickhouse.com/blog/getting-data-into-clickhouse-part-1) and [Part 2](https://clickhouse.com/blog/getting-data-into-clickhouse-part-2-json) A walkthrough of getting Hacker News data into ClickHouse and running some fun queries on it. 
5. [MySQL CDC to Clickhouse using Decodable's Change Stream Capabilities](https://youtu.be/Nvy1HWB1mT0) Decodable shows in this video how to synchronize data from MySQL to ClickHouse. 
6. [New ClickHouse Adopters](https://clickhouse.com/docs/en/introduction/adopters/):  A fun addition this month, welcome Nintendo emulator [Dolphin](https://twitter.com/delroth_/status/1567300096160665601). Also welcome to open source ad blocker [AdGuard](https://adguard.com/en/blog/adguard-dns-2-0-goes-open-source.html) and non-profit project [OONI](https://twitter.com/OpenObservatory/status/1558014810746265600?s=20&t=hvcDU-LIrgCApP0rZCzuoA). Get yourself added as well!

Thanks for reading. We’ll see you next month!

The ClickHouse Team

ClickHouse Newsletter September 2022: Deleting data can make you feel better

medium_deleteblog.png

small_deleteblog.png

thumbnail_deleteblog.png

deleteblog.png

_Did you find this page by clicking the "View in Browser" link in an email? Well, that's unfortunate. You can find the [December Newsletter](/blog/newsletter_2022_december) just by clicking the link._

At ClickHouse, we are very happy that in-person meetups are finally back. It’s true that sometimes they coincide with 38 C (100 F) heat - like our meetup in London the past month. But hey, that’s why they’re in the evening, plus there’s pizza. Join a [ClickHouse meetup](https://www.meetup.com/pro/clickhouse) group close by if you can!

We also have a new webpage! It’s much snazzier than the old one, take a look: [https://clickhouse.com/](https://clickhouse.com)

Read on to learn about our new benchmark, let us get you up to speed on what’s new in ClickHouse 22.7, find out if airlines are really as bad as they say, and fill your reading list with some summer material.

By the way, if you’re reading this on our website, did you know you can receive every monthly newsletter as an email in your inbox as well? Sign up [here](https://discover.clickhouse.com/newsletter.html).

**Upcoming Events**

**ClickHouse v22.8 Release Webinar**  
 * **_When?_** Thursday, August 18 @ 9 am PST / 6 pm CEST
 * **_How do I join?_** Register [here](https://clickhouse.com/company/events/v22-8-release-webinar). 



**ClickBench - Benchmark for analytical databases**
Did you know that we’re obsessed with performance at ClickHouse? In our [changelog](https://clickhouse.com/docs/en/whats-new/changelog/), we have an entire section “Performance Improvement” for every release, and it’s often a very long section. That’s why so [many](https://clickhouse.com/docs/en/about-us/adopters/) companies use ClickHouse for [use cases](https://clickhouse.com/customer-stories) requiring fast queries on a lot of data. It’s also why our T-shirts have this text on the back:

```
12 rows in set.
Elapsed: 0.920 sec.
Processed: 107.43 billion rows.
214.86 GB (116.80 billion rows/s, 233.61 GB/s)
```

To continue to track ClickHouse performance and compare it to other systems, we launched ClickBench, our benchmark for analytical databases. The dataset is based on a real-world web analytics workload, the kind that ClickHouse is often used for. Many other use cases have similar data and similar queries, for example, network traffic, structured logs, and event data.

Read up on the background and methodology [here](https://github.com/ClickHouse/ClickBench/) and see the current results at [https://benchmark.clickhouse.com/](https://benchmark.clickhouse.com/).

By the way, did you know [the fastest way to parse JSON is not to parse JSON](https://youtu.be/JBomQk4Icjo?t=3284)?

**ClickHouse v22.7**

As usual, our monthly release is full of features:

1. **Positional arguments** are now turned on by default, so you can use `SELECT ... ORDER BY 1, 2`. But as we’re discovering ourselves, old habits die hard!
2. [**Deprecate Ordinary**](https://github.com/ClickHouse/ClickHouse/pull/38335) Only long-time users will know this: the Ordinary database engine and old *MergeTree syntax are now deprecated. If you have any old databases that have survived countless upgrades, now is the time to change them.
3. [**Expressions in window functions**](https://github.com/ClickHouse/ClickHouse/pull/37848) are now supported.
4. **Not one, but two new join algorithms** - [`direct`](https://github.com/ClickHouse/ClickHouse/pull/35363) and [`full_sorting_merge`](https://github.com/ClickHouse/ClickHouse/pull/35796), give them a try! More to come.
5. [**MongoDB table function**](https://github.com/ClickHouse/ClickHouse/pull/37213) to query MongoDB from ClickHouse. Even better, to read data from MongoDB and write it to ClickHouse for better query performance! Especially aggregations should be much, much faster in ClickHouse.
6. [**Additional filters**](https://github.com/ClickHouse/ClickHouse/pull/38475) to transparently apply filter conditions when reading from a table (`additional_table_filters`) or on a result set (`additional_result_filter`).
7. [**Simple charts**](https://github.com/ClickHouse/ClickHouse/pull/38197) The built-in Play UI (e.g. [`http://localhost:8123/play`](http://localhost:8123/play)) can now draw some basic charts.
8. [**HTTP support**](https://github.com/ClickHouse/clickhouse-go/issues/597) for Go The official [clickhouse-go](https://github.com/ClickHouse/clickhouse-go) driver now supports HTTP.
9. [**ClickHouse + Superset**](https://github.com/ClickHouse/clickhouse-connect) We have a new Superset connector, [clickhouse-connect](https://github.com/ClickHouse/clickhouse-connect). Please give it a try and report any issues.

Take a  look at the [release webinar slides](https://presentations.clickhouse.com/release_22.7/), the [recording](https://youtu.be/IOJyo14BpTQ) and please upgrade (unless you want to stay on an LTS release).


**Query of the Month: Airlines are maybe not that bad**

Common wisdom has it that airlines are terrible and only getting worse. Extra charges for everything from bags to in-flight water, seats that have less legroom every year - and flight schedules that are so tightly packaged they lead to endless delays. The first two are certainly true, but what about the third? We can investigate with ClickHouse!

One of the example datasets in the ClickHouse documentation is [OnTime](https://clickhouse.com/docs/en/getting-started/example-datasets/ontime). It contains over 200 million records of many commercial flights in the US dating back to 1987. Each record contains information about a flight including the date and time of the flight, departure and arrival airports, flight number, and much more. Importantly for us, there are fields showing how delayed the flight was (if at all) compared to its scheduled times.

We want to check if flight schedules are too tight and many planes get caught up in delays that then get worse throughout the day. For that, we need to isolate individual planes. Luckily, the dataset contains the tail number uniquely identifying a particular aircraft. Let’s play around with a few queries to get to know the data:


```
-- Total number of unique aircraft in the dataset
SELECT count(DISTINCT Tail_Number) FROM ontime

-- Number of unique tail numbers by year
SELECT Year, count(DISTINCT Tail_Number) count FROM ontime GROUP BY Year
ORDER BY count DESC
``` 



The first query shows that there are 18,531 distinct tail numbers in the dataset. Note, the total number of actual aircraft is going to be different - planes can and do change tail numbers, for example, when changing carriers or countries of registration. This is also likely the reason why the second query shows an all-time high of 7,768 unique tail numbers in the year 2002, much higher than the second-highest value of 5,892 in 2019. 2002 was a particularly bad year for the aviation industry and many planes likely changed owners.

The second query also shows that prior to 1995 the dataset does not contain any tail numbers. So we should limit our queries to just those rows that have them. We might think that `WHERE Tail_Number != ''` would work, but it does not:


```
SELECT DISTINCT Tail_Number FROM ontime WHERE Tail_Number != '' ORDER BY Tail_Number
-- Top results: #N/A, '144DA, (null), -N037M

SELECT DISTINCT Tail_Number FROM ontime WHERE Tail_Number != '' ORDER BY Tail_Number DESC
-- Top results: �NKNO�, XXXXXX, Unknow, UNKNOW 
```



Lots of values that do not look like valid tail numbers! Instead of filtering out what we don’t want we should just filter for what we want, i.e. valid tail numbers. Luckily, tail numbers have a uniform structure: Letters representing the country of registration, some numbers and more letters often representing the airline. The letters at the end are optional. So we can select only rows with valid tail numbers like this:


```
SELECT DISTINCT Tail_Number FROM ontime WHERE match(Tail_Number,
'^[A-Z]+[0-9]+[A-Z0-9]*$') ORDER BY Tail_Number
-- Top results: A367NW, D942DN, M67153, etc. - all valid
```



The kind of simple queries we’ve run so far are very important when using a new dataset to learn its contents and limitations - no dataset is perfect, and the sooner you work out where the trouble is the better.

Before you read on for the last step, head over to the documentation for the `sequenceMatch` function [here](https://clickhouse.com/docs/en/sql-reference/aggregate-functions/parametric-functions/#sequencematchpatterntimestamp-cond1-cond2-). It’s an ingenious function that allows you to check whether an ordered set of rows contains rows that match a sequence of conditions. It is often used to identify user behavior, for example, you could use it to identify all users of an online shop that first searched for a particular word or phrase, then went on to look at a product detail page, put it in their shopping cart and bought it. You could check sub-sequences of the main sequence to see which step had the biggest drop-off.

`sequenceMatch` is one of 1261 functions currently built into ClickHouse (`SELECT count() FROM system.functions`). Some are well-known SQL functions (count, sum, avg, etc.) but many are specific to ClickHouse and I bet you don’t know all of them! It took me over six months to notice `sequenceMatch`. Have a look around the list of functions sometimes, chances are good you’ll learn something new.

Back to our dataset. We can use `sequenceMatch` to check how the delays of aircraft evolve during a day - do they tend to get worse or do they get better? In the first query, we check how many planes that are delayed by more than 60 minutes subsequently become delayed by more than 90 minutes:


```
WITH (
	DepDelayMinutes > 60 AS delay,
	DepDelayMinutes > 90 AS longer_delay
)
SELECT sum(c) FROM (
	SELECT count() c FROM ontime
	WHERE match(Tail_Number, '^[A-Z]+[0-9]+[A-Z0-9]*$')
	GROUP BY FlightDate, Tail_Number
	HAVING sequenceMatch('(?1)(?2)')(toDateTime(DepTime), delay, longer_delay)
)
```



The result: 6,884,070. What about the delay getting shorter, to less than 30 minutes:


 ```
WITH (
	DepDelayMinutes > 60 AS delay,
	DepDelayMinutes < 30 AS shorter_delay
)
SELECT sum(c) from (
	SELECT count() c FROM ontime
	WHERE match(Tail_Number, '^[A-Z]+[0-9]+[A-Z0-9]*$')
	GROUP BY FlightDate, Tail_Number
	HAVING sequenceMatch('(?1)(?2)')(toDateTime(DepTime), delay, shorter_delay)
)
```



The result here: 7,264,056. Seems quite a few planes do actually catch up with their schedule over a day. Maybe not everything in the airline industry is as bad as we think. Then again, delays have been getting worse over the years, average delays today are about twice as long as they were in the 90s:


```
 SELECT Year, avg(DepDelayMinutes) FROM ontime GROUP BY Year ORDER BY Year DESC
```



Let’s end it here for today. And next time you’re stuck at an airport, fire up ClickHouse and explore!

**Reading Corner**

What we’ve been reading:
1. [DeepL’s journey with ClickHouse](https://clickhouse.com/blog/deepls-journey-with-clickhouse) DeepL brought in ClickHouse, Kafka and Metabase for a shift to data-driven development of the world’s best machine translator.

2. [NANO Corp: From experimentation to production, the journey to Supercolumn](https://clickhouse.com/blog/from-experimentation-to-production-the-journey-to-supercolumn) Read how NANO Corp stumbled into ClickHouse after being frustrated with all other alternatives and stayed because it was the perfect fit for their use case of monitoring gigabit networks in real time.

3. [Fast, Feature Rich and Mutable : ClickHouse Powers Darwinium's Security and Fraud Analytics Use Cases](https://clickhouse.com/blog/fast-feature-rich-and-mutable-clickhouse-powers-darwiniums-security-and-fraud-analytics-use-cases) Digital risk platform Darwinium chose ClickHouse for high-throughput and low-latency writes into wide tables of thousands of columns.

4. [ClickHouse and the open source modern data stack](https://blog.luabase.com/clickhouse-for-data-nerds/) Blockchain analytics provider Luabase chose ClickHouse for its speed and walks through some neat queries including pivoting data.

5. [New ClickHouse Adopters:](https://clickhouse.com/docs/en/introduction/adopters/) Welcome open source feature flagging and experimentation platform [GrowthBook](https://www.growthbook.io/). Get yourself added as well!
 
Thanks for reading. We’ll see you next month!


The ClickHouse Team


Christoph Wurm

Blog posts by Christoph Wurm

ClickHouse Newsletter January 2023: Better Safe Than Sorry

ClickHouse Newsletter December 2022: Project all your troubles away

ClickHouse Newsletter November 2022: The purifying effects of obfuscation

ClickHouse Newsletter October 2022: A cloud has arrived

ClickHouse Newsletter September 2022: Deleting data can make you feel better

ClickHouse Newsletter August 2022: Airlines are maybe not that bad