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.…

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…

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…

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…

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…

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

Snowflake Pattern Matching – LIKE, LIKE ANY, CONTAINS, LIKE ALL Conditions

The pattern matching conditions in Snowflake are used to search a string for a given pattern. You can search for the string by matching particular patterns. Snowflake Pattern Matching A pattern-matching operator searches a string for a pattern specified in the conditional expression and returns either Boolean (true/ false) or matching value if it finds a match. These conditions are particularly important when you need to search string patterns in your database column values. Pattern matching conditions are mainly used in WHERE conditions. Following are the commonly used pattern matching…

Comments Off on Snowflake Pattern Matching – LIKE, LIKE ANY, CONTAINS, LIKE ALL Conditions
January 30, 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…

Comments Off on Redshift NOT NULL Constraint, Syntax and Examples
January 27, 2020
Apache Spark

SQL Merge Operation Using Pyspark – UPSERT Example

In the relational databases such as Snowflake, Netezza, Oracle, etc, Merge statement is used to manipulate the data stored in the table. In this article, we will check how to SQL Merge operation simulation using Pyspark. The method is same in Scala with little modification. SQL Merge Statement The MERGE command in relational databases, allows you to update old records and insert new records simultaneously. This command is sometimes called UPSERT (UPdate and inSERT command). Following is the sample merge statement available in RDBMS. merge into merge_test using merge_test2 on…

1 Comment
January 27, 2020
General

SQL and Hive GROUP BY Alternative-Example

It is common to write the queries using GROUP BY and HAVING clause to group records or rows. Group by clause use columns in Hive or relational database tables for grouping particular column values mentioned with the group by. But, GROUP BY and DISTINCT operations are costly. It is applicable to both Hive and relational databases. But, in some cases, you can rewrite the queries to remove GROUP BY clause. In this article, we will check what are GROUP BY alternative methods available in Hive and SQL. SQL and Hive…

Comments Off on SQL and Hive GROUP BY Alternative-Example
January 24, 2020
Snowflake

Generate Snowflake Objects DDL using GET_DDL Function

Snowflake is a fully managed cloud data warehouse solution provided on AWS, Azure and GCP. You don't have to manage hardware's and your only task is to manage databases and tables that you create as part of your project development. In this article, we will check one of the administrative tasks, generate DDL for Snowflake objects such as view, tables DDL using built-in GET_DDL function. Snowflake Objects DDL using GET_DDL Function Snowflake provides many useful functions to make developers and administrators task easy. One of such function is GET_DDL function,…

Comments Off on Generate Snowflake Objects DDL using GET_DDL Function
January 22, 2020