Hive - Difference between order by and sort by

Order by: the actual ordering of data is performed in a single node.

hive> select * from emp order by ename;

can use asc/ascending for ordering.

data will be order by ename ASCENDING by default.

hive> select * from emp order by ename desc/descending;

data will be order by ename DESCENDING.

Sort by: if data is stored across more than one node, partial sorting is done node wise, but it can't be 100% perfect sorting. Can use ascending/asc , descending/desc for ascending and descending sorting.

Following will display the data sorted by ename in ascending order. 

hive>select * from emp sort by ename asc;

Hive - Database Commands

What is Hive?

Hive is a data warehousing infrastructure for Hadoop. That converts SQL queries to a MapReduce(MR) program, and this MR runs in the YARN (Yet Another Resource Negotiator)  framework. Supports analysis of large data sets stored in Hadoop Distributed File System (HDFS). It was built for Data Mining Applications, which takes to process data from several minutes to several hours. Which is also called Hive Query Language (HQL). 

The following are some commands that are used in HQL.

Show Database: List all installed databases in the system.

hive> Show Databases;

use <dbname>: To set current working DB name by default. 

hive>use siva;

we can see the change, instead hive (default), we can see the change hive (siva). Currently we are working using siva database.

Show Tables: List all tables in the selected database

hive>show tables;

To list all data from table emp in Siva database.

hive> select * from siva.emp;

Limit: Command is used to get 'n' number of records from the table.

To list 5 records from emp table.

hive>select * from emp limit 5;

Describe: Displays a basic structure of a given table.

hive>describe emp;

Describe Extended: To get additional details of table.

hive>describe extended emp;

Show Functions: To list all system functions.

hive>show functions;

Describe Function: To get function syntax and basic information of function. To get function XPath information.

hive>describe function XPath;

Describe Function Extended: To get function syntax and detailed information of function. 

hive>describe function extended Xpath;

!Clear: To clear the screen.

hive>!clear;