<?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 Denis Gorobets on Medium]]></title>
        <description><![CDATA[Stories by Denis Gorobets on Medium]]></description>
        <link>https://medium.com/@orginux?source=rss-31cb6f4bca67------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*s0iGaerYRTQuwJMm.</url>
            <title>Stories by Denis Gorobets on Medium</title>
            <link>https://medium.com/@orginux?source=rss-31cb6f4bca67------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 05:29:20 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@orginux/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[How to Compare Rows in Two CSV Files Using ClickHouse]]></title>
            <link>https://orginux.medium.com/how-to-compare-rows-in-two-csv-files-using-clickhouse-76bd7c5eaeb9?source=rss-31cb6f4bca67------2</link>
            <guid isPermaLink="false">https://medium.com/p/76bd7c5eaeb9</guid>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Denis Gorobets]]></dc:creator>
            <pubDate>Thu, 29 Feb 2024 07:11:32 GMT</pubDate>
            <atom:updated>2024-02-29T07:11:32.332Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*r8alNTj4onAvPbfi" /><figcaption>Photo by <a href="https://unsplash.com/@vincentvanzalinge?utm_source=medium&amp;utm_medium=referral">Vincent van Zalinge</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Introduction</h3><p>Dealing with data often requires comparing rows across different CSV files. In this post, we will see how to compare rows in two CSV files using ClickHouse.</p><p>Before diving into the comparison, ensure you have clickhouse-local installed it. If not, you can download it from the <a href="https://clickhouse.tech/docs/en/getting-started/install/">official website</a>.</p><p>In this example, we will compare two CSV files: stores.csv and stores_2.csv. These files contain store metadata, including city, state, type, and cluster. You can download the files from <a href="https://www.kaggle.com/competitions/store-sales-time-series-forecasting/overview">Kaggle</a>. The files are the same, but stores_2.csv has one row less than stores.csv.</p><h3>Compare Two CSV Files</h3><p>As always, we will use clickhouse-local for this kind of operation.</p><h3>Number of RowsThis</h3><p>First, let’s check the number of rows in each file:</p><pre>WITH<br>    (<br>        SELECT count()<br>        FROM file(&#39;stores.csv&#39;)<br>    ) AS s1,<br>    (<br>        SELECT count()<br>        FROM file(&#39;stores_2.csv&#39;)<br>    ) AS s2<br>SELECT<br>    s1,<br>    s2,<br>    s1 = s2 AS equal<br>;</pre><p>Output:</p><pre>┌─s1─┬─s2─┬─equal─┐<br>│ 54 │ 53 │     0 │<br>└────┴────┴───────┘</pre><p>As we can see, the number of rows is different.</p><h3>Find the Differences</h3><p>Now, let’s find the row that is in stores.csv but not in stores_2.csv:</p><pre>SELECT *<br>FROM file(&#39;stores.csv&#39;) AS s1<br>LEFT JOIN file(&#39;stores_2.csv&#39;) AS s2 ON s1.store_nbr = s2.store_nbr<br>WHERE s2.store_nbr IS NULL</pre><p>Output:</p><pre>┌─store_nbr─┬─city──┬─state─────┬─type─┬─cluster─┬─s2.store_nbr─┬─s2.city─┬─s2.state─┬─s2.type─┬─s2.cluster─┐<br>│        10 │ Quito │ Pichincha │ C    │      15 │         ᴺᵁᴸᴸ │ ᴺᵁᴸᴸ    │ ᴺᵁᴸᴸ     │ ᴺᵁᴸᴸ    │       ᴺᵁᴸᴸ │<br>└───────────┴───────┴───────────┴──────┴─────────┴──────────────┴─────────┴──────────┴─────────┴────────────┘</pre><h3>Store the Differences</h3><p>If needed, we can store the differences in a table:</p><pre>CREATE TABLE differences<br>ENGINE = Memory AS<br>SELECT *<br>FROM file(&#39;stores.csv&#39;) AS s1<br>LEFT JOIN file(&#39;stores_2.csv&#39;) AS s2 ON s1.store_nbr = s2.store_nbr<br>WHERE s2.store_nbr IS NULL<br>;</pre><p>Or into a file:</p><pre>SELECT *<br>FROM file(&#39;stores.csv&#39;) AS s1<br>LEFT JOIN file(&#39;stores_2.csv&#39;) AS s2 ON s1.store_nbr = s2.store_nbr<br>WHERE s2.store_nbr IS NULL<br>INTO OUTFILE &#39;differences.csv&#39;<br>FORMAT CSV<br>;</pre><p>That’s it, now you know how to compare rows in two CSV files using ClickHouse.</p><p><em>Originally published at </em><a href="https://orginux.github.io/posts/compare-two-tables/"><em>https://orginux.github.io</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=76bd7c5eaeb9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Internal Replication setting in ClickHouse]]></title>
            <link>https://orginux.medium.com/internal-replication-setting-in-clickhouse-55f7fbbc34f6?source=rss-31cb6f4bca67------2</link>
            <guid isPermaLink="false">https://medium.com/p/55f7fbbc34f6</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Denis Gorobets]]></dc:creator>
            <pubDate>Thu, 01 Feb 2024 06:51:55 GMT</pubDate>
            <atom:updated>2024-02-01T06:51:55.236Z</atom:updated>
            <content:encoded><![CDATA[<p>There are two options for internal_replication setting in ClickHouse:</p><h3>internal_replication = true</h3><p>This option is useful when you’re using Replicated tables and inserting data into Distributed tables (in most cases). The Distributed table inserts data only into one of the underlying table replicas, after that the Replicated table replicates data into other replicas.</p><h3>internal_replication = false</h3><p>This option can be useful when you’re using non-Replicated tables and inserting data into a Distributed table. Writes down data to all underlying replicas if we do insert into a Distributed table. In this case, the Distributed table replicates the data itself.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*upDTa-JIBDnIs5Xe.png" /></figure><h3>Simple example</h3><p>Configuration:</p><p>We have two ClickHouse clusters: CH-A and CH-B:</p><p>For shards of `CH-A` we set internal_replication = false:</p><pre>            ...<br>            &lt;shard&gt;<br>                &lt;internal_replication&gt;false&lt;/internal_replication&gt;<br>                &lt;replica&gt;<br>                ...</pre><p>And shards of `CH-B` will have internal_replication = true:</p><pre>            ...<br>            &lt;shard&gt;<br>                &lt;internal_replication&gt;true&lt;/internal_replication&gt;<br>                &lt;replica&gt;<br>                ...</pre><p>You can use my <a href="https://github.com/orginux/clickhouse-cluster-compose">clickhouse-cluster-compose</a> project to deploy this example. You need to change the setting in <a href="https://github.com/orginux/clickhouse-cluster-compose/blob/main/config/clickhouse/config.d/remote_servers.xml">config/clickhouse/config.d/remote_servers.xml</a> file.</p><p>Table structure and all other settings will be the same for both clusters, the difference only in internal_replication setting.</p><h3>Create tables:</h3><pre>CREATE TABLE table_local ON CLUSTER &#39;{cluster}&#39;<br>(<br>    `num` Int32<br>)<br>ENGINE = MergeTree<br>ORDER BY num<br>;<br><br>CREATE OR REPLACE TABLE table_distr ON CLUSTER &#39;{cluster}&#39; AS table_local<br>ENGINE = Distributed(&#39;{cluster}&#39;, default, table_local, rand())<br>;</pre><h3>Insert data:</h3><p>On one of the replicas of both clusters execute:</p><pre>INSERT INTO table_distr VALUES (1)(2)(3)(4);</pre><h3>Count insert queries:</h3><p>Now we can check how many insert queries were executed on each replica:</p><pre>SELECT count()<br>FROM remote(&#39;clickhouse-01-01&#39;, &#39;system.query_log&#39;)<br>WHERE query_kind = &#39;Insert&#39;;<br><br>SELECT count()<br>FROM remote(&#39;clickhouse-01-02&#39;, &#39;system.query_log&#39;)<br>WHERE query_kind = &#39;Insert&#39;;</pre><p>On CH-A ( internal_replication = false) we have:</p><pre>┌─count()─┐<br>│       2 │<br>└─────────┘<br><br>┌─count()─┐<br>│       2 │<br>└─────────┘</pre><p>On CH-B ( internal_replication = true) the result is:</p><pre>┌─count()─┐<br>│       2 │<br>└─────────┘<br><br>┌─count()─┐<br>│       0 │<br>└─────────┘</pre><h3>Conclusion</h3><p>As you can see, CH-A has two insert queries for each replica, because the Distributed table replicates the data. CH-B only has the insert queries for a single replica, which means we have the rows only on one replica. To replicate the data on other replicas we need to use Replicated table engines. When one of the table replicas receives the write, it will be replicated to the other replicas through ClickHouse Keeper.</p><p><em>Originally published at </em><a href="https://orginux.github.io/posts/internal-replication"><em>https://orginux.github.io</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=55f7fbbc34f6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Convert Parquet to CSV Using ClickHouse]]></title>
            <link>https://orginux.medium.com/convert-csv-to-parquet-using-clickhouse-b3dc07bd77c3?source=rss-31cb6f4bca67------2</link>
            <guid isPermaLink="false">https://medium.com/p/b3dc07bd77c3</guid>
            <category><![CDATA[clickhouse]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Denis Gorobets]]></dc:creator>
            <pubDate>Sat, 20 Jan 2024 10:27:42 GMT</pubDate>
            <atom:updated>2024-01-31T16:25:42.647Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*LjTfMJhdxJRITmg0" /><figcaption>Photo by <a href="https://unsplash.com/@kenrickmills?utm_source=medium&amp;utm_medium=referral">Kenrick Mills</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><h3>Download a Parquet file from S3</h3><p>To convert a Parquet to CSV , we can use clickhouse-local:</p><pre>clickhouse-local</pre><p>Then run the following query:</p><pre>SELECT *<br>FROM s3(&#39;https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet&#39;)<br>INTO OUTFILE &#39;house_0.csv&#39;<br>FORMAT CSV</pre><p>Or just use one bash command:</p><pre>clickhouse-local --query=&quot;SELECT * FROM s3(&#39;https://datasets-documentation.s3.eu-west-3.amazonaws.com/house_parquet/house_0.parquet&#39;) INTO OUTFILE &#39;house_0.csv&#39; FORMAT CSV&quot;</pre><h3>Local Parquet to CSV</h3><p>Check the parquet file:</p><pre>❯ file house_0.parquet<br>house_0.parquet: Apache Parquet</pre><p>Again, we can use clickhouse-local:</p><pre>clickhouse-local</pre><p>Then run the following query:</p><pre>SELECT *<br>FROM `house_0.parquet`<br>INTO OUTFILE &#39;house_0.csv&#39;<br>FORMAT CSV</pre><p>Check the CSV file:</p><pre>❯ wc -l house_0.csv<br>2772030 house_0.csv</pre><p>Or as always, we can use one bash command:</p><pre>clickhouse-local --query=&quot;SELECT * FROM &#39;house_0.parquet&#39; INTO OUTFILE &#39;house_0.csv&#39; FORMAT CSV&quot;</pre><h3>Convert CSV to Parquet</h3><p>Of course, we can also convert a CSV to Parquet:</p><pre>clickhouse-local --query=&quot;SELECT * FROM &#39;house_0.csv&#39; INTO OUTFILE &#39;house_1.parquet&#39; FORMAT Parquet&quot;</pre><p>Check the result:</p><pre>❯ file house_1.parquet<br>house_1.parquet: Apache Parquet</pre><p><em>Originally published at </em><a href="https://orginux.github.io/posts/convert-csv-parquet/"><em>https://orginux.github.io</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b3dc07bd77c3" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Anki: My Top Add-Ons for CS and English Learners]]></title>
            <link>https://orginux.medium.com/anki-my-top-add-ons-for-cs-and-english-learners-b0a05c58f8ff?source=rss-31cb6f4bca67------2</link>
            <guid isPermaLink="false">https://medium.com/p/b0a05c58f8ff</guid>
            <category><![CDATA[education]]></category>
            <category><![CDATA[dev]]></category>
            <category><![CDATA[anki]]></category>
            <category><![CDATA[flashcards]]></category>
            <category><![CDATA[english]]></category>
            <dc:creator><![CDATA[Denis Gorobets]]></dc:creator>
            <pubDate>Mon, 09 Oct 2023 06:17:27 GMT</pubDate>
            <atom:updated>2023-10-09T06:17:27.262Z</atom:updated>
            <content:encoded><![CDATA[<p>I’ve found the flashcard approach to be effective in a variety of learning areas, including vocabulary acquisition, algorithm mastery, and hands-on development practice.</p><h3>Review Heatmap</h3><ul><li><a href="https://ankiweb.net/shared/info/1771074083">Link</a> — code: 1771074083</li></ul><p>One of the most popular Anki add-ons is Review Heatmap. This add-on adds a heatmap that displays the number of cards reviewed each day, information about the longest and current streaks</p><figure><img alt="an example of a heatmap calendar" src="https://cdn-images-1.medium.com/max/721/1*UomXPSApxdpAH9qdIAIh8w.png" /></figure><p>This can help you stay motivated and disciplined in your studies.</p><h3>Oxford Define Addon</h3><p><a href="https://ankiweb.net/shared/info/495793198">Link</a> — code: 495793198</p><p>This add-on adds the ability to fill the back of a card with words and pronunciations from Oxford Dictionaries. This is really useful for English language learners as it automates much of the manual work.</p><p>To start using this add-on, you need to create an account at <a href="https://developer.oxforddictionaries.com/">developer.oxforddictionaries.com</a> and get a unique APP_ID and APP_KEY, then put this information in the add-on configuration:</p><figure><img alt="Oxford Define Addon configuration" src="https://cdn-images-1.medium.com/max/910/1*SnaqzltXXOJwehTXqboZ7A.png" /></figure><p>As you can see in the configuration, you can customize the shortcut and specify what information this add-on will add to the back of a card.</p><p>If everything is set right, you can add any word to the front of a card and click the add-on button or selected shortcut, the add-on fills the back of the card:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*RVzKGH3mDPAc8C4BY0DHaQ.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4IbzGJ9RcPUO4Zsdi_2BtQ.png" /></figure><p>As you can see, the add-on takes care of the entire process of adding a new word. However, it’s important to exercise caution when using this feature, you can find yourself in a situation where your desk is cluttered with unnecessary words.</p><h3>Syntax Highlighting for Code</h3><p><a href="https://ankiweb.net/shared/info/1463041493">Link</a> — 1463041493</p><p>Sometimes you need to add a block of code to a card, the Anki app doesn’t support code formatting out of the box, or I couldn’t find one. But this add-on helps you to insert syntax-highlighted code snippets into your notes.</p><figure><img alt="an example of the Highlighting add-on use" src="https://cdn-images-1.medium.com/max/955/1*7s1-JVSTNEV0A1BsiI2DZA.png" /></figure><p>After inserting a block of code, you need to select it and click the add-on button or use the shortcut.</p><p>In the add-on configuration, you can set a style, a list of programming languages, and a hotkey:</p><figure><img alt="The Syntax Highlighting configuration" src="https://cdn-images-1.medium.com/max/1010/1*dIkhW1moiWCA2n-9pBpEng.png" /></figure><p>One of the use cases is to prepare for interviews and drill algorithmic problems, for example, I have a desk with my favorite LeetCode problems.</p><h3>AnkiConnect</h3><p><a href="https://ankiweb.net/shared/info/2055492159">Link</a> — 2055492159</p><p>AnkiConnect allows external applications such as Obsidian in my case to interact with Anki through an HTTP API. The security aspect may raise questions, as in many other add-ons. However, I have found it extremely useful when I need to convert a portion of my Obsidian notes into Anki cards. Installing <a href="https://github.com/reuseman/flashcards-obsidian">the Flashcards plugin</a> for Obsidian is required.</p><h3>Button Colours (Good, Again)</h3><p><a href="https://ankiweb.net/shared/info/2494384865">Link</a> — code: 2494384865</p><p>By default, the answer buttons are not color-differentiated and to recognize them quickly I use this add-on:</p><figure><img alt="Button Colours add-on is disabled" src="https://cdn-images-1.medium.com/max/489/1*3uMjvF7z9mhr3bQbYPK25A.png" /></figure><figure><img alt="Button Colours add-on is enabled" src="https://cdn-images-1.medium.com/max/490/1*6jtFCWF1PewNmICY4wdTiw.png" /></figure><p>By the way, to avoid clicking on the answer buttons, I use the numeric row on my keyboard: 1 for “Again,” 2 — “Hard,” 3 — “Good,” and 4 — “Easy” This feature is enabled by default in Anki.</p><p>These Anki add-ons provide useful tools to optimize your Anki usage. Use them wisely to enhance your learning journey. Have a happy studying!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b0a05c58f8ff" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>