hive外部表手顺(LDAP)

1、gcluster配置文件添加

gcluster_enable_external_table=1

2、星环初始化beeline连接

172.16.3.192 root/gbasepoc123

cd /opt/TDH-Client

source init.sh

3、进入hive后建库建表

beeline -u "jdbc:hive2://node192:10000/default" -n hive -p admin

create database hubo;

use hubo;

4、星环对写入的普通表有insert限制，需要load;本次选用orc表

create table t(a int,b string)

CLUSTERED BY (a) INTO 4 BUCKETS

STORED AS ORC

TBLPROPERTIES ('transactional'='true');

insert into t values(1,'a'),(2,'b'),(3,'c');

select * from t;

5、配置域名所有的hadoop的节点信息写入到8a的所有节点/etc/hosts

172.16.3.138 node138

172.16.3.192 node192

172.16.3.113 node113

6、查看namenode端口号和datanode端口号

cat /opt/TDH-Client/conf/hdfs1/hdfs-site.xml | grep -A 1 "dfs.namenode.rpc-address"

[namenode 8020]

cat /opt/TDH-Client/conf/hdfs1/hdfs-site.xml | grep -A 1 "dfs.datanode.ipc.address"

[datanode 50020]

7、查看Principal

klist -kt /opt/hdfs.headless.keytab

8、集群设置参数

set global _gbase_hdfs_rpcconfig='dfs.encrypt.data.transfer=false,dfs.block.access.token.enable=true';

set global gbase_hdfs_auth_mode=LDAP;

set global gbase_hdfs_keytab='/opt/hdfs.keytab';

set global gbase_hdfs_principal='hdfs/node192@TDH';

set global gbase_hdfs_protocol='RPC';

set global gcluster_connect_to_hiveserver2='172.16.3.192?10000?hive?admin?120?1?LDAP?LDAP';

set global gcluster_unload_data_for_hive='hdp://root@172.16.3.192:8020/opt/test.orc';

9、8a创建外部表(多一列fileid)

create load write external table test.t1(name varchar(100),id varchar(20),entry_date varchar(200), date varchar(200), fileid int)location ''

PROPERTIES TYPE 'hive' DATABASE 'wyl' TABLE 't1'

INFORMAT DATA_FORMAT parquet

OUTFORMAT ;

CREATE load WRITE EXTERNAL TABLE "t3" (

"name" varchar(128) DEFAULT NULL,

"id" varchar(128) DEFAULT NULL,

"entry_date" varchar(128) DEFAULT NULL,

"fileid" int(11) DEFAULT NULL

) LOCATION ''

PROPERTIES TYPE 'hive' DATABASE 'wyl' TABLE 't2'

INFORMAT DATA_FORMAT 9 FILE_FORMAT UNDEFINED FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'

OUTFORMAT FIELDS TERMINATED BY ' ' ESCAPED BY '\' LINES TERMINATED BY '\n' STARTING BY '' WRITEMODE BY NORMAL

ENGINE=EXPRESS DEFAULT CHARSET=utf8 TABLESPACE='sys_tablespace'