<?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 Burhanuddin Bhopalwala on Medium]]></title>
        <description><![CDATA[Stories by Burhanuddin Bhopalwala on Medium]]></description>
        <link>https://medium.com/@burhanuddinbhopalwala?source=rss-76c303c51d34------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*GFTup7kCiuoeg9jPEwp0Qw.jpeg</url>
            <title>Stories by Burhanuddin Bhopalwala on Medium</title>
            <link>https://medium.com/@burhanuddinbhopalwala?source=rss-76c303c51d34------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sun, 22 Nov 2026 21:07:23 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@burhanuddinbhopalwala/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[BigData/DBA: Querying a Database Table with over a Billion Rows]]></title>
            <link>https://levelup.gitconnected.com/bigdata-dba-querying-a-database-table-with-over-a-billion-rows-2278f17d3e69?source=rss-76c303c51d34------2</link>
            <guid isPermaLink="false">https://medium.com/p/2278f17d3e69</guid>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[architecture]]></category>
            <dc:creator><![CDATA[Burhanuddin Bhopalwala]]></dc:creator>
            <pubDate>Fri, 26 Aug 2022 20:44:51 GMT</pubDate>
            <atom:updated>2022-12-17T12:01:46.888Z</atom:updated>
            <content:encoded><![CDATA[<h4>Querying a Relational Database(s) with size in TBs and PBs, aka Very Large Databases (VLDB)</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/626/1*Ky79awKprGNVxeS1u13kKA.jpeg" /><figcaption>photo-credit: rkimball.com</figcaption></figure><p>Hey folks! In this brief article, I’ve jotted down the various de-facto Database (DB) optimization techniques particular to VLDBs. Most importantly, I’ve covered a summary of the mental model around structuring these optimization techniques before applying or jumping back to a specific one.</p><blockquote><em>The inspiration for writing this article: In quite a few of my past projects, where I was working with VLDBs, I’ve realized that to solve the DB problem of READ/WRITE latencies — we often too quickly end up getting onto very advanced techniques like sharding or NoSQL migration before ensuring the basic yet powerful naive optimization techniques are in check. This article is the past reflection of my experience to solve such problems.</em></blockquote><p>Let me start with a famous quote from the DB world, here it goes:</p><blockquote>The best way to query a table with a billion rows is to avoid querying a table with a billion rows.</blockquote><h3>Table of contents</h3><ol><li><strong>De-facto DB optimization techniques &amp; mental models around implementing them</strong></li><li><strong>Indexing digest! — Detailing on indexing, types of the index scan/s, and examples around that</strong></li><li><strong>The gist of other techniques: partitioning, replication, and sharding in brief.</strong></li></ol><h3>1. Naive DB optimization techniques</h3><p>When it comes to relational DB(s), there are various simple and effective strategies available to optimize the READ/WRITE IOPS or latencies. Below is the summary of all the techniques to be checked in order.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*stnd_4z7fDWDOXrzStyOIg.png" /><figcaption>DB practices</figcaption></figure><p>It is understandable from the above picture that to optimize READ/WRITE latencies of VLDBs one should think of <strong><em>indexing</em></strong>, <strong><em>caching </em></strong>&amp; <strong><em>replication</em></strong> techniques in place before actually investing efforts in advanced techniques like <strong><em>sharding</em></strong>, or<strong><em> moving to NoSQL </em></strong>databases which have their disadvantages [not discussing here].</p><h3>2. Indexing digest</h3><p>Since Indexing is a much more popular practice than others mentioned above, in this section, I will demonstrate how one can fully leverage indexing at its best. Thus, it can query the records from the table of such VLDB without a single scan of the source table.</p><p>Let’s start with the below snapshot attached, It mentions all the indexing levels and at each level extends its enhancements to the right side using the right arrow.<strong> Level 1</strong> is the sequential scan (no indexing) by default whereas <strong>level 3 </strong>is the index scan.</p><p>The upwards arrow from <strong>level x</strong> to <strong>level x-1</strong> is to show the fallback scan. If index scan fails it fallbacks to bitmap scan and if bitmap scan fails it fallback to sequential scan.</p><p><em>So note that if the indexing is not configured properly, the underlying scan will always be fallback to a sequential scan or a parallel sequential scan based on the DB capabilities.</em></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*y1iys9uXeQ65i0y7SOsgDQ.png" /><figcaption>query latency optimization leveraging DB index</figcaption></figure><p>Before explaining the above snapshot consider a scheme of tables as:<br><strong>public.vehicle_histories: (vehicle_no, timestamp, lat, long, speed, distance, state). </strong>Let me define a target query to be like this to understand each of the 3 levels mentioned above:</p><pre>EXPLAIN ANALYZE<br>SELECT  <br>    UPPER(vehicle_no),  <br>    CAST(pst_timestamp AS DATETIME),  <br>    latitude :: NUMERIC,  <br>    longitude :: NUMERIC<br>FROM  vehicle_histories<br>WHERE  CASE1 | CASE2 | CASE3<br>ORDER BY  pst_timestamp DESC<br>LIMIT  10;</pre><h4>Case 1/Level 1: No Index Sequential Scan</h4><p><strong>WHERE: vehicle_no = “XYZ” <br></strong>This would be a sequential scan and can be further optimized to a parallel sequential scan using an MPP database.</p><p><em>Massively Parallel Processing (MPP) DB like PostgreSQL, AWS Redshift (fork of PostgreSQL), Snowflake. Usually the main trait of a Datawarehouse DB.</em></p><h4>Case 2/Level 2: Bitmap Scan</h4><p><strong>Index on: </strong>vehicle_no_and_vehicle_timestamp_idx + pst_timestamp_idx<br><strong>WHERE:</strong> vehicle_no = “XYZ” <strong>OR</strong> pst_timestamp BETWEEN (T1, T2)</p><blockquote><em>vehicle_no_vehicle_timestamp_idx =&gt; This is an example of a </em><strong><em>multi-column</em></strong><em> index as 2 or more columns/attribute constitute the index.</em></blockquote><p>In this case, we end up with a<strong> bitmap index scan</strong>. You can think of it as an intersection between 2 or more indexes. This is much better than a parallel/sequential scan but still not as fast as an <strong>index scan or index-only scan</strong> (we will see this in the later cases)</p><p>Also, note that since this index scan is an intersection of 2 indexes we have a second index on pst_timestamp. It is important to perform such <strong>OR queries using bitmap scans</strong>.</p><h4>Case 3: Index Scan</h4><p><strong>Index on: </strong>vehicle_no_pst_timestamp_idx<br><strong>WHERE: v</strong>ehicle_no = “XYZ” <strong>AND</strong> pst_timestamp BETWEEN (T1, T2)</p><p>This is a happy case scenario. The database will use the index heap file to find the <strong><em>row ID &amp; page number of the source table </em></strong>and then jump to the source table heap file to get the data. But jumping to the source heap file and look up the rows is still overhead and eventually leads to degrading the query &amp; index performance.</p><blockquote><em>As written above, the index scan is still more efficient than the bitmap index scan.</em></blockquote><h4>Case 3/Sub case: Index Only Scan (most efficient)</h4><p><strong>Index on: </strong>vehicle_no_pst_timestamp_idx <strong>WITH</strong> latitude &amp; longitude<br><strong>WHERE: </strong>vehicle_no = “XYZ” AND pst_timestamp BETWEEN (T1, T2)</p><blockquote><em>vehicle_no_vehicle_pst_timestamp_idx </em><strong><em>WITH</em></strong><em> lat &amp; long =&gt; This is an example of a </em><strong><em>multi-line index</em></strong><em> where we made the index on </em><strong><em>vehicle_no</em></strong><em> &amp; </em><strong><em>pst_timestamp</em></strong><em> only but we are storing additional columns such as </em><strong><em>latitude</em></strong><em> and </em><strong><em>longitude</em></strong><em> in the index heap file itself as an extra line of information to avoid scanning source table heap file.</em></blockquote><p>It is important to note that <strong>there is a difference between a multi-column index and a multi-line index</strong>. In a multi-line index, we store additional lines as information unlike in a multi-column index where we only have an index and no extra attributes/columns.</p><p>In any Database, there are always some 20% of the queries which run 80% of the time. Here we can fully utilize the power of index by making the scan as Index only scan for the handful of queries.</p><p>I hope all 3 cases and their sub-cases now seem to be pretty much straightforward for all. Thanks for reading this so far as a Bonus, I wanted to share a few more techniques that can further be used after Indexing.</p><h3>3. The gist of other techniques</h3><p>Let’s talk about the other techniques <strong>in order </strong>apart from indexing that can be used to enhance the READ/WRTIE latencies. As per the below snapshot:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*stnd_4z7fDWDOXrzStyOIg.png" /><figcaption>DB performance enhancements</figcaption></figure><ul><li><strong>Caching: </strong>Caching can be used to reduce the count of queries per cache TTL. This will indirectly reduce the DB CPU and memory usage. This can be done at the application level mostly using an in-memory cache like REDIS.</li></ul><p>But, It is worth noting that caching can be done and configured at the DB level as well. Mainly PostgreSQL uses caching for storing the data, index data, and even the query cost/planners.</p><blockquote>You can configure the PostgreSQL caching by changing the <strong>shared_buffer </strong>and can increase this up to &lt; 30% of the system RAM from 128MB (default).</blockquote><ul><li><strong>Partitioning: </strong>As the name entails, we can have the partitioning on region or timestamp. For example in the case of the above example the partitioning would look like this:</li></ul><p><strong>Parent table:</strong> event_histories</p><p>Partition field: pst_timestamp @ monthly frequency<br><strong>Child table 1:</strong> event_histories_jan_2022_feb_2022<br><strong>Child table 2: </strong>event_histories_feb_2022_mar_2022<br><strong>Child table 3: </strong>event_histories_mar_2022_apr_2022</p><blockquote><em>Usually, as a cleaning service monthly job runs to delete a partition older than 3–6 months and do the vacuuming etc. The partitioning technique is useful for reading but at the same time, it is highly beneficial for BULK WRITES.</em></blockquote><ul><li><strong>Replication</strong>: In this approach, we have a master-slave architecture/arc where the master use WRITES and SLAVE use READS.</li><li><strong>Sharding</strong>: Sharding should always be the last-ditch effort to optimize the DB &amp; Query performance. The complexity and maintenance overhead can be cumbersome esp at the application level. But there are tools available to formalize the process of sharding for different databases. For example MySQL: <a href="https://vitess.io/">https://vitess.io/</a></li></ul><p>We have covered a lot in this article regarding Relational DB’s performance enhancements. I hope you like the article.</p><p><em>Thanks for reading!</em></p><p><strong><em>Connect </em></strong>🤝<strong><em>:</em></strong></p><ul><li><strong><em>Email</em></strong>: <em>bbhopalw@gmail</em></li><li><strong><em>Linkedin</em></strong>: <a href="http://www.linkedin.com/in/bbhoaplw"><em>www.linkedin.com/in/bbhoaplw</em></a></li></ul><p><strong><em>For further reading </em></strong>✍️<strong><em>:</em></strong></p><p><strong><em>Big Data &amp; Cloud Engineering blogs:</em></strong></p><ul><li><strong><em>Towards Data Science Publication:</em> </strong><a href="https://medium.com/@burhanuddinbhopalwala"><em>https://medium.com/@burhanuddinbhopalwala</em></a></li></ul><p><strong><em>Backend Engineering &amp; Software Engineering blogs:</em></strong></p><ul><li><strong><em>DEV Community:</em><br></strong><a href="https://dev.to/burhanuddinbhopalwala"><em>https://dev.to/burhanuddinbhopalwala</em></a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2278f17d3e69" width="1" height="1" alt=""><hr><p><a href="https://levelup.gitconnected.com/bigdata-dba-querying-a-database-table-with-over-a-billion-rows-2278f17d3e69">BigData/DBA: Querying a Database Table with over a Billion Rows</a> was originally published in <a href="https://levelup.gitconnected.com/">Level Up Coding</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[COVID-19 & Machine Learning]]></title>
            <link>https://medium.com/data-science/covid-19-machine-learning-4f064df53c43?source=rss-76c303c51d34------2</link>
            <guid isPermaLink="false">https://medium.com/p/4f064df53c43</guid>
            <category><![CDATA[python-programming]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[covid19]]></category>
            <category><![CDATA[artificial-intelligence]]></category>
            <dc:creator><![CDATA[Burhanuddin Bhopalwala]]></dc:creator>
            <pubDate>Thu, 26 Mar 2020 01:05:19 GMT</pubDate>
            <atom:updated>2023-07-07T18:11:01.902Z</atom:updated>
            <content:encoded><![CDATA[<h4>A potential Machine Learning approach that can help control COVID-19</h4><p><strong><em>Successfully</em></strong> <strong><em>submitted to: </em></strong><a href="https://innovate.mygov.in/covid19/"><strong><em>https://innovate.mygov.in/covid19/</em></strong></a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6r3nRdQnxoKng_Q2u0F6zw.jpeg" /><figcaption>Credit: <a href="https://unsplash.com/">https://unsplash.com/</a></figcaption></figure><blockquote><strong>Note:</strong> My approach will focus on addressing the challenges that <strong>India </strong>(my home country) is having to assess the impact of COVID-19. But the below method can easily be generalized as other affecting countries are also having similar challenges as in India.</blockquote><blockquote>In addition, I have made this ML approach by considering the learnings from highly infected and worst-hit countries like <strong>Italy, Spain, UK, the USA</strong>, and highly successful countries in controlling the COVID-19 pandemic like <strong>South Korea; China-Wuhan, Australia</strong>,<strong> UAE</strong>!</blockquote><h3><strong>Table of contents?</strong></h3><ol><li><strong><em>Addressing the main problems/challenges to controlling COVID-19 spread + death toll in India &amp; other Countries?</em></strong></li><li><strong><em>My proposed idea/solution</em></strong></li><li><strong><em>ML problem formulation &amp; solution addressing the above challenges</em></strong></li><li><strong><em>A potential ML approach/solution</em></strong></li><li><strong><em>Is this a permanent solution?</em></strong></li></ol><h3><strong>1: <em>Addressing the main problems/challenges to control COVID-19 spread + death toll in India &amp; other Countries?</em></strong></h3><p><strong>— The problem is:</strong></p><p><strong><em>Lack of focused Testing!!!</em></strong></p><p><strong><em>1.1: Why Testing is important</em></strong></p><ul><li>Testing allows infected people to<em> know</em> that they are infected. This can help them receive the care they need, and it can help them take measures to reduce the probability of infecting others. People who don’t know they are infected might not stay at home and thereby risk infecting others.</li><li>Testing is also crucial for an appropriate response to the pandemic. It allows us to understand the spread of the disease and take evidence-based measures to slow it down the spread of the disease.</li><li>Unfortunately, the capacity for COVID-19 Testing is still low in many countries, including INDIA and around the world. For this reason, we still do not have a good understanding of the spread of the pandemic.</li></ul><p><em>How does the COVID-19 test work: </em><a href="https://www.youtube.com/watch?v=AUmVgDlutE0"><em>here</em></a></p><p><strong><em>1.2: Then, What should be the testing threshold?</em></strong></p><p>To find this, we need to see understand the visualization. I have also attached the link to the entire <strong><em>ipython</em></strong> notebook below.</p><p>Data source: <a href="https://ourworldindata.org/grapher/covid19-tests-per-million-people">https://ourworldindata.org/</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CVFox3aouP6XALXUfCxj4Q.png" /><figcaption>2 Y-axis bar plot per million</figcaption></figure><ul><li>Countries like <strong><em>China-Wuhan, South Korea, and Australia</em></strong> have been prosperous till now in flattening the curve for COVID-19 spread because they have increased the testing_per_million according to their population_per_million.</li></ul><blockquote>Clearly, from the above visualization, we can conclude that to control COVID-19 spread, the <strong>(test_per_million ≥ population_per_million * 100) means that a population of 1 million needs at least 100 tests capacity.</strong></blockquote><p>Countries like <strong><em>India, the USA, the UK, Spain, and Italy</em></strong> have not been able to match the above testing criteria and have been still struggling with the COVID-19 pandemic, even with <strong>testing_per_million &gt; population_per_million.</strong></p><p><strong><em>1.3: What are the Challenges in increasing the # of testing_per_million for INDIA &amp; other countries? And why is it needed in multiples?</em></strong></p><ul><li><strong><em>India &amp; other affected countries have limited health resources. </em></strong><em>India has eight doctors per 10,000 people compared to 41 in Italy and 71 in South Korea.</em></li><li><em>The early stage of the disease (first 1–3 days) with a viral load is too low to be detected. This results in a false-negative, and hence it requires multiple tests/individuals, which eventually results in testing_per_million should be at least greater than or equal to 100 folds of population_per_million, which we can learn from other successful countries&#39; data.</em></li><li><em>Testing is a manual process that results in </em><strong><em>handling &amp; human errors</em></strong><em>.</em></li></ul><p><em>Why We Don’t Have Enough COVID-19 Tests (detailed): </em><a href="https://medium.com/@ASlavitt/why-we-dont-have-enough-coronavirus-tests-3cfee1c47264"><em>here</em></a></p><h3>2: My proposed idea/solution</h3><p>So, if the problem is a Lack of focused Testing!!! Then the solution for the COVID-19 pandemic is nothing but:</p><blockquote><strong>“Test Test Test &amp; then Quarantine”</strong></blockquote><p>But, I had already mentioned that the Challenges in increasing # of Testing are not very trivial, especially when INDIA and other countries, even the USA, UK, Italy, and Iran, also had minimal health resources and have the same problem.</p><p>In Computer Science, there is a prevalent saying: <strong>“The best solutions are the simplest ones!”</strong>. We need to increase the # of tests per million in very little time, and here I am proposing my idea/solution to this problem:</p><blockquote>We should prioritize the Testing using <strong>the Priority-based Automated Testing System (PbATS) </strong>using ML/AI. This helps prioritize the Testing and helps in determining the priority of giving limited healthcare services.</blockquote><blockquote>Using PbATS, we will categorize the population based on their input. Doing this, let’s say we categorize the population into three categories as follows:</blockquote><blockquote><strong>Category 1 (Self-Quarantined):</strong> They are either not affected or are still not showing the systems but affected (the false negatives). It mainly contains the unaffected &amp; some of the false-negative cases. Hence, they will need to undergo the PbATS periodically up to a specific duration.</blockquote><blockquote><strong>Category 2 (Test-Priority):</strong> This category contains some false-negatives but started showing symptoms and hence need manual Testing</blockquote><blockquote><strong>Category 3</strong> <strong>(Healthcare services-Priority)</strong>: This category consists of highly likely people affected with COVID-19, and hence, they should be strictly Quarantined and have been given priority hospital services.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JjlD00vOzd5LLRkaTEhwDg.png" /><figcaption>PbATS Workflow</figcaption></figure><blockquote><strong>Note:</strong> <strong>This PbATS mechanism will be an AI/ML approach with which we categorize these three categories based on the historical data of all the past COVID-19 patients</strong>. And as we all know, that “All Machine Learning models are wrong and only some are useful” — Considering this also, my solution contains multiple attempts for category one and category 3, as category two already having manual Testing in place.</blockquote><blockquote><strong>Also, the # of categories and periodicity can be decided accurately with the help of Domain Experts/COVID-19 Experts.</strong></blockquote><h3>3: ML problem formulation &amp; solution addressing the above challenges</h3><p>Now, For the above solution to work, we need robust PbATS. I will now demonstrate a potential approach to building this PbATS using Machine Learning techniques.</p><blockquote><strong><em>ML Problem formulation: </em></strong><em>We need to categorize the population based on there, features (age, sex), symptoms, and past travel history.</em></blockquote><blockquote><strong>Solution workflow: </strong>Once this PbATS is ready, each needs to undergo this PbATS by filling their details digitally or via a volunteer (to minimize the errors), and then Doctors can easily prioritize the Testing /Healthcare services and can select whom to Quarantined. Which helps Doctors and GOV to stop COVID-19 with minimal health resources.</blockquote><p><em>Eventually, This PbATS Mechanism will indirectly help the Government &amp; Healthcare providers to increase the </em><strong><em>test_per_million</em></strong><em> according to </em><strong><em>the population_per_million</em></strong><em> needed to stop the COVID-19 spread.</em></p><h3>4: A potential ML approach/solution</h3><p><strong><em>4.1: Data?</em> </strong>For<strong> </strong>this, I have taken the data and scraped the data (using text mining techniques), and concatenated them. Data sources are as follows:</p><ul><li><a href="https://ourworldindata.org/coronavirus">https://ourworldindata.org/coronavirus</a></li><li><a href="https://www.kaggle.com/tags/covid19">https://www.kaggle.com/tags/covid19</a></li><li><a href="https://www.who.int/docs/default-source/coronaviruse/situation-reports/20200323-sitrep-63-covid-19.pdf?sfvrsn=d97cb6dd_2">https://www.who.int/docs/default-source/coronaviruse/situation-reports/20200323-sitrep-63-covid-19.pdf?sfvrsn=d97cb6dd_2</a></li></ul><p><strong><em>Here are what the raw data looks like:</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fDiQDte-otztOTRiJk-_cg.png" /><figcaption>sample raw data</figcaption></figure><p><strong><em>4.2: After data cleaning:</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8QZXGOiQNerWOGrgMPlkrg.png" /><figcaption>data-cleaning</figcaption></figure><p><strong><em>4.3: Then, I did some feature engineering as follows:</em></strong></p><ul><li>Convert the age range (60–65) to its mean value (62.5)</li><li>Added <strong><em>date_past_onset_symptoms (dpos) </em></strong>which is the difference in the # of days b/w date_confirmation &amp; date_onset_symptoms</li><li>Added <strong><em>travel_history (th)</em></strong> flag based on an operation between travel_history_dates &amp; travel_history_locations</li><li>Finally, merge all the features as one <strong><em>master-symptom/mixed symptom</em></strong> from the entire data point. Why I have done this will be more apparent later</li></ul><p><strong><em>4.4: Final dataset:</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4TX-t0xKkwXIEv16ym2yhg.png" /><figcaption>final dataset</figcaption></figure><p><strong><em>4.5: Natural Language Processing (NLP) on master symptom</em></strong></p><p>Then, I have performed all the standard NLP techniques for vectorizing the master-symptom. I have used <strong><em>BoW+W2V (Word2Vec-gensim)</em></strong>. I am using W2V because I need to cluster the symptoms based on the <strong>relationship (not similarity &amp; counts)</strong>, which helps in the clustering process.</p><p>I have used <strong><em>BoW instead of TF-IDF</em></strong> because Our Dataset does not have many rare, occurring words that need more importance.</p><p><strong><em>4.6: ML Clustering:</em></strong></p><p><em>Finally, I have done clustering using</em><strong><em> KMeans++ </em></strong><em>(most general purpose). The no of clusters we are getting using the elbow method is also 3 (coincidentally), but it can be changed using much more </em><strong><em>Domain Knowledge</em></strong><em> also!</em></p><p><strong><em>4.7: Results/Word Cloud:</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*qC-ApSkMqLyjQk9deRUB8Q.png" /><figcaption>3-clusters based master-symptoms, dpos: days-past-onset-symptoms, th: travel-history</figcaption></figure><p><strong><em>Final Categories:</em></strong> As you can see from the above word clouds of <strong>master-symptoms</strong>, We can approximately categorize the prone populations as:</p><blockquote><strong>Category 3 (Healthcare service-Priority): </strong>[Age between 30–75] + [fever, cough, respiratory, runny nose, sore throat, pneumonia, headache, chest tightness with <strong>dpos &gt; 4</strong>] + [<strong>th-1</strong> active travel histories mainly]</blockquote><blockquote><strong>Category 2 (Test-Priority):</strong> [Age between 0–75] + [fever, cough, malaise, pneumonia, stiffness, joint, muscular soreness with <strong>dpos between 1–3</strong>] + [<strong>th-0/1</strong> active or inactive travel histories]</blockquote><blockquote><strong>Category 1 (Self-Quarantined):</strong> [Age between 0–75] + [fever, cough, weakness, diarrhea, dizziness, chills with <strong>dpos between 1–3</strong>] + [<strong>th-0</strong> mostly inactive travel histories with some active travel histories also]</blockquote><p><strong><em>learning: </em></strong><em>As we can see, the symptoms patterns </em><strong><em>(mixed symptoms)</em></strong><em> depend on </em><strong><em>Age, </em></strong><em>mainly in the case of COVID-19, and gets severe with </em><strong><em>dpos</em></strong><em>. My efforts were to detect these patterns only for prioritizing the Testing using PbATS.</em></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0d7b65c0ef17e4e7b500aa96dc8724c6/href">https://medium.com/media/0d7b65c0ef17e4e7b500aa96dc8724c6/href</a></iframe><blockquote>Similar implementation: <a href="https://coronariskcalculator.in/">here</a></blockquote><h3>5: Is this a permanent solution?</h3><p><strong><em>Simply No! </em></strong>The above solution only helps INDIA and other countries to stop COVID-19 spread with limited Healthcare services. And hence helps in the containment of Sars-CoV-2.</p><p>This permanent solution is <strong><em>Vaccines</em></strong>, which require weeks and even months to be ready. This gap can easily field also using ML/AI by finding a vaccine using a combination of existing virus vaccines (Sars-1, Spanish flu), etc., using ML/AI techniques.</p><p><strong>Last</strong>, yes, the above approach address limited Healthcare resources problems. Still, Countries like India and others need to have at least 30%-40% of the population per million Healthcare resources (Beds/Ventilators) for category 3.</p><p><strong><em>This blog is my small contribution battle against COVID-19 esp prevalent in INDIA. Submitted to: </em></strong><a href="https://innovate.mygov.in/covid19/"><strong><em>https://innovate.mygov.in/covid19/</em></strong></a></p><p>That’s pretty much of it!<em><br>Connect:</em> <strong><em>bbhopalw@gmail.com</em></strong></p><p><strong><em>PS:</em></strong></p><ul><li><em>Requests regarding datasets — I have made both the </em><strong><em>Dataset &amp; Web Scraping code written in nodeJS</em></strong><em> open-source, available on my GitHub.</em></li><li><em>Regarding </em><strong><em>collaboration</em></strong><em>, please email me directly.</em></li><li><em>This is the original blog. Readers may find this in other Data Science </em><strong><em>newsletters (upGrad, DS news)</em></strong><em> also (republished!).</em></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/456/1*27OlQ0k63YDhsxwFzu945g.png" /><figcaption>republished — newsletter</figcaption></figure><p><strong><em>Connect </em></strong>🤝<strong><em>:</em></strong></p><ul><li><strong><em>Email</em></strong>: <em>bbhopalw@gmail</em></li><li><strong><em>Linkedin</em></strong>: <a href="http://www.linkedin.com/in/bbhoaplw"><em>www.linkedin.com/in/bbhoaplw</em></a></li></ul><p><strong><em>For further reading </em></strong>✍️<strong><em>:</em></strong></p><p><strong><em>Big Data &amp; Cloud Engineering blogs:</em></strong></p><ul><li><strong><em>Towards Data Science Publication:</em> </strong><a href="https://medium.com/@burhanuddinbhopalwala"><em>https://medium.com/@burhanuddinbhopalwala</em></a></li></ul><p><strong><em>Backend Engineering &amp; Software Engineering blogs:</em></strong></p><ul><li><strong><em>DEV Community:</em><br></strong><a href="https://dev.to/burhanuddinbhopalwala"><em>https://dev.to/burhanuddinbhopalwala</em></a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4f064df53c43" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/covid-19-machine-learning-4f064df53c43">COVID-19 &amp; Machine Learning</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Moving an on-premise production Database to a managed Cloud Serverless Database in nearly no…]]></title>
            <link>https://medium.com/analytics-vidhya/moving-an-on-premise-production-database-to-a-managed-cloud-serverless-database-in-nearly-no-d1fb3f47cfd3?source=rss-76c303c51d34------2</link>
            <guid isPermaLink="false">https://medium.com/p/d1fb3f47cfd3</guid>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[database]]></category>
            <category><![CDATA[aws]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[aws-dms-etl]]></category>
            <dc:creator><![CDATA[Burhanuddin Bhopalwala]]></dc:creator>
            <pubDate>Fri, 14 Feb 2020 04:31:33 GMT</pubDate>
            <atom:updated>2022-12-17T11:59:55.081Z</atom:updated>
            <content:encoded><![CDATA[<h3>BigData/ELT: Serverless Database migration and replication with nearly no downtime</h3><p>Moving an on-premise production Database to Cloud with a serverless migration and replication process in nearly no downtime…</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N6h4u1KKOBKYjse1dOYJjQ.png" /><figcaption>photo-credit: Google</figcaption></figure><h3>Table of contents</h3><ol><li><strong>Why a serverless approach for Database migration and replication?</strong></li><li><strong>Prerequisites — ETL + AWS DMS + AWS RDS</strong></li><li><strong>A Step-by-Step process and demo</strong></li><li><strong>BONUS — Pro Tips!</strong></li></ol><h3><strong>1: Why serverless approach for Database migration and replication?</strong></h3><p>In one of my previous blogs (link attached below), I have already mentioned why the Serverless approach is always a good choice in terms of Operational Excellence, Security, Performance, Reliability, and Cost.</p><blockquote><strong>Tip:</strong> These are also called <strong>5 pillars of the Well-Architected Framework</strong>. In my own experience, one should strictly take care of Performance &amp; Cost. As these all 5 pillars are synergies to each other, among which Performance &amp; Cost is the most important.</blockquote><p><a href="https://towardsdatascience.com/amazon-s3-data-lake-storing-analyzing-the-streaming-data-on-the-go-a-serverless-approach-48b68a9433ff">Amazon S3 Data Lake | Storing &amp; Analyzing the Streaming Data on the go | A Serverless Approach</a></p><p>So, A popular open-source Change Data Capture (CDC) platform called <a href="https://debezium.io/">https://debezium.io/</a> is available for migrating and replicating CDC, which is amazing and I also personally used it. But, In terms of the 5 Pillars of a Well-Architected Framework it has the following disadvantages:</p><ul><li><strong><em>Operational Excellence: </em></strong>Global read lock on source Database, Need operational maintenance, Debezium lag monitoring, Server maintenance that runs the Debezium, etc.</li><li><strong><em>Security:</em></strong> Not managed security both for encryption at flight and encryption at rest.</li><li><strong><em>Performance/Scalability:</em></strong> This can easily be scalable via Docker. But auto-scaling is cumbersome at times.</li><li><strong><em>Reliability:</em></strong> Having several internal dependencies over Zookeeper, Kafka, etc makes it less reliable.</li><li><strong><em>Cost:</em></strong> It’s free but, the maintenance cost is there!</li></ul><blockquote>Hence, I will use a managed and serverless process for migration and replication using AWS DMS Service and will show you a step by step demo for setting this up. But it is having its own disadvantages which, I mentioned at the last in the BONUS section and ways to handle that also!</blockquote><h3>2: <strong>Prerequisites — ETL + AWS DMS + AWS RDS</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XTPbN0buRPb33qxZHohRlw.png" /><figcaption>Architecture</figcaption></figure><p><strong><em>2.1: ETL:</em></strong> ETL stands for Extract Transform Load. Usually, ETL data pipelines are used for giving structure to unstructured data.</p><p>But, we can also use it for migrating a structured data source from one to another either a relational or a non-relational database. And we are going to this now but not from scratch but in a more or less serverless way.</p><p><strong><em>2.2: AWS DMS:</em></strong> Amazon Web Service (AWS) Data Migration Service (DMS) is a service that we use for setting up a full fledge ETL process. Usually setting up an ETL process is easy but maintaining an ETL process is non-trivial due to several dependencies of <strong>Docker</strong>,<strong> Debezium</strong>,<strong> Kafka</strong>, etc.</p><p>If you want to learn it from scratch, below is my article regarding that, you can check that out below, the process of ETL I have discussed in the below article is the same as we are going to run our replication instance (explained later).</p><p>If you want to learn it from scratch, below is my article regarding that, you can check that out below, the process of ETL I have discussed in the below article is the same as we are going to run our replication instance (explained later).</p><p><a href="https://towardsdatascience.com/4-easy-steps-to-setting-up-an-etl-data-pipeline-from-scratch-a6e67c40b6e1">4 Easy steps to setting up an ETL Data pipeline from scratch</a></p><p>Now, We know why we need AWS DMS. So, AWS DMS compromises of 3 components majorly:</p><ul><li><strong><em>AWS DMS Endpoints: </em></strong>These are the source and target endpoints. While setting this up, one should take care of VPC networking esp for the Replication instance which needs networking with the source, and target. AWS also provides the option for testing this.</li><li><strong><em>AWS DMS Replication Instance:</em></strong> Simply an ec2 instance, which will run our entire ETL process. The replication instance stores the CDC (Change Data Capture) checkpoints, and table histories for a specified duration (so as to reload later if we stop the replication) and also under the hood does all the heavy lifting for us. Below attaching my reference Github repo for better understanding.</li><li><strong><em>AWS DMS Replication Task:</em></strong> ETL task that will run inside the replication instance. Just for learning and understanding purposes, below attaching the code of setting up ETL using Debezium CDC, AWS uses the same methodology for data migration and replication.</li></ul><p><a href="https://github.com/burhanuddinbhopalwala/etl-elasticsearch-app">burhanuddinbhopalwala/etl-elasticsearch-app</a></p><h3><strong>3: A Step-by-Step process and demo</strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CCs4szKeALdFy9zx-FY0oA.png" /><figcaption>DMS — Bird’s Eye View</figcaption></figure><p>In this demo, I will take a database hosted on an ec2 instance as a source endpoint and AWS RDS as a target endpoint.</p><p><strong>Step 1 Setting up source and target endpoints: </strong>Attaching configurations below.</p><p>Note: Use connection attributes for setting <strong><em>parallelLoadThreads</em></strong> for loading the table parallelly or <strong><em>useBCPFullLoad</em></strong> for the Bulk load. You can change it as per your needs. They are important and some of the <strong><em>connection attributes</em></strong> are as follows:</p><ul><li><strong><em>safeguard policy</em></strong> — Changes the behavior of SQL Server by opening transactions to prevent the transaction log from being truncated while AWS DMS is reading the log. Valid values are EXCLUSIVE_AUTOMATIC_TRUNCATION or RELY_ON_SQL_SERVER_REPLICATION_AGENT (default).</li><li><strong><em>useBCPFullLoad</em></strong> — Directs AWS DMS to use BCP (bulk copy) for data loading. Valid values are Y or N. When the target table contains an identity column that does not exist in the source table, you must disable the use of BCP for loading the table by setting the parameter to N.</li><li><strong><em>BCPPacketSize</em></strong> — If BCP is enabled for data loads, then enter the maximum packet size used by BCP. Valid values are 1–100000 (default 16384).</li><li><strong><em>controlTablesFileGroup</em></strong> — Specifies the file group to use for the control tables that the AWS DMS process creates in the database.</li><li><strong><em>parallelLoadThreads</em></strong> — Discussed above.</li></ul><p>Endpoints configuration:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/8b36f140bd1b8fc44f682b267b46d05f/href">https://medium.com/media/8b36f140bd1b8fc44f682b267b46d05f/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6GiP7wxuSbgIVis_HuD6AQ.png" /><figcaption>Advance setting — connection attributes</figcaption></figure><p><strong>Step 2 Setting up replication instance: </strong>Attaching below master configuration in step 3.</p><blockquote><strong>Note:</strong> If your source and target let’s say staging and production are in different VPC then replication instance won’t work unless connected by a VPC Peering connection both the sides.</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8OYeZ3KjSttRsvTBp-wc5w.png" /><figcaption>VPC peering for source and target in different endpoints</figcaption></figure><p>Before moving ahead please run the test for connectivity between the endpoints and the replication instance manually.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WcN6CLCVBmOf31kpPBaTGA.png" /></figure><p><strong>Step 3 Setting up the replication task</strong>: This is the most important part because, if the replication task is configured incorrectly, it will easily go into an <strong>Errored </strong>state and then you need a monitor with the task using Cloudwatch logs. Below is attaching the configuration template and screenshot.</p><p>For all the configurations, refer to the below master configuration file:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9b1108b8921395581cdec679070e4130/href">https://medium.com/media/9b1108b8921395581cdec679070e4130/href</a></iframe><p>Use the Table Mapping below under the Advance setting:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ttufWcQkdhOv2c9DydfuNw.png" /><figcaption>Table mapping for replication task</figcaption></figure><pre><strong>{<br> &quot;rules&quot;: [<br>  {<br>   &quot;rule-type&quot;: &quot;selection&quot;,<br>   &quot;rule-id&quot;: &quot;1&quot;,<br>   &quot;rule-name&quot;: &quot;1&quot;,<br>   &quot;object-locator&quot;: {<br>    &quot;schema-name&quot;: &quot;&lt;SOURCE_DB_NAME&gt;&quot;,<br>    &quot;table-name&quot;: &quot;%&quot;<br>   },<br>   &quot;rule-action&quot;: &quot;include&quot;<br>  }<br> ]<br>}</strong></pre><blockquote><strong>Tip</strong><em>: You can use AWS CLI can also be used for creating the replication instance and task using the above config.</em></blockquote><p><strong><em>Final Results:</em></strong></p><p>As you can see all the classic models have been migrated and will start replicating CDC based on the configuration selected above!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*A2erduqobLXRYRtYGLYdRA.png" /><figcaption>classic models — migration</figcaption></figure><p><strong>4:</strong> <strong>BONUS — Pro Tips!</strong></p><p><strong>4.1: DMS Limitations (we encountered 😤):</strong></p><blockquote>The AUTO_INCREMENT attribute on a column was not migrated to a target database column.</blockquote><blockquote>Indexes created on a portion of a column value aren’t migrated. So, secondary indexes, default values, procedures, triggers, etc. as part of the data migration was not migrated.— The reason is AWS focusses on a minimalistic approach (only objects) for data migration.</blockquote><blockquote>DMS didn’t migrate the default values.</blockquote><p>So, a good solution for migrating the indexes is to dump the schema and start the replication task with the <strong><em>Truncate</em></strong> only option, which will preserve the dump schema (with AutoIncrement, Indexes, Default values) and migrate and replicate the CDC only.</p><p>Aliter’s best solution is to migrate the indexes manually if you realized this after the data migration process entirely. This trick works fairly well also!</p><blockquote>Another limitation is If the MySQL-compatible source is stopped during full load, the AWS DMS task doesn’t stop with an error. The task ends successfully, but the target might be out of sync with the source. If this happens, either restart the task or reload the affected tables, that’s the only solution.</blockquote><p><strong>4.2: <em>Cost</em></strong>: Usually for <strong><em>data migration up to 1–2 TB t2.medium or t2.large</em> <em>works fairly well</em></strong> and costs around 45–50 USD/month. Increasing the instance type will only help in initial data migration but the replication speed remains the same in all instance types. Try not to go for a much higher instance type, it will only cost you more!</p><p>AWS DMS is billed by the hour if you choose for replicating ongoing CDC changes after migration then only you prefer to choose validation changes (only if you need them). Because <strong><em>enabling</em></strong> <strong><em>validations will substantially increase the migration time and hence more cost.</em></strong></p><p>That’s pretty much of it!</p><p><strong><em>Connect </em></strong>🤝<strong><em>:</em></strong></p><ul><li><strong><em>Email</em></strong>: <em>bbhopalw@gmail</em></li><li><strong><em>Linkedin</em></strong>: <a href="http://www.linkedin.com/in/bbhoaplw"><em>www.linkedin.com/in/bbhoaplw</em></a></li></ul><p><strong><em>For further reading </em></strong>✍️<strong><em>:</em></strong></p><p><strong><em>Big Data &amp; Cloud Engineering blogs:</em></strong></p><ul><li><strong><em>Towards Data Science Publication:</em> </strong><a href="https://medium.com/@burhanuddinbhopalwala"><em>https://medium.com/@burhanuddinbhopalwala</em></a></li></ul><p><strong><em>Backend Engineering &amp; Software Engineering blogs:</em></strong></p><ul><li><strong><em>DEV Community:</em><br></strong><a href="https://dev.to/burhanuddinbhopalwala"><em>https://dev.to/burhanuddinbhopalwala</em></a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d1fb3f47cfd3" width="1" height="1" alt=""><hr><p><a href="https://medium.com/analytics-vidhya/moving-an-on-premise-production-database-to-a-managed-cloud-serverless-database-in-nearly-no-d1fb3f47cfd3">Moving an on-premise production Database to a managed Cloud Serverless Database in nearly no…</a> was originally published in <a href="https://medium.com/analytics-vidhya">Analytics Vidhya</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Amazon S3 Data Lake | Storing & Analyzing the Streaming Data on the go | A Serverless Approach]]></title>
            <link>https://medium.com/data-science/amazon-s3-data-lake-storing-analyzing-the-streaming-data-on-the-go-a-serverless-approach-48b68a9433ff?source=rss-76c303c51d34------2</link>
            <guid isPermaLink="false">https://medium.com/p/48b68a9433ff</guid>
            <category><![CDATA[analytics]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[devops]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[aws]]></category>
            <dc:creator><![CDATA[Burhanuddin Bhopalwala]]></dc:creator>
            <pubDate>Tue, 04 Feb 2020 00:52:51 GMT</pubDate>
            <atom:updated>2022-12-17T11:53:04.231Z</atom:updated>
            <content:encoded><![CDATA[<h3>BigData/Streaming: Amazon S3 Data Lake | Storing &amp; Analyzing the Streaming Data on the go (in near real-time) | A Serverless Approach</h3><h4>Making an Amazon S3 data lake by storing the streaming data &amp; analyzing it on the go in near real-time…</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/800/1*p6AHNCG7SoywFWM-W763zw.gif" /><figcaption>S3 data lake serverless architecture | photo-credit: aws.amazon.com</figcaption></figure><h3>Table of contents</h3><ol><li><strong>What is streaming data and its main challenges (3V’s)?</strong></li><li><strong>What is the serverless approach and Why should we use a serverless approach?</strong></li><li><strong>Prerequisites — AWS + AWS Kinesis Firehose Delivery Stream+ AWS Kinesis Producer/Consumer + AWS Lambda + AWS S3 Storage + AWS Athena — Bird’s-eye view</strong></li><li><strong>AWS Kinesis Delivery Stream Setup — Step by Step</strong></li><li><strong>BONUS — Pro Tips!</strong></li></ol><h3>1: <strong>What is streaming data and its main challenges (3V’s)?</strong></h3><p>Streaming data is simply meant a continuous flow of data. And today in the age of the Internet, Devices like Smart Phones, Watches, GPS Sensors are popular sources for streaming data.</p><blockquote><strong><em>Note</em></strong><em>: An ecosystem created by all of these Devices by connecting each other over the Internet is what we called as</em><strong><em> Internet of Things (IoT).</em></strong></blockquote><p><strong>3 main challenges with streaming data (3V’s):</strong></p><p><strong><em>1: Velocity (Throughput/min):</em></strong> From Gigabytes/min <strong>(GB/min)</strong> to Terabytes/min <strong>(TB/min)</strong>. Consuming streaming data at this velocity without any loss of information is always not an easy task and is non-trivial.</p><p><strong><em>2: Variety (Types): </em></strong>If the data is unstructured(JSON/audio/video format), then it becomes more difficult to analyze such data especially when the data is in flight (will cover in future articles) as compared to when the data in rest!</p><p><strong><em>3: Volume (DB Size): </em></strong>From Terabytes <strong>(TBs)</strong> to Petabytes<strong>(PBs)</strong> to Exabytes <strong>(EBs)</strong>, storing streaming data requires lots of space and again scanning such raw data to query over it also becomes a challenging task.</p><blockquote><strong>Tip</strong><em>: These 3V’s are the key parameters that decide the data category (small, medium or big data) and hence also play a key role in deciding the type of Database storage solution we need to store the data. Confused? See below:</em></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gMYq-PDHp-1Vhh43dyre7A.png" /><figcaption>Difference between small, medium &amp; big-data based on 3V’s</figcaption></figure><h3>2: <strong>What is the serverless approach and Why should we use a serverless approach?</strong></h3><p>Let me start by clearing the common myth &amp; confusion after reading the word “serverless” — serverless does not mean performing computation without servers. Simply means delegating the responsibility for managing the Servers to the <strong>Cloud Service Providers (AWS/Google Cloud Platform (GCP)/Microsoft Azure)</strong>, so that we can always focus on business logic!</p><p><strong>So, why should we use a serverless approach?</strong></p><p>I have already mentioned the 3 main challenges with the streaming data. It requires not only lots of Team effort but constant maintenance. Auto Scaling/Elasticity is also not very trivial. Eventually more cost!</p><p>But, In serverless, it’s the opposite, We require minimal maintenance. Cloud Service Providers will auto-scale for us and eventually way less maintenance &amp; cost!</p><blockquote><strong><em>Note:</em></strong> In BONUS — Pro Tips: I will also share, how to configure the delivery streams so it cost as minimum as possible.</blockquote><h3>3: Prerequisites — AWS + AWS Kinesis Firehose Delivery Stream + AWS Kinesis Producer/Consumer + AWS Lambda + AWS S3 Storage + AWS Athena — Bird’s-eye view</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CHONfJybhvmfrrGSDmhTiQ.png" /><figcaption>AWS Kinesis Delivery Stream — Lambda — S3 — Athena(Data Analysis) | photo-credit: SDS</figcaption></figure><blockquote><strong>AWS</strong>: Amazon Web Services (AWS) is the cloud provider we are using. One can use Google Cloud Platform (GCP) or MS Azure for respective services.</blockquote><ul><li><strong><em>AWS Kinesis Firehose Delivery Stream:</em> </strong>Kinesis is nothing but a managed (serverless) Apache Kafka. AWS usually has 2 options for using Kinesis. Kinesis Data Stream (for real-time) &amp; Kinesis Firehose Delivery Stream which is a near-real-time (~60-sec delay) service. This blog, will use AWS Firehose Kinesis delivery streams going ahead.</li><li><strong>AWS Kinesis Producer:</strong> AWS Kinesis Producer SDK (prefer for high performance) or AWS Kinesis Agent are 2 popular ways for shipping the data to AWS Kinesis.</li><li><strong><em>AWS Kinesis Consumer:</em></strong> If You want to consume the data apart from storing the data, You can do that using AWS Kinesis Consumer SDK / AWS Kinesis Client Library (KCL uses AWS DynamoDB under the hood) or even AWS Kinesis Connector Library if you are JAVA lover.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*U80mPWVZVPED4J7JFoKu0A.png" /><figcaption>Kinesis uses shards same as for Apache Kafka for data distribution | photo-credit: SDS</figcaption></figure><ul><li><strong><em>AWS Lambda:</em></strong> We will transform our data streams to record in-flight using AWS Lambda. A virtual function from AWS that we will use as a service a.k.a `function-as-a-service`. It’s easy to use and cost-efficient. <a href="https://aws.amazon.com/lambda/">https://aws.amazon.com/lambda/</a></li><li><strong><em>AWS S3:</em></strong> We will use AWS S3 Service for our data lake. It’s one of the most simple, reliable, and cost-efficient AWS services.</li><li><strong><em>AWS Athena:</em></strong> For analyzing the data stored in AWS S3, it will use AWS Athena which is generally used for AWS S3 analytical and ad-hoc queries.</li></ul><h3>4: AWS Kinesis Firehose Delivery Stream setup — Step by Step</h3><p><strong><em>Step 1:</em></strong> AWS Kinesis Producer: I am using here AWS Kinesis Agent, as in my example the data is consuming directly onto the file. AWS Kinesis Agent will directly transfer these file Objects to S3 via AWS Kinesis Delivery Streams.</p><p>Kinesis Agent needs to be installed where you are receiving the streaming data or generating the log data.</p><pre>$ sudo yum install -y aws-kinesis-agent<br>...<br>1081 packages excluded due to repository priority protections<br>Package aws-kinesis-agent-1.1.3-1.amzn1.noarch <br>installed and on latest version$ cd /etc/aws-kinesis<br>$ sudo vim agent.json</pre><p>Go to agent.json from the above command and place Your IAM credentials, &amp; the location of the server where you are receiving the streaming or generating the log data. Below you can find the agent.json file:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/63cee7db69d8388f203766933a6a8562/href">https://medium.com/media/63cee7db69d8388f203766933a6a8562/href</a></iframe><p>You can also do the same using AWS Kinesis SDK Library and the code for the same using Python you can find it here:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/bce38070acc811be7930a6b1ac8b51e4/href">https://medium.com/media/bce38070acc811be7930a6b1ac8b51e4/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_q5RqHqkf5z5wlaqE7MF6A.png" /><figcaption>AWS Kinesis — Lambda record transformation</figcaption></figure><blockquote><strong><em>Note</em></strong>: I have used JavaScript for Kinesis Producer Library example using npm aws-kinesis-producer</blockquote><p><strong><em>Step 2:</em></strong> Let’s set up AWS S3 now. We just need to create an AWS S3 Bucket. While you can AWS S3 Bucket, you can choose default S3 Server Side Encryption (S3-SSE) also for Encryption at rest.</p><p><strong><em>Step 3:</em></strong> Now we are setting up AWS Kinesis Delivery Streams. You can find the options below. Partitioning is a must so that Athena can be able to SCAN the data faster. I am partitioning the data below hours-wise.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cFvpusykgr-ob_QThFCSRQ.png" /><figcaption>AWS Kinesis Delivery — Options</figcaption></figure><p>If you want to use AWS Lambda for records transformation in flight, You can do that also. You can find the manual transformation code below:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/58113da7039e3441f0b1c5b5e04ed91d/href">https://medium.com/media/58113da7039e3441f0b1c5b5e04ed91d/href</a></iframe><blockquote><strong>Note: </strong>Standard records transformation to JSON is also available. You can find it here: <a href="https://github.com/aws/aws-lambda-java-libs"><em>https://github.com/aws/aws-lambda-java-libs</em></a></blockquote><p><strong><em>Step 4:</em></strong> Finally, AWS Athena for querying over our AWS S3 data lake, excited! Below attaching both the query for making a Database in Athena over S3 data and select a query for reading the data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*nqbbDLdugu6rQGyB7rOtZw.png" /><figcaption>AWS Athena — For creating the Database from AWS S3</figcaption></figure><p>If your data is already partitioned inside S3, You can also use existing S3 partition inside Athena, by typing the following ALTER command in Athena console.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yjzV97TbX0FvBK_VaeCVig.png" /><figcaption>AWS Athena — ALTER command over S3 Partitioning</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Byk28yN_CRYNcYWaNdBG9Q.png" /><figcaption>AWS Athena — SELECT query</figcaption></figure><p><strong><em>Final Results:</em></strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0aToTFf_s6GrC8SXBPLzEA.png" /><figcaption>Partitioned data — S3 via AWS Kinesis Firehose Delivery Streams</figcaption></figure><pre>$ aws s3 cp s3://&lt;bucket-key-name&gt;/&lt;sub-bucket-key-name&gt;/dt=2020–01–20–08/ . — recursive | xargs -rn 1 gzip -d *data-1–2020–01–20–08–10–00–04453c-3e98–47a3-b28d-521ae9ff9b3d.log<br>data-1–2020–01–20–08–10–15–04453c-3e98–47a3-b28d-521ae9ff9b3d.log<br>data-1–2020–01–20–08–10–30–04453c-3e98–47a3-b28d-521ae9ff9b3d.log<br>data-1–2020–01–20–08–10–45–04453c-3e98–47a3-b28d-521ae9ff9b3d.log</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hLLCXpfjoVSTgHYS761HPw.png" /><figcaption>AWS Athena — Results after query over S3 data</figcaption></figure><blockquote><strong><em>Note:</em> </strong>You might be thinking, How AWS Athena crawls &amp; scan the S3 Data? It uses AWS Glue Data Crawler (similar to Extract Transform Load ( ETL) job). It does all the heavy lifting under the hood. <a href="https://docs.aws.amazon.com/glue/latest/dg/populate-data-catalog.html"><em>https://docs.aws.amazon.com/glue/latest/dg/populate-data-catalog.html</em></a></blockquote><p>That’s it. It’s too easy…</p><blockquote>Mention: Athena also allows to invoking Machine Learning using SQL Queries. RANDOM_CUT_FOREST (for outliers), HOTSPOTS (for finding the dense area) are popular for that. That’s easy too…</blockquote><h3>5: As promised initially, BONUS — Pro Tips!</h3><ul><li><strong><em>Performance:</em></strong><em> Always prefer to use AWS Kinesis SDK libraries for high performance &amp; throughput. It supports batching also.</em></li><li><strong><em>Performance: </em></strong><em>If you want to consume the data with AWS Kinesis Delivery Stream, You can use “Kinesis-Enhanced Fanout Streams” which will allow consuming the data with just double the capacity of a shard i.e. from 1 MB/s to 2 MB/s.</em></li><li><strong><em>Cost:</em></strong><em> Use GZIP compression for reducing the size of the Object. While restoring just use the</em><strong><em> “gzip -d &lt;s3-file-keyname&gt;”</em></strong><em> command to get the data in the raw format again. GZIP will help you to compress the size by 75% and hence You will end up saving up to 75% of the S3 cost. Usually, AWS S3 Costs about 0.03$/GB.</em></li></ul><blockquote><strong>Note: GZIP is</strong> known for large compression ratios, but poor decompression speeds and high CPU usage as compared to <strong>ZIP</strong> format. <strong>GZIP</strong> usually preferred!</blockquote><ul><li><strong><em>Cost:</em></strong><em> Use AWS S3 Life Cycle Rules — AWS S3 stores each object by default on a Standard Zone (means frequent-access zone). AWS S3 Life Cycle Rules will move the Objects to Standard-IA (Infrequent Access) over time which are again 30% cheaper than the Standard S3 Zone.</em></li><li><strong><em>Security:</em></strong><em> Always enable S3-Server Side Encryption (SSE) for security. For even more sensitive data, You can also use S3-Client (SSE-C), In which, you can pass your encryption key over HTTPS.</em></li></ul><p><em>Thanks for reading. I hope you will find this blog helpful. Please stay tuned for more such upcoming blogs on cutting-edge Big Data, Machine Learning &amp; Deep Learning. Stay tuned! The best is yet to come :)</em></p><p>That’s pretty much of it!</p><p><strong><em>Connect </em></strong>🤝<strong><em>:</em></strong></p><ul><li><strong><em>Email</em></strong>: <em>bbhopalw@gmail</em></li><li><strong><em>Linkedin</em></strong>: <a href="http://www.linkedin.com/in/bbhoaplw"><em>www.linkedin.com/in/bbhoaplw</em></a></li></ul><p><strong><em>For further reading </em></strong>✍️<strong><em>:</em></strong></p><p><strong><em>Big Data &amp; Cloud Engineering blogs:</em></strong></p><ul><li><strong><em>Towards Data Science Publication:</em> </strong><a href="https://medium.com/@burhanuddinbhopalwala"><em>https://medium.com/@burhanuddinbhopalwala</em></a></li></ul><p><strong><em>Backend Engineering &amp; Software Engineering blogs:</em></strong></p><ul><li><strong><em>DEV Community:</em><br></strong><a href="https://dev.to/burhanuddinbhopalwala"><em>https://dev.to/burhanuddinbhopalwala</em></a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=48b68a9433ff" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/amazon-s3-data-lake-storing-analyzing-the-streaming-data-on-the-go-a-serverless-approach-48b68a9433ff">Amazon S3 Data Lake | Storing &amp; Analyzing the Streaming Data on the go | A Serverless Approach</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[4 Easy steps to setting up an ETL Data pipeline from scratch]]></title>
            <link>https://medium.com/data-science/4-easy-steps-to-setting-up-an-etl-data-pipeline-from-scratch-a6e67c40b6e1?source=rss-76c303c51d34------2</link>
            <guid isPermaLink="false">https://medium.com/p/a6e67c40b6e1</guid>
            <category><![CDATA[kafka-connect]]></category>
            <category><![CDATA[etl-pipeline]]></category>
            <category><![CDATA[kafka-streams]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[elk-stack]]></category>
            <dc:creator><![CDATA[Burhanuddin Bhopalwala]]></dc:creator>
            <pubDate>Tue, 21 Jan 2020 18:10:54 GMT</pubDate>
            <atom:updated>2024-09-30T09:47:35.057Z</atom:updated>
            <content:encoded><![CDATA[<h3>BigData/ETL: 4 Easy steps to setting up an ETL Data pipeline from scratch</h3><h4>Setting up an ETL pipeline in a few commands</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XhlDlxwU3_5K0W0-NxiBkw.jpeg" /><figcaption>ETL (Extract Transform Load)</figcaption></figure><blockquote><strong>What not to expect from this Blog?</strong> Managed ETL solutions like AWS Glue, AWS Data Migration Service or Apache Airflow. Cloud-based techniques are managed but not free. And are not covered in this article.</blockquote><h3>Table of contents</h3><ol><li><strong>What is an ETL pipeline?</strong></li><li><strong>What are the various use cases of an ETL pipeline?</strong></li><li><strong>ETL prerequisites — Docker + Debezium + Kafka + Kafka Connect — Bird’s-eye view</strong></li><li><strong>ETL setup — A 4 step process</strong></li></ol><h3><strong>1: What is an ETL?</strong></h3><p>ETL stands for Extract Transform Load pipeline. And it’s used for setting up a Data warehouse or Data lake.</p><blockquote>Note: Data warehouse is collecting multiple structured Data sources like Relational databases, but in a Data lake we store both structured &amp; unstructured data.</blockquote><h3>2: <strong><em>What are the various use cases of an ETL pipeline?</em></strong></h3><p>ETL has broad use cases which are as follows:</p><ul><li>Giving structure to unstructured data, because we are storing it into a Data warehouse which generally we use for storing structured data from multiple resources.</li><li>Data pipeline for ML engineers to get the Data for training the models. And is quite often to be the first task for an ML engineer / Data Scientist (L1).</li><li>For making a backup or staging data source.</li></ul><blockquote><strong>Note:</strong> In this example, we will use the MySQL as source database and Elasticsearch as destination which inherently integrated with Kibana for Data Visualization and Machine Learning.</blockquote><h3><strong><em>3: ETL prerequisites —Docker + Debezium + Kafka + Kafka Connect — Bird’s-eye view</em></strong></h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Z_se01KYnKPOWzGYTM9IQw.png" /><figcaption>MySQL (source) + Debezium + Kafka / Kafka Connect + Elasticsearch (destination) + Kibana</figcaption></figure><p>As you can see from the above picture we are going to use the following:</p><ul><li><strong><em>Docker:</em></strong> A Container Management System (CMS). We are using Docker for simplicity. <a href="https://www.docker.com/">https://www.docker.com/</a></li><li><strong>Debezium: </strong>Debezium is nothing but a Change Data Capture (CDC). Which tracks every event (insert, update, delete) from the source DB and will push the event to Kafka using Kafka Connect. It uses source Database logs for reading every transaction and making an event for a particular transaction.</li></ul><blockquote><strong>Note:</strong> In case of MySQL we called it as binlog and in case of PostgreSQL we called it as wal-logs (Write Ahead Log)</blockquote><ul><li><strong><em>Kafka Connect:</em></strong> As the name suggests, it helps Debezium to connect with Kafka.</li><li><strong><em>Kafka:</em></strong> Kafka helps in event streaming and consuming in real-time. Kafka works with Zookeeper for tracking the events. <a href="https://bit.ly/2Gb9Sm7">https://bit.ly/2Gb9Sm7</a></li><li><strong><em>ELK (destination):</em></strong> We are considering Elasticsearch as our destination Data source, which by default integrated with Kibana for Data Visualization &amp; Machine learning too, this is popularly known as Elasticsearch + Logstash + Kibana (ELK stack) <a href="https://bit.ly/36dmioe">https://bit.ly/36dmioe</a></li></ul><h3><strong><em>4: Let’s start setting this up — A 4 Step process</em></strong></h3><p><strong><em>Step 1:</em></strong> Changing the MySQL binlog format which Debezium likes: Just go to /etc/my.cnf, basically in MySQL configuration file and add the following configurations:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Ov9FpI4rQ2Z5bJc8c0mZtA.png" /><figcaption>/etc/my.cnf (MySQL configuration file)</figcaption></figure><p><strong><em>Step 2:</em></strong> Starting Zookeeper, Kafka &amp; Kafka Connect using Docker:</p><pre>$ docker run -it --rm --name zookeeper -p 2181:2181 -p 2888:2888 -p 3888:3888 debezium/zookeeper </pre><pre>$ docker run -it --rm --name kafka -p 9092:9092 --link zookeeper:zookeeper debezium/kafka:1.0</pre><pre>$ docker run -it --rm --name connect -p 8083:8083 -e GROUP_ID=1 -e CONFIG_STORAGE_TOPIC=my_connect_configs -e OFFSET_STORAGE_TOPIC=my_connect_offsets -e STATUS_STORAGE_TOPIC=my_connect_statuses --link zookeeper:zookeeper --link kafka:kafka --link mysql:mysql debezium/connect:1.0</pre><blockquote><strong>Note:</strong> If you are familiar with Docker, You can use docker-compose.yaml. You can find it here: <a href="https://github.com/debezium/debezium-examples/blob/master/tutorial/docker-compose-mysql.yaml">https://github.com/debezium/debezium-examples/blob/master/tutorial/</a></blockquote><p><strong><em>Step 3 (Extract)</em></strong>: We’ll use curl to submit to our Kafka Connect service a JSON request message to start capturing events from source DB using Debezium under the hood (It needs the source DB credentials below):</p><pre>curl -i -X POST <br>-H &quot;Accept:application/json&quot; <br>-H &quot;Content-Type:application/json&quot; <br>localhost:8083/connectors/ -d <br>&#39;{ &quot;name&quot;: &quot;etl-connector&quot;, <br>&quot;config&quot;: {                                                 &quot;connector.class&quot;:      &quot;io.debezium.connector.mysql.MySqlConnector&quot;, <br>&quot;tasks.max&quot;: &quot;1&quot;, <br>&quot;database.hostname&quot;: &quot;&lt;mysql_host&gt;&quot;, <br>&quot;database.port&quot;: &quot;3306&quot;, <br>&quot;database.user&quot;: &quot;&lt;mysql_username&gt;&quot;, <br>&quot;database.password&quot;: &quot;&lt;mysql_password&gt;&quot;, <br>&quot;database.server.id&quot;: &quot;184054&quot;, <br>&quot;database.server.name&quot;: &quot;dbserver1&quot;, <br>&quot;database.whitelist&quot;: &quot;&lt;database_name&gt;&quot;, &quot;database.history.kafka.bootstrap.servers&quot;: &quot;kafka:9092&quot;, &quot;database.history.kafka.topic&quot;: &quot;dbhistory.&lt;db_name&gt;&quot; } }&#39;</pre><p><strong><em>Step 4 (Transform &amp; Load)</em></strong>: The final step is to write a Kafka Consumer. The Consumer is nothing but a simple function/code that will extract the Debezium events Transform it and Load them into the ELK destination.</p><p>Please find the entire reference source code template here: <a href="https://github.com/burhanuddinbhopalwala/etl-elasticsearch-app">https://github.com/burhanuddinbhopalwala/etl-elasticsearch-app</a></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9f4c5b9edaa4bafb76717d0d2b2411c4/href">https://medium.com/media/9f4c5b9edaa4bafb76717d0d2b2411c4/href</a></iframe><p>It’s Done! In this example, we are using bulk Insertion for Elasticsearch. And you can see the logs below from the above source code.</p><pre>...<br>2017-09-21 07:38:48,385 INFO   MySQL|dbserver1|task  Kafka version : 0.11.0.0   [org.apache.kafka.common.utils.AppInfoParser]<br>2org.apache.kafka.clients.consumer.internals.AbstractCoordinator]<br>2017-09-21 07:38:48,402 INFO   MySQL|dbserver1|task  Successfully joined group inventory-connector-dbhistory with generation 1   [org.apache.kafka.clients.consumer.internals.AbstractCoordinator]<br>2017-09-21 07:38:48,403 INFO   MySQL|dbserver1|task  Setting newly assigned partitions [dbhistory.inventory-0] for group inventory-connect WorkerSourceTask{id=inventory-connector-0} finished initialization and start   [org.apache.kafka.connect.runtime.WorkerSourceTask</pre><pre>INFO -- : CREATING MASTER DB CONNECTION<br>INFO -- : CONNECT ELASTICSEARCH<br>INFO -- : CONNECTED KAFKA<br>INFO -- : WAITING FOR 500 MESSAGES, RECEIVED 1, ID: 685475<br>INFO -- : WAITING FOR 500 MESSAGES, RECEIVED 2, ID: 457548<br>INFO -- : WAITING FOR 500 MESSAGES, RECEIVED 3, ID: 985484<br>INFO -- : WAITING FOR 500 MESSAGES, RECEIVED 4, ID: 258547<br>INFO -- : WAITING FOR 500 MESSAGES, RECEIVED 5, ID: 257544</pre><blockquote><strong>Errors</strong>: If you get any errors you can always go to Debezium website: <a href="https://debezium.io/">https://debezium.io/</a>.</blockquote><p>Lastly, Thanks for reading. I hope you find this blog helpful. And as always remember to breathe :)</p><p><strong><em>Connect </em></strong>🤝<strong><em>:</em></strong></p><ul><li><strong><em>Email</em></strong>: <em>bbhopalw@gmail</em></li><li><strong><em>Linkedin</em></strong>: <a href="http://www.linkedin.com/in/bbhoaplw"><em>www.linkedin.com/in/bbhoaplw</em></a></li></ul><p><strong><em>For further reading </em></strong>✍️<strong><em>:</em></strong></p><p><strong><em>Big Data &amp; Cloud Engineering blogs:</em></strong></p><ul><li><strong><em>Towards Data Science Publication:</em> </strong><a href="https://medium.com/@burhanuddinbhopalwala"><em>https://medium.com/@burhanuddinbhopalwala</em></a></li></ul><p><strong><em>Backend Engineering &amp; Software Engineering blogs:</em></strong></p><ul><li><strong><em>DEV Community:</em><br></strong><a href="https://dev.to/burhanuddinbhopalwala"><em>https://dev.to/burhanuddinbhopalwala</em></a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a6e67c40b6e1" width="1" height="1" alt=""><hr><p><a href="https://medium.com/data-science/4-easy-steps-to-setting-up-an-etl-data-pipeline-from-scratch-a6e67c40b6e1">4 Easy steps to setting up an ETL Data pipeline from scratch</a> was originally published in <a href="https://medium.com/data-science">TDS Archive</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>