Hive相关操作-分区-桶-udf

1.分区

手动创建hive分区目录是无法被识别的。
需要执行命令创建：

alter table book add partition(dt="20190722") location '/user/hive/warehouse/mytest.db/book/dt=20190722'

或者执行修复分区命令

MSCK repair table book；

2.元数据库

hive自带有一个元数据库为derby，该数据库只能用于测试，因为实际上每次启动的时候都会创建一个derby的链接，但是关闭hive。下次又会生成一个新的链接，上次的数据会全部丢失。且在并发上也不能支持，所以建议在测试环境上也使用mysql。
在centos系统中有自带的mysql。完全可以使用。安装命令如下：

• CentOS7

[root@host01/]# yum -y install mariadb
[root@host01/]# yum -y install mariadb-server
[root@host01/]# systemctl start mariadb
[root@host01/]# /usr/bin/mysql_secure_installation
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
 
In order to log into MariaDB to secure it, we'll need the current
password for the root user.  If you've just installed MariaDB, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
 
Enter current password for root (enter for none):
OK, successfully used password, moving on...
 
Setting the root password ensures that nobody can log into the MariaDB
root user without the proper authorisation.
 
Set root password? [Y/n] Y
New password:123456
Re-enter new password: 123456
Password updated successfully!
Reloading privilege tables..
... Success!
 
 
By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
 
Remove anonymous users? [Y/n] Y
... Success!
 
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
 
Disallow root login remotely? [Y/n] n
... skipping.
 
By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
 
Remove test database and access to it? [Y/n] Y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!
 
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
 
Reload privilege tables now? [Y/n] Y
... Success!
 
Cleaning up...
 
All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.
 
Thanks for using MariaDB!

• Cenos6

[root@host02/]# service mysqld start
[root@host02/]# mysql_secure_installation 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTIONUSE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the passwordwill be blank,
so you should just press enter here.

Enter current password for root (enter for none): 
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password: 
Re-enter new password: 
Password updated successfully!
Reloading privilege tables..
 ... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing,and to make the installation
go a bit smoother.  You should removethem before moving into a
production environment.

Remove anonymous users? [Y/n] y
 ... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] n
 ... skipping.

By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only fortesting, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
 - Dropping test database...
 ... Success!
 - Removing privileges on test database...
 ... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
 ... Success!

Cleaning up...



All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for usingMySQL!

3.hive桶

hive的桶的概念适合用于统计数据。

create table teacher(id string,name string)
cluster by(id) into 3 buckets
row format delimited
feilds terminated by '\t';

开启分桶机制

set hive.enforce.bucketing=true;

对于分桶表，不允许以外部文件的方式导入数据，只能从另外一张表导入。

4.hive的UDF

步骤：

1.新写一个Java类，打成Jar上传至服务器。

package cn.tedu.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
public class ToUpperUDF extends UDF{
	public String evaluate(string str){
		reture str.toUpperCase();
	}
}

2.在hive中进行注册

hive> add Jar /home/txx/udf.jar

3.在hive中为UDF起一个专有的名称

hive> create temporary function myudf as 'cn.tedu.udf.ToUpperUDF'

4.然后就可以使用啦，但是这里只是临时的function，持久化的话需要写入到hive的启动文件中

5.hive的beeline连接

beeline
beeline> !connect jdbc:hive2://192.168.8.20:10000/default

beeline -n root -u jdbc:hive2://192.168.8.20:10000/default

有Kerberos的连接命令：

beeline
beeline> !connect jdbc:hive2://192.168.8.20:10000/default;principal=hive/[e-mail]