<?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 Anas Aslam on Medium]]></title>
        <description><![CDATA[Stories by Anas Aslam on Medium]]></description>
        <link>https://medium.com/@aanas_96926?source=rss-f92ffe469504------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*4Bmfi4cbVD39UV76</url>
            <title>Stories by Anas Aslam on Medium</title>
            <link>https://medium.com/@aanas_96926?source=rss-f92ffe469504------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 01:20:08 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@aanas_96926/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[Rethinking SQL: BigQuery’s Pipe Operator]]></title>
            <link>https://medium.com/google-cloud/rethinking-sql-bigquerys-pipe-operator-e537cb75dae2?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/e537cb75dae2</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[sql]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data-warehouse]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Fri, 25 Oct 2024 12:08:28 GMT</pubDate>
            <atom:updated>2024-10-26T04:45:09.154Z</atom:updated>
            <content:encoded><![CDATA[<p>BigQuery, Google Cloud’s powerful data warehouse, has introduced a feature that’s transforming how we write SQL: the pipe operator (|&gt;). This seemingly simple addition brings a new level of clarity and efficiency to your queries, making complex data transformations feel intuitive and manageable.</p><h3>A New Era for SQL Clarity</h3><p>Traditional SQL, while robust, can sometimes become a maze of nested subqueries and convoluted clauses, especially when dealing with intricate logic. The pipe operator offers a refreshing alternative:</p><ul><li><strong>Sequential Flow:</strong> Instead of nesting statements within each other, the pipe operator lets you chain them sequentially. This creates a more natural, top-to-bottom flow that mirrors how we often think about data processing steps.</li><li><strong>Enhanced Readability:</strong> By breaking down your query into a series of distinct stages, pipes make it significantly easier to understand the logic and the transformations applied at each step. This is invaluable for both the developer writing the query and anyone else who needs to understand or maintain it later.</li><li><strong>Improved Maintainability:</strong> Debugging, modifying, or extending your queries becomes less daunting when your code is organized into clear, manageable segments. Pipes naturally encourage this modularity, making your SQL more robust and adaptable to changing requirements.</li></ul><h3>The Power of Pipes: Use Cases</h3><p>The pipe operator shines in various scenarios, but it’s particularly well-suited for these situations:</p><ul><li><strong>Sequential Transformations:</strong> When your data needs to undergo a series of operations, such as filtering, aggregation, and joining, pipes provide a clean and intuitive way to express this flow. Each step builds upon the previous one, creating a logical progression.</li><li><strong>Complex Logic:</strong> For queries involving multiple levels of nesting or intricate conditions, pipes can help simplify the structure and improve readability. By breaking down the complexity into smaller, more manageable chunks, pipes make it easier to grasp the overall logic.</li><li><strong>Data Exploration and Analysis:</strong> When you’re exploring data and experimenting with different transformations, pipes offer a flexible and interactive way to build and refine your queries. You can easily add, remove, or reorder steps in the pipeline to see how they affect the results.</li></ul><h3>A Versatile Tool in Your SQL Arsenal</h3><p>The pipe operator isn’t just about chaining statements together. It seamlessly integrates with a wide range of SQL clauses and features, making it a versatile tool for various data manipulation tasks:</p><ul><li><strong>Data Modification:</strong> Use pipes with clauses like EXTEND, SET, DROP, and RENAME to modify the structure and content of your data in a clear, step-by-step manner.</li><li><strong>Query Refinement:</strong> Combine pipes with ORDER BY and LIMIT to refine your results and focus on the data that matters most.</li><li><strong>Advanced Techniques:</strong> Integrate pipes with WITH clauses to define named subqueries, improving code organization and reusability. You can even incorporate User-Defined Functions (UDFs) within your pipe workflows to apply custom logic or transformations.</li></ul><h3><strong>Example</strong></h3><p>Lets assume, we wanted to find the total sales for each product in each store, ordered by the store id and total sales in descending order</p><p><strong>Standard SQL :</strong></p><blockquote>“SELECT<br> store_id,<br> product_name,<br> SUM(sales_amount) AS total_sales<br> FROM<br> `sales_data`<br> GROUP BY<br> store_id, product_name<br>ORDER BY<br> store_id DESC, total_sales DESC”</blockquote><p><strong>Pipe Syntax SQL :</strong></p><blockquote>FROM `sales_data`<br>|&gt; AGGREGATE SUM(sales_amount) AS total_sales<br> GROUP AND ORDER BY store_id DESC, total_sales DESC;</blockquote><p>Here GROUP BY and ORDER BY are clubbed together , so that the columns doesnot needs to be repeated. Also PIPE Syntax provides the clear understanding of the purpose of the SQL in am easy way to read and write.</p><p>By embracing the pipe operator, you unlock a more streamlined and efficient way to write SQL in BigQuery. Experience the clarity and conciseness it brings to your data transformations.</p><p><strong>References:</strong></p><ul><li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax">Pipe query syntax | BigQuery | Google Cloud</a></li><li><a href="https://cloud.google.com/bigquery/docs/reference/standard-sql/pipe-syntax#pipe_operators">Pipe query syntax | BigQuery | Google Cloud</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e537cb75dae2" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/rethinking-sql-bigquerys-pipe-operator-e537cb75dae2">Rethinking SQL: BigQuery’s Pipe Operator</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[LLM-Powered SQL Validator: Ensuring Seamless Database Migrations]]></title>
            <link>https://medium.com/google-cloud/llm-powered-sql-validator-ensuring-seamless-database-migrations-b30945daebac?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/b30945daebac</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[migration]]></category>
            <category><![CDATA[translation]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Fri, 27 Sep 2024 11:02:29 GMT</pubDate>
            <atom:updated>2024-09-30T06:56:32.907Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<p>Migrating databases to BigQuery can be a complex process, especially when it comes to translating SQL from a legacy system. Ensuring the accuracy of these translations is critical for a smooth transition and preventing downstream data issues. This blog post introduces an LLM-based utility tool designed to identify any translation errors between your source database SQL and its BigQuery equivalent.</p><h3>Code</h3><p><a href="https://github.com/GoogleCloudPlatform/professional-services/tree/main/tools/llm_based_sql_translation_validator">https://github.com/GoogleCloudPlatform/professional-services/tree/main/tools/llm_based_sql_translation_validator</a></p><h3>The Challenge of SQL Translation</h3><p>Different database systems often have unique syntax, functions, and data types. This can make it challenging to ensure that SQL queries written for one system will function correctly after being translated to BigQuery. Manual validation can be time-consuming and error-prone, especially for large-scale migrations.</p><h3>Introducing the LLM-Based SQL Translation Validator</h3><p>This innovative tool leverages the power of Large Language Models (LLMs) to automate the validation process. It compares your source SQL with its corresponding BigQuery SQL, identifying any discrepancies in syntax, logic, or data types. This early detection of translation errors can save significant time and effort during the migration process.</p><h3>Key Features and Benefits</h3><ul><li><strong>Automated Validation:</strong> Eliminates the need for manual comparison of SQL queries, reducing human error and accelerating the migration process.</li><li><strong>Early Error Detection:</strong> Identifies translation errors early in the migration process, preventing costly rework and data inconsistencies later on.</li><li><strong>Increased Confidence:</strong> Provides a clear understanding of translation accuracy, instilling confidence in the migrated queries before deployment.</li><li><strong>Simplified Deployment:</strong> Built entirely using BigQuery SQL, requiring no additional infrastructure or complex deployment scripts.</li><li><strong>Generic Applicability:</strong> Can be used to compare SQL from any database to BigQuery SQL, making it a versatile tool for various migration scenarios.</li></ul><h3>How it Works</h3><ol><li><strong>Input:</strong> The tool takes as input the source SQL and its translated BigQuery SQL equivalent.</li><li><strong>LLM Analysis:</strong> An LLM analyzes both SQL statements, comparing their syntax, functions, and data types.</li><li><strong>Error Identification:</strong> The LLM identifies any inconsistencies or differences in logic between the two statements.</li><li><strong>Output:</strong> The tool provides a JSON output detailing each identified difference, its potential impact, and suggested corrections.</li></ol><h3>Getting Started</h3><p>To start using the LLM-Based SQL Translation Validator, follow these simple steps:</p><ol><li><strong>Create a Dataset:</strong> Create a new dataset in your chosen region within BigQuery.</li><li><strong>Establish a Connection:</strong> Create a BigQuery connection of type “Cloud Resource” in the same region.</li><li><strong>Store Input Files:</strong> Create a Cloud Storage location to store your source and target SQL files.</li><li><strong>Deploy Procedures:</strong> Deploy the two stored procedures, run_sql_validator and call_llm, by executing the provided SQL scripts in your dataset.</li><li><strong>Execute the Validator:</strong> Call the run_sql_validator stored procedure with the necessary input parameters, including the dataset name, connection name, source database type, and Cloud Storage paths for input and output files.</li><li><strong>View Results:</strong> Query the validation_report view to see the validation results.</li></ol><p><strong>By leveraging the power of LLMs, this SQL Translation Validator streamlines the database migration process, ensuring accuracy and saving valuable time.</strong></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b30945daebac" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/llm-powered-sql-validator-ensuring-seamless-database-migrations-b30945daebac">LLM-Powered SQL Validator: Ensuring Seamless Database Migrations</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[A Gemini-Powered Guide to Bigquery Query Optimization]]></title>
            <link>https://medium.com/google-cloud/a-gemini-powered-guide-to-bigquery-query-optimization-815025e182c6?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/815025e182c6</guid>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[gemini]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Fri, 27 Sep 2024 10:44:31 GMT</pubDate>
            <atom:updated>2024-09-28T05:09:08.965Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<p>This blog post explores a powerful technique for optimizing BigQuery SQL queries using Gemini function calling. The process involves retrieving a query plan for a given BigQuery job ID, analyzing the plan to identify performance bottlenecks, and providing tailored recommendations for optimization.</p><h3>Source Code:</h3><blockquote><a href="https://github.com/GoogleCloudPlatform/professional-services/blob/main/tools/bigquery_optimization_recommender_using_query_plan/Bigquery_Optimization_Recommender_using_Query_Plan.ipynb"><em>https://github.com/GoogleCloudPlatform/professional-services/blob/main/tools/bigquery_optimization_recommender_using_query_plan/Bigquery_Optimization_Recommender_using_Query_Plan.ipynb</em></a></blockquote><h3>The Optimization Process</h3><ol><li><strong>Input:</strong> The process starts with the user providing a BigQuery job ID. This ID corresponds to a specific SQL query that has been executed in BigQuery.</li><li><strong>Retrieving the Query Plan:</strong> Using Gemini function calling, the BigQuery job API is called to retrieve the query plan for the given job ID. The query plan is a JSON representation of the steps BigQuery takes to execute the query.</li><li><strong>Analysis:</strong> The query plan is analyzed to identify potential performance bottlenecks. This may include full table scans, inefficient joins, complex filtering, or suboptimal use of resources.</li><li><strong>Recommendations:</strong> Based on the analysis, tailored optimization recommendations are provided. These may include creating indexes, partitioning tables, or rewriting the query.</li></ol><h3>Benefits of Using Gemini Function Calling</h3><ul><li><strong>Automation:</strong> Automates the process of retrieving the query plan and analyzing it, saving time and effort.</li><li><strong>Accuracy:</strong> Leverages Gemini’s advanced language processing capabilities to accurately identify performance bottlenecks.</li><li><strong>Personalization:</strong> Provides tailored recommendations for optimization based on the specific characteristics of the query.</li></ul><h3>Conclusion</h3><p>Optimizing BigQuery SQL queries can significantly improve their performance, leading to faster results and reduced costs. Gemini function calling provides a powerful and efficient way to automate this optimization process. By analyzing the query plan and providing tailored recommendations, Gemini helps users to get the most out of BigQuery.</p><p><strong>Note:</strong> This blog post is based on the information provided in the notebook and is intended for informational purposes only. Always refer to the official BigQuery documentation for the latest and most comprehensive information on query optimization.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=815025e182c6" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/a-gemini-powered-guide-to-bigquery-query-optimization-815025e182c6">A Gemini-Powered Guide to Bigquery Query Optimization</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Streamlining Data Pipelines with BigQuery Workflows]]></title>
            <link>https://medium.com/google-cloud/streamlining-data-pipelines-with-bigquery-workflows-35d1f9d94bac?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/35d1f9d94bac</guid>
            <category><![CDATA[orchestration]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Thu, 26 Sep 2024 12:38:14 GMT</pubDate>
            <atom:updated>2024-09-30T02:34:18.433Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<p>As data pipelines grow in complexity, managing dependencies and ensuring reliable execution becomes crucial. BigQuery workflows provide a robust solution for orchestrating tasks within BigQuery, streamlining your data processes and improving efficiency.</p><p><strong>What are BigQuery Workflows?</strong></p><p>Powered by Dataform, BigQuery workflows enable the scheduling and execution of SQL workloads and scripts. This allows for the automation of recurring tasks, such as data ingestion, transformation, and loading, as well as more complex operations like model training and report generation.</p><p><strong>Key Benefits:</strong></p><ul><li><strong>Simplified Orchestration:</strong> Define dependencies between tasks, ensuring they execute in the correct order.</li><li><strong>Increased Reliability:</strong> Workflows offer built-in error handling and retry mechanisms for improved robustness.</li><li><strong>Enhanced Productivity:</strong> Automate repetitive tasks, freeing up valuable time for data analysis and exploration.</li><li><strong>Improved Maintainability:</strong> Centralize and manage your data pipeline logic in a single, organized workflow.</li></ul><p><strong>Creating and Managing Workflows</strong></p><p>Before creating a workflow, ensure you have:</p><ul><li>Enabled billing for your Google Cloud project.</li><li>Enabled the necessary APIs, including the BigQuery API and the Dataform API.</li><li>The appropriate IAM roles to create and manage workflows.</li></ul><p>Workflows can be created and managed through the Google Cloud console, the bq command-line tool, or the BigQuery API. This flexibility allows you to integrate workflow management into your preferred development environment.</p><p><strong>Workflow Components:</strong></p><ul><li><strong>Workflows:</strong> Define the overall workflow, including the schedule and tasks to be executed.</li><li><strong>Schedules:</strong> Determine the frequency and timing of workflow execution.</li><li><strong>Tasks:</strong> Represent individual units of work within a workflow, such as running a SQL query or executing a script.</li></ul><p><strong>Workflow Execution:</strong></p><p>Each workflow schedule is run by a custom Dataform service account. This service account requires specific permissions to access and execute the tasks defined in the workflow.</p><p><strong>Limitations and Considerations:</strong></p><p>BigQuery workflows are subject to Dataform quotas and limits. Additionally, workflows are currently only available in specific regions. It’s important to review the documentation for the latest information on availability and limitations.</p><p>By leveraging BigQuery workflows, you can streamline your data pipelines, reduce manual effort, and improve the reliability of your data processes.</p><p>To learn more, refer to the official documentation:</p><ul><li><strong>Workflows Introduction:</strong> <a href="https://cloud.google.com/bigquery/docs/workflows-introduction">https://cloud.google.com/bigquery/docs/workflows-introduction</a></li><li><strong>Creating Workflows:</strong> <a href="https://cloud.google.com/bigquery/docs/create-workflows">https://cloud.google.com/bigquery/docs/create-workflows</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=35d1f9d94bac" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/streamlining-data-pipelines-with-bigquery-workflows-35d1f9d94bac">Streamlining Data Pipelines with BigQuery Workflows</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Real-Time Insights with BigQuery Continuous Queries]]></title>
            <link>https://medium.com/google-cloud/real-time-insights-with-bigquery-continuous-queries-a-quick-overview-6d16c8e952ba?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/6d16c8e952ba</guid>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[real-time-analytics]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Thu, 26 Sep 2024 09:40:52 GMT</pubDate>
            <atom:updated>2024-09-28T05:08:12.930Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<p>In today’s data-driven world, the ability to analyze information in real-time is a game-changer. Google BigQuery’s continuous queries provide a powerful solution for just that, allowing you to query streaming data and gain immediate insights.</p><p><strong>What are Continuous Queries?</strong></p><p>Imagine a traditional SQL query as a snapshot of your data at a specific moment. Now, picture a continuous query as a live stream constantly analyzing new data as it flows into your BigQuery tables. This means you can detect trends, identify anomalies, and trigger actions immediately.</p><p><strong>Why Use Continuous Queries?</strong></p><p>Continuous queries open up a world of possibilities for real-time data analysis:</p><ul><li><strong>Time-series analysis:</strong> Track and understand trends in data over time, such as website traffic, sensor readings, or financial transactions.</li><li><strong>Real-time machine learning:</strong> Apply your machine learning models to incoming data for instant predictions, classifications, or anomaly detection.</li><li><strong>Data replication:</strong> Keep your data synchronized across different platforms by continuously replicating it from BigQuery to other databases or systems.</li></ul><p><strong>Getting Started with Continuous Queries</strong></p><p>BigQuery offers flexible ways to create and manage continuous queries:</p><ul><li><strong>BigQuery console:</strong> A user-friendly interface for defining and managing your queries.</li><li><strong>Command-line tool:</strong> Use the bq command-line tool for scripting and automation.</li><li><strong>BigQuery API:</strong> Integrate continuous queries programmatically into your applications.</li></ul><p><strong>Important Considerations:</strong></p><ul><li><strong>Supported Operations:</strong> While versatile, continuous queries support a subset of SQL operations. Make sure your query logic aligns with these.</li><li><strong>Cost Optimization:</strong> Since continuous queries run constantly, optimize them to avoid unnecessary computations and costs.</li><li><strong>Monitoring and Alerting:</strong> Keep an eye on your continuous queries’ performance and resource usage. Set up alerts to be notified of any issues.</li></ul><p><strong>Dive Deeper:</strong></p><ul><li><strong>Continuous Queries Documentation:</strong> <a href="https://cloud.google.com/bigquery/docs/continuous-queries">https://cloud.google.com/bigquery/docs/continuous-queries</a></li><li><strong>Introduction to Continuous Queries:</strong> <a href="https://cloud.google.com/bigquery/docs/continuous-queries-introduction">https://cloud.google.com/bigquery/docs/continuous-queries-introduction</a></li></ul><p>Ready to unlock the power of real-time insights? Start exploring BigQuery continuous queries today!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=6d16c8e952ba" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/real-time-insights-with-bigquery-continuous-queries-a-quick-overview-6d16c8e952ba">Real-Time Insights with BigQuery Continuous Queries</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Data Cleansing Using Bigquery Recursion]]></title>
            <link>https://medium.com/google-cloud/data-cleansing-using-bigquery-recursion-b613d371a5bd?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/b613d371a5bd</guid>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[recursive]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[recursion]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Fri, 03 Mar 2023 06:19:01 GMT</pubDate>
            <atom:updated>2023-03-03T07:20:44.935Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction:</h3><p><strong>Recursion</strong> is a technique in SQL that allows you to define a query that references itself. This can be useful for solving problems that can be broken down into smaller, repetitive problems. For example, a recursive query can be used to generate a list of all the descendants of a given node in a tree.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SbU_JsqhisWH4Y6Sfqibtg.png" /><figcaption>Recursion</figcaption></figure><p><strong>Bigquery Supports Recursive CTE.</strong></p><p>Lets take an example of a recursive CTE</p><pre>WITH RECURSIVE<br>  CTE_1 AS (<br>    SELECT 1 AS iteration<br>    UNION ALL<br>    SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration &lt; 3<br>  )<br>SELECT iteration FROM CTE_1<br>ORDER BY 1 ASC</pre><p>The preceding query produces the following output:</p><pre>+-----------+<br>| iteration |<br>+-----------+<br>| 1         |<br>| 2         |<br>| 3         |<br>+-----------+</pre><p>The parts of the recursive CTE are as follows:</p><ul><li>Recursive CTE name: CTE_1</li><li>Base term: SELECT 1 AS iteration</li><li>Union operator: UNION ALL</li><li>Recursive term: SELECT iteration + 1 AS iteration FROM CTE_1 WHERE iteration &lt; 3</li></ul><p>Now, we have understood what is recursive CTE is and its parts. Lets take an example of how to use recursive CTE for data cleansing use cases.</p><h3>Usecase:</h3><p>Lets assume, we have two tables <strong>emp_address</strong> and <strong>address_cleansing_tbl</strong></p><p>emp_address table has 3 fields ( emp_id, emp_name and <strong>home_address</strong> )<br>address_cleansing_tbl has 3 fields (id, <strong>search_word</strong> and <strong>replacement_word</strong>)</p><p>The requirement is to clean the address field from address_table and making it standardised by using the data from address_cleansing table.<br>This can be done by finding the search_word in the home_address field and replacing it with the replacement_word from the address_cleansing_tbl table.</p><h4>Sample Data:</h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/854/1*fHFyLxve6xj0adzXlMTeIQ.png" /><figcaption>Address Table</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/782/1*IL3whgdbvahVSLPcqPIGBQ.png" /><figcaption>Cleansing Table</figcaption></figure><p>From the above example, we are going to find the word “FNO” and replace it with “Flat No”, “Apt.” with “Apartment” and “:(colon)” with “#(hash)” etc</p><h4>Recursive CTE:</h4><p>Lets see, how to achieve this using recursive CTE</p><pre>WITH<br>  RECURSIVE address_standardization AS (<br>  SELECT<br>    emp_id,<br>    emp_name,<br>    home_address,<br>    REPLACE(home_address,search_word, replacement_word) AS clean_home_address,<br>    1 AS recursion_level,<br>    ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY id ) AS row_id,<br>  FROM<br>    poc_aa_cs.emp_address a<br>  LEFT OUTER JOIN<br>    poc_aa_cs.address_cleansing_tbl b<br>  ON<br>    a.home_address LIKE CONCAT(&#39;%&#39;,b.search_word,&#39;%&#39;) QUALIFY row_id = 1 <br>    --base term<br>           <br>  UNION ALL<br><br>  SELECT<br>    a.emp_id,<br>    a.emp_name,<br>   home_address,<br>    REPLACE(a.clean_home_address,b.search_word, b.replacement_word) AS clean_home_address,<br>    a.recursion_level+1 AS recursion_level,<br>    1 AS row_id,<br>  FROM<br>    address_standardization a<br>  LEFT OUTER JOIN<br>    poc_aa_cs.address_cleansing_tbl b<br>  ON<br>    a.clean_home_address LIKE CONCAT(&#39;%&#39;,b.search_word,&#39;%&#39;)<br>    AND row_id=1<br>  WHERE<br>    b.replacement_word IS NOT NULL )<br>--recursive_term<br><br>SELECT<br>  emp_id,<br>  emp_name,home_address,<br>  clean_home_address,<br>  recursion_level<br>FROM<br>  address_standardization QUALIFY ROW_NUMBER() OVER (PARTITION BY emp_id ORDER BY recursion_level DESC ) =1<br>ORDER BY<br>  emp_id --getting final cleansed word</pre><p>In the base term, we are replacing a word in the home address field with a replacement word. The first word to be replaced is determined by the row_number function.</p><p>In the recursive term, we are iterating the replacement of the base_term’s home_address field until there is no longer search_word to be found.</p><p>In the final select query, we are retrieving only the last recursion level which contains the final cleansed address.</p><h4>Output:</h4><p>Lets see the output of the Recursive CTE</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*yXA1FdYi8Cu2db11V0be9A.png" /><figcaption>Recursion Output</figcaption></figure><p>We could clearly see from the output, by comparing home_address field with the clean_home_address field, that the “FNO” is replaced with “Flat No”, “:(colon) “ is replaced with “#(hash), “Apt.” is replaced with Apartment etc</p><h3>Conclusion:</h3><p>Hence by using a recursion technique , we cleansed the address field iteratively until it is fully cleaned.</p><h3>Remarks:</h3><p>In most cases, looping technique’s can be used to solve the similar problems.However the main purpose of this article is to show how to use the Recursion in Bigquery.</p><p>Incase, if you are writing a looping in Bigquery, feel free to refer my other article on Bigquery Looping Optimization</p><p><a href="https://medium.com/google-cloud/bigquery-looping-optimization-8d575e4b72d0">Bigquery Looping Optimization</a></p><p><strong>I frequently write about Google Cloud Technologies and Optimization Techniques. Feel free to follow me for future articles.</strong></p><h4>Appendix:</h4><p>My other articles from Bigquery for your reference:</p><ul><li><a href="https://medium.com/google-cloud/long-running-query-optimization-guide-for-bigquery-b3bd3fbca629">Long Running Query Optimization Guide for Bigquery</a></li><li><a href="https://medium.com/google-cloud/bigquery-merge-optimization-13fc7147efbf">Bigquery Merge Optimization through Partition Pruning</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=b613d371a5bd" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/data-cleansing-using-bigquery-recursion-b613d371a5bd">Data Cleansing Using Bigquery Recursion</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Bigquery Looping Optimization]]></title>
            <link>https://medium.com/google-cloud/bigquery-looping-optimization-8d575e4b72d0?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/8d575e4b72d0</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[iteration]]></category>
            <category><![CDATA[optimization]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Sun, 05 Feb 2023 10:14:56 GMT</pubDate>
            <atom:updated>2023-03-02T13:18:00.092Z</atom:updated>
            <content:encoded><![CDATA[<p>Looping is a sequence of actions that is continually repeated until a certain condition is reached. In Bigquery, loop runs in a sequence, which means until the previous iteration is completed, the new iteration will not get triggered. Since the iteration happens sequentially, the overall execution time increases.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/682/1*cLQVkWbII8gpaAWKDP4QGA.png" /><figcaption>LOOP</figcaption></figure><p>In this article, i will cover about the steps to parallelize the iteration, provided there is no dependency between the iterations.</p><h4>Example:</h4><p>Lets take an example of an aggregate_table which has 5 columns:<br>dataset_id<br>table_id<br>filter_condition<br>aggregate_function<br>aggregate_value</p><p>The requirement is to calculate the <strong>aggregate_value</strong> field based on the <strong>aggregate_function</strong> and update the aggregate_value field in the <strong>aggregate_table</strong>.</p><blockquote><strong>Sample Data</strong></blockquote><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*fXzk_N0FLqUkTGIblptgTw.png" /><figcaption>Sample Data</figcaption></figure><p>For example,</p><p><strong>In row 1</strong>: aggregate_value = select<strong> MIN(product_price) </strong>from poc_aa_cs.product_tgt table where launch_date = ‘1900–01–03’<br><strong>In row2</strong>: aggregate = select <strong>MAX(product_price)</strong> from poc_aa_cs.product_tgt where launch_date = ‘1900–01–05’</p><p>Let’s use FOR loop to iterate over the rows.</p><h4><strong>Original Procedure:</strong></h4><p>The `aggregate_proc` procedure definiton with sequential execution as below:</p><pre>CREATE OR REPLACE PROCEDURE<br>  poc_aa_cs.aggregate_proc ()<br>BEGIN<br>DECLARE<br>  aggregate_statement string; -- the aggregation statement<br>DECLARE<br>  update_statement string; -- the update table statement<br>DECLARE<br>  var_agg_value float64; -- the output of the aggregate function<br>  FOR iterator IN (<br>  SELECT<br>    *<br>  FROM<br>    poc_aa_cs.tbl_aggregate ) DO<br>SET<br>  aggregate_statement = &quot;select &quot;||iterator.aggregate_function||&quot; from &quot;||iterator.dataset_id||&quot;.&quot;||iterator.table_id||&quot; where &quot;||iterator. filter_condition;<br>EXECUTE IMMEDIATE<br>  (aggregate_statement) INTO var_agg_value; -- storing the aggregate output<br>SET<br>  update_statement = &quot;update poc_aa_cs.tbl_aggregate set aggregate_value=(&quot;||var_agg_value||&quot;) where dataset_id=&#39;&quot;||iterator.dataset_id ||&quot;&#39; and table_id=&#39;&quot;||iterator.table_id ||&quot;&#39; and aggregate_function=&#39;&quot;||iterator.aggregate_function||&quot;&#39;&quot;;<br>EXECUTE IMMEDIATE<br>  (update_statement); -- updating the table with aggregate value<br>END<br>  FOR;<br>END<br>  ;<br>CALL<br>  poc_aa_cs.aggregate_proc ();</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*1oiyPh_o1MeZHMcJ1Y1mCQ.png" /><figcaption>Approach 1: Performance Results</figcaption></figure><p>The variable “aggregate_statement” contains the sql statement for aggregation calculation and the variable “update_statement” contains the sql statement to update the value of the aggregate_value field.</p><p>Here, the procedure is iterating over all the rows in the source table (tbl_aggregate) one by one, hence the overall execution time is 5mins 12 secs.</p><h4>Optimized Procedure with Parallelism:</h4><p>Now, lets see how this procedure can be changed, so it is compatible for parallel execution.</p><p>In order to do it, we will create five new variables</p><p><strong>total_partition_count</strong>: Total number of parallel execution needed<br><strong>partition_id</strong>: The partition to pick from the total_partition<br><strong>v_record_count</strong>: Total record count from the source table<br><strong>v_start_rownum</strong>: Starting row number from the source table<br><strong>v_end_rownum</strong>: Ending row number from the source table</p><p>Here, we are going to split the execution based on the row_number of the table.</p><pre>CREATE OR REPLACE PROCEDURE<br>  poc_aa_cs.aggregate_proc_parallel (IN total_partition_count int64,<br>    IN partition_id int64)<br>BEGIN<br>DECLARE<br>  aggregate_statement string;<br>DECLARE<br>  update_statement string;<br>DECLARE<br>  var_agg_value float64;<br>DECLARE<br>  v_record_count INT64;-- total record count from source table<br>DECLARE<br>  v_start_rownum INT64;-- starting row number<br>DECLARE<br>  v_end_rownum INT64;-- ending row number<br>SET<br>  v_record_count=(<br>  SELECT<br>    COUNT(1)<br>  FROM<br>    poc_aa_cs.tbl_aggregate);<br>SET<br>  v_start_rownum=((partition_id-1)*CAST(TRUNC((v_record_count/total_partition_count), 0) AS int64)) +1;<br>IF<br>  (partition_id &lt;&gt; total_partition_count) THEN<br>SET<br>  v_end_rownum=(partition_id*CAST(TRUNC((v_record_count/total_partition_count),0) AS INT64));<br>END IF<br>  ;<br>IF<br>  (partition_id = total_partition_count) THEN<br>SET<br>  v_end_rownum=(partition_id*CAST(TRUNC((v_record_count/total_partition_count), 0) AS INT64))+(total_partition_count-1);<br>END IF<br>  ; CREATE TEMP TABLE aggregate_temp AS (<br>  SELECT<br>    dataset_id,<br>    table_id,<br>    aggregate_function,<br>    filter_condition,<br>    ROW_NUMBER() OVER (ORDER BY insert_time ASC) AS row_num<br>  FROM<br>    poc_aa_cs.tbl_aggregate QUALIFY row_num BETWEEN v_start_rownum<br>    AND v_end_rownum); FOR iterator IN (<br>  SELECT<br>    *<br>  FROM<br>    aggregate_temp ) DO<br>SET<br>  aggregate_statement = &quot;select &quot;||iterator.aggregate_function||&quot; from &quot;||iterator.dataset_id||&quot;.&quot;||iterator.table_id||&quot; where &quot;||iterator.filter_condition;<br>EXECUTE IMMEDIATE<br>  (aggregate_statement) INTO var_agg_value;<br>SET<br>  update_statement = &quot;update poc_aa_cs.tbl_aggregate set aggregate_value=(&quot;||var_agg_value||&quot;) where dataset_id=&#39;&quot;||iterator.dataset_id ||&quot;&#39; and table_id=&#39;&quot;||iterator.table_id ||&quot;&#39; and aggregate_function=&#39;&quot;||iterator.aggregate_function||&quot;&#39;&quot;;<br>EXECUTE IMMEDIATE<br>  (update_statement);<br>END<br>  FOR;<br>END<br>  ;<br>CALL<br>  poc_aa_cs.aggregate_proc_parallel (2,<br>    1);</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*bMBesLO18PMnhYG_Fg4mdg.png" /><figcaption>Optimized Procedure with Parallelism</figcaption></figure><h4>Performance Results:</h4><p>For example, you decided to run the procedure in 2 parallel execution.</p><p>Hence, you can invoke the procedure as below:</p><p>call poc_aa_cs.aggregate_proc_parallel (total_partiton_count, partition_id )<br><strong>call poc_aa_cs.aggregate_proc_parallel (2,1); <br>call poc_aa_cs.aggregate_proc_parallel (2,2);</strong></p><p>Assume that the total records in the table is <strong>250</strong>, then<br><strong>aggregate_proc_parallel (2,1)</strong> will process from <strong>row_number 1 to 125</strong><br><strong>aggregate_proc_parallel (2,2) </strong>will process from <strong>row_number</strong> <strong>126 to 250</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*9RLknXlk6n-svVFUFqo0tA.png" /><figcaption>Approach 2: Performance Results</figcaption></figure><h4><strong>Conclusion:</strong></h4><p>By executing parallely [ aggregate_proc_parallel (2,1) and aggregate_proc_parallel (2,2) ],<strong> </strong>the total execution time to complete all the rows went down from 5min 12 sec to 2mins 42 sec.</p><p>Thus the overall process time is reduced due to parallization.</p><h4>Remarks:</h4><p>Here, i wanted to give you the generic approach to split the workload based on the row_number. However based on requirement, splitting logic can be anything. Example in this case, you can even split the workload based on each dataset, table etc and accordingly change the row splitting logic.</p><p><strong>I frequently write about Google Cloud Technologies and Optimization Techniques. Feel free to follow me for future articles.</strong></p><h4><strong>Appendix:</strong></h4><p>My other articles from Bigquery for your reference:</p><ul><li><a href="https://medium.com/google-cloud/long-running-query-optimization-guide-for-bigquery-b3bd3fbca629">Long Running Query Optimization Guide for Bigquery</a></li><li><a href="https://medium.com/google-cloud/bigquery-merge-optimization-13fc7147efbf">Bigquery Merge Optimization through Partition Pruning</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=8d575e4b72d0" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/bigquery-looping-optimization-8d575e4b72d0">Bigquery Looping Optimization</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Convert P_INTERSECT (Teradata) to Bigquery]]></title>
            <link>https://medium.com/google-cloud/convert-p-intersect-teradata-to-bigquery-f088efcb62?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/f088efcb62</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[migration]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[teradata]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Tue, 10 Jan 2023 14:06:58 GMT</pubDate>
            <atom:updated>2023-01-11T12:05:16.738Z</atom:updated>
            <content:encoded><![CDATA[<h3>Introduction:</h3><p>P_INTERSECT is a teradata function which returns the overlapping portion of the two period expressions.</p><p>If the period expressions do not overlap, then the result will be NULL. If one of the period expression is NULL, then the result will be NULL. Else, the result will be a period data type that is comparable to the period expressions.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/928/0*z_xneOfUtM5ZHuxf.png" /><figcaption>Teradata to Bigquery Conversion</figcaption></figure><h3>Prerequisites</h3><ol><li>Basic knowledge of the Teradata data warehouse and its SQL syntax</li><li>Good understanding of Bigquery and its SQL syntax</li></ol><h3>Conversion Logic:</h3><p>Lets say, pe1 and pe2 are two period expressions. In Bigquery, we can either convert period datatype to struct datatype or we can split period column into two columns (start and end columns).<br>eg) BEGIN(pe1) as pe1_start and END(pe1) as pe1_end, BEGIN(pe2) as pe2_start and END(pe2) as pe2_end</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*-ho5Mu11eE5SFKfs5Xbwrg.png" /><figcaption>pe1 P_INTERSECT pe2</figcaption></figure><p>The conversion logic of pe1 P_INTERSECT pe2 is as follows:</p><blockquote><strong>CASE WHEN pe1_start &gt;= pe2_start<br> THEN pe1_start<br> ELSE pe2_start END pe12_start,<br> CASE WHEN pe1_end &lt;= pe2_end<br> THEN pe1_end<br> ELSE pe2_end END pe12_end</strong></blockquote><h4>Example Scenario:</h4><p>Lets says, the requirement is to find overlapping of DW and DM period fields</p><p>The sample data looks as below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*8Qct88m-U9zLY8aGnkIITQ.png" /><figcaption>Sample Data</figcaption></figure><h4>Teradata SQL:</h4><blockquote>select ACCT_ID, DW_PERIOD P_INTERSECT DM_PERIOD from <br>account_table;</blockquote><p>Lets say, DW_PERIOD is split into two columns (DW_EFF_DT and DW_EXPR_DT) and DM_PERIOD is split into two columns (DM_EFF_DT and DM_EXPR_DT)</p><h4>Bigquery SQL:</h4><blockquote>select ACCT_ID,<br> CASE WHEN DW_EFF_DT &gt;= DM_EFF_DT <br> THEN DW_EFF_DT<br> ELSE DM_EFF_DT END DW_DM_EFF_DT,<br> CASE WHEN DW_EXPR_DT &lt;= DM_EXPR_DT<br> THEN DW_EXPR_DT<br> ELSE DM_EXPR_DT END DW_DM_EXPR_DT<br> from poc_aa_cs.account_table<br> order by ACCT_ID;</blockquote><h3>Translation Output:</h3><p>The output based on the sample data is as follows:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1002/1*L7tcq3kowhMwIclz8Oogcg.png" /><figcaption>Output of pe1 P_INTERSECT pe2</figcaption></figure><p>We can clearly see that for both the ACCT_ID, only the overlap dates were returned as output.</p><p>After these changes, Bigquery SQL generates the same output as Teradata.</p><h4>APPENDIX:</h4><p>My other articles from Bigquery for your reference:</p><ul><li><a href="https://medium.com/google-cloud/convert-reset-when-teradata-to-google-bigquery-f355ced09872">Convert RESET WHEN (Teradata) to Google BigQuery</a></li><li><a href="https://medium.com/google-cloud/convert-group-by-cube-teradata-to-bigquery-9650b91b12f4">Convert Group By CUBE (Teradata) to Bigquery</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=f088efcb62" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/convert-p-intersect-teradata-to-bigquery-f088efcb62">Convert P_INTERSECT (Teradata) to Bigquery</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Bigquery Merge Optimization through Partition Pruning]]></title>
            <link>https://medium.com/google-cloud/bigquery-merge-optimization-13fc7147efbf?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/13fc7147efbf</guid>
            <category><![CDATA[optimization]]></category>
            <category><![CDATA[partition]]></category>
            <category><![CDATA[bigquery]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[data]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Tue, 10 Jan 2023 10:26:46 GMT</pubDate>
            <atom:updated>2023-02-05T09:42:37.138Z</atom:updated>
            <content:encoded><![CDATA[<p>Lets understand Partition Pruning using the Merge Statement.</p><p>A MERGE statement is a DML statement that can combine INSERT, UPDATE, and DELETE operations into a single statement and perform the operations atomically.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/394/1*_76cESW91LqVZuJjPalEbQ.png" /><figcaption>Bigquery Merge</figcaption></figure><h3><strong><em>Example:</em></strong></h3><p>Consider a product table with primary key as product_id and partitioned by launch_date.</p><p>The example query below, merges products from the staging_product table into the target_product table. If a product is already present in target_product table, then the query updates the product_price field. Otherwise, the query inserts a new product into the target table.</p><p>Assume that the staging_product table contains product_id that falls into several partitions. (launch_dates)</p><h4><strong>Original SQL:</strong></h4><blockquote>MERGE product_tgt T<br>USING product_stg S<br>ON T.product_id = S.product_id and<br><strong>T.launch_date = S.launch_date</strong><br>WHEN MATCHED THEN UPDATE<br>SET T.product_price = S.product_price<br>WHEN NOT MATCHED THEN<br>INSERT (product_id,product_price,launch_date) values (S.product_id,S.product_price,S.launch_date);</blockquote><p>Here, the partition filter is dynamic, which means that the Bigquery needs to compute the partition during the runtime based on the launch_date.</p><p>However <strong>BigQuery cannot prune partitions based on dynamic predicates,</strong> i.e. values computed during the query execution</p><p>The query can be optimized as below:</p><h4><strong>Optimized SQL:</strong></h4><blockquote>DECLARE src_range STRUCT&lt;date_min DATE, date_max DATE&gt;;</blockquote><blockquote>SET src_range =(SELECT STRUCT(MIN(launch_date) AS date_min,MAX(launch_date) AS date_max) FROM product_stg );</blockquote><blockquote>MERGE product_tgt T<br>USING product_stg S<br>ON T.Product_Id = S.Product_Id and<br><strong>T.launch_date &gt;= src_range.date_min and T.launch_date &lt;=src_range.date_max</strong><br>WHEN MATCHED THEN UPDATE<br>SET T.Product_Price = S.Product_Price<br>WHEN NOT MATCHED THEN<br>INSERT (Product_Id,Product_Price,Launch_Date) values (S.Product_id,S.Product_Price,S.Launch_Date);</blockquote><p>BigQuery prune partitions based on <strong>static</strong> <strong>predicates. </strong>Hence we have precomputed date_min, data_max in a separate query and provide those as static values in MERGE SQL.</p><p>min and max technique can be applied for both date and integer based partition column.</p><h4>Performance Results:</h4><p>Lets take some sample data for staging and target table and compare the query scan bytes for both the approaches.</p><p>Consider, that the staging table contains 1 million records that needs to be merged with target table, which contains 7 million records. Both tables are partitioned by launch_date with primary key as product_id.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1000/1*TelYPsca75MO6blFAyHiHQ.png" /><figcaption>staging table count before merge</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/968/1*0n4IBj7_a8dTp0VEVJZIaQ.png" /><figcaption>target table count before merge</figcaption></figure><h4><strong>Approach 1: Un-optimized Merge</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*EmwLDr3zVklWy45z49PFuQ.png" /><figcaption>Approach 1: Scanned Bytes (183 MB)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QuVHponFTHt9a4oANUYWuw.png" /><figcaption>Approach 1: Execution Details</figcaption></figure><h4><strong>Approach 2: Optimized Merge</strong></h4><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*cYNsHnTQYCPkLw38PoGckQ.png" /><figcaption>Approach 2: Scanned Bytes (30 MB)</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Odh1CPgny4-LZko4o8DmvQ.png" /><figcaption>Approach 2: Execution Details</figcaption></figure><h4><strong>Conclusion:</strong></h4><p>As we can clearly see from both the approaches: The Bytes scanned, Bytes Shuffled, Slot time consumed and Elapsed Time from Approach 1 is higher than the Approach 2.</p><p>This is due to the fact that the BigQuery prune partitions based on static predicates rather than dynamic predicates.</p><p>This concepts applies not only for merge operations but also for other operations like join, filter etc</p><p><strong>I frequently write about Google Cloud Technologies and Optimization Techniques. Feel free to follow me for future articles.</strong></p><p><strong>Appendix:</strong></p><p>My other articles from Bigquery for your reference:</p><ul><li><a href="https://medium.com/google-cloud/long-running-query-optimization-guide-for-bigquery-b3bd3fbca629">Long Running Query Optimization Guide for Bigquery</a></li><li><a href="https://medium.com/google-cloud/convert-normalize-teradata-to-bigquery-76deb6af9a40">Convert NORMALIZE (Teradata) to Bigquery</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=13fc7147efbf" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/bigquery-merge-optimization-13fc7147efbf">Bigquery Merge Optimization through Partition Pruning</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Convert Group By CUBE (Teradata) to Bigquery]]></title>
            <link>https://medium.com/google-cloud/convert-group-by-cube-teradata-to-bigquery-9650b91b12f4?source=rss-f92ffe469504------2</link>
            <guid isPermaLink="false">https://medium.com/p/9650b91b12f4</guid>
            <category><![CDATA[data]]></category>
            <category><![CDATA[google-cloud-platform]]></category>
            <category><![CDATA[teradata]]></category>
            <category><![CDATA[group-by]]></category>
            <category><![CDATA[bigquery]]></category>
            <dc:creator><![CDATA[Anas Aslam]]></dc:creator>
            <pubDate>Thu, 24 Nov 2022 05:28:05 GMT</pubDate>
            <atom:updated>2022-11-25T03:28:47.276Z</atom:updated>
            <cc:license>http://creativecommons.org/publicdomain/zero/1.0/</cc:license>
            <content:encoded><![CDATA[<h3>Introduction:</h3><p>Group by CUBE is a teradata extension to the group by clause that allows performing multiple group by clauses in the single statement.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*6LE-cZtDSiKUsV9SXZ9AKQ.png" /><figcaption>CUBE 2n groups</figcaption></figure><p>Given <em>n</em> dimensions, CUBE generates 2<em>n</em> different kinds of groups. Each group is represented as a single row.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/928/1*7dVO0M_dqlKNy3vXLvYlPw.png" /><figcaption>Teradata to Bigquery Conversion</figcaption></figure><h3>Prerequisites</h3><ol><li>Basic knowledge of the Teradata data warehouse and its SQL syntax</li><li>Good understanding of Bigquery and its SQL syntax</li></ol><h3>Conversion Logic</h3><h4>Example Scenario:</h4><p>Lets says, the requirement is to find out the effect of city and state on the sales_amount</p><p>The sample data looks as below</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*rCmcJbDyHMP4s3bOtEzWHQ.png" /><figcaption>Sample Data</figcaption></figure><h4>Teradata SQL:</h4><blockquote>SELECT city, state, SUM(sales_amount) as total_sales<br>FROM sales_tbl<br>GROUP BY CUBE (city, state);</blockquote><h4><strong>Bigquery SQL:</strong></h4><blockquote>SELECT<br> sales_tbl.city AS city,<br> NULL AS state,<br> sum(sales_tbl.sales_amount) total_sales<br> FROM<br> sales_tbl<br> GROUP BY 1</blockquote><blockquote>UNION ALL</blockquote><blockquote>SELECT<br> NULL AS city,<br> sales_tbl.state AS state,<br> sum(sales_tbl.sales_amount) total_sales<br> FROM<br> sales_tbl<br> GROUP BY 2</blockquote><blockquote>UNION ALL</blockquote><blockquote>SELECT<br> sales_tbl.city AS city,<br> sales_tbl.state AS state,<br> sum(sales_tbl.sales_amount) total_sales<br> FROM<br> sales_tbl<br> GROUP BY 1, 2</blockquote><blockquote>UNION ALL</blockquote><blockquote>SELECT<br> NULL AS city,<br> NULL AS state,<br> sum(sales_tbl.sales_amount) total_sales,<br> FROM<br> sales_tbl<br>;</blockquote><h4>Translation Explanation:</h4><p>Since there are two dimensions in the group by cube, (2*2) = 4 groups are created.</p><p><strong>Group 1: Effect of City</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/822/1*Y_52QoZzfUsgOMxyUHhV7g.png" /><figcaption>Group 1</figcaption></figure><p><strong>Group 2: Effect of State</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/818/1*NaBHUWAo1uK6VhrqjBhYfA.png" /><figcaption>Group 2</figcaption></figure><p><strong>Group 3: Effect of City and State</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/992/1*poXGT3h1CGsDDQBpKfPZ8Q.png" /><figcaption>Group 3</figcaption></figure><p><strong>Group 4: Effect of No City and State</strong></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/704/1*seg-4xvId91wmDW4CT8BBg.png" /><figcaption>Group 4</figcaption></figure><p>Hence the final query output is a union all of all the above 4 groups</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/996/1*33k7rjL--cF4Vd8Y59-eXw.png" /><figcaption>Group By CUBE Output</figcaption></figure><p>After these changes, Bigquery SQL generates the same output as Teradata.</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=9650b91b12f4" width="1" height="1" alt=""><hr><p><a href="https://medium.com/google-cloud/convert-group-by-cube-teradata-to-bigquery-9650b91b12f4">Convert Group By CUBE (Teradata) to Bigquery</a> was originally published in <a href="https://medium.com/google-cloud">Google Cloud - Community</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
    </channel>
</rss>