<?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 Shyamal Akruvala on Medium]]></title>
        <description><![CDATA[Stories by Shyamal Akruvala on Medium]]></description>
        <link>https://medium.com/@shyamal.akruvala?source=rss-87e8a1d3013b------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*LpoWr9fUcg9ZtO5lth6T_A.png</url>
            <title>Stories by Shyamal Akruvala on Medium</title>
            <link>https://medium.com/@shyamal.akruvala?source=rss-87e8a1d3013b------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 22:36:47 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@shyamal.akruvala/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[Chat Your Way to Insights Using Snowflake Cortex Analyst]]></title>
            <link>https://medium.com/@shyamal.akruvala/chat-your-way-to-insights-using-snowflake-cortex-analyst-bc2c72c67e0b?source=rss-87e8a1d3013b------2</link>
            <guid isPermaLink="false">https://medium.com/p/bc2c72c67e0b</guid>
            <category><![CDATA[nemo-guardrails]]></category>
            <category><![CDATA[llama-guard]]></category>
            <category><![CDATA[streamlit]]></category>
            <category><![CDATA[snowflake-data-cloud]]></category>
            <category><![CDATA[cortex-analyst]]></category>
            <dc:creator><![CDATA[Shyamal Akruvala]]></dc:creator>
            <pubDate>Sat, 07 Jun 2025 12:14:27 GMT</pubDate>
            <atom:updated>2025-06-07T12:14:27.302Z</atom:updated>
            <content:encoded><![CDATA[<p>Imagine getting answers from your company’s vast Snowflake data just by asking a question in plain English. No need to write complex SQL queries or wait for a data analyst. That’s the promise of conversational analytics.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/408/1*htmqb1lq8aMN1DcYnIZVvg.png" /><figcaption>Fritz (Image created using ChatGPT)</figcaption></figure><p>I’ve created a chatbot application (<a href="https://github.com/Shyamal002/snowflake_cortex_analyst_bot">FrostyQuery</a>) which will take in a user’s query in natural language and seamlessly transform it into actionable insights from Snowflake. Users might ask:</p><blockquote><em>“Show me the current accounts”</em></blockquote><p>or</p><blockquote><em>“What are the most popular account types?”</em></blockquote><p>But since databases only understand SQL, FrostyQuery steps in to bridge that gap. It interprets natural language queries, translates them into SQL, executes them on Snowflake, and presents the results in an easily understandable format — no SQL expertise required.</p><h3>Core Components</h3><h3>🔮 1. Natural Language Understanding with Snowflake Cortex Analyst</h3><p>At its heart, FrostyQuery leverages the power of <strong>Snowflake Cortex Analyst</strong> to perform the crucial task of converting your natural language into SQL.</p><h3>📃 2. Semantic Model for Data Context</h3><p>But how does it know what your data means? <br>This is where the <strong>Semantic Model</strong> comes in. Think of the Semantic Model as a dictionary and relationship map for your data. Defined in a YAML file (see <a href="https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst/semantic-model-spec">docs</a>) and stored in a Snowflake stage, it provides Cortex Analyst with essential context about the tables, columns, relationships, measures, and dimensions within your Snowflake schema. It even includes sample values for columns, helping the system understand the data better. It also includes relationships between tables so that joins are better defined. Additionally, verified queries can be explicitly added to inform the Cortex Analyst on how certain joins and calculations should be performed, ensuring precision for complex analytical requests.</p><p>For example, if you ask:</p><blockquote><em>“Show me customers who are Indian”</em></blockquote><p>The Semantic Model helps Cortex Analyst understand that “customers” refers to the CUSTOMERS table and &quot;Indian&quot; relates to the NATIONALITY column.</p><h3>💻 3. Streamlit UI: The Chat Stage</h3><p>The interaction point for the user is the <strong>Streamlit UI</strong>. This is the “stage” where the chatbot performs. <br>It provides:</p><ul><li>Input elements (text boxes for questions)</li><li>Display areas (tables, charts)</li><li>Interactive elements (feedback buttons, role selectors)</li></ul><p>The UI captures the user’s question and sends it to the chatbot’s logic layer.</p><h3>🛡️ 4. Guardrails for Safe Interaction</h3><p>FrostyQuery incorporates <strong>Guardrails</strong> to prevent unsafe or harmful inputs. Two systems are supported:</p><ul><li><strong>NVIDIA NeMo Guardrails</strong></li><li><strong>LlamaGuard (via Ollama)</strong></li></ul><p>These systems evaluate both the user prompts and bot responses using policy-driven or LLM-based checks, although output validation is currently limited and may not always catch unsafe or sensitive information. Users can select the type of guardrails or disable them if needed. Additionally, the system includes a basic keyword filter that checks for harmful SQL terms like ‘DROP’, ‘TRUNCATE’, or ‘DELETE’ in user-provided SQL inputs and blocks such queries to prevent potential misuse. These checks are performed before the prompt is sent to the Cortex Analyst, ensuring that unsafe content is intercepted early in the request cycle.</p><h3>⚖️ 5. SQL Execution and Result Handling</h3><p>Once a user’s prompt is cleared through guardrail checks and found safe, it is handed over to the Snowflake Cortex Analyst. Cortex Analyst intelligently converts the prompt into a precise SQL statement, using the Semantic Model to ensure the query is both context-aware and structurally accurate. This generated SQL is returned to the chatbot, which then uses Pandas to execute the query against Snowflake, retrieving the relevant data. The results are polished and transformed into easy-to-understand tables or rich visualizations, all within the Streamlit UI. Users can also download the data as a CSV with a single click. Robust error handling ensures that even if something goes wrong, the experience remains smooth and informative.</p><h3>🔐 6. Role-Based Access Control (RBAC)</h3><p><strong>Security is key</strong>. FrostyQuery implements <strong>RBAC</strong> to restrict access based on user roles (e.g., “Regular User”, “Auditor”). <br>Each role has:</p><ul><li>Blocked Tables</li><li>Blocked Queries</li></ul><p>These restrictions are enforced dynamically in the app and selected via the UI sidebar. While Cortex Analyst already leverages Snowflake’s built-in RBAC policies for secure data access, this additional layer leverages traditional data engineering logic to explicitly control what the end user can see or query. It provides granular visibility restrictions that complement Snowflake’s native security features.<br>If a user requests access to restricted data, the system responds with a permission error.</p><h3>💭 7. Multiturn Conversations</h3><p>To make conversations feel natural and allow for follow-up questions, FrostyQuery uses <strong>Session State Management</strong> in tandem with <a href="https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst#:~:text=FALSE-,Multi%2Dturn%20conversation%20in%20Cortex%20Analyst,-%C2%B6"><strong>Cortex Analyst’s Multiturn conversation capabilities</strong></a>. This powerful combination keeps the dialogue flowing by maintaining context across turns, enabling users to build on previous questions naturally. It stores chat history and preferences with Streamlit’s st.session_state, allowing contextual queries like:</p><blockquote><em>“Show me all the customers in New York”</em></blockquote><p>Followed by:</p><blockquote><em>“Which customers make transactions using cash?”</em></blockquote><p>The bot remembers what “which” means based on earlier queries and then will show customers filtered by “New York” and “Cash” transactions.</p><h3>📚 8. Verified Query Repository Support</h3><p>FrostyQuery makes use of Cortex Analyst’s powerful <strong>Verified Query Repository</strong> feature. This allows us to provide Cortex with a curated set of example SQL queries that showcase how to perform complex joins, aggregations, or domain-specific calculations. These verified queries act as trusted references, guiding Cortex Analyst when converting user prompts into SQL. As a result, even highly complex, multi-table join queries are generated more accurately and consistently, enhancing both reliability and performance for advanced use cases.</p><h3>🧠 10. Feedback Integration with Cortex Analyst</h3><p>Feedback doesn’t just stop at user interface level; FrostyQuery integrates directly with <strong>Cortex Analyst’s Feedback API</strong>. When a user gives a thumbs down to a response, the system prompts for additional input to understand what went wrong. This feedback — both positive and negative — is securely stored in Snowflake’s internal feedback tables. Over time, this data can be used to refine the Semantic Model and enhance the accuracy of future natural language to SQL conversions. It’s a continuous loop of learning and improvement driven by real user interactions.</p><h3>🚀 Bringing It All Together</h3><p>FrostyQuery is more than just a simple question-answering bot. It’s a full-fledged solution for <strong>self-service analytics</strong> over Snowflake. It aims to empower business users to explore and understand data <strong>through natural conversation</strong>, backed by enterprise-grade security and flexibility.</p><h4><strong>Github Repo:</strong> <a href="https://github.com/Shyamal002/snowflake_cortex_analyst_bot">https://github.com/Shyamal002/snowflake_cortex_analyst_bot</a></h4><p><em>If you’re just getting started with Snowflake and would like some guidance on creating a trial account and enabling Cortex Analyst, you might find my other post helpful: </em><a href="https://medium.com/@shyamal.akruvala/%EF%B8%8F-getting-started-with-snowflake-create-a-trial-account-and-enable-cortex-analyst-920d9b842bd9"><em>Create Snowflake Trial Account and Enable Cortex Analyst</em></a></p><p><strong>References:<br></strong>- <a href="https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst">https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst</a><br>- <a href="https://docs.nvidia.com/nemo/guardrails/latest/index.html">https://docs.nvidia.com/nemo/guardrails/latest/index.html</a><br>- <a href="https://huggingface.co/meta-llama/Llama-Guard-3-8B">https://huggingface.co/meta-llama/Llama-Guard-3-8B</a><br>- <a href="https://github.com/Snowflake-Labs/sfguide-getting-started-with-cortex-analyst">https://github.com/Snowflake-Labs/sfguide-getting-started-with-cortex-analyst</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=bc2c72c67e0b" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[❄️ Getting Started with Snowflake: Create a Trial Account and Enable Cortex Analyst]]></title>
            <link>https://medium.com/@shyamal.akruvala/%EF%B8%8F-getting-started-with-snowflake-create-a-trial-account-and-enable-cortex-analyst-920d9b842bd9?source=rss-87e8a1d3013b------2</link>
            <guid isPermaLink="false">https://medium.com/p/920d9b842bd9</guid>
            <category><![CDATA[snowflake-cortex-analyst]]></category>
            <category><![CDATA[snowflake-data-cloud]]></category>
            <category><![CDATA[snowflake-cortex-ai]]></category>
            <dc:creator><![CDATA[Shyamal Akruvala]]></dc:creator>
            <pubDate>Sat, 07 Jun 2025 11:59:44 GMT</pubDate>
            <atom:updated>2025-06-07T11:59:44.582Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*zOLMLFZjcXBL0d6L4rZq8w.png" /><figcaption>Created using ChatGPT</figcaption></figure><p>If you’re curious about exploring the power of <strong>Snowflake’s Data Cloud</strong> — especially the exciting new <strong>Cortex Analyst</strong> feature that lets you talk to your data in plain English — this post is your guide. We’ll walk you through setting up a <strong>Snowflake free trial account</strong> and show you how to <strong>enable Snowflake Cortex Analyst</strong>, so you can start querying like never before.</p><h3>Step 1: Sign Up for a Free Snowflake Trial</h3><p>Snowflake offers a generous <strong>30-day free trial</strong> with $400 in credits. Here’s how you can get started:</p><ol><li>Go to <a href="https://signup.snowflake.com/">https://signup.snowflake.com/</a></li><li>Fill in your work email and personal details.</li><li>Choose a cloud provider (AWS, Azure, or GCP) and region closest to you.</li></ol><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*iCRsNSZ4FZcnmsr0dTbIbA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gyzshpdQ9P36RiF-NxMuIA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*hUX2e1NqyaJWcOL_A6u_qA.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mjdKO4SGkLyK4MKqSsm5yQ.png" /></figure><h3>Step 2: Activate Your Account</h3><p>After submitting the form, you’ll receive an email from Snowflake to activate your account.<em> Check for email from Snowflake Computing (no-reply@snowflake.net)</em></p><ol><li>Click the activation link.</li><li>Set your username and password.</li><li>Log in to the <strong>Snowflake web UI</strong>.</li></ol><p><em>The email will also contain a link you can use to login in future in form of</em></p><p><a href="https://9rjj6kfe.r.ap-southeast-2.awstrack.me/L0/https:%2F%2Fjnemssj-gu36721.snowflakecomputing.com%2Fconsole%2Flogin/1/0108019738d788a2-fd527c5e-7921-4419-9dc6-65f49a780380-000000/PegfubxCZ9ZTqfslHcohvDE00-8=209">https://&lt;snowflake_host&gt;.snowflakecomputing.com/console/login</a></p><h3>Step 3: Familiarize Yourself with the Snowflake Web Interface</h3><p>Once inside, you’ll land on the <strong>Snowsight</strong> dashboard (Snowflake’s web-based UI).</p><ul><li>You can explore sample databases, write SQL queries, and manage warehouses.</li><li>Notice the <strong>top nav bar</strong> with tabs for Worksheets, Dashboards, and Marketplace.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*d5ssnixJ88G7bAmxYKkYIQ.png" /><figcaption>Snowsight — <a href="https://docs.snowflake.com/en/user-guide/ui-snowsight">https://docs.snowflake.com/en/user-guide/ui-snowsight</a></figcaption></figure><h3>Step 4: Enable Snowflake Cortex Analyst</h3><p><strong>Snowflake Cortex Analyst</strong> is part of the Cortex AI suite, designed to make it easy to <strong>ask questions in plain English</strong> and get data-driven answers. <br><em>Read more at </em><a href="https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst">https://docs.snowflake.com/en/user-guide/snowflake-cortex/cortex-analyst</a></p><p>Cortex Analyst is natively available in the following regions.</p><ul><li>AWS ap-northeast-1 (Tokyo)</li><li>AWS ap-southeast-2 (Sydney)</li><li>AWS us-east-1 (Virginia)</li><li>AWS us-west-2 (Oregon)</li><li>AWS eu-central-1 (Frankfurt)</li><li>AWS eu-west-1 (Ireland)</li><li>Azure East US 2 (Virginia)</li><li>Azure West Europe (Netherlands)</li></ul><p>If you selected any other region when setting up your trial account then execute below query to enable cross-region inference to use Cortex Analyst.</p><pre>use role accountadmin; <br>ALTER ACCOUNT SET CORTEX_ENABLED_CROSS_REGION = &#39;AWS_US&#39;;</pre><p>You can learn more about cortex analyst using the QuickStart guide: <a href="https://quickstarts.snowflake.com/guide/getting_started_with_cortex_analyst/#0">https://quickstarts.snowflake.com/guide/getting_started_with_cortex_analyst/#0</a></p><p>Snowflake makes it incredibly easy to spin up a modern data platform in minutes. With <strong>Cortex Analyst</strong>, you can go from raw data to rich insights using nothing but natural language.</p><p>Whether you’re a data analyst, business user, or curious technologist — give it a try and experience the future of querying firsthand.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=920d9b842bd9" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[File Comparison Using PySpark and Pandas]]></title>
            <link>https://medium.com/@shyamal.akruvala/file-comparison-using-pyspark-and-pandas-84811c6116af?source=rss-87e8a1d3013b------2</link>
            <guid isPermaLink="false">https://medium.com/p/84811c6116af</guid>
            <category><![CDATA[parquet]]></category>
            <category><![CDATA[big-data]]></category>
            <category><![CDATA[comparing-files]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[pandas]]></category>
            <dc:creator><![CDATA[Shyamal Akruvala]]></dc:creator>
            <pubDate>Fri, 12 Nov 2021 12:33:51 GMT</pubDate>
            <atom:updated>2021-11-12T12:33:51.146Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/0*XczySXEgIJAHiaCd" /><figcaption>Photo by <a href="https://unsplash.com/@goian?utm_source=medium&amp;utm_medium=referral">Ian Schneider</a> on <a href="https://unsplash.com/?utm_source=medium&amp;utm_medium=referral">Unsplash</a></figcaption></figure><p>While working on BigData projects for the past couple years, comparing files for data discrepancies has been a common task. This task becomes more crucial when we are migrating between systems and are required to ensure the output of the old and the new systems are identical. There are multiple ways of doing it and it also depends on the nature of the comparison.</p><p>Most of the time I tend to rely on Spark’s <a href="https://spark.apache.org/docs/latest/api/python/reference/api/pyspark.sql.DataFrame.subtract.html">subtract</a> method to help me identify the differing records between two files loaded into DataFrames. This gives me a general understanding of the differences but doesn’t help to easily identify them. This story is about a quick and simple way to visualize those differences, eventually speeding up the analysis.</p><p>Importing pandas, numpy and pyspark and creating a spark session.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e1d3f7bf5046484aa7fb3551a8ae8ce2/href">https://medium.com/media/e1d3f7bf5046484aa7fb3551a8ae8ce2/href</a></iframe><p>Creating DataFrame 1 with some mocked up data</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6dbe4129a13a3a14414e75da336a73eb/href">https://medium.com/media/6dbe4129a13a3a14414e75da336a73eb/href</a></iframe><figure><img alt="Displaying the records of the first dataframe" src="https://cdn-images-1.medium.com/max/389/1*eOFdcR1gYAburXmJCu-_Tw.png" /></figure><p>Creating DataFrame 2 with some mocked up data with few rows removed and couple changed</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c988f3545559004265fbb41f1b708c7e/href">https://medium.com/media/c988f3545559004265fbb41f1b708c7e/href</a></iframe><figure><img alt="Displaying the records of the second dataframe" src="https://cdn-images-1.medium.com/max/486/1*nMgyZHMDW6dE3oCDc1m28w.png" /></figure><p>Created a method which takes 2 DataFrames as input along with couple optional parameters (Primary Key and No of mismatch records to display)</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/6ece91d9da5bf9aeae178be500e67fe2/href">https://medium.com/media/6ece91d9da5bf9aeae178be500e67fe2/href</a></iframe><p>The method does the following,</p><ol><li>Check if the schemas of the two DataFrames are identical. If they are not then the method exits after displaying the schemas side by side.</li><li>Then we use Spark’s Subtract method to get the differing rows in either DataFrame. If the count of these resulting DataFrames is 0 (zero) then we can conclude they are identical data-wise.</li><li>If there are differing records then convert the subtracted Spark DataFrames into Pandas (<em>might be useful to use .limit(100) in case the overall dataset is large and expected differences are large too), </em>make the Primary Key column as the first column and then rearrange columns for presentation.</li><li>Finally the method returns a styled Pandas DataFrame which can be visualized using IPython’s display method.</li></ol><p>Examples</p><p>★ In case the schemas are not same then</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/c6550f13c1ee5d59faac0807e9b6f69a/href">https://medium.com/media/c6550f13c1ee5d59faac0807e9b6f69a/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/601/1*sTdcW0ZcXdcWyujzZQjNtQ.png" /></figure><p>★ In case the schemas are same but data has mismatches</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/1914d4e219c328561c32ff9c85a2db47/href">https://medium.com/media/1914d4e219c328561c32ff9c85a2db47/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9IfuCIdK3_O2PFZILqVquA.png" /><figcaption>1 column as Primary Key</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/555/1*OeGFrMPq4ntbyFoYxevvbg.png" /><figcaption>2 columns as Primary Key</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/971/1*a-Ti7x9tp2PO8bErx3g85w.png" /><figcaption>No Primary Key provided.</figcaption></figure><p>Above we know that the records with ids 4, 5, 7, 8, 9 were not present in second DataFrame (highlighted in CYAN) and records for ids 6 and 10 were modified (highlighted in PINK).</p><p>★ In case the schemas and the data are same</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/fb38dfd6fcdf239d1c8cbcf69054faa3/href">https://medium.com/media/fb38dfd6fcdf239d1c8cbcf69054faa3/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/527/1*auVBBhy4NagE5xjlaoprRg.png" /></figure><p>Thanks for reading this article and hope you liked it 🙂</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=84811c6116af" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Spark Join Types Visualized]]></title>
            <link>https://medium.com/@shyamal.akruvala/spark-join-types-visualized-30d784068c8a?source=rss-87e8a1d3013b------2</link>
            <guid isPermaLink="false">https://medium.com/p/30d784068c8a</guid>
            <category><![CDATA[spark]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[join]]></category>
            <dc:creator><![CDATA[Shyamal Akruvala]]></dc:creator>
            <pubDate>Fri, 23 Jul 2021 09:40:35 GMT</pubDate>
            <atom:updated>2021-07-24T00:27:56.780Z</atom:updated>
            <content:encoded><![CDATA[<p>Joins are an integral part of any data analysis or integration process. Two sets of data, left and right, are brought together by comparing one or more columns (read keys) along with the joining conditions to determine the final output which can contain data from either left or right or both based on the <strong><em>join types</em></strong>.</p><p>Apache Spark provides the below joins types,</p><ol><li><strong>Inner </strong>Joins (Records with keys matched in BOTH left and right datasets)</li><li><strong>Outer </strong>Joins (Records with keys matched in EITHER left or right datasets)</li><li><strong>Left Outer </strong>Joins (Records with keys in the left dataset)</li><li><strong>Right Outer </strong>Joins (Records with keys in the right dataset)</li><li><strong>Left Semi </strong>Joins (Records from left dataset with matching keys in right dataset)</li><li><strong>Left Anti </strong>Joins (Records from left dataset with not matching keys in right dataset)</li><li><strong>Natural </strong>Joins (done using implicit column matching between datasets)</li><li><strong>Cross </strong>Joins (Cartesian product of the left and right datasets)</li></ol><p>Let’s create some data to demonstrate the various joins.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/30a1898fe8196a64b62bb174422d081a/href">https://medium.com/media/30a1898fe8196a64b62bb174422d081a/href</a></iframe><p>I’ve created 2 DataFrames (namesDF, typesDF) and they can be linked using the ‘<strong>Id</strong>’ column. Here’s how they look like,</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/382/1*7__KOhkO-ztoMNX50ekm-Q.png" /></figure><p>I’ll focus more on the DataFrame APIs as in SparkSQL the syntax is similar to any other SQL. The syntax for joining using the DataFrame APIs is,</p><pre>DataFrame1.join(DataFrame2, [on=keys], [how=joinType])</pre><p><em>The joining on condition and join type are optional. </em><strong><em>DataFrame1 </em></strong><em>is termed as </em><strong><em>left </em></strong><em>and </em><strong><em>DataFrame2 </em></strong><em>is termed as </em><strong><em>right</em></strong></p><h3>Inner Joins</h3><p>This is the default join i.e. if no join type is mentioned then Spark performs an inner join. Only those records are pulled into the output where the keys from both datasets, left and right, match. Keys that do not exists in both the datasets are ignored i.e. not shown / outputted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*akN3WEIt8AecJnIcqd8hFg.jpeg" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/93f468ec153ce04d4e80cf8a3889dfd5/href">https://medium.com/media/93f468ec153ce04d4e80cf8a3889dfd5/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/484/1*8fV_5RpOTTujwDUmtvkaTA.png" /></figure><h3>Outer Joins aka Full Outer Joins</h3><p>Records from both left and right datasets are included in output. In places where the keys don&#39;t match <strong>null </strong>values are inserted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ftgP-j6Ywe6beMt0BqqFzA.jpeg" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a4cc28af3d7b2c7a774bc317c1b0e31b/href">https://medium.com/media/a4cc28af3d7b2c7a774bc317c1b0e31b/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/404/1*8yXpp21l0p58jo4DrWfetQ.png" /></figure><h3>Left Joins aka Left Outer Joins</h3><p>All records from the left dataset are part of the output. Records from the right dataset are included where keys are matched. For remaining <strong>null </strong>values are inserted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*WGMkmg0p4HQRe6zgMaNFKA.jpeg" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/036bc7f710076d6a63410797a562bf18/href">https://medium.com/media/036bc7f710076d6a63410797a562bf18/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/548/1*3iaSClvu32HFQ0OpG5zd_g.png" /></figure><h3>Right Outer Joins aka Right Joins</h3><p>All records from the right dataset are part of the output. Records from the left dataset are included where keys are matched. For remaining nulls are inserted.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HK-WNcCBI1HPveMEER5Rtg.jpeg" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/37f3cb163cb3ce9b7ece52e769681024/href">https://medium.com/media/37f3cb163cb3ce9b7ece52e769681024/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/574/1*uYyFgCe1Kli57IpFZtjPsw.png" /></figure><h3>Left Semi Joins</h3><p>Only records from the left dataset are included where they have a matching key in the right dataset. If left dataset has duplicates then output will contain duplicates. It can be looked upon as a filter rather than a join. We filter the left dataset based on matching keys from the right dataset.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XX9jRco6AC0qJxOLLFGvBQ.jpeg" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7824441a937a97c784c1fe350f5fed66/href">https://medium.com/media/7824441a937a97c784c1fe350f5fed66/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/376/1*dlYjdcyGvs83jP7kc6NJXQ.png" /></figure><h3>Left Anti Joins</h3><p>Opposite to Left Semi Joins.<br>Only records from the left dataset are included where they do not have a matching key in the right dataset. Similar to a NOT IN SQL filter.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*N5Aix4JO3hem6Lni1v-Ntg.jpeg" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/693bc2be7a5f108b6b114afad5714952/href">https://medium.com/media/693bc2be7a5f108b6b114afad5714952/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/351/1*DrLrC74Gs2aZuNyujKxvXA.png" /></figure><h3>Natural Joins</h3><p>When using Natural Join Spark tries to implicitly guess on the columns to join. Implicit is always risky and hence this join should be used with caution. <em>Also its available only in Spark SQL and not in the DataFrame APIs.</em></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/885a9aa5d48dad075d0e1d16fe063095/href">https://medium.com/media/885a9aa5d48dad075d0e1d16fe063095/href</a></iframe><h3>Cross Join aka Cartesian Product</h3><p>Every record from the left dataset is joined to every record in the right dataset. This results in an explosion 🎆🧨 of records. If we cross two datasets with 1000 records each then the resultant output will be 1000 * 1000 = 1,000,000 records. This can easily result into OOM errors. <br>But there can be a valid case where you want to generate every possible combination of two datasets. So to avoid confusion you can use the <strong>crossJoin </strong>method to call it out explicitly.</p><p><em>Note: I believe as a precautionary step to avoid creating a cartesian product which may crash the application, Spark in versions lesser than 3.x has the cross join config setting to False by default. You’ll get an error like below (checked on v2.4.4) and it’ll request you to explicitly set the </em><strong><em>spark.sql.crossJoin.enabled </em></strong><em>setting to</em><strong><em> True. </em></strong><em>This setting is set to True by default in v3.x onwards.</em></p><pre>pyspark.sql.utils.AnalysisException: &#39;Detected implicit cartesian product for INNER join between logical plans<br>Join condition is missing or trivial.<br>Either: use the CROSS JOIN syntax to allow cartesian products between these relations, <br>or: enable implicit cartesian products by setting the configuration <br>variable spark.sql.crossJoin.enabled=True;&#39;</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*0zixLCyH5V-8B6PYsDvFOw.jpeg" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e44dc6f43005c44fffa6c4ebe4ad0e99/href">https://medium.com/media/e44dc6f43005c44fffa6c4ebe4ad0e99/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/693/1*0GewFeaqonnMrjUvW_AmUg.png" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f007fe54ff90ca5a48800a6b3edc5904/href">https://medium.com/media/f007fe54ff90ca5a48800a6b3edc5904/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/755/1*hE2VL1rLZ3fnC5tQPLOPOQ.png" /></figure><h3>Notes</h3><ol><li>One thing the many Venn diagrams on SQL joins fail to communicate is that if there are duplicates in your source they will be in your join output</li></ol><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/b4dda1f7c915eae3f1a7f4a5fe06d54e/href">https://medium.com/media/b4dda1f7c915eae3f1a7f4a5fe06d54e/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/697/1*Slld7pijyXEUqJS1iVFmRQ.png" /></figure><p>2. Usually the keys you join on would not include null values. But in case there is a scenarios where you’d like to join on null keys then you can use the <strong>eqNullSafe </strong>option in the joining condition.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/09045f83a7d5818d8aac021f2bf28c5b/href">https://medium.com/media/09045f83a7d5818d8aac021f2bf28c5b/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/707/1*kiKt_k9xTOxW9pkOmXxAyA.png" /></figure><p>Thank you for spending your time in reading this article. Have a good one 😃</p><h3>References</h3><ol><li><a href="https://spark.apache.org/docs/3.1.1/api/python/reference/api/pyspark.sql.DataFrame.join.html">Spark Documentation</a></li><li><a href="https://stackoverflow.com/questions/41728762/including-null-values-in-an-apache-spark-join">Stackoverflow</a></li><li><a href="https://stackoverflow.com/questions/41728762/including-null-values-in-an-apache-spark-join">Stackoverflow Null Safe Check</a></li></ol><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=30d784068c8a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Jupyter Notebook — Tips & Tricks]]></title>
            <link>https://medium.com/@shyamal.akruvala/jupyter-notebook-tips-tricks-73ec6cbe4a7?source=rss-87e8a1d3013b------2</link>
            <guid isPermaLink="false">https://medium.com/p/73ec6cbe4a7</guid>
            <dc:creator><![CDATA[Shyamal Akruvala]]></dc:creator>
            <pubDate>Sun, 18 Jul 2021 10:02:34 GMT</pubDate>
            <atom:updated>2021-07-19T01:30:35.287Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*sfOJ0CqyrrXP-ArOIW8qvQ.jpeg" /></figure><h3>Jupyter Notebook — Tips &amp; Tricks</h3><p>In my current project we collect a lot of data and perform analytics on it. It ranges from identifying trends to answering very specific business questions. We use Jupyter Notebooks extensively as it provides an awesome interface for interactive analysis and ability to share the findings or insights with others. This article documents some of the useful features that I use on a daily basis.</p><p>A Jupyter Notebook as defined on their <a href="https://jupyter.org/">official homepage</a> is</p><blockquote><em>An open-source web application that allows you to create and share documents that contain live code, equations, visualizations and narrative text. Uses include: data cleaning and transformation, numerical simulation, statistical modeling, data visualization, machine learning, and much more.</em></blockquote><p>The name JuPyteR is derived from the three core programming languages or kernels the tool supports, namely Julia, Python and R. The IPython kernel is shipped along with Jupyter. Jupyter Notebooks, Jupyter Lab and Jupyter Hub are the three products offered for interactive processing or analytics. For installation and usage you can follow the steps available at <a href="https://jupyter.org/install">Jupyter Home</a> or <a href="https://jupyter-notebook.readthedocs.io/en/stable/index.html">Jupyter Docs</a></p><p>Below are some useful tricks that helped me get the most out of the tool,</p><h3>1. Screen Width</h3><p>By default the Notebook leaves a lot of margin on the sides. These days when working from home I connect my laptop to an external monitor and personally I prefer to use all the real estate available on my screen for development.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*3PG_obFnOdQ5TyDwjJKzzA.png" /></figure><p>You can extend the width of usable Notebook space by executing the below command</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a96b2de86aede42d8f6f26bc583920f4/href">https://medium.com/media/a96b2de86aede42d8f6f26bc583920f4/href</a></iframe><p>Post executing the commands your Notebook would look like below giving you more cell space to write code</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lqEVDxAr-ZQLI4_89av7FA.png" /></figure><h3>2. Output for all commands in a cell</h3><p>I tend to club a lot of my commands into one cell. Like read, count etc. By default only the output of the last command is shown.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/516/1*e28kfS1MyBCdLJ9yiocg6g.png" /></figure><p>You can execute the below commands to enable the output of all commands to be displayed.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/5f4d46e080ad14969b2c902e07265593/href">https://medium.com/media/5f4d46e080ad14969b2c902e07265593/href</a></iframe><p>Post execution all the relevant outputs from a cell will be displayed in order.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/743/1*U_-ToqC_g1ZnVP30_NuZtQ.png" /></figure><h3>3. Pandas settings</h3><p>When working with a wide and/or a large dataset Pandas hides some columns and rows to try and fit to the display. The “hidden” rows / columns are shown as dots and highlighted in green in below image</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1015/1*guAnnYjST5X0TngbwL1WYA.png" /></figure><p>You can set the below two properties to enforce Pandas to show the required number of columns and rows,</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/1f14287b6d31ed3a2b6ef6b3979b8b6f/href">https://medium.com/media/1f14287b6d31ed3a2b6ef6b3979b8b6f/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*W5Y0Ht5-FUE1s9OcOwJ5Kg.png" /></figure><p>Additionally there could be a scenario where one particular column has a large value which is shown truncated by Pandas. Here you can use the max_colwidth option to set the display length</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/956/1*wm8dZWf8RvvUsR1Zij8Dig.png" /></figure><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/cfa6a799a81e506bd27c5f30f35d4f09/href">https://medium.com/media/cfa6a799a81e506bd27c5f30f35d4f09/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/1007/1*oO2iOgLOMHDQ9EXVYbuJdw.png" /></figure><p>You can read about the available options for pandas at <a href="https://pandas.pydata.org/pandas-docs/stable/user_guide/options.html">Pandas Documentation</a></p><h3>4. Executing shell commands</h3><p>Running shell commands directly from the Notebook is as easy as prefixing them with an exclamation mark (!). All commands that would run on the shell prompt would work</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*XmpF07ao6M9Zcz9mW5nIvg.png" /></figure><p>You can use this approach to install any missing dependencies too</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/558/1*2_ji3iD-9lK0NOnhadBbBA.png" /></figure><p>The output of the shell command can be stored in a variable and used within the notebook for processing.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/361/1*zu4W-F-0rdbw5gSFe-z9CA.png" /></figure><h3>5. Magic Commands</h3><p>For everyday mundane tasks IPython provides shortcuts that are referred to as <em>magic</em> commands. You can refer the documentation at <a href="https://ipython.readthedocs.io/en/stable/interactive/magics.html">IPython Docs</a>.</p><p>These commands are prefixed by <strong>%</strong> symbol. A <strong>single %</strong> if you want the command to <strong>operate over a single line</strong> and <strong>double %%</strong> to <strong>operate over multiple lines</strong> or the whole cell.</p><p>Few commonly used ones are,</p><p><strong>A) Executing an external script from within the notebook.</strong></p><p>There may be a case where you have a script that prepares the data or does some tasks as a precursor to running the current Notebook. You can call such scripts and/or notebooks from within the current Notebook using <strong>%run</strong></p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/90dc96d6189456043ca324d816804a0e/href">https://medium.com/media/90dc96d6189456043ca324d816804a0e/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/464/1*nFPEgoU0qYa5mojZJFnUog.png" /></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/360/1*ObDlTAXKRK9zB8TME5Xb5w.png" /></figure><p><strong>B) Measure Execution Time</strong></p><p>You can use <strong>%time</strong> or <strong>%%time</strong> to measure the execution time of your code. <em>For small pieces of code it would even run it multiple times and show the best time</em>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/514/1*SNMQe5FFrfl2MLosgIBCZA.png" /></figure><p><em>There are extensions available to show the cell execution time automatically (yellow highlights)</em></p><p>There are many magic commands and you can list them using <strong>%lsmagic</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bRksUkcgUcGqMIMJ4bojzA.png" /></figure><h3>6. Additional functionality using extensions</h3><p>Detailed documentation on the extensions is available <a href="https://jupyter-contrib-nbextensions.readthedocs.io/en/latest/">here</a></p><p>Execute the below 2 commands on your CLI to install the extensions</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/a7894717ebaab9132d8810fb1981afb0/href">https://medium.com/media/a7894717ebaab9132d8810fb1981afb0/href</a></iframe><p>Then on your Jupyter Homepage you should see the new tab for extensions. From here you can enable and disable them. Highlighted a few commonly used ones in the below image</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yZq11sIxyfpSRtyFgQX5Lg.png" /></figure><h3>7. Keyboard Shortcuts</h3><p>There are many keyboard shortcuts in Jupyter that can speed up your development. To view them you can <strong>press ESC</strong> to enable the command mode and then <strong>h</strong> for opening the shortcut window.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/192/1*8Nfiw9PqLoceWqfaOO8goA.png" /></figure><h3>8. Run the notebook from CLI</h3><p>Finally you can also execute your complete notebook from the command line interface using the below command</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/7ebc0b3c16a2e34f739eae40b55639ad/href">https://medium.com/media/7ebc0b3c16a2e34f739eae40b55639ad/href</a></iframe><p>There is a lot more that Jupyter Notebooks offer and it’s worth reading their <a href="https://jupyter-notebook.readthedocs.io/en/stable/index.html">documentation</a></p><p>Thank you for spending some time in reading this post. Have a wonderful life!</p><h3>References</h3><ul><li><a href="https://stackoverflow.com/questions/21971449/how-do-i-increase-the-cell-width-of-the-jupyter-ipython-notebook-in-my-browser">Stackoverflow</a></li><li><a href="https://pandas.pydata.org/pandas-docs/stable/user_guide/options.html">Pandas Documentation</a></li><li><a href="https://ipython.readthedocs.io/en/stable/interactive/magics.html#cell-magics">IPython Documentation</a></li></ul><p><em>Originally published at </em><a href="https://www.linkedin.com/pulse/jupyter-notebook-tips-tricks-shyamal-akruvala"><em>https://www.linkedin.com</em></a><em>.</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=73ec6cbe4a7" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Apache Spark’s Logical and Physical Plans Using Explain() Method]]></title>
            <link>https://medium.com/@shyamal.akruvala/apache-sparks-logical-and-physical-plans-using-explain-method-2487b9be537?source=rss-87e8a1d3013b------2</link>
            <guid isPermaLink="false">https://medium.com/p/2487b9be537</guid>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[explain-plan]]></category>
            <category><![CDATA[spark]]></category>
            <dc:creator><![CDATA[Shyamal Akruvala]]></dc:creator>
            <pubDate>Mon, 28 Jun 2021 15:39:34 GMT</pubDate>
            <atom:updated>2021-07-10T01:06:59.906Z</atom:updated>
            <content:encoded><![CDATA[<p>Spark recommends using the structured APIs (DataFrame, DataSet, SQL) compared to low-level RDDs to leverage the awesome power of the Catalyst and Tungsten engines for optimizing the overall workload.</p><p>When dealing with RDDs we inform Spark on <strong>“HOW” </strong>we want the data to be processed. But while using the structured API we inform Spark on <strong>“WHAT”</strong> we want to process and let Spark figure out the best approach on how to do it. Spark created Logical and Physical plans and determines the best plans to implement.</p><p>Code written using the structured APIs, if valid, is converted into a logical plan i.e. a set of abstract transformations without any references to the driver or executors where the code would run. As this includes only syntactically correct code and the references to the various columns / tables are yet to be validated, its called an Unresolved Logical Plan.<br>This Unresolved Logical Plan is passed through an analyzer which validates the columns and tables used in the code again the Spark catalog (repository of DataFrame information), finally “resolving” it. In case a referenced column / table doesn’t exist in the catalog then the analyzer may reject the unresolved logical plan. If all goes well then the output of the analyzer is a Resolved Logical Plan. <br>This Resolved Logical Plan is now given to the Catalyst Optimizer which produces an Optimized Logical Plan based on a set of internal rules.</p><figure><img alt="Diagram showing various stages from Logical to Physical Plan creation" src="https://cdn-images-1.medium.com/max/1024/1*Zi3HueJBqzFY7eOeXCOw_w.jpeg" /><figcaption>Various stages from logical to physical plan creation</figcaption></figure><p>Using the Optimized Logical Plan many different versions of the Physical Plan are generated and passed through a Cost Based selection model. The best physical plan is then selected and executed on the cluster.</p><p>The <strong>explain()</strong> method helps us view these plans. It’s available in Spark version 1.3+ and takes in an <strong>optional boolean parameter </strong>named “<strong>extended</strong>”. By default this parameter is set to False, hence only the Physical Plan is displayed. To view the various Logical Plans, along with the Physical Plan, you’ll need to pass the extended parameter as True <em>E.g. df.explain(extended = True) or df.explain(True)</em></p><p>Let’s review this using an example. Below code snippet creates a dataframe from sample data and then calls the explain() method without and with the extended parameter set to True.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/0b3b98619a1607a341586d6a7202adbc/href">https://medium.com/media/0b3b98619a1607a341586d6a7202adbc/href</a></iframe><p>As we can see below, we only get the Physical Plan information if we do not set the extended parameter to True when calling the explain() method.</p><figure><img alt="Explain plan without the extended parameter set to True. i.e. only Physical Plan is shown" src="https://cdn-images-1.medium.com/max/1024/1*MlE-RTlzEFjqrxhQnP3phA.jpeg" /><figcaption>Explain method call without extended parameter set to True</figcaption></figure><p>When we set set the extended parameter to True, we get all the plans (3 Logicals and 1 Physical)</p><figure><img alt="Explain plan with the extended parameter set to True." src="https://cdn-images-1.medium.com/max/1024/1*Me-V9vpE-ZJkYA8NCUlzpw.jpeg" /><figcaption>Explain method call with extended parameter set to True</figcaption></figure><blockquote>If we carefully observe the plans we can see Catalyst Optimizer at work here. I’ve applied two separate filters in the code and these show up as separate steps in the Parsed or Unresolved Logical Plan and Analyzed or Resolved Logical Plan. Once the plan is given to the Catalyst Optimizer, the filters are combined and optimized into one call as seen in the Optimized Logical Plan.</blockquote><p>In Spark 3.0, a new <strong>optional string parameter </strong>“<strong>mode</strong>” has been added to the explain() method which can be used to specify the format of the plans being displayed. <em>Note: Both “extended” and “mode” cannot be used simultaneosly.<br></em>The mode parameter can take values,<br>1. <strong>simple</strong> -&gt; prints only a Physical Plan<br>2. <strong>extended </strong>-&gt; prints both the Logical and Physical Plans<br>3. <strong>codegen </strong>-&gt; prints a Physical Plan and the generated codes if available<br>4. <strong>cost </strong>-&gt; prints a Logical Plan and stats if available<br>5. <strong>formatted </strong>-&gt; prints the Physical Plan and node details</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OPWvVxdpY8xkFFlmhT_NUA.jpeg" /><figcaption>Passing extended and mode together results in error</figcaption></figure><p><em>You can run the code with various values of mode and see the expected output of the plans for deeper analysis.</em></p><h3><strong>References</strong></h3><ul><li><a href="https://spark.apache.org/docs/latest/api/python/reference/api/pyspark.sql.DataFrame.explain.html">Apache Spark Docs</a></li><li><a href="https://www.amazon.in/Spark-Definitive-Guide-Matei-Zaharia/dp/935213706X/ref=sr_1_1?crid=3S481MIT3TX68&amp;dchild=1&amp;keywords=spark+the+definitive+guide&amp;qid=1624814966&amp;sprefix=spark+the+de%2Caps%2C268&amp;sr=8-1">Spark: The Definitive Guide by Bill Chambers and Matei Zaharia</a></li><li><a href="https://www.youtube.com/watch?v=Ofk7G3GD9jk">A Tale of Three Apache Spark APIs: RDDs, DataFrames, and Datasets — Jules Damji</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=2487b9be537" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Overcoming Parquet Schema Issues]]></title>
            <link>https://medium.com/@shyamal.akruvala/overcoming-parquet-schema-issues-4f7ae38d1c4a?source=rss-87e8a1d3013b------2</link>
            <guid isPermaLink="false">https://medium.com/p/4f7ae38d1c4a</guid>
            <category><![CDATA[parquet]]></category>
            <category><![CDATA[pandas]]></category>
            <category><![CDATA[pyarrow]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[parquet-schema]]></category>
            <dc:creator><![CDATA[Shyamal Akruvala]]></dc:creator>
            <pubDate>Thu, 20 Aug 2020 18:40:46 GMT</pubDate>
            <atom:updated>2021-07-10T01:06:49.332Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uN3G3dR-_2Brm4OzxfF8Uw.jpeg" /><figcaption>Photo by <a href="https://unsplash.com/@genessapana?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Genessa Panainte</a> on <a href="https://unsplash.com/s/photos/spark?utm_source=unsplash&amp;utm_medium=referral&amp;utm_content=creditCopyText">Unsplash</a></figcaption></figure><p>Couple approaches on how we overcame parquet schema related issues when using Pandas and Spark dataframes.</p><p>In my current project we rely solely on parquet files for all our data processing. Initially we extracted data from MS SQL Server into .csv (dat) files and schema into .sch files. Using Spark and iconv we converted these into parquet files, ensuring the schema was always applied before saving into our Hadoop Data Store.</p><p>Recently we moved to using Pandas (with pyodbc) for extractions, and directly uploading to HDFS as parquet using Pandas to_parquet() method. These files needed one more round of processing to apply the schema, which are now safely stored in a central schema repository. This is where we encountered the issue shown below</p><pre><strong>TypeError</strong>: field ActiveFlag: <strong>IntegerType can not accept object</strong> 1.0 <strong>in type &lt;class &#39;float&#39;&gt;</strong></pre><p>Let me explain my analysis</p><figure><img alt="Schema of a table showing the column ActiveFlag as tinyint in the database" src="https://cdn-images-1.medium.com/max/696/1*kHQbBUMIJmJRT7FmKzOL8g.png" /><figcaption>Image by author</figcaption></figure><p>In the screenshot you can see a dummy table (EMP) that I created with the column ActiveFlag as tinyint. Expected values are 0, 1 or null</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/507/1*vptsNQTTB1yN99aJt_aVbA.png" /><figcaption>Image by author</figcaption></figure><p>Created some dummy data to populate the table. Highlighted record is the one we are interested in. <br>As we can see ActiveFlag has integer values 0, 1 and null for last record.</p><p>I read the data in a Pandas dataframe, display the records and schema, and write it out to a parquet file.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/70be4560aa5c5b4ef2130e4acc5ac79d/href">https://medium.com/media/70be4560aa5c5b4ef2130e4acc5ac79d/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/478/1*0RXDPnTuaaFT5a70C0NIMQ.png" /><figcaption>Image by author</figcaption></figure><p>As we see above highlighted, the ActiveFlag column is stored as float64. Values like 0, 1, and null are converted to 0.0, 1.0 and NaN. This is due to Pandas limitation to store null in series of int64 Dtype.</p><p>We now read the file into a Spark dataframe, print the records and review the schema. We see ActiveFlag as double and all columns marked nullable = true</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/f25a01c32370d5a88a2b6f2db4bb99fb/href">https://medium.com/media/f25a01c32370d5a88a2b6f2db4bb99fb/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/600/1*KgC616nylHOsda3gnxIAcw.png" /><figcaption>Image by author</figcaption></figure><p>Let’s create a schema and apply it to the Spark dataframe. The schema gets applied successfully but when we perform a show or a write that’s when Spark will throw a TypeError indicating it cannot convert float into IntegerType.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/e52356c4a9b5952a27ad30d575889247/href">https://medium.com/media/e52356c4a9b5952a27ad30d575889247/href</a></iframe><p>One approach is to create a PyArrow table from Pandas dataframe while applying the required schema and then convert it into Spark dataframe. <br>As seen below the PyArrow table shows the schema and data correctly</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/2412e2d356e0c250fc29c79e9b319069/href">https://medium.com/media/2412e2d356e0c250fc29c79e9b319069/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/489/1*HFXt3ZhH0jNvIxaGAuxqJg.png" /><figcaption>Image by author</figcaption></figure><p>If you do a search on the ways to convert a PyArrow table into a Spark dataframe you’ll most commonly see the to_pandas() method of PyArrow table being called and then Spark’s createDataFrame method on the Pandas dataframe. But if we again convert back to Pandas then the schema would be lost and data messed up. So reviewing the options available we saw the to_pydict() method on the PyArrow table. This would convert the table into an ordered dictionary with schema and data retained correctly. And then from this we can create a Spark dataframe and apply our schema.</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/dee5d456777215ead16351fc95b2c117/href">https://medium.com/media/dee5d456777215ead16351fc95b2c117/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/506/1*rKBjDiwei_4EJcK2AP8AJQ.png" /><figcaption>Image by author</figcaption></figure><p>Another approach I figured out recently is to use Int64 Dtype newly available in Pandas 1.0.0 . We can directly use <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.convert_dtypes.html">convert_dtypes()</a> on the Pandas dataframe when saving the parquet file and it would store the data and datatype correctly. Then we simply read it in Spark and apply our schema. Work done :)</p><iframe src="" width="0" height="0" frameborder="0" scrolling="no"><a href="https://medium.com/media/fcd4cb590f713735c38df599e644cfdc/href">https://medium.com/media/fcd4cb590f713735c38df599e644cfdc/href</a></iframe><figure><img alt="" src="https://cdn-images-1.medium.com/max/505/1*ZH5yU3ig_3lY2tQZHhDBYw.png" /><figcaption>Image by author</figcaption></figure><p><strong>References:</strong><br>1. <a href="https://stackoverflow.com/questions/48578787/convert-ordered-dictionary-to-pyspark-dataframe">https://stackoverflow.com/questions/48578787/convert-ordered-dictionary-to-pyspark-dataframe</a><br>2. <a href="https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.convert_dtypes.html">https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.convert_dtypes.html</a></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=4f7ae38d1c4a" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>