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
Apache Spark

Create Spark SQL isdate Function – Date Validation

Many databases such as SQL Server supports isdate function. Spark SQL supports many data frame methods. We have already seen Spark SQL date functions in my other post, "Spark SQL Date and Timestamp Functions". You may have noticed, there is no function to validate date and timestamp values in Spark SQL. Alternatively, you can use Hive date functions to filter out unwanted date. In this article, we will check how to create Spark SQL isdate user defined function with an example. Create Spark SQL isdate Function The best part about…

Continue ReadingCreate Spark SQL isdate Function – Date Validation
Comments Off on Create Spark SQL isdate Function – Date Validation
July 30, 2020
Azure Synapse

Azure Synapse Export Data using sqlcmd – Example

Exporting data to CSV or text format is a common requirement in the data warehouse. Many application accept delimited data as an input. Instead of providing access to actual tables, organizations export data in the form of CSV and provide it for analysis. In this article, we will check how to export Azure Synapse data using sqlcmd command line interface. We will also check different method that you can use sqlcmd to export data. Azure Synapse Export Data using sqlcmd The sqlcmd command line tool is one of the easiest…

Continue ReadingAzure Synapse Export Data using sqlcmd – Example
Comments Off on Azure Synapse Export Data using sqlcmd – Example
July 21, 2020
Azure Synapse

Azure Synapse Update Join Syntax – Update using other Table

In an ETL model, we use fact tables to store data. Initially, data is loaded into stage (intermediate) tables and then finally, cleansed data is loaded to target fact tables. New records are loaded and existing records are updated, You can use MERGE statement or update table using some other table. In this article, we will check Azure synapse update join syntax with an example. Azure Synapse Update Join Many ETL applications such as loading fact tables use an update join statement where you need to update a table using…

Continue ReadingAzure Synapse Update Join Syntax – Update using other Table
Comments Off on Azure Synapse Update Join Syntax – Update using other Table
July 21, 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
General

Cloud Spanner FORMAT_DATE Function and Example

You can use the FORMAT_DATE function to format the string containing a date expression. It is one of the highly used functions to format date. This function is equivalent to to_char and to_date function present in other relational databases. In this article, we will check cloud Spanner FORMAT_DATE function, syntax and some example Cloud Spanner FORMAT_DATE Function The format_date function is used to date expression to required format. Cloud Spanner FORMAT_DATE function formats the date expression containing date according to the specified date format string. Cloud Spanner FORMAT_DATE Function Syntax Following is the FORMAT-DATE syntax. FORMAT_DATE(format_string,…

Continue ReadingCloud Spanner FORMAT_DATE Function and Example
Comments Off on Cloud Spanner FORMAT_DATE Function and Example
July 13, 2020
General

Google Cloud Spanner String Functions, Syntax, Examples

Google Cloud Spanner String Functions are used for various string manipulations in your SQL queries. Cloud Spanner supports most of the standard SQL string functions along with the many extensions to those functions. In my other article, Google Cloud Spanner Regular Expression Functions, we have seen regular expression string functions. In this article, we will check other string functions. Google Cloud Spanner String Functions Following are some of the commonly used Cloud Spanner string functions. String FunctionsDescriptionsBYTE_LENGTH(value)Returns the length of the value in bytes. Value can be string or bytes.CHAR_LENGTH(value)…

Continue ReadingGoogle Cloud Spanner String Functions, Syntax, Examples
Comments Off on Google Cloud Spanner String Functions, Syntax, Examples
July 13, 2020
General

How to Export Spark DataFrame to Teradata Table

In my other article, Steps to Connect Teradata Database from Spark, we have seen how to connect Teradata database from Spark using the JDBC driver. In this article, we will check how to export spark dataframe to a Teradata table using same JDBC drivers. We will also check how to create table our of Spark dataframe if it's not present in the target database. i.e. Teradata database. Export Spark DataFrame to Teradata Table Apache Spark is fast because of its in-memory computation. It is common practice to use Spark as…

Continue ReadingHow to Export Spark DataFrame to Teradata Table
Comments Off on How to Export Spark DataFrame to Teradata Table
July 13, 2020