Redshift

Convert Unix epoch time into Redshift timestamps

Many relational databases such as Snowflake, PostgreSQL support functions to convert Unix epoch time into timestamp value. But, Redshift does not support these unix to timestamp functions. You will have to use an alternate approach to convert epoch format. In this article, we will check how to convert unix epoch time into Redshift timestamps with some examples. Convert Unix epoch time into Redshift timestamps The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1, 1970. Amazon Redshift does not provide specific functions to convert…

Continue ReadingConvert Unix epoch time into Redshift timestamps
Comments Off on Convert Unix epoch time into Redshift timestamps
August 18, 2020
Redshift

Amazon Redshift Validate Date – isdate Function

Many databases such as SQL Server supports isdate function. Amazon Redshift supports many date functions. We have already discussed Redshift date functions in my other post, "Commonly used Redshift Date Functions". You may have noticed, there is no function to validate date and timestamp values in Amazon Redshift. In this article, we will check how to validate date in Redshift using isdate Python user defined function with an example. Redshift Validate Date using isdate User Defined Function The best part about Redshift is that you can write user defined function…

Continue ReadingAmazon Redshift Validate Date – isdate Function
Comments Off on Amazon Redshift Validate Date – isdate Function
August 11, 2020
Redshift

Redshift Recursive Query Example

You can use recursive query to query hierarchies of data, such as an organizational structure, bill-of-materials, and document hierarchy. Redshift does not support all features that are supported in PostgreSQL. One of such features is Recursive CTE or VIEWS. Redshift does not support either WITH RECURSIVE Clause or using the RECURSIVE Clause in a CREATE VIEW Statement. In this article, we will check Redshift Recursive Query Alternative with an working example. Redshift Recursive Query Amazon Redshift, a fully-managed cloud data warehouse, now adds support for Recursive Common Table Expression (CTE)…

Continue ReadingRedshift Recursive Query Example
4 Comments
August 9, 2020
Redshift

Redshift Stored Procedure Array Variable Alternative

The ARRAY data type is a composite data value that consists of zero or more elements of a specified specific data type. Most of the relational databases such as Netezza, Teradata, etc. supports array variables to be defined inside stored procedures. As of now, Amazon Redshift does not support array variables. In this article, we will check, Redshift Stored Procedure array variable alternative. Redshift Stored Procedure Array Variable Support As mentioned, Amazon Redshift does not support array variables. In my other article, Amazon Redshift Array Support and Alternatives, we have…

Continue ReadingRedshift Stored Procedure Array Variable Alternative
Comments Off on Redshift Stored Procedure Array Variable Alternative
July 21, 2020
Redshift

How to Export Spark DataFrame to Redshift Table

In my other article How to Create Redshift Table from DataFrame using Python, we have seen how to create Redshift table from Python Pandas DataFrame. In this article, we will check how to export Spark DataFrame to Redshift table. Export Spark DataFrame to Redshift Table Apache Spark is fast because of its in-memory computation. It is common practice to use Spark as an execution engine to process huge amount data. Sometimes, you may get a requirement to export processed data back to Redshift for reporting. We are going to use…

Continue ReadingHow to Export Spark DataFrame to Redshift Table
Comments Off on How to Export Spark DataFrame to Redshift Table
July 10, 2020
Redshift

Rows Affected by Last Redshift SQL Query – Examples

In many situations, you need to know the number of rows affected by an insert, delete or update query in the current session. The popular relational databases such as SQL Server, Snowflake provides system variables that hold the information of records affected by the last SQL statement. Amazon Redshift just like an Azure synapse date warehouse does not provide any system variables. We have to identify the workaround to get rows affected by the last SQL query in Redshift. Redshift Rows Affected by Last SQL Query When you are migrating existing applications such as a Teradata BTEQ scripts to Redshift, you will find it hard…

Continue ReadingRows Affected by Last Redshift SQL Query – Examples
Comments Off on Rows Affected by Last Redshift SQL Query – Examples
July 7, 2020
Redshift

Redshift Temporary Tables, Usage and Examples

Similar to many other relational databases such as Netezza, Snowflake, Oracle, etc. Amazon Redshift support creating temp or temporary tables to hold non-permanent data. i.e. Data which you will use only in the current session and Redshift will drop the temp table soon after the session ends. In this article, we will check how to create Redshift temp or temporary tables, syntax, usage and restrictions with some examples. Redshift Temporary Tables The temporary table in Redshift is visible only within the current session. The table is automatically dropped at the…

Continue ReadingRedshift Temporary Tables, Usage and Examples
Comments Off on Redshift Temporary Tables, Usage and Examples
May 5, 2020
Redshift

TRY_CAST Function Alternative in Redshift – Examples

There are many situations where in CAST conversion fails. For example, let us say you are trying to convert varchar to integer. The cast function will fail if the content is not valid integer values. Data bases such as Snowflake, Azure SQL data warehouse supports try_cast function to safely convert data types. In this article, we will check TRY_CAST function alternative in Redshift and how to use it to safely convert data types of the input values. TRY_CAST Function Alternative in Reshift Before going into details about try_cast alternative in…

Continue ReadingTRY_CAST Function Alternative in Redshift – Examples
Comments Off on TRY_CAST Function Alternative in Redshift – Examples
April 25, 2020
Redshift

Redshift NOT NULL Constraint, Syntax and Examples

Similar to most of the MPP databases such as Snowflake, the Amazon Redshift database allows you to define constraints. The Redshift database does not enforce constraints like primary key, foreign key and unique key. But, it does enforce the NOT NULL constraint. In this article, we will check Redshift NOT NULL constraint, its syntax and usage. Redshift NOT NULL Constraint Constraints other than NOT NULL are created as disabled. Amazon Redshift enforces only NOT NULL. You can create NOT NULL constraint while creating tables. Redshift NOT NULL Constraint Syntax There…

Continue ReadingRedshift NOT NULL Constraint, Syntax and Examples
Comments Off on Redshift NOT NULL Constraint, Syntax and Examples
January 27, 2020
Redshift

Redshift Split String on Delimiter and Examples

Be it relational database management system or any programming language, most common requirement is the split string and extract the particular value of the result. In this article, we will check Redshift split string on delimiter with some examples. Redshift Split String Many relational databases such as Netezza, PostgreSQL, etc, supports array functions. You can use those array functions to extract records from split string result. Unfortunately, Amazon Redshift does not support array functions. Redshift does support split_part string function, you can use this function to split string on any…

Continue ReadingRedshift Split String on Delimiter and Examples
Comments Off on Redshift Split String on Delimiter and Examples
October 7, 2019