Unlike other relational databases, Apache Hive does not have any system table that keeps track of size of growing tables. It is difficult to find table size in hive using query. As a part of maintenance, you should identify the size of growing tables periodically. Big tables can cause the performance issue in the Hive.Below are some of methods that you can use to list Hive high volume tables. Use hdfs dfs -du Command Hadoop supports many useful commands that you can use in day to day activities such as…

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…