<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Denys Golotiuk on Medium]]></title>
        <description><![CDATA[Stories by Denys Golotiuk on Medium]]></description>
        <link>https://medium.com/@golotyuk?source=rss-246b8dbde5aa------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*HE5p27lyZKbZmPkMUoM8gw.png</url>
            <title>Stories by Denys Golotiuk on Medium</title>
            <link>https://medium.com/@golotyuk?source=rss-246b8dbde5aa------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Mon, 16 Nov 2026 09:27:55 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@golotyuk/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[Using partitions in Clickhouse]]></title>
            <link>https://medium.com/datadenys/using-partitions-in-clickhouse-3ea0decb89c4?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/3ea0decb89c4</guid>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[partitioning]]></category>
            <category><![CDATA[clickhousedb]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Tue, 29 Nov 2022 12:43:04 GMT</pubDate>
            <atom:updated>2022-11-29T12:43:04.519Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WptoJ39VSSTan8k7UXxMGw.png" /><figcaption>Table partitioning in Clickhouse</figcaption></figure><p>Clickhouse supports table partitioning which can be useful in cases we deal with serial data and need to work only with a moving window of it (e.g. we only work with last 3 months of data). Since it’s better to avoid mutations (happen when we delete data), partitions can become an efficient way to get rid of unused (outdated) data.</p><h3>How partitions work</h3><p>Clickhouse allows us to split tables in multiple blocks. This can later be used to manipulate (say, drop or move) those blocks in an efficient manner. To enable partitions we have to define partition key using PARTITION BY expression, which Clickhouse will use to split table data:</p><pre>CREATE TABLE test (a,b,c) PARTITION BY (a) ORDER BY (b)</pre><p>When partition key is defined, Clickhouse will automatically route incoming data to be saved into separate blocks (partitions):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*g4XsE27eWYXM2s8f0B6d5g.png" /></figure><p>We will still have single table and everything will work like we expect it to work. But internally, table data will be stored within multiple partitions. Why use that? Let’s see on examples.</p><h3>Test tables</h3><p>Let’s say we have a situation where we need to store <a href="https://medium.com/datadenys/optimizing-timeseries-data-in-clickhouse-c6518a9d6da0">timeseries data</a>. We work with last 7 days of data only and we need to clear old data on a daily basis. We have a simple table:</p><pre>CREATE TABLE unpartitioned<br>( `t` Date, `label` UInt8, `value` UInt32 )<br>ENGINE = MergeTree ORDER BY (label, t)</pre><p>We usually filter by t and label or by label only:</p><pre>SELECT AVG(value) FROM unpartitioned WHERE t = today() AND label = 10</pre><p>We have populated our table with 100 million random rows:</p><pre>INSERT INTO unpartitioned<br>SELECT today() - rand32() % 10, rand32() % 10000, rand32()<br>FROM numbers(100000000);</pre><h3>Creating paritioned table</h3><p>Let’s create partitioned table and copy source data:</p><pre>CREATE TABLE partitioned<br>(`t` Date, `label` UInt8, `value` UInt32)<br>ENGINE = MergeTree PARTITION BY t ORDER BY label<br><br>INSERT INTO partitioned SELECT * FROM unpartitioned</pre><p>Since we have chosen t column (date) to partition our table based on, we have removed it from order key. We can check which partitions our table have by using system.parts table:</p><pre>SELECT partition, formatReadableSize(sum(bytes))<br>FROM system.parts<br>WHERE table = &#39;partitioned&#39;<br>GROUP BY partition</pre><p>Which gives us the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/957/1*SrYAJkcWyxPQAFIderIUmw.png" /></figure><p>We can see that 10 partitions were created for our table (which is exactly what we’ve expected). Different partition was created for each t column value (each day).</p><h3>Query performance</h3><p>When working with partition tables, Clickhouse will check if all partitions should be used for a query or it can skip some partitions. This is a tricky situation that can result in performance either improved or degraded depending on how many partitions out query hits.</p><h4>Single partition hit</h4><p>Assume we have the following query:</p><pre>SELECT avg(value) FROM unpartitioned WHERE t = today() AND label = 10</pre><p>Let’s see how that works for the original table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1023/1*CRdSMBhQH79UKQVKueMi0w.png" /></figure><p>Now the partitioned one:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1023/1*6gegTTODATUb8rO84EICNQ.png" /></figure><p>Partitioned table required Clickhouse to scan less rows to give an answer to our query. Why? Clickhouse tells that it has to read more parts for unpartitioned table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*PUE0YMHsH5XFJkngpiaVcQ.png" /></figure><p>This happens because in case of partitioned table, we have a single partition hit — Clickhouse reads data from a single partition and ignores others. Our partitioned table has simple sort key built on top of label column. Which means Clickhouse finds target partition first and then filters query based on a label sort key:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5XDVkuQ109wNqNhmSIvW5A.png" /></figure><p>In case of unpartitioned table, Clickhouse loads all possible parts based on a combination of label + t (note columns order) values, since they are both being a part of the sorting key:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*993cyM69RPxynhW_xUAvcw.png" /></figure><h4>Multiple partitions hit</h4><p>Now let’s assume we have the following query:</p><pre>SELECT t, avg(value) FROM unpartitioned WHERE label = 10 GROUP BY t</pre><p>Here we want to get data accross all available period and filter it by label. Let’s see how unpartitioned table performs:</p><pre>EXPLAIN ESTIMATE SELECT t, avg(value) FROM unpartitioned WHERE label = 10 GROUP BY t</pre><p>Which shows us that Clickhouse is going to read 450k rows from 6 parts:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/609/1*v1SYW2M4VdUZd5Puvx0lbg.png" /></figure><p>While partitioned table will result int reading almost 700k rows from 35 parts:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/609/1*j0lJgbS00IBDxz51mtotZQ.png" /></figure><p>This happened, because unpartitioned table still uses its sorting key to find relevant parts to load and then filter. While partitioned table has to find relevant partitions first and then find relevant parts to further filter in each partition:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PhfLGNkPzEJ2t8Ft9KqR6g.png" /></figure><p>This gives us an important insight into how partition can improve or make things worse:</p><ul><li>If we have most of our queries hit single partition, we will generally see query performance improvement,</li><li>On the other hand, if queries usually hit multiple partitions, it can result in degraded performance.</li></ul><p>Read performance gains can be an effect of partitioning, but shouldn’t be used for that, as <a href="https://medium.com/datadenys/improving-clickhouse-query-performance-tuning-key-order-f406db7cfeb9">sorting key</a> is something to tune for performance.</p><h3>Ingest performance</h3><p>Using partitions will also affect how data is ingested. When data arrives into partitioned table, Clickhouse has to basically find relevant partitions for data and then (create and then) populate them with new data. This is one additional step if we compare to unpartitioned table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O_0eTQxW4FWXNwlK9km7Fw.png" /></figure><p>We can spot the difference if we try to insert into partitioned and unpartitioned table (with the same sorting key by label column):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1E0LWmb_m2_-Fo528VzkEA.png" /></figure><p>Thus we have a limit of how many partitions can be inserted within a single query, which is 100 by default. Trying to insert too many partitions will result in the following error:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DXZ-X_47BAZbGHRNAOK9FQ.png" /></figure><p>If we still need to insert to a lot of partitions, we have to split data into multiple batches grouped into smaller packets of partitions.</p><p>Given this, it’s a good case to have a partitioned table when we insert serial data (e.g. timeseries) and our insert batches usually hit single or little partitions.</p><h3>Choosing partitioning key</h3><p>Good approach is to try to <strong>keep number of partitions within a couple of tens or so</strong>. As a popular case, for dates, it’s a good idea to use higher aggregating functions to lower the number of partitons:</p><pre>PARTITION BY toYYYYMM(date_column)</pre><p>Remember, that in most cases partitioning is not needed at all as this is not the tool to improve query performance. The only case when performance is improved — when queries hit single partition.</p><p>Good reason for using partitions is that we can manage table partitions as a separate objects, e.g. drop or move it.</p><h3>Removing data by dropping partitions</h3><p>Let’s return to our original unpartitioned table. Since we only need last 7 days of data, we have to delete old data each day:</p><pre>ALTER TABLE unpartitioned DELETE WHERE t &lt; (today() - 7)</pre><p>This will result in a mutation, because rows will not be deleted instantly, but only marked as deleted by Clickhouse. Then, in the background, Clickhouse will eventually remove that data.</p><p>For partitioned table we can instantly drop entire partition with outdated data:</p><pre>ALTER TABLE partitioned DROP PARTITION &#39;2022-11-08&#39;</pre><p>Partition name (like 2022–11–08) can be found in the system.parts table. And since this is a lightweight operation (basically a folder being removed from disk) server performance is not affected. We can also instantly see table size reduction.</p><h3>Summary</h3><p>Using partitions in Clickhouse allows transparently splitting table into multiple blocks and have a way to manage those blocks independantly (e.g. drop them). Partitioning key should always be a low cardinality expression (with a couple of tens of values). Do not consider partitioning for query performance improvement and mind that data ingest performance can degrade for partitioned tables.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3ea0decb89c4" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/using-partitions-in-clickhouse-3ea0decb89c4">Using partitions in Clickhouse</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Estimating duplicates and deduplicating data in Clickhouse]]></title>
            <link>https://medium.com/datadenys/estimating-duplicates-and-deduplicating-data-in-clickhouse-60e35f883765?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/60e35f883765</guid>
            <category><![CDATA[deduplication]]></category>
            <category><![CDATA[data-deduplication]]></category>
            <category><![CDATA[clickhouse]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Mon, 21 Nov 2022 15:19:17 GMT</pubDate>
            <atom:updated>2022-11-21T15:19:17.314Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sNbwOOdUwmgZgybxT7avBg.png" /><figcaption>Deduplicating data in Clickhouse</figcaption></figure><p>Clickhouse has a way to quickly estimate number of duplicates in a table as well as remove duplicates so only unique rows remain. Popular solution here is to GROUP BY columns and insert resulting data into new table. But this can be a challenge if tables are large and disks are full. Clickhouse has an efficient way to handle both tasks efficiently.</p><h3>Test data</h3><p>Let’s assume we have the following table:</p><pre>CREATE TABLE default.test<br>(<br>    `a` UInt32,<br>    `b` UInt32,<br>    `c` UInt32<br>)<br>ENGINE = MergeTree<br>ORDER BY tuple()</pre><p>We’ve populated it with 100 million rows of random data:</p><pre>INSERT INTO test<br>  SELECT rand32() / 100000, rand32() / 10000, rand32() / 1000<br>  FROM numbers(100000000)</pre><h3>Estimating unique rows number</h3><p>Clickhouse <a href="https://medium.com/datadenys/memory-efficient-unique-values-calculation-on-large-datasets-in-clichouse-4eefe36db1d0">uniq</a>() function can accept multiple arguments so estimating the number of unique rows can be done with the following query:</p><pre>SELECT uniq(a,b,c) FROM test</pre><p>Which gives us an approximate number of unique rows (based on 3 columns) in our table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/159/1*9jb6mYhLvY6KbxinIdNEnA.png" /></figure><p><strong>Hack:</strong> if we need to find unique rows based on all columns in table, we can simply use * instead of all columns names as an argument of uniq() function:</p><pre>SELECT uniq(*) FROM test</pre><p>To find a fraction of unique rows, we can use the following:</p><pre>SELECT uniq(*)/count(*) FROM test</pre><p>This will give us:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/313/1*3qJUsQuVO_XuPnu4okxsSA.png" /></figure><p>Which means we only have something like 4.3% of unique rows in a table, while the rest 95.7% is duplicated data.</p><h3>Removing duplicates</h3><p>Clickhouse has a built in tool to remove duplicates from MergeTree tables using DEDUPLICATE option in OPTIMIZE statement:</p><pre>OPTIMIZE TABLE test FINAL DEDUPLICATE</pre><p>We need FINAL<strong> </strong>here to deduplicate data even if it’s within a single part already. It took something like 7 seconds in our case:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/421/1*GFsSKrElapKTkgUSo8RjoQ.png" /></figure><p>Now we can make sure we do not have any duplicates in the table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/425/1*QfJhNhe3VplnTXBLejtRsA.png" /></figure><p>This doesn’t give us 100% since uniq() is an approximation. In order to get accurate calculation we can use the following (memory consuming) query:</p><pre>SELECT count(distinct a,b,c)/count(*) FROM test;</pre><p>And this will prove we no longer have duplicates in our table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/1*X9TtGwV_m3_RnGOa_kyg1w.png" /></figure><h4>Deduplicating based on a subset of columns</h4><p>If data needs to be deduplicated based on some columns only (instead of all columns), we can add BY expression:</p><pre>OPTIMIZE TABLE test FINAL DEDUPLICATE BY a,b;</pre><p>Here, we have removed duplicates based on (a,b) columns only instead of all columns.</p><h3>Summary</h3><p>Fraction of unique rows in a table can be quickly estimated using uniq() function:</p><pre>SELECT uniq(*)/count(*) FROM table</pre><p>If this gives values which are far less than 1, then we have a lot of duplicates in the table. Duplicated rows can be removed using the following query:</p><pre>OPTIMIZE TABLE test FINAL DEDUPLICATE</pre><p>This will leave only unique rows in the table.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=60e35f883765" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/estimating-duplicates-and-deduplicating-data-in-clickhouse-60e35f883765">Estimating duplicates and deduplicating data in Clickhouse</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[How to use window functions in Mysql]]></title>
            <link>https://medium.com/datadenys/how-to-use-window-functions-in-mysql-541eae9f04eb?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/541eae9f04eb</guid>
            <category><![CDATA[running-total]]></category>
            <category><![CDATA[sql-window-functions]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[moving-average]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Fri, 11 Nov 2022 16:38:36 GMT</pubDate>
            <atom:updated>2022-11-11T16:38:36.555Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Kngpdy5BH_813v5V8RMOxQ.png" /><figcaption>Mysql window functions</figcaption></figure><p>Using windows in Mysql is a way to query data based on a set of rows instead of a single row. This helps in comparative analysis, when we do not only calculate values based on groups but also based on preceding or following rows in a group. Popular examples here are moving average and cumulative sum. Let’s take a look on practical cases…</p><h3>Test table and data</h3><p>Assume we have the following table:</p><pre>CREATE TABLE `test` (<br>  `id` bigint unsigned NOT NULL AUTO_INCREMENT,<br>  `amount` float DEFAULT NULL,<br>  `user_id` int unsigned DEFAULT NULL,<br>  `product_id` int unsigned DEFAULT NULL,<br>  `at` datetime DEFAULT NULL,<br>  PRIMARY KEY (`id`)<br>)</pre><p>…with some test data:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/549/1*0qdONENtET501wmW-lIDAw.png" /></figure><p>Here we store product transactions, generated by users with some amount (of money) happened at a specific time.</p><h3>How windows work in Mysql</h3><p>The window is simply some set of rows (defined by specific rules), for example:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OGdRuEZxxJHSr9Gd2YlEyg.png" /></figure><p>Here we have 2 randomly selected set of 3 rows. Since this selection doesn’t make any sense let’s select one window for each product:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9Tiayp9H_uTPFJ3ctJezLA.png" /></figure><p>Now we have 3 windows, each includes data for only one specific product. Basically, we have just grouped our data based on product_id:</p><pre>SELECT product_id, sum(amount) FROM test GROUP by product_id LIMIT 5</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/328/1*uOypMF4Ec9-InOMxlitejw.png" /></figure><p>That is an aggregation as we know it. In contrast, window function will calculate [something we’re asking for] for <strong>each row</strong> in a group (window) instead of a group:</p><pre>SELECT product_id,<br>       sum(amount) over (partition by product_id)<br>FROM test LIMIT 5;</pre><p>This will give us the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/996/1*1-B34F2EZdXB-afc-rsjWg.png" /></figure><p>So, we do not actually group our data on product_id column. Instead we ask Mysql to add sum(amount) value into each table row, calculated for every product_id group. WTF is this used for and why we need it? Let’s discover based on several cases. But before that, let’s understand how windowing works from the perspective of coding.</p><h4>Window syntax</h4><p>From the example above, we’ve asked Mysql to calculate sum for each product_id:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sCypyqxDohncJtPjXuwYxA.png" /></figure><p>Or, in other words, we’re adding sum for defined windows to each row in our original table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*L4ZtUNHLiqaUKpcLBRv_SA.png" /></figure><p>Now let’s find out practical cases to use that for.</p><h3>Value fraction</h3><p>Popular scenario is to calculate current row value fraction in window total. In our case, we can understand each row amount fraction per each product total (amount sum for each product):</p><pre>SELECT product_id,<br>       amount, 100*amount/sum(amount) over (partition by product_id) as frac<br>FROM test LIMIT 10</pre><p>And we get the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/882/1*ieqVg2Ff6a2a7wj6eXkuVA.png" /></figure><p>So for each row we can see its amount significance in relation to total amount for corresponding product. Following this example, we can further improve our query and order it by fraction value:</p><pre>SELECT product_id,<br>       amount, 100*amount/sum(amount) over (partition by product_id) as frac<br>FROM test ORDER BY frac DESC LIMIT 10</pre><p>And we’re then able to get a list of rows where amount has the biggest fraction from the product total:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/882/1*hkXL6iu65Xc0yJDAwGYyQA.png" /></figure><h4>Value fraction from table total</h4><p>We can skip OVER() expression (just leave it empty) and find each row amount fraction from table total:</p><pre>SELECT product_id,<br>       amount, 100*amount/sum(amount) over () as frac<br>FROM test LIMIT 10</pre><p>In this case, we basically define a single window which is our entire table. So sum(amount) will return total for all rows in a table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/882/1*qtLNQ2Wl-knM2gNpSaqXtA.png" /></figure><h4>More aggregate functions</h4><p>We can use any aggregate function with windows:</p><pre>SELECT id, product_id, amount,<br>       avg(amount) over (partition by product_id),<br>       min(amount) over (partition by product_id)<br>FROM test LIMIT 10;</pre><p>Which will give us averages and minimums for each window:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Q_CgEgWl3maXvhWckCLEdQ.png" /></figure><h3>Running total</h3><p>Another popular example is calculating cumulative totals as we go row by row. When we want to apply running aggregations, we need to define ORDER expression for OVER() clause:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9n_5Ej2JmT_Ytl7rffnybQ.png" /></figure><p>Let’s take a look at this example:</p><pre>SELECT at, product_id, amount,<br>       sum(amount) over (partition by product_id order by id) as run_sum<br>FROM test LIMIT 10</pre><p>This will calculate amount column cumulative sum for each product:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*K3H2YHKtK7xbiWIzE3DN6Q.png" /></figure><p>In other words, we got data for cummulative chart in run_sum column:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bbb57G3sgoyOfCZB2De3sA.png" /></figure><h3>Moving average</h3><p>When our data is time-based, we might want to calculate aggregations for a set of rows that includes current and multiple previous (or following) rows. This is what called moving aggregations and can be done by specifying ROWS expression for OVER() clause:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZfTnugU8rwq7NbMLaFOn4g.png" /></figure><p>Let’s calculate moving average for amount column based on a window of 3 preceding rows and zero following:</p><pre>SELECT at, id, amount,<br>       avg(amount) over (<br>                     order by id<br>                     rows between 3 preceding and 0 following<br>                   ) as run_avg<br>FROM test LIMIT 10;</pre><p>Which gives the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5LSXFsr3hE5icyANYOw2MA.png" /></figure><h3>Top row from group</h3><p>Let’s say we would like to understand which user (user_id) has bought our product (product_id) at the biggest price (amount). This can be done using rank() window function. First, let’s see what rank function gives us:</p><pre>SELECT product_id, user_id, amount,<br>       RANK() OVER(partition by product_id order by amount desc)<br>FROM test LIMIT 15 </pre><p>This will sort our window and assign rank (sorted position number) to each row based on a given order by expression in OVER() clause:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Dv3rpOwG4O7gLEkVf2J9Wg.png" /></figure><p>The last query will give us the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/850/1*ohIhWQxJkpwfN3DnJ31lFA.png" /></figure><p>Now we can filter only those rows with rank = 1 because we want to see top row for each product:</p><pre>WITH ranked AS(<br>  SELECCT product_id, user_id, amount,<br>  RANK() OVER(partition by product_id order by amount desc) as rnk<br>  FROM test<br>)<br>SELECT product_id, user_id, amount<br>FROM ranked<br>WHERE rnk = 1;</pre><p>Which gives us user_id with biggest amount value for each product — exactly what we need:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/850/1*iZjLlClyLOJ-FLd1DbluDw.png" /></figure><h3>Summary</h3><p>Window functions allow analyzing datasets based on groups of rows instead of single rows. In cotrast to grouping aggregations, windows are used in the context of each row (instead of context of grouped values). Popular examples of window functions are moving averages and running totals, but window-based analysis goes far beyond those.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=541eae9f04eb" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/how-to-use-window-functions-in-mysql-541eae9f04eb">How to use window functions in Mysql</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Big data exploratory data analysis with Clickhouse]]></title>
            <link>https://medium.com/datadenys/big-data-exploratory-data-analysis-with-clickhouse-227f4a1dad3c?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/227f4a1dad3c</guid>
            <category><![CDATA[big-data-analytics]]></category>
            <category><![CDATA[big-data-analysis]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[exploratory-data-analysis]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Fri, 04 Nov 2022 15:36:14 GMT</pubDate>
            <atom:updated>2022-11-04T15:39:14.125Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x5svW4YUJ1d_59z94RM1hg.png" /><figcaption>Exploratory data analysis with Clickhouse</figcaption></figure><h3>Big data exploratory analysis with Clickhouse</h3><p>Any analysis starts with exploratory stage — when we try to understand the big picture of the data itself. We usually do things like geting medians or finding distributions during this step, which Python Numpy/Pandas allows us to easily accomplish. But if we talk about large data volumes (Terabytes+) we can’t simply use tools which rely on RAM. But Clickhouse can help, it has a powerfull set of tools to dig into data beyond trivial min/max/avg analysis, let’s take a look at them.</p><h3>Test table and data</h3><p>We’re going to use the following table and <a href="https://clickhouse.cloud/">clickhouse.cloud</a> service for tests:</p><pre>CREATE TABLE test (<br>  `time` DateTime, `a` UInt64, `b` Float64,<br>  `c` UInt32, `d` UInt32, `e` UInt32<br>) ORDER BY time</pre><p>Yeah, we can skip ENGINE for new versions of Clickhouse, which is 22.10.1.17597 on our test cloud service. Alos, we have 1 billions rows of randomly generated values in our table:</p><pre>INSERT INTO test<br>SELECT now() - number, rand64()/1000000000,<br>       round(rand32()/1000000)/100,<br>       rand32()/1000000, rand32()/10000, rand32()/100<br>FROM numbers(<strong>1000000000</strong>);</pre><h4>Min, Max, Avg, Median</h4><p>First things first, so let’s see what we have in terms of basic aggegations:</p><pre>SELECT min(a), max(a), avg(a), median(a) FROM test</pre><p>Which will give us the following overiew of a column:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/522/1*YOrJTCm5pjS30lFd9Mwlyw.png" /></figure><p>Note, that <a href="https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/median/">median</a> function is an approximate implementation (for the sake of speed), so consider using medianExact() if you need exact reults.</p><p>For columns of the same type, we can use *<a href="https://clickhouse.com/docs/en/sql-reference/aggregate-functions/combinators/#-foreach">ForEach</a> combinator to show us aggregations results for all listed columns:</p><pre>WITH [<strong>a, c, d, e</strong>] AS col<br>SELECT maxForEach(col), minForEach(col),<br>       avgForEach(col), medianForEach(col)<br>FROM test\G</pre><p>Note, we had to skip b column, because it’s Float, not Int. The latest query will return multiple arrays with values positioned accordingly to columns listed in WITH clause:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/818/1*r0N7_jfuv4aOK9R20gs_UQ.png" /></figure><h3>Weighted average</h3><p>In some cases we want to understand weighted averages, when we have a value in one column (let it be a column) and its weights in another (let’s use c column for that):</p><pre>SELECT <strong>avgWeighted</strong>(a, c) FROM test</pre><p>Now the result is different from standard average value because c column has different value (weight) for each row:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/197/1*_xx7jTotky7IhCh_nlrchA.png" /></figure><h3>Quantiles</h3><p>Values distribution analysis starts with understanding most popular quantiles values, which can be accomplished using <a href="https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/quantile#quantile">quantile()</a> function:</p><pre>SELECT quantile(0.01)(a), quantile(0.05)(a), quantile(0.1)(a),<br>       quantile(0.5)(a),  quantile(0.9)(a),  quantile(0.95)(a),<br>       quantile(0.99)(a)<br>FROM test\G</pre><p>Which gives us following quantiles for a column:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/362/1*2QjbFtwLMHlTWqoH2CQnOA.png" /></figure><p>Numeric argument of quantile() function represents which quantile we need to calculate, values can go from 0 to 1, where:</p><ul><li>0.01 is a 1% percentile (meaning 1% of all column values that are less then calculated quantile value),</li><li>0.99 is a 99% percentile,</li><li>0.5 is a 50% percentile (or a median).</li></ul><h3>Histogram</h3><p>Clickhouse has <a href="https://clickhouse.com/docs/en/sql-reference/aggregate-functions/parametric-functions/#histogram">histogram()</a> function which can calculate number of values within given number of bins:</p><pre>SELECT histogram(<strong>10</strong>)(a) FROM test\G</pre><p>Which gives us:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*pVy4S7jo6o0DfHbfoo1mCg.png" /></figure><p>Since this doesn’t look too useful for evaluation, we can use bar() function to plot that:</p><pre>WITH    count(*) AS m, <strong>histogram(10)(a)</strong> AS h<br>SELECT  m, arrayJoin(h).3 AS v, <strong>bar(v, 0, m / 10, 50)</strong><br>FROM    test</pre><p>Here, we calculate histogram for a columns based on 10 bins and plot results by using calculated histogram “height” values (v) as a bar width (having 50 symbols as a maximum width of bar). Now we can see our values are randomly distributed</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*T9SmRWaVwbpoNjwdzCux1Q.png" /></figure><h4>Skewness</h4><p>In order to understand how symmetric our data is we can use skewness evaluation:</p><pre>SELECT <strong>skewPop(a), skewPop(b), skewPop(c), skewPop(d)</strong> FROM test</pre><p>And we can see we have pretty symmetrical datasets (dataset is considered to be symmetric if skewness falls into -0.5…0.5 range):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/935/1*PqKSBPQhPtgGgzal1roVnA.png" /></figure><h3>Unique values</h3><p>As we know Clickhouse uses <a href="https://medium.com/datadenys/memory-efficient-unique-values-calculation-on-large-datasets-in-clichouse-4eefe36db1d0">memory efficient approximate unique</a> values algorythm which is more than ok during exploratory analysis for most cases:</p><pre>SELECT uniq(a), uniq(b), uniq(c), uniq(d), uniq(e) FROM test\G</pre><p>Which will give us the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/420/1*Rv1zJb-qKPZ2g21ECr1aZA.png" /></figure><h3>Top popular values</h3><p>Sometimes we need to understand which values are most popular in our datasets. Though it seems to be simple with standard SQL, e.g. for a column:</p><pre>SELECT <strong>a, count(*) AS total</strong> FROM test<br><strong>GROUP BY a</strong> <strong>ORDER BY total</strong> DESC LIMIT 5</pre><p>… we can actually use <a href="https://clickhouse.com/docs/en/sql-reference/aggregate-functions/reference/topk/">topK()</a> function, which does the same in a much more efficient way:</p><pre>SELECT <strong>topK(5)(a)</strong> FROM test</pre><p>This will return 5 most popular values from a column, but more importantly it takes 10x less time to execute:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1004/1*0S4pWIjiEovqDvejhD5kFw.png" /></figure><h3>Variance</h3><p>Variance helps us understand the scale of deviation of values in a dataset. We can calculate that in Clickhouse as well:</p><pre>SELECT <strong>varPop(a)</strong>, <strong>varPop(c)</strong>, <strong>varPop(d)</strong> FROM test</pre><p>Which gives us the variance of 3 specified columns:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/592/1*FBYzVWeeI-qtRrGor4F5zA.png" /></figure><p>Clickhouse uses the following formula to calculate variance:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZoBencEhDZq0momQqYaJAQ.png" /></figure><h4>Covariance</h4><p>We can also calculate covariance of 2 columns:</p><pre>SELECT covarPop(c,d) FROM test</pre><p>Which returns:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/200/1*5nY5DU_DihZAuT7mGBzmyA.png" /></figure><p>The following formula is used for calculations here:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N9vKsNyVspEyICVePeVqmw.png" /></figure><h3>Summary</h3><p>Clickhouse math and aggregation functions can be used to do initial explaratory data analysis on large datasets. Clickhouse uses optimized approximation algorythms for some calculations (like uniques or quantiles) to help get faster results, which are usually ok during initial analysis.</p><p>List of popular functions used for data exploration:</p><ul><li>min(), max(), avg() and median() for most basic stuff,</li><li>avgWeighted(col, weights) to calculate average with weights,</li><li>quantile(level)(col) to get quantiles,</li><li>histogram(bins)(col) and bar() to plot histogram,</li><li>skewPop(col) to understand how symmetric data is,</li><li>uniq(col) for approximate (but fast) uniques calculation,</li><li>topK(N)(col) to find most popular N values,</li><li>varPop(col) to estimate values deviation.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=227f4a1dad3c" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/big-data-exploratory-data-analysis-with-clickhouse-227f4a1dad3c">Big data exploratory data analysis with Clickhouse</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data cleansing and preparation for analysis with Python and Pandas]]></title>
            <link>https://medium.com/datadenys/data-cleansing-and-preparation-for-analysis-with-python-and-pandas-d93d75db5e79?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/d93d75db5e79</guid>
            <category><![CDATA[data-cleansing]]></category>
            <category><![CDATA[data-cleaning]]></category>
            <category><![CDATA[data-preparation]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[pandas]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Wed, 02 Nov 2022 15:53:12 GMT</pubDate>
            <atom:updated>2022-11-02T15:53:12.392Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YUqe0TOiosTxg7UF71GMVQ.png" /><figcaption>Pandas data cleansing</figcaption></figure><p>Any data usually (always?) contain errors. In order to do accurate analysis and build efficient ML models, data needs to be cleansed prior to digging into it. Python Pandas has powerfull tools for data cleansing and further preparation for modeling. Let’s see how that’s done…</p><h3>Test dataset</h3><p>We’re going to use <a href="https://github.com/practiceprobs/datasets/tree/main/netflix-titles">Netflix titles dataset</a>, which contains 8.8k rows:</p><pre>import pandas as pd</pre><pre>df = pd.read_csv(&#39;<a href="https://raw.githubusercontent.com/practiceprobs/datasets/main/netflix-titles/netflix-titles.csv&#39;">https://raw.githubusercontent.com/practiceprobs/datasets/main/netflix-titles/netflix-titles.csv&#39;</a>)<br>print(df.head())</pre><h3>Columns management</h3><p>It’s a good idea to check available columns as a first step of data exploration:</p><pre>print(df.columns)</pre><p>Which gives us the list of all columns in the dataset:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/723/1*Tyn4lgtjjPdQ8CJM5-7NAw.png" /></figure><h4>Renaming columns</h4><p>We can change names ofcolumns (e.g. for cases of unclear titles):</p><pre>df.rename(columns = {&#39;<strong>show_id</strong>&#39;: &#39;<strong>id</strong>&#39;}, <strong>inplace=True</strong>)</pre><p>Here, we rename show_id column to id and use inplace=True to overwrite our existing dataframe object instead of creating new one.</p><h4>Dropping columns</h4><p>Removing unncecessary or unclear data is also important, and sometimes we need to drop an entire column:</p><pre>df.drop([&#39;<strong>description</strong>&#39;, &#39;<strong>listed_in</strong>&#39;], <strong>axis=1</strong>, inplace=True)</pre><p>Here we remove description and listed_in columns (axis=1 is required to remove a column instead of a row). If we need to extract only some columns out of many, we can use a shorter option:</p><pre>df = df[[&#39;id&#39;, &#39;type&#39;]]<br>print(df)</pre><p>Which will include only id and type columns in our final dataset:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/216/1*3DzUxQiBcE_F0gm6HvGOTQ.png" /></figure><h3>Values management</h3><p>We discover a lot of unclear or broken data during exploratory anaysis in many cases. Sometimes errors are obvious and can be easily fixed by manipulating values.</p><h4>Updating and creating values</h4><p>Values can be easily updated using combination of filtering and manipulating operators:</p><pre>df.<strong>loc</strong>[<strong>df[&#39;type&#39;] == &#39;Movie&#39;</strong>, &#39;<strong>type</strong>&#39;] = &#39;<strong>M</strong>&#39;</pre><p>Here we’ve replaced all Movie values of type column with M value. We can also use math operators as well as functions. Let’s create new column based on calculations from exsiting values:</p><pre>df[&#39;<strong>century</strong>&#39;] = df[&#39;<strong>release_year</strong>&#39;]/100+1</pre><p>We’ve created new century column based on release_year column value and some math:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/952/1*bOa03u3M7TLAsid26mAt9w.png" /></figure><h4>Replacing text</h4><p>Text replacement can help fix grammar errors or reduce values variance:</p><pre>df[&#39;title&#39;] = df[&#39;<strong>title</strong>&#39;].str.replace(&#39;<strong>World War</strong>&#39;, &#39;<strong>WW</strong>&#39;)</pre><p>Here, we have replaced all World War strings in title columns with WW. In order to find values containing certain [sub]strings:</p><pre>print(df[df[&#39;title&#39;].str.contains(&#39;WW&#39;)])</pre><p>Which gives us the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/820/1*R-on2CKMCADp8VPoywMzlQ.png" /></figure><h4>Updating NaN values</h4><p>Undefined values are better to overwrite with meaningful values where possible:</p><pre>df[&#39;cast&#39;] = df[&#39;cast&#39;].fillna(&#39;Nobody&#39;)</pre><p>Now our cast column has Nobody values instead of NaN:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/534/1*Rsaxfy1pNS8QwCoS5gjmPQ.png" /></figure><h4>Removing/filtering rows</h4><p>There are cases, when some rows are meaningless or too corrupted to use in analysis, so it’s better to get rid of them:</p><pre>df.drop(df.loc[df[&#39;<strong>release_year</strong>&#39;] &lt; <strong>1950</strong>].index, inplace=True)</pre><p>Here we’ve removed all rows where release_year value is less than 1950. Shorter form for the same is also available:</p><pre>df = df[<strong>df[&#39;release_year&#39;] &gt;= 1950</strong>]</pre><p>So we’ve just filtered all “correct” values and overwritten our df data frame object.</p><h3>Managing data types</h3><p>Sometimes we need to convert between different types. In order to check column types we can use dtypes property of data frame:</p><pre>print(df.<strong>dtypes</strong>)</pre><p>Which shows types for all columns in our data frame. We find out that century column is of float64 type:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/220/1*4OZXWmWMzSOtff2wF4og5Q.png" /></figure><p>So let’s convert that column to integer:</p><pre>df[&#39;century&#39;] = df[&#39;century&#39;].<strong>astype(&#39;int&#39;)</strong></pre><p>And we can check dtypes again to make sure column type has changed:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/246/1*yPgCw8iiDjGA07xV1FyXxA.png" /></figure><h3>Managing duplicates</h3><p>Finding and removing duplicate rows is one of the critical steps during data cleansing, as duplicates can dramatically damange modeling and analysis itself. Finding duplicates is as easy as:</p><pre>print(df[df.<strong>duplicated</strong>()])</pre><p>Which will output a set of rows that have duplicates (all columns have the same value) in the dataset. Single duplicate row has been found in our case:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/864/1*i9fORQm4H-q7Erl51-Fnug.png" /></figure><p>We can also find duplicates based on specific columns (instead of all columns), e.g. to find duplicate title column values:</p><pre>print(df[df[&#39;<strong>title</strong>&#39;].duplicated()])</pre><p>In order to drop duplicate rows and leave only single unique row out of multiple:</p><pre>df.<strong>drop_duplicates</strong>(inplace=True)</pre><p>This will overwrite df dataframe with only unique rows from original dataset.</p><h3>Convert categorical columns to numeric</h3><p>If we plan to use non numeric columns in ML models, we might want to encode those into numeric values. There are couple of ways to do it.</p><h4>One hot encoding</h4><p>One option is to use one hot encoding, which will create multiple binary columns with ones and zeros:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*B3SVG3-M-x3rZ6tPnTJvQw.png" /></figure><p>So we literaly take all unique values from a text column and convert that list to [additional] columns. Then we fill those columns with 1 where column name is the same as it’s [text] value in a row. And put 0 in all other cases. Those new columns are also called binary, because they can have one of two possible values — 1 or 0.</p><p>Let’s encode type column:</p><pre>df = df.join(pd.<strong>get_dummies</strong>(df.<strong>type</strong>, <strong>prefix=&#39;Type&#39;</strong>))<br>df.drop(&#39;type&#39;, axis=1, inplace=True)</pre><p>Here, we have created binary columns prefixed with Type from values in type column. We’ve also added those new column to our dataset using join and then dropped old type column from dataset. We can see new columns now:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/850/1*qpF15gdrqT8jFCkY1uysfg.png" /></figure><h4>Converting text values to IDs</h4><p>Another approach to get numeric values out of text is to just convert to IDs, generated for those (unique) values:</p><pre>df[&#39;rating&#39;] = pd.<strong>factorize</strong>( pd.Series(df[&#39;<strong>rating</strong>&#39;]) )[0]</pre><p>Here, we’ve used factorize to generate IDs based on rating column values. Then we’ve written results to rating column again, which is now numeric:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/954/1*6kc0EYnN5PAC0gVukwiDzg.png" /></figure><h3>Summary</h3><p>Pandas module has powerful tools to cleanse and prepare data for further analysis and modeling which may dramatically improve its accuracy. Cleansing usually includes the following:</p><ul><li>Meaningless columns removal using df.drop();</li><li>Erroneous values updates using df.loc[];</li><li>Picking correct data types for columns using df[&#39;col&#39;].astype(&#39;type&#39;);</li><li>Duplicates removal using df.drop_duplicates() ;</li><li>Text to numeric values encoding (e.g. using one hot encoding).</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d93d75db5e79" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/data-cleansing-and-preparation-for-analysis-with-python-and-pandas-d93d75db5e79">Data cleansing and preparation for analysis with Python and Pandas</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimizing star-schema queries with IN queries and denormalization]]></title>
            <link>https://medium.com/datadenys/optimizing-star-schema-queries-with-in-queries-and-denormalization-cc281bbe19a5?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/cc281bbe19a5</guid>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[star-schema]]></category>
            <category><![CDATA[sql-joins]]></category>
            <category><![CDATA[query-optimization]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Mon, 31 Oct 2022 15:14:32 GMT</pubDate>
            <atom:updated>2022-10-31T15:15:53.439Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b5XGJIHgIYOC8BJhH7X4nw.png" /><figcaption>Clickhouse Star Schema</figcaption></figure><h3>Optimizing star-schema queries with IN queries and denormalization in Clickhouse</h3><p>Most data environments usually have 2 main groups of data objects. First — event based objects, usually organized into timeseries tables. Second — instances (with properies/features), usually organized into ID-based tables (and stored in row-based storages). This is what called star (or normalized) schema. This stucture is storage efficient, but can result in slow read performance. In some cases denormalization can be used to make things faster, let’s see how.</p><h3>Star schema</h3><p>Let’s see how Clickhouse performs using joins on sample star-schema data. We have 2 tables — log (timeseries events) and users (instances with features):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*GwnSdcNsb_l1pB00w6Mk3g.png" /></figure><p>We have log events table which refers users table using user_id column. Events log table has 250m rows and the following structure:</p><pre>CREATE TABLE <strong>log</strong> (dt DateTime, <strong>user_id</strong> UInt64, session_id UInt64, platform String, label String, url String)<br>ENGINE = MergeTree ORDER BY (dt, user_id, session_id)</pre><p>It also worth mentioning that features tables are usually mutating a lot (data gets added, removed and updated) while events tables are historically static (data is only appeneded but not changed). To keep things simple for now, we have users table in Clickhouse as well:</p><pre>CREATE TABLE <strong>users</strong><br>(`<strong>id</strong>` UInt64, `name` String, `category` String, `age` UInt8 )<br>ENGINE = MergeTree ORDER BY (category, id)</pre><h3>Star schema Join</h3><p>Let’s try real world example — counting events from users of a certain category and age within specific period of time:</p><pre>SELECT date(dt) AS day, count(*) FROM <strong>log l</strong><br><strong>JOIN</strong> <strong>users u</strong> ON u.id = l.user_id<br>WHERE<br>  <strong>u.category</strong> = &#39;aa&#39; AND <strong>u.age</strong> &gt; 50 AND <strong>dt</strong> &gt; NOW() - INTERVAL 1 YEAR<br>GROUP BY day ORDER BY day DESC LIMIT 5</pre><p>While our log table has 250 million rows, our users table has around 25 million rows. So previous query results in the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1015/1*nLyrutq5Kx_Lffn50kMXpw.png" /></figure><p>Doesn’t seem very fast, let’s check execution details using EXPLAIN:</p><pre>EXPLAIN indexes = 1, json = 1 SELECT date(dt) ...</pre><p>This will bring large response, but the most important stuff is following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/963/1*-mFhF4hdOIYHDJoTNjwSKg.png" /></figure><p>This is because JOINs in Clickhouse don’t exactly work like we used to with other databases (separate article on the soon).</p><h3>Using IN instead of Joins</h3><p>In most cases we can easily switch to IN instead of JOIN. We should ensure nested table doesn’t return too much data, so Clickhouse doesn’t exceed available memory. E.g. in our case, filtering on users table returns only 1k someting of rows, which is cool:</p><pre>SELECT count(*) FROM users WHERE (category = &#39;aa&#39;) AND (age &gt; 50)</pre><pre>┌─count()─┐<br>│    <strong>1122</strong> │<br>└─────────┘</pre><p>Using IN is easy in our case and will be much more efficient:</p><pre>SELECT date(dt) AS day, count(*) FROM log<br>WHERE <strong>user_id IN</strong> (<br>  SELECT id FROM users WHERE (category = &#39;aa&#39;) AND (age &gt; 50)<br>) AND (dt &gt; (NOW() - toIntervalYear(1)))<br>GROUP BY day ORDER BY day DESC LIMIT 5</pre><p>Which brings in dramatically better results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/993/1*3DCYVV46ahXgKOHsDDQrgw.png" /></figure><p>Although this can work in most cases we have to make sure IN expression sometimes it doesn’t result in sufficient speed improvements. Denormalizing data can be considered in those cases.</p><h3>Denormalizing data</h3><p>Denormalizing data basically means creating single table from multiple joined tables. Let’s create and populate denormalized table in our case:</p><pre>REATE TABLE log_users (<br>  `dt` DateTime, `user_id` UInt64, `session_id` UInt64,<br>  `platform` String, `label` String, `url` String,<br>  `user_name` String, `user_category` String, `user_age` UInt8<br>)<br>ENGINE = MergeTree<br>ORDER BY (dt, user_id, session_id, user_category, platform, label)</pre><p>As you see, we’re now able to use more sophisticated sorting key, so more querying cases are covered. Populating takes some time:</p><pre>INSERT INTO log_users<br>SELECT l.*, u.name, u.category, u.age<br>FROM log l JOIN users u ON (l.user_id = u.id)</pre><p>But now we can see our initial query performs much better:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1007/1*CMNlKwaoFK7vpJNnk5c5Fg.png" /></figure><p>Obviously, having everything in a single table, gives us powerfull optimization tools including better tuned <a href="https://medium.com/datadenys/improving-clickhouse-query-performance-tuning-key-order-f406db7cfeb9">sorting key</a> and <a href="https://medium.com/datadenys/using-projections-to-speedup-queries-in-clickhouse-cd58e393b1cd">projections</a>. But don’t forget that you have to pay with additional disk space for this solution.</p><h4>Real time denormalization</h4><p>Previous example is good if you have enough time to wait while denormalized table population occurs. But we actually can get real time denormalized data by using MATERIALIZED VIEW solution:</p><pre>CREATE <strong>MATERIALIZED VIEW</strong> <strong>log_users_rt</strong> (<br>  `dt` DateTime, `user_id` UInt64, `session_id` UInt64,<br>  `platform` String, `label` String, `url` String,<br>  `user_name` String, `user_category` String, `user_age` UInt8<br>)<br>ENGINE = MergeTree ORDER BY (dt, user_id, session_id) <strong>AS<br>SELECT</strong> l.*, u.name AS user_name, u.category AS user_category,<br>       u.age AS user_age<br>FROM default.log AS l INNER JOIN users AS u ON l.user_id = u.id</pre><p>Here, we ask Clickhouse to do the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TLzWxR3v3kWraic5sg6SWA.png" /></figure><p>So Clickhouse will automatically join fresh portion of log table data with users table data and store enriched (denormalized) data into log_users_rt materialized view.</p><p>Note that Clickhouse will now execute JOIN on users table for <strong>each</strong> INSERT query, so we might need to tune periods and packet size of insertions:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*O5gpPEhWXaaktwWonVtH7g.png" /></figure><p>Insert time in our case is 4 seconds, so we have to execute inserts in larger batches and longer periods (e.g. each 10 seconds or so).</p><h3>Using data from Mysql for feature tables</h3><p>The cool thing, that queries with IN can use MySQL engine tables, so you actually have Mysql manage features data (which it does pretty good, since we deal with updates/deletes on primary key in feature tables).</p><p>We just have to create MySQL engine table:</p><pre>REATE TABLE <strong>users_mysql</strong><br>(`id` UInt64, `name` String, `category` String, `age` UInt8 )<br>ENGINE = <strong>MySQL(&#39;127.0.0.1:3306&#39;, &#39;db&#39;, &#39;users&#39;, &#39;usr&#39;, &#39;pwd&#39;)</strong></pre><p>We assume our Mysql server has users table in db database of the same structure. Now we can use it for our query:</p><pre>SELECT date(dt) AS day, count(*) FROM log<br>WHERE (user_id IN (<br>  SELECT id FROM <strong>users_mysql</strong> WHERE <strong>category</strong> = &#39;aa&#39; AND <strong>age</strong> &gt; 50<br>)) AND (dt &gt; (NOW() - toIntervalYear(1)))<br>GROUP BY day ORDER BY day DESC LIMIT 5</pre><p>Note, that nested query is executed on Mysql side, so creating following index (for Mysql users table) should be used for our case:</p><pre>CREATE INDEX c_a ON <strong>users(category, age)</strong></pre><p>We’d better not use JOINs here at all, because Clickhouse will first offload data from MySQL and only then — filter it (again, separate article on JOIN is in plans already).</p><h3>Summary</h3><p>Using IN for star-schema queries performs quite well in most cases in Clickhouse. Still, data denormalization can be considered to improve speed even further. Materialized views can be used to denormalize data on ingestion stage, so denormalized data is accessible in real time. But note, that this will bring in additional disk space usage and downgrade insert performance.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cc281bbe19a5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/optimizing-star-schema-queries-with-in-queries-and-denormalization-cc281bbe19a5">Optimizing star-schema queries with IN queries and denormalization</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using projections to speedup queries in Clickhouse]]></title>
            <link>https://medium.com/datadenys/using-projections-to-speedup-queries-in-clickhouse-cd58e393b1cd?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/cd58e393b1cd</guid>
            <category><![CDATA[sql-optimization]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[query-optimization]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Fri, 28 Oct 2022 16:54:29 GMT</pubDate>
            <atom:updated>2022-10-28T16:54:29.430Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OxM-ivDA42O43_yQmT_tjA.png" /></figure><p>Clickhouse is efficient enough so most analytical queries will execute fast in many cases without extra optimization activities. But, if you have frequent queries that require a lot of resources for execution, it’s a good idea to help Clickhouse find more efficient ways to execute those. First of all, make sure your <a href="https://medium.com/datadenys/improving-clickhouse-query-performance-tuning-key-order-f406db7cfeb9">primary key is fine tuned</a>. After that, consider using projections to speedup certain queries.</p><h3>Reasons queries are slow</h3><p>Let’s imagine we have the following table:</p><pre>CREATE TABLE test ( <strong>a</strong> UInt32, <strong>b</strong> UInt32, <strong>c</strong> UInt32, <strong>d</strong> UInt32 )<br>ENGINE = MergeTree <strong>ORDER BY (a, b)</strong></pre><p>Primary key will be used to efficiently execute queries on a and b columns (or any combination of):</p><pre>SELECT count(*) FROM test WHERE <strong>a = 1000</strong></pre><p>This query will use primary key to optimize scanning:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/980/1*H4tsM0sm_nr2Jcd5aCz0hQ.png" /></figure><p>But filtering on d or c will lead to a full table scan:</p><pre>SELECT count(*) FROM test WHERE <strong>c = 1000</strong></pre><p>Which results in the following (our test table has 100 million ows):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1004/1*gS9UnTJphxoW-YjY35S87A.png" /></figure><p>This happens, because our table data is only sorted by a+b columns (and stored in that way):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*C3ujUF547b1URsLtsf9F0Q.png" /></figure><p>Because c column is out of primary key, Clickhouse can’t help but scan the entire table to find what we’re looking for.</p><h3>Using projections</h3><p><a href="https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/mergetree/#projections">Projections</a> can be used to automatically build additional (usually) sorted datasets for tables, that can be used by Clickhouse to execute specific queries faster. So basically projection is a kind of a row db index, but more flexible.</p><p>To create projection on a table we should specify expression to build it on:</p><pre>ALTER TABLE test ADD projection <strong>c </strong>(<strong>SELECT c ORDER BY c</strong>);<br>ALTER TABLE test MATERIALIZE projection <strong>c</strong>;</pre><p>Here we’ve created created a projection with c name for the SELECT c ORDER BY c expression.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1IniARXOl6EECd1Crf7hQQ.png" /></figure><p>So now Clickhouse has all values from c column sorted and stored in a separate structure, called projection. If we create projection on a table with data, we need MATERIALIZE operation to build projection for existing data. Newely arrived data will be automatically processed and stored in projection.</p><p>Let’s check previous query after projection was created:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1022/1*IcJS39vMWSmmmiTLGy_pcA.png" /></figure><p>As we can see, our query became dramatically faster and ~1000x less resources consuming.</p><h4>Picking projections for queries</h4><p>We can have any [reasonable] number of projections per table. Clickhouse will try to pick best projection for each query. Say we have the following query:</p><pre>SELECT count(*) FROM test WHERE <strong>c = 1000</strong> AND <strong>d &gt; 1000</strong></pre><p>Again, Clickhouse has to scan entire data set to find the answer:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1014/1*Kr0qdZnlMsMXCjO5bL5duQ.png" /></figure><p>We can create the following projection to speed things up in this case:</p><pre>ALTER TABLE test ADD projection <strong>d_c </strong>(<strong>SELECT c, d ORDER BY c, d</strong>);<br>ALTER TABLE test MATERIALIZE projection <strong>d_c</strong>;</pre><p>Now query is executed in a much more efficient way:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1021/1*bKsfCLa5-a-hb7Q_sch1rw.png" /></figure><p>Good thing here is that d_c projection will also cover cases for filtering on c column only (as it comes first in <strong>ORDER BY c, d </strong>expression). Let’s first remove c projection:</p><pre>ALTER TABLE test DROP PROJECTION c</pre><p>Now, let’s make sure d_c projection is used for our query:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1021/1*AUT18b45LZpt6ByJsNysKA.png" /></figure><p>By using EXPLAIN we can check which projection is used for query:</p><pre><strong>EXPLAIN json = 1, indexes = 1</strong> SELECT count(*) FROM test WHERE c = <br>1000</pre><p>Which gives us the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/836/1*NgLuCVZrfo-AIqgjnITezw.png" /></figure><h4>Aggregate projections</h4><p>Cool thing about projections is that we can use aggregates and grouping. Let’s see how the following query performs (we still have d_c projection in table):</p><pre>SELECT count(*), c FROM test WHERE <strong>c &gt; 10 AND c &lt; 20</strong> <strong>GROUP BY c</strong></pre><p>We can see that Clickhouse had to scan something like the amount of collapsed rows under aggregation (9 *23.3k = 201k while 295k was scanned which is pretty close):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1007/1*Jw0ZWgB6G2teBN7UPqEWWA.png" /></figure><p>But we can actually create aggregated projection to improve things:</p><pre>ALTER table test add projection <strong>c_count</strong> (<strong>SELECT count(*), c GROUP BY c</strong>);<br>ALTER TABLE test MATERIALIZE projection <strong>c_count;</strong></pre><p>Now the query is ~8 times more efficient since new projection is used:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1001/1*nxxBILH2JRX-7Swj5RYJEA.png" /></figure><p>Here our projection stores aggregated data on the fly (using <a href="https://clickhouse.com/docs/en/engines/table-engines/mergetree-family/aggregatingmergetree/">AggregatingMergeTree</a> backend):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*tj7vtj-S3V7C1m8tEBj0tg.png" /></figure><p>And actually, processed rows value is only that big (43k) due to default granule size of 8k, meaning Clickhouse reads at least 8k rows per key hit.</p><h4>Picking columns for projection</h4><p>Let’s look at the following query:</p><pre>SELECT count(*), <strong>max(b)</strong> FROM test WHERE c = 1000</pre><p>Since we have projections on c column, but not on d column, Clickhouse will still have to read the entire table to execute this query:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1009/1*WpKAVzOwM-NRq4_no2dQEQ.png" /></figure><p>Which is obviously bad and can be fixed by creating more relevant projection:</p><pre>ALTER TABLE test ADD projection <strong>c_b (SELECT max(b), count(*) GROUP BY c)</strong>;<br>ALTER TABLE test MATERIALIZE projection <strong>c_b</strong>;</pre><p>Now our query performs much better:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1009/1*kisuo38va805ezAyM8B7Tw.png" /></figure><p>This is important as this means we have to add <strong>all</strong> <strong>columns</strong> to projection that are used in the query. This is different from row-based databases indexes, which referrence entire rows. But columnar database doesn’t even know what row is, that’s why <strong>projection != index</strong>. Projection is just some part of the original table data stored in a different form, which is better suitable for certain queries.</p><h3>Before speeding up [all] slow queries</h3><p>Having “slow” [analytical] query is not always as bad, as wasting resources on supporting additional structures (like projections) at the write-time. If the query is executed couple of times per day and it takes couple of minutes (or even hours) to generate results… Do you need to speed it up? If the table structure is ok and <a href="https://medium.com/datadenys/how-clickhouse-primary-key-works-and-how-to-choose-it-4aaf3bf4a8b9">primary key is tuned</a> (which already means it serves most of querying cases best), maybe there’s a better way to handle some of slow queries, like scheduled precaching (so UI is fast)?</p><p>Why consider such things when we can just make all queries fast? Read-time optimization always comes with write-time overhead. E.g. let’s compare how throughput changes when we feed data into original table with no projections and to a table with 3 projections. We’ll insert 50 million rows into each table:</p><pre>INSERT INTO test SELECT<br>    round(number / 100000),<br>    round(number / 100),<br>    round(rand32() / 1000000),<br>    round(rand32() / 1000)<br>FROM numbers(50000000)</pre><p>Original table was filled in 3 seconds:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1010/1*TZ0NsbtDdWtQQ0LEwjqsIQ.png" /></figure><p>But the table with projections took Clickhouse 7x time to fill with the same 50 million rows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1010/1*8z7Gr_l2bVxTE5reHLviGQ.png" /></figure><p>It doesn’t mean we should never use projections. Instead we need to carefully analyze both ends (read and write performance) to make sure the <em>whole system is performant instead of a single query</em>. So the best candidate for optimization is usually a query that is frequent (popular) enough to create negative impact on the whole system.</p><p>From this perspective it is more important to optimize query that takes 1 second but is being executed <strong>every couple of seconds</strong> than 5-minutes-long query that’s executed <strong>once a day</strong>.</p><h3>Summary</h3><p>Projections can be used to improve query performance when primary key can’t handle it. In order to use projection we only need to create it and Clickhouse will automatically pick it up for relevant queries:</p><pre>ALTER TABLE tbl ADD projection <strong>p1 </strong>(<strong>SELECT col1 ORDER BY col1</strong>);</pre><p>Projections can be created on expressions as well using functions and aggregations:</p><pre>ALTER TABLE tbl <br>ADD projection <strong>p2 </strong>(<strong>SELECT col2, count(*), MAX(col1) GROUP BY col2</strong>);</pre><p>Note, that each projection will downgrade data ingestion performance, so use projections in cases when system is highly affected by slow queries. In other cases consider using (pre)caching or asking your analyst to be patient :)</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=cd58e393b1cd" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/using-projections-to-speedup-queries-in-clickhouse-cd58e393b1cd">Using projections to speedup queries in Clickhouse</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Working with JSON in Clickhouse]]></title>
            <link>https://medium.com/datadenys/working-with-json-in-clickhouse-32e41ac0ff86?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/32e41ac0ff86</guid>
            <category><![CDATA[analytics-database]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[json]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Wed, 26 Oct 2022 17:01:45 GMT</pubDate>
            <atom:updated>2022-10-26T17:01:45.072Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*PNNd0CW6s6NfWvEkIo8fTA.png" /><figcaption>Clickjhouse JSON</figcaption></figure><p>There’s plenty of cases when we can’t define data structure in advance due to its dynamic nature. Data objects can have different set of features from time to time or depending on other circumstances. In such cases we can use JSON to save dynamic data and Clickhouse provides handy tools to work with that.</p><h3>Storing JSON data</h3><p>The simplest (and single as of now) way to store JSON object is to create String typed column and store JSON text representation inthere:</p><pre>CREATE TABLE test_string ( `t` DateTime, <strong>`v` String</strong> )<br>ENGINE = MergeTree ORDER BY t</pre><p>We’re going to save JSON data under v column:</p><pre>INSERT INTO test_string VALUES(now(), <strong>&#39;{&quot;name&quot;:&quot;Joe&quot;,&quot;age&quot;:95}&#39;</strong>)</pre><p>Clickhouse provides several functions to work with JSON data, including validating JSON data itself.</p><h4>Validating and checking keys</h4><p>We can simply check if JSON is valid using isValidJSON function:</p><pre>SELECT v, isValidJSON(v) FROM test_string</pre><p>Which returns 1 (or 0) if JSON is valid (or not):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/735/1*MHrBLeOY5Cht4qz42xSjmA.png" /></figure><p>We can also check if object contains certain key (or any of given keys) which is useful during data cleansing procedures:</p><pre>SELECT<br>    <strong>JSONHas(v, &#39;name&#39;) AND JSONHas(v, &#39;rating&#39;)</strong> AS is_valid,<br>    count(*)<br>FROM test_string GROUP BY <strong>is_valid</strong></pre><p>Here we require our JSON field to have name and rating keys defined to be marked as valid:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/733/1*9onYYDRb16BYGfV1Vgrn4Q.png" /></figure><p>As we can see, our table contains 2 invalid values.</p><h4>Extracting values</h4><p>In most cases we want to operate with JSON attributes (keys) values, which can be done in multiple ways. First of all there is a function to extract typed values based on key names:</p><pre>SELECT<br>    <strong>JSONExtract</strong>(v, &#39;name&#39;, &#39;String&#39;),<br>    <strong>JSONExtract</strong>(v, &#39;rating&#39;, &#39;UInt32&#39;)<br>FROM test_string LIMIT 5</pre><p>Here we extract name key value as string and rating key value as [unsigned 4 bytes] integer from v column value (which stores JSON):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/662/1*a73VQXMQZOO8MFydHf7LFw.png" /></figure><p>We can extract scalar types (like String, Int/UInt* or Float*) or complex structures, like Array or Tuple (or combinations of any):</p><pre>SELECT JSONExtract(&#39;{&quot;val&quot;: [1,2,3,4]}&#39;, &#39;val&#39;, &#39;Array(UInt8)&#39;)<strong>[2]</strong></pre><p>Which will return second integer value from val array (which is a JSON key):</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/696/1*7JkGCXgPQZ_4ELVW0EJwdQ.png" /></figure><p>Another option to extract value (without having to think about types) is to use JSON_VALUE function:</p><pre>SELECT <strong>JSON_VALUE(v, &#39;$.name&#39;)</strong> FROM test_string LIMIT 5</pre><p>Which, again, will simply list value of name key of v JSON column:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/244/1*_hKtmMYxaVCHlbAJVqJruw.png" /></figure><h4>Using JSON key values for indexing</h4><p>As we know, Clickhouse allows using function to build sorting keys based on. That’s why we can use JSON extraction functions in indexes to optimize certain queries:</p><pre>CREATE TABLE test_index ( `t` Int64, `v` String )<br>ENGINE = MergeTree <strong>ORDER BY JSONExtractUInt(v, &#39;rating&#39;)</strong></pre><p>Here, we’ve used extracted rating key value as a sorting key, so the relevant queries will be quite efficient:</p><pre>SELECT count(*) FROM test_index<br>WHERE <strong>JSONExtractUInt(v, &#39;rating&#39;) = 140970729</strong></pre><p>Clickhouse will answer in the very efficient way in this case since <a href="https://medium.com/datadenys/how-clickhouse-primary-key-works-and-how-to-choose-it-4aaf3bf4a8b9">it uses index</a> for that:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1021/1*IZX0cCi3z_RdKRnGRGcuZQ.png" /></figure><p>On the contrary, if we filter on the JSON key which is not under index, full table scan follows. For example, the same query on the same table/data, but with a different sort index results in the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1021/1*XxREv2tzbkb4HFhAIl6TYg.png" /></figure><p>250x rows scanned, 250x RAM used and 35x time spent. Not good.</p><h4>JSON keys or separate columns?</h4><p>If you know your JSON field is going to have exactly certain keys of exactly certain types, <em>it is better to move them out of JSON value into separate columns before writing to Clickhouse</em>. Doing so will save space (because of saving data using relevant types instead of strings) and improve performance (since no JSON extraction overhead will happen).</p><h3>Experimental JSON object type</h3><p>Clickhouse has experimental <a href="https://clickhouse.com/docs/en/sql-reference/data-types/json/">JSON type</a> which is too early to consider for production, but let’s take a look at that. Since the feature is experimental we have to:</p><pre>SET allow_experimental_object_type = 1</pre><p>Now we can use JSON type while creating tables:</p><pre>CREATE TABLE test_json ( `t` DateTime, <strong>`v` JSON</strong> )<br>ENGINE = MergeTree ORDER BY t</pre><p>First cool thing about this type, is that we can directly use object notation in queries:</p><pre>SELECT <strong>v.name, v.rating</strong> FROM test_json LIMIT 5</pre><p>And Clickhouse knows what to do:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/212/1*p1hDx7HRTcxBmpfnPcxLiQ.png" /></figure><p>Second cool thing about this type is that it takes less space (~30% saved in our case) than String field usage:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/757/1*R3V1jmIKqP4k1WHLlNbsOA.png" /></figure><p>But one downside here is the speed of inserting data. If we compare insertion speed of the same data into String and JSON columns we can see almost 10 fold difference:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y-O27LPm3Vy8As1GJhQYfg.png" /></figure><p>Anyway, this is still experimental, so hopefully Clickhouse team will document (in details) and release this soon.</p><h3>Summary</h3><p>Working with JSON in Clickhouse is as simple as using String field for data and then using set of JSON* functions to check and extract key values from JSON fields:</p><pre>SELECT <strong>JSONExtract</strong>(json_col, &#39;key&#39;, &#39;String&#39;) FROM table</pre><p>We can use extraction functions as indexes (or part of indexes) to improve query performance, but consider moving those to separate columns instead and leave only dynamic set of keys for storing in JSON columns.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=32e41ac0ff86" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/working-with-json-in-clickhouse-32e41ac0ff86">Working with JSON in Clickhouse</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Solving systems of linear equations using matrices and Python]]></title>
            <link>https://medium.com/datadenys/solving-systems-of-linear-equations-using-matrices-and-python-5e203a9ea146?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/5e203a9ea146</guid>
            <category><![CDATA[gaussian-elimination]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[inverse-matrix]]></category>
            <category><![CDATA[linear-algebra]]></category>
            <category><![CDATA[numpy]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Wed, 19 Oct 2022 17:17:15 GMT</pubDate>
            <atom:updated>2022-10-19T17:17:15.269Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*54HqL2iCOE9gbTcyWbvBxQ.png" /></figure><p>Matrices stay at the very basis of all math used for ML. Let’s understand why it is so and how matrices can be used to solve systems of linear equations from perspective of 2 different methods.</p><h3>Systems of linear equations in ML</h3><p>Standard task that ML methods solves can be generally illustrated as:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*KzDERmsOaLGoWpkCqRaUMg.png" /></figure><p>We deal with a set of features, having a “table” with feature values and target value combinations. We have target value defined for a subset of data we can use to learn on. After learning is done, we can then apply our findings to get results for a dataset with unknown target value:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bbqRwROZ0EzIKiuZnPcikA.png" /></figure><p>As we remember from Math, line function can be represented as:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bRQjssUfQDh3DRudmLtACQ.png" /></figure><p>This is a single dimension example, and for 2 and more dimensions:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*i1f1PHapPwPaqQKt7dz4cQ.png" /></figure><p>Now if we treat our x1, x2 and so on, as our features and f as a target value, we can rewrite our initial learning dataset into the form of linear equations:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4_9H1b-LjOwd13ylWSxrsw.png" /></figure><p>And now we basically only need to write that down as a system of linear equations and solve that:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XMdlkM-i9CwbTN6hlGs7Gg.png" /></figure><p>After solving that, we get specific values of a1, a2 … (also called weights), that we will use in our linear function to find unknown target values (f) with known feature values (x). And this final function with calculated weights is what called an <strong>ML model</strong>.</p><h4>Real live note</h4><p>As you may have noticed, this sounds too easy to be a part of powerful ML industry. In fact, this is the <em>ideal case</em>. In real live you never have enough data to be able to build ideal model, that’s why you end up with either infinite solutions or no solutions at all using this approach. In practive, best you can do, is get a solution that is close to ideal solution. But, also in practice, this is more than enough in most cases.</p><p>Still ML engineers deal with systems of linear equations but on a different level. Instead of solving direct target value function of features equations, we solve equations that help us minify so called error (distance between real target value and calculated one based on given weights). E.g. if we use MSE based on Euclidean norm:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EUfPx6JQg2VJGSk5HKC0rg.png" /></figure><blockquote>I will write more on linear regression and error function optimization later, and for now let’s forget about it and return to equation systems and how matrices can be used to solve them.</blockquote><h3>Matrix form of linear equations system</h3><p>So how matrices are related to systems of linear equations? Say we have the following system of linear equations to solve:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S8D19dXdNxhpBgHqsQAP9w.png" /></figure><p>Now let’s build a matrix of weights (those numbers before our x1…3) and two vectors — X and target values Y:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*LcHBxXowN4_DMyTqkxu4uQ.png" /></figure><p>As we know, <a href="https://medium.com/datadenys/matrices-and-vectors-basic-math-for-ai-with-python-examples-ed06d7da01ac#df0d">matrices can be multipled</a>, that’s why writing the following in the form of matrices (vectors) is the same as writing our initial system of linear equations:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gUqKJt1of-ZKBOwP_VpEBQ.png" /></figure><p>Great, so now we know, matrices are cool for less writing. But not only, we can as well use matrices to find solutions for our equations.</p><h3>Finding solution using inverse matrix</h3><p>Linear algebra acually lets us easily find solution in matrix form:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NKxtniugUTWX52ykr06sJw.png" /></figure><p>Where this strange w with -1 is called <strong>inverse matrix</strong>. It can be easily found with Python as well as X vector after multiplying inverted matrix with Y:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2313789edad656c7dc781365d6f9ee20/href">https://medium.com/media/2313789edad656c7dc781365d6f9ee20/href</a></iframe><p>Here we’ve found w_inv using np.linalg.inv method, and the multiplied w_inv matrix by Y (which is vector) to get resulting X vector. This script gives us the following:</p><pre>[[1.]<br> [2.]<br> [3.]]</pre><p>Which is a calculated X vector:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Nu9g0COBzQyhKLrU-TbpwQ.png" /></figure><h3>Gaussian elimination method</h3><p>While previous method is cool, it is hard for computers, as inverted matrix calculation complexity growth dramatically with the number of objects and dimentions in matrix.</p><p>Another approach (used by a group of methods) is to transform w matrix to the form where equations solution becomes trivial. This form is called triangular form of a matrix and the method itself is called Gaussian elimination (because we actually eliminate some elements of a matrix).</p><p>Transformation process if done in multiple iterations and the following is allowed:</p><ul><li>multiply any row by any number (except zero, of course),</li><li>add any row to any other row,</li><li>swap any rows.</li></ul><p>Another thing Gaussian method asks us to do is to append our Y vector to our w matrix to get a so called extended matrix:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9CAmyFCIH906CU9YathoWg.png" /></figure><p>Now let’s itarate to get final triangular form. First, let’s swap first and last rows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2OQTv_6DJtaoyeJD3pysiA.png" /></figure><p>Now let’s subtract first row multiplied by 4 from third row:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5ROvQid8qqVfZf886Ifbig.png" /></figure><p>Next, we’ll add first row to the second one:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*u9TfJXqXpMZCknmw4eGDrQ.png" /></figure><p>Good, we need to do one last thing to get triangular form — get 0 where we have -6. To achive this we have to multiply second row by 6/8 and add it to third row:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cBUe4afh9OT-1rewP2GHPA.png" /></figure><p>This is the final form of our matrix, since we have zeros under its diagonal (this is the triangular form we’ve searched for). As we remember, this matrix represents our w for X vector and Y vector on the right side, so we can write updated system based on new matrix form:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_gS1Lht8HIVbQd4alEO_5A.png" /></figure><p>This is the trivial system form, that let’s us iterate from third to first equation by using each lower-iteration solution on upper-iteration:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uWRHk1FmuSBx3TPKI8CaxQ.png" /></figure><p>In Python scipy can be used to get optimized triangular matrix:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/4986ecd54e5b8430a69a7ddd8c58a9ac/href">https://medium.com/media/4986ecd54e5b8430a69a7ddd8c58a9ac/href</a></iframe><p>Note, that scipy will return different matrix, than we iterated to. This is because scipy picks different row as a first row (it takes an element with max first value). But still, the solution to the system using this matrix will be the same as we’ve got. So there can be different triangual forms of a single matrix.</p><h3>Summary</h3><p>We can write down any system of linear equations in a matrix form. And from there we can use matrices transformations to find system solution, for example, using inversed matrix:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*R68_J077_WK5Y9wtuTFjIQ.png" /></figure><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5e203a9ea146" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/solving-systems-of-linear-equations-using-matrices-and-python-5e203a9ea146">Solving systems of linear equations using matrices and Python</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Using EXPLAIN in Mysql to analyze and improve query performance]]></title>
            <link>https://medium.com/datadenys/using-explain-in-mysql-to-analyze-and-improve-query-performance-f58357deb2aa?source=rss-246b8dbde5aa------2</link>
            <guid isPermaLink="false">https://medium.com/p/f58357deb2aa</guid>
            <category><![CDATA[mysql-performance]]></category>
            <category><![CDATA[mysql-server]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[mysql-8]]></category>
            <category><![CDATA[mysql-index]]></category>
            <dc:creator><![CDATA[Denys Golotiuk]]></dc:creator>
            <pubDate>Mon, 17 Oct 2022 15:10:17 GMT</pubDate>
            <atom:updated>2022-10-17T15:10:17.072Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZUuGHo1sxmL4Ia30a3xYGg.png" /><figcaption>Mysql EXPLAIN statement</figcaption></figure><p>Mysql EXPLAIN statement lets us understand efficiency of our queries and grasp ideas on possible ways to optimize it. Just creating indexes is not sufficient to improve performace, using EXPLAIN is mandatory to understand if indexes are used correctly and efficiently.</p><p>Long story short, EXPLAIN shows how Mysql executes given query:</p><pre><strong>EXPLAIN</strong> SELECT * FROM c LIMIT 1</pre><p>This will give us the following output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/847/1*bakpPJZuQzjxPb_IJHDLKA.png" /></figure><p>From all columns, most important is <strong>rows</strong> column, because it shows how many rows Mysql had to process to find answer to our query. Also, <strong>key</strong> and <strong>key_len</strong> columns will show info on an index used for the query. Let’s take a look on several examples.</p><h3>Primary key selects</h3><p>Since we use INNODB engine and our table has ID columns which is a primary key, query execution on primary key results in a single row scan:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/920/1*98r6UlrL3q-az7lwyUXxdg.png" /></figure><p>As we can see, Mysql was able to find our row in a single step by using PRIMARY key (that’s because <a href="https://medium.com/datadenys/tuning-mysql-8-0-server-for-better-performance-238e463f5583#a896">clustered indexes</a> are good for primary key lookups). We can also see, that key_len gives us 8 which is the length of our primary key (because it has single 8-byte bigint column in it).</p><h3>Single column queries and indexes</h3><p>If Mysql was unable to use index for the query, key column will be empty under EXPLAIN:</p><pre>EXPLAIN SELECT count(*) FROM c WHERE country = &#39;Ukraine&#39;</pre><p>Which gives us the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/920/1*u-rGV15JBbxl10CjwV5wEA.png" /></figure><p>Here we can see that Mysql scanned almost 100k rows (which is the size of our table) and used no index to filter data.</p><h4>Creating single column index</h4><p>Let’s create an index on a country column to see what happens with our query:</p><pre>CREATE INDEX <strong>country</strong> on c(<strong>country</strong>);</pre><p>Now let’s examine EXPLAIN of the same query:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vju05xXnQ8ozo6FuBiHecw.png" /></figure><p>Now we can see country (the name of our newely created index) under key column which is good, because Mysql successfully used this index for query. It resulted in 14 rows being scanned instead of 100k which is enormous resources saving.</p><h3>Composite indexes</h3><p>Let’s now take this query with filtering on 2 columns:</p><pre>EXPLAIN SELECT * FROM c<br>WHERE <strong>country = &#39;Ukraine&#39;</strong> AND <strong>industry = &#39;banking&#39;</strong></pre><p>This gives us the following output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*2T4UxjU2u__cPW8-mp00AQ.png" /></figure><p>Here we can see, that Mysql used the same index on country column for the query. But we can see, that it actually selected all 14 rows again and then additionally filtered all of them to see if they meet industry condition:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FvNHY042KI15fWSpOrUXlQ.png" /></figure><p>That’s why we see Using index condition under Extra column of EXPLAIN. It means Mysql used index only to get first result set, and then had to iterate through each row in that set to check condition on second column.</p><h4>Creating composite index</h4><p>We can make more efficient index that Mysql can use for this case — composite index on both columns:</p><pre>CREATE INDEX <strong>country_industry</strong> on c(<strong>country, industry</strong>);</pre><p>Now EXPLAIN will give us the following for our previous query:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*h5thbV8M75-PKOjWXk_KMg.png" /></figure><p>Mysql used newely created index for this query and it ended up in scanning only 3 rows to get results for the query.</p><h3>Possible keys and choosing indexes</h3><p>Mysql will show possible indexes it analyzes before executing query in possible_keys column of EXPLAIN statement:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/709/1*-kGpHdMLAhY9C1zCnhS6Xg.png" /></figure><p>This means, 2 indexes were considered to be used:</p><ul><li>country which was not selected to use,</li><li>country_industry which was selected to use.</li></ul><p>Mysql does the analysis on its own, but it’s pretty good in picking indexes that will yield in the best query performance.</p><h3>Partial index usage</h3><p>Let’s remove country index (the one built for the single country column):</p><pre>DROP INDEX country on c;</pre><p>Now let’s check how our first query performs:</p><pre>EXPLAIN SELECT count(*) FROM c WHERE <strong>country = &#39;Ukraine&#39;</strong></pre><p>And we can see that Mysql was still able to use country_industry index to get results based on a single country column value only:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zJt6Wnd3ijw2BrZ6Bu1j4g.png" /></figure><p>That’s because Mysql is able to use partial index prefix for queries:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Llbt2BcNccOBY8DLYcN9uw.png" /></figure><p>But note that it only works if query uses columns from index prefix. In case used columns are not prefixes, Mysql is unable to use index:</p><pre>EXPLAIN SELECT * FROM c WHERE <strong>industry = &#39;banking&#39;</strong></pre><p>Which will result in full table scan:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/903/1*OXW6ASvQ6I6pvCNc_C7rJg.png" /></figure><p>That’s because industry column is in index, but is not an index prefix:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3vfE2gIh9v5M-iXCSrnijQ.png" /></figure><p>Composite indexes can be built using any number of columns:</p><pre>CREATE INDEX filter<br>ON c(<strong>created, shard_page, shard_letter, country, industry</strong>)</pre><p>It is a good idea consider building bigger (containing more columns) indexes to cover more query cases.</p><h4>Length of used index</h4><p>We can understand if Mysql used only part of an index by checking key_ley column value of EXPLAIN statement. For example, our query that uses both columns of an index:</p><pre>EXPLAIN SELECT * FROM c<br>WHERE <strong>country</strong> = &#39;Ukraine&#39; AND <strong>industry</strong> = &#39;banking&#39;;</pre><p>This will return 1030 for key_len column which is a full length of an index:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/678/1*tjyiB8zq4bHoJjV-YgwFNA.png" /></figure><p>Our other query will return different key_len value:</p><pre>EXPLAIN SELECT * FROM c WHERE <strong>country</strong> = &#39;Ukraine&#39;;</pre><p>And we can see:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/619/1*6DC_TITJkMg27qun0QOXQw.png" /></figure><p>That’s how we know if full index was used for the query or only a part of it.</p><h4>Partial values usage</h4><p>Mysql is so cool it can even use a part of a column value in index for queries execution. Let’s assume following query:</p><pre>EXPLAIN SELECT * FROM c WHERE <strong>country LIKE &#39;Uk%&#39;</strong></pre><p>As we can see, Mysql is still able to use index:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/617/1*6nzXtmZ39OwEAj3Ur_Mw6A.png" /></figure><p>In this case Mysql will filter results based on partial value of an index column. But the same rule applies here, partial column value should be a prefix or otherwise index can’t be used:</p><pre>EXPLAIN SELECT * FROM c WHERE country <strong>LIKE &#39;%ne&#39;</strong></pre><p>This will lead to full table scan:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/574/1*_Q_-qXbFs3kozdzHkQqZHQ.png" /></figure><h3>Sorting</h3><p>Let’s examine ordering query:</p><pre>EXPLAIN SELECT * FROM c <strong>ORDER BY address DESC</strong> LIMIT 10</pre><p>As we can see Mysql is unable to use any indexes for that:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/930/1*SdWW2-GgTHJ9KGq9SU5gEg.png" /></figure><p>When we see Using filesort it doesn’t mean that Mysql actually does something with files, it’s just a name of a sorting operation used. But seeing this means Mysql is sorting results on its own (which is usually bad).</p><p>Indexes can be used for sorting as well as filtering, so creating the following index will help:</p><pre>CREATE INDEX <strong>address</strong> on c(<strong>address</strong>)</pre><p>And now query execution is efficient since Mysql used index for sorting:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/930/1*mvnC4U0oKDjimn-wRbtnQQ.png" /></figure><p>We can as well mix index column usage in filtering and sorting:</p><pre>SELECT * FROM c WHERE <strong>country = &#39;Ukraine&#39;</strong> ORDER BY <strong>industry</strong></pre><p>And Mysql will be able to use our country_industry index for that:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/187/1*LfTq_mKPkRH4Gm0xS68Tiw.png" /></figure><h3>Summary</h3><p>Using EXPLAIN can help understand why your query is underperforming and check if your indexes are used in the most efficient way. You should first of all check rows column to understand how much rows Mysql needs to scan to get results. Second is to check key and key_len columns to understand if index is used for the query and if full index was used.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f58357deb2aa" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datadenys/using-explain-in-mysql-to-analyze-and-improve-query-performance-f58357deb2aa">Using EXPLAIN in Mysql to analyze and improve query performance</a> was originally published in <a href="https://medium.com/datadenys">DataDenys</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>