Apache Hive

Let’s say we have a table name  employee_multiple_depts and each employee in the table is mapped to 3 departments – dept1, dept2 and dept3. select * […]

This is a very useful trick when you have a big Hive script as part of your production jobs and you want to check the consistency […]

This is a common problem if you are bringing data from a legacy system or simply from a system which you don’t have control over. First […]

Let’s say you have a Hive table and the Hive table is pointing at a location or directory which has several sub directories and each subdirectories […]

How to parse information from URL in Hive? Hive offers 2 functions to work with URLS – parse_url and parse_url_tuple. With both functions you can extract […]

Both get_json_object and json_tuple functions in Hive are meant to work with JSON data in Hive.  Let’s create a table with some JSON data to work […]

Let’s consider the below table employee_depts  with 2 columns – ename and dept_list. dept_list is of type array and has the list of departments. CREATE TABLE […]

It is quite reasonable for Hive cli or Beeline users to want to see the database they are currently on and it’s frustrating that this is […]

The difference between static and dynamic partitioning only exists when the partition is being created based on how the partitions are added to the table. Once […]

Let’s see how to move a Hive table from one database to another in action. We are currently inside a database named hirw. Let’s list the […]

Hive by default store all the files behind the Hive table under the warehouse directory. But this location can be overridden during table or later when […]

If you have worked with tools like Toad, DB Visualizer or SQL Server Management Studio you know it is quite easy to select an existing table […]

In this post we will explain the architecture of Hive along with the various components involved and their functions. HiveServer2 HiveServer2 is an improved implementation of […]

LATERAL VIEW and EXPLODE are 2 different things in Hive. Lateral view is used in conjunction with user-defined table generating functions such as explode().  Problem Let’s […]

Comparing 2 dates is quite common when you deal with data. Hive has datediff function to help you compare 2 dates. Solution datediff function in Hive […]

Simple problem with a simple solution. Solution Order the records first and then apply the LIMIT clause to limit the number of records. SELECT * FROM […]

Simple problem with a simple solution. Solution Use the powerful regexp_replace function to replace characters. regexp_replace(string INITIAL_STRING, string PATTERN, string REPLACEMENT): Replace tab in the string […]

Pretty simple problem with a simple solution. Solution CURRENT_DATE will give the current date and CURRENT_TIMESTAMP will give you the date and time. 0: jdbc:hive2://ms2.hirw.com:2181,wk1.hirw.co> SELECT […]

Sometimes you would have a need to list all the columns in a table. You can do this pretty easily if you are using a RDBMS […]