【必需】ambari plus 数据库初始化文件
# 【必需】ambari plus 数据库初始化文件
Ambari Plus 3.0.0 在官方 Ambari 数据库上增加了租户、用户归属、权限绑定、审计总账和默认集群归属等表。新装环境里，官方 Ambari DDL 只能创建原生表；如果少了这份增量 DDL，服务可以启动，但进入权限控制面、租户管理或欢迎语相关页面时就会出现数据缺失。
必需
这一步必须和官方 Ambari DDL 使用同一个数据库。本文示例库名是 ambari，不要单独创建 ambari_plus 库，也不要把这份 SQL 导入到 Hive、Ranger 或其他组件库里。
# 导入方式
如果前面已经按 Step9 创建了 ambari 库和 ambari 用户，把本文 SQL 保存为 Ambari-Plus-DDL-MySQL-CREATE.sql，然后在核心节点执行：
mysql -uambari -p ambari < Ambari-Plus-DDL-MySQL-CREATE.sql
1
导入后建议马上查两个结果：第一，关键表是否存在；第二，内置 root 租户是否已经写入。
mysql -uambari -p -D ambari -N -e "SHOW TABLES LIKE 'ambari_plus_tenant';"
mysql -uambari -p -D ambari -e "SELECT tenant_key,status FROM ambari_plus_tenant;"
1
2
2
能看到 ambari_plus_tenant，并且 root 租户状态是 ACTIVE，这份增量 DDL 就算落库成功。
# 初始化 SQL
下面这份 SQL 保留建表、索引和基线数据，不依赖表注释元数据，适合 MySQL / MariaDB 新装环境初始化使用。
CREATE TABLE IF NOT EXISTS ambari_plus_tenant(
tenant_key VARCHAR(100) NOT NULL,
display_name VARCHAR(255) NOT NULL,
description VARCHAR(1024),
status VARCHAR(32) NOT NULL,
created_by VARCHAR(255),
created_time BIGINT NOT NULL,
updated_by VARCHAR(255),
updated_time BIGINT NOT NULL,
CONSTRAINT PK_ap_tenant PRIMARY KEY (tenant_key)
);
CREATE TABLE IF NOT EXISTS ambari_plus_user(
user_key VARCHAR(512) NOT NULL,
cluster_name VARCHAR(255) NOT NULL,
user_name VARCHAR(255) NOT NULL,
display_name VARCHAR(255) NOT NULL,
auth_source VARCHAR(32) NOT NULL,
status VARCHAR(32) NOT NULL,
created_by VARCHAR(255),
created_time BIGINT NOT NULL,
updated_by VARCHAR(255),
updated_time BIGINT NOT NULL,
CONSTRAINT PK_ap_user PRIMARY KEY (user_key)
);
CREATE TABLE IF NOT EXISTS ambari_plus_user_checkin(
user_name VARCHAR(255) NOT NULL,
display_name VARCHAR(255),
previous_login_time BIGINT,
last_login_time BIGINT,
last_active_time BIGINT,
login_count BIGINT NOT NULL DEFAULT 0,
created_time BIGINT NOT NULL,
updated_time BIGINT NOT NULL,
CONSTRAINT PK_ap_user_checkin PRIMARY KEY (user_name)
);
CREATE TABLE IF NOT EXISTS ambari_plus_tenant_member(
membership_key VARCHAR(128) NOT NULL,
tenant_key VARCHAR(100) NOT NULL,
cluster_name VARCHAR(255) NOT NULL,
user_name VARCHAR(255) NOT NULL,
member_role VARCHAR(64) NOT NULL,
status VARCHAR(32) NOT NULL,
source VARCHAR(64) NOT NULL,
created_by VARCHAR(255),
created_time BIGINT NOT NULL,
updated_by VARCHAR(255),
updated_time BIGINT NOT NULL,
CONSTRAINT PK_ap_member PRIMARY KEY (membership_key)
);
CREATE TABLE IF NOT EXISTS ambari_plus_role_def(
role_key VARCHAR(100) NOT NULL,
display_name VARCHAR(255) NOT NULL,
scope_type VARCHAR(32) NOT NULL,
built_in INTEGER NOT NULL,
status VARCHAR(32) NOT NULL,
description VARCHAR(1024),
created_by VARCHAR(255),
created_time BIGINT NOT NULL,
updated_by VARCHAR(255),
updated_time BIGINT NOT NULL,
CONSTRAINT PK_ap_role_def PRIMARY KEY (role_key)
);
CREATE TABLE IF NOT EXISTS ambari_plus_perm_tpl(
permission_key VARCHAR(255) NOT NULL,
object_type VARCHAR(100) NOT NULL,
object_name VARCHAR(255) NOT NULL,
action_name VARCHAR(100) NOT NULL,
scope_type VARCHAR(32) NOT NULL,
built_in INTEGER NOT NULL,
status VARCHAR(32) NOT NULL,
description VARCHAR(1024),
CONSTRAINT PK_ap_perm_tpl PRIMARY KEY (permission_key)
);
CREATE TABLE IF NOT EXISTS ambari_plus_role_binding(
binding_key VARCHAR(128) NOT NULL,
tenant_key VARCHAR(100) NOT NULL,
cluster_name VARCHAR(255) NOT NULL,
role_key VARCHAR(100) NOT NULL,
subject_type VARCHAR(64) NOT NULL,
subject_name VARCHAR(255) NOT NULL,
status VARCHAR(32) NOT NULL,
source VARCHAR(64) NOT NULL,
description VARCHAR(1024),
created_by VARCHAR(255),
created_time BIGINT NOT NULL,
updated_by VARCHAR(255),
updated_time BIGINT NOT NULL,
CONSTRAINT PK_ap_role_bind PRIMARY KEY (binding_key)
);
CREATE TABLE IF NOT EXISTS ambari_plus_tenant_audit(
audit_id VARCHAR(128) NOT NULL,
tenant_key VARCHAR(100),
cluster_name VARCHAR(255),
event_type VARCHAR(100) NOT NULL,
status VARCHAR(32) NOT NULL,
actor VARCHAR(255),
target_type VARCHAR(100),
target_name VARCHAR(255),
execution_target VARCHAR(100),
binding_id VARCHAR(128),
external_id TEXT,
external_name VARCHAR(255),
message VARCHAR(2048),
correlation_id VARCHAR(128),
operation_id VARCHAR(128),
request_id VARCHAR(128),
details_json TEXT,
event_time BIGINT NOT NULL,
CONSTRAINT PK_ap_audit PRIMARY KEY (audit_id)
);
CREATE TABLE IF NOT EXISTS ambari_plus_perm_ext_ref(
ref_key VARCHAR(128) NOT NULL,
tenant_key VARCHAR(100) NOT NULL,
cluster_name VARCHAR(255) NOT NULL,
binding_id VARCHAR(128) NOT NULL,
object_type VARCHAR(100) NOT NULL,
object_name VARCHAR(255) NOT NULL,
subject_type VARCHAR(64) NOT NULL,
subject_name VARCHAR(255) NOT NULL,
execution_target VARCHAR(100) NOT NULL,
external_type VARCHAR(100) NOT NULL,
external_service VARCHAR(255),
external_id VARCHAR(255),
external_name VARCHAR(255),
desired_signature VARCHAR(128),
actual_signature VARCHAR(128),
drift_status VARCHAR(32) NOT NULL,
source VARCHAR(64) NOT NULL,
message VARCHAR(2048),
last_verified_time BIGINT,
last_reconciled_time BIGINT,
created_by VARCHAR(255),
created_time BIGINT NOT NULL,
updated_by VARCHAR(255),
updated_time BIGINT NOT NULL,
CONSTRAINT PK_ap_ext_ref PRIMARY KEY (ref_key)
);
CREATE INDEX idx_ap_user_cluster ON ambari_plus_user (cluster_name);
CREATE UNIQUE INDEX uq_ap_user_cluster_name ON ambari_plus_user (cluster_name, user_name);
CREATE INDEX idx_ap_user_checkin_last_login ON ambari_plus_user_checkin (last_login_time);
CREATE INDEX idx_ap_member_tenant ON ambari_plus_tenant_member (cluster_name, tenant_key);
CREATE UNIQUE INDEX uq_ap_member_user ON ambari_plus_tenant_member (cluster_name, tenant_key, user_name);
CREATE INDEX idx_ap_rb_tenant ON ambari_plus_role_binding (cluster_name, tenant_key);
CREATE INDEX idx_ap_rb_subject ON ambari_plus_role_binding (cluster_name, subject_type, subject_name);
CREATE INDEX idx_ap_audit_tenant ON ambari_plus_tenant_audit (tenant_key);
CREATE INDEX idx_ap_audit_time ON ambari_plus_tenant_audit (event_time);
CREATE INDEX idx_ap_audit_corr ON ambari_plus_tenant_audit (correlation_id);
CREATE INDEX idx_ap_audit_op ON ambari_plus_tenant_audit (operation_id);
CREATE INDEX idx_ap_ext_cluster ON ambari_plus_perm_ext_ref (cluster_name);
CREATE INDEX idx_ap_ext_binding ON ambari_plus_perm_ext_ref (binding_id);
CREATE INDEX idx_ap_ext_target ON ambari_plus_perm_ext_ref (execution_target, drift_status);
CREATE TABLE IF NOT EXISTS ambari_plus_tenant_cluster(
cluster_name VARCHAR(255) NOT NULL,
tenant_key VARCHAR(100) NOT NULL,
ambari_cluster_id BIGINT,
ownership_mode VARCHAR(64) NOT NULL,
status VARCHAR(32) NOT NULL,
source VARCHAR(64) NOT NULL,
created_by VARCHAR(255),
created_time BIGINT NOT NULL,
updated_by VARCHAR(255),
updated_time BIGINT NOT NULL,
CONSTRAINT PK_ap_tenant_cluster PRIMARY KEY (cluster_name)
);
CREATE INDEX idx_ap_tc_tenant ON ambari_plus_tenant_cluster (tenant_key);
CREATE INDEX idx_ap_tc_status ON ambari_plus_tenant_cluster (status);
CREATE INDEX idx_ap_audit_cluster ON ambari_plus_tenant_audit (cluster_name);
CREATE INDEX idx_ap_audit_binding ON ambari_plus_tenant_audit (binding_id);
SET @ap_now := CAST(UNIX_TIMESTAMP(CURRENT_TIMESTAMP(3)) * 1000 AS UNSIGNED);
INSERT INTO ambari_plus_tenant(
tenant_key, display_name, description, status, created_by, created_time, updated_by, updated_time
) VALUES (
'root', 'root', '系统内置租户，用于承载默认资源归属和基础管理员关系。', 'ACTIVE',
'Ttbigdata', @ap_now, 'Ttbigdata', @ap_now
) ON DUPLICATE KEY UPDATE
display_name = VALUES(display_name),
description = VALUES(description),
status = 'ACTIVE',
updated_by = 'Ttbigdata',
updated_time = @ap_now;
INSERT INTO ambari_plus_role_def(
role_key, display_name, scope_type, built_in, status, description, created_by, created_time, updated_by, updated_time
) VALUES (
'tenant_admin', '租户管理员', 'TENANT', 1, 'ACTIVE',
'管理租户成员、角色绑定和集群归属。',
'Ttbigdata', @ap_now, 'Ttbigdata', @ap_now
) ON DUPLICATE KEY UPDATE
display_name = VALUES(display_name),
scope_type = VALUES(scope_type),
built_in = VALUES(built_in),
status = 'ACTIVE',
description = VALUES(description),
updated_by = 'Ttbigdata',
updated_time = @ap_now;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208