AWS Redshift Correlated Subquery and its Restrictions

Redshift Correlated subquery is a query within a query that refer the columns from the parent or outer query. This kind of subquery contains one or more correlations between its columns and the columns produced by the outer query. Redshift does support the regular and correlated subqueries. In most cases, the Redshift correlated subqueries are used to improve the SQL query performance. Page Contents Introduction to Redshift Correlated Subquery What is Redshift Correlated Subquery? Examples of Redshift Correlated Subquery Redshift Correlated Subquery Performance Considerations Redshift Correlated Subquery Restrictions Conclusion Introduction…

Continue ReadingAWS Redshift Correlated Subquery and its Restrictions
Comments Off on AWS Redshift Correlated Subquery and its Restrictions

Redshift Extract Function Usage and Examples

Redshift extract function extracts the sub field represented by units from the date/time value, interval, or duration specified for column. This function is equivalent to Redshift date_part() function. Page Contents Introduction to String Functions Amazon Redshift Extract Function Redshift Extract Function Datepart or Timepart and Abbreviations Amazon Redshift Extract Function Examples Benefits of Extract Function in Amazon Redshift Conclusion Introduction to String Functions The extract function is very useful when you are working with different heterogeneous data sources. You may have requirement to extract part of timestamp values and pass it to an…

Continue ReadingRedshift Extract Function Usage and Examples
Comments Off on Redshift Extract Function Usage and Examples

Redshift Interval Data Types and Conversion Examples

Amazon Redshift supports interval types in the same way as other postgreSQL databases such as Netezza, Vertica, Oracle, etc. It accepts interval syntax with unit specifications. You have to specify the units along withe interval value. In this article we will check Redshift interval data types and conversion Examples. Redshift Interval Data Types Use an interval literal to identify specific periods of time, such as 10 hours or 6 days. You can use these interval literals in conditions and calculations that involve date-time expressions. Redshift accepts the interval syntax, but…

Continue ReadingRedshift Interval Data Types and Conversion Examples
Comments Off on Redshift Interval Data Types and Conversion Examples

Optimize Query Performance with Redshift Collocated Tables

In a Redshift data warehouse appliance, if two tables use same distribution style and column, then rows for joining columns are on the same data slices. These types of tables are called collocated tables as required data is available in same data slice and less data needs to be moved during query execution. If you join two tables which uses same distribution style and column, then join is called collocated join. In this article, we will check how to optimize the query performance with Redshift collocated tables. How to Create…

Continue ReadingOptimize Query Performance with Redshift Collocated Tables
Comments Off on Optimize Query Performance with Redshift Collocated Tables

What are Redshift Sequence Alternative? – Example

In a database management system, sequence is a named object in an individual database, that can provide the unique value when get next value method. Usually, sequences are used to generate unique numbers that can be used as surrogate key value, for example, you can use that surrogate key as a primary key values. Redshift does not support sequences. But, however, there are alternative methods. In this article, we will check Redshift sequence alternative methods with some examples. Page Contents Introduction to Amazon Redshift What are Sequence in SQL? Benefits…

Continue ReadingWhat are Redshift Sequence Alternative? – Example
Comments Off on What are Redshift Sequence Alternative? – Example

Redshift Explain Command and Examples

Redshift Explain plan Command is used to get information or cost on individual operations required to execute given query. This command provides information on join operations, aggregate functions, etc. It provides information on how your query would be executed on Redshift under current condition. In this article, we will very how Redshift explain command works with an example. Page Content Introduction to Redshift Amazon Redshift Explain Command Redshift Explain Command Syntax Redshift Explain Command Example Redshift Explain Output Operators Benefits of Redshift explain plan Command Conclusion Introduction to Redshift Amazon…

Continue ReadingRedshift Explain Command and Examples
Comments Off on Redshift Explain Command and Examples

Redshift CASE Statement, Usage and Examples

In general, the CASE expression or command is a conditional expression, similar to if-then-else statements found in other languages. CASE is used to specify a result when there are multiple conditions. In this article, we will check how to use Redshift CASE Statement, its syntax and usage with some examples. Post Content Introduction to Redshift CASE Statement Syntax and Examples of CASE Statements in Redshift Simple CASE Statements Searched CASE Statements Usage of CASE Statements in Redshift CASE Statements with Aggregation Redshift CASE Statement with Windows Functions Redshift CASE Statement…

Continue ReadingRedshift CASE Statement, Usage and Examples
Comments Off on Redshift CASE Statement, Usage and Examples

Redshift Dynamic SQL Queries and Examples

When you are working on the various data sources and different kind of SQL queries, there may be situations where you want to generate dynamic SQL queries dynamically based on your requirement. In Redshift you can PREPARE SQL statement and EXECUTE it. Redshift also support use of EXECUTE command in Redshift stored procedures. In this article, we will check Redshift Dynamic SQL Queries, how to PREPARE them and run using EXECUTE statements. The dynamic SQL in Redshift can be useful in many scenarios, such as when you need to execute…

Continue ReadingRedshift Dynamic SQL Queries and Examples
Comments Off on Redshift Dynamic SQL Queries and Examples

Working with Amazon Redshift Stored Procedure

Amazon Redshift supports stored procedures written in PL/pgSQL, a dialect of the PostgreSQL database language. Stored procedures in Redshift are used to encapsulate logic on the database side and can be executed by multiple client applications, improving code reusability and reducing the amount of duplicated code in your applications. In this article, we will check how to create a stored procedure in Redshift using simple examples. Amazon Redshift Stored Procedure Overview Amazon Redshift stored procedures are used to encapsulate the data migration, data validation and business specific logic's and same time…

Continue ReadingWorking with Amazon Redshift Stored Procedure
Comments Off on Working with Amazon Redshift Stored Procedure

How to Export Spark-SQL Results to CSV?

Data plays important role in today's decision making process. Be it online bookstore, e-commerce website or online food delivery applications use user data to provide better customer service. These are many organizations that share data to decision making systems. These companies provide data in the form of flat files or direct access to the source system. Many companies use Spark as an execution engine. In this article, we will check how to export Spark-SQL results to CSV flat file. The created flat files or CSV files then be transported using…

Continue ReadingHow to Export Spark-SQL Results to CSV?
Comments Off on How to Export Spark-SQL Results to CSV?