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 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
Snowflake

Convert Permanent table to Transient Table in Snowflake

Snowflake Transient tables are similar to that of permanent tables only difference is that they don not support fail-safe period. Therefore, cost associated with fail-safe is not applicable to transient tables. You can use transient tables in an ETL design to hold temporary data. In this article, we will check how to convert permanent table to transient table in Snowflake. Transient Table in Snowflake As mentioned earlier, transition tables are similar to managed tables with key difference such as fail-safe is not available. The transient tables are designed for transitory…

Continue ReadingConvert Permanent table to Transient Table in Snowflake
Comments Off on Convert Permanent table to Transient Table in Snowflake
May 2, 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
Snowflake

Different Snowflake Connectors and Explanation

A Snowflake cloud data warehouse supports connection from many leading programming languages. You can use these connectors and drivers to take advantage of Snowflake's unique capabilities and features. Snowflake supports many third-party and native connectors. In this article, we will check different Snowflake connectors, drivers and third-party tools. Different Snowflake Connectors Following is a list of different connectors and drivers available in Snowflake. SnowSQL - Snowflake Command Line InterfaceSnowflake Connector for PythonSnowflake Connector for SparkGo Snowflake DriverSnowflake Connector for KafkaNode.js Driver.NET DriverJDBC DriverODBC DriverSnowCD (Connectivity Diagnostic Tool) Now, let us…

Continue ReadingDifferent Snowflake Connectors and Explanation
Comments Off on Different Snowflake Connectors and Explanation
February 24, 2020
Snowflake

How to Find Snowflake Table Size? – Query and Examples

Snowflake is a fully managed cloud data warehouse solution. It is fast, reliable and comes with near zero management. Similar to many relational databases, Snowflake support many system tables and views. System tables can store the important information such as records count, constraint details, etc. In this article, we will check how to find the Snowflake table size using system tables present in the information schema. How to Find Snowflake Table Size? As mentioned earlier, Snowflake maintains the important information in system tables. One of such view is TABLE_STORAGE_METRICS view.…

Continue ReadingHow to Find Snowflake Table Size? – Query and Examples
Comments Off on How to Find Snowflake Table Size? – Query and Examples
February 17, 2020
Snowflake

Optimize Snowflake Table Structure to Improve Performance

The performance of the Snowflake cloud data warehouse is directly dependent on the optimal table structure and design. Optimizing Snowflake table structure is one of the important aspect to improve the performance of query, data loading and unloading process. In this article, we will check how to optimize the Snowflake table structure to improve query performance. Optimize Snowflake Table Structure There are no specific best practices that you can apply to optimize the table structure. Creating optimal table structure that uses right data type and length is one of the…

Continue ReadingOptimize Snowflake Table Structure to Improve Performance
Comments Off on Optimize Snowflake Table Structure to Improve Performance
February 6, 2020
Snowflake

Snowflake Transient Tables, Usage and Examples

Snowflake Transient tables are similar to permanent tables with the key difference that they do not have a Fail-safe period. The transient tables are similar to temporary tables, but, you have to explicitly drop transient tables at the end of the session. Snowflake Transient Tables Snowflake transient tables persist until explicitly dropped and are available to all users with the appropriate privileges. The transient tables are designed for transitory data that needs to be maintained beyond the current session. Because transient tables do not have a Fail-safe period, they provide…

Continue ReadingSnowflake Transient Tables, Usage and Examples
Comments Off on Snowflake Transient Tables, Usage and Examples
February 5, 2020
Snowflake

Snowflake Temporary Tables, Usage and Examples

Similar to other relational databases, Snowflake support creating temp or temporary tables to hold non-permanent data. i.e. Data which is used in the current session. In this article, we will check how to create Snowflake temp tables, syntax, usage and restrictions with some examples. Snowflake Temporary Tables The temporary table in Snowflake is visible only within the current session. Temporary tables only exist within the session in which they were created and persist only for the remainder of the session. Once the session ends, the system will purge the data…

Continue ReadingSnowflake Temporary Tables, Usage and Examples
Comments Off on Snowflake Temporary Tables, Usage and Examples
February 5, 2020
Snowflake

Snowflake Type of Subqueries and Examples

In general, subquery in a database is a nested query block in a query statement. It is simply a SELECT expression enclosed in a parenthesis. The Subquery may return zero to one or more values to its upper or parent SELECT statements. In this article, we will check Snowflake type of subqueries with an examples. Snowflake Subqueries A subquery in Snowflake is a nested select statement, that return zero or more records to is upper select statement. The outer SELECT statement that contains subquery is sometimes referred to as a…

Continue ReadingSnowflake Type of Subqueries and Examples
Comments Off on Snowflake Type of Subqueries and Examples
February 4, 2020