Installation and simple use of hive

Hive is installed on only one node

1. Upload the TAR package

2. Unzip
TAR-ZXVF hive-0.9.0.tar.gz-c/usr/local
3. Configure MySQL metastore (switch to root user)
Configuring Hive_home Environment variables
Rpm-qa | grep MySQL
RPM-E mysql-libs-5.1.66-2.el6_3.i686--nodeps
RPM-IVH mysql-server-5.1.73-1.glibc23.i386.rpm
RPM-IVH mysql-client-5.1.73-1.glibc23.i386.rpm
Modify the MySQL password
/usr/bin/mysql_secure_installation
(Note: Remove anonymous users, allow users to connect remotely)
Log in to MySQL
Mysql-u root-p

4. Configure Hive
CP Hive-default.xml.template Hive-site.xml
Modify Hive-site.xml (delete all content, leave only one <property></property>)
Add the following content:
<property>
<name>javax.jdo.option.ConnectionURL</name>
<value>jdbc:mysql://hadoop00:3306/hive?createDatabaseIfNotExist=true</value>
&LT;DESCRIPTION&GT;JDBC connect string for a JDBC metastore</description>
</property>

<property>
<name>javax.jdo.option.ConnectionDriverName</name>
<value>com.mysql.jdbc.Driver</value>
<description>driver class name for a JDBC metastore</description>
</property>

<property>
<name>javax.jdo.option.ConnectionUserName</name>
<value>root</value>
<description>username to use against Metastore database</description>
</property>

<property>
<name>javax.jdo.option.ConnectionPassword</name>
<value>123</value>
<description>password to use against Metastore database</description>
</property>

5. After installing HIVE and mysq, copy the MySQL connection jar package to the $hive_home/lib directory
If there is an issue with no permissions, in MySQL authorization (performed on the machine where MySQL is installed)
Mysql-uroot-p
# (Execute the following statement * *: All tables under all libraries%: Any IP address or host can be connected)
GRANT all privileges on * * to ' root ' @ '% ' identified by ' 123 ' with GRANT OPTION;

FLUSH privileges;

6. Build table (default is internal table)
CREATE TABLE Trade_detail (ID bigint, account string, income double, expenses double, time string) row format delimited fie lds terminated by ' \ t ';
Build partition Table
CREATE TABLE Td_part (ID bigint, account string, income double, expenses double, time string) partitioned by (Logdate Strin g) Row format delimited fields terminated by ' \ t ';
Build External Tables
Create external table Td_ext (ID bigint, account string, income double, expenses double, time string) row format delimited Fields terminated by ' \ t ' location '/td_ext ';

7. Create a partitioned table
Common table and partition table differences: There is a large amount of data needed to build a partitioned table
CREATE TABLE book (id bigint, name string) partitioned by (pubdate string) row format delimited fields terminated by ' \ t ';

Partition Table Load Data
Load data local inpath './book.txt ' overwrite to table book partition (pubdate= ' 2010-08-22 ');

Load data local inpath '/root/data.am ' into table beauty partition (nation= "USA");

CREATE TABLE Trade_detail (ID bigint, account string, income double, expenses double, time string) row format delimited fie lds terminated by ' \ t ';
CREATE TABLE User_info (ID bigint, account string, name string, age int.) row format delimited fields terminated by ' \ t ';

Load data local inpath '/home/hadoop/data/trade_detail ' overwrite into table trade_detail;
Load data local inpath '/home/hadoop/data/user_info ' overwrite into table user_info;

Create table result row format delimited fields terminated by ' \ t ' as select T1.account, T1.income, t1.expenses, T1.surplu S, t2.name from User_info T2 join (select Account, sum (income) as income, sum (expenses) as expenses, sum (income-expenses) As surplus from Trade_detail Group by account) T1 on (t1.account = T2.account);

Create an external table
Create external table T_detail (ID bigint, account string, income double, expenses double, time string) partitioned by (log Date string) row format delimited terminated by ' \ t ' location '/hive/td_partition ';

Create temporary function areaudf as ' Cn.itcast.hive.udf.AreaUDF ';


Load data inpath '/apache_cleaned/2013-05-31/part-r-00000 ' into table Hmbbs partition (logdate= ' 2018-05-31 ');

8, the use of hive UDF

To inherit the Org.apache.hadoop.hive.ql.exec.UDF class implementation evaluate

Custom Function Call Procedure:
1. Add a jar package (executed in the Hive command line)
hive> add Jar/root/nudf.jar;

2. Create a temporary function
Hive> Create temporary function getnation as ' Cn.yy.hive.udf.NationUDF ';

3. Call
Hive> Select ID, Name, getnation (Nation) from usertable;

4. Save the query results in HDFs
hive> CREATE table result row format delimited fields terminated by ' \ t ' as SELECT * from usertable ORDER by id DESC;
Hive> Select ID, getareaname (ID) as name from Tel_rec;

Create table result row format delimited fields terminated by ' \ t ' as select ID, getnation (Nation) from usertable;