66.管理Hive外部表——Sentry

66.1 演示环境介绍

• 操作系统：CentOS6.5
• CM和CDH版本：5.12.1
• 采用root用户操作
• 集群运行正常，Kerberos/HDFS/Hive/Impala/Hue服务已与Sentry集成，Hive用户是超级用户

66.2 操作演示

创建测试库及外部表

• 使用hive用户登录Kerberos，并通过beeline登录HiveServer2
  • 创建fayson数据库

0: jdbc:hive2://localhost:10000/> create database fayson;
INFO  : Compiling command(queryId=hive_20170916155353_12e7c551-6a72-4ff3-b581-353c4dbd0fb0): create database fayson
INFO  : Semantic Analysis Completed
…
INFO  : OK
No rows affected (0.232 seconds)
0: jdbc:hive2://localhost:10000/> 

• 在fayson库下创建外部表student_hive，建表语句

create external table if not exists student_hive(
  name string,
  age int,
  addr string
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY ','
LOCATION '/extwarehouse/student_hive';

• 向/extwarehouse/student_hive表put数据

[root@ip-186-31-6-148 ~]# hadoop fs -put student.txt /extwarehouse/student_hive
[root@ip-186-31-6-148 ~]# hadoop fs -ls /extwarehouse/student_hive
Found 1 items
-rw-r--r--   3 hive supergroup         59 2017-09-16 16:05 /extwarehouse/student_hive/student.txt
[root@ip-186-31-6-148 ~]# 

• /extwarehouse/student_hive数据目录不存，在创建外部表时自动生成，且数据目录属主为hive。

0: jdbc:hive2://localhost:10000/> select * from student_hive;
...
INFO  : OK
+--------------------+-------------------+--------------------+--+
| student_hive.name  | student_hive.age  | student_hive.addr  |
+--------------------+-------------------+--------------------+--+
| fayson             | 23                | guangdong          |
| zhangsan           | 24                | shenzhen           |
| lisi               | 55                | guangzhou          |
+--------------------+-------------------+--------------------+--+
3 rows selected (0.216 seconds)
0: jdbc:hive2://localhost:10000/> 

创建角色并授权

• 创建faysonall角色并且授权给fayson用户组
  • 授权fayson用户组拥有fayson库所有权限

create role faysonall;
grant all on database fayson to role faysonall;
grant role faysonall to group fayson;

• 使用fayosn用户登录Kerberos，通过beeline连接HiveServer2

[fayson@ip-186-31-6-148 root]$ beeline 
Beeline version 1.1.0-cdh5.12.1 by Apache Hive
beeline> !connect jdbc:hive2://localhost:10000/;principal=hive/ip-186-31-6-148.fayson.com@FAYSON.COM
scan complete in 2ms
Connecting to jdbc:hive2://localhost:10000/;principal=hive/ip-186-31-6-148.fayson.com@FAYSON.COM
Connected to: Apache Hive (version 1.1.0-cdh5.12.1)
Driver: Hive JDBC (version 1.1.0-cdh5.12.1)
Transaction isolation: TRANSACTION_REPEATABLE_READ
0: jdbc:hive2://localhost:10000/> 

• 切换至fayson数据库对student_hive表操作
  • 可以向表中插入数据

0: jdbc:hive2://localhost:10000/> insert into student_hive values('lisi', 22, 'beijing');
...
INFO  : OK
No rows affected (22.501 seconds)
0: jdbc:hive2://localhost:10000/>

• 可以查询表数据

0: jdbc:hive2://localhost:10000/> select * from student_hive;
...
INFO  : OK
+--------------------+-------------------+--------------------+--+
| student_hive.name  | student_hive.age  | student_hive.addr  |
+--------------------+-------------------+--------------------+--+
| lisi               | 22                | beijing            |
| fayson             | 23                | guangdong          |
| zhangsan           | 24                | shenzhen           |
| lisi               | 55                | guangzhou          |
+--------------------+-------------------+--------------------+--+
4 rows selected (0.215 seconds)
0: jdbc:hive2://localhost:10000/> 

• HDFS验证
  • fayson用户可以浏览student_hive的数据目录，查看数据目录下文件内容，但没有delete和put文件的权限。

[fayson@ip-186-31-6-148 ~]$ hadoop fs -ls /extwarehouse/student_hive
Found 2 items
-rwxr-xr-x   3 hive supergroup         16 2017-09-16 16:16 /extwarehouse/student_hive/000000_0
-rw-r--r--   3 hive supergroup         59 2017-09-16 16:05 /extwarehouse/student_hive/student.txt
[fayson@ip-186-31-6-148 ~]$ hadoop fs -rmr /extwarehouse/student_hive/student.txt
rmr: DEPRECATED: Please use 'rm -r' instead.
rmr: Failed to move to trash: hdfs://ip-186-31-6-148.fayson.com:8020/extwarehouse/student_hive/student.txt: Permission denied: user=fayson, access=WRITE, inode="/extwarehouse/student_hive":hive:supergroup:drwxr-xr-x
[fayson@ip-186-31-6-148 ~]$ hadoop fs -put student1.txt /extwarehouse/student_hive/
put: Permission denied: user=fayson, access=WRITE, inode="/extwarehouse/student_hive":hive:supergroup:drwxr-xr-x
[fayson@ip-186-31-6-148 ~]$ 

• hive创建的外部表，通过Sentry授权后，fayson用户组使用beeline和Hue能对该表进行查询和插入操作。
  • 但不能对HDFS和Hue FileBrowser上的数据目录进行新增和删除操作，由于fayson用户无操作数据目录的权限。

大数据视频推荐：
CSDN
大数据语音推荐：
企业级大数据技术应用
大数据机器学习案例之推荐系统
自然语言处理
大数据基础
人工智能：深度学习入门到精通