General

What is Dateadd Function Alternative in Teradata SQL?

In ANSI SQL, Dateadd function adds the specified value for the specified date or time part to a date, time, or timestamp. However, the same function is not available in Teradata. In this article, we will check what is Dateadd function alternative in Teradata SQL, methods and examples. Dateadd Function Alternative in Teradata SQL Many relational databases and data warehouse appliances such as Snowflake, SQL Server, Netezza, Oracle, etc support date add function to add or subtract days, months, years, hours, minutes, and seconds to date or timestamp value. For…

Continue ReadingWhat is Dateadd Function Alternative in Teradata SQL?
Comments Off on What is Dateadd Function Alternative in Teradata SQL?
September 8, 2020
Snowflake

Convert Unix epoch time into Snowflake Date

Many relational databases such as PostgreSQL support functions to convert Unix epoch time into a date or timestamp value. But, there are no built-in functions available in Snowflake that you can use to convert epoch time. 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 the Snowflake date with some examples. Convert Unix epoch time into Snowflake Date The Unix epoch (or Unix time or POSIX time or Unix timestamp) is the number of seconds that have elapsed since January 1,…

Continue ReadingConvert Unix epoch time into Snowflake Date
Comments Off on Convert Unix epoch time into Snowflake Date
September 7, 2020
Redshift

Best Amazon Redshift Query Tools – SQL Editors

Similar to Snowflake, Amazon Redshift is a cloud based data warehouse and is a component of Amazon web services (AWS). Amazon Redshift supports both ODBC and JDBC connection. There are many companies released a tool that uses Redshift ODBC or JDBC driver to connect Redshift. In this article, we will check some of best Amazon Redshift query tools or SQL editor that you can use. Best Amazon Redshift Query Tools - SQL Editors Many SQL developers are comfortable with the tools to execute queries and play around data. There are…

Continue ReadingBest Amazon Redshift Query Tools – SQL Editors
Comments Off on Best Amazon Redshift Query Tools – SQL Editors
September 4, 2020
Redshift

UNLOAD Redshift Table to S3 and Local

Amazon Redshift unload command exports the result or table content to one or more text or Apache Parquet files on Amazon S3. It uses Amazon S3 server-side encryption. You can unload the result of an Amazon Redshift query to your Amazon S3 data lake in Apache Parquet, an efficient open columnar storage format for analytics. In this article, we will check how to unload Redshift table to Amazon S3 and later download it to the local system using Amazon AWS command line interface (CLI). Unload Redshift Table Unload command unloads…

Continue ReadingUNLOAD Redshift Table to S3 and Local
Comments Off on UNLOAD Redshift Table to S3 and Local
August 26, 2020
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