What is Hadoop HIVE Query Language

Hive Query Language

Capture 15 Hive QL is the HIVE Query language

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

Capture 15 Hive does not support transactions.

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

Capture 15 DDL and DML are the parts of HIVE QL

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

Capture 15 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.

Databases in Hive:

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

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

Capture 15 Hive provides commands such as

  1. CREATE DATA BASE db name Capture 15 to create database in Hive
  2. USE db name Capture 15 To use the database in Hive.
  3. DROP db name Capture 15 To delete the database in Hive.
  4. SHOW DATA BASE Capture 15 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.

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

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

Capture 15 The command for creating a table in Hive is

 have>CREATE TABLE EMP (empid int, ename string, esal double)

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

Capture 15 In have, we are having two types of tables

  1. Managed tables
  2. 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.

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;

Capture 15 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.

Capture 15 How to load the data in managed tables

We can load the data in two ways

  1. Local Mode
  2. 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.


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 ‘\user\Ramesh\Hive-external’;

                                Location will be automatically created.

Loading data into External Tables:-

Capture 15 Loading data from HDFS to

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

Capture 15 Flow of Data in Hive process at the sample location

hive query language

      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.

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

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

Capture 15 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

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

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

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

Altering Table:

Capture 15 Most table properties can be altered with the ALTER TABLE statement, which change metadata about the table but not the table itself

Capture 15 ALTER TABLE modifies table meta data on.

Capture 15 Then statements can be used to fix mistakes in schema, move partition locations and do other operations.

Renaming a Table:

Capture 15 This statement is used to rename the table Log_messages to log msgs

Cmd: ALTER TABLE log _ messages RENAME To logmsgs;

Changing columns

     You can rename a column, change its position, type or comment.


ALTER TABLE log-messages CHANCE COLUMN hms hours-minutes-

Seconds INT COMMENT ’The hours, minutes and seconds are part of the times tamp’ AFTER Severity;

Capture 15 You have to specify the old name, a new name and the type even if the name or type is not changed.

Capture 15 If you are not moving the column, the AFTER other – column close is not necessary.

Capture 15 In the example shown, we move the column after the column

Capture 15 If you want to move the column to the first position, use FIRST instead of AFTER other – column.

Adding Columns

      You can add new columns to the end of the existing columns, before any partition .

 Example: ALTER TABLE Log-message ADD COLUMNS(app-name String COMMENT” Application Name” ,session-id long);

Deleting or replacing columns:

     The replace statement can only be used with tables that use one of the native ser De modules are Dynamic Ser De or Meta data Type column set ser De.

Capture 15 Ser De determines how records are parsed into columns i.e deserialization and how records columns are stored (serialization)

Hours-mins-sees INT
Severity STRING
Message String);

     This statement effectively renames the original hms column and removes the server and process – id columns from the original schema definition.

    As for all the ALTER Statements, only the table metadata is changed.

Partitioning and Bucketing:

     Hive organizes tables into partitions, a way of dividing a table into course – grained parts based on the value of a partition column, such as date.

Capture 15 Using partition can make it faster to do queries on slices of the data.

Capture 15 Tables or partitions may further be sub divided into buckets, to give extra structure to the data that may be used for more efficient queries.

Capture 15 For example, bucketing by user ID means we can quickly evaluate a user based query by running if on a randomized sample of the total set of users.


Capture 15 A table may be partitioned in multiple dimensions.

Capture 15 For example, in addition to partitioning logs by date, we might also subpartition each date partition by country to permit efficient queries by location.

Capture 15 partitioned are defined at table creation time using the PATITIONED by the clause , which takes a list of column definitions.

Capture 15 If we want to search a large amount of data, then we can divide the large data into partitions.


hive>create table party table(loaded int, log error string)
PARTITIONED BY (Logdt string, country string) row format delimited field terminated by ‘\t’ lines terminated by ‘\n’ stored as text file

Capture 15 Partition is one of the concepts in Hive where exactly certain things are grouped by the means of column combination.


There are two reasons why you might want to organize your tables (or partitions) into buckets.

  1. The first is to enable more efficient queries.
  2. The second reason to bucket a table is to make sampling more efficient.


     Tell Hive that a table should be bucketed. And we use the CLUSTERED By clause to specify the columns to bucket and the number of buckets

hive>CREATE TABLE bucketed users(id INT, name STRINA)

      Here we are using the user ID to determine the bucket the Hive does which is done by hashing the value and reducing module and the number of buckets, so any particular bucket will effectively have a random set of users in it.

     The data within a bucket may additionally be stored by one or more columns.

      This allows even more efficient map-side joins, since the join of each bucket becomes an efficient merge sort.

   Syntax for delving that a table has sorted buckets is:

have>CREATE TABLE bucketed users(id INT, name STRING)


0 Responses on What is Hadoop HIVE Query Language"

Leave a Message

Your email address will not be published. Required fields are marked *

Copy Rights Reserved © Mindmajix.com All rights reserved. Disclaimer.