HIVE -Serde(CSV)

HIVE has an immense capability of processing the data and it can be achieved by the help of the package SERDE which means Serialization and Deserialization.

To read the semi structured data like JSON,XML , HIVE need to understand how to process such kind of format.To solve this ,SERDE came into the picture.

SERDE performs two function mainly :-

1.)Reading data from Table.
2.)Writing the data back to the HDFS.

DESERIALIZER takes the binary or string representation of the record and converts into the Java object that can be manipulated by the HIVE.
SERIALIZER takes the Java object and convert it back into the such a format that can be written into the HDFS.

SERDE can be downloaded from the hadoop distribution vendor like (cloudera or Hortonworks)
The JAR file need to be placed  into  the $HIVE_HOME/lib.The required SERDE need to be register  into the HIVE .
Let us take an example to understand the SERDE in a more efficient way.
we have a csv file serdefile.csv having the below data.
 
we need to put this file into hdfs using the put command.
hadoop fs -put <source> <destination>
we need to create the external table to read the
 
we can now do the basic select query from the above table.

HIVE - View

VIEW in HIVE is similar to what we have in the RDBMS system.The concept of VIEW has been incorporated from the RDBMS .It allows us to store the Query and treat it like as Table.It does not store the data.

Some of the basic functions of a VIEW are :-

1.) To reduce query complexity.
2.)To reduce the data on the basis of some condition.
3.)Query a view is like querying a table.
4.)If a query references a table, then the definition of the view is combined with the rest of the query by Hive's query planner.
5.)Query using the view can fail, if the referenced table/column does not exist.

CREATING A VIEW

To create any view , we need an underlying table on which we have to create our query.
Let us take  a simple example with employees table and we will align our query on this table.
we have the below data:-

 
we want to have only those id which are having salary more than 50000.



Syntax :-
create view view name as select column name from referenced_table;

The select * from viewemp will give us the below result.

 

The view is available in the database just like the normal table , we will get the view name if we give the show tables;

we should note that we can create index on the view and  can join two views also .

Dropping a View :-

drop view viewname;

 

HIVE - Partitioned Tables

Partition in Hive is mainly  done to balance the load horizontally.Normally ,when we fire a query it will check for all the data and produce the result.This process is quite cumbersome and involve a lot of map reduce job.The job usually take too long to execute.
In order to reduce such a huge  effort , we need to partition the table based  on some column.

Below is the query to create a table with partition on the date column :

















The syntax for loading the data into the partition table is bit different from the normal table which we used in our earlier tutorials.
 

Once the table is created and we loaded the data , we will find the sub directory created in the user/hive/warehouse location.

Each sub directory corresponds to a particular partition.

If we fire a query with like select * from hivepart where year=2017.
The following query search for the data in the only sub directory that belongs to the year=2017 This will reduce a lot of map reduce operation and can be effective way of improving the performance.

STRICT MODE:-
By Default ,HIVE runs in a STRICT mode .The STRICT mode does not allow to fire a query on a partition table witjout where clause on partition table.

We can change the mode by giving "nonstrict".



In case if we fire a query then we get the semantic error .

How to check the partition in the table:

 

Dynamic Partitioning :- The Dynamic partitioning is used to eliminates the process of hard coding the partition value in a table.
But for the data to be loaded , we need a statging table.we cannot load the data from the directory .
First we will create a staging table.

create table hivestag1(col1 int, year string)
row format delimited
fields terminated by ','
lines terminated by '\n';

Then , we need to create the final table.

create table hivefin(col1 int)
partitioned by (year string)
row format delimited
fields terminated by ','
lines terminated by '\n';
Finally , we need to load the data from the staging table into the final table.

insert overwrite table  hivefin
partition(year)
select * from hivestag;
Partition for the Hivefin Table:

We need to check the below property :-

set hive.mapred.mode=nonstrict;
hive.exec.max.dynamic.partitions;
hive.exec.max.dynamic.partitions.pernode;

