What is the usage of rownum in hive?

There is no built-in function in Hive similar to ROWNUM in Oracle. ROWNUM is a pseudo-column used in Oracle databases to retrieve the row number of query results.

However, in Hive, you can use the ROW_NUMBER() window function to simulate the functionality of ROWNUM. The ROW_NUMBER() function returns a unique number for each row in the query result. It is typically used with the OVER clause to define partitioning and ordering.

Here is an example of using the ROW_NUMBER() function in Hive:

SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY col1) AS rownum
FROM table_name;

The mentioned query will return a result set containing the original data and the row number for each row. The ROW_NUMBER() function sorts the data based on the values in col1 column and assigns a unique row number to each row.

Please note that the ROW_NUMBER() function is one of the window functions in Hive, and it requires Hive version 0.11.0 or higher to use.