Apache Hive DUAL Table Support and Alternative

  • Post author:
  • Post last modified:October 28, 2019
  • Post category:BigData
  • Reading time:4 mins read

Apache Hive like many other relational databases does not support dual table. You can simply use the SELECT without FROM clause to display the results of the function or expression that you are testing. But, it may cause a problem when you are migrating from Oracle to Hive. You may find a lot of queries using dual tables. In this article, we will check what is the dual table alternative in Hive and how to use it.

Apache Hive DUAL Table Support and Alternative

What is DUAL table in Relational Databases?

In relation databases, the DUAL is special one row, one column table present by default in Relational databases. The database like, oracle automatically create dual table and grant SELECT access to all users by default.

The dual table has one column, DUMMY, defined to be VARCHAR2(1) or CHAR(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement or evaluating user defined functions.

Apache Hive DUAL Table Alternative

When migrating data from Oracle environments, you may have noticed that the user may have used the DUAL table to test the database connection or perform basic computations. The dual tables are also used for evaluating user defined functions. 

In Apache Hive, there is no DUAL table. You may want to implement an object that can act as an equivalent dual table so you may have to keep source queries as it is.

How to Create DUAL Table in Hive?

As mentioned earlier, the dual table has one column, DUMMY, defined to be CHAR(1), and contains one row with a value X. Simply create a table named DUAL with column as DUMMY of type CHAR(1) which hold value ‘X’.

For examples, below is the DUAL example. 

create table dual(dummy char(1));

Insert value ‘X’ in it. For example,

 insert into dual values('X');

Note that, you have to create a dual table in all your Hive databases. You can add this table schema in your automated script that create database on your Hive cluster.

Now, you can directly migrate scripts which is using DUAL table without modifying the source scripts.

For example, below is the sample example.

 select current_date from dual;

+-------------+--+
|     _c0     |
+-------------+--+
| 2019-10-28  |
+-------------+--+
1 row selected (0.253 seconds)

Related Articles,

Hope this helps 🙂

Tags: ,