<?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 Sathiya Sarathi on Medium]]></title>
        <description><![CDATA[Stories by Sathiya Sarathi on Medium]]></description>
        <link>https://medium.com/@sathiyasarathi?source=rss-ae9f7a2b0daf------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/2*TLolRThR8fsztaOErut-Qw.jpeg</url>
            <title>Stories by Sathiya Sarathi on Medium</title>
            <link>https://medium.com/@sathiyasarathi?source=rss-ae9f7a2b0daf------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Thu, 19 Nov 2026 23:00:27 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@sathiyasarathi/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[Streaming data from Databases]]></title>
            <link>https://medium.com/datawrangler/streaming-data-from-databases-86128df9ccb8?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/86128df9ccb8</guid>
            <category><![CDATA[streaming]]></category>
            <category><![CDATA[engineering]]></category>
            <category><![CDATA[java]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[analytics]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Mon, 13 May 2024 12:43:05 GMT</pubDate>
            <atom:updated>2024-05-13T12:43:05.461Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/454/0*1ZgvXWM1q7tEZrNj.jpg" /></figure><p>Data Streaming processes help in transferring a huge amount of data without buffering in memory. This has laid a strong foundation for the data streaming and queuing applications/services of today such as Kafka, Pulsar, etc. In this article, we will be exploring how data streaming can be implemented over JDBC connectors in Java and how we can evade the memory exhaustion issues.</p><h3>Setting up the Database</h3><p>Let’s set up a Database engine locally, to test our data streaming capabilities. We have set up MySQL database version 8.0.22-0 on Ubuntu 20.04.1 LTS (Code Name: Focal). For Windows, you can also use WSL to install Ubuntu on your local machine. You can find the instructions to set up WSL from <a href="https://ubuntu.com/wsl">https://ubuntu.com/wsl</a>.</p><pre>&gt; sudo apt-get install mysql-server <br>&gt; sudo service mysql start</pre><p>The above command installs and starts the MySQL service. Now that the service is up and running, let&#39;s login using the MySQL client.</p><pre>&gt; sudo mysql -u root -p<br>Enter password:<br>Welcome to the MySQL monitor.  Commands end with ; or \g.<br>Your MySQL connection id is 12<br>Server version: 8.0.22-0ubuntu0.20.04.3 (Ubuntu)<br><br>Copyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.<br><br>Oracle is a registered trademark of Oracle Corporation and/or its<br>affiliates. Other names may be trademarks of their respective<br>owners.<br><br>Type &#39;help;&#39; or &#39;\h&#39; for help. Type &#39;\c&#39; to clear the current input statement.<br><br>mysql&gt;  </pre><p>For this exercise, we will be using the repository <a href="https://github.com/datawrangl3r/datastreamexample.git">https://github.com/datawrangl3r/datastreamexample.git</a>:</p><pre>&gt; git clone https://github.com/datawrangl3r/datastreamexample.git</pre><p>We now locate the cloned project’s directory and source the sql file sampledatabase.sql inside the mysql client by running the following commands:</p><pre>mysql&gt; source sampledatabase.sql<br>Query OK, 0 rows affected, 1 warning (0.00 sec)<br><br>Query OK, 0 rows affected (0.00 sec)<br><br>Query OK, 0 rows affected (0.00 sec)<br><br>Query OK, 0 rows affected (0.00 sec)<br><br>Query OK, 0 rows affected (0.00 sec)<br><br>.<br>.<br>.<br>.<br><br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; </pre><p>This will create a new database known as classicmodels. Let&#39;s create a new user to access this database from the mysql client.</p><pre>mysql&gt; CREATE USER &#39;testuser&#39;@&#39;localhost&#39; IDENTIFIED BY &#39;password&#39;;<br>Query OK, 0 rows affected (0.02 sec)<br><br>mysql&gt; flush privileges;<br>Query OK, 0 rows affected (0.00 sec)<br><br>mysql&gt; GRANT ALL PRIVILEGES ON classicmodels.* TO &#39;testuser&#39;@&#39;localhost&#39;;<br>Query OK, 0 rows affected (0.01 sec)<br><br>mysql&gt; flush privileges;<br>Query OK, 0 rows affected (0.00 sec)</pre><p>Now, we have a user known as testuser, accessible by using the password - password having full access to the classicmodels database. This database has access to the following tables.</p><pre>mysql&gt; show tables;<br>+-------------------------+<br>| Tables_in_classicmodels |<br>+-------------------------+<br>| customers               |<br>| employees               |<br>| offices                 |<br>| orderdetails            |<br>| orders                  |<br>| payments                |<br>| productlines            |<br>| products                |<br>+-------------------------+<br>8 rows in set (0.01 sec)</pre><h3>OpenJDK runtime and JDBC Drivers</h3><p>Now that our database is ready, let’s install the OpenJDK runtime and the drivers to run our Java Program and access the database.</p><pre>&gt; sudo apt update<br>&gt; sudo apt install openjdk-11-jdk<br>&gt; java --version<br>openjdk 11.0.9 2020-10-20<br>OpenJDK Runtime Environment (build 11.0.9+11-Ubuntu-0ubuntu1.20.04)<br>OpenJDK 64-Bit Server VM (build 11.0.9+11-Ubuntu-0ubuntu1.20.04, mixed mode, sharing</pre><p>In addition to the JDK runtime, the JDBC driver can be downloaded and installed as follows:</p><pre>&gt; sudo apt install libmariadb-java</pre><p>The following entry needs to be added to ~/.bashrc and sourced as follows:</p><pre>export CLASSPATH=$CLASSPATH:/usr/share/java/mariadb-java-client.jar</pre><pre>&gt; source ~/.bashrc</pre><p>In the next section, we will be streaming and exporting these tables as individual CSV flat files.</p><h3>Stream Data from MySQL</h3><p>Now that we are ready, let’s dive right into the code. The full code can be found at: <a href="https://github.com/datawrangl3r/datastreamexample/blob/main/connector.java">https://github.com/datawrangl3r/datastreamexample/blob/main/connector.java</a></p><p>The pseudocode can be elaborated as:</p><ul><li>Create connection object &amp; prepare statements using java.sql.DriverManager. Set the fetchsize of the statement to a preferred number which denotes the number of rows to be retrieved per call.</li></ul><pre>Connection con = DriverManager.getConnection(url, user, password); <br>Statement st = con.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, <br>                            java.sql.ResultSet.CONCUR_READ_ONLY);</pre><ul><li>Initialize an empty string ArrayList — <em>myTableList</em></li></ul><pre>ArrayList &lt;String&gt; myTableList = new ArrayList &lt;String&gt; ();</pre><ul><li>Execute the query and save the contents to the initialized ResultSet object.</li></ul><pre>ResultSet rs = st.executeQuery(query);</pre><ul><li>Until this object returns False, keep fetching and adding the table names to the arraylist defined initially.</li><li>String tableName = rs.getString(1); myTableList.add(tableName);</li><li>Iterate through the table names in arraylist and for each table name, perform the following actions.</li><li>Create another connection object &amp; prepare statements using java.sql.DriverManager. Set the fetchsize of the statement to a preferred number, say 1000.</li></ul><pre>Connection con2 = DriverManager.getConnection(url, user, password); <br>Statement st2 = con2.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY, <br>             java.sql.ResultSet.CONCUR_READ_ONLY); st2.setFetchSize(1000);</pre><ul><li>Write the headers to each file once</li><li>Fetch row-by-row and iterate through the columns, keep writing to the file along with the delimiter</li><li>Close the file when it’s done</li><li>Repeat</li></ul><p>As a result, we get multiple CSVs written to the script directory, each file corresponding to the entire content of the table from the MySQL database.</p><p>In the above pseudocode, the section where we define the setFetchsize if not defined, the code could run into an out-of-memory exception for very large datasets. This enables the JDBC driver to stream the results rather than buffering them all at once. This can also lower the fetch size down to less than 1 percent.</p><p>Another point to note is that, whenever these connections are defined, concurrent statements can not be passed with the same connection object. This puts us in a position to stream the results as fast as we can.</p><p><em>Originally published at </em><a href="https://www.datawrangler.in/blog/13-data-streams-in-java/"><em>https://www.datawrangler.in</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=86128df9ccb8" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/streaming-data-from-databases-86128df9ccb8">Streaming data from Databases</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[IBM Cloud — From Spreadsheets to Cloud Hosted Dashboards Under 30 Minutes — DATAWRANGLER]]></title>
            <link>https://medium.com/datawrangler/ibm-cloud-from-spreadsheets-to-cloud-hosted-dashboards-under-30-minutes-datawrangler-9a06433fdbca?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/9a06433fdbca</guid>
            <category><![CDATA[bi]]></category>
            <category><![CDATA[machine-learning]]></category>
            <category><![CDATA[ibm-cloud]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[ibm]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Mon, 13 May 2024 12:30:14 GMT</pubDate>
            <atom:updated>2024-05-13T12:30:47.605Z</atom:updated>
            <content:encoded><![CDATA[<h3>IBM Cloud — From Spreadsheets to Cloud Hosted Dashboards Under 30 Minutes</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/454/0*Jr8Dc90V2ujw_TS0.jpg" /></figure><p>IBM’s cloud computing offering — IBM Cloud provides a scalable infrastructure, and platform as a service to organizations from small size to enterprise capacity. The offering is characteristic of its transparent, minimalistic, yet powerful environment for computing and Business Intelligence needs. This article aims to demonstrate how the IBM Cloud Services can be easily used to convert a boring and simple spreadsheet to a mighty enterprise-ready BI dashboard with scalable visualizations across an organization, under 30 minutes for free.</p><h3>IBM Cloud — An Introduction</h3><p>In the following video, we can understand how tedious the development lifecycle was for building dashboards and hosting online initially and how the cloud push can be done with much ease using IBM Cloud now.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FJ-tJpN4_usg%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DJ-tJpN4_usg&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FJ-tJpN4_usg%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/33aa4b9083f2d3ab565ae00456505903/href">https://medium.com/media/33aa4b9083f2d3ab565ae00456505903/href</a></iframe><h3>IBM Cloud Signup — A Speed Run</h3><p>An account to access IBM Cloud for free, can be done under a minute with no credit card information (We aren’t kidding) ❤️. Head over to <a href="https://cloud.ibm.com/registration">https://cloud.ibm.com/registration</a> and create your account today. Here’s how we did it.</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2FYAh9VKcNU2k%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3DYAh9VKcNU2k&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2FYAh9VKcNU2k%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/11d52eb72021964319967b29737771ae/href">https://medium.com/media/11d52eb72021964319967b29737771ae/href</a></iframe><p>In the above video, the spreadsheet for the exercise can be downloaded from the following URL: <a href="https://www.kaggle.com/shivamb/netflix-shows/download">https://www.kaggle.com/shivamb/netflix-shows/download</a></p><h3>Creating your dashboard on IBM Cloud and Sharing</h3><p>The dashboards can be created using IBM Watson Studio and hosted using Cognos Dashboard Embedded Service as explained in the video below:</p><iframe src="https://cdn.embedly.com/widgets/media.html?src=https%3A%2F%2Fwww.youtube.com%2Fembed%2Fxlwu5V8RBjo%3Ffeature%3Doembed&amp;display_name=YouTube&amp;url=https%3A%2F%2Fwww.youtube.com%2Fwatch%3Fv%3Dxlwu5V8RBjo&amp;image=https%3A%2F%2Fi.ytimg.com%2Fvi%2Fxlwu5V8RBjo%2Fhqdefault.jpg&amp;key=a19fcc184b9711e1b4764040d3dc5c07&amp;type=text%2Fhtml&amp;schema=youtube" width="854" height="480" frameborder="0" scrolling="no"><a href="https://medium.com/media/d2c072f199122f4bde47bf0b2b53f27a/href">https://medium.com/media/d2c072f199122f4bde47bf0b2b53f27a/href</a></iframe><p>And that concludes how quick it is to go from simple spreadsheets to cloud-hosted BI Dashboard under 30 minutes, using IBM Cloud.</p><p><em>Originally published at </em><a href="https://datawrangler.in/blog/12-ibm-cloud/"><em>https://www.datawrangler.in</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9a06433fdbca" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/ibm-cloud-from-spreadsheets-to-cloud-hosted-dashboards-under-30-minutes-datawrangler-9a06433fdbca">IBM Cloud — From Spreadsheets to Cloud Hosted Dashboards Under 30 Minutes — DATAWRANGLER</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ETL & Enterprise Level Practices]]></title>
            <link>https://medium.com/datawrangler/etl-enterprise-level-practices-b1e592c05346?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/b1e592c05346</guid>
            <category><![CDATA[data-wrangler]]></category>
            <category><![CDATA[adf]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Thu, 03 Dec 2020 18:03:53 GMT</pubDate>
            <atom:updated>2020-12-05T11:02:09.690Z</atom:updated>
            <content:encoded><![CDATA[<p>ETL Strategies &amp; Pipelines have now become inevitable for cloud business needs. There are several ETL tools in the market ranging from open-source ones such as <em>Airflow, Luigi, Azkaban, Oozie</em> to enterprise solutions such as <em>Azure Data Factory, AWS Glue, Alteryx, Fivetran</em>, etc. But what makes the data pipelines to be Industry-ready and robust? The practices and the industrial standards that are put into the architecture and design of the pipelines do. In this article, we touch upon these aspects and characteristics of Enterprise-level practices that can be followed to make a robust data pipeline.</p><h3>ETL-Alphabets</h3><p>We will be explaining the ETL strategies, alphabetically as shown in the illustration below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/proxy/1*-KsSmzkWv33mez1O0zOwnw.jpeg" /></figure><p><strong>Archival Strategies</strong></p><p>Archive older unused data, to keep the data warehouses clean and performant. By performing such cleanups, unused indexes can be cleared, and unused space can be retrieved.</p><p><strong>Be a responsible citizen</strong></p><ul><li>Select only those columns that are required for the analysis — While performing the extraction, select only the required columns. This will help in increasing the performance of the data warehouse and also, speeds up the extraction process.</li><li>Power down resources when not in use. If you are using clusters for processing or databases for staging; make sure to power down the resources that are not in use.</li></ul><p><strong>Cache Data</strong></p><p>Accessing a table more often? Why not cache that table for rapid usage? For example, Spark’s createOrReplaceTempView function creates just a mere reference to the table whereas the saveAsTable() stores the table to HDFS. This won&#39;t make much of a difference for smaller datasets, yet could be a pivotal improvement while transforming larger datasets.</p><p><strong>Data Security &amp; Anonymity</strong></p><p>Mask the sensitive information or better not to bring those to the target Data warehouse. Always perform your ETL practices over VPNs or secured groups. Always use a key vault service (Vault, Azure Key Vault, AWS Secrets Manager), etc. in case if you don’t have a Universal Authentication Identity Service Provider protected by individual Service Principals.</p><p><strong>Extract Only Necessary Fields</strong></p><p>You don’t always require a select * from table . Select only those fields that are required from the source database tables. If you are wondering if you needed any columns in the future, many of the ETL solutions such as Azure Data Factory, provide options to facilitate schema drift. This could be a major game-changer in terms of the performance and efficiency of the ETL processes.</p><p><strong>Find the Appropriate Business Date Column</strong></p><p>Identifying the fields is one, and identifying the business date columns is another. The business date columns are essential since the data lake timestamp columns are not always reliable. These columns are essential for performing incremental changes to your data warehouse.</p><p><strong>Generalize the ETL solution</strong></p><p>The ETL solution that you create should be able to accommodate changes dynamically. Changes shouldn’t bring about havoc, rework, or major code changes. Make sure to bring in a lot of dynamic parameters and configurable placeholders such as File Location, File System Location, Logging Location, Name of Facts, Dimensions, Staging Tables, etc.</p><p><strong>High-Level Data flow</strong></p><p>Keep your ETL Pipelines and the corresponding data flows, as simple as possible. Plan before you start, draft a blueprint of what your pipeline is about to accomplish. This helps in figuring out those activities that are tricky and are prone to failures, such as processing at the staging layer and copy to the destination data warehouse.</p><p><strong>Incremental Data Refreshes</strong></p><p>Incremental data refreshes are nothing but bringing in the data that has changed or newly inserted into the Data Lake. Business date stamps can be very helpful while performing incremental data refreshes. Incremental data refreshes can be performed wherever it’s applicable since performing a complete refresh of the data for the whole year can be expensive if done frequently.</p><p><strong>Joins in Queries</strong></p><p>Joins in queries are fine, as long as you know what you are doing and the query plan is under check. Make sure to check for the conditions used to join the query, filter the results even before you could join, if possible.</p><p><strong>Keys-Surrogates &amp; Composites</strong></p><p>Surrogate Keys are columns that are added to the data warehouse tables to ensure uniqueness in the destination tables. These keys are essential to perform an upsert operation (insert and update if the insert fails) on the destination tables. The choice of columns is based on the business context and usually, these keys are framed as an SHA encryption of multiple columns combined using a delimiter.</p><p>E.g.: In a fact table representing details about the users purchasing products: Product_id, Order_timestamp, and Customer_id, Delivery_date uniqueness can be achieved for each entry by creating a surrogate key like SHA(Product_id, ‘|’, Order_timestamp, ‘|’, Customer_id). In case, there’s an update in the Delivery_date, the newer date can be updated based on the surrogate key in the Data warehouse.</p><p>While querying the data warehouse, it’s a good practice to check the query plan, if the indexes are properly used. If not, the required indexes can be created. For queries utilizing multiple columns, these can be recognized and a composite key combining these columns can be created for easier access.</p><p><strong>Logging</strong></p><p>Logging helps to track down the pesky ETL run failures. If the ETL application provides logging out of the box, no external logging is required. If not, it’s highly essential to have a custom script that does the logging.</p><p><strong>Monitor</strong></p><p>Mechanisms to monitor the ETL processes need to be in place. For example, Azure Data Factory and Airflow has some really good interfaces to track the failures. In addition to this, mechanisms such as e-mail alerts and slack notifications can be really helpful. In short, a holistic view of the pipeline’s health needs to be available to monitor at all times.</p><p><strong>Nested Loops-Avoid at all costs</strong></p><p>In most situations, the memory leaks observed in the ETL jobs are due to the Join Strategies. This can be found in the Query Plans. Depending on the database and processing engines, the strategy may vary.</p><p>For example: In Spark, the join strategy can be rated from ‘worst’ to ‘best’ as follows:</p><p><em>Broadcast nested loop join (worst) &gt; Cartesian Product &gt; sort-merge join &gt; Hash Joins (best)</em></p><p>As noticed above nested loop join is chosen to be the last resort. A nested loop join strategy looks like this:</p><pre>for each_row in relation_1:<br> for each_row in relation_2:<br> # join condition gets executed`</pre><p>An n squared complexity is not something you would want to experience on a large dataset.</p><p><strong>Optimize &amp; Scale</strong></p><p>ETL Pipelines can be optimized by finding the right time window to execute the pipeline. For example, while scheduling a pipeline to extract the data from the production database, the production business hours need to be taken into consideration so that, the transactional queries of the business applications are not hindered. Choosing the right choice of tools, processing clusters, and strategies can help scale the pipeline. For example, the choice of the clusters for processing can be decided by calculating the query runtime statistics and volume of the rows extracted.</p><p><strong>Parallelize wherever possible</strong></p><p>Parallel processing is always efficient than sequential processing. Big data storage solutions and non-columnar destinations work parallel in nature. Higher versions of relational databases such as Postgres has the ability to parallelize the queries too. The individual processes of ETL can also be parallelized if the tool provides a solution.</p><p><strong>Quality of Data</strong></p><p>The source can be in any form, i.e.) the data in data lakes can be in varied forms. However, the target data warehouses should always have clean data. The ETL scripts need to make sure that the data written to the destination needs to be of high quality. The quality parameters include precision in floats, prevent varchar overflows, split columns based on the present delimiters (‘|’, ‘,’, ‘\t’), use the appropriate compression formats, etc.</p><p><strong>Recovery &amp; Checkpoints</strong></p><p>The ETL Pipelines are to be designed in a pessimistic way, by asking the ‘ <em>what-if</em> ‘ questions. Assume that there’s an outage in the source data lake/datastore; Will the pipeline be capable enough to pause and resume or perhaps, recover or restore from the failed checkpoint? By designing such fail-safe strategies, the pipelines can be robust.</p><p><strong>Schemas &amp; SCDs</strong></p><p>Warehouse and ETL designs are purely driven by the business requirements and the Engineers need to ask the right questions to design the pipeline and BI requirements that the business needs. Schema &amp; Slowly Changing Dimensions are the major steering components for the warehouse design. The schemas can be Star schemas or Snow Flake schemas depending on how fast the schema evolves.</p><ul><li>Star Schemas are constant and each dimension is represented only by a one-dimensional table</li><li>Snow Flake Schemas are a lot more granular compared to the Star Schemas where dimension tables are normalized further, split into multiple tables.</li></ul><p>In addition to this, depending on the necessity to store the historic information of the facts or dimensions, the choice needs to be made between SCD1 or SCD2.</p><ul><li>SCD1 is the activity of updating the existing rows if the key matches.</li><li>SCD2 is the activity where the older data is preserved/invalidated and inserts the newer rows, promoting them to be the latest.</li></ul><p><strong>Temp Tables-Use them Wisely</strong></p><p>As mentioned in the ‘Cache Data’, temporary tables are just a mere representation of the physical tables. They are accessible only to the executed cluster, available until the cluster runs, and are slow for operations since it is neither in memory nor persisted. True to its name, these temp tables are destroyed upon the termination of the cluster.</p><p><strong>Upserts</strong></p><p>As mentioned in the section ‘Schemas &amp; SCDs’, SCD1 will depend on the unique key based on which the upsert operation is done. Upserts are nothing but Inserts and Updates on conflict. Here, the conflict refers to the inability to insert since the key already exists, which will in turn lead to an upsert operation.</p><p><strong>Views</strong></p><p>Creating views on top of complex queries can be very helpful at times when the results of the query needed to be computed and used only a minimal number of times. If it’s too expensive to store, views can come in quite handy.</p><p><strong>Where Conditions</strong></p><p>While performing the transformations, selecting only the bucket of data that is required for the processing can save a large amount of computing time. The ‘where’ clauses can help in achieving this, especially during the incremental refreshes.</p><p><em>Originally published at </em><a href="https://www.datawrangler.in/2020/12/etl-enterprise-level-practices.html"><em>https://www.datawrangler.in</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b1e592c05346" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/etl-enterprise-level-practices-b1e592c05346">ETL &amp; Enterprise Level Practices</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Red Vs Blue Pill — Making the right decision on the choice of BI Server]]></title>
            <link>https://medium.com/datawrangler/red-vs-blue-pill-making-the-right-decision-on-the-choice-of-bi-server-981af9e092a2?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/981af9e092a2</guid>
            <category><![CDATA[power-bi-embedded]]></category>
            <category><![CDATA[tableau-server]]></category>
            <category><![CDATA[tableau]]></category>
            <category><![CDATA[comparison]]></category>
            <category><![CDATA[power-bi]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Mon, 09 Mar 2020 21:20:00 GMT</pubDate>
            <atom:updated>2020-10-03T17:38:13.148Z</atom:updated>
            <content:encoded><![CDATA[<h3>Red Vs Blue Pill — Making the right decision on the choice of BI Server</h3><p>This universe is governed by a cosmic duality, sets of two opposing and complementing principles, or cosmic energies that can be observed in nature. There’s Yin and there’s Yang…</p><p>There’s relational and there are non-relational databases…</p><p>And of course, the visualization tools; There’s <strong>Tableau Server</strong> and there’s <strong>Power BI Embedded</strong>.!!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/400/1*l9-vhqfT73n9MFiBxQKPUA.png" /><figcaption>The Yin &amp; Yang of the present BI universe</figcaption></figure><p>There’s no such thing as a perfect BI server that solves everyone’s (the admin, the data-scientist, the analyst, the end-user) problems. Each of these tools outweighs each other in several aspects and we are about to settle the score once and for all on a one-on-one comparison between Tableau Server and Power BI Embedded.</p><p>By the time when you are done reading these articles, you should be able to answer the following question:</p><blockquote><strong>Should you choose Tableau Server or Power BI Embedded?</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/498/1*CswfnXP7zABBwuSkf0fhpg.gif" /><figcaption>After this, there is no turning back.</figcaption></figure><h3>Choice of the Cloud &amp; Databases:</h3><p>Are you using Microsoft SQL server or are your servers hosted on Microsoft Azure? well, why not use Power BI? Yes, you heard me right, you’ve got a good edge over the Tableau. Check if the data source that you are using, is developed or maintained by Microsoft or even better — Check if your database is compatible for not being required to have an on-premises data gateway from <a href="https://docs.microsoft.com/en-us/power-bi/power-bi-data-sources">this link</a>.</p><p>Of course, this is not the case with Tableau; with a wide variety of connectors and drivers to establish the connectivity, Tableau seems to be much more friendly in terms of the databases. Also, with the support of Tableau Server packages for Linux, Tableau servers have become much more efficient in managing the resources.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/412/0*fRrTPHAEMQZYRzIx.gif" /></figure><h3>Data Extracts &amp; Refreshes:</h3><blockquote><strong>How huge is your data? And are you using extracts or live connections?</strong></blockquote><p>Of course, you may follow all the good practices; if your extract still happens to be huge, Tableau seems to be a better choice. The ability to devise a multi-node architecture for the tableau server is a better fit to improve the performance of the data extracts and data refreshes. <br> In the case of Power BI, the rate of refreshes depend on the size and configuration of the data gateway instance deployed in the same subnet as that of the databases (for Non-Microsoft databases).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/0*vvvpkOXbDVHxxHNJ.gif" /></figure><h3>Visualization Renders:</h3><p>Visualization is the core of the BI exercise. And if those dashboards are not loading on time, that’s gonna hit you real bad. The rendering is all dependent on the hardware — especially the memory. In Tableau, you get to control your hardware — whether it’s a cluster setup or a single node setup. This is not the case with Power BI where you need to purchase a premium dedicated capacity for the rendering to happen; the lowest configuration — A1 has a capacity of performing about 300 renders per hour with a price of 1$ approx per hour. <br>Tableau is the winner here since you are not limited by the number of renders happening.</p><p>Well, it doesn’t stop there. and when your audience grows and when you go up the ladder from (A1 to A6) configuration, you will know that you are shelling out a huge amount of money (could go all the way up to 36 times that of the cost of A1).</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/540/0*KzzsVDHqMbidfGk8.gif" /></figure><h3>Scale:</h3><p>How many viewers are about to have a look at your dashboards? Is it going to be the dashboard with your visualizations on it or is it going to be just the PDF rendering of your dashes? For client-side renderings; Tableau has a cluster setup available to be configured with multiple nodes, each of which can be assigned with the specific processes and dozens of other tweak-able options in hand; scalability is at disposal for the Tableau admins.</p><p>Does it mean that Power BI is up for no good? They do have a dedicated capacity that can be modified based on the peak hour refresh rates and the number of renders happening in an hour.</p><p>We don’t have any winners for this round since both the services incur similar costs when it comes to the licensing aspect.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/0*ukWitRBPwyxClReZ.gif" /></figure><h3>Licenses &amp; Pricing:</h3><p>By this time, you might have figured out the right BI tool for the job. And that’s the reason why pricing comes at the end.</p><p>This topic by itself requires a separate post to talk about as a whole. We will try to cover as crisp as we could in this section.</p><p>Let’s look at a hypothetical scenario, Shall we? Consider an Organization with about 200 data scientists; let’s estimate the Pricing for both Tableau Server &amp; Power BI Embedded.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*0AAY4I5VVbF2h5NT.gif" /></figure><p><strong>Tableau Server — Requisite &amp; Cost: </strong><br> 1. License for the users to use Tableau Desktop &amp; publish to tableau server [70$ per user per month].<br> 2. A tableau server hosted an a compute instance with at least 8 physical cores and 32 Gigabytes of RAM (if a cluster setup is required for better performance — add two more instances with at least 4 cores and 16 Gigabytes of RAM to it) <br>[Single node — 0.70$ per hour; Cluster 0.70$ + 0.38$ + 0.38$ = 1.46$ per hour].<br> 3. A Server creator license with the required number of creators [70$ per user per month].</p><blockquote>Cost incurred: (200*70)+(1.46*730)+(200*70) = <strong><em>29065.8$ /month</em></strong> <br> (For a cluster Setup)</blockquote><p><strong>Power BI Embedded — Requisite &amp; Cost:</strong><br> 1. Pro License for the individual users [9.99$ per user per month].<br> 2. A Power BI Embedded dedicated capacity for performing the refreshes on a custom workspace — let’s consider A4 for the sake of the discussion [$8.06 per hour].<br> 3. A data gateway inside the same subnet as that of the database (recommended configuration of 4 cores and 16 Gigabytes of RAM) [0.38$ per hour].</p><blockquote>Cost incurred: (200*9.99)+(8.06*730)+(730*0.38) = <strong><em>8159.2$ per month</em></strong></blockquote><p>Now that we have the facts right., it’s time to pick our winner!! There isn’t…Both are good contenders and it all comes down to our requirement &amp; usage.</p><blockquote><strong>Let’s build Visualisations; not wage wars!!</strong></blockquote><p><em>Originally published at </em><a href="https://www.datawrangler.in/2020/03/red-vs-blue-pill-making-right-decision.html"><em>https://www.datawrangler.in</em></a><em> on March 9, 2020.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=981af9e092a2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/red-vs-blue-pill-making-the-right-decision-on-the-choice-of-bi-server-981af9e092a2">Red Vs Blue Pill — Making the right decision on the choice of BI Server</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Postgres: Optimization & Beyond]]></title>
            <link>https://medium.com/datawrangler/postgres-optimization-beyond-3c9174e4512a?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/3c9174e4512a</guid>
            <category><![CDATA[database]]></category>
            <category><![CDATA[index]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[postgresql]]></category>
            <category><![CDATA[programming]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Sat, 10 Aug 2019 14:37:00 GMT</pubDate>
            <atom:updated>2020-10-03T17:39:54.515Z</atom:updated>
            <content:encoded><![CDATA[<p>Postgres, one of the widely used Relational Database Management System; has been widely adopted due to its ability to handle different workloads such as web services, warehouses, etc.</p><blockquote><strong><em>Fun Fact:</em></strong><em> The name Postgres comes from it’s predecessor originated from UC Berkley’s Ingres Database (</em>INteractive GRaphics iterchangE System; meaning it’s Post-INGRES<em>).</em></blockquote><p>There are times when the performance is straight forward and in other cases when the expected performance is not met; the Database requires some tweaking in the form of structural modifications to the table, Query Tuning, Configuration improvements, etc.</p><p>This article will provide some useful pointers and action plans to become a power-user in optimizing Postgres.</p><p>What to do when a query is slow?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*udxpi-nT0n98jkcu.gif" /><figcaption>What to do when a query is slow?</figcaption></figure><p>In most cases, the occurrence of a slow query is due to the absence of indexes, for those fields that are being used in the where clause of the query.</p><p>That should have solved the problem, right? RIGHT?</p><p>You:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*jjb-Ad2kwbCn-yAx.gif" /><figcaption>Not helping at all.!!</figcaption></figure><p>I hear you; Life ain’t Fair, or<em> Is it?</em></p><p>Not all Indexes for the fields in the WHERE clause can be helpful; It all depends on the appropriate query plan prepared by the optimizer: Prepend `EXPLAIN ANALYZE` to the query and run it to find the query plan.</p><p><strong>Pro Tip:</strong> Use <a href="https://explain.depesz.com/">https://explain.depesz.com/</a> to visualize and analyze your query plan. The color formatting gives a straight forward output to debug the reason for the slowness.</p><p>The query plan itself can provide a whole lot of information about where the resources are overflowing. Given below, are a few of those keywords that you can find in the query plan and what they mean to you and the query performance.</p><p><strong>Sequential Scan:</strong> Yes, you read that right. The scan occurs sequentially; the filter runs for the whole table and returns the rows that match the condition which can be very expensive and exhaustive. In the case of a single page / small table, Sequential scans are pretty fast.</p><p>But for larger tables; To speed up the query, the sequential scan needs to be changed to an <strong>Index Scan</strong>. This can be done by creating indexes on the <strong>columns that are present in the where clause</strong>.</p><p><strong>Index Scans / Index Only Scans: </strong>Index Scans denote that the indexes are being properly used. Just make sure that the analyzing &amp; vacuuming happens once in a while. This keeps all the dead tuples out of the way and allows the optimizer to choose the right index for the scan.</p><p><strong>Bitmap Index Scan: </strong>And this right here, is the bummer. Bitmap Index Scans are accompanied by Bitmap Heap Scans on top. These scans occur mostly when one tries to retrieve multiple rows but not all, based on multiple logical conditions in the where clause.</p><p>It basically creates a bitmap out of the pages of the table, based on the condition provided (hence the Bitmap Heap Scan on top). The query can be sped up by creating a <strong>composite index A.K.A multicolumn index</strong>; which changes this scan to an Index Scan.</p><p><strong>Caution:</strong> The order of the columns in the composite index needs to be maintained the same order as that of the where clause.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*9GzLaUAPbJKNGJ_2cBwMUg.png" /></figure><h4>Summarizing:</h4><p>Indexes are good; Unused Indexes are Bad;<br>Having Too many Indexes is OK, as long as they are being used at some point.</p><p>More RAM for the DB is Good. The VACUUM &amp; ANALYZE of the tables is too good!!!<br>ARCHIVAL of Old Data → Being a good citizen and you are awesome!!</p><p>For optimal performance, the following settings (requires a restart of the server) need to be made to the Postgresql conf file present in: `<strong><em>/etc/postgresql/10/main/postgresl.conf</em></strong>`</p><p>shared Buffer — 75% of RAM<br> work_mem — 25% of RAM<br> maintenance_mem — Min: 256MB; Max:512MB</p><p>Consider the scenario, where Postgres Server’s has 160Gigs of RAM:</p><p>shared_buffer: 120GB<br> work_mem: 40GB<br> maintenance_mem: 256MB</p><h4>Steps to Optimize a query:</h4><p>1) Run Explain Analyze on your Query, and if takes too long; Run Explain on your Query.</p><p>2) Copy the output and paste it onto the dialogue box @ <a href="https://explain.depesz.com/">https://explain.depesz.com/</a></p><p>3) Check the Stats of your query:</p><p><strong>Index Scans / Index Only Scans</strong> are the best and <strong>no changes</strong> need to be made.</p><p><strong>Sequential Scans</strong> can be <strong>converted into</strong> <strong>Index Scans</strong> by <strong>creating the index for the particular column</strong> in the where clause.</p><p><strong>Bitmap Heap Scans</strong> can be <strong>converted into Index Scans</strong> by <strong>creating composite indexes</strong> A.K.A multicolumn indexes, with the<strong> same order</strong> as that of the where clause, as:</p><p><strong>CREATE INDEX $indexName ON $tableName ($Field1, $Field2);</strong></p><p>Note to Self: Index &amp; Optimize.!!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/448/1*Xs4J9Eyfxo_fvZSRp5HHFg.gif" /></figure><p><em>Originally published at </em><a href="https://www.datawrangler.in/2019/08/postgres-optimization-beyond-level-up.html"><em>https://www.datawrangler.in</em></a><em> on August 10, 2019.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=3c9174e4512a" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/postgres-optimization-beyond-3c9174e4512a">Postgres: Optimization &amp; Beyond</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Flyway — Database Migrations made easy]]></title>
            <link>https://medium.com/datawrangler/flyway-database-migrations-made-easy-12f4c79eac34?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/12f4c79eac34</guid>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[mysql]]></category>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[migration]]></category>
            <category><![CDATA[flyway]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Sun, 24 Mar 2019 20:09:00 GMT</pubDate>
            <atom:updated>2020-10-03T17:41:41.052Z</atom:updated>
            <content:encoded><![CDATA[<h3>Flyway — Database Migrations made easy</h3><h4>&amp; How not to accidentally Rollback all of your migrations</h4><p><strong>Flyway</strong> — by boxfuse: This is a <strong>schema migration tool</strong> and it acts more of like version control for your relational databases.</p><p>If you are manually executing your SQL scripts or if your administrator is manually executing the SQL scripts, on your production or UAT environment, you definitely need this tool to be set up in all of your environments.</p><p>Before we proceed:</p><p><strong>Statutory Warning:</strong></p><p><strong>Never execute the following command, be it your production or UAT environment:</strong></p><p><strong>$ flyway clean # Do not execute this, ever!!!!</strong></p><p><strong>Wondering what it does? It rolls back whatever table migrations/changes you have done through flyway, along with their data. </strong><br><strong>In short, Don’t ever execute this command.</strong></p><p>Now that we are done with all the warnings:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/480/0*BomF8hjJr_iR1leZ.gif" /></figure><h3>Installation:</h3><p>It is fairly straight forward:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/1267150eb006d9bfd935b9f19b3b6b4b/href">https://medium.com/media/1267150eb006d9bfd935b9f19b3b6b4b/href</a></iframe><p>Run the above command in a shell prompt.<br> Running the above creates a directory called as flyway-x.x.x/<br> Inside this directory are many other directories of which, the two most import directories are:</p><ul><li>conf/ — Configuration for each of the databases are kept here as individual conf files</li><li>SQL/ — SQL migrations are kept under different directories for each of the above configurations</li></ul><h3>Setting up the Configuration file:</h3><p>If this is your first time with flyway, I would urge you to go through the configuration file from top to bottom, it’s kinda fun, comical, and scary too. Especially, this part — quote and quote from the default configuration:</p><p><strong># Whether to disabled clean. (default: false)<br># This is especially useful for production environments where running clean can be quite a career-limiting move.<br>flyway.cleanDisabled=false</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/478/0*wJrnsnY0dAPhxjpp.gif" /></figure><blockquote>It’s all fun until one day you accidentally do a clean.</blockquote><p>Again, make sure that this option<strong> flyway.cleanDisabled is set to true,</strong> at all costs.</p><h3>First Things First — User creation in the Database:</h3><p>Make sure you have two users created in your database.</p><p>1) A normal user which should be used at all times — doesn’t have delete or drop privileges:</p><p>E.g.: In MySQL:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/9b7715d05b4c8662a298e3c82e8d9f05/href">https://medium.com/media/9b7715d05b4c8662a298e3c82e8d9f05/href</a></iframe><p>2) And a deleteOnlyUser which should be used only during repair operations and delete/drop operations in a database. The reason why we have such an alternate user is to have much more clear access control over the database.</p><p>E.g.: In MySQL</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/3e7f328bed8c7ccd2124f5bf666bf586/href">https://medium.com/media/3e7f328bed8c7ccd2124f5bf666bf586/href</a></iframe><h3>SQL Setup:</h3><p>Place all the SQL files in their individual directories corresponding to each of the databases under the SQL directory inside flyway-x.x.x.</p><p>Each of the SQL files should be named with a flyway friendly convention, as:</p><p><strong>V1.0__some_random_text.sql</strong></p><p>Make sure that the V in the filename is an uppercase.</p><h3>Configuration Setup:</h3><p>Delete the default configuration file under conf and substitute it with something like the following. Once again, there will be two configurations one for the default user and another for the deleteOnlyUser as:</p><ol><li><strong>DefaultUser Configuration:</strong></li></ol><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c87cdce903493e0b1f5feae9c65fbac5/href">https://medium.com/media/c87cdce903493e0b1f5feae9c65fbac5/href</a></iframe><p><strong>2. DeleteUser Configuration:</strong></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6564260052f21cbd48b120d8ac7f120c/href">https://medium.com/media/6564260052f21cbd48b120d8ac7f120c/href</a></iframe><h3>All Set for migration:</h3><p>Now, there are some basic commands in the flyway for migration, repair, and displaying the information.</p><p><em>$ flyway -configFiles=’flyway-x.x.x/conf/$file_name.conf’ info</em></p><p>Displays the schema versions and baseline related information from the MySQL schema_version table.</p><p><strong><em>Migrate:</em></strong></p><p><em>$ flyway -configFiles=’flyway-x.x.x/conf/$file_name.conf’ migrate</em></p><p>Migrate command scans the filesystem for available migrations. It also compares these with the completed migrations. It is the centerpiece, aiding in the migration of the SQL files.</p><p><strong><em>Repair:</em></strong></p><p><em>$ flyway -configFiles=’flyway-x.x.x/conf/$file_name.conf’ repair</em></p><p>When there’s a failed migration, upon correction; the checksums need to be Realigned of the applied migrations with the ones of the available migrations.</p><p><strong><em>Clean:</em></strong></p><p><strong><em>$ flyway -configFiles=’flyway-x.x.x/conf/$file_name.conf’ repair</em> </strong><br><strong>Don’t even think about it. If you are still wondering, it rolls back all of your migrations. Not suitable for Production/UAT/Pre prod or anywhere else.</strong></p><h3>BONUS: Migrating to a different version of flyway or Starting afresh with a new set of SQL scripts:</h3><p>Let’s say, our database grows in size, and there comes a scenario where the old migrations need to be archived. In that case, the following maintenance needs to be done.</p><p><em>mysql&gt; drop table flyway_schema_history;<br>mysql&gt; drop table schema_version;</em></p><p>Alter your configuration file to locate the recent SQL files and set the baseline to a different version number.</p><p><em>$ flyway -configFiles=’flyway-x.x.x/conf/$file_name.conf’ baseline</em></p><p>This baselines the database with the mentioned version. This will cause migrate to ignore all migrations up to and including that particular version<em>.</em></p><p>That wraps up our discussion and flyway.</p><p>And remember kids; Always set your flyway.cleanDisabled as True.</p><p>As quoted from the <strong><em>flyway.conf</em></strong> file:</p><p># Whether to disabled clean. (default: false)<br># This is especially useful for production environments where running clean can be quite a career-limiting move.</p><p><em>Originally published at </em><a href="https://www.datawrangler.in/2019/03/flyway-database-migrations-made-easy.html"><em>https://www.datawrangler.in</em></a><em> on March 24, 2019.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=12f4c79eac34" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/flyway-database-migrations-made-easy-12f4c79eac34">Flyway — Database Migrations made easy</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[CD-Stream:CDC Replicator Tool]]></title>
            <link>https://medium.com/datawrangler/cd-stream-cdc-replicator-tool-365a37a460a9?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/365a37a460a9</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[cdc]]></category>
            <category><![CDATA[etl]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Tue, 30 Oct 2018 21:12:00 GMT</pubDate>
            <atom:updated>2019-05-27T19:14:05.911Z</atom:updated>
            <content:encoded><![CDATA[<h4>++ Cons on ETL pipelines</h4><p>Just another day at the work place;</p><p><strong><em>5 minutes post the boot:</em></strong></p><p>You hear everyone complain that the production database is slow. You quickly start to investigate; exploring all possible outcomes on the dashboards..</p><blockquote><strong>Could it have been the long-running slow query which you had raised a ticket for the production support to fix?.. Or Is it one of the queries run based on a non-indexed column?</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*5b7aE4SKkbTm-jb8.gif" /></figure><p><strong><em>6th Minute and 15 minutes later:</em></strong></p><p>Next you hear the fellow data-analysts lament over their failed reports.</p><p>You now realize that your CPU had taken a humongous amount of query load and you understand that your relational database system has gone for a toss into an eternal slumber.</p><h3>And all of this due to a slow running query of your ETL pipeline..!! Ding. Ding.. Ding…!! We have a winner!!!</h3><p>Alright, let’s back it up a little bit.</p><p>Probably you did/used one of the following:</p><p>- SELECT * from production_database.table where updated_at between x and y;</p><p>- Bulk exports and Dumps once in every few minutes</p><p>- Long running and forgotten Zombie Crontabs</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/311/0*NWQ-pURqbOWtGPdr.jpg" /></figure><p>Let’s put it this way.. DB Size &lt; 500 GB; it’s OK to do selects but if it exceeds &gt;500GB, unless you have cuts in the budget, do not ever do a bulk select and transfer it over the wire to the destination database in the form of a pipeline.</p><h4>Enter CDC:</h4><p>CDC A.K.A Change Data Capture is there to assist this data wrangling exercise and if your data is rapidly growing and if your BI/BA need an access to that sweet-sweet DWH (data warehouse); THIS right here, is the way to go.</p><h4>You meant the replication? — I don’t wanna get stuck with my OLTP DB Engine:</h4><p>Well, you are not alone. For many reasons, data-wranglers generally don’t prefer a similar database engine as that of the OLTP. The main reasons include query performance, need for triggers and ease of re-running transformation jobs. But, doing so, requires setting up of airflow clusters and setting up connectors to transform and load.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*S7iFWzeO5Ts9cYJ2.jpg" /></figure><p>Wouldn’t it be great if you could do all this without doing a bulk select from the production database? Of course, you can..</p><p><strong>CD-Stream</strong> is a cross-database CDC driven replicator tool that currently supports replication between <strong><em>MySQL and Postgres</em></strong>.</p><p>The tool runs queues to process the information occurring in the binary logs of the source database and replicates it across to a destination database of entirely different engine.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/0*zO-ZxOZB5LpJtGsY.png" /></figure><p>Post the setup, as given in the project page: <a href="https://github.com/datawrangl3r/cd-stream">CD-Stream</a>; there’s a directory called ‘sample’ in the project which contains some of the intensive DDL and Data Insertion scripts, for you to evaluate and exercise.</p><p>And that, <em>People</em>; is how you wrangle production data!!!</p><p><em>Originally published at </em><a href="https://www.datawrangler.in/2018/10/cd-streamcdc-replicator-tool-cons-on.html"><em>https://www.datawrangler.in</em></a><em> on October 30, 2018.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=365a37a460a9" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/cd-stream-cdc-replicator-tool-365a37a460a9">CD-Stream:CDC Replicator Tool</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The No-BS guide to Autocomplete and Fuzzy-search in Elasticsearch]]></title>
            <link>https://medium.com/datawrangler/the-no-bs-guide-to-autocomplete-and-fuzzy-search-in-elasticsearch-85c89ea306c7?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/85c89ea306c7</guid>
            <category><![CDATA[fuzzy-matching]]></category>
            <category><![CDATA[autocomplete]]></category>
            <category><![CDATA[fuzzy-logic]]></category>
            <category><![CDATA[elasticsearch]]></category>
            <category><![CDATA[keyword-research]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Tue, 03 Jul 2018 18:53:00 GMT</pubDate>
            <atom:updated>2020-10-03T17:42:29.244Z</atom:updated>
            <content:encoded><![CDATA[<h3>Elasticsearch — Autocomplete and Fuzzy-search</h3><h4>The No-BS guide</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/1*x3uqttlp-NNAlHxqwRbXyg.gif" /></figure><p><strong><em>Before we begin.. Here are a few basics.</em></strong></p><h3>Analyzer:</h3><p>An analyzer does the analysis or splits the indexed phrase/word into tokens/terms upon which the search is performed with much ease.<br> <br> An analyzer is made up of tokenizers and filters.<br> <br> There are numerous analyzers in Elasticsearch, by default;<br> here, we use some of the custom analyzers tweaked to meet our requirements.</p><h3>Filter:</h3><p>A filter removes/filters keywords from the query. Useful when we need to remove false positives from the search results based on the inputs.<br> <br> We will be using a stop word filter to remove the specified keywords in the search configuration from the query text.</p><h3>Tokenizer:</h3><p>The input string needs to be split, to be searched against the indexed documents. We are about to use ngram here, which splits the query text into sizeable terms.</p><h3>Mappings:</h3><p>The created analyzer needs to be mapped to a field-name, for it to be efficiently used while querying.</p><h3>T’is time!!!</h3><p>Now that we have covered the basics, it&#39;s time to create our index.</p><h3>Fuzzy Search:</h3><p>The first upon our index list is fuzzy search:</p><pre>curl -vX PUT http://localhost:9200/books -d @fuzzy_index.json \<br>--header &quot;Content-Type: application/json&quot;</pre><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/089f68066c7d3b66764cc5b3e6bae4c9/href">https://medium.com/media/089f68066c7d3b66764cc5b3e6bae4c9/href</a></iframe><p>And, the following books and their corresponding authors are loaded to the index.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/606/1*COXoe1ieftVJczLwxty2Xg.png" /></figure><p>When a fuzzy query such as:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/97fee62b68ee1db37c03d355c54e8ba4/href">https://medium.com/media/97fee62b68ee1db37c03d355c54e8ba4/href</a></iframe><p>This query with the match keyword as “ready” returns the matched books ready as a keyword in the phrase; as,</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2d355191413d6dea9c683c6bba1a119f/href">https://medium.com/media/2d355191413d6dea9c683c6bba1a119f/href</a></iframe><h3>AutoComplete:</h3><p>Next up, is the autocomplete. The only difference between a fuzzy search and an autocomplete is the min_gram and max_gram values.</p><p>In this case, depending on the number of characters to be auto-filled, the min_gram and max_gram values are set, as follows:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/36a385a3207075d32818d113615a275e/href">https://medium.com/media/36a385a3207075d32818d113615a275e/href</a></iframe><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=85c89ea306c7" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/the-no-bs-guide-to-autocomplete-and-fuzzy-search-in-elasticsearch-85c89ea306c7">The No-BS guide to Autocomplete and Fuzzy-search in Elasticsearch</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Elasticsearch to MongoDB Migration — MongoES]]></title>
            <link>https://medium.com/datawrangler/elasticsearch-to-mongodb-migration-mongoes-ddd521ea20d5?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/ddd521ea20d5</guid>
            <category><![CDATA[migration]]></category>
            <category><![CDATA[db-migration]]></category>
            <category><![CDATA[data-migration]]></category>
            <category><![CDATA[mongodb]]></category>
            <category><![CDATA[elasticsearch]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Sun, 11 Feb 2018 11:05:00 GMT</pubDate>
            <atom:updated>2019-05-27T18:23:42.154Z</atom:updated>
            <content:encoded><![CDATA[<h3>Elasticsearch to MongoDB Migration — MongoES</h3><p>The following are some of the instances where the developers simply <strong>love to hate</strong>!</p><ul><li><strong>The one-last-thing syndrome </strong>— This reminds me of the following quote:</li></ul><blockquote><em>The first 90 percent of the code accounts for the first 90 percent of the development time. The remaining 10 percent of the code accounts for the other 90 percent of the development time.</em></blockquote><blockquote>- <em>Tom Cargill, Bell Labs, from the book `Programming Pearls`</em></blockquote><ul><li><strong>QAs declaring certain undocumented features to be as bugs</strong> — Seriously, this create traumas for a developer.</li><li><strong>Interruptions during coding</strong> — Here’s an idea. Try talking to developers while they code; chances are, they have just about &lt;10% of your attention.</li></ul><p>There are some problems which we get used to..</p><p>But, there are others which makes us wanna do this..</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/0*nyUrsv_0miHBVbq8.gif" /><figcaption>Ouch!! That’s gotta hurt real bad</figcaption></figure><ul><li><strong>DISCONNECTION FROM THE SERVER DUE TO BAD INTERNET DURING A MIGRATION </strong>— Ouch!! That’s gotta hurt real bad.</li></ul><h3>Talking about ES to MongoDB Migration - How hard could that be?</h3><p><strong>Good Side:</strong></p><ul><li>JSON objects are common for both.</li><li>Numerous tools to choose from, for migration.</li></ul><p><strong>Bad Side:</strong></p><ul><li>The Migration can be hideous, and can eat up a lot of the system resources. Be ready for a system-freeze, in case the migration tool uses a queue.</li></ul><p><strong>Ugly Side:</strong></p><ul><li>Can never be resumed from the point of failure. If the connectivity goes down during the migration; the transferred collection has to be deleted and the data transfer has to be initiated once again from the beginning.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/220/0*ZqI8-3spnmkC1q26.gif" /></figure><p>Alright, there’s nothing there to be felt bad about.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/640/0*h4RS28Mnj9ywK7CH.png" /></figure><p><a href="https://github.com/datawrangl3r/mongoes"><strong><em>MongoES</em></strong></a> is a pure python3-bred Migration tool to migrate documents from the Elasticsearch’s index to the MongoDB collections.</p><p>It’s robust in it’s native way; no queues/message brokers are involved; which means that there won’t be any memory spikes or system freezes.</p><p>This became achievable due to the fact that <em>MongoES </em>specifically uses a tagging strategy prior to the migration. The tagging happens in the source Elasticsearch, which stands as a checkpoint during the migration.</p><h3>Why a new custom id tagging, while there’s an _id already?</h3><p>Unless the documents are explicitly tagged, the _id fields in Elasticsearch documents are a bunch of alphanumeric strings generated to serialize the documents. These <strong><em>_id</em></strong> columns become unusable, since queries/aggregations can not be run using them.</p><p><strong>MongoES — How to:</strong></p><ol><li>Install all the Prerequisites.</li><li>Clone the repository from <a href="https://github.com/datawrangl3r/mongoes.git">https://github.com/datawrangl3r/mongoes.git</a></li><li>Edit the <strong><em>mongoes.json</em></strong> file according to your requirements.</li></ol><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/27709a5beaeca657328adcf8870dc2e3/href">https://medium.com/media/27709a5beaeca657328adcf8870dc2e3/href</a></iframe><p>4. Make sure that both the elasticsearch and mongoDB services are up and running, and fire up the migration by keying in:</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/23b7c7874fe9cdc211546b7e6fcc1d87/href">https://medium.com/media/23b7c7874fe9cdc211546b7e6fcc1d87/href</a></iframe><p>5. Sit back and relax; for we got you covered! The migration’s default value is 1000 documents per transfer.</p><p>Happy Wrangling!!! :)</p><p><em>Originally published at </em><a href="https://www.datawrangler.in/2018/02/elasticsearch-to-mongodb-migration.html"><em>https://www.datawrangler.in</em></a><em> on February 11, 2018.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=ddd521ea20d5" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/elasticsearch-to-mongodb-migration-mongoes-ddd521ea20d5">Elasticsearch to MongoDB Migration — MongoES</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[ES Index — S3 Snapshot & Restoration:]]></title>
            <link>https://medium.com/datawrangler/es-index-s3-snapshot-restoration-7b7def0d9e0d?source=rss-ae9f7a2b0daf------2</link>
            <guid isPermaLink="false">https://medium.com/p/7b7def0d9e0d</guid>
            <category><![CDATA[snapshot]]></category>
            <category><![CDATA[restore]]></category>
            <category><![CDATA[backup]]></category>
            <category><![CDATA[index]]></category>
            <category><![CDATA[elasticsearch]]></category>
            <dc:creator><![CDATA[Sathiya Sarathi]]></dc:creator>
            <pubDate>Fri, 15 Dec 2017 16:26:00 GMT</pubDate>
            <atom:updated>2019-05-26T09:46:39.648Z</atom:updated>
            <content:encoded><![CDATA[<h3>ES Index — S3 Snapshot &amp; Restoration:</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/484/0*tB85jys_DWuTYI7o.jpg" /></figure><p>The question is.. What brings you here? Fed up with all the searches on how to back-up and restore specific indices?</p><p>Fear not, for your search quest ends here.!</p><p>After going through a dozens of tiny gists and manual pages, here it is.. We’ve done all the heavy-lifting for you.</p><p>The following tutorial was tested on <strong><em>elasticsearch V5.4.0</em></strong></p><p>And before we proceed, remember:</p><p><strong>Do’s:</strong></p><p>Make sure that the <strong><em>elasticsearch version of the backed-up</em></strong></p><p><strong><em>cluster’s version &lt;=(lesser than or equals) Restoring Cluster’s version.</em></strong></p><p><strong>Dont’s:</strong></p><p>Unless it’s highly necessary;</p><p><strong><em>curl -XDELETE ‘http://localhost:9200/nameOfTheIndex</em></strong></p><p><strong><em>#deletes a specific index</em></strong></p><p>Especially not, when you are drunk!:</p><p><strong><em>curl -XDELETE ‘http://localhost:9200/_all</em></strong></p><p><strong><em>#deletes all indexes</em></strong></p><blockquote>(This is where the drunk part comes in..!!)</blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/500/0*PaiwTUgOnjUipmrX.jpg" /></figure><h3>Step1: Install S3 plugin Support:</h3><p>sudo bin/elasticsearch-plugin install repository-s3<br> (or)<br>sudo /usr/share/elasticsearch/bin/elasticsearch-plugin install repository-s3</p><p>Depends on where your elasticsearch-plugin executable is installed. This enables the elasticsearch instance to communicate with the AWS S3 buckets.</p><h3>Step2: Input the Snapshot registration settings:</h3><p><strong>METHOD</strong>: PUT</p><p><strong>URL:</strong> <a href="http://localhost:9200/_snapshot/logs_backup?verify=false&amp;pretty">http://localhost:9200/_snapshot/logs_backup?verify=false&amp;pretty</a></p><p><strong>PAYLOAD:</strong><br> {<br> “type”: “s3”,<br> “settings”: {<br> “bucket”: “WWWWWW”,<br> “region”: “us-east-1”,<br> “access_key”: “XXXXXX”,<br> “secret_key”: “YYYYYY”<br> }<br> }</p><p><strong>In the URL:</strong><br>- <strong>logs_backup : </strong>Name of the snapshot file</p><p><strong>In the payload JSON:</strong><br>-<strong> bucket </strong>: “WWWWW” is where you enter the name of the bucket.<br>- <strong>access_key &amp; secret_key </strong>: The values “XXXXXX” and “YYYYYY” is where we key in the access key and secret key for the buckets based on the <em>IAM</em> policies. If you need any help to find it, here’s a link which should guide you through (<a href="https://aws.amazon.com/blogs/security/wheres-my-secret-access-key/">https://aws.amazon.com/blogs/security/wheres-my-secret-access-key/</a>).<br>- <strong>region </strong>: region where the bucket is hosted (choose any from: <a href="http://docs.aws.amazon.com/general/latest/gr/rande.html">http://docs.aws.amazon.com/general/latest/gr/rande.html</a>).</p><p>This should give a response as, ‘<strong>{“acknowledged”: “true”}</strong>’.</p><h3>Step3: Cloud-Sync — list all Snapshots:</h3><p><strong>URL: </strong><a href="http://localhost:9200/_cat/snapshots/logs_backup?v">http://localhost:9200/_cat/snapshots/logs_backup?v</a></p><p><strong>In the URL:</strong><br>- <strong>logs_backup : </strong>Name of the snapshot file<br> Time to sync up all the list of snapshots. If all our settings have been sync’d up just fine; we should end up with a list of indices, close to that of what is shown below:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/811/0*F29ShkFj4tm_F5VC.png" /></figure><h3>Step4: Creating a Snapshot:</h3><p><strong>METHOD</strong>: PUT</p><p><strong>URL:</strong> <a href="http://localhost:9200/_snapshot/logs_backup/type_of_the_backup?wait_for_completion=true">http://localhost:9200/_snapshot/logs_backup/type_of_the_backup?wait_for_completion=true</a></p><p><strong>PAYLOAD:</strong><br> {<br> “indices”: “logstash-2017.11.21”,<br> “include_global_state”: false,<br> “compress”: true,<br> “encrypt”: true<br> }</p><p><strong>In the URL:</strong><br>- <strong>logs_backup</strong> : Name of the snapshot file</p><p>- <strong>type_of_the_backup</strong> : Could be any string</p><p><strong>In the payload JSON:</strong><br>- <strong>indices</strong> : Correspond to the index which is to be backed-up to S3 bucket. In case of multiple indices to back up under a single restoration point, the indices can be entered in the form of an array.<br>- <strong>include_global_state </strong>: set to ‘false’ just to make sure there’s a cross-version compatibility. <strong><em>WARNING If set to ‘true’, the index can be restored only to the ES of the source version. </em></strong><br>- <strong>compress</strong> : enables compression of the index meta files backed up to S3.<br>- <strong>encrypt : </strong>In case if extra encryption on the indices is necessary.</p><p>This should give a response as, ‘<strong>{“acknowledged”: “true”}</strong>’</p><h3>Step5: Restoring a Snapshot:</h3><p><strong>METHOD</strong>: PUT</p><p><strong>URL:</strong> <a href="http://localhost:9200/_snapshot/name_of_the_backup/index_to_be_restored/_restore">http://localhost:9200/_snapshot/name_of_the_backup/index_to_be_restored/_restore</a></p><p><strong>PAYLOAD:</strong><br> {<br> “ignore_unavailable”: true,<br> “include_global_state”: false<br> }</p><p>In the URL:<br><strong>logs_backup</strong> : Name of the snapshot file</p><p><strong>index_to_be_restored</strong> : Any of the index from the id listed in Step:3</p><p>In the payload JSON:<br>- <strong>ignore_unavailable</strong> : It’s safe to set this to true, to avoid unwanted checks.<br>- <strong>include_global_state </strong>: set to ‘false’ just to make sure there’s a cross-version compatibility. <strong>WARNING</strong> <strong>If set to ‘true’, the index can be restored only to the ES of the source version.</strong></p><p>This should give a response as, ‘<strong>{“acknowledged”: “true”}</strong>’</p><p>Et Voila! The restoration is complete.</p><p>And Don’t forget to recycle the space corresponding to the index by safely deleting it — Reuse, Reduce &amp; Recycle :)</p><p>Happy Wrangling!!!</p><p><em>Originally published at </em><a href="https://www.datawrangler.in/2017/12/es-index-s3-snapshot-restoration.html"><em>https://www.datawrangler.in</em></a><em> on December 15, 2017.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7b7def0d9e0d" width="1" height="1" alt=""><hr><p><a href="https://medium.com/datawrangler/es-index-s3-snapshot-restoration-7b7def0d9e0d">ES Index — S3 Snapshot &amp; Restoration:</a> was originally published in <a href="https://medium.com/datawrangler">Datawrangler</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>