Redshift

SQL GROUPING SETS Alternative in Redshift

In my other articles, we have discussed GROUP BY with CUBE and GROUP BY ROLLUP alternatives. In this article, we will check this SQL GROUPING SETS alternative in Amazon Redshift with an example. The GROUPING SETS option in SQL gives you the ability to combine multiple GROUP BY clauses into one GROUP BY clause. The GROUPING SETS is one of the powerful GROUP BY extension. The group set is a set of dimension columns. Amazon Redshift does not support GROUPING SETS. SQL GROUPING SETS Alternative in Redshift GROUP BY GROUPING…

Continue ReadingSQL GROUPING SETS Alternative in Redshift
Comments Off on SQL GROUPING SETS Alternative in Redshift
April 6, 2021
Redshift

SQL GROUP BY ROLLUP Function Alternative in Redshift

In my other articles, we have discussed GROUP BY with GROUPING SETS and GROUP BY with CUBE alternatives. In this article, we will check the SQL GROUP BY ROLLUP function alternative in Amazon Redshift. The relational databases such as Oracle, Teradata, etc. support GROUP BY ROLLUP function to group the result rows. However, Amazon Redshift does not support GROUP BY ROLLUP function. SQL GROUP BY ROLLUP Alternative in Redshift Similar to GROUP BY with CUBE, the GROUP BY ROLLUP is an extension of the GROUP BY clause that produces sub-total rows. Sub-total…

Continue ReadingSQL GROUP BY ROLLUP Function Alternative in Redshift
Comments Off on SQL GROUP BY ROLLUP Function Alternative in Redshift
April 5, 2021
Redshift

SQL GROUP BY with CUBE Function Alternative in Redshift

In my other articles, we have discussed GROUP BY with GROUPING SETS and GROUP BY ROLLUP alternatives. In this article, we will check the SQL GROUP BY CUBE function alternative in Amazon Redshift. The relational databases such as Oracle, Teradata, etc. support GROUP BY with CUBE function to group the result rows. However, Amazon Redshift does not support GROUP BY with CUBE function. SQL GROUP BY with CUBE Function Alternative in Redshift The GROUP functions such as ROLLUP, CUBE, GROUPING SETS are an extension of the GROUP BY clause. The CUBE allows you to generate subtotals like…

Continue ReadingSQL GROUP BY with CUBE Function Alternative in Redshift
Comments Off on SQL GROUP BY with CUBE Function Alternative in Redshift
April 5, 2021
Redshift

Amazon Redshift CONCAT Function-Syntax and Examples

The data warehouse is built from many heterogeneous data sources. It is a common requirement to combine data from multiple columns or two strings before loading them to target table. For example, you may get requirement to combine state and city columns before loading data to the customer table. In this article, we will check Amazon AWS Redshift CONCAT function, its Syntax and examples. We will also check how to combine two or more columns using Redshift CONCAT operator (||). Amazon Redshift provides multiple methods to concatenate the strings The…

Continue ReadingAmazon Redshift CONCAT Function-Syntax and Examples
Comments Off on Amazon Redshift CONCAT Function-Syntax and Examples
March 16, 2021
Azure Synapse

How to Export Azure Synapse Table to Local CSV using BCP?

It is common practice to offload large table to a flat file such as CSV, TSV or fixed width files. Many application stores data in the form of flat files such as CSV. Managing flat files such as CSV is easy and it is easy to transport using any electronic medium. In this article, we will check how to export Azure Synapse table to local CSV using BCP command? In my other article, we have discussed how to export an Azure table to local CSV using SQLCMD command. Azure Synapse…

Continue ReadingHow to Export Azure Synapse Table to Local CSV using BCP?
Comments Off on How to Export Azure Synapse Table to Local CSV using BCP?
March 9, 2021
Azure Synapse

How to Load Local File to Azure Synapse using BCP?

It is common practice to offload large table to a flat file such as CSV, TSV or fixed width files. Managing flat files such as CSV is easy and it is easy to transport using any electronic medium. In this article, we will check how to load or import local flat file to Azure Synapse using the BCP command with some examples. Load Local Flat File to Azure Synapse using BCP Utility The bulk copy program utility (bcp) bulk copies data from a file in a user-specified format to an instance such as…

Continue ReadingHow to Load Local File to Azure Synapse using BCP?
Comments Off on How to Load Local File to Azure Synapse using BCP?
March 4, 2021
Azure Synapse

Azure Synapse INSERT with VALUES Limitations and Alternative

Azure Synapse Analytics data warehouse is based on TSQL. But, not all TSQL features are available in Azure Synapse dedicated SQL pool. The feature such as using an expression or UDF in VALUES clause of INSERT INTO table statement is not supported. In this article, we will check Azure Synapse INSERT with VALUES limitations and alternative methods. Azure Synapse INSERT with VALUES Limitations The VALUES clause Introduces the list or lists of data values to be inserted. There must be one data value for each column in column_list, if specified, or…

Continue ReadingAzure Synapse INSERT with VALUES Limitations and Alternative
Comments Off on Azure Synapse INSERT with VALUES Limitations and Alternative
March 3, 2021
Azure Synapse

Azure Synapse Analytics Cursor Alternative

SQL Cursor is a database object to retrieve data from a result set one row at a time. Database such as Oracle, Teradata, Microsoft SQL Server support cursors. The cursor are useful in many scenarios such as retrieving records in a loop. SQL Cursor always returns one row at a time, you can perform your calculation on returned values. In this article, we will check what is Azure Synapse Analytics Cursor Alternative with an example. Does Azure Synapse Analytics support SQL Cursor Variable? The Microsoft SQL Server, which is based…

Continue ReadingAzure Synapse Analytics Cursor Alternative
Comments Off on Azure Synapse Analytics Cursor Alternative
February 26, 2021
Azure Synapse

Azure Synapse DROP TABLE IF EXISTS Alternatives

Many relational databases such as Netezza supports DROP TABLE IF EXISTS syntax to safely drop table if it is already present in the database. The Microsoft SQL Server 2016 or higher version supports drop table if exists. However, same command won't work on Azure Synapse dedicated SQL pool server. In this article, we will check what are the Azure Synapse analytics DROP TABLE IF EXISTS alternatives with some examples. Azure Synapse DROP TABLE IF EXISTS Alternatives The DROP TABLE IF EXISTS statement checks the existence of the table in the schema, and…

Continue ReadingAzure Synapse DROP TABLE IF EXISTS Alternatives
Comments Off on Azure Synapse DROP TABLE IF EXISTS Alternatives
February 25, 2021
Azure Synapse

Create External Tables in Azure Synapse Analytics

There are many situations in which you need to access the data without loading it to Azure Synapse analytics. The data may be stored in an external data source such as flat files. Azure Synapse Analytics dedicated SQL pool allows you to query external data available on Azure the data lake or Azure Blob storage. In this article, we will check how to create external tables in Azure Synapse Analytics. Create External Tables in Azure Synapse Analytics Create MASTER KEYCreating a Database Scoped CredentialCreating External Data SourceCreate an External File…

Continue ReadingCreate External Tables in Azure Synapse Analytics
Comments Off on Create External Tables in Azure Synapse Analytics
February 24, 2021