What is Hadoop HIVE Query Language

Hive Query Language

 Hive QL is the HIVE QUERY LANGUAGE

 Hive offers no support for row level inserts, updates and deletes.

 Hive does not support transactions.

 Hive adds extensions to provide better performance in the context of hadoop and to integrate with custom extensions and even external programs.

 DDL and DML are the parts of HIVE QL

 Data Definition language (DDL) is used for creating, altering and dropping databases, tables, views, functions and indexes.

 Data manipulation language is used to put data into Hive tables and to extract data to the file system and also how to explore and manipulate data with queries, grouping, filtering, joining etc.

Accelerate your career with Hadoop Training and become expertise in Apache Hadoop.

Databases in Hive:

 The Data bases in the Hive is essentially just a catalog or name space of tables.

 They are very useful for larger clusters with multiple teams and users, as a way of avoiding table name

 Hive provides commands such as

Related Article: CLI Commands In Hadoop With Syntaxes

CREATE DATA BASE db name  to create database in Hive
USE db name  To use the database in Hive.
DROP db name  To delete the database in Hive.
SHOW DATA BASE  to see the list of the DataBase
If no database is specified, tables belong to the default Data Base.

Tables in Hive:

     Hive table is logically made up of the data being stored and the associated metadata describing the layout of the data in the table.

 The data typically resides in HDFS, although it may reside on any Hadoop file system including the local file system.

 Hive stores the metadata in a relational database and not in HDFS.

 The command for creating a table in Hive is

 have>CREATE TABLE EMP (empid int, ename string, esal double)
ROW FORMAT DELIMITED FIELDS TERMINATED By ‘t’ LINES TERMINATED by ‘n’ STORED AS TEXT FILE;

 To display the description of the table we use have>desc emp;

 In have, we are having two types of tables

Managed tables
External tables

1. Managed tables
       Managed tables are the one which will be managed in the Hive warehouse i.e. whenever we create a managed table definition, it will be stored under the default location of the Hive warehouse i.e./user/Hive/ware house.

When we drop a managed table, Hive deletes the data in the table

Managed tables are less convenient for sharing with other tools.

Checkout Hadoop Tutorial

Syntax for creating Hive managed table:-

Hive>create table manage- tab (empid, ename string, esal int) row format delimited fields terminated by ‘t’ lines terminated by ‘m’ stored as a text file;

 As discussed above, the table will be created under/user/Hive/ware house/managed-tab by giving the command as

#hadoop fs –ls/user/Hive/warehouse.

 How to load the data in managed tables

We can load the data in two ways

Local Mode
HDFS Mode
In local mode, the syntax is

hive>load data local in path’/home/new Batch/input1.txt’
Into table managed-tab;

For HDFS mode, the syntax is

hive>load data in path’/user/ramesh/Hive/input2.txt’
Into table managed – tab;
Once the successful loading of the table and once the file is loaded, the file will be deleted in HDFS path and we can see in use/Hive/ware house

2) External Tables:-

     Along with the managed tables, Hive also uses external tables.

     Whenever the key word ‘external’ comes in the table definition part. Hive will not bother about the table definition, i.e. the external table will not be managed by the Hive warehouse system.

     Along with the external keyword, we can also mention the ‘location’ in the table definition, where exactly the table definition will get stored.

     When you drop an external table, Hive leave the data untouched and only delete the meta data.

Syntax:-

Hive>create external table external- tab(empid int, ename string, esal double) 
row format delimited fields
Terminated by ‘f’ lines terminated by ‘n’ stored as text file location 

‘userRameshHive-external’;

??
?Location will be automatically created.

Loading data into External Tables:-

 Loading data from HDFS to

Hive>load data in path’/Ramesh/input data.txt’ into table external-tab;

 Flow of Data in Hive process at the sample location

If we delete the managed table, both the schema and the data file will be deleted.

But, if we delete external tables, only the schema will be deleted and data file will be there in the specified location.

Frequently asked Hadoop Interview Questions

Difference between managed tables & External Tables:-

      One of the main differences between managed and external tables in Hive is that when an external table is dropped, the data associated with it does not get deleted from only the meta data (no. of cols, types of cols, terminators etc.) gets dropped form the Hive meta store

 When a managed table gets dropped, both the metadata and data get dropped.

 I have so far always preferred making table external because if the schema of my Hive table changes, I can just drop the external table and recreate another external table over the same HDFS data with the new schema

Hive>Create external table log in for tab(log id int, log Error string,Log error count int)
row format delimited fields 
terminated by’f’ stored as text file location ‘user/external location’;
Hive>select*from log in for tab;
   

We get the result from the file which we specified in the location path

 For external tables, no need to load the data explicitly.

 However, most of the changes to schema can now be made through ALTER TABLE or similar command

 So, the recommendation to use external tables over managed tables might be more of a legacy concern than a contemporary one.

If you want More Visit Mindmajix
 

Author

Lianamelissa is Research Analyst at Mindmajix. A techno freak who likes to explore different technologies. Likes to follow the technology trends in market and write about them.