In general, data type is an attribute that specifies type of data that is going to be stored in that specific column. Each column, variable and expression has related data type associated with its column in SQL and HiveQL. However, data type names are not consistent across all databases. Hive supports almost all data types that relational database supports. In this article, we will check Apache Hive data types and Best practices. When you issue Apache Hive create table command in the Hadoop environment, each column in a table structure…

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…