梧桐数据库对接 Hive 权限使用方法

原创吼吼哈嘿 2025-03-22

产品简介

梧桐数据库（WuTongDB）是基于 Apache HAWQ 打造的一款分布式 OLAP 数据库。产品通过存算分离架构提供高可用、高可靠、高扩展能力，实现了向量化计算引擎提供极速数据分析能力，通过多异构存储关联查询实现湖仓融合能力，可以帮助企业用户轻松构建核心数仓和湖仓一体数据平台。
2023年6月，梧桐数据库（WuTongDB）产品通过信通院可信数据库分布式分析型数据库基础能力测评，在基础能力、运维能力、兼容性、安全性、高可用、高扩展方面获得认可。

点击访问：
梧桐数据库（WuTongDB）相关文章
 梧桐数据库（WuTongDB）产品宣传材料
 梧桐数据库（WuTongDB）百科

梧桐数据库对接 Hive 权限使用方法

支持功能

1、支持2段principal、支持3段pricipal

2、支持同一个hive，多个用户、多个principal、多个kerberos配置，来访问各自权限的表

3、支持hms ha，hms的高可用，可以再url中填写多个ip和Port，以逗号分割。这里需要注意如果高可用多个连接port不一样，那需要再hdfs_client中配置多个Port

4、支持一个session内访问多个hive schema

5、支持票据自动续约功能

CREATE FILESPACE hs_f ON hive ('hive://user=<username>');

CREATE TABLESPACE hs_t ON '<hive database name>' FILESPACE hs_f;

set external_catalog_hive_authorization = 'native'; --可以配置到guc文件中

CREATE SCHEMA hs_s TABLESPACE hs_t;

\d hs_s.*

增加hdfs_client.xml配置

<property>

<name><username>.hms.hive.security.authentication</name>

<value>kerberos</value>

<property>

<property>

<name><username>.hms.hive.security.keytab</name>

<value>/usr/local/wutong/conf/wutongdb/hive.keytab</value>

</property>

<property>

<name><username>.hms.hive.security.principal</name>

<value>hive@wutong907381.COM</value>

--<value>hive/host@wutong907381.COM</value>

</property>

<property>

<name><username>.hms.hive.security.ticket.lifetime</name>

<value>10000</value> --ms

</property>

<property>

<name><username>.hs2.hive.security.keytab</name>

<value>/usr/local/wutong/conf/wutongdb/hive.keytab</value>

</property>

<property>

<name><username>.hs2.hive.security.principal</name>

<value>hive@wutong907381.COM</value>

--<value>hive/host@wutong907381.COM</value>

</property>

<property>

<name><username>.hs2.hive.security.ticket.lifetime</name>

<value>10000</value> --ms

</property>

<property>

<name><username>.hms.hive.connection.url</name>

<value>hive://<ip>:<port>,<ip>:<port>,...</value>

</property>

<property>

<name><username>.hs2.hive.connection.url</name>

<value>hive://<ip>:<port>,<ip>:<port>,...</value>

</property>复制

例子

<property>

<name>hive_1.hms.hive.connection.url</name>

<value>v_storagemaster_host:9083</value>

</property>

<property>

<name>hive_1.hms.hive.security.principal</name>

<value>hive/v_storagemaster_host@KDCSERVER.wutong.COM</value>

</property>

<property>

<name>hive_1.hms.hive.security.keytab</name>

<value>/etc/security/keytabs/hdfs.keytab</value>

</property>

<property>

<name>hive_1.hms.hive.security.ticket.lifetime</name>

<value>10000</value>

</property>

<property>

<name>hive_1.hms.hive.security.authentication</name>

<value>kerberos</value>

</property>

<property>

<name>hive_1.hs2.hive.connection.url</name>

<value>sdf:111,v_storagemaster_host:9000,v_storagemaster_host:10000</value>

</property>

<property>

<name>hive_1.hs2.hive.security.principal</name>

<value>hive/v_storagemaster_host@KDCSERVER.wutong.COM</value>

</property>

<property>

<name>hive_1.hs2.hive.security.keytab</name>

<value>/etc/security/keytabs/hdfs.keytab</value>

</property>

<property>

<name>hive_1.hs2.hive.security.ticket.lifetime</name>

<value>10000</value>

</property>

<property>

<name>hive_1.hs2.hive.security.authentication</name>

<value>kerberos</value>

</property>

<property>

<name>hive_2.hms.hive.connection.url</name>

<value>v_storagemaster_host:9083</value>

</property>

<property>

<name>hive_2.hms.hive.security.principal</name>

<value>hive/v_storagemaster_host@KDCSERVER.wutong.COM</value>

</property>

<property>

<name>hive_2.hms.hive.security.keytab</name>

<value>/etc/security/keytabs/hdfs.keytab</value>

</property>

<property>

<name>hive_2.hms.hive.security.authentication</name>

<value>kerberos</value>

</property>

<property>

<name>wutong.hms.hive.connection.url</name>

<value>v_storagemaster_host:9083</value>

</property>

<property>

<name>wutong.hms.hive.security.principal</name>

<value>wutong@KDCSERVER.wutong.COM</value>

</property>

<property>

<name>wutong.hms.hive.security.keytab</name>

<value>/etc/security/keytabs/hdfs.keytab</value>

</property>

<property>

<name>wutong.hms.hive.security.authentication</name>

<value>kerberos</value>

</property>

<property>

<name>wutong.hs2.hive.connection.url</name>

<value>v_storagemaster_host:10000</value>

</property>

<property>

<name>wutong.hs2.hive.security.principal</name>

<value>wutong@KDCSERVER.wutong.COM</value>

</property>

<property>

<name>wutong.hs2.hive.security.keytab</name>

<value>/etc/security/keytabs/hdfs.keytab</value>

</property>

<property>

<name>wutong.hs2.hive.security.authentication</name>

<value>kerberos</value>

</property>复制

SQL使用方法

--test url connect、 high availability、life time timeout

CREATE FILESPACE hs_f2 ON hive ('hive://user=hive_1');

CREATE TABLESPACE hs_t2 ON 'default' FILESPACE hs_f2;

CREATE SCHEMA hs_s2 TABLESPACE hs_t2;

select * from hs_s2.t2 tt where tt.id = 8;

set external_catalog_hive_authorization = 'native';

-- test authorization,inner ticket life time

select * from hs_s2.t2 tt where tt.id = 8;

select pg_sleep(10);

--out of life time,reconnect hive

select * from hs_s2.t2 tt where tt.id = 8;

set external_catalog_hive_authorization = '';

drop schema hs_s2;

drop tablespace hs_t2;

drop filespace hs_f2;

--test only metastore

CREATE FILESPACE hs_f3 ON hive ('hive://user=hive_2');

CREATE TABLESPACE hs_t3 ON 'default' FILESPACE hs_f3;

CREATE SCHEMA hs_s3 TABLESPACE hs_t3;

select * from hs_s3.t2 tt where tt.id = 8;

set external_catalog_hive_authorization = 'native';

select * from hs_s3.t2 tt where tt.id = 8;

select t1.id from hs_s3.t1 as t1 inner join hs_s3.t3 as t3 on t1.id = t3.id;

set external_catalog_hive_authorization = '';

drop schema hs_s3;

drop tablespace hs_t3;

drop filespace hs_f3;

-- test auth

CREATE FILESPACE hs_f4 ON hive ('hive://user=wutong');

CREATE TABLESPACE hs_t4 ON 'default' FILESPACE hs_f4;

CREATE SCHEMA hs_s4 TABLESPACE hs_t4;

select * from hs_s4.t2 tt where tt.id = 8;

set external_catalog_hive_authorization = 'native';

select * from hs_s4.t2 tt where tt.id = 8;

set external_catalog_hive_authorization = '';

drop schema hs_s4;

drop tablespace hs_t4;

drop filespace hs_f4;

--test multi hive connect

CREATE FILESPACE hs_f2 ON hive ('hive://user=hive_1');

CREATE TABLESPACE hs_t2 ON 'default' FILESPACE hs_f2;

CREATE SCHEMA hs_s2 TABLESPACE hs_t2;

CREATE FILESPACE hs_f3 ON hive ('hive://user=hive_2');

CREATE TABLESPACE hs_t3 ON 'default' FILESPACE hs_f3;

CREATE SCHEMA hs_s3 TABLESPACE hs_t3;

CREATE FILESPACE hs_f4 ON hive ('hive://user=wutong');

CREATE TABLESPACE hs_t4 ON 'default' FILESPACE hs_f4;

CREATE SCHEMA hs_s4 TABLESPACE hs_t4;

set external_catalog_hive_authorization = '';

select tt1.id from hs_s2.t1 as tt1 inner join hs_s3.t3 as tt3 on tt1.id = tt3.id;

set external_catalog_hive_authorization = 'native';

select tt1.id from hs_s2.t1 as tt1 inner join hs_s3.t3 as tt3 on tt1.id = tt3.id;

set external_catalog_hive_authorization = '';

select tt1.id from hs_s4.t1 as tt1 inner join hs_s3.t3 as tt3 on tt1.id = tt3.id;

set external_catalog_hive_authorization = 'native';

select tt1.id from hs_s4.t1 as tt1 inner join hs_s3.t3 as tt3 on tt1.id = tt3.id;复制