<?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 Iqbal Singh on Medium]]></title>
        <description><![CDATA[Stories by Iqbal Singh on Medium]]></description>
        <link>https://medium.com/@Iqbalkhattra85?source=rss-4d6775d784d5------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*LZYg-lwN9ZodduPNCWIXhA.png</url>
            <title>Stories by Iqbal Singh on Medium</title>
            <link>https://medium.com/@Iqbalkhattra85?source=rss-4d6775d784d5------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 13:42:27 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@Iqbalkhattra85/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[SQLDesk : Building a BI tool in AI era]]></title>
            <link>https://medium.com/@Iqbalkhattra85/sqldesk-claude-coded-bi-tool-6225064317c9?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/6225064317c9</guid>
            <category><![CDATA[open-source]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[business-intelligence]]></category>
            <category><![CDATA[ai-agent]]></category>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Sat, 07 Nov 2026 01:23:15 GMT</pubDate>
            <atom:updated>2026-11-07T11:31:13.697Z</atom:updated>
            <content:encoded><![CDATA[<h3>SQLDesk : Building a BI tool in AI Era</h3><h3>SQLDesk is an open-source place where users and agents query the same data under the same rules.</h3><p>I used the open source tool Redash for long time and loved it, its simple. open source and get the job done. Always wanted to add some of the features like better visualizations, better audit and control for admins running it on prem. With Claude and Codex into the picture i was thinking to extend redash from some time but was not sure how that will work. I picked up the project one day and after almost 8 weeks i have something that i use every day to query and visualize the data across all Data sources in once place, i use it as admin so have given access to claude to build queries dashboards using the CLI and thn basically stamp it to confirm i checked the data and this query answers a particular question so others can use it as well. just thought of putting it out if anyone else wanna try and check it out, Might have rough edges for now but i will be working on it in coming days to smooth out those areas. Here we go….</p><p><a href="https://sqldesk.org/index.html">SQLDesk</a></p><p>Every data team is having some version of the same conversation this year.</p><p>Somebody wires an AI agent to the warehouse. It’s magic for a week. Then someone asks the uncomfortable questions:</p><p><em>Whose credentials is it using? Can it see the salary table? Did it just run a full scan on the 4-billion-row events table at 9am on a Monday? And why did it write a new “revenue” query when finance already has one everybody agreed on?</em></p><p>Meanwhile the dashboards live in one tool, the notebooks on somebody’s laptop, the Kafka topics in a terminal window and the BI charges you for every person who only wants to <em>read</em> a chart.</p><p>i added SQLDesk to get all that done in one place, one self-hosted install where you write SQL, chart it, share it — and then hand it to your agent, which sees exactly what you see and nothing more.</p><p>Here are the five things it does that made me build it.</p><h3>1. Your agent gets your permissions, not a second set of keys</h3><p>SQLDesk speaks MCP (the Model Context Protocol), so Claude or any agent that speaks MCP can connect to it directly. The difference from “give the bot a database user” is the whole point:</p><ul><li>It runs as you. Same data sources, same row limits, same groups. If you can’t see the HR schema, neither can your agent. There is no service account to configure, on purpose.</li><li>It looks for work your team already did <em>before</em> writing anything new. It searches saved queries and dashboards first, so it reuses finance’s definition of revenue instead of inventing its own.</li><li>It checks SQL before it runs it. Missing WHERE, missing LIMIT, an ORDER BY that will sort the whole table it&#39;s told, in plain words.</li><li>Writes are off until you turn them on, and when they’re on they arrive as drafts, capped per day. Every call refusals included is audited with what it changed.</li></ul><p>Here’s a real exchange against SQLDesk’s demo data (trimmed where marked)</p><pre>&gt; which region brings in the most revenue?<br><br>  find_queries(&quot;revenue by region&quot;)<br>    #2 Every region, measured four ways<br>      Four measures per region, for finding the one that behaves unlike the rest.<br>      - Revenue by region (column chart; x: region; y: revenue)<br>    …<br><br>  check_sql(…)<br>    WARNING: No WHERE clause and no LIMIT<br>    WARNING: ORDER BY with no LIMIT<br><br>  run_query(… limit 3)<br>    region           revenue     orders<br>    Japan            132212.86   508<br>    United Kingdom   111590.14   443<br>    South Africa     105544.56   423</pre><p>Notice what happened: the agent didn’t guess at table names. It found the query your team already trusts, got warned about an expensive pattern, and added a LIMIT before running. That&#39;s 18 tools in total 11 that read, 7 that can build queries, charts, dashboards and (muted) alerts.</p><h3>2. From one SELECT to a dashboard that works</h3><p>The core is the part analysts already know how to use: a SQL editor with a schema browser, autocomplete and parameters, over 60+ data sources — PostgreSQL, MySQL, BigQuery, Snowflake, ClickHouse, Trino, MongoDB, Elasticsearch, Prometheus and many more.</p><p>Then everything downstream of that query:</p><ul><li>27 kinds of visualization the usual lines and bars, plus sankeys, candlesticks, cohorts, funnels, maps, trees and more, all configured without code.</li><li>Dashboards that filter across every chart at once, refresh themselves on the server, and export to a one-page PDF.</li><li>Alerts to 13 destinations, email, Slack, Microsoft Teams, PagerDuty, webhooks and more with a picture of the chart if you want one.</li><li>Public links and embeds for the people who’ll never log in.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*S0RdRjNpysZxtaGz_R-aCQ.png" /></figure><h3>3. Notebooks for the work a single query can’t hold</h3><p>Some questions don’t fit in one SELECT. They need two data sources joined, a reshape that&#39;s easier in pandas, and a chart at the end. Today that work happens in a Jupyter notebook on someone&#39;s laptop where the warehouse permissions, the scheduler and the audit log all stop.</p><p>SQLDesk notebooks (in beta) keep that work where the data lives:</p><ul><li>A chain of SQL and Python cells. Pull from one source, join it to another, reshape it in Python, chart it.</li><li>Cells are named, never numbered. A later cell reads an earlier one <em>by name </em>as a table in SQL, as a data frame in Python so reordering never breaks a reference.</li><li>A Python cell computes; it can’t query. It only reads the cells above it, which means no credential ever reaches the Python interpreter. That’s the design decision I’m proudest of.</li><li>Stale is shown, never silently “fixed.” If a cell’s inputs changed, it tells you and leaves the number you saw exactly as it was.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VmQHMzNKoibOa_t5FjVxjA.png" /></figure><h3>4. Watch a Kafka topic like it’s a table</h3><p>In SQLDesk, a Kafka cluster is a data source and its topics are tables. You write ordinary SQL against a live window of a topic, in the same editor the <em>Execute</em> button just becomes <em>Start streaming</em> and chart it like anything else.</p><ul><li>JSON, Avro and Protobuf, with or without a schema registry.</li><li>Per-minute rollups keep the part worth keeping for 24 hours.</li><li>A slot is a topic, not a viewer: five people watching the same topic share one consumer.</li></ul><pre>select time_bucket(interval &#39;10 seconds&#39;, _received_at) as at,<br>       region,<br>       count(*) as orders<br>from orders<br>where _received_at &gt; now() - interval &#39;3 minutes&#39;<br>group by 1, 2<br>order by 1, 2</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*7AOdKyPLf-2FzPTw2IX-1w.png" /></figure><p>It’s deliberately <em>not</em> a streaming engine it’s a bounded window kept in DuckDB. But for “what is actually arriving on this topic right now, by region?”, it beats kafkacat | jq by a mile.</p><p>Who this helps: the on-call engineer and the product team who want to <em>see</em> the event stream without building a pipeline first.</p><h3>5. Self-hosted, open source and viewers are free</h3><p>SQLDesk is Apache 2.0 and runs on your own machines. There’s no pricing because there’s nobody to charge you for seats. Share the dashboard with the whole company; it costs the same.</p><p>It’s also quiet: nothing phones home. No usage data, no account details, and no version check unless you point one at a server of your own.</p><p>Images are published for amd64 and arm64, so it runs natively on an Apple Silicon laptop. There’s a Helm chart for Kubernetes too.</p><p>Then after you’ve created your admin account on the first page build a complete demo in about a minute: That gives you a dataset, 19 queries, one visualization of every type, a dashboard and a notebook to click around in. Every screenshot in this post is that demo.</p><p>Who this helps: the startup that doesn’t want a BI bill that grows with headcount, and the platform team that wants its data to stay inside its own network.</p><h3>What it is not (on purpose)</h3><p>I’d rather you know before you install it:</p><ul><li>Not a point-and-click question builder. Somebody writes SQL; everybody else reads it, changes its parameters and subscribes.</li><li>Not infrastructure monitoring. It queries Prometheus and Grafana Mimir, but not at one-second resolution.</li><li>Not a streaming engine. A bounded window, kept in DuckDB and rolled up.</li><li>Not a place to run arbitrary Python. Notebook Python computes on the cells above it, with no credentials.</li></ul><h3>Try it</h3><ul><li>Website and docs: <a href="https://sqldesk.org/">sqldesk.org</a> — with search across the whole guide.</li><li>Source: <a href="https://github.com/bot-netizen/sqldesk">github.com/bot-netizen/sqldesk</a></li></ul><p>I’d genuinely love to hear what breaks, what’s missing, and which of these five you’d want to see go deeper next.</p><p><em>SQLDesk is an independent open-source project built on the Redash codebase. It is not affiliated with or endorsed by the Redash project.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6225064317c9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Read Without a Reason !!]]></title>
            <link>https://medium.com/@Iqbalkhattra85/read-without-a-reason-fe1cfe0dbb86?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/fe1cfe0dbb86</guid>
            <category><![CDATA[design-thinking]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[reading]]></category>
            <category><![CDATA[model]]></category>
            <category><![CDATA[critical-thinking]]></category>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Fri, 04 Sep 2026 18:20:18 GMT</pubDate>
            <atom:updated>2026-09-04T18:39:09.212Z</atom:updated>
            <content:encoded><![CDATA[<p><em>How scattered reading turns into real intuition</em></p><p>I have worked on data systems for last 15 years and one thing i usually get asked by my peer engineers <em>“How they can see gaps or find cool problems to solve” or “How sometimes the dots just connect and you can see through a problem”</em></p><p>For this one, i used a model for very long time shared by one of my mentors at the start of my career.</p><h3>The stuff I learned “for nothing”</h3><p>I started my career teaching c/c++ &amp; Java to college students. Then i got my first real tech job as PL/SQL developer.</p><p>So i filed Java away and i remember thinking that i would be database person for rest of my career and would never write Java or C/C++ again. Almost eight year later i was running map reduce jobs, thn spark applications and these days some flink review of stuff written in heavy Java design pattrens.</p><p>Everything i thought i wasted my time on the JVM architecture, object lifecycle, the language i was sure i had left behind turned out to be the exact thing i needed 15 years later in my career and still use it. it is not something i planned but it was sitting in my head waiting. That is the pattern that became a model.</p><h3>The Model: Dots, Clusters, Overlap</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*FNbxN7osZgHNevLhnQJ_CA.png" /></figure><h3>1. Collect dots</h3><p>For starters, Read two or three blogs, docs or anything that interests you a day, something related to your field.For me it was anything around programming or software engineering. Also Real engineering; not think pieces about engineering, not roundups, not “TOP trends.” Read about Someone debugging a memory leak, Someone explaining a Scheduler or Someone doing some hacky strange project with Go.</p><p>The rule is that you read it <strong>without asking whether you’ll use it.</strong> You’re not working in Rust? Read the piece on the borrow checker works anyway. You’ll never touch that Rust codebase? Doesn’t matter.</p><p>Each thing you read is a dot landing somewhere on a two-dimensional map in your head. Most of them land alone and look useless for years. That’s fine. That’s the cost, just accumulate the data.</p><h3>2. Dots Become Clusters</h3><p>Keep reading and the dots stop landing randomly. You have so much information on diff domains that the Dots start landing near each other.</p><p>You read enough about the JVM and you have a cluster: Garbage Collection, Collection Types, Heap Behaviour, Allocation. You read enough about Spark and you have another: Execution model, Shuffles, Executor memory. Then Flink. Then Iceberg. Then the Rust ownership model.</p><p>These are your knowledge clusters. You have not built them deliberately. They’re just what happens when dots accumulate in the same neighbourhood over a time period.</p><h3>3. Clusters Start to Overlap</h3><p>This is the part that actually matters, and it’s the part you can’t rush.</p><p>Once clusters get dense enough, they touch. Someone asks you a streaming question and you’re answering from Flink <em>and</em> from Spark at the same time, because they’re both solving the same underlying problem in different ways. Write-ahead logs show up everywhere once you’ve seen them once in databases, in streaming engines, in storage layers. Somebody starts explaining Delta or Hudi to you and you’re already three steps ahead, because you’ve read enough about Iceberg that the shape is familiar. You’re not learning a new thing. You’re mapping a new label onto a structure you already know.</p><p>And then the real thing happens: it goes unconscious. A problem shows up in front of you and the connections form before you’ve decided to make them. You don’t feel yourself reasoning. You just <em>see</em> it.</p><p>it’s where the “how did you know that” moments come from. It’s not intelligence. It’s inventory, you have seen it some where or read about it somewhere.</p><h3>Why this matters more now, not less</h3><p>Obvious objection: why read anything when Claude and GPT will answer the question for you?</p><p>Its Backwards. As these tools get better at producing solutions, the scarce skill becomes judging them and judgment runs on context. The models are good at the code. You need to be good at everything underneath it.</p><p>You don’t need to memorise for-loops and syntax. You need to know how a Spark executor structures memory, Is there an option to change the memory config. Real win is you need to ask right questions and for that you need context maybe just ask why it cant do the way flink can do it, its easy to ask of you know how flink do it. How much of it goes to storage versus execution. Where objects actually live on-heap or off-heap.</p><p>None of that comes from asking a model a question. It comes from having read about it a hundred times in a hundred contexts until it’s part of how you think.</p><h3>What to actually do ?</h3><p>Start reading. Without a reason.</p><p>Few pieces of real engineering writing a day. Follow what’s interesting, not what’s relevant. Don’t take notes if you don’t want to, this isn’t a note-taking system, it’s an accumulation habit. Don’t audit yourself on whether you retained it. Its not just experience, its different experiences as we cant be every where and do everything. Best way to get this is to read.. Keep learning.</p><p>You won’t see the payoff for long time. Then one day someone will ask you how you saw it, and you won’t have a good answer either but somehow you can just see through the problem without knowing.</p><h3><strong>References</strong></h3><p><strong>The clusters (Chunking)</strong></p><ul><li>Chase, W. G., &amp; Simon, H. A. (1973). Perception in Chess. <em>Cognitive Psychology</em>, 4(1), 55–81. <a href="https://andymatuschak.org/prompts/Chase1973.pdf">PDF</a> · <a href="https://doi.org/10.1016/0010-0285(73)90004-2">DOI</a> The original chunking study. Masters reconstruct real board positions far better than novices, and random positions no better at all.</li></ul><p><strong>The overlap (Analogy and Structure)</strong></p><ul><li>Gentner, D. (1983). Structure-Mapping: A Theoretical Framework for Analogy. <em>Cognitive Science</em>, 7(2), 155–170. <a href="https://groups.psych.northwestern.edu/gentner/papers/Gentner83.2b.pdf">PDF</a> · <a href="https://doi.org/10.1207/s15516709cog0702_3">DOI</a> Why experts map across domains by deep structure while novices map by surface features. This is the write-ahead-log point, formalized.</li><li>Koestler, A. (1964). <em>The Act of Creation.</em> The bisociation argument: insight as the collision of two frames that were each already coherent.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=fe1cfe0dbb86" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Trino On Yarn: Maximizing Resource Utilization.]]></title>
            <link>https://medium.com/@Iqbalkhattra85/trino-on-yarn-maximizing-resource-utilization-b71625596d20?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/b71625596d20</guid>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[trinos]]></category>
            <category><![CDATA[yarn]]></category>
            <category><![CDATA[big-data-analytics]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Fri, 01 Sep 2023 02:15:55 GMT</pubDate>
            <atom:updated>2023-09-01T02:15:55.339Z</atom:updated>
            <content:encoded><![CDATA[<h3>Containerization</h3><p>Containerization of applications is a well-known concept in the software world, Everyone knows the flexibility and isolation provided by containerizing an application. In the Data world, There are many orchestration tools available today Kubernetes, Mesos &amp; YARN are the most adopted ones.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/547/1*cSY4Le3dHAImbf4At6rtZA.png" /><figcaption>www.dataopensource.com</figcaption></figure><p>Today, our focus will be on YARN containers. Apache Hadoop YARN was originally developed to execute isolated Hadoop data-intensive Java processes. In version 3.0, Docker support was added. YARN is a centralized resource manager that utilizes a capacity queue model to segregate resources among different tenets. Each tenet or team is assigned a certain percentage quota of the cluster with a predefined priority, ensuring resource availability for everyone. Hadoop is widely used in organizations for on-premises clusters, and there are numerous SQL engines that have native Hadoop support. Compared to Kubernetes, YARN has a simpler architecture with a straightforward learning curve. However, as the storage and processing separation model has made a comeback in the industry, many organizations are migrating from Hadoop to Kubernetes for heavy data processing due to its flexibility and native cloud support.</p><h3><a href="https://hadoop.apache.org/docs/r3.1.1/hadoop-yarn/hadoop-yarn-site/yarn-service/YarnServiceAPI.html">YARN Service API</a></h3><p>The YARN Service API provides a way to manage and deploy long-running services on a Hadoop cluster.It is designed to make it easier to run and manage services that need to be highly available, scalable, and fault-tolerant stateful or stateless services. its design provides a declarative way to define services using a YAML-based specification file that includes information such as the service name, number of instances and the dependencies. it provides a way to manage the life cycle of the services, including starting, stopping, scaling, automatic fail over, rolling upgrades, and integration with Hadoop’s security and monitoring systems</p><p><strong><em>Service Definition</em></strong> : A YAML-based specification file that describes the service and its properties, including the service name, the number of instances, the resource requirements, dependencies, and other details.</p><p><strong><em>Service Instance</em></strong> : A running instance of a service, which is created based on the service definition. A service can have multiple instances, which can be scaled up or down based on demand.</p><p><strong><em>Component</em></strong> : A component is a single process or container that runs as part of a service instance. A service can have multiple components, each with its own resource requirements.</p><p><strong><em>Resource Profile</em></strong> : A set of resource requirements for a service instance or component, including CPU, memory, and disk space. Resource profiles are used to allocate resources and ensure that a service instance or component has the necessary resources to run.</p><p><strong><em>Placement Constraints</em></strong> : Rules that define where a service instance or component can be placed within a cluster. Placement constraints can be used to ensure that services are distributed across nodes in a balanced way.</p><p><strong><em>Service Lifecycle</em></strong> : The lifecycle of a service, including starting, stopping, upgrading, and scaling. The Service API provides methods for managing the service lifecycle, including APIs for creating, updating, and deleting services, and for scaling service instances up or down.</p><h3><a href="https://trino.io/docs/current/overview.html">Trino</a></h3><p>Trino is a distributed SQL query engine designed to query large data sets distributed over one or more heterogeneous data sources. It is based on the JVM model and can run bare metal or use any common resource manager such as K8S or YARN. Trino Does not have native support for Yarn Resource Manager and also, YARN service API is still in GA mode as of Hadoop3.3; This article is for knowledge purposes only. Please take due diligence before implementing this solution in production.</p><p>Trino is a very lightweight java SQL engine based on the Controller &amp; Worker architecture same as Presto.</p><h4>Coordinator</h4><p>The Coordinators server works as the controller for the whole Trino cluster it accepts a user query, parse and analyzes the sql syntax, Create an execution plan and schedule it on the workers for execution. Coordinator in itself is a very light process, and in production systems, it is not recommended for any heavy lifting of data processing. All end users connect to the coordinator for the query execution.</p><h4>Discovery URI</h4><p>The coordinator process also contains another endpoint which is used by all the worker nodes to share a heartbeat every x seconds across the cluster. It helps the coordinator process to make intelligent scheduling decisions during the execution of a user request. The coordinator will not schedule a query on a node where the last heartbeat is older than a defined threshold. The Discovery server also publishes the total cluster worker status to all the nodes, which help in fault toleration in case of a worker process dies.</p><h4>Worker</h4><p>The worker process performs dumb query execution job as defined and scheduled by the coordinator on the cluster. The worker picks up a data split and processes it based on the pre-defined execution plan, and reports back the results to the coordinator.</p><h3>YARN Service Demo</h3><p>We will be launching the Trino service on YARN using the json file. the flow will be as below.</p><h4>Coordinator Deployment:</h4><ul><li>A Coordinator &amp; Discovery server endpoint outside the YARN cluster so we can have a dedicated controller node to avoid any noisy neighbour scenario, we can add the coordinator service on YARN containers as well but it will be impacted by the running YARN services load and also as YARN does not provide and disc or network level separation, We might end up impacting other running loads on YARN due to heavy Trino User QPS.</li><li>Download the artifacts for Trino and add the required configuration files based on the instructions on this session <a href="https://trino.io/docs/current/installation/deployment.html#configuring-trino">here</a> !</li><li>Assign a dedicated node.id=ffffffff-ffff-ffff-ffff-ffffffffffff as we will not be changing this one in near future, It is better to use meaning full value as it will show up in our Trino UI.</li><li>We can assign discovery.uri=localhost:8085 as discovery end point will be running on same machine, Please make sure we have not changed the server port else use the new defined port in place of 8085.</li><li>Launch the Coordinator using the launcher command and you can validate the deployment by going to &lt;coordinator_hst:8085&gt; url. Trino UI should be up and there should be no worker nodes on the cluster at this point of time.</li></ul><h4>Worker Deployment (YARN)*</h4><p>Worker Deployment will be little complex as we need to create and Artifact of the worker node and ship it to the YARN service, We will be using the tarball artifact type for the demo. YARN also support docker type though we need to install docker executable on all the worker nodes.</p><ul><li>Download the Trino Base Artifacts and set up the etc configuration directory by following the instructions given on the Trino deployment page.</li><li>Create require node.data.dir=/var/trino/data on all the worker/data nodes on the cluster that have active node managers and where we are expecting to run our Trino workers. You can choose to create only on a specific set of nodes as well if you are planning to use <a href="https://hadoop.apache.org/docs/stable/hadoop-yarn/hadoop-yarn-site/NodeLabel.html">YARN node Labels</a> for Trino deployment.</li><li>Once all files are created, generate a new tar file using tar -cvzf trino-server-&lt;version&gt;.tar.gz &lt;Trino Directory&gt; , we will use this trino artifact to launch the Trino on YARN.</li><li>Create a YARN App launcher file as below.</li><li>yarn app -launch &lt;app-name&gt; yarn-trino-demo.json</li></ul><h3>yarn-trino-demo.json</h3><pre>{<br>  &quot;name&quot; : &quot;trino-yarn-demo&quot;,<br>  &quot;version&quot; : &quot;1.0.0&quot;,<br>  &quot;artifact&quot; : {<br>    &quot;id&quot;: &quot;&lt;hdfs_path_Artifact&gt;/trino-server-412.tar.gz&quot;,<br>    &quot;type&quot; : &quot;TARBALL&quot;<br>  },<br>  &quot;resource&quot; : {<br>    &quot;cpus&quot; : 4,<br>    &quot;memory&quot; : &quot;12288&quot;<br>  },<br>  &quot;configuration&quot; : {<br>    &quot;properties&quot; : {<br>      &quot;yarn.service.container-failure-per-component.threshold&quot;: 1000,<br>      &quot;yarn.service.am-resource.memory&quot;: 3072<br>    }<br>  },<br>  &quot;lifetime&quot; : -1,<br>  &quot;components&quot; : [ {<br>    &quot;name&quot; : &quot;worker&quot;,<br>    &quot;dependencies&quot; : [ ],<br>    &quot;readiness_check&quot; : {<br>      &quot;type&quot; : &quot;HTTP&quot;,<br>      &quot;properties&quot; : {<br>        &quot;url&quot; : &quot;http://localhost:8085/v1/info&quot;<br>      }<br>    },<br>    &quot;launch_command&quot; : &quot;./conf/entrypoint.sh&quot;,<br>    &quot;resource&quot; : {<br>      &quot;cpus&quot; : 4,<br>      &quot;memory&quot; : &quot;12288&quot;,<br>      &quot;additional&quot; : { }<br>    },<br>    &quot;placement_policy&quot;: {<br>      &quot;constraints&quot;: [<br>        {<br>          &quot;type&quot;: &quot;ANTI_AFFINITY&quot;,<br>          &quot;scope&quot;: &quot;NODE&quot;,<br>          &quot;target_tags&quot;: [<br>            &quot;worker&quot;<br>          ]<br>        }<br>      ]<br>    },<br>    &quot;number_of_containers&quot; : 10,<br>    &quot;run_privileged_container&quot; : false,<br>    &quot;configuration&quot; : {<br>      &quot;env&quot; : {<br>        &quot;JAVA_HOME&quot; : &quot;/opt/java17/jdk-17.0.4.1&quot;,<br>        &quot;POTENTIAL_PATH&quot; : &quot;$JAVA_HOME/bin:$PATH&quot;,<br>        &quot;HADOOP_HOME&quot; : &quot;$PWD/lib/hadoop&quot;,<br>        &quot;HADOOP_YARN_HOME&quot; : &quot;$PWD/lib/hadoop&quot;,<br>        &quot;CONTAINER_CONFS&quot; : &quot;$PWD/conf&quot;,<br>        &quot;TRINO_ETC&quot; : &quot;$TRINO_HOME/etc&quot;,<br>        &quot;TRINO_ETC_CATALOG&quot; : &quot;$TRINO_ETC/catalog&quot;,<br>        &quot;TRINO_CONFIG_PROP_NAME_BEFORE&quot; : &quot;trino-config.properties&quot;,<br>        &quot;TRINO_CONFIG_PROP_NAME&quot; : &quot;config.properties&quot;,<br>        &quot;PORT&quot;: &quot;8085&quot;<br>      },<br>    &quot;files&quot; : [{<br>        &quot;type&quot; : &quot;PROPERTIES&quot;,<br>        &quot;dest_file&quot; : &quot;trino-node.properties&quot;,<br>        &quot;properties&quot; : {<br>          &quot;node.environment&quot;: &quot;poc&quot;,<br>          &quot;node.id&quot;: &quot;${CONTAINER_ID}&quot;,<br>          &quot;node.data-dir&quot;: &quot;/data/trino-yarn/data&quot;<br>        }<br>      },{<br>        &quot;type&quot; : &quot;TEMPLATE&quot;,<br>        &quot;dest_file&quot; : &quot;entrypoint.sh&quot;,<br>        &quot;src_file&quot; : &quot;/user/hubble-poc/trino-confs-single-worker/entrypoint.sh&quot;,<br>        &quot;properties&quot; : {}<br>      }]<br>    },<br>    &quot;restart_policy&quot; : &quot;ALWAYS&quot;<br>  }]<br>  },<br>  &quot;queue&quot; : &lt;yarn-queue&gt;,<br>  &quot;kerberos_principal&quot; : { }<br>}</pre><p><em>*might need some changes as the file is not tested, provided for example</em></p><h3>entrypoint.json</h3><ul><li>We also need an entrypoint.sh file in the directory where we are launching the yarn app locally.</li></ul><pre>#!/bin/bash<br>#Add variables for trino.<br>mkdir -p $TRINO_ETC_CATALOG<br>cp $CONTAINER_CONFS/trino-catalog-hive.properties $TRINO_ETC_CATALOG/hive.properties<br>cp $CONTAINER_CONFS/trino-catalog-iceberg.properties $TRINO_ETC_CATALOG/iceberg.properties<br>cp $CONTAINER_CONFS/trino-catalog-mysql.properties $TRINO_ETC_CATALOG/mysql.properties<br>cp $CONTAINER_CONFS/trino-jvm.config $TRINO_ETC/jvm.config<br>cp $CONTAINER_CONFS/trino-node.properties $TRINO_ETC/node.properties<br>cp $CONTAINER_CONFS/trino-config.properties $TRINO_ETC/config.properties<br>cp $CONTAINER_CONFS/resource-groups.properties $TRINO_ETC/resource-groups.properties<br>#Set path to Java17+ manually as Trino need Java 17+ and hadoop runs on Java 8, <br>#we need to manually push the java17 to all worker nodes to a specific directory <br>#and set path at the time of launching trino. <br>export PATH=$JAVA_HOME/bin:$PATH<br>java -version<br>echo &quot;Check Java Version...&quot;<br>#cat $PWD/conf/trino-node.properties<br>#cat $PWD/conf/trino-node-test2.properties<br>## Launch Trino in the YARN Container. <br>$PWD/lib/trino-server-415/bin/launcher run</pre><ul><li>SSH into your yarn client machine and Once all the files are created launch the yarn app with command yarn app -launch &lt;app-name&gt; yarn-trino-demo.json , Make sure you have both above files in the same direcotry where you are launching the app.</li></ul><h3>Trino UI</h3><ul><li>Open the Coordinator server on port 8085 you will see the UI with active worker count as per out requirement of 10 containers in the launch file.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*B22gTQE_4prjepCK" /></figure><p>YARN Service API is still in GA mode and it is getting better with the new releases, Running Trino on K8s is another good idea how ever with Hadoop we will be sending the user queries to the data and it is very useful when you are scanning peta bytes of data every day as Data locality help to minimize the data shuffling across the network.</p><p>Please feel free to try it out and feel free to share if any questions. Thanks for reading.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b71625596d20" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Apache Spark — “User Defined Functions” are not Bad]]></title>
            <link>https://medium.com/@Iqbalkhattra85/apache-spark-user-defined-functions-are-not-bad-c4f7e29fefd1?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/c4f7e29fefd1</guid>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[apache-spark]]></category>
            <category><![CDATA[udaf]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[spark]]></category>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Mon, 27 Dec 2021 15:30:06 GMT</pubDate>
            <atom:updated>2022-03-23T13:42:17.801Z</atom:updated>
            <content:encoded><![CDATA[<h3>Apache Spark — “UDAF/UDF User Defined Functions” are not Bad</h3><h4>Automatic v/s Manual</h4><p>There are two types of people in this world, one who like everything ready to use and others who want to tailor everything according to the requirement. Both choices have their own pros and cons. <a href="https://minnesota.cbslocal.com/2016/10/11/report-only-18-percent-of-americans-can-drive-manual/">Only 18% of North Americans can actually drive a manual</a> and remaining prefer an automatic. The fun and control you get while driving a manual can’t be explained, I my self prefer driving a manual. It might be an over head in the start but once you get hold of it, it is way better in comparison to an automatic whether you are going for the acceleration or the economy except you are driving a Tesla :) .</p><h4>User defined Aggregate Functions</h4><p>Apache Spark’s data processing user defined aggregator functions falls under the same tailor made manual category, I have read many articles where the focus was more on pushing engineers away from them but in real UDF’s/UDAF’s are not bad they are just complex. One cannot use them in any scenario but there are use cases where a user defined function can give you huge performance boost with little bit tweaking. User functions are black box for the spark optimizer and it is unable to optimize the code that is the main concern behind avoiding them.</p><blockquote><em>Today I am sharing a use case where using a UDAF saved almost 50% of the processing overhead by decreasing the memory footprint for the complex business logic for us by 90%. We dropped the shuffle of a 10TB per hour job to gb’s.</em></blockquote><h3><strong>Scenario</strong></h3><p>We are grabbing 11 Trillion events per day for particular use case or 120Mil per second and the data event are nested into a single fields aligned by 1:M data structure. Any job or frame work can give you challenges on that scale and this job was my personal favourite from long time. We optimized the logic twice in past once by changing the data model for input and second time moving away from Spark API to Spark UDF. it almost cut down the job by 30% but still it was missing something.</p><h4>Spark SQL API</h4><p>In a perfect world any engineer would love to use Spark SQL API and the solution will look something like below.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6043af9489d6103b9f6a547f1c41dbe1/href">https://medium.com/media/6043af9489d6103b9f6a547f1c41dbe1/href</a></iframe><h3><em>Output</em></h3><pre>// inputDF.show(false)<br>+---+---------------+<br>|key|list_data      |<br>+---+---------------+<br>|k1 |[A, B, C, D]   |<br>|k1 |[A, B, C, D, E]|<br>|k2 |[F, G, H, I]   |<br>|k2 |[G, H, I]      |<br>|k2 |[F, H, I]      |<br>|k3 |[J, K, L, M, N]|<br>|k3 |[J, K, M]      |<br>|k1 |[B, C, E]      |<br>+---+---------------+<br><br>  <br>println(&quot;Input DF Count : &quot; + inputDF.count())<br>Input DF Count : 8</pre><pre>println(&quot;Exploded dataframe Count: &quot; + explodedDF.count())<br>Exploded data frame Count: 30</pre><pre>// Check the output. <br>result.show(20,false)<br>+---+----------+-----+<br>|key|list_value|count|<br>+---+----------+-----+<br>|k1 |A         |2    |<br>|k1 |B         |3    |<br>|k1 |C         |3    |<br>|k1 |D         |2    |<br>|k1 |E         |2    |<br>|k2 |F         |2    |<br>|k2 |G         |2    |<br>|k2 |H         |3    |<br>|k2 |I         |3    |<br>|k3 |J         |2    |<br>|k3 |K         |2    |<br>|k3 |L         |1    |<br>|k3 |M         |2    |<br>|k3 |N         |1    |<br>+---+----------+-----+</pre><h4>Spark UDAF</h4><p>Solution using spark User defined Aggregator function is bit complex compared to above but it is way better when you dealing with large scale data. The catch is we dont need to explode a column we will write a custom function to aggregate the list datatype and return a Maptype with key as the list element and the value as the count of that element occurred in whole data set. We will not explode the data set.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/33b66572de170abd2a0b58b6fe7cedfc/href">https://medium.com/media/33b66572de170abd2a0b58b6fe7cedfc/href</a></iframe><h3><em>Output</em></h3><pre>// inputDF.show(false)<br>+---+---------------+<br>|key|list_data      |<br>+---+---------------+<br>|k1 |[A, B, C, D]   |<br>|k1 |[A, B, C, D, E]|<br>|k2 |[F, G, H, I]   |<br>|k2 |[G, H, I]      |<br>|k2 |[F, H, I]      |<br>|k3 |[J, K, L, M, N]|<br>|k3 |[J, K, M]      |<br>|k1 |[B, C, E]      |<br>+---+---------------+<br><br>  <br>println(&quot;Input DF Count : &quot; + inputDF.count())<br>Input DF Count : 8</pre><pre>println(&quot;Aggregated dataframe Count: &quot; + AggregatedDF.count())<br>Exploded data frame Count: 3</pre><pre>// Check the output. <br>result.show(20,false)<br>+---+----------+-----+<br>|key|list_value|count|<br>+---+----------+-----+<br>|k1 |A         |2    |<br>|k1 |B         |3    |<br>|k1 |C         |3    |<br>|k1 |D         |2    |<br>|k1 |E         |2    |<br>|k2 |F         |2    |<br>|k2 |G         |2    |<br>|k2 |H         |3    |<br>|k2 |I         |3    |<br>|k3 |J         |2    |<br>|k3 |K         |2    |<br>|k3 |L         |1    |<br>|k3 |M         |2    |<br>|k3 |N         |1    |<br>+---+----------+-----+</pre><h3>Conclusion</h3><p>If you run the code and look at the intermediate results in both the approaches the <br>- Spark SQL API explodes 8 records -&gt; 30 records -&gt; 14 records<br>- Spark UDAF aggregate the 8 records -&gt; 3 records -&gt; 14 records</p><h4><em>Intermediate results</em></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4tB8CEppIa5cl89PPoxBTw.png" /></figure><p>We can see we dropped the memory footprint for the Spark stage by <em>90% </em>Spark SQL API is simple and saves a lot of time for engineers but in some use cases UDF’s and UDAF’s can change the resource requirement for your job drastically.</p><p>Please feel free to let me know what you think about using UDAF’s and if you need any help. The above example is based on UserDefinedAggregateFunction class which is deprecated in Spark3.0. I will post a example based on the Aggregator class soon. Thanks for reading !!!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c4f7e29fefd1" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Re: dash — Python Data Source]]></title>
            <link>https://medium.com/geekculture/re-dash-python-data-source-e5200a863cb2?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/e5200a863cb2</guid>
            <category><![CDATA[github]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[redash]]></category>
            <category><![CDATA[api]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Wed, 18 Aug 2021 16:37:11 GMT</pubDate>
            <atom:updated>2021-08-19T18:36:51.253Z</atom:updated>
            <content:encoded><![CDATA[<h3>Re: dash — Python Data Source</h3><p>Redash is an open-source free BI tool that users can easily set up on their machine using docker. It is very lightweight and easy to use for analyzing data across multiple data sources. Redash <em>v10-preview</em> version is out and you can try it using the below tutorial.</p><p><a href="https://medium.com/@Iqbalkhattra85/re-dash-open-source-bi-tool-a27fb26b3072">Re:dash — Open Source BI tool</a></p><p>Redash comes out of the box with 20+ data sources, it can connect to any open source or cloud database easily by using the data source UI. However, there are some other data sources available in Redash that can be used to pull data from URLs or APIs. These data sources are not enabled by default and are also recommended to be used on personal systems only due to security reasons.</p><p>We will be discussing on <strong><em>How to use Python as a data source</em></strong> to achieve it in this article.</p><p><a href="https://redash.io/help/data-sources/querying/supported-data-sources">Supported Data Sources</a></p><h3>Use Case</h3><p>As engineers or analysts for ad-hoc data analysis, we often have to add a decoding layer on top of our data before sharing or presenting it on an end-user dashboard. For example, a city_id = 1 will never make sense to anyone viewing the dashboard or a complex visualization. Adding the mappings or dimensions data set to the back-end data source is not a good design option in such cases as the data set will no longer be needed after the analysis.</p><p>Secondly, there will be an extra overhead of adding and removing the data set from a production system which can take days to weeks depending on the schedule of your data team, who are most keen to avoid one-off requests :)</p><h3>Python Data Source</h3><p>In such a scenario, We need a data source that can read the data from an external data source and also has the flexibility to manipulate the result set to join with other data queries.</p><p>Python data source in redash gives the users this flexibility to query the external URL/APIs and pull the one-off decode files for ad-hoc analysis. The current data source does not support global python functions but it gives a wide range of functionality through some predefined functions, default print function is also available for easy debugging of the scripts.</p><h4><strong>How to enable?</strong></h4><p><strong>By default</strong> python is not available as a data source, users need to update the redash.env file and add the below line while setting up the Redash instance. this option is only available in the self-hosted instances or users can check with their Redash admin team for enabling it.</p><pre>REDASH_ADDITIONAL_QUERY_RUNNERS=redash.query_runner.python</pre><blockquote>If you have a redash instance already running, please restart your celery workers after setting the parameter.</blockquote><h4>How to Use?</h4><p>Redash requires every query to return a dict type result object in python for visualizing the records on a table or graph format. All the built-in query runner classes for databases return a dictionary object for every query and the format looks like below.</p><p><strong>Result object format :</strong></p><pre>result = {<br>    &quot;rows&quot;: [<br>        {&quot;id&quot;: 1, &quot;value&quot;: &quot;s2f1&quot;},<br>    ],<br>    &quot;columns&quot;: [<br>        {<br>            &quot;name&quot;: &quot;id&quot;,<br>            &quot;friendly_name&quot;: &quot;id&quot;,<br>            &quot;type&quot;: TYPE_INTEGER,<br>        },<br>        {<br>            &quot;name&quot;: &quot;value&quot;,<br>            &quot;friendly_name&quot;: &quot;value&quot;,<br>            &quot;type&quot;: TYPE_STRING,<br>        }<br>    ]<br>}</pre><p>Every Result Object contains two keys</p><ul><li>rows key contains the list of n number output records for the query. Each row element can contain n number of columns in the output. Each row element has to be in JSON format.</li><li>columns key contains the schema for the records. Each schema field has to have two values name &amp; type of the field. Python query runner comes with predefined data types for the result object, mentioned below</li></ul><pre># Supported data types<br>- TYPE_DATETIME                                   <br>- TYPE_BOOLEAN                                  <br>- TYPE_INTEGER                                   <br>- TYPE_STRING                                   <br>- TYPE_DATE                                   <br>- TYPE_FLOAT</pre><p>So in the case of python data sources, users have to format the output of their python script and return a formatted result object.</p><p><strong>Functions:</strong></p><p>Python query runners also support a group of functions for reading, updating result objects from other queries. Users can read the output of other queries and can join the output from multiple queries using python.</p><pre>## Return the result Object for a predefined query<br>get_query_result(&lt;Redash Query ID&gt;):                               </pre><pre>## Print Table Schema Information for a data source  <br>get_source_schema(&lt;Redash Data Source Name or ID&gt;)  <br>                                <br>## Execute a query and return result object against a data source in ## python<br>execute_query(&lt;Redash Data Source Name or ID&gt;, &lt;SQL QUERY&gt;)                                  </pre><pre>## Add new column to the result object<br>add_result_column(&lt;result object&gt;, &lt;field_name&gt;, &lt;field_name_for_displauy&gt;, &lt;field_datatype&gt;)</pre><pre>## Add a new row to the result object<br>add_result_row(&lt;result object&gt;, &lt;values&gt;)</pre><h3>Example</h3><p>We will pull a file from a URL and will create a data set in redash to use the file as a mapping file.</p><ul><li>Create the Python Data Source &amp; Add all the modules you need for your script.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/651/1*ny2m5p4ElaYBBLD2gEQXPA.png" /><figcaption>Python Data Source Configuration</figcaption></figure><ul><li>Create a Python Script and pull the file from the git hub.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/734/1*--vYw20eI3UrFJJTJVBCqg.png" /><figcaption>Python Snippet for URL file</figcaption></figure><ul><li>Execute the script and get the output. it will print all the logging information we added using the print function with timestamps and it can help you to capture the time taken on each step.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/625/1*DIud0KECfg0Ma0oPB8Qogw.png" /><figcaption>Output.</figcaption></figure><p><strong>Functions Example</strong></p><ul><li>Getting results of an old query and adding a new record to the result set.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/687/1*Ov2SsBXLaQ_Nbs6o2djMgQ.png" /></figure><p>Python data source is not limited to pulling the data from URL or APIs only, it can be used in many ways. it will not feasible to list all of them under the scope of this article. Please try out all the in-built functions and let me know if you face any issues. you can get the code for the examples on the below link, I will try to add more stuff in future.</p><h3>Thanks for reading, Have a nice time !!</h3><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e5200a863cb2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/geekculture/re-dash-python-data-source-e5200a863cb2">Re: dash — Python Data Source</a> was originally published in <a href="https://medium.com/geekculture">Geek Culture</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Re:dash — Open Source BI tool]]></title>
            <link>https://medium.com/@Iqbalkhattra85/re-dash-open-source-bi-tool-a27fb26b3072?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/a27fb26b3072</guid>
            <category><![CDATA[postgres]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[bi]]></category>
            <category><![CDATA[open-source]]></category>
            <category><![CDATA[redash]]></category>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Tue, 17 Aug 2021 18:59:25 GMT</pubDate>
            <atom:updated>2021-08-17T18:59:25.052Z</atom:updated>
            <content:encoded><![CDATA[<h3>Re:dash — Open Source BI tool</h3><p>Old days of querying the data on a black terminal or pulling into an Excel sheet for analysis are long gone. Data visualization (data viz) has changed the way we look into data. It is way easier to make sense out of numerous data by mapping the data points with graphical elements and there are tons of tools available in the market.</p><h3><strong>Redash</strong></h3><p>is an open-source tool for querying and visualizing the data using SQL queries. Redash</p><ul><li>Comes with a number of graphical visualizations out of the box.</li><li>Support 20+ different data sources.</li><li>Supports data joins across multiple data sources.</li><li>Can be self hosted using the Docker Compose or K8s Helm charts.</li><li>Supports SAML authentication for single sign-on for users.</li><li>Is very flexible as users can add new visualizations to extend the tool set if required by their project.</li></ul><p><a href="https://github.com/getredash/redash">GitHub - getredash/redash: Make Your Company Data Driven. Connect to any data source, easily visualize, dashboard and share your data.</a></p><p><em>We will be looking into Redash v10-preview version in this article.</em></p><h3>Pre- Requisite</h3><ul><li>Docker Compose is required on the machine.</li><li>Remote PostgreSQL database is required for Re:dash state management.</li></ul><h3>Docker-Compose</h3><p>You can set up re-dash easily in a docker containers. I will go through the details for setting up the docker container for re-dash.</p><ul><li>I am using external PostgreSQL DB installation on a remote server, to keep the state intact after the containers are down.</li><li>You will need to JDBC connection String in below format for connecting to postgres database. (Update in the redash.env file)</li></ul><pre>postgresql://&lt;username&gt;:&lt;pwd&gt;@&lt;host&gt;/&lt;database&gt;</pre><ul><li>Put both compose and env file in same directory.</li></ul><h4><strong>Compose File</strong></h4><pre># This configuration file is for the **development** setup.<br># For a production example please refer to getredash/setup repository on GitHub.</pre><pre>version: &quot;2.2&quot;<br>x-redash-service: &amp;redash-service<br>  image:<br>    redash/redash:preview<br>  env_file:<br>    &quot;redash.env&quot;</pre><pre>services:<br>  server:<br>    &lt;&lt;: *redash-service<br>    command: server<br>    depends_on:<br>      - redis<br>    ports:<br>      - &quot;5000:5000&quot;<br>      - &quot;5678:5678&quot;<br>    environment:<br>      REDASH_WEB_WORKERS: 4<br>  scheduler:<br>    &lt;&lt;: *redash-service<br>    command: scheduler<br>    environment:<br>      QUEUES: &quot;celery&quot;<br>      WORKERS_COUNT: 1<br>  scheduled_worker:<br>    &lt;&lt;: *redash-service<br>    command: worker<br>    environment:<br>      QUEUES: &quot;scheduled_queries&quot;<br>      WORKERS_COUNT: 1<br>  adhoc_worker:<br>    &lt;&lt;: *redash-service<br>    command: worker<br>    environment:<br>      QUEUES: &quot;queries,schemas&quot;<br>      WORKERS_COUNT: 2</pre><pre>  redis:<br>    image: redis:latest<br>    restart: unless-stopped<br>  nginx:<br>    image: nginx:latest<br>    ports:<br>      - &quot;5001:80&quot;<br>    depends_on:<br>      - server<br>    links:<br>      - server:redash<br>    restart: always</pre><h3>redash.env</h3><pre>REDASH_HOST=<a href="http://localhost/redash">http://localhost/redash</a><br>PYTHONUNBUFFERED=0<br>REDASH_LOG_LEVEL=INFO<br>REDASH_REDIS_URL=redis://redis:6379/0<br>REDASH_COOKIE_SECRET=redash-selfhosted<br>REDASH_SECRET_KEY=redash-selfhosted<br>REDASH_DATABASE_URL=postgresql://&lt;username&gt;:&lt;pwd&gt;@&lt;host&gt;/&lt;database&gt;</pre><ul><li>Put both docker compose and redash.env files in the same directory.</li><li>Create DB schema for the redash state &amp; Start the docker containers.</li></ul><pre>docker-compose run --rm server create_db<br>docker-compose run up -d</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/775/1*nhX7_AtVNBcalV2KpdVb6g.png" /><figcaption>Starting up the Docker containers</figcaption></figure><ul><li>Go to <a href="http://localhost:5000/">http:localhost:5000</a> &amp; create a default user.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/795/1*smfPS9EFKlXqess_CcYGbQ.png" /></figure><h3>User Interface</h3><p>Redash is built around the concept of <strong>Queries</strong> and <strong>Dashboards</strong>. Users can create SQL queries &amp; create visualizations. Once the user has finalized the query it can be added to a dashboard. Multiple queries can be added to a single dashboard.</p><p>Home page lists the user bookmarked dashboards and queries for quick access.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hvx1errrezt3uR6cLuxyew.png" /><figcaption>Redash Home Page.</figcaption></figure><ul><li>Create the <strong>Queries</strong> and <strong>Dashboards</strong> by following the links on the home page.</li></ul><h3>Demo Dashboard</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/864/1*6l8jCQOf6XJQEUWGc4M03A.png" /></figure><p>Thanks for reading, please feel free to reach out to me if you have any questions.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=a27fb26b3072" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[PXE Boot — Installing OS like a Pro.]]></title>
            <link>https://medium.com/@Iqbalkhattra85/pxe-boot-installing-os-like-a-pro-af0582756128?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/af0582756128</guid>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Sun, 06 Sep 2020 14:20:47 GMT</pubDate>
            <atom:updated>2020-09-06T14:29:21.242Z</atom:updated>
            <content:encoded><![CDATA[<h3>PXE Boot — Installing RedHat OS like a Pro.</h3><p>Covid-19 has changed a lot of things in the world and working remotely is one of the major changes that happened to the IT industry. Offices were closed, for getting anything done in the office or to fix some machines you have to work with IT, which can take weeks some time.</p><p>While WFH , I picked up a project to set up an R&amp;D K8s Cluster and the biggest challenge was to install the operating system on these machines remotely. I have worked a lot on setting up Hadoop, Mesos clusters in past but never worked on PXE booting the machines remotely for those projects.</p><p>Always loved working on new technologies and started working on this project, learned along the way and set up the OS in 2 weeks&#39; time. I am sharing the details on how to set up OS on a set of machines remotely.</p><h3>Tools</h3><p>One requires knowledge of the below tools/commands for setting up a machine, there are other options available as well for each one of them.</p><h3><strong>PXE boot</strong></h3><p><strong>Preboot eXecution Environment</strong> (<strong>PXE</strong>, most often pronounced as <em>pixie</em>) specification describes a standardized client-server environment that <a href="https://en.wikipedia.org/wiki/Booting">boots</a> a software assembly, retrieved from a network, on PXE-enabled clients. On the client-side, it requires only a PXE-capable network interface controller (NIC) and uses a small set of industry-standard network protocols such as DHCP and TFTP.</p><p>A high-level overview of the system calls for a network boot.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/684/1*Zwb4mV5P3MsfDFOwy0yrJw.png" /><figcaption>PXE boot</figcaption></figure><h3>kickstart</h3><p>Many system administrators set up multiple systems at a time and would prefer to use an automated installation method to install Red Hat Enterprise Linux on their machines. To answer this need, <strong>Red Hat</strong> created the kickstart installation method. Using kickstart, a system administrator can create a single file containing the answers to all the questions that would normally be asked during a typical installation.</p><ul><li>We can describe the disc formatting we need in the kick start file to avoid the tedious disc configuration set up during installation.</li><li>We can easily enable and configure the IP tables on the machines using a kickstart file for ssh and other port access on the machine.</li></ul><h3><strong>Cobbler</strong></h3><p>Cobbler is a Linux installation server that allows for rapid setup of network installation environments. It glues together and automates many associated Linux tasks so you do not have to hop between many various commands and applications when deploying new systems, and, in some cases, changing existing ones. Cobbler can help with provisioning, managing DNS and DHCP, package updates, power management, configuration management orchestration, and much more.</p><p><a href="https://cobbler.github.io/">Cobbler - Linux install and update server</a></p><p>Once the cobbler server is installed, you can download and mount the required OS iso file on your machine and can start the below commands.</p><p>There are three main concepts for installing the operating system using the Cobbler server. Distro, Profile and System</p><p><strong><em>Distro<br></em></strong>Distro command Add the operating system we want to install to the cobbler system. Any RedHat OS distribution can be imported into the cobbler system using the below commands.</p><p><strong><em>Profile</em></strong><br>Once the operating system distribution is set up, A OS profile needs to be set up for the installation process. A profile will attach a kickstart installation file to a distribution.</p><p><strong><em>System<br></em></strong>The last step is to add the client machine network IP and MAC address to the cobbler system so that during the next boot machine will send out a DHCP request for boot files over the network and the Cobbler system can authenticate the machine based on the details.</p><p><strong><em>Foreman</em></strong><em> is another tool that can be used instead of Cobbler and it is getting a lot of traction these days, it also comes with a nice and easy to access UI.</em></p><p><a href="https://theforeman.org/">https://theforeman.org/</a></p><h3>Ipmitool</h3><p>I was planning to install the operating system on very old machines and those machines had no tool as iDrac for accessing the kernel level properties remotely. I used Intelligent Platform Management Tool ipmitool for accessing the boot configurations on the machines and update the boot order to use network boot on the next restart(one time only).</p><p><a href="https://github.com/ipmitool">https://github.com/ipmitool</a></p><h3>Setting up the OS</h3><ul><li>Read the instructions and install the Cobbler server on a machine with the same network as the client machines.</li><li>Make sure you have network access on the root user for the Client OS machines.</li><li>Get the IP, Hostname and Mac Address for the client machine where the OS has to be installed.</li><li>for MAC address, if you use etho NIC (<strong><em>cat /sys/class/net/eth0/address)</em></strong></li><li>Install and set up ipmitool on the client machine for enabling the network boot option remotely.</li></ul><p><em>Disclaimer: Wiping a machine and installing a new Operating system will remove all the data from the machine and will also format the drives based on the </em><em>kickstart file configuration. Please test the process on a test server before rolling out in production also make sure to check the documentation for the tools you are using.</em></p><h4>Steps</h4><ol><li>Download and mount the required operating system distribution on the cobbler server. (for CenOS 8)</li></ol><pre>wget <a href="http://mirror.csclub.uwaterloo.ca/centos/8.1.1911/isos/x86_64/CentOS-8.1.1911-x86_64-dvd1.iso">http://mirror.csclub.uwaterloo.ca/centos/8.1.1911/isos/x86_64/CentOS-8.1.1911-x86_64-dvd1.iso</a></pre><pre># mkdir /mnt/iso/c8-1<br># mount -t iso9660 -o loop /root/isingh/iso/CentOS-8.1.1911-x86_64-dvd1.iso /mnt/iso/c8-1<br> <br># mount | grep iso<br>  /root/isingh/iso/CentOS-8.1.1911-x86_64-dvd1.iso on /mnt/iso/c8-1 type iso9660 (rw,loop=/dev/loop1)</pre><p>2. Import the Os into the cobbler system, add the distribution and check the report for the distribution details.</p><pre>cobbler import --path=/mnt/iso/c8-1 --name=CentOS-8.1.1911-x86_64</pre><pre>cobbler distro add \<br>--name=CentOS-8.1.1911-x86_64 \<br>--kernel=/var/www/cobbler/ks_mirror/CentOS-8.1.1911-x86_64/images/pxeboot/vmlinuz \<br>--initrd=/var/www/cobbler/ks_mirror/CentOS-8.1.1911-x86_64/images/pxeboot/initrd.img \<br>-—kopts=&quot;ksdevice= inst.repo=<a href="http://@@http_server@@/cblr/links/CentOS-7.3-DVD-x86_64">http://@@http_server@@/cblr/links/</a>CentOS-8.1.1911-x86_64&quot;</pre><pre>cobbler distro report --name=CentOS-8.1.1911-x86_64</pre><p>3. Add the cobbler profile for attaching a Kickstarter file to the OS distribution. Use the distro name created in the previous step.</p><pre>cobbler profile add \ <br>--name=TEST-OS-ROLLOUT <br>--distro=CentOS-8.1-DVD-x86_64  --kickstart=/var/lib/cobbler/kickstarts/reporting-k8s-c8.ks</pre><p>4. Set up the client system details in the Cobbler server and attach a profile to the system for DHCP call responses and run Cobbler sync to sync all the new configurations in the server.</p><pre>cobbler system add \ <br>--name=newmachine.a2z.com \<br>--hostname=newmachine.a2z.com \           &lt;= Client Hostname<br>--profile=TEST-OS-ROLLOUT \<br>--interface=eth0 \<br>--ip-address=192.192.192.192 \            &lt;= Client IP<br>--mac=10:2Z:1X:99:AA:CC \                 &lt;= Client Mac Address<br>--static=1 \<br>--netmask=255.252.0.0 \<br>--gateway=192.192.298.250 \</pre><pre>cobbler sync</pre><p>5. Log in to the Client machine, enable the pxe boot. Use ipmitool or any other inbuilt tool as iDRAC. For ipmitool</p><pre>## Run below command to enable pxe boot. <br>$ ipmitool chassis bootdev pxe<br>Set Boot Device to pxe</pre><pre>## ERROR: Could not open device at /dev/ipmi0 or /dev/ipmi/0 or /dev/ipmidev/0: No such file or directory<br>Unable to get Chassis Power Status</pre><pre>## If you get above error enable the kernel modules by running below commands and try again. <br>modprobe ipmi_devintf<br>modprobe ipmi_si</pre><p>6. Reboot the client machine and you will see below while the client machine reboots.</p><pre>shutdown -r now</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1001/1*TEHpWZEKQSMEfdVUgUFGrQ.png" /></figure><p><strong><em>OS installation can take 10–30 mins based on the configurations in the kick start file.</em></strong></p><p><em>Feel free to review and let me know if you find any issues. Thanks for reading.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=af0582756128" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Apache Spark Executor Memory Architecture]]></title>
            <link>https://medium.com/@Iqbalkhattra85/apache-spark-executor-memory-architecture-152ca15f575a?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/152ca15f575a</guid>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[apache]]></category>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Sun, 17 May 2020 22:27:41 GMT</pubDate>
            <atom:updated>2020-05-17T22:27:41.354Z</atom:updated>
            <content:encoded><![CDATA[<p>Apache Spark framework is a distributed processing framework for big data. It is based on the Map Reduce Algorithm for processing the big data workloads by shipping the processing logic to the data.</p><p>Spark reads all the data in the memory of an executor and performs the in-memory transformations, resulting in 10x faster processing compared to the old Hadoop map-reduce processing engine.</p><p>However, loading and processing the data in-memory for the bigger workloads is a pain and the famous Java OOM exception is common in memory-intensive spark jobs. Understanding Spark memory architecture is very important for optimizing the workloads in production.</p><h3>Memory Architecture</h3><p>Spark memory architecture consists of below components</p><ul><li><strong>Memory Manager</strong>: <em>It</em> <em>manages Spark’s overall memory usage within a JVM. This component implements the policies for dividing the available memory across tasks and for allocating memory between storage (memory used caching and data transfer) and execution (memory used by computations, such as shuffles, joins, sorts, and aggregations).</em></li><li><strong>Task Memory Manager</strong>: <em>It</em> <em>manages the memory allocated by individual<br>tasks. Tasks interact with TaskMemoryManager and never directly interact with the JVM-wide</em></li><li><strong>Memory Consumer</strong>: <em>These</em> <em>are clients of the TaskMemoryManager and<br>correspond to individual operators and data structures within a task. The TaskMemoryManager receives memory allocation requests from MemoryConsumers and issues callbacks to consumers in order to trigger spilling when running low on memory.</em></li><li><strong>Memory Pool:</strong> <em>Pools are a bookkeeping abstraction used by the Memory Manager to track the division of memory between storage and execution.</em></li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6j5JgB9tzF-ChCAV-NL1wQ.png" /><figcaption>Spark Memory Package Document Screenshot.</figcaption></figure><h3>Unified Memory Manager</h3><p>Spark 1.6+ used the new UnifiedMemoryManager for the memory architecture, However, it came with the backwards compatibility for the old memory manager StaticMemoryManager.</p><p>There are two implementations of org.apache.spark.memeory.MemoryManager which vary in how they handle the sizing of their memory pools:</p><p>- org.apache.spark.memory.UnifiedMemoryManager, the default in <strong>Spark 1.6+</strong>, enforces soft boundaries between storage and execution memory, allowing requests for memory in one region to be fulfilled by borrowing memory from the other.</p><p>- org.apache.spark.memory.StaticMemoryManager enforces hard boundaries between storage and execution memory by statically partitioning Spark’s memory and preventing storage and execution from borrowing memory from each other. This mode is retained only for legacy compatibility purposes.</p><p>We will be discussing the UnifiedMemoryManager in this article, <strong><em>In Spark 3.0, StaticMemoryManager support is removed.</em></strong></p><p>A MemoryManager that enforces a soft boundary between execution and storage such that either side can borrow memory from the other.</p><p><strong><em>Execution memory </em></strong><em>refers to that used for computation in shuffles, joins, sorts and aggregations.</em></p><p><strong><em>Storage memory</em></strong><em> refers to that used for caching and propagating internal data across the cluster. There exists one MemoryManager per JVM.</em></p><p><a href="https://github.com/apache/spark/blob/branch-2.2/core/src/main/scala/org/apache/spark/memory/UnifiedMemoryManager.scala">apache/spark</a></p><p>The region shared between execution and storage is a fraction of (the total heap space — 300MB) configurable through spark.memory.fraction (0.6).</p><p>The position of the boundary within this space is further determined by spark.memory.storageFraction(0.5). This means the size of the storage region is 0.6 * 0.5 = 0.3 of the heap space by default.</p><p>Storage can borrow as much execution memory as is free until execution reclaims its space. When this happens, cached blocks will be evicted from memory until sufficient borrowed memory is released to satisfy the execution memory request. Similarly, execution can borrow as much storage memory as is free.</p><p><strong><em>However, execution memory is never evicted by storage</em></strong> due to the complexities involved in implementing this. The implication is that attempts to cache blocks may fail if execution has already eaten up most of the storage space, in which case the new blocks will be evicted immediately according to their respective storage levels.</p><pre><em>/**<br> * Grow the execution pool by evicting cached blocks, thereby shrinking the storage pool.<br> * When acquiring memory for a task, the execution pool may need to make multiple<br> * attempts. Each attempt must be able to evict storage in case another task jumps in<br> * and caches a large block between the attempts. This is called once per attempt.<br> */</em></pre><pre>def maybeGrowExecutionPool(extraMemoryNeeded: Long): Unit = {<br>  if (extraMemoryNeeded &gt; 0) {<br>    <br>// There is not enough free memory in the execution pool, so try to reclaim memory from storage. We can reclaim any free memory from the storage pool. If the storage pool has grown to become larger than `storageRegionSize`, we can evict blocks and reclaim the memory that storage has borrowed from execution.</pre><pre>    val memoryReclaimableFromStorage = math.<em>max</em>(<br>      storagePool.memoryFree,<br>      storagePool.poolSize - storageRegionSize)</pre><pre>    if (memoryReclaimableFromStorage &gt; 0) {<br>      // Only reclaim as much space as is necessary and available:<br>      val spaceToReclaim = storagePool.freeSpaceToShrinkPool(<br>        math.<em>min</em>(extraMemoryNeeded, memoryReclaimableFromStorage))<br>      storagePool.decrementPoolSize(spaceToReclaim)<br>      executionPool.incrementPoolSize(spaceToReclaim)<br>    }<br>  }<br>}</pre><h3>Spark Executor JVM Structure:</h3><ul><li>Reserved Memory Bytes: By default, 300 MB is allocated to run the container by the spark. The value is hardcoded in the code using a constant and cannot be updated.</li><li>60% is assigned to both storage and execution memory by default.</li><li>(Container size -RESERVER_BYTES)-0.6 is assigned for the user memory for metadata and handling OOM errors.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/898/1*Sd5klQpJUyJ0o1J2ixzSPQ.png" /><figcaption>Apache Spark Executor memory Architecture</figcaption></figure><p><em>Spark Memory Managers implemented the separation of the JVM heap into two parts of storage and execution. Ols Static Memory Manager provided a lot of flexibility to the engineers for tuning the memory usage of a job and the transition to the current Unified Memory Manager changed the design to provide an abstraction for complex memory tunning to the JVM by dynamically changing the partition boundary between the storage and execution pools during run time.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=152ca15f575a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Optimize Spark Structured Streaming for Scale.]]></title>
            <link>https://medium.com/@Iqbalkhattra85/optimize-spark-structured-streaming-for-scale-d5dc5dee0622?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/d5dc5dee0622</guid>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[hadoop]]></category>
            <category><![CDATA[apache]]></category>
            <category><![CDATA[streaming]]></category>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Sat, 18 Apr 2020 15:36:35 GMT</pubDate>
            <atom:updated>2024-03-20T19:59:59.034Z</atom:updated>
            <content:encoded><![CDATA[<h3>Spark Metadata: Optimizing Spark Structured Streaming for Scale.</h3><p>Spark structured streaming production-ready version was released in spark 2.2.0. Our team was excited to test it on a scale, we updated one of our biggest jobs to streaming and pushed on production to process around 6–8 Terabytes of data every hour. We faced some challenges, looked into the design in detail and found some ways to optimize it.</p><p>I will discuss some low hanging fruits in this article, will focus on how you can speed up the micro-batch processing and how to speed up the read from the output of a structured streaming job.</p><h3>State Mechanism in Spark</h3><p><em>Please refer to my other article, if you are not aware of how spark saves the state in a structured streaming job</em>.</p><p><a href="https://medium.com/@Iqbalkhattra85/exactly-once-mechanism-in-spark-structured-streaming-7a27d8423560">Exactly Once Mechanism in Spark Structured Streaming</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kgVgacBOMuadW3tNQ7tMgg.png" /></figure><h3>Disclaimer</h3><p><em>These optimizations are done on the spark state saving mechanism and a small change can mess up the whole production job and data.</em></p><p><strong><em>Please go through all the details in spark documentation and also make sure you understand the mechanism before making any changes in production.</em></strong></p><h3>Optimizations</h3><ul><li>Commit Cleanup.</li><li>Explicit Metadata Reader.</li></ul><h3>Commit Cleanup</h3><p><a href="https://issues.apache.org/jira/browse/SPARK-24295">https://issues.apache.org/jira/browse/SPARK-24295</a></p><p>The streaming job saves the input and output batch information in checkpoint and commits files per batch. Streaming jobs run 24x7 and can run for years resulting in processing a large amount of data and the size of files grow with the time and can reach up to 10’s of GB’s.</p><p>Checkpointing and Commit file compaction are done on the driver for every 10 batches (starting from 0). It is a monolithic process and takes up to 10 mins based on the compact file size. It slows down the performance of the structured streaming job as while the driver is working on compacting the file, all the worker nodes don’t do anything and the job is wasting resources.</p><blockquote><em>Do we really need all the information stored in a compact file ??</em></blockquote><p>Compact files store the details of all the files processed and generated by the streaming job from the start date. Based on the retention of the production systems, some time the input files are only stored for a limited period and the same for the streaming job output files. Most of the production systems have retention on all the data sets in Hadoop File System.</p><h4>Scenario</h4><p>After running the streaming job for 5–6 months with 3000k files every 3 mins, we found that the streaming job is taking 8–10 mins for the compaction batch. We were not able to find the cause of slowness for the batch and we started digging into the spark metadata design. We found our compact file size has grown up to 6 GB and after every 10 batches, the streaming job was adding the delta of last 9 batches to this file and generating a new copy of it which resulted in wasting a lot of resources and job throughput was dropped to 50% for the compaction batch.</p><h4>Issue</h4><p>We found that the compact files on source and output commits has a list of 180 days of processed files and generated files. this decreased the streaming job throughput and also after a certain limit the streaming job started failing due to OOM on the driver and we have to increase the size to keep it running.</p><h4>Approach</h4><p>Compact files are in JSON format and also keeps track of the timestamps for the files with other information as well. We decided to set up a new job as to</p><ul><li>Kill the Spark streaming job once a week.</li><li>Kick a spark job to read in the latest .comapct JSON file for checkpointing and commits.</li><li>Filter out all the records for old files based on the retention period for the input and output files.</li><li>Save the old compact file for backup and for input/output file analysis.</li><li>Move the new shredded compact file to the spark checkpointing directory and commit directory. Keeping the same file structure and file name.</li><li>Make sure the spark streaming job is not running, while we are updating the metadata compact file.</li><li>Make sure to run clean up the compact files for both source and target metadata. <em>(refer to my previous article for understanding the mechanism)</em></li></ul><h4>Results</h4><ul><li>Shredding the compact file helped in reducing the size of the file from 6GB to 3GB resulting in an overall 40% run time gain for a compact batch.</li><li>Using Spark for the compact file processing helped in distributing the load in parallel to multiple tasks.</li><li>The whole process for shredding both input and output metadata on spark took only 10 mins per run.</li><li>Overall spark streaming job throughput per hour increased by 10%. The job was processing around 18–20 batches per hour and after the changes, it saved 6–8 mins every hour which were equivalent to 2 more batches or processing 6000 more files per hour.</li></ul><h3>Explicit Metadata Reader</h3><p>Spark Streaming job creates a _spark_metadata the directory inside the streaming job output directory. This directory contains the commit information for all the output batches for the streaming job and every 9th file is a compact file with the information of all the files generated by job, since the start date.</p><h4>Scenario</h4><p>We have already fixed the issue with the compact file size and have shredded the compact files to 3 GB but still, all the down-stream jobs were taking a long pause while reading the input from the streaming job and the throughput of the downstream job was impacted.</p><h4>Issue</h4><p>We looked into the spark driver logs and found that the driver is taking a long pause to read the input files list from the _spark_metadata directory. After further analysis, we found downstream spark job drivers list the latest compact file and all the new batches. It processes the list on the driver to extract and list all the required input files based on the partition values.</p><h4>Approach</h4><p>We analyzed the jobs and found a similar problem as compact file shredding. Spark driver was trying to process 3GB of data as a single task on job driver, which was the root cause for the slowness. We came up with a design to write a new library to distribute the read for the spark metadata.</p><ul><li>Write a new library to read _spark_metadata using the same logic from the spark code to list the latest compact file with all new batch commit files.</li><li>Create a data frame from the JSON files.</li><li>Filter the data frame based on the input partition ids for the downstream job.</li><li>Generate a list of input files and push the list to the spark driver for downstream job input.</li></ul><h4>Results</h4><ul><li>Distributing the read for the metadata files optimized the input read time for the downstream jobs by 5–8 mins.</li><li>We have many downstream jobs for the spark streaming and overall gains were huge.</li></ul><p>We are running streaming in production for more than two years now, We have done a lot of other optimizations to speed up the spark jobs. Commit file cleanup is still an open issue with the spark. Explicit Metadata Reader is something we built for our jobs. We are hoping to add explicit metadata reader as a third-party package to spark so others can use it.</p><p>Feel free to share if you have seen such scenarios in production with the Spark framework or if you used a different approach to sort it out !!</p><p>Thank you.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=d5dc5dee0622" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Exactly Once Mechanism in Spark Structured Streaming]]></title>
            <link>https://medium.com/@Iqbalkhattra85/exactly-once-mechanism-in-spark-structured-streaming-7a27d8423560?source=rss-4d6775d784d5------2</link>
            <guid isPermaLink="false">https://medium.com/p/7a27d8423560</guid>
            <category><![CDATA[apache-spark]]></category>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[apache]]></category>
            <category><![CDATA[streaming]]></category>
            <dc:creator><![CDATA[Iqbal Singh]]></dc:creator>
            <pubDate>Wed, 11 Mar 2020 13:19:35 GMT</pubDate>
            <atom:updated>2020-03-11T14:08:23.865Z</atom:updated>
            <content:encoded><![CDATA[<h3>Exactly Once Mechanism in Apache Spark</h3><h4>Structured Streaming state mechanism.</h4><h4>Data Delivery Guarantee</h4><p>All the next-generation data processing or streaming frameworks as Kafka, Spark, Apache Flink etc. come with different levels of data guarantee as</p><ul><li>Exactly Once (each record will be delivered once)</li><li>At Most Once</li><li>At Least Once</li></ul><p>However, everything has a price. Exactly once scenarios are most expensive as the job needs to make sure all the data is processed exactly once, with no duplicate or missing records.</p><h3>Spark Structured Streaming</h3><p>Before we dive in, If you don&#39;t know how to run a structured streaming application, you can read it here.</p><p><a href="https://medium.com/@Iqbalkhattra85/running-a-spark-structured-streaming-job-4d69422a0d2f?source=friends_link&amp;sk=fd64fe14622ad4a197098e55243a6a5c">https://medium.com/@Iqbalkhattra85/running-a-spark-structured-streaming-job-4d69422a0d2f?source=friends_link&amp;sk=fd64fe14622ad4a197098e55243a6a5c</a></p><h3>State Mechanism</h3><p>Spark <em>spark.sql.streaming.checkpointLocation</em> parameter adds an HDFS directory to spark session object and spark uses this directory to store checkpointing information for the structured streaming queries.</p><p>In our example query, we are processing a stateless transformation for converting a CSV file to a Parquet file, This is like a basic pre-staging data cleansing job with data scrubbing and data structuring. We are not performing any aggregations or watermarking.</p><p>We created a Structured Streaming parserQuery that reads all the files from the input dir and generates a Parquet output dataset based on the user-defined configuration of the micro-batch size and interval.</p><p><em>(Try to run the job for better understanding)</em></p><p><strong>Code Link</strong>: <a href="https://gist.github.com/isinghmm/ef158f7d1e48e47e82e57cd09100fc44">here</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/973/1*u5sxoOLBFSkR7IVc0tqADg.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/893/1*TfFIio8OTPSzDYKqyUzqow.png" /></figure><h4>Overview</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*17dfaFPzmsoI4EeUxeQZgw.png" /></figure><h4>Source Data Checkpointing</h4><p>Source data checkpointing is a state mechanism needed to make sure that the job is,</p><ul><li>Processing all the input data files.</li><li>Processing all the input files only once to avoid any data duplicates.</li><li>Able to recover from failed runs by itself.</li></ul><p>For the source data checkpointing, Our structured streaming query will create a new directory under the checkpointing location with queryName and this directory will contain three directories with specific checkpointing information about each micro-batch and one metadata file with query details.</p><p>The job will create one file per streaming micro-batch, with the batch number (same as Spark UI) under <strong>commits, offsets and sources </strong>directory.</p><p><strong><em>Job Output Dir: </em></strong>/tmp/checkpoint_streaming_poc</p><p><strong><em>Query Base Dir: </em></strong><strong><em>/</em></strong><em>tmp/checkpoint_streaming_poc/</em><strong><em>Parsing_Query</em></strong></p><p><em>The first line of each file will contain a value </em><em>v1 . This is the data source version spark is using for reading input. I have used spark2.2 that uses data source version v1. Data Source version v2 is available in new spark versions.</em></p><ol><li><strong><em>commits: </em></strong>Each commit files contain the commit information for the batch metrics.</li><li><strong><em>offset: </em></strong>Each offset file will contain the offset information for a particular batch. It will contain the batch watermark, batch timestamp and spark configurations.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SBKRl3tq0qhwAnZs8zWU6A.png" /></figure><p><strong><em>3. sources: </em></strong>Each source file will contain the information about every input file processed in the particular micro-batch with details of fileName, timestamp and the batch for the processing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*5HzokVJBXOzdckMM80jL3w.png" /></figure><p><strong><em>4. metadata: </em></strong>The metadata <strong>file</strong> contains the metadata information about our query as a query ID. This is the only file that covers the information of the structured streaming query.</p><pre>{&quot;id&quot;:&quot;caccdf75-85e4-4f68-9841-9a842efe5c9b&quot;}</pre><h4>Target Data Commits</h4><p>Target Data Commits are important for the job In the case of a Restart, Job needs to make sure what was the last successful completed micro-batch of data before starting the new batch.</p><p>For output data commit, the streaming job creates a new _spark_metadata directory under the output dir for committing the output micro-batches. The job will create one file per micro-batch under this output commit directory.</p><p><em>Output Dir for the structured streaming job contains the output data and a spark internal </em><em>_spark_metadata directory. Spark uses this dir internally for reading data for downstream jobs (Explained later in this article).</em></p><pre>hadoop fs -ls /tmp/streaming_query_poc/_spark_metadata</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/958/1*PHxcx806EPVzpseKl0DEdQ.png" /></figure><p>Every micro-batch output commit file contains information about batch output metrics with the information for every output file path, file name, file size, isDir flag, modification time for the file, block size for the file and the action.</p><pre>cat /tmp/streaming_query_poc/_spark_metadata/2</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SfCxX539JCiEGYJmrxoCdQ.png" /></figure><h3>.Compact File</h3><p>if we look closely at both the input and output checkpointing and commit saving directories respectively, we can see that every 10th file <strong>(9,19,29,39…)</strong> has an extension of <strong>“.compact”.</strong> A compact file aggregates the last 9 files and compacts all the data to a single file, this is to avoid the creation of too many small files as a structured streaming job can run for years and can process millions of batches. By Default spark only keep last 100 files in the checkpointing dir’s (10 .compact commit files one per 10 commit files)</p><p>We can configure by using below params</p><pre>&quot;spark.sql.streaming.minBatchesToRetain&quot; //default 100</pre><pre>&quot;spark.sql.streaming.fileSink.log.compactInterval&quot; //default 10</pre><pre>&quot;spark.sql.streaming.fileSource.log.compactInterval&quot; //default 10</pre><p><strong><em>As per design a Compact file under the source checkpointing dir will have a list of all the files processed by the Spark structured streaming job till today and the compact file under the output commit metadata will contain the list of files produced by the job till today</em></strong>.</p><h3>Working</h3><p>Spark MicroBatch Execution Class handles the processing for the checkpointing for the source data. On a very high level, we can add the steps as below.</p><ul><li>The job will check the last completed batch number will do a +1 and create a new batch number for the job.</li><li>It will list the files in the source directory ordered by the HDFS file timestamp.</li><li>Check the processed files from the source checkpointing dir and filter only unprocessed files.</li><li>Cut a batch defined by the max file batch threshold from the unprocessed file list.</li><li>Create a file with &lt;batchNumber&gt; as a name on the source side under all three above mentioned directories and will start processing the batch.</li><li>Once the batch completes successfully, A batch commit file will be created with all the output file details under the _spark_metadata directory.</li></ul><h3>Handling Failure</h3><p>In case of a failure,</p><ul><li>if the source checkpointing and output commit (_spark_metadata) dir have the same latest batch id, the job will start with the above-mentioned steps.</li><li>If the job created a batch on the source side and failed while processing the batch then the job will use the already created source batch details from the source batch checkpointing file and will process the batch.</li><li>Streaming jobs are very sensitive towards the state dir’s if there is any mismatch between the dir’s the job will keep on failing.</li></ul><h3>Downstream Data Reads</h3><p>Most Important is the mechanism to make sure downstream jobs are getting exactly one copy of data. To achieve this, a structured streaming job will only commit the whole batch once all the input files are processed. If a batch fails after processing partial data, the Job Output directory will have some output files for the failed batch but there will be no commit file for the batch under spark_metadata directory.</p><ul><li>While-Reading Data, Spark checks the input data location of a job and enforces the spark job to read from spark_metadata . if it is available under the input location.</li><li>By doing this, jobs reading from the structured streaming output directory will only generate an input file list from _spark_metadata and will never read any partial batches.</li></ul><h4><strong><em>It gives us a Data Guarantee of Exactly Once for downstream jobs.</em></strong></h4><p>This mechanism is expensive, it adds two more stages for listing and defining a micro-batch for processing and this is a big pain for large systems consuming thousands of files. I will list all the issues and workarounds for them in my next post. <br>Feel free to review and let me know if any changes are required.</p><p>Thanks !!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7a27d8423560" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>