<?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 Dinesh Kumar A S on Medium]]></title>
        <description><![CDATA[Stories by Dinesh Kumar A S on Medium]]></description>
        <link>https://medium.com/@dineshkumarkct?source=rss-5153fde22cfd------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*_ShbgTwAn8f19_xKmLE1Mg@2x.jpeg</url>
            <title>Stories by Dinesh Kumar A S on Medium</title>
            <link>https://medium.com/@dineshkumarkct?source=rss-5153fde22cfd------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 08:41:50 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@dineshkumarkct/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[My Experience with Data Stores — Part 1]]></title>
            <link>https://medium.com/@dineshkumarkct/my-experience-with-data-stores-part-1-94fd57eb274c?source=rss-5153fde22cfd------2</link>
            <guid isPermaLink="false">https://medium.com/p/94fd57eb274c</guid>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[redshift]]></category>
            <dc:creator><![CDATA[Dinesh Kumar A S]]></dc:creator>
            <pubDate>Mon, 08 Jul 2024 18:23:20 GMT</pubDate>
            <atom:updated>2024-07-08T18:26:00.728Z</atom:updated>
            <content:encoded><![CDATA[<h3>My Experience with Data Stores — Part 1</h3><p>In my 10+ years of experience in Data Engineering and Analytics, I have worked with various data stores, ranging from basic relational databases to modern data warehouses and data lakes. Here, I share my experiences and feedback on some of the key data stores I have used:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xgfuAn_XK7pqlr3DpuUjRw.jpeg" /></figure><h3><strong>MySQL</strong></h3><p>I have not used MySQL as a target system for analytics. While it is suitable for startups with limited data and low throughput, as your reporting system or ingestion scales, MySQL can introduce more maintenance challenges to achieve low latency.</p><p>However, MySQL was a common source system for many of my batch pipelines. Applications and products often use MySQL due to its simplicity, optimised high write throughput, suitability for update event transactions at scale, scalability through sharding and replication, and availability of connectors.</p><h3><strong>Redshift</strong></h3><p>I have worked with Redshift in both its provisioned cluster and serverless forms.</p><p><strong>Provisioned Clusters</strong><br>We used Redshift for data warehousing and reporting, especially within an AWS stack. It supported bulk ingestion through Copy Commands, which suited our periodic high-volume ingestions.</p><p>Redshift’s columnar storage provided good read latency for analytical queries. However, as data volumes and read requests tripled, the tight coupling of storage and compute in provisioned clusters became problematic. We faced issues with WriteOPS struggling while reads had no load, and vice versa. Horizontal scaling through sharding introduced maintenance complexities, including vacuuming and deep copying large tables. These challenges led us to switch to Snowflake.</p><p><strong>Serverless</strong><br>In recent years, we have used Redshift Serverless in some regions. The serverless version decouples storage and compute, has no major maintenance, and improved vacuuming. However, it still has a way to go to match the ease-of-use of Snowflake.</p><h3><strong>Snowflake</strong></h3><p>I have extensive experience with Snowflake, which stands out for its ease-of-use, support, minimal maintenance, and recent features.</p><p>Snowflake’s main drawback is its cost for certain types of usage with frequent updates on data requiring clustering if you need a sub second latency for read queries. Also, if queries are spread round the clock it keeps the warehouse On always adding to the cost. However, Snowflake greatly reduces maintenance efforts with features like auto-clustering, time-travel, easy replication, precise cost reporting, and a user-friendly UI.</p><p>Data engineers can focus on designing and scaling pipelines rather than dealing with data store downtimes. Manual scaling happens in seconds, and auto-scaling based on workloads is efficient. However, careful consideration of workload nature and configuration parameters is necessary to manage costs effectively.</p><h3>Delta Lake</h3><p>I have recently started using Delta Lake from Databricks, which is a good option for building data lakes.</p><p>Unlike data warehouses like Snowflake and Redshift that enforce schema on write, data lakes supports schema on read but also ensures data fits to the predefined schema. We are building a data lake for relaxed latency workloads and has plans to optimise query engines and data layout for low latency workloads over time. A key consideration is choosing suitable query engines and optimising data layout to improve query performance.</p><p>Effective arrangement of data in a delta lake, based on query patterns and common predicates, can significantly enhance retrieval speed.</p><p>When considering whether to implement a data lake, especially if you have an AI/ML system in place, there are several key parameters to evaluate. Here are some crucial factors to consider:</p><p>Key Parameters for Implementing a Data Lake</p><ul><li>Data lakes are designed to handle large volumes of data, making them ideal for AI/ML systems that require bulk data ingestion for training models.</li><li>Data lakes support various data types (structured, semi-structured, and unstructured), which is beneficial for AI/ML applications that often need to process diverse data sources.</li><li>If your system predominantly handles relaxed SLA requests, a data lake can be a suitable choice. Data lakes are optimised for high throughput rather than low latency</li><li>Understand the expected query performance and latency requirements. Data lakes may not be suitable for use cases requiring sub-second response times but are excellent for deeper analytical and exploratory queries.</li><li>Consider the initial setup costs, ongoing maintenance costs, and potential savings from using a data lake. Factors include storage costs, compute costs, and the cost of data management tools and personnel.</li></ul><h3>Redis</h3><p>Redis is an in-memory data store primarily used as a cache. Its quick retrieval capabilities make it unsuitable as a data warehouse due to its in-memory architecture and limited support for queries and joins. However, Redis is invaluable in data engineering pipelines for tasks such as:</p><p><strong>Deduplication within a window </strong>— You push the candidate keys into redis and set the TTL as your window size. drop incoming record if record already exists in Redis.</p><p><strong>Cache your metadata</strong> — Avoid frequent meta calls to database. Cache them in Redis and use them throughout. This will reduce IO to database.</p><p><strong>You have got lua scripting in Redis</strong>. Can write effective server-side scripts and execute them atomically. Have used this to identify duplicates based on a custom logic.</p><p><strong>Use it as a timer </strong>— Have used this to figure out if a Spark job is stuck. The Spark streaming job keeps pushing time.now() to Redis in every microbatch. Build a parallel poller to monitor this and alert if the pipeline is stuck at a microbatch for a long time. This will be helpful in scenarios where the job is alive but stuck. Cache anything and everything to make your pipeline run faster and reduce IO to external databases. It supports many data structures too.</p><p>Redis can cache anything and everything to make your pipeline run faster and reduce IO to external databases, supporting various data structures.</p><h3>Conclusion</h3><p>Each data store has its strengths and weaknesses, and the choice depends on specific use cases and requirements. MySQL is great for applications with high write throughput, Redshift is a solid choice within the AWS ecosystem, Snowflake excels in ease-of-use and minimal maintenance, Delta Lake is suitable for flexible data lakes, and Redis is invaluable for caching and quick data retrieval.</p><p>I will delve into more detailed aspects of each datastore in my upcoming blogs.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=94fd57eb274c" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Consumer Lag Monitoring for Spark Streaming Application + Kafka]]></title>
            <link>https://medium.com/@dineshkumarkct/consumer-lag-monitoring-for-spark-streaming-application-kafka-5d58d07106d?source=rss-5153fde22cfd------2</link>
            <guid isPermaLink="false">https://medium.com/p/5d58d07106d</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[apache-spark]]></category>
            <category><![CDATA[apache-kafka]]></category>
            <category><![CDATA[spark-streaming]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Dinesh Kumar A S]]></dc:creator>
            <pubDate>Mon, 20 Mar 2023 05:32:28 GMT</pubDate>
            <atom:updated>2023-06-27T09:27:45.563Z</atom:updated>
            <content:encoded><![CDATA[<p>When you build a consumer to read messages from a message queue such as Apache Kafka and the consumer consumes and processes slower than the rate of production of messages, it will contribute to Lag.</p><p>For example, if Kafka gets the messages in topic at the rate of 100 messages per second but the consumer you built consumes and processes at the rate of 50 messages per second, then the lag will pile up at the rate of 100–50 = 50 messages per second. So after one hour, the total lag will be 1,80,000 messages.</p><h4>Why is Consumer Lag crucial in a streaming pipeline?</h4><p>Most of the streaming pipelines we build is to process data in near real time and when there is a lag the target will start seeing delay in the data synchronisation.</p><h4>How common is Lag in a streaming pipeline?</h4><p>Lag is very common in a streaming pipeline because of the following reasons</p><ol><li>The upstream system can have fluctuations in traffic based on the usage.</li><li>There can be a sudden event by a big customer and this can contribute to a big spike in traffic</li><li>Some instances in the streaming pipeline can be unhealthy for a short period of time and before it recovers, the lag will pile up</li></ol><p>Lag is an indicator of the health and scale of the pipeline. When there is a lag and if it is piling up over time, action is needed from the engineers.</p><h4>Lag monitoring system</h4><p>A Lag monitoring system to monitor the lag between the consumer and the topic in real time is mandatory. Typically, a dashboard that shows the lag for every X minutes is needed to monitor the lag and see if it piles up due to any unfortunate event.</p><p>If the streaming pipeline fails, the lag will continuous grow since no messages are consumed and transformed. This is a significant indicator for the engineers to identify if the pipeline is not running as expected.</p><p>Generally the Kafka will have a Burrow URL associated with it. This will provide the lag number for any consumer group attached to the Kafka broker.</p><p>URL :<a href="https://my_burrow_api.com/api/burrow/cluster/my_cluster/consumer/my_consumer_group">https://my_burrow_api.com/api/burrow/cluster/my_cluster/consumer/my_consumer_group</a></p><pre>{<br>  &quot;error&quot;: false,<br>  &quot;message&quot;: &quot;consumer status returned&quot;,<br>  &quot;status&quot;: {<br>    &quot;cluster&quot;: &quot;virginia&quot;,<br>    &quot;group&quot;: &quot;my_consumer_group&quot;,<br>    &quot;status&quot;: &quot;OK&quot;,<br>    &quot;complete&quot;: 1,<br>    &quot;partitions&quot;: [<br>      {<br>        &quot;topic&quot;: &quot;my_topic&quot;,<br>        &quot;partition&quot;: 0,<br>        &quot;owner&quot;: &quot;/0.0.0.0&quot;,<br>        &quot;client_id&quot;: &quot;kafkaclient&quot;,<br>        &quot;status&quot;: &quot;OK&quot;,<br>        &quot;start&quot;: {<br>          &quot;offset&quot;: 149887382,<br>          &quot;timestamp&quot;: 1679286242711,<br>          &quot;observedAt&quot;: 1679286252000,<br>          &quot;lag&quot;: 0<br>        },<br>        &quot;end&quot;: {<br>          &quot;offset&quot;: 149887819,<br>          &quot;timestamp&quot;: 1679286377713,<br>          &quot;observedAt&quot;: 1679286387000,<br>          &quot;lag&quot;: 0<br>        },<br>        &quot;current_lag&quot;: 0,<br>        &quot;complete&quot;: 1<br>      },<br>      {<br>        &quot;topic&quot;: &quot;my_topic&quot;,<br>        &quot;partition&quot;: 1,<br>        &quot;owner&quot;: &quot;/0.0.0.0&quot;,<br>        &quot;client_id&quot;: &quot;kafkaclient&quot;,<br>        &quot;status&quot;: &quot;OK&quot;,<br>        &quot;start&quot;: {<br>          &quot;offset&quot;: 149740248,<br>          &quot;timestamp&quot;: 1679286247799,<br>          &quot;observedAt&quot;: 1679286258000,<br>          &quot;lag&quot;: 0<br>        },<br>        &quot;end&quot;: {<br>          &quot;offset&quot;: 149740615,<br>          &quot;timestamp&quot;: 1679286382801,<br>          &quot;observedAt&quot;: 1679286388000,<br>          &quot;lag&quot;: 0<br>        },<br>        &quot;current_lag&quot;: 0,<br>        &quot;complete&quot;: 1<br>      },<br>      {<br>        &quot;topic&quot;: &quot;my_topic&quot;,<br>        &quot;partition&quot;: 2,<br>        &quot;owner&quot;: &quot;/0.0.0.0&quot;,<br>        &quot;client_id&quot;: &quot;kafkaclient&quot;,<br>        &quot;status&quot;: &quot;OK&quot;,<br>        &quot;start&quot;: {<br>          &quot;offset&quot;: 149952545,<br>          &quot;timestamp&quot;: 1679286255168,<br>          &quot;observedAt&quot;: 1679286265000,<br>          &quot;lag&quot;: 400<br>        },<br>        &quot;end&quot;: {<br>          &quot;offset&quot;: 149952924,<br>          &quot;timestamp&quot;: 1679286390169,<br>          &quot;observedAt&quot;: 1679286390000,<br>          &quot;lag&quot;: 0<br>        },<br>        &quot;current_lag&quot;: 400,<br>        &quot;complete&quot;: 1<br>      }<br>    ],<br>    &quot;partition_count&quot;: 2,<br>    &quot;maxlag&quot;: {<br>      &quot;topic&quot;: &quot;my_topic&quot;,<br>      &quot;partition&quot;: 0,<br>      &quot;owner&quot;: &quot;/0.0.0.0&quot;,<br>      &quot;client_id&quot;: &quot;kafkaclient&quot;,<br>      &quot;status&quot;: &quot;OK&quot;,<br>      &quot;start&quot;: {<br>        &quot;offset&quot;: 149887382,<br>        &quot;timestamp&quot;: 1679286242711,<br>        &quot;observedAt&quot;: 1679286252000,<br>        &quot;lag&quot;: 0<br>      },<br>      &quot;end&quot;: {<br>        &quot;offset&quot;: 149887819,<br>        &quot;timestamp&quot;: 1679286377713,<br>        &quot;observedAt&quot;: 1679286387000,<br>        &quot;lag&quot;: 600<br>      },<br>      &quot;current_lag&quot;: 600,<br>      &quot;complete&quot;: 1<br>    },<br>    &quot;totallag&quot;: 1000<br>  },<br>  &quot;request&quot;: {<br>    &quot;url&quot;: &quot;/v3/kafka/my_cluster/consumer/my_consumer_group/lag&quot;,<br>    &quot;host&quot;: &quot;host-1- virginia&quot;<br>  }<br>}</pre><p>The <strong>totallag is 1000</strong> for the consumer group ‘my_consumer_group’ for the topic ‘my_topic’. If there are multiple topics, the response will have the lag for each partition under each topic.</p><p>This Burrow response works on the consumer group. Spark, on the other hand, does not have a concept of standard consumer group and it completely depends on Checkpoint file only.</p><p>Checkpoint directory that will get updated between micro-batches which will look like the following</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/372/1*03D1O8sj_RIM_OXB290lrg.png" /></figure><p>Offsets directory will have a new file created for every batch to be processed. Commits directory will have a file with the same name as Offsets once the batch is complete.</p><p>An Offset file will look like below</p><pre>v1<br>{<br>  &quot;batchWatermarkMs&quot;: 0,<br>  &quot;batchTimestampMs&quot;: 1679281140002,<br>  &quot;conf&quot;: {<br>    &quot;spark.sql.streaming.stateStore.providerClass&quot;: &quot;org.apache.spark.sql.execution.streaming.state.HDFSBackedStateStoreProvider&quot;,<br>    &quot;spark.sql.streaming.join.stateFormatVersion&quot;: &quot;2&quot;,<br>    &quot;spark.sql.streaming.stateStore.compression.codec&quot;: &quot;lz4&quot;,<br>    &quot;spark.sql.streaming.stateStore.rocksdb.formatVersion&quot;: &quot;5&quot;,<br>    &quot;spark.sql.streaming.statefulOperator.useStrictDistribution&quot;: &quot;false&quot;,<br>    &quot;spark.sql.streaming.flatMapGroupsWithState.stateFormatVersion&quot;: &quot;2&quot;,<br>    &quot;spark.sql.streaming.multipleWatermarkPolicy&quot;: &quot;min&quot;,<br>    &quot;spark.sql.streaming.aggregation.stateFormatVersion&quot;: &quot;2&quot;,<br>    &quot;spark.sql.shuffle.partitions&quot;: &quot;200&quot;<br>  }<br>}<br>{<br>  &quot;topic_1&quot;: {<br>    &quot;0&quot;: 55723268,<br>    &quot;1&quot;: 55874113,<br>    &quot;2&quot;: 56377024<br>  },<br>  &quot;topic_2&quot;: {<br>    &quot;0&quot;: 6845083,<br>    &quot;1&quot;: 6849802,<br>    &quot;2&quot;: 6846012<br>  },<br>  &quot;topic_3&quot;: {<br>    &quot;0&quot;: 107977253,<br>    &quot;1&quot;: 107835992,<br>    &quot;2&quot;: 108074880<br>  }<br>}</pre><p>Commit File</p><pre>v1<br>{&quot;nextBatchWatermarkMs&quot;:0}</pre><p>Thus the Offset file is the source for us to generate the total lag.</p><p>Even-though the Spark Streaming does not register the consumer groups in Kafka, the burrow URL can still return the latest offset for any particular topic using the Topics API</p><p>URL : <a href="https://my_burrow_api.com/api/burrow/cluster/my_cluster/consumer/my_consumer_group">https://my_burrow_api.com/api/burrow/cluster/my_cluster/topic/m</a>y_topic</p><pre>{<br>  &quot;error&quot;: false,<br>  &quot;message&quot;: &quot;topic offsets returned&quot;,<br>  &quot;offsets&quot;: [<br>    400525470,<br>    400150589,<br>    400165658,<br>    313568243,<br>    313586906,<br>    313652577,<br>    42689842,<br>    42665916,<br>    42663526,<br>    42669086,<br>    42685936,<br>    42690057<br>  ],<br>  &quot;request&quot;: {<br>    &quot;url&quot;: &quot;/v3/kafka/my_cluster/topic/my_topic&quot;,<br>    &quot;host&quot;: &quot;tools_1&quot;<br>  }<br>}</pre><p>So, a simple program that takes the difference between the Topic Response and the latest Checkpoint files will produce the lag number for any application</p><p>Code Snippet with S3 as my checkpoint location</p><pre>def get_lag_from_checkpoint(bucket, burrow_url, checkpoint_path):<br><br>        application_total_lag = 0<br><br>        s3 = boto3.client(‘s3&#39;)<br>        paginator = s3.get_paginator(&quot;list_objects&quot;)<br><br>        def get_last_modified(obj):<br>            return int(obj[‘LastModified’].strftime(‘%s’))<br><br>        page_iterator = paginator.paginate(Bucket=bucket, Prefix=&quot;{}/offsets/&quot;.format(checkpoint_path))<br><br>        last_added = None<br><br>        for page in page_iterator:<br>            if &quot;Contents&quot; in page:<br>                last_added = [obj[‘Key’] for obj in sorted(page[&quot;Contents&quot;], key=get_last_modified)][-1]<br><br>        if last_added is None:<br>            raise Exception<br><br>        result = s3.list_objects(Bucket=bucket, Prefix=last_added)<br>        obj = result.get(‘Contents’)[0]<br>        data = s3.get_object(Bucket=bucket, Key=obj.get(‘Key’))<br>        contents = data[‘Body’].read()<br>        dict_content = json.loads(contents.decode(&quot;utf-8&quot;).split(‘\n’)[2])<br>        topics = dict_content.keys()<br><br>        for topic in topics:<br><br>            topic_total_lag = 0<br><br>            url = ‘{}/{}’.format(burrow_url, topic)<br>       <br>            try:<br>                data = get_response_from_https://medium.com/url(url)<br>            except:<br>                raise Exception<br><br>            topic_max_offsets = data[&#39;offsets&#39;]<br><br>            for partition in dict_content[topic]:<br>                topic_total_lag = topic_total_lag + (<br>                        topic_max_offsets[int(partition)] - dict_content[topic][&#39;{}&#39;.format(str(partition))])<br><br>            application_total_lag = application_total_lag + topic_total_lag<br><br>        return application_total_lag if application_total_lag &gt;= 0 else 0</pre><p>I have scheduled the above program in airflow and pushed the value to prometheus in real time and can be visualised in a Grafana dashboard. You can use any CRON or scheduler to do the same.</p><p>If the burrow API is not available, we can even start a separate python client to get the latest offset for every poll interval</p><pre>from kafka import KafkaConsumer, TopicPartition<br><br>consumer = KafkaConsumer(<br>                group_id=&#39;&#39;,<br>                bootstrap_servers=&#39;&#39;,<br>                sasl_plain_username=&#39;&#39;,<br>                sasl_plain_password=&#39;&#39;,<br>                security_protocol=&#39;&#39;,<br>                sasl_mechanism=&#39;&#39;,<br>                ssl_cafile=&#39;&#39;,<br>                enable_auto_commit=False<br>            )<br><br>tp = TopicPartition(topic, partition)<br>consumer.assign([tp])<br>consumer.seek_to_end(tp)<br><br># Get last offset for the topic:partition<br>last_offset = consumer.position(tp)</pre><h4>Lag Dashboard</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZNXLMYUC5VSZCveB4AkHeA.png" /></figure><p>The graph above shows the lag for the application. The hidden value is the application name. So the total lag is calculated for my application from the checkpoint file and plotted in Grafana in real time. The Lag is showing to and fro spikes since my application has a trigger interval of 1 minute. So the number should always be between 0 and Maximum number that can pile up for my application.</p><p>If it piles up like below, then there is a problem which we need to debug and fix it.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bB6yKseEuap_B48ePvf4iA.png" /></figure><p>Happy Reading !! Leave a clap if you find this useful!!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=5d58d07106d" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Introduction to Structured Streaming in Apache Spark (PySpark) + Kafka]]></title>
            <link>https://medium.com/@dineshkumarkct/introduction-to-structured-streaming-in-apache-spark-pyspark-kafka-9151b01d79ba?source=rss-5153fde22cfd------2</link>
            <guid isPermaLink="false">https://medium.com/p/9151b01d79ba</guid>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[apache-spark-streaming]]></category>
            <category><![CDATA[apache-spark]]></category>
            <category><![CDATA[structured-streaming]]></category>
            <category><![CDATA[real-time-analytics]]></category>
            <dc:creator><![CDATA[Dinesh Kumar A S]]></dc:creator>
            <pubDate>Fri, 23 Dec 2022 15:01:55 GMT</pubDate>
            <atom:updated>2023-01-05T08:40:41.780Z</atom:updated>
            <content:encoded><![CDATA[<p>A Streaming data pipeline is the need of the hour since the industry is moving towards near real time analytics and streaming applications. This article covers the basics to use Structured streaming in Apache Spark with Kafka.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*b1IOC24h-LyfZV4UkTFSaw.jpeg" /></figure><h4>Where does Spark fit in the streaming world?</h4><p>There are multiple providers of streaming pipelines in the market such as Apache Flink, Apache Beam, Apache Spark etc.</p><p>Spark is the widely adopted and well supported framework and it works on <em>micro-batches</em> for streaming unlike a real streaming which works on atomic records.</p><p>For example, You have a large dataset of world population. You write a Spark program to find the female population in India.</p><p>A typical Spark batch job and does the following.</p><ol><li>Start the batch application</li><li>Read the data</li><li>Filter data based on the condition (sex=’F’ and country=’IND’)</li><li>Write result data into target location</li><li>Shut the application down</li></ol><p>Suppose if the source changes from file system to Kafka and you use Spark Streaming for the same, the app will work as below.</p><ol><li>Start the streaming app</li><li>Read the first set of records from Kafka</li><li>Filter data based on the condition (sex=’F’ and country=’IND’)</li><li>Write result data into target location</li><li>Go for the next set of records</li><li>Terminate only if it fails or if the shutdown is called.</li></ol><blockquote>Apache Spark Streaming works on micro-batches. This is like running the batch application on a regular frequency on an incremental chunk of incoming data.</blockquote><h4>Why Structured Streaming and how is it different from others?</h4><p>Apache Spark has two APIs for streaming</p><ol><li>DStream : DStream is using the basic API of Spark, which is RDD. DStream reads input data as RDD and all the logic is written on the RDD.</li><li>Structured Stream : Structured stream, as the name says, it reads the input as Dataframe and Dataset (Structured with columns and rows). It utilises the Spark SQL API.</li></ol><p>Both the APIs have their own areas of application. We will talk about Structure Streaming in this article and how it works as a consumer for Apache Kafka.</p><h3>Apache Kafka</h3><p>Apache Kafka is a message bus, which is resilient, fault tolerant and scalable.</p><p>Few points about Kafka to brush up before reading ahead,</p><ol><li>Topic</li><li>Partition and Offset</li><li>Broker, Producer and Consumer</li></ol><h3>Installation</h3><p>There are lot of articles explaining how to install</p><ol><li>PySpark</li><li>Apache Kafka (If you are testing from local)</li></ol><p>After installation, the next step is to find the relevant package or artifact in Spark+Kafka for your version of Spark.</p><p>I use, <a href="https://mvnrepository.com/artifact/org.apache.spark/spark-sql-kafka-0-10_2.12/3.2.1">https://mvnrepository.com/artifact/org.apache.spark/spark-sql-kafka-0-10_2.12/3.2.1</a> for my project which is quite sufficient and stable.</p><p>There are multiple ways to install this package at the start of the Spark Application.</p><ol><li>During Spark Session creation in the code</li></ol><pre>spark = SparkSession.builder \<br>        .appName(&quot;{}&quot;.format(application_name)) \<br>        .config(&#39;spark.jars.packages&#39;, &#39;org.apache.spark:spark-sql-kafka-0-10_2  .12:3.2.1&#39;) \<br>        .getOrCreate()</pre><p>2. As a package in Spark Submit command</p><pre>spark-submit --deploy-mode cluster  <br>--master yarn --executor-cores 4 <br>--executor-memory 5g --driver-memory 5g <br>--driver-cores 4 <br>--packages org.apache.spark:spark-sql-kafka-0-10_2.12:3.2.1 <br>/path/to/job args .</pre><p>This package has all the necessary methods to connect Spark with Kafka.</p><h3>Read Stream</h3><p>Once the Spark Session is created, we can use that to create the readStream.</p><p>The readStream is the first step to create an input object for stream.</p><p>Following is the snippet that covers the basic options needed. You can refer the document <a href="https://spark.apache.org/docs/latest/structured-streaming-kafka-integration.html">https://spark.apache.org/docs/latest/structured-streaming-kafka-integration.html</a> for more options.</p><pre>df = spark.readStream \<br>        .format(&quot;kafka&quot;) \<br>        .options(**options) \<br>        .option(&quot;maxOffsetsPerTrigger&quot;, 10000) \<br>        .option(&quot;startingOffsets&quot;, starting_offset) \<br>        .option(&quot;startingTimestamp&quot;, starting_offset_timestamp) \<br>        .option(&#39;failOnDataLoss&#39;, &#39;false&#39;) \<br>        .load()</pre><ol><li><em>Format is Kafka since we are consuming from Kafka</em></li><li><em>**options are below</em></li></ol><pre>options = {<br>        &quot;kafka.sasl.jaas.config&quot;: &#39;org.apache.kafka.common.security.plain.PlainLoginModule required username=&quot;{kafka_username}&quot; password=&quot;{kafka_pwd}&quot;;&#39;.format(<br>            kafka_username=kafka_sasl_username, kafka_pwd=kafka_sasl_password),<br>        &quot;kafka.sasl.mechanism&quot;: &quot;PLAIN&quot;,<br>        &quot;kafka.security.protocol&quot;: &quot;SASL_PLAINTEXT&quot;,<br>        &quot;kafka.bootstrap.servers&quot;: kafka_bootstrap_servers, # Eg. &quot;kafka-x.kafka:9094,kafka-y.kafka:9094&quot;<br>        &quot;subscribe&quot;: topics # Eg. &quot;Topic1,Topic2&quot;<br>    }</pre><p>The options include username and password of Kafka, broker IP, Topics to be consumed. We can pass multiple topics in one application. It should be comma separated. Eg. <em>Topic1,Topic2,Topic3.</em></p><p>The spark application will listen to all three topics at once in every micro-batch.</p><p>3. <em>startingOffsets</em></p><p>This will mention the starting point.</p><p>I mostly give <strong>latest </strong>for my use-case<strong> </strong>since the application will read the latest message. <strong>earliest </strong>will start the oldest message available in Kafka.</p><p>Pass the startingOffsets as the static values when you need to restart from a particular offset. This is useful if a re-run of set of records from Kafka is needed.</p><p>4. <em>startingTimestamp, startingOffsetsByTimestamp</em></p><p>These are similar to startingOffsets in starting from a particular point. But the difference here is that we pass the timestamp of the message produced to Kafka.</p><p>If a message has the following keys in Kafka,</p><ol><li>TopicName: Topic1</li><li>partition : 0</li><li>offset : 123</li><li>timestamp : 1671784395223</li></ol><p>We can re-start application from this message as the starting point in multiple ways</p><p><strong>Using startingOffsets:</strong></p><pre>.option(&quot;startingOffsets&quot;, &quot;{&quot;Topic1&quot;:{&quot;0&quot;:123,&quot;1&quot;:-1}&quot;) \ #-1 refers latest for partition 1</pre><p><strong>Using startingOffsetByTimestamp:</strong></p><pre>.option(&quot;startingOffsetsByTimestamp&quot;, &quot;{&quot;Topic1&quot;:{&quot;0&quot;: 1671784395223,&quot;1&quot;:-1}&quot;) \ #-1 refers latest for partition 1</pre><p><strong>Using startingTimestamp</strong>: <em>(This will restart for all the Topics from this timestamp. Mostly used in complete re-run and not specific to a topic or partition)</em></p><pre>.option(&quot;startingTimestamp&quot;, 1671784395223)</pre><p>However, when an application is stopped and restarted, it will read from ‘Checkpoint’, which will be covered in the next section.</p><h3>Write Stream</h3><p>WriteStream is similar to readStream but it handles writing the output to an external sink.</p><p>There are many types of sinks supported in Spark such as Console, Memory, File Sink, Kafka Sink, ForEachBatch etc</p><p>You can refer detailed explanation and how to use them here. <a href="https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html">https://spark.apache.org/docs/latest/structured-streaming-kafka-integration.html</a></p><p>I will cover few of the basic and common aspects taking file and forEachBatch sinks as example</p><p><em>Sample 1: File sink</em></p><pre>query = df.writeStream \<br>        .queryName(&quot;test1&quot;) \<br>        .format(&quot;parquet&quot;) \<br>        .trigger(processingTime=&#39;10seconds&#39;) \<br>        .option(&quot;checkpointLocation&quot;,&quot;path/to/checkpoint/dir&quot;) \<br>        .option(&quot;path&quot;, &quot;path/to/outpur/dir&quot;)<br>        .outputMode(&quot;append&quot;) \<br>        .start()<br><br>query.awaitTermination()</pre><p><em>Sample 2 : forEachBatch sink</em></p><pre>query = df.writeStream \<br>        .queryName(&quot;test2&quot;) \<br>        .foreachBatch(function_call) \<br>        .trigger(processingTime=&#39;10seconds&#39;) \<br>        .option(&quot;checkpointLocation&quot;,&quot;path/to/checkpoint&quot;) \<br>        .outputMode(&quot;append&quot;) \<br>        .start()<br><br>query.awaitTermination()</pre><p>The difference between both of them is,</p><ul><li>Sample1 has the dataframe(df) directly written to Kafka. So, all the transformations are applied already in df and it will be sent to the output directory specified</li><li>Sample 2 just takes the dataframe df and forwards each micro-batch to a function <em>function_call </em>which will do all the transformations on the micro-batch and may write the output.</li></ul><p>I have used <em>type2</em> in my project where we take the input dataframe and apply forEachBatch to implement all the business logics on the data and write the output to data lake.</p><p>The significant points are the following</p><h4><em>checkpointLocation</em></h4><ul><li>Kafka has a registry in its architecture where it will maintain the information of all the consumer groups, it’s last committed offset etc. When the consumer group disconnects from Kafka and reconnects again, Kafka will help the consumer resume from the last committed message since it has the information.</li><li>Applications like Spark does not depend on Kafka since the concept of consumer group is not strict here. So Spark maintains its own register to note down the last committed offsets for each partition in each topic. So, the next time the same application starts referring to the same checkpoint location, it will resume as expected. It is a simple file system and can be S3, BlobStore etc which is highly available.</li></ul><p>Following is an example of a checkpoint offset file where it stores the latest read offset for Topic1.</p><pre>v1<br>{&quot;batchWatermarkMs&quot;:0,&quot;batchTimestampMs&quot;:1671730121810,&quot;conf&quot;:{&quot;spark.sql.streaming.stateStore.providerClass&quot;:&quot;org.apache.spark.sql.execution.streaming.state.HDFSBackedStateStoreProvider&quot;,&quot;spark.sql.streaming.join.stateFormatVersion&quot;:&quot;2&quot;,&quot;spark.sql.streaming.stateStore.compression.codec&quot;:&quot;lz4&quot;,&quot;spark.sql.streaming.stateStore.rocksdb.formatVersion&quot;:&quot;5&quot;,&quot;spark.sql.streaming.flatMapGroupsWithState.stateFormatVersion&quot;:&quot;2&quot;,&quot;spark.sql.streaming.multipleWatermarkPolicy&quot;:&quot;min&quot;,&quot;spark.sql.streaming.aggregation.stateFormatVersion&quot;:&quot;2&quot;,&quot;spark.sql.shuffle.partitions&quot;:&quot;200&quot;}}<br>{&quot;Topic1&quot;:{&quot;2&quot;:99502679,&quot;1&quot;:99208742,&quot;0&quot;:99335610}}</pre><h4>trigger interval</h4><p>Spark stream is a micro-batch and it has to pull data in a regular interval from Kafka to make sure it does not lag behind.</p><p>Trigger interval defines the frequency of it. In our example, the application polls new data for every 10 seconds. It means, it has to read, process and write to output and get ready for the next batch within every 10 seconds.</p><blockquote>If the processing time is greater than trigger interval, the application will start falling behind and there will be a delay piling up. So, the thumb rule for any streaming application is <strong>processing time &lt; trigger interval</strong>.</blockquote><h4>output mode</h4><p>There are three output modes supported. They are append, update and complete. I have used append in most of the use cases. A detailed explanation is in the official doc. <a href="https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html">https://spark.apache.org/docs/latest/structured-streaming-programming-guide.html</a></p><h4>awaitTermination</h4><p>The variable <em>query </em>in the code snippets is the identifier of the streaming query. It has lot of methods and <em>awaitTermination</em> is one of them</p><p>Since the stream is a continuously running process, theoretically terminates. This is done by <em>awaitTermination.</em> It instructs the stream to run continuously until it receives a kill signal or an error.</p><p>We can also stop the stream if a condition is met by calling <em>query.stop()</em></p><p>These are the basics of Spark Structured streaming + Kafka and this should help you to make the application up and running.</p><p>I will be covering some of the specific concepts and experiences with streaming in the upcoming articles.</p><p><em>Happy Reading!! Leave a clap if you find this interesting!!</em></p><p><strong>Note:</strong> For Spark performance optimisation techniques, refer the following articles:</p><ul><li><a href="https://medium.com/@dineshkumarkct/apache-spark-optimisation-part-1-c325be5637e0">Apache Spark Optimisation-Part 1</a></li><li><a href="https://medium.com/@dineshkumarkct/apache-spark-optimisation-part-2-a21d27f3b97">Apache Spark Optimisation — Part 2</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9151b01d79ba" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[5 Tips for Writing a better SQL]]></title>
            <link>https://medium.com/@dineshkumarkct/5-tips-for-writing-a-better-sql-8cba704edf79?source=rss-5153fde22cfd------2</link>
            <guid isPermaLink="false">https://medium.com/p/8cba704edf79</guid>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[database]]></category>
            <dc:creator><![CDATA[Dinesh Kumar A S]]></dc:creator>
            <pubDate>Wed, 07 Dec 2022 15:09:25 GMT</pubDate>
            <atom:updated>2022-12-08T05:04:18.858Z</atom:updated>
            <content:encoded><![CDATA[<p>SQL is the integral part of the data engineering world. Be it frameworks like PySpark, ETL tools like Informatica and Datastage, SQL is the key. Writing a better SQL definitely saves a lot of time and money in your pipeline.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/900/1*67XM0ztpjqjOdakuE3GnRQ.jpeg" /><figcaption>PC: <a href="https://www.asug.com/">https://www.asug.com/</a></figcaption></figure><h4>1. Understand the Order of Execution</h4><p>Knowing the order of execution is the most important of all.</p><p>Consider the following SQL for an example.</p><pre>SELECT DISTINCT department_name, sum(salary) as sal, …<br>FROM employee <br>    JOIN departmemt<br>      ON employee.department_id = department.id<br>    WHERE employee.age &gt; 40<br>    GROUP BY department_name<br>    HAVING sum(sal)&gt;10000<br>    ORDER BY department_name desc<br>    LIMIT 10;</pre><blockquote>P1 : From and Join</blockquote><p>The from part of the SQL executes first including any Joins mentioned in it. So try to mention all your join conditions in Join clause. I have seen people doing a cross join and mentioning filter conditions in Where clause to pick necessary records. Don’t do that. It will join all data and filter again which is not needed.</p><blockquote>P2 : Where Clause (Filter)</blockquote><p>Filter Clause is the second most important in list. It filters out all the unwanted rows and just take the needed rows for computation going forward.</p><p>In our example we are taking ahead rows only with the employee’s age is &gt;40. If it is 20% of total rows, we are saving 80% of time in computation.</p><blockquote>P3 : Group By Clause</blockquote><p>GroupBy groups the result from filter into unique number of records as mentioned in the groupBy clause. In our case, we will have N number of records where N=Number of Departments.</p><blockquote>P4 : Select Clause</blockquote><p>The select clause is the first step of collecting the data to be shown as the result. Don’t use select *. Always select only required columns since * will load all data into memory which is not advised. Also, when a GroupBy is applied, you can select only that column in Select accompanied by any aggregate function on another column</p><p>For eg. Since we have grouped by DepartmentName, we cannot select EmployeeName in select because GroupBy is already executed and has only N unique values of department.</p><blockquote>P5 : Having</blockquote><p>Having will always execute on the selected aggregate values after a groupBy. In our case, we have filtered departments whose sum(salary)&gt;10000. This will not be possible before aggregation and that’s why Having happens after GroupBy.</p><blockquote>P6: Order By</blockquote><p>Sorting happens at the end after selecting all the necessary rows and columns.</p><blockquote>P7 : Limit</blockquote><p>Limit is the last step and it is only to limit during display/producing results. This will be helpful where I/O or network transfer is involved and can used with Offset for pagenation.</p><p><em>Limit is not equal to Where. Where filters data for computation whereas Limit just displays few after computation</em></p><h4>2. Use Window Functions wherever needed</h4><p>The window functions that will make life easier in most of the places are</p><ol><li>Rank</li><li>Dense Rank</li><li>Row Number</li><li>Lead</li><li>Lag</li></ol><p>For eg., For the SQL to find the 2nd highest salary, I have seen people writing the following query</p><pre>select <br>  max(salary) <br>from <br>  employee <br>where <br>  employee.salary &lt; (<br>    select <br>      max(salary) <br>    from <br>      employee<br>  );</pre><p>Although the query above works, it will not scale for 5th highest salary. It will require 4 subqueries. So Rank() is the best option. Also when you add group by into like ‘2nd highest salary of each department’, adding a partition by will simply solve the use-case. Dense_Rank() is similar to Rank() but gives continuous ranks.</p><pre>select <br>  department_name, <br>  rank() over(<br>    partition by department <br>    order by <br>      salary<br>  ) <br>from <br>  employee <br>  join department on...</pre><p>Similarly, Row_num() is the best to identify duplicates in data. The following query will have row_num=1 for unique values and more than 1 for duplicates within each department. A delete query on top of this with row_num&gt;1 will clean the duplicates.</p><pre>select <br>  emp_id, <br>  emp_name, <br>  Department_name, <br>  , <br>  ROW_NUMBER() over (<br>    partition by Department_name <br>    order by <br>      emp_id<br>  ) as rn <br>from <br>  employee <br>  join department on...</pre><p>Similarly, Lag() and Lead() will be very helpful when you require data from the previous or next rows respectively.</p><h4>3. Execution Plan is the key</h4><p>Almost all the databases today have an Analyse function or a similar function to tell you the physical plan of execution.</p><p>Also, after execution, you can check the plan,</p><ul><li>to identify the areas where the query is taking more time?</li><li>to check if any cross product is happening?</li><li>to check the time taken in each step</li><li>Is the caching happening efficiently if applicable?</li><li>Does the data spill to disk adding I/O and making query slower?</li><li>Is the filter happening at the right step to eliminate unwanted rows?</li></ul><p>These questions can be answered when we look at the plan. Optimisation can be done based on the results.</p><p>Following is the Physical Plan of Snowflake with all the metrics discussed above.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*xHOeDoitbmbdbp23GhwpfQ.png" /><figcaption><a href="https://sonra.io/snowflake/loading-data-into-snowflake-and-performance-of-large-joins/">https://sonra.io</a></figcaption></figure><h4>4. Advanced techniques for better problem solving</h4><p>There are advanced techniques in SQL to get the work done efficiently like in regular programming languages.</p><ul><li>CTE (Common Table Expression)</li><li>Recursion</li></ul><p>&gt; CTE is very similar to creating a variable in the program and use it everywhere else. This will avoid redundancy in queries when a particular subquery is the base for subsequent computations.</p><pre># CTE<br>WITH avg_per_department AS<br>  (SELECT department_id, AVG(salary) AS average_salary<br>   FROM employee join department on ...<br>   GROUP BY department_id)<br>SELECT e.id, e.name, e.joining_date, avg.average_salary AS avg_for_store<br>FROM employee e<br>JOIN avg_per_department avg<br>ON e.department_id = avg.department_id;</pre><p>&gt; To solve the recursive problems like forming a hierarchy between employee and managers, recursion is the ideal solution.</p><pre>WITH RECURSIVE emp_mgr AS (<br>  SELECT id, name, manager_id<br>  FROM employees<br>  WHERE manager_id IS NULL  -- start with the employees who have no manager<br>  UNION ALL<br>  SELECT e.id, e.name, e.manager_id<br>  FROM employees e<br>  INNER JOIN emp_mgr em<br>  ON em.id = e.manager_id  -- join with the previous results to get the next level<br>)<br>SELECT *<br>FROM emp_mgr;</pre><p>Achieving the above solution for any number of employees without recursion is close to impossible.</p><h4>5. Readability is must</h4><p>SQL is also a code and it will be pushed to production.</p><p>A readable SQL is a must for easier understanding. Try to beautify the SQL and follow best practices.</p><blockquote>An SQL with no formatting</blockquote><pre>select emp_id, emp_name, Department_name,ROW_NUMBER() <br>over (partition by Department_name order by emp_id) as rn from employee join department on ...</pre><blockquote>The same with formatting</blockquote><pre>select <br>  emp_id, <br>  emp_name, <br>  Department_name, <br>  , <br>  ROW_NUMBER() over (<br>    partition by Department_name <br>    order by <br>      emp_id<br>  ) as rn <br>from <br>  employee <br>  join department on...</pre><ul><li>Use proper indentation and new lines.</li><li>There are plenty of beautification tools available online. Many IDEs also do this and it is safer too.</li></ul><p><em>Bye!! Leave a clap if you find this useful!!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8cba704edf79" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[What is Distributed Computing?]]></title>
            <link>https://medium.com/@dineshkumarkct/what-is-distributed-computing-331bd48f856e?source=rss-5153fde22cfd------2</link>
            <guid isPermaLink="false">https://medium.com/p/331bd48f856e</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[hadoop]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[distributed-systems]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Dinesh Kumar A S]]></dc:creator>
            <pubDate>Fri, 25 Nov 2022 09:59:24 GMT</pubDate>
            <atom:updated>2022-11-28T06:22:05.807Z</atom:updated>
            <content:encoded><![CDATA[<h3>What is Distributed Processing? – A Beginner ‘s guide</h3><p>“What is distributed processing ; Why and where is it needed”. The idea behind distributed processing in the data engineering world is ‘<em>Divide and Conquer</em>’. Divide the input and get the work done faster. Let us discuss more!!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*nRsIKMoKCIxVx7AoJUho-A.jpeg" /></figure><p>Let us take this scenario!!</p><p>You have written a program that counts the number of prime numbers in a file containing N numbers. The program takes 5 seconds to run for 1000 numbers in one machine with 4 cores and 6GB RAM.</p><p>If you increase it to 5000, theoretically it will take 5*5 = 25 seconds. But if the requirement is to complete the job within 10 seconds whatever be the size of the input.</p><p>The ideal solution is to “<strong>Just distribute the work</strong>”</p><h4>Worker machines or Slaves</h4><p>Mathematically, you will need 5 machines of the same size to complete them. You split the input by 5 chunks and give them to 5 machines and all 5 are running in parallel. They are the <strong>worker nodes</strong> or slaves in the distributed system. They do the actual work (Which is counting in our case)</p><h4>Master Node</h4><p>Even though workers do the task, someone has to enable this process happening smoothly ; should verify job is happening properly. Typically a manager of these 5 workers and he is the <strong>master node </strong>in the system. He does not do the actual work but he does the supervision.</p><h4>Map and Reduce</h4><p>Splitting the input into smaller subsets is called <strong>Partitioning. </strong>The smaller subsets are assigned to machines and process is started. This is called <strong>Mapping</strong> phase. Now, 5 machines have completed their job of counting prime numbers for their own subset of input. Let us say the individual counts are 10,20,11,40,100. The total = 10+20+11+40+100 = <strong>181. </strong>This process of collecting the intermediate results and summing up to the total number is called <strong>Reducing.</strong></p><h4>Resource Managers</h4><p>We have <strong>resource managers</strong> who are like the bankers owning the resources to run a job. Whenever the master requests for more worker resources (For simplicity, consider resources are nothing but machines), the resource manager provides them. Some examples are YARN, Mesos etc.</p><h4>Having seen the architecture, why is it effective?</h4><ol><li>We are not overloading any machine here. The data is rightly split.</li><li>We don’t need a machine with 100s of nodes and PetaBytes of RAM to do a bigger task. Just use many smaller machines which are cheaper.</li><li>The possibility of scaling and expanding is technically infinite. If you have money to add more machines to your cluster to make work faster, you can scale out your system</li><li>It is different from parallel processing. No sharing of memory. For the user, it is one whole system but underneath every machine is independent in memory and storage. So it is more stable.</li></ol><p><em>Happy Reading!! Leave a clap if you like this blog!!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=331bd48f856e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Apache Spark Optimisation — Part 2]]></title>
            <link>https://medium.com/@dineshkumarkct/apache-spark-optimisation-part-2-a21d27f3b97?source=rss-5153fde22cfd------2</link>
            <guid isPermaLink="false">https://medium.com/p/a21d27f3b97</guid>
            <category><![CDATA[performance-optimization]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[apache-spark]]></category>
            <dc:creator><![CDATA[Dinesh Kumar A S]]></dc:creator>
            <pubDate>Wed, 23 Nov 2022 17:33:40 GMT</pubDate>
            <atom:updated>2022-11-24T08:03:00.612Z</atom:updated>
            <content:encoded><![CDATA[<h3>Apache Spark Optimisation — Part 2</h3><p>Even though we write our Spark code clean, sometimes performance degrades because of the nature of source data, its distribution, quality, volume, throughput. What are the techniques to tackle these? In this blog, we will discuss the next set of optimisation techniques focused on the incoming data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TXZVQqFGqcHZ6aaT0UpKyg.png" /><figcaption>source : <a href="https://www.pngwing.com/">https://www.pngwing.com/</a></figcaption></figure><p>Note : Please refer <a href="https://medium.com/@dineshkumarkct/apache-spark-optimisation-part-1-c325be5637e0"><em>https://medium.com/@dineshkumarkct/apache-spark-optimisation-part-1-c325be5637e0</em></a> for the part 1 in optimisations.</p><h3>Fix the Skew!</h3><p>Skewness is the uneven distribution of data and it is completely based on the nature of the business and logic that generates it.</p><p>If you take an example of YouTube in a geo location and you collect information every second. consider the following cases.</p><ol><li>There are 10 user who watches YouTube round the clock in more than 10 genres in a day.</li><li>There are 1 million users who watch less than 6 hours a day and watch less than 10 genres in a day</li></ol><p>Imagine you have to provide a live recommendation based on the usage for all the users in that location. 99% of your data will be from the 10 users.</p><p>Following are the problems you will face.</p><ul><li>Most of the work you do and the resources you use to enrich and generate recommendations will go to the top 10 customers.</li><li>If you write an application to read real time usage events from Kafka and produce results for all the users in near real time, you might have a partition based on users. This will make the task for the top 10 users to take 10x of the time to complete whereas the remaining tasks would have completed lot sooner and will be waiting for the slower 10 to complete.</li><li>The overall Job time is anyway equal to the time taken by the slowest task to reach completion.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HCUMMT5PwOdqh6tHS3V9Hg.png" /><figcaption>Event Timeline of Skewed Stage</figcaption></figure><ul><li>Another problem is, suppose you provide 100 executors for 100 tasks — which is for 100 users — 90 executors might have completed the task and the 10 executor will be doing the heavy lifting. You are paying for 90 idle executors also since the stage completes only if all tasks complete.</li></ul><p>There are lot of standard techniques to fix the skew. Let me cover few.</p><ol><li>Understand the distribution of data before applying any partitioning in any part of the your data pipeline.</li><li>Salting is a great technique to eliminate the effect of skewness. By Salting, you can add a random value on top of data introducing more partitions within one big chunk.</li><li>Spark 3 has AQE which handles skewness during join by enabling the parameter : <strong><em>spark.conf.set(“spark.sql.adaptive.enabled”,”true”)</em></strong></li></ol><h3>Too many small files spoil the performance!</h3><p>Suppose, if you read data from a data lake or a bucket or a folder containing files, small files issue is more common.</p><p>When there are lot of small files, like in thousands or even lakhs in some cases, Spark driver will need lot of time to list the files before it schedules the job on the executors.</p><p>So, the job will technically never start and it will be stuck in the listing stage itself.</p><p>Ways to solve them are:</p><ol><li>If the upstream system is in your scope of development, try consolidating files into bigger chunks.</li><li>If not, implement a program before the Spark application to combine small files into bigger files — <strong>Compaction</strong>. Thus the main application will have lesser and big enough partitions to run efficiently.</li></ol><h3>Memory allocation : Dynamic or Static?</h3><p>Spark gives you two options in memory allocation.</p><ol><li>You can define your own configuration parameters (number of executors, num of cores per executors, driver memory, executor memory etc … ) at the start of the application.</li><li>You can enable dynamic allocation where the Spark Application can get the necessary resources from the cluster whenever needed and complete the application asap.</li></ol><p>When <em>spark.dynamicAllocation.enabled=true</em>?</p><ul><li>If you don’t have a consistent throughput (in case of streaming app) or the size of the input varies time to time (in batch).</li><li>If the SLA is too low and cost is lower in priority compared to the performance and time taken for the app to complete.</li><li>If you are not confident about all the configuration parameters in Spark.</li></ul><p>When <em>spark.dynamicAllocation.enabled=false</em>?</p><ul><li>If the incoming data is consistent in throughput and volume.</li><li>If you have a limitation on the resources to be allocated and you are not running a high priority applications and an occasional delay is okay.</li><li>If you are using a shared cluster among other high priority applications.</li><li>If you are aware of the configuration parameters in spark submit.</li></ul><h3>No random values in configuration</h3><p>Spark can complete a task faster than any other systems if the proper resources such as memory and CPU are provided.</p><p>Improper settings may make the job slower and sometimes it can even die in the middle and never reach completion eventhough you have a big enough cluster to complete the work.</p><p>Few of the parameters to be careful about are</p><ul><li>Choose proper values for Number of cores per executor, Number of executors, Exec Memory, Driver Memory. Refer this doc for an example : <a href="https://spoddutur.github.io/spark-notes/distribution_of_executors_cores_and_memory_for_spark_application.html">https://spoddutur.github.io/spark-notes/distribution_of_executors_cores_and_memory_for_spark_application.html</a></li><li><strong><em>Shuffle Parallelism</em></strong> : The default is 200 but we can increase or decrease based on our needs. Increase to a higher values if the data size is in Gigs and 200 partitions might produce very big partitions and you have executors sitting idle. Decrease the same to a lesser number probably 10 or 20 in cases where data is small in size and fewer tasks will be beneficial with the available cluster resources.</li><li><strong><em>Default Parallelism</em></strong> : The default value is the total number of cores on your system. RDD will be created from a source with number of partitions equal to default parallelism. Adjust this number based on the subsequent stages in the job.</li><li>spark.sql.shuffle.partitions is not spark.default.parallelism!!</li><li><strong><em>Coalesce and repartitioning</em></strong> : Coalesce and repartition may be used to combine small files at the target side but if it is done in the middle, it will have fewer tasks from that point which may slow down subsequent stages since some executors may sit idle with no tasks.</li><li><strong><em>Number of target partitions </em></strong>: At the target, choose the coalesce and repartition number wisely. There is no magic number that fits for all. Get your desired size of each partition at the target location which is good for the downstream systems and arrive at the partitions based on the size.</li></ul><p>Part 1: <a href="https://medium.com/@dineshkumarkct/apache-spark-optimisation-part-1-c325be5637e0">https://medium.com/@dineshkumarkct/apache-spark-optimisation-part-1-c325be5637e0</a></p><p><em>See you on another blog!! Leave a clap if you find this interesting!!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a21d27f3b97" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Warehousing with Snowflake]]></title>
            <link>https://medium.com/@dineshkumarkct/why-is-snowflake-popular-39911f1dfba6?source=rss-5153fde22cfd------2</link>
            <guid isPermaLink="false">https://medium.com/p/39911f1dfba6</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[snowflake]]></category>
            <category><![CDATA[etl]]></category>
            <dc:creator><![CDATA[Dinesh Kumar A S]]></dc:creator>
            <pubDate>Fri, 18 Nov 2022 10:08:16 GMT</pubDate>
            <atom:updated>2022-11-24T04:24:41.547Z</atom:updated>
            <content:encoded><![CDATA[<p>Snowflake is gaining popularity and the adoption among the organisations is increasing day by day. Why is it popular? Let us discuss!!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/512/1*7cTd056drGZn9tjjV1nG0g.png" /><figcaption>source : <a href="https://icon-icons.com/">https://icon-icons.com/</a></figcaption></figure><h3>Snowflake is Columnar !!</h3><p>Columnar databases are well suited for analytical workloads and processing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*NbCmyQyxrCAozBKt9T8xzg.png" /><figcaption>source: <a href="https://www.techtarget.com/searchdatamanagement/definition/columnar-database">https://www.techtarget.com/searchdatamanagement/definition/columnar-database</a></figcaption></figure><ul><li>Row based databases are efficient for atomic read, writes and updates. Well suited as a backend database for applications. But, it is bad for aggregations and analytical queries for reporting. The reason is, analytical queries will require more rows for aggregation and things and this will read more blocks of memory from the disk.</li><li>On the other side, Columnar based databases are efficient for analytical queries since it stores all the records belonging to a single column in one or fewer memory block.</li></ul><p><em>Refer the diagram above to visualise how data is stored in a row oriented versus columnar database.</em></p><ul><li>Snowflake is columnar and well suited for analytical layers, data warehousing and reporting requirements.</li></ul><h3>De-Coupled Storage and Compute !!</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/858/1*TqbbsJZpibCNTURrpSI8Pw.png" /><figcaption>source: <a href="https://docs.snowflake.com/">https://docs.snowflake.com/</a></figcaption></figure><p>There are many scenarios where the user has less volume of data but high volume of queries fired. There are cases where the ingestion data volume is high but the queries are not frequent and not heavy.</p><p>The databases where both are coupled forces you to spend for both storage and compute even though one of them is not needed for your use case.</p><p>Snowflake has storage and compute de-coupled. So the choice of scaling is ours.</p><h3>Storage is Cheaper !!</h3><p>Snowflake stores data in S3/Blob Storage as micro-partitions.</p><p>They are able to achieve performance by their proprietary architecture by reading their micro partitions.</p><p>Since storages like S3 are cheaper compared to disks attached to databases, the storage is very cheap in snowflake</p><h3>Pay for what you use !!</h3><p>For users with inconsistent workloads both in ingestion and querying, snowflake is ideal.</p><p>Since the compute resources are elastic, when there is no process using the compute for a configured amount of time, it will turn the warehouse off. So no cost for the warehouse during non-business hours.</p><p>Also, elastic scaling gives options to scale the size of the warehouse up and down based on the traffic/workload.</p><p>So, maximum number of warehouses during peak hours and minimum during low traffic hours and completely turn it off when there is no query at all.</p><h3>No maintenance overhead !!</h3><p>No separate maintenance activities are needed for snowflake.</p><p>There are databases which will require you to take care of the house keeping activities like Vacuuming, Freeing up space etc. But snowflake does not require users to take care of them.</p><h3>Storage is Centralised !!</h3><p>Imaging a scenario you have two businesses running in your organisation and both of them have their own data marts.</p><p>In a traditional databases design where data and storage is coupled tightly, we create two databases or clusters (if MPP) holding data in two independent places.</p><p>When a scenario comes requiring a joint reporting between both of them, we need to think of another layer to integrate both or to build a federated view. All these are time taking and inefficient.</p><p>Since snowflake has no limitation on storage, we can keep all the data marts together under one account and create separate schemas for each businesses. When needed to join and report, we can simply join across schemas.</p><h3>You got Clustering !!</h3><p>Clustering is similar to indexing in MySQL and Sorting and Distribution in Redshift.</p><p>It is to re-organise data after every ingestion or update in the underlying data. Better organised the data inline with the reporting requirement is, better is the performance.</p><h3>Things to consider!!</h3><ul><li>Even though it is based on the pay per use model, we have to be cautious on the usage since snowflake is costlier.</li><li>One wrong setting in warehouse configuration, cost may shoot up and eat your monthly budget in a day itself.</li><li>Auto-clustering is needed for better performance in low SLA reporting and it comes with a cost. This is generally a default feature in data warehouses like redshift since we buy the cluster as a whole.</li><li>Ingestion is always in bulk since atomic insert is not recommended in columnar stores. So, as we know, not a candidate for non-analytical and real time requirements.</li></ul><p><em>Leave a clap if you find this interesting!!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=39911f1dfba6" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Apache Spark Optimisation-Part 1]]></title>
            <link>https://medium.com/@dineshkumarkct/apache-spark-optimisation-part-1-c325be5637e0?source=rss-5153fde22cfd------2</link>
            <guid isPermaLink="false">https://medium.com/p/c325be5637e0</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[apache-spark]]></category>
            <category><![CDATA[data-engin]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[performance-optimization]]></category>
            <dc:creator><![CDATA[Dinesh Kumar A S]]></dc:creator>
            <pubDate>Tue, 15 Nov 2022 12:36:03 GMT</pubDate>
            <atom:updated>2022-11-24T04:25:09.460Z</atom:updated>
            <content:encoded><![CDATA[<h3>Apache Spark Optimisation — Part 1</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1JvHRSmPVDcDw9PvG6_ADA.png" /><figcaption>Image source: <a href="https://www.pngwing.com/">https://www.pngwing.com/</a></figcaption></figure><p>Writing an efficient Spark program is mandatory for achieving better performance and not to spend more on the infrastructure.</p><p>There are lot of techniques for optimisation like</p><ul><li>Choosing right partitions at input, shuffle and output stages</li><li>Repartitioning</li><li>Persisting and Caching</li><li>Handling skews by Salting</li><li>Speculative execution</li></ul><p>But before we go there (Will be covered in another blog), writing an efficient code irrespective of the nature of data it handles is important.</p><p>Following is the basic checklist while writing any spark program (Batch or Streaming)</p><h3>No extra actions in the flow of the code</h3><ul><li>Every action triggers a job for itself and it will take time.</li><li>If a collect is not needed or it can be achieved by some other ways in the logic of the program, avoid them.</li></ul><h3>Filter data as early as possible</h3><ul><li>Always read data which is required for the application</li><li>Make use of predicate pushdown feature.</li><li>If it has to be read as a whole due to a limitation, then apply filter immediately after reading and keep only the required data for subsequent operations.</li></ul><h3>Cache - A double edged sword</h3><ul><li>Cache is very effective in scenarios where you have repetitive actions on an intermediate dataset. It will avoid redundant steps.</li><li>But Caching holds memory. If it is really needed and it is small enough to fit in memory, use it. Otherwise, it is better to avoid caching.</li></ul><h3>No Python UDFs</h3><ul><li>Catalyst Query Optimiser will not be utilised when we write Python UDFs. So it is always better to avoid UDFs and leverage built-in functions</li><li>Scala UDFs are better since it avoids an extra step of serialisation and deserialisation.</li><li>If we have to still go for UDFs, we can consider alternate options like Pandas UDFs or using Scala UDFs inside PySpark Program</li></ul><h3>Can I use broadcast join?</h3><ul><li>Even though we have many join types, it is always, “When to use broadcast join and when not to use it?”</li><li>Use broadcast join only if that can increase performance.</li><li>Also, if one of the tables is smaller enough to be broadcasted, broadcast it. Else, it is better not to broadcast to avoid OOM issues.</li></ul><h3>Prefer foreachPartition over foreach</h3><ul><li>Evaluate the situation and use the right option.</li><li>If you have scenarios like using a database connection for every record in foreach, shift to foreachPartition. It will create a connection for each partition and not for every row like in foreach.</li></ul><h3>Stick to Spark Built-in functions</h3><ul><li>The primary advantage of using Apache Spark APIs is “<em>The built-in functions are optimised. There is an optimiser (Catalyst query optimiser) to optimise our query/program if we use Spark built-in functions</em>”</li><li>If we write more Python/Scala functions in the PySpark/Spark application, then there is no benefit of using Spark.</li></ul><p><em>See you on another blog!! Leave a clap if you find this interesting!!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c325be5637e0" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>