HIVE-Managed Table Vs External Table

Managed table and External table is one of the main concept and need to be understand carefully.Normally, the table which we will create is the managed tables.
So, Let us understand what exactly the Managed Table and External table internally behave.

Managed Tables :- In managed table , the data is moved from the it's original directory to the location of the HIVE meta store (/user/hive/warehouse/). The location can be default or user can provide it.The required directory and sub directory is also created.
In case , we drop the table the data as well as the metadata will get removed from the meta store.
HIVE controls the complete life cycle of the table.

External Tables:- In external table , the data does not move from the HDFS to the table storage location.It means HIVE does not owns the data.
Metadata for this table get updated in the meta store.In case , the table is dropped the data does not get deleted only the metadata get deleted.
During the creation of the table , we need to provide the EXTERNAL keyword for  the creation of the table.

When to use External Tables:- There are some scenario when we need to use EXTERNAL table.For our learning purpose, the managed table is fine.But when we are working on a production cluster . it is recommended that we should go for for the External Tables.
1.) When we are using some other tool to excavate the same piece of data .
2.) When we have multiple views or table on the same data set.
3.) To query external dataset present in the external system like Amazon S3.

Syntax for creating an external table.

 
Syntax for creating a normal table.

 

HIVE -Loading CSV Files

To Load a csv file in a HIVE table , we need to make the HIVE engine how data is being ingested inside it.

Query to create the table:-

 

We will load the data from the local filesystem into the hivecsv table.

 
The output will be something like this:-

HIVE - Loading Data Into Table

Once we have created the database and the Table .We are supposed to load some data inside that table. We will create a simple table and will load some data inside it.

CREATE TABLE EMPLOYEE (COL1 INT);





The table has been created with the name employee having a single column COL1.
We can check the details of the table with the help of the DESCRIBE command.



We can load data either from the local file system or from the Hadoop file system.
We will see both the example separately .First of all, we load the data from the local filesystem.

Data Loading From Local Filesystem 

Load data local inpath 'sangam/test.txt' into table employee;





Similarly ,we can load the data from the HDFS via using the below command.

Data Loading From HDFS 

Load data inpath 'hivetest/test.txt' into table employee;





We should note that when we are using the local filesystem , we need to use the local keyword .We can verify the data using the below command.





 
Since ,we have loaded the data twice once through the local filesystem and other through the HDFS .so we are getting the duplicates in the table.

Data Loading through another HIVE table
We can load from other table using the insert  and select command as we do this in the  SQL like databases;


Insert into employeenew select * from employee;

 

Hive - Database and Table Creation

The traditional database works on the fact that they control the complete data storage system.
It will check if the data that is being written follows the constraints , datatype ,lengths etc.
The above property is called SCHEMA ON WRITE

HIVE does not follow above all as it does not have it's own storage system and rely on the HDFS for it's data storage.
HIVE can read any data that is kept in the HDFS which is created , updated or sometimes the data got damaged also.
This property is called SCHEMA ON READ.

So, Let us start and check the different databases available in our cluster.
 
It will display all the databases available in the HIVE.


We should note that HIVE contains a default database if we do not specify the database name , the default database will get provoked.

Normally , when we are working with a large data set and a number of databases ,we forget that in which DB we are working .we can set a property to identify the current DB using the below command.

 

Creating a DB :- We can create our own db using the simple command .

 

Using the particular DB :-We can use the particular db using the use keyword followed by the database name.

 

Whenever we create a database in the HIVE ,a directory is created and the tables are stored in the sub directories.Exception is the default database.

The default location is the hive.metastore.warehouse.dir.
We can check the directory for the default db in the below location:-
 
 

We can switch the database directory at the time of database creation.

Dropping a database :- We can drop a database using "drop database database_name;"
However, it will throw ana error if it contains tables inside the database.
In order to override this property, we can use
Drop database database_name cascade;

Using the cascade keyword will drop all the tables , then the db and finally all the directory associated with it.