Hive Exercise-I

Mail me for Raw data....

These are the steps for performing exercise :


i) Create Database
------------------
create database retail_store;


ii) Select Database
------------------
use retail_store;

iii) Create table for storing transactional records
-------------------------------------------------
create table txn_records(txnno INT, txndate STRING, custno INT, amount DOUBLE, 
category STRING, product STRING, city STRING, state STRING, spendby STRING)
row format delimited
fields terminated by ','
stored as textfile;

iv) Load the data into the table
-------------------------------
LOAD DATA LOCAL INPATH 'txns' OVERWRITE INTO TABLE txn_records;

v) Describing metadata or schema of the table
---------------------------------------------
describe txn_records;

vi) Counting no of records
-------------------------
select count(*) from txn_records;

vii) Counting total spending by category of products
--------------------------------------------------
select category, sum(amount) from txn_records group by category;

viii) 10 customers
--------------------
select custno, sum(amount) from txn_records group by custno limit 10;

Hive Introduction (Cont...)

Hive Introduction-2

The main components of Hive are:

• Metastore: It stores all the metadata of Hive. It stores data of data stored in database, tables, columns, etc.
• Driver: It includes compiler, optimizer and executor used to break down the Hive query language statements.
• Query compiler: It compiles HiveQL into graph of map reduce tasks.
• Execution engine: It executes the tasks produces by compiler.
• Thrift server: It provides an interface to connect to other applications like MySQL, Oracle, Excel, etc. through JDBC/ODBC drivers.
• Command line interface: It is also called Hive shell. It is used for working with data either interactively or batch data processing.
• Web Interface: It is a visual structure on Hive used for interaction with data.

Data Storage in Hive:

Hive has different forms of storage options and they include:

• Metastore: Metastore keeps track of all the metadata of database, tables, columns, datatypes etc. in Hive. It also keeps track of HDFS mapping.
• Tables: There can be 2 types of tables in Hive. First, normal tables like any other table in database. Second, external tables which are like normal tables except for the deletion part. HDFS mappings are used to create external tables which are pointers to table in HDFS. The difference between the two types of tables is that when the external table is deleted its data is not deleted. Its data is stored in the HDFS whereas in case of normal table the data also gets deleted on deleting the table.
• Partitions: Partition is slicing of tables that are stored in different subdirectory within a table’s directory. It enhances query performance especially in case of select statements with “WHERE” clause.
• Buckets: Buckets are hashed partitions and they speed up joins and sampling of data.

Hive vs. RDBMS (Relational database)

Hive and RDBMS are very similar but they have different applications and different schemas that they are based on.

• RDBMS are built for OLTP (Online transaction processing) that is real time reads and writes in database. They also perform little part of OLAP (online analytical processing).
• Hive is built for OLAP that is real time reporting of data. Hive does not support inserting into an existing table or updating table data like RDBMS which is an important part of OLTP process. All data is either inserted in new table or overwritten in existing table.
• RDBMS is based on write schema that means when data is entered in the table it is checked against the schema of table to ensure that it meets the requirements. Thus loading data in RDBMS is slower but reading is very fast.
• Hive is based on read schema that means data is not checked when it is loaded so data loading is fast but reading is slower.

Hive Query Language (HQL)

HQL is very similar to traditional database. It stores data in tables, where each table consists of columns and each column consists of specific number of rows. Each column has its own data type. Hive supports primitive as well as complex data types. Primitive types like Integer, Bigint, Smallint, Tinyint, Float, Double Boolean, String, and Binary are supported. Complex types include Associative array: map , Structs: struct , and Lists: list .

Data Definition statements (DDL) like create table, alter table, drop table are supported. All these DDL statements can be used on Database, tables, partitions, views, functions, Index, etc. Data Manipulation statements (DML) like load, insert, select and explain are supported. Load is used for taking data from HDFS and moving it into Hive. Insert is used for moving data from one Hive table to another. Select is used for querying data. Explain gives insights into structure of data.

Hive Introduction

Hive is a data warehouse infrastructure tool to process structured data in Hadoop. It resides on top of Hadoop to summarise Big Data, and makes querying and analysing easy.





Hive started by facebook......

Hive installation using MySQL on ubuntu

1. Install MySQL
             $ sudo apt-get install mysql-server
             Note: You will be prompted to seta password for root.

2. Install the MySQL Java Connector –
           $ sudo apt-get install libmysql-java

3. Create soft link for connector in Hive lib directory or copy connector jar to lib folder –
           ln -s /usr/share/java/mysql-connector-java.jar $HIVE_HOME/lib/mysql-connector-java.jar
          Note :- HIVE_HOME points to installed hive folder.

4. Create the Initial database schema using the hive-schema-0.14.0.mysql.sql file ( or the file
corresponding to your installed version of Hive) located in the
           $HIVE_HOME/scripts/metastore/upgrade/mysql directory.
           $ mysql -u root -p
           Enter password:


mysql> CREATE DATABASE metastore;
mysql> USE metastore;
mysql> SOURCE $HIVE_HOME/scripts/metastore/upgrade/mysql/hive-schema-0.14.0.mysql.sql;

5. You also need a MySQL user account for Hive to use to access the metastore. It is very important
to prevent this user account from creating or altering tables in the metastore database schema.

        mysql> CREATE USER 'hiveuser'@'%' IDENTIFIED BY 'hivepassword';
        mysql> GRANT all on *.* to 'hiveuser'@localhost identified by 'hivepassword';
        mysql> flush privileges;
        Note : – hiveuser is the ConnectionUserName in hive-site.xml ( As explained next)

6. Create hive-site.xml ( If not already present) in $HIVE_HOME/conf folder with the
configuration below –

<configuration>
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://localhost/metastore?
createDatabaseIfNotExist=true</value>
<description>metadata is stored in a MySQL server</description>
</property>
<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>MySQL JDBC driver class</description>
</property>
<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>hiveuser</value>
<description>user name for connecting to mysql server</description>
</property>
<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>hivepassword</value>
<description>password for connecting to mysql server</description>
</property>
<property>
<name>datanucleus.autoCreateSchema</name>
<value>false</value>
<description>Creates necessary schema on a startup if one doesn't exist
</ description>
</property>
</configuration>

7. We are all set now. Start the hive console.

Type hive and enter



For any clarification type in comment and keep update with latest information with www.facebook.com/coebda