DWgeek.com

How to Optimize the Redshift Query Performance?

In most of the cases, we pay lots of attention to improve the performance of the web application, but ignore the back-end SQL performance tuning. Amazon Redshift is a fully managed, petabyte-scale, massively parallel data warehouse that offers simple operations and high performance. Amazon Redshift can run a data model such as production transaction system third-normal-form model, star and snowflake schemas, data vault, or simple flat tables. This article takes you through the most common performance-related opportunities when writing query in Amazon Redshift and gives you concrete guidance on how to optimize…

Comments Off

January 31, 2023

Redshift

Redshift RSQL Control Statements – IF-ELSE-GOTO-LABEL

Amazon Redshift is a data warehousing service provided by Amazon Web Services (AWS). It allows users to store and analyze large amounts of data in a scalable and cost-effective manner. Amazon AWS Redshift RSQL is a command-line client for interacting with Amazon Redshift clusters and databases. Redshift RSQL is similar to Teradata BTEQ and is used to interact with the data stored in a Redshift cluster. In this article, we will check Amazon Redshift RSQL control statements such as IF, ELSE, GOT, LABEL,etc. Redshift RSQL Control Statements Amazon Redshift RSQL…

Comments Off

January 27, 2023

General

How to Connect to Databricks SQL Endpoint from Azure Data Factory?

A Databricks SQL Endpoint is a compute cluster, quite similar to the cluster we have known in the Databricks that allows you execute SQL commands on data objects within the Databricks environment. The Databricks allows you to connect using various tools such as DBT, connect to Notebook using Azure Data Factory, etc. But there is no direct method to connect Databricks SQL endpoint warehouse. In this article, we will check how to connect to Databricks SQL endpoint from Azure Data Factory (ADF). Connect to Databricks SQL Endpoint from Azure Data…

Comments Off

January 27, 2023

General

How to Export SQL Server Table to S3 using Spark?

Apache Spark is one of the emerging Bigdata technology. Due to its in memory distributed and fast computation, you can use it to perform heavy jobs such as analyzing petabytes of data or export millions or billions of records from any relational database to cloud storage such as Amazon S3, Azure Blob or Google cloud storage. In this article, we will check how to export SQL Server table to the Amazon cloud S3 bucket using Spark. We will use PySpark to demonstrate the method. In my other article, we have…

Comments Off

January 19, 2023

General

Connect to SQL Server From Spark – PySpark

Due to its in memory distributed and fast computation, Apache Spark is one of the emerging Bigdata technology. Apache Spark in memory distributed computation allows you to analyze petabytes of data without any performance issue. In this article, we will check one of methods to connect SQL Server database from Spark program. Preferably, we will use PySpark to read SQL Server table. Connection method is similar to that have already discussed for Oracle, Netezza, Snowflake, Teradata, etc. Steps to Connect SQL Server From Spark To access SQL Server from Apache…

Comments Off

January 19, 2023

Snowflake

How to Create Synonym in Snowflake?

Synonyms in relational databases allows you to create easily names for long table, view names, or any other objects such as sequence, procedure, function, materialized view. Databases such as Netezza, Oracle support creating and managing synonyms. The synonyms provide an alternate way of referencing tables or views that present in the current or other databases. The Snowflake database does not support creating synonym yet. In this article, we will check an alternate method similar to create synonym in Snowflake. How to Create Synonym in Snowflake? The Snowflake cloud database does…

Comments Off

December 5, 2022

Apache Spark

Spark SQL Count Distinct Window Function

Windows are commonly used analytical functions in a Spark SQL query. The COUNT is one of such a windows functions that will allow you to count over certain window. Many relational databases such as Oracle support COUNT window function with distinct keyword. However, Spark SQL does not support count distinct window function. Spark SQL Count Distinct Window Function Spark SQL supports count window functions. However, the COUNT window function with distinct keyword is not supported as of now. Following is the example of an Oracle COUNT window function with distinct…

Comments Off

November 29, 2022

Snowflake

Rows Affected by Last Snowflake SQL Query – Example

Need to find out how many rows were inserted, updated, or deleted after running a query in Snowflake? Just like other major databases, Snowflake gives you a direct way to track these changes using built-in system variables. Let's look at how you can easily get the rows affected by your last Snowflake SQL query to build better scripts and workflows. Rows Affected by Last Snowflake DML Statement Page Content Introduction Rows Affected by Last Snowflake SQL Query Example: Getting Rows Affected by an INSERT Query Example: Getting Rows Affected by…

Comments Off

November 16, 2022

Snowflake

Blocks in Snowflake Scripting: Complete Guide with Examples

If you are writing procedural code in Snowflake like loops, branching logic, or error handling you will use blocks in Snowflake scripting. A block is the fundamental structure that groups your SQL and programmatic logic together. In this guide, we will break down exactly how to structure a block, the difference between stored procedures and anonymous blocks, and look at fully runnable examples you can use right away. Blocks in Snowflake Scripting Page Contents Blocks in Snowflake Scripting Types of Blocks in Snowflake Scripting Block in a Stored Procedure Anonymous…

Comments Off

November 9, 2022

Snowflake

Snowflake Scripting Cursor Syntax and Examples

In my other article, we have discussed Snowflake scripting control structures and loops to manipulate Snowflake data. In this article, let us discuss how to define and use the Snowflake scripting cursor within a stored procedure and anonymous block. Snowflake also support JavaScript to write stored procedures, but it does not support cursor when JavaScript used. You can use the JavaScript result as a cursor alternative. Page Content Introduction What is Cursor in Snowflake Scripting? How use Cursors in Snowflake Scripting? Declaring Cursors Opening Cursors Fetching Data from Cursors Closing…

2 Comments

October 4, 2022