Snowflake

What is Snowflake Lateral Join and How to use it?

The lateral join in Snowflakes is similar to that of Hive lateral views. Lateral joins in snowflake behaves more like a correlated sub queries than normal snowflake joins. In this article, we will check what is Snowflake lateral join and how to use it. We will also check the lateral view with FLATTEN keyword and an example. What is Snowflake Lateral Join? In Snowflake, lateral keyword allows an in-line view to reference columns from a table expression that precedes that in-line view, but in some cases it may not refer…

Continue ReadingWhat is Snowflake Lateral Join and How to use it?
Comments Off on What is Snowflake Lateral Join and How to use it?
December 12, 2019
Snowflake

Different Snowflake Join Types and Examples

SQL Join is a clause in your query that is used for combining specific fields from two or more tables based on the common columns available. Snowflake joins are different from the set operators. Joins are used to combine rows from multiple tables. In this article, we will learn about different Snowflake join types with some examples. Test Data Following tables will be used to demonstrate different join types available in Snowflake cloud data warehouse system. S_STUDENTS +----+------+-----------+ | ID | NAME | CITY | |----+------+-----------| | 1 | AAA…

Continue ReadingDifferent Snowflake Join Types and Examples
Comments Off on Different Snowflake Join Types and Examples
December 11, 2019
BigData

Hive Self Join Query, Performance and Optimization

By definition, self join is a join in which a table is joined itself. Self joins are usually used only when there is a parent child relationship in the given data. In this article, we will check how to write self join query in the Hive, its performance issues and how to optimize it. Hive Self Join Query As mentioned earlier, self join is used when there is parent-child relation between your data. For example, consider an employee table. an employee table contains details about the employees and an employee…

Continue ReadingHive Self Join Query, Performance and Optimization
Comments Off on Hive Self Join Query, Performance and Optimization
November 5, 2019
Vertica

Vertica Update Join Syntax – Update using another Table

In a data warehouse, application may have various data sources. All these data sources are stored in tables in the database. Data is collected over the time and it may or may not be accurate. In some cases, you may want to update the table values based on the data available in another table over same or other database on the same server. For example, in a case of slowly changing dimension type 2, you may want to update the dimension table using a stage table that we created to…

Continue ReadingVertica Update Join Syntax – Update using another Table
Comments Off on Vertica Update Join Syntax – Update using another Table
May 28, 2019
Greenplum

Greenplum Different Joins and Examples

Joins in SQL are used to retrieve or combine specific columns from two or more tables based on common data columns available in the tables. Columns from multiple tables are integrated using different joins. In this article, we will check Greenplum different joins and demonstrate using live examples. These joins are same as PostgreSQL joins. Test Data Below are the tables and associated data that we will be using to demonstrate different joins available in Greenplum database. Table1: template1=# select * from table1; id | name | city ----+-------+----------- 1…

Continue ReadingGreenplum Different Joins and Examples
Comments Off on Greenplum Different Joins and Examples
May 10, 2019
BigData

SQL SET Operator MINUS Alternative in Hive and Examples

The set operators in SQL are used to combine similar data set of two or more SELECT statements. Here similar data set literally means the number of columns and its data type should match, otherwise you must explicitly type cast the data types of the values in SELECT statements. Hive does support UNION and UNION ALL set operator, INTERSECT and MINUS are not supported as of now. In this article, we will check SQL set operator MINUS alternative in Hive with an example. SQL SET Operator MINUS Alternative in Hive…

Continue ReadingSQL SET Operator MINUS Alternative in Hive and Examples
Comments Off on SQL SET Operator MINUS Alternative in Hive and Examples
April 3, 2019
Vertica

Different Vertica Join Types and Examples

Joins in SQL are used to combine specific columns from two or more tables based on common data columns available. Multiples tables are integrated using joins. In general, joins are used to combine multiple tables. In this article, we will check different Vertica join types with an example. Test Data Below are the tables and associated data that we will be using to demonstrate different joins available in Vertica database: Stud: VMart=> select * from testdb.stud; Id | name | city ----+-------+----------- 1 | Bill | London 2 | Ram…

Continue ReadingDifferent Vertica Join Types and Examples
Comments Off on Different Vertica Join Types and Examples
April 1, 2019