In general, fixed-width text files are special types of text files where the row format is specified by column widths, pad character and either left or right alignments. In the fixed width file format, column width is in terms of units of characters. Fixed width format files are usually generated by machines such as switches, SS7 etc. In this article, we will learn about Apache Hive fixed-width file loading options and some examples. Fixed-Width File Overview In general, fixed-length format files use ordinal positions, which are offsets to identify where…

Unlike various relational databases such as Netezza, Teradata, Oracle etc, Apache hive support pattern matching using LIKE, RLIKE or INSTR functions. You can search for string by matching patterns. Note that, Hive LIKE statement is case-sensitive. Apache Hive LIKE statements returns TRUE if string that you are searching for. The Hive NOT LIKE is negation of LIKE and vice-versa. Related reading: Apache Hive Regular Expression Functions Apache Hive String Functions and Examples Hive LIKE Statement Patterns Matching If the string does not contain any percentage sign or underscore, then pattern…

In some application, you may have to derive column values from base tables. For example, you may have to find out maximum value of aggregated column data. In this scenario, you will have to create aggregated data first and then apply MAX function on that column. You can achieve this by using Apache Hive derived tables. We will check type of derived tables supported in Hive with some examples. Apache Hive Derived Tables Apache Hive derived tables is a subquery which will be there in FROM clause of the HiveQL…

Apache Hive Correlated subquery is a query within a query that refer the columns from the outer query. Hive does support some of subqueris such as table subquery, WHERE clause subquery etc, and correlated subqueries. In most cases, the Hive correlated subqueries are used to improve the Hive query performance. Above diagram clearly explains the correlated subqueries in case of relational databases and Apache Hive. Read: Apache Hive Supported Subqueries and Examples Apache Hive Correlated Subquery Examples For example, consider query, “check if student id is already exists in the…

A subquery in Hive is a select expression that is enclosed in parentheses as a nested query block in a HiveQL query statement. The subquery in Hive is like other relational database subquery that may return zero to one or more values to its upper select statements. In this article, we will check Apache Hive supported subqueries and some examples. Apache Hive Supported Subqueries As mentioned above, Hive subquery is a select expression enclosed in parenthesis as a nested query block. You can use these nested query blocks in any…

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…