BigData

SQL SET Operator MINUS Alternative in Impala

The SQL set operators are used to combine data from two or more SELECT statements. The set operators can combine only similar data sets. Here similar data set literally means the number of columns and its data type should match, otherwise you must explicitly type cast the types of the values or columns in the SELECT statements. Just like Apache hive, Impala support only 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…

Comments Off on SQL SET Operator MINUS Alternative in Impala
May 31, 2019
BigData

Cloudera Impala Merge Statement – UPSERT Command

The MERGE query or statement in SQL is used to perform incremental load. With the help of SQL MERGE statement, you can perform UPDATE and INSERT simultaneously based on the condition. i.e. you can update old values and insert new records. The merge command is widely used in incremental load where you have to update old records and insert new records if any. In this article, we will check Cloudera Impala Merge Statement along with some Impala native UPSERT command. In SQL world, the merge statement is also referred to…

Comments Off on Cloudera Impala Merge Statement – UPSERT Command
May 30, 2019
BigData

Impala Dynamic SQL Support and Alternative Approaches

Dynamic SQL lets SQL statements be created and execute at run time, i.e. you can build SQL queries based on the user or application input and execute them to provide required output. For examples, pass a session specific value to the Impala queries dynamically during runtime. In this article, we will check how to build Cloudera Impala Dynamic SQL queries and how to execute them. Most of the relational databases like Netezza, Teradata, etc. supports stored procedure that will allow you to build and execute dynamic queries. Impala Dynamic SQL…

Comments Off on Impala Dynamic SQL Support and Alternative Approaches
May 29, 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…

Comments Off on Vertica Update Join Syntax – Update using another Table
May 28, 2019
BigData

Hive Dynamic SQL Support and Alternative

Dynamic SQL queries are created on the fly and executed. Dynamic SQL lets SQL statements be defined and execute at run time, i.e. you can build SQL queries based on the user input and execute them to provide required output. For examples, pass a session specific value to the HQL queries dynamically during runtime. In this article, we will check how to build Apache Hive Dynamic SQL queries and how to execute them. Hive Dynamic SQL Support Apache Hive version 1.x and Cloudera impala does not support dynamic SQL, you…

Comments Off on Hive Dynamic SQL Support and Alternative
May 27, 2019
Vertica

Vertica Dynamic SQL Support and Alternative

Vertica dynamic SQL lets SQL statements be defined and run at run time, i.e. you can build SQL queries based on the user input and execute them to provide required output. For examples, pass a  date value or session specific values to the SQL queries dynamically. Most of the modern day databases such as Netezz, Oracle, etc support dynamic SQL as a part of stored procedure or functions. Unfortunately, Vertica database does not provide support for dynamic SQL. Hopefully, future versions of Vertica database may provide support to the dynamic…

Comments Off on Vertica Dynamic SQL Support and Alternative
May 26, 2019
General

Execute Java from Python, Syntax and Examples

As per most of the surveys, Python is one of the fastest growing programming language. From web-development to complex scientific calculation, Python is being used in almost all fields. Similar to Python, Java is also one of the widely used programming languages. In this article, we will check how to get access to Java libraries from Python programs. There are lots of modules available to execute Java from Python. We will be discussing one of the such easiest module in this article. Jpype Python Module Python provides many modules that…

Comments Off on Execute Java from Python, Syntax and Examples
May 24, 2019
BigData

Hive Merge Tables Statement – Alternative and Example

The MERGE query or statement in SQL is used to perform incremental load. With the help of SQL MERGE statement, you can perform UPDATE and INSERT simultaneously based on the condition. i.e. you can update old values and insert new records. The MERGE statement in SQL are mainly used to implement slowly changing dimensions. As of now, Hive does not support MERGE statement. In this article, we will check what is Hive Merge tables alternative with an example. Sometimes, update insert is also called UPSERT. Related Article, Slowly changing dimension…

Comments Off on Hive Merge Tables Statement – Alternative and Example
May 22, 2019
General

Teradata Joins, Syntax and Examples

Just like Teradata set operators, you can combine records from multiples tables using Teradata joins. Teradata join syntax is similar to other database SQL joins. You can execute SQL queries with different join types on Teradata machine with little or without any change. There are many advantages of using SQL joins, for example, combine columns from multiples tables, update tables using joins. The best way to understand the Teradata joins is to get to know the SQL joins and then consider the join strategies that is used by Teradata database…

Comments Off on Teradata Joins, Syntax and Examples
May 20, 2019
General

Teradata NVL and NVL2 Function, Syntax and Examples

The Teradata is one of the widely used MPP relational database systems. Most of the organizations are using Teradata for their high-performance servers such as analytics, back-end systems for reporting servers, etc. Teradata is used with many heterogeneous data sources, you may get lots of junk and null values. You should have a mechanism to deal with such a data. In this article, we will check such a null handling functions. i.e. Teradata NVL function, and NVL2 functions with some examples. Teradata NVL Function  Teradata NVL functions replaces a NULL…

Comments Off on Teradata NVL and NVL2 Function, Syntax and Examples
May 14, 2019