Random thoughts and technical experiments
Many database systems offer the ability to evaluate an arbitrary string as SQL and run the resulting query - EXECUTE IMMEDIATE in Oracle, EXECUTE in Postgres. ClickHouse didn’t have this ability to run dynamic SQL - until recently. But this changed with version 26.7 . Let’s take a look. Parameters in queries What ClickHouse has had for a long time is query parameters . In the client or in the SQL…
Taking the latest value of a timeseries is, from ClickHouse’s point of view, just a form of aggregation. In an earlier blog I showed how to set up an ingestion pipeline that continually updates the latest values to an AggregatingMergeTree table. This is a common requirement in analyzing IoT data. But recently I encountered an interesting variant of this use case: That customer wanted to show, for…
Refreshable Materialized Views (RMV) are a way to define scheduled data transformations in ClickHouse. A materialized view definition becomes an RMV by adding a REFRESH clause to the DDL like so: CREATE TABLE ukp_bytown ( town LowCardinality ( String ), total UInt32 ) ENGINE = MergeTree ORDER BY ( town ); CREATE MATERIALIZED VIEW rmv_ukp REFRESH EVERY 5 SECOND TO ukp_bytown AS SELECT town , sum (…
Did you know you can send files, like log files, directly to an endpoint in ClickHouse? For simple use cases this means you might not need to use a collector/agent like Open Telemetry Collector . Let’s look at a simple example. I am using a 100 line sample from this example , taken from the ClickStack documentation . The data is structured and represents a web server log and looks like this: {…
One of the most flexible ways to get data from Kafka into ClickHouse is the Kafka Table Engine . This allows to connect to Kafka in various ways, and it also supports multiple data formats. However, when you use Confluent’s Schema Registry (or a compatible service) to enforce a data contract, support is limited to Avro format . What if one uses JSON with the Schema registry instead? In that case,…
Intro A while ago, I wrote this blog about quantiles in Druid . I am planning to do a similar exercise for ClickHouse: this blog here is a preparatory exercise about generating the test data. Back then, I wrote an external data generator script but I thought by myself: with the richness of ClickHouse, can this be done natively? Let’s find out! Recap: the Python version Here is the Python code that…
Asynchronous inserts and buffer tables ClickHouse is known for being able to handle large amounts of data that are inserted in near real time. However, each insertion creates at least one new data part, which leads to frequent writes and requires a lot of background merges. Therefore, larger batches of at least 1000 rows are recommended . But what if you cannot or don’t want to batch up data in…
Here’s another of my learnings from playing with ClickHouse. This one is about the QUALIFY clause. It is a filter that is evaluated after applying window functions, but before any GROUP BY clause. I had been thinking of this clause mainly as syntactic sugar - you can write SELECT number , COUNT () OVER ( PARTITION BY number % 3 ) AS partition_count FROM numbers ( 10 ) QUALIFY partition_count = 4…
The Problem In text search, you often want to be agnostic of diacritics - those appendages to Latin script letters, like umlaut markers, accents, cedilles, and other modifiers. You want the search term ‘Jose’ to also match ‘José’, for instance. A customer, faced with this question in ClickHouse, came up with an expression like this:…
This is a question I encountered recently. A customer wanted to get started with ClickHouse but they had their historical data in CSV files in Google Drive. Here’s how to get the job done. Main source is this StackOverflow answer . Get the file ID: Go to your Google Drive in your browser. Right-click the file you want to download and click Get shareable link . The link looks like this:…
ClickHouse has a lot of built-in functionality that is just there for analysts and data engineers to discover. Today, I am once more going to revisit one of my old Druid blogs . It’s about the difficulty of counting unique things, which is particularly relevant for any kind of clickstream or media analytics, where you want to count unique visitors over arbitrary segments of content, time,…
Let’s pick up one of my earlier blogs . In that blog, I took a very simple data set to show how Druid can aggregate data upon ingestion. Today, I will show how much easier this works with ClickHouse. You can use any version of ClickHouse for this exercise. For instance, you can run ClickHouse locally on your laptop. Or check out the installation docs for other options. In today’s tutorial, you…
In my previous post , I redefined the dot product of two vectors. But there’s more fun to be had doing linear algebra with ClickHouse! Let’s define some linear algebra operations for pure educational value. Turning things around: Outer product If you multiply two vectors element by element, you get a matrix. This is known as the outer product. It is denoted by u ⊗ v and can also be written as uv T…
After a brief hiatus, the Data Cookbook Elf is back! I have a new job and am now working at ClickHouse , so the data engineering kitchen will be changing its menu a bit and will be dealing with facts and features around ClickHouse. I am truly happy to work with this database now, and the richness of ClickHouse’s SQL dialect is simply amazing! Today, the Data Cookbook Elf will be looking at some of…
Up until Druid 30, data in a Druid segment was always sorted by time. But this is about to change. Druid 31 comes with an experimental option to change the segment sort order. With Druid Summit 2024 and the announcement of Druid 31 around the corner, let’s take a look at this new feature. This blog is based on the public documentation of this pull request . This is a sneak peek into Druid 31…
Imply’s Polaris service has a new feature: Lookups. Lookups in Polaris provide a convenient way to model dimension tables in a star schema, and they are more flexible and less resource hungry than the lookups you know from open source Druid. Let’s take a closer look!
In the previous episode of the Druid Data Cookbook , I showed how to extract and process all elements out of a nested array of objects in Druid. But what if the elements you want to process are scattered over different levels of the object hierarchy? By extending the UNNEST paradigm, we can handle these cases too! A data sample Our data looks like this: { "timestamp" : "2024-09-01" , "id" : "1" ,…
A common problem in data that we ingest into Druid is that we may encounter arrays of nested objects, and we want to reason about specific fields within those objects. For instance, assume we have various teams for some sort of contest, and the members of a team might be represented like so: [ { "name" : "Alice" , "gender" : "F" }, { "name" : "Bob" , "gender" : "M" }, { "name" : "Carol" , "gender"…
Let’s look at a neat new feature in Druid 30 that makes using the API more flexible. For this tutorial, download a fresh copy of Druid 30 . Run a local quickstart instance and ingest the Wikipedia sample data as per this tutorial . Recap: Parameters in the query API Druid is typically queried through a REST API; the payload is documented here . The API supports parameterizing queries, which is…
In the latest versions of Druid, handling of logical expressions, and in particular of NULL (unknown) values, has been changed to match the SQL standard. This leads to some behavior that may be surprising to long time Druid users. Let’s take a look at some examples! Server configuration settings that affect NULL handling There are three configuration settings that affect NULL handling:…