If you have been working on other RDBMS like Oracle, Redshift etc then you will be surprised to know Hive does not support LEFT-RIGHT functions. You will either should write your own UDF’s using Java or find out any other alternatives. In this article, we will check Apache Hive LEFT-RIGHT functions alternative with some examples. Hive LEFT-RIGHT Functions Alternatives Since Hive does not support LEFT-RIGHT function, you could use Hive SUBSTR string function or regexp_extract regular expression function to select leftmost or rightmost characters from the string values. Other possible…

In general, extract function extracts the sub field represented by units from the date/time value, interval, or duration specified for column. Apache Hive does not support extract function, you can use other built in functions to extract required units from date value. In this article, we will check Hive extract function alternative and some examples. Hive Extract Function Alternative There is no extract function in Hive to extract sub part of date values. You can use Hive built in date function date_format() to extract required values from date fields. Below…

CSV or comma separated flat files are most common file system used to transfer data using electronic media. You may get requirement to export data for ad-hoc query or just unload data for subset of columns available in table, in this case export Hive table into CSV format using Beeline client comes into handy. Beeline Separated Value Output Format The values of a row are separated by different delimiters. Starting with Hive 0.14 there are improved output formats available, dsv (custom delimiter), csv2 (comma separated value) and tsv2 (tab separated…

A temporary table is a convenient way for an application to automatically manage intermediate data generated during a large or complex query execution. Hive 0.14 onward supports temporary tables. You can use them as a normal table within a user session. In this article, we will check Apache Hive Temporary tables, examples on how to create and usage restrictions. Apache Hive Temporary Tables Hive temporary tables are local to the user session. You can use temporary table repeatedly within a user session for multiple times. Hive automatically deletes all temporary…

Group concat is a single string representing the argument value concatenated together for each row of the result set. The resulting string is a comma separated values. Many relational databases supports group-concat functionality as a standard built in function. Unfortunately, Hive does not have group_concat function. In this article, we will check Apache Hive group_concat alternative functions and working examples. Apache Hive group_concat alternative Functions As mentioned earlier, Apache Hive does not support group_concat function. You have to use other built in functions available in Hive to perform group_concat. Apache…

Hive is batch processing engine, you cannot use it as a transaction system. Sometimes you may need to generate sequence row number for document use. ROWNUM is sometime useful when you are working with multi-level SQL queries. There is no ROWNUM pseudo column in Apache Hive. In this article, we will check Hive ROWNUM pseudo column equivalent. If you are coming from Oracle or traditional database background, you will find it difficult in Hive without ROWNUM pseudo column. The one possible solution to this is ROW_NUMBER() analytical function as Hive…