Databricks - Setting up Custom External Hive Metastore in Azure using MSSQL Server
So you want to store you Hive database externally instead of the built-in databricks database? I’m not going to ask why, you have reasons, and there’s official document. But i’ll describe the steps that worked for me.
Create SQL Database
First, you need SQL database, i’m not going to describe how you create it, as there are plenty of online resources.
But important things to note from production experience are:
- Name your database in lowercase only.
- Do not use any special characters, i.e. hyphens etc. Name it simple, something like “meta”. There are various bugs in Databricks that do not work well with weird names.
- Even for production workload, Basic pricing tier is more than enough. It’s only metadata that is stored, traffic is very minimal, and Basic limit of 2gb for metadata is impossible to reach.
Initialise Metastore Database
Ok, once done, you need to create necessary tables for metastore DB. If you have an external connectivity to SQL server, just use any tool to connect to it. If you only have connectivity from databricks itself (corporate firewalls etc.) you can run the following Scala code from the notebook itself.
Cell 1. The DDL Script Definition.
1val script = """
2-- Licensed to the Apache Software Foundation (ASF) under one or more
3-- contributor license agreements. See the NOTICE file distributed with
4-- this work for additional information regarding copyright ownership.
5-- The ASF licenses this file to You under the Apache License, Version 2.0
6-- (the "License"); you may not use this file except in compliance with
7-- the License. You may obtain a copy of the License at
8--
9-- http://www.apache.org/licenses/LICENSE-2.0
10--
11-- Unless required by applicable law or agreed to in writing, software
12-- distributed under the License is distributed on an "AS IS" BASIS,
13-- WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
14-- See the License for the specific language governing permissions and
15-- limitations under the License.
16
17------------------------------------------------------------------
18-- DataNucleus SchemaTool (ran at 08/04/2014 15:10:15)
19------------------------------------------------------------------
20-- Complete schema required for the following classes:-
21-- org.apache.hadoop.hive.metastore.model.MColumnDescriptor
22-- org.apache.hadoop.hive.metastore.model.MDBPrivilege
23-- org.apache.hadoop.hive.metastore.model.MDatabase
24-- org.apache.hadoop.hive.metastore.model.MDelegationToken
25-- org.apache.hadoop.hive.metastore.model.MFieldSchema
26-- org.apache.hadoop.hive.metastore.model.MFunction
27-- org.apache.hadoop.hive.metastore.model.MGlobalPrivilege
28-- org.apache.hadoop.hive.metastore.model.MIndex
29-- org.apache.hadoop.hive.metastore.model.MMasterKey
30-- org.apache.hadoop.hive.metastore.model.MOrder
31-- org.apache.hadoop.hive.metastore.model.MPartition
32-- org.apache.hadoop.hive.metastore.model.MPartitionColumnPrivilege
33-- org.apache.hadoop.hive.metastore.model.MPartitionColumnStatistics
34-- org.apache.hadoop.hive.metastore.model.MPartitionEvent
35-- org.apache.hadoop.hive.metastore.model.MPartitionPrivilege
36-- org.apache.hadoop.hive.metastore.model.MResourceUri
37-- org.apache.hadoop.hive.metastore.model.MRole
38-- org.apache.hadoop.hive.metastore.model.MRoleMap
39-- org.apache.hadoop.hive.metastore.model.MSerDeInfo
40-- org.apache.hadoop.hive.metastore.model.MStorageDescriptor
41-- org.apache.hadoop.hive.metastore.model.MStringList
42-- org.apache.hadoop.hive.metastore.model.MTable
43-- org.apache.hadoop.hive.metastore.model.MTableColumnPrivilege
44-- org.apache.hadoop.hive.metastore.model.MTableColumnStatistics
45-- org.apache.hadoop.hive.metastore.model.MTablePrivilege
46-- org.apache.hadoop.hive.metastore.model.MType
47-- org.apache.hadoop.hive.metastore.model.MVersionTable
48--
49-- Table MASTER_KEYS for classes [org.apache.hadoop.hive.metastore.model.MMasterKey]
50CREATE TABLE MASTER_KEYS
51(
52 KEY_ID int NOT NULL,
53 MASTER_KEY nvarchar(767) NULL
54);
55
56ALTER TABLE MASTER_KEYS ADD CONSTRAINT MASTER_KEYS_PK PRIMARY KEY (KEY_ID);
57
58-- Table IDXS for classes [org.apache.hadoop.hive.metastore.model.MIndex]
59CREATE TABLE IDXS
60(
61 INDEX_ID bigint NOT NULL,
62 CREATE_TIME int NOT NULL,
63 DEFERRED_REBUILD bit NOT NULL,
64 INDEX_HANDLER_CLASS nvarchar(4000) NULL,
65 INDEX_NAME nvarchar(128) NULL,
66 INDEX_TBL_ID bigint NULL,
67 LAST_ACCESS_TIME int NOT NULL,
68 ORIG_TBL_ID bigint NULL,
69 SD_ID bigint NULL
70);
71
72ALTER TABLE IDXS ADD CONSTRAINT IDXS_PK PRIMARY KEY (INDEX_ID);
73
74-- Table PART_COL_STATS for classes [org.apache.hadoop.hive.metastore.model.MPartitionColumnStatistics]
75CREATE TABLE PART_COL_STATS
76(
77 CS_ID bigint NOT NULL,
78 AVG_COL_LEN float NULL,
79 "COLUMN_NAME" nvarchar(767) NOT NULL,
80 COLUMN_TYPE nvarchar(128) NOT NULL,
81 DB_NAME nvarchar(128) NOT NULL,
82 BIG_DECIMAL_HIGH_VALUE nvarchar(255) NULL,
83 BIG_DECIMAL_LOW_VALUE nvarchar(255) NULL,
84 DOUBLE_HIGH_VALUE float NULL,
85 DOUBLE_LOW_VALUE float NULL,
86 LAST_ANALYZED bigint NOT NULL,
87 LONG_HIGH_VALUE bigint NULL,
88 LONG_LOW_VALUE bigint NULL,
89 MAX_COL_LEN bigint NULL,
90 NUM_DISTINCTS bigint NULL,
91 NUM_FALSES bigint NULL,
92 NUM_NULLS bigint NOT NULL,
93 NUM_TRUES bigint NULL,
94 PART_ID bigint NULL,
95 PARTITION_NAME nvarchar(767) NOT NULL,
96 "TABLE_NAME" nvarchar(256) NOT NULL
97);
98
99ALTER TABLE PART_COL_STATS ADD CONSTRAINT PART_COL_STATS_PK PRIMARY KEY (CS_ID);
100
101CREATE INDEX PCS_STATS_IDX ON PART_COL_STATS (DB_NAME,TABLE_NAME,COLUMN_NAME,PARTITION_NAME);
102
103-- Table PART_PRIVS for classes [org.apache.hadoop.hive.metastore.model.MPartitionPrivilege]
104CREATE TABLE PART_PRIVS
105(
106 PART_GRANT_ID bigint NOT NULL,
107 CREATE_TIME int NOT NULL,
108 GRANT_OPTION smallint NOT NULL CHECK (GRANT_OPTION IN (0,1)),
109 GRANTOR nvarchar(128) NULL,
110 GRANTOR_TYPE nvarchar(128) NULL,
111 PART_ID bigint NULL,
112 PRINCIPAL_NAME nvarchar(128) NULL,
113 PRINCIPAL_TYPE nvarchar(128) NULL,
114 PART_PRIV nvarchar(128) NULL
115);
116
117ALTER TABLE PART_PRIVS ADD CONSTRAINT PART_PRIVS_PK PRIMARY KEY (PART_GRANT_ID);
118
119-- Table SKEWED_STRING_LIST for classes [org.apache.hadoop.hive.metastore.model.MStringList]
120CREATE TABLE SKEWED_STRING_LIST
121(
122 STRING_LIST_ID bigint NOT NULL
123);
124
125ALTER TABLE SKEWED_STRING_LIST ADD CONSTRAINT SKEWED_STRING_LIST_PK PRIMARY KEY (STRING_LIST_ID);
126
127-- Table ROLES for classes [org.apache.hadoop.hive.metastore.model.MRole]
128CREATE TABLE ROLES
129(
130 ROLE_ID bigint NOT NULL,
131 CREATE_TIME int NOT NULL,
132 OWNER_NAME nvarchar(128) NULL,
133 ROLE_NAME nvarchar(128) NULL
134);
135
136ALTER TABLE ROLES ADD CONSTRAINT ROLES_PK PRIMARY KEY (ROLE_ID);
137
138-- Table PARTITIONS for classes [org.apache.hadoop.hive.metastore.model.MPartition]
139CREATE TABLE PARTITIONS
140(
141 PART_ID bigint NOT NULL,
142 CREATE_TIME int NOT NULL,
143 LAST_ACCESS_TIME int NOT NULL,
144 PART_NAME nvarchar(767) NULL,
145 SD_ID bigint NULL,
146 TBL_ID bigint NULL
147);
148
149ALTER TABLE PARTITIONS ADD CONSTRAINT PARTITIONS_PK PRIMARY KEY (PART_ID);
150
151-- Table CDS for classes [org.apache.hadoop.hive.metastore.model.MColumnDescriptor]
152CREATE TABLE CDS
153(
154 CD_ID bigint NOT NULL
155);
156
157ALTER TABLE CDS ADD CONSTRAINT CDS_PK PRIMARY KEY (CD_ID);
158
159-- Table VERSION for classes [org.apache.hadoop.hive.metastore.model.MVersionTable]
160CREATE TABLE VERSION
161(
162 VER_ID bigint NOT NULL,
163 SCHEMA_VERSION nvarchar(127) NOT NULL,
164 VERSION_COMMENT nvarchar(255) NOT NULL
165);
166
167ALTER TABLE VERSION ADD CONSTRAINT VERSION_PK PRIMARY KEY (VER_ID);
168
169-- Table GLOBAL_PRIVS for classes [org.apache.hadoop.hive.metastore.model.MGlobalPrivilege]
170CREATE TABLE GLOBAL_PRIVS
171(
172 USER_GRANT_ID bigint NOT NULL,
173 CREATE_TIME int NOT NULL,
174 GRANT_OPTION smallint NOT NULL CHECK (GRANT_OPTION IN (0,1)),
175 GRANTOR nvarchar(128) NULL,
176 GRANTOR_TYPE nvarchar(128) NULL,
177 PRINCIPAL_NAME nvarchar(128) NULL,
178 PRINCIPAL_TYPE nvarchar(128) NULL,
179 USER_PRIV nvarchar(128) NULL
180);
181
182ALTER TABLE GLOBAL_PRIVS ADD CONSTRAINT GLOBAL_PRIVS_PK PRIMARY KEY (USER_GRANT_ID);
183
184-- Table PART_COL_PRIVS for classes [org.apache.hadoop.hive.metastore.model.MPartitionColumnPrivilege]
185CREATE TABLE PART_COL_PRIVS
186(
187 PART_COLUMN_GRANT_ID bigint NOT NULL,
188 "COLUMN_NAME" nvarchar(767) NULL,
189 CREATE_TIME int NOT NULL,
190 GRANT_OPTION smallint NOT NULL CHECK (GRANT_OPTION IN (0,1)),
191 GRANTOR nvarchar(128) NULL,
192 GRANTOR_TYPE nvarchar(128) NULL,
193 PART_ID bigint NULL,
194 PRINCIPAL_NAME nvarchar(128) NULL,
195 PRINCIPAL_TYPE nvarchar(128) NULL,
196 PART_COL_PRIV nvarchar(128) NULL
197);
198
199ALTER TABLE PART_COL_PRIVS ADD CONSTRAINT PART_COL_PRIVS_PK PRIMARY KEY (PART_COLUMN_GRANT_ID);
200
201-- Table DB_PRIVS for classes [org.apache.hadoop.hive.metastore.model.MDBPrivilege]
202CREATE TABLE DB_PRIVS
203(
204 DB_GRANT_ID bigint NOT NULL,
205 CREATE_TIME int NOT NULL,
206 DB_ID bigint NULL,
207 GRANT_OPTION smallint NOT NULL CHECK (GRANT_OPTION IN (0,1)),
208 GRANTOR nvarchar(128) NULL,
209 GRANTOR_TYPE nvarchar(128) NULL,
210 PRINCIPAL_NAME nvarchar(128) NULL,
211 PRINCIPAL_TYPE nvarchar(128) NULL,
212 DB_PRIV nvarchar(128) NULL
213);
214
215ALTER TABLE DB_PRIVS ADD CONSTRAINT DB_PRIVS_PK PRIMARY KEY (DB_GRANT_ID);
216
217-- Table TAB_COL_STATS for classes [org.apache.hadoop.hive.metastore.model.MTableColumnStatistics]
218CREATE TABLE TAB_COL_STATS
219(
220 CS_ID bigint NOT NULL,
221 AVG_COL_LEN float NULL,
222 "COLUMN_NAME" nvarchar(767) NOT NULL,
223 COLUMN_TYPE nvarchar(128) NOT NULL,
224 DB_NAME nvarchar(128) NOT NULL,
225 BIG_DECIMAL_HIGH_VALUE nvarchar(255) NULL,
226 BIG_DECIMAL_LOW_VALUE nvarchar(255) NULL,
227 DOUBLE_HIGH_VALUE float NULL,
228 DOUBLE_LOW_VALUE float NULL,
229 LAST_ANALYZED bigint NOT NULL,
230 LONG_HIGH_VALUE bigint NULL,
231 LONG_LOW_VALUE bigint NULL,
232 MAX_COL_LEN bigint NULL,
233 NUM_DISTINCTS bigint NULL,
234 NUM_FALSES bigint NULL,
235 NUM_NULLS bigint NOT NULL,
236 NUM_TRUES bigint NULL,
237 TBL_ID bigint NULL,
238 "TABLE_NAME" nvarchar(256) NOT NULL
239);
240
241ALTER TABLE TAB_COL_STATS ADD CONSTRAINT TAB_COL_STATS_PK PRIMARY KEY (CS_ID);
242
243-- Table TYPES for classes [org.apache.hadoop.hive.metastore.model.MType]
244CREATE TABLE TYPES
245(
246 TYPES_ID bigint NOT NULL,
247 TYPE_NAME nvarchar(128) NULL,
248 TYPE1 nvarchar(767) NULL,
249 TYPE2 nvarchar(767) NULL
250);
251
252ALTER TABLE TYPES ADD CONSTRAINT TYPES_PK PRIMARY KEY (TYPES_ID);
253
254-- Table TBL_PRIVS for classes [org.apache.hadoop.hive.metastore.model.MTablePrivilege]
255CREATE TABLE TBL_PRIVS
256(
257 TBL_GRANT_ID bigint NOT NULL,
258 CREATE_TIME int NOT NULL,
259 GRANT_OPTION smallint NOT NULL CHECK (GRANT_OPTION IN (0,1)),
260 GRANTOR nvarchar(128) NULL,
261 GRANTOR_TYPE nvarchar(128) NULL,
262 PRINCIPAL_NAME nvarchar(128) NULL,
263 PRINCIPAL_TYPE nvarchar(128) NULL,
264 TBL_PRIV nvarchar(128) NULL,
265 TBL_ID bigint NULL
266);
267
268ALTER TABLE TBL_PRIVS ADD CONSTRAINT TBL_PRIVS_PK PRIMARY KEY (TBL_GRANT_ID);
269
270-- Table DBS for classes [org.apache.hadoop.hive.metastore.model.MDatabase]
271CREATE TABLE DBS
272(
273 DB_ID bigint NOT NULL,
274 "DESC" nvarchar(4000) NULL,
275 DB_LOCATION_URI nvarchar(4000) NOT NULL,
276 "NAME" nvarchar(128) NULL,
277 OWNER_NAME nvarchar(128) NULL,
278 OWNER_TYPE nvarchar(10) NULL
279);
280
281ALTER TABLE DBS ADD CONSTRAINT DBS_PK PRIMARY KEY (DB_ID);
282
283-- Table TBL_COL_PRIVS for classes [org.apache.hadoop.hive.metastore.model.MTableColumnPrivilege]
284CREATE TABLE TBL_COL_PRIVS
285(
286 TBL_COLUMN_GRANT_ID bigint NOT NULL,
287 "COLUMN_NAME" nvarchar(767) NULL,
288 CREATE_TIME int NOT NULL,
289 GRANT_OPTION smallint NOT NULL CHECK (GRANT_OPTION IN (0,1)),
290 GRANTOR nvarchar(128) NULL,
291 GRANTOR_TYPE nvarchar(128) NULL,
292 PRINCIPAL_NAME nvarchar(128) NULL,
293 PRINCIPAL_TYPE nvarchar(128) NULL,
294 TBL_COL_PRIV nvarchar(128) NULL,
295 TBL_ID bigint NULL
296);
297
298ALTER TABLE TBL_COL_PRIVS ADD CONSTRAINT TBL_COL_PRIVS_PK PRIMARY KEY (TBL_COLUMN_GRANT_ID);
299
300-- Table DELEGATION_TOKENS for classes [org.apache.hadoop.hive.metastore.model.MDelegationToken]
301CREATE TABLE DELEGATION_TOKENS
302(
303 TOKEN_IDENT nvarchar(767) NOT NULL,
304 TOKEN nvarchar(767) NULL
305);
306
307ALTER TABLE DELEGATION_TOKENS ADD CONSTRAINT DELEGATION_TOKENS_PK PRIMARY KEY (TOKEN_IDENT);
308
309-- Table SERDES for classes [org.apache.hadoop.hive.metastore.model.MSerDeInfo]
310CREATE TABLE SERDES
311(
312 SERDE_ID bigint NOT NULL,
313 "NAME" nvarchar(128) NULL,
314 SLIB nvarchar(4000) NULL
315);
316
317ALTER TABLE SERDES ADD CONSTRAINT SERDES_PK PRIMARY KEY (SERDE_ID);
318
319-- Table FUNCS for classes [org.apache.hadoop.hive.metastore.model.MFunction]
320CREATE TABLE FUNCS
321(
322 FUNC_ID bigint NOT NULL,
323 CLASS_NAME nvarchar(4000) NULL,
324 CREATE_TIME int NOT NULL,
325 DB_ID bigint NULL,
326 FUNC_NAME nvarchar(128) NULL,
327 FUNC_TYPE int NOT NULL,
328 OWNER_NAME nvarchar(128) NULL,
329 OWNER_TYPE nvarchar(10) NULL
330);
331
332ALTER TABLE FUNCS ADD CONSTRAINT FUNCS_PK PRIMARY KEY (FUNC_ID);
333
334-- Table ROLE_MAP for classes [org.apache.hadoop.hive.metastore.model.MRoleMap]
335CREATE TABLE ROLE_MAP
336(
337 ROLE_GRANT_ID bigint NOT NULL,
338 ADD_TIME int NOT NULL,
339 GRANT_OPTION smallint NOT NULL CHECK (GRANT_OPTION IN (0,1)),
340 GRANTOR nvarchar(128) NULL,
341 GRANTOR_TYPE nvarchar(128) NULL,
342 PRINCIPAL_NAME nvarchar(128) NULL,
343 PRINCIPAL_TYPE nvarchar(128) NULL,
344 ROLE_ID bigint NULL
345);
346
347ALTER TABLE ROLE_MAP ADD CONSTRAINT ROLE_MAP_PK PRIMARY KEY (ROLE_GRANT_ID);
348
349-- Table TBLS for classes [org.apache.hadoop.hive.metastore.model.MTable]
350CREATE TABLE TBLS
351(
352 TBL_ID bigint NOT NULL,
353 CREATE_TIME int NOT NULL,
354 DB_ID bigint NULL,
355 LAST_ACCESS_TIME int NOT NULL,
356 OWNER nvarchar(767) NULL,
357 RETENTION int NOT NULL,
358 SD_ID bigint NULL,
359 TBL_NAME nvarchar(256) NULL,
360 TBL_TYPE nvarchar(128) NULL,
361 VIEW_EXPANDED_TEXT text NULL,
362 VIEW_ORIGINAL_TEXT text NULL,
363 IS_REWRITE_ENABLED bit NOT NULL DEFAULT 0
364);
365
366ALTER TABLE TBLS ADD CONSTRAINT TBLS_PK PRIMARY KEY (TBL_ID);
367
368-- Table SDS for classes [org.apache.hadoop.hive.metastore.model.MStorageDescriptor]
369CREATE TABLE SDS
370(
371 SD_ID bigint NOT NULL,
372 CD_ID bigint NULL,
373 INPUT_FORMAT nvarchar(4000) NULL,
374 IS_COMPRESSED bit NOT NULL,
375 IS_STOREDASSUBDIRECTORIES bit NOT NULL,
376 LOCATION nvarchar(4000) NULL,
377 NUM_BUCKETS int NOT NULL,
378 OUTPUT_FORMAT nvarchar(4000) NULL,
379 SERDE_ID bigint NULL
380);
381
382ALTER TABLE SDS ADD CONSTRAINT SDS_PK PRIMARY KEY (SD_ID);
383
384-- Table PARTITION_EVENTS for classes [org.apache.hadoop.hive.metastore.model.MPartitionEvent]
385CREATE TABLE PARTITION_EVENTS
386(
387 PART_NAME_ID bigint NOT NULL,
388 DB_NAME nvarchar(128) NULL,
389 EVENT_TIME bigint NOT NULL,
390 EVENT_TYPE int NOT NULL,
391 PARTITION_NAME nvarchar(767) NULL,
392 TBL_NAME nvarchar(256) NULL
393);
394
395ALTER TABLE PARTITION_EVENTS ADD CONSTRAINT PARTITION_EVENTS_PK PRIMARY KEY (PART_NAME_ID);
396
397-- Table SORT_COLS for join relationship
398CREATE TABLE SORT_COLS
399(
400 SD_ID bigint NOT NULL,
401 "COLUMN_NAME" nvarchar(767) NULL,
402 "ORDER" int NOT NULL,
403 INTEGER_IDX int NOT NULL
404);
405
406ALTER TABLE SORT_COLS ADD CONSTRAINT SORT_COLS_PK PRIMARY KEY (SD_ID,INTEGER_IDX);
407
408-- Table SKEWED_COL_NAMES for join relationship
409CREATE TABLE SKEWED_COL_NAMES
410(
411 SD_ID bigint NOT NULL,
412 SKEWED_COL_NAME nvarchar(255) NULL,
413 INTEGER_IDX int NOT NULL
414);
415
416ALTER TABLE SKEWED_COL_NAMES ADD CONSTRAINT SKEWED_COL_NAMES_PK PRIMARY KEY (SD_ID,INTEGER_IDX);
417
418-- Table SKEWED_COL_VALUE_LOC_MAP for join relationship
419CREATE TABLE SKEWED_COL_VALUE_LOC_MAP
420(
421 SD_ID bigint NOT NULL,
422 STRING_LIST_ID_KID bigint NOT NULL,
423 LOCATION nvarchar(4000) NULL
424);
425
426ALTER TABLE SKEWED_COL_VALUE_LOC_MAP ADD CONSTRAINT SKEWED_COL_VALUE_LOC_MAP_PK PRIMARY KEY (SD_ID,STRING_LIST_ID_KID);
427
428-- Table SKEWED_STRING_LIST_VALUES for join relationship
429CREATE TABLE SKEWED_STRING_LIST_VALUES
430(
431 STRING_LIST_ID bigint NOT NULL,
432 STRING_LIST_VALUE nvarchar(255) NULL,
433 INTEGER_IDX int NOT NULL
434);
435
436ALTER TABLE SKEWED_STRING_LIST_VALUES ADD CONSTRAINT SKEWED_STRING_LIST_VALUES_PK PRIMARY KEY (STRING_LIST_ID,INTEGER_IDX);
437
438-- Table PARTITION_KEY_VALS for join relationship
439CREATE TABLE PARTITION_KEY_VALS
440(
441 PART_ID bigint NOT NULL,
442 PART_KEY_VAL nvarchar(255) NULL,
443 INTEGER_IDX int NOT NULL
444);
445
446ALTER TABLE PARTITION_KEY_VALS ADD CONSTRAINT PARTITION_KEY_VALS_PK PRIMARY KEY (PART_ID,INTEGER_IDX);
447
448-- Table PARTITION_KEYS for join relationship
449CREATE TABLE PARTITION_KEYS
450(
451 TBL_ID bigint NOT NULL,
452 PKEY_COMMENT nvarchar(4000) NULL,
453 PKEY_NAME nvarchar(128) NOT NULL,
454 PKEY_TYPE nvarchar(767) NOT NULL,
455 INTEGER_IDX int NOT NULL
456);
457
458ALTER TABLE PARTITION_KEYS ADD CONSTRAINT PARTITION_KEY_PK PRIMARY KEY (TBL_ID,PKEY_NAME);
459
460-- Table SKEWED_VALUES for join relationship
461CREATE TABLE SKEWED_VALUES
462(
463 SD_ID_OID bigint NOT NULL,
464 STRING_LIST_ID_EID bigint NULL,
465 INTEGER_IDX int NOT NULL
466);
467
468ALTER TABLE SKEWED_VALUES ADD CONSTRAINT SKEWED_VALUES_PK PRIMARY KEY (SD_ID_OID,INTEGER_IDX);
469
470-- Table SD_PARAMS for join relationship
471CREATE TABLE SD_PARAMS
472(
473 SD_ID bigint NOT NULL,
474 PARAM_KEY nvarchar(256) NOT NULL,
475 PARAM_VALUE varchar(max) NULL
476);
477
478ALTER TABLE SD_PARAMS ADD CONSTRAINT SD_PARAMS_PK PRIMARY KEY (SD_ID,PARAM_KEY);
479
480-- Table FUNC_RU for join relationship
481CREATE TABLE FUNC_RU
482(
483 FUNC_ID bigint NOT NULL,
484 RESOURCE_TYPE int NOT NULL,
485 RESOURCE_URI nvarchar(4000) NULL,
486 INTEGER_IDX int NOT NULL
487);
488
489ALTER TABLE FUNC_RU ADD CONSTRAINT FUNC_RU_PK PRIMARY KEY (FUNC_ID,INTEGER_IDX);
490
491-- Table TYPE_FIELDS for join relationship
492CREATE TABLE TYPE_FIELDS
493(
494 TYPE_NAME bigint NOT NULL,
495 COMMENT nvarchar(256) NULL,
496 FIELD_NAME nvarchar(128) NOT NULL,
497 FIELD_TYPE nvarchar(767) NOT NULL,
498 INTEGER_IDX int NOT NULL
499);
500
501ALTER TABLE TYPE_FIELDS ADD CONSTRAINT TYPE_FIELDS_PK PRIMARY KEY (TYPE_NAME,FIELD_NAME);
502
503-- Table BUCKETING_COLS for join relationship
504CREATE TABLE BUCKETING_COLS
505(
506 SD_ID bigint NOT NULL,
507 BUCKET_COL_NAME nvarchar(255) NULL,
508 INTEGER_IDX int NOT NULL
509);
510
511ALTER TABLE BUCKETING_COLS ADD CONSTRAINT BUCKETING_COLS_PK PRIMARY KEY (SD_ID,INTEGER_IDX);
512
513-- Table DATABASE_PARAMS for join relationship
514CREATE TABLE DATABASE_PARAMS
515(
516 DB_ID bigint NOT NULL,
517 PARAM_KEY nvarchar(180) NOT NULL,
518 PARAM_VALUE nvarchar(4000) NULL
519);
520
521ALTER TABLE DATABASE_PARAMS ADD CONSTRAINT DATABASE_PARAMS_PK PRIMARY KEY (DB_ID,PARAM_KEY);
522
523-- Table INDEX_PARAMS for join relationship
524CREATE TABLE INDEX_PARAMS
525(
526 INDEX_ID bigint NOT NULL,
527 PARAM_KEY nvarchar(256) NOT NULL,
528 PARAM_VALUE nvarchar(4000) NULL
529);
530
531ALTER TABLE INDEX_PARAMS ADD CONSTRAINT INDEX_PARAMS_PK PRIMARY KEY (INDEX_ID,PARAM_KEY);
532
533-- Table COLUMNS_V2 for join relationship
534CREATE TABLE COLUMNS_V2
535(
536 CD_ID bigint NOT NULL,
537 COMMENT nvarchar(256) NULL,
538 "COLUMN_NAME" nvarchar(767) NOT NULL,
539 TYPE_NAME varchar(max) NOT NULL,
540 INTEGER_IDX int NOT NULL
541);
542
543ALTER TABLE COLUMNS_V2 ADD CONSTRAINT COLUMNS_PK PRIMARY KEY (CD_ID,"COLUMN_NAME");
544
545-- Table SERDE_PARAMS for join relationship
546CREATE TABLE SERDE_PARAMS
547(
548 SERDE_ID bigint NOT NULL,
549 PARAM_KEY nvarchar(256) NOT NULL,
550 PARAM_VALUE varchar(max) NULL
551);
552
553ALTER TABLE SERDE_PARAMS ADD CONSTRAINT SERDE_PARAMS_PK PRIMARY KEY (SERDE_ID,PARAM_KEY);
554
555-- Table PARTITION_PARAMS for join relationship
556CREATE TABLE PARTITION_PARAMS
557(
558 PART_ID bigint NOT NULL,
559 PARAM_KEY nvarchar(256) NOT NULL,
560 PARAM_VALUE nvarchar(4000) NULL
561);
562
563ALTER TABLE PARTITION_PARAMS ADD CONSTRAINT PARTITION_PARAMS_PK PRIMARY KEY (PART_ID,PARAM_KEY);
564
565-- Table TABLE_PARAMS for join relationship
566CREATE TABLE TABLE_PARAMS
567(
568 TBL_ID bigint NOT NULL,
569 PARAM_KEY nvarchar(256) NOT NULL,
570 PARAM_VALUE varchar(max) NULL
571);
572
573ALTER TABLE TABLE_PARAMS ADD CONSTRAINT TABLE_PARAMS_PK PRIMARY KEY (TBL_ID,PARAM_KEY);
574
575CREATE TABLE NOTIFICATION_LOG
576(
577 NL_ID bigint NOT NULL,
578 EVENT_ID bigint NOT NULL,
579 EVENT_TIME int NOT NULL,
580 EVENT_TYPE nvarchar(32) NOT NULL,
581 DB_NAME nvarchar(128) NULL,
582 TBL_NAME nvarchar(256) NULL,
583 MESSAGE_FORMAT nvarchar(16),
584 MESSAGE text NULL
585);
586
587ALTER TABLE NOTIFICATION_LOG ADD CONSTRAINT NOTIFICATION_LOG_PK PRIMARY KEY (NL_ID);
588
589CREATE TABLE NOTIFICATION_SEQUENCE
590(
591 NNI_ID bigint NOT NULL,
592 NEXT_EVENT_ID bigint NOT NULL
593);
594
595ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT NOTIFICATION_SEQUENCE_PK PRIMARY KEY (NNI_ID);
596
597-- Constraints for table MASTER_KEYS for class(es) [org.apache.hadoop.hive.metastore.model.MMasterKey]
598
599-- Constraints for table IDXS for class(es) [org.apache.hadoop.hive.metastore.model.MIndex]
600ALTER TABLE IDXS ADD CONSTRAINT IDXS_FK1 FOREIGN KEY (INDEX_TBL_ID) REFERENCES TBLS (TBL_ID) ;
601
602ALTER TABLE IDXS ADD CONSTRAINT IDXS_FK2 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID) ;
603
604ALTER TABLE IDXS ADD CONSTRAINT IDXS_FK3 FOREIGN KEY (ORIG_TBL_ID) REFERENCES TBLS (TBL_ID) ;
605
606CREATE UNIQUE INDEX UNIQUEINDEX ON IDXS (INDEX_NAME,ORIG_TBL_ID);
607
608CREATE INDEX IDXS_N51 ON IDXS (SD_ID);
609
610CREATE INDEX IDXS_N50 ON IDXS (ORIG_TBL_ID);
611
612CREATE INDEX IDXS_N49 ON IDXS (INDEX_TBL_ID);
613
614
615-- Constraints for table PART_COL_STATS for class(es) [org.apache.hadoop.hive.metastore.model.MPartitionColumnStatistics]
616ALTER TABLE PART_COL_STATS ADD CONSTRAINT PART_COL_STATS_FK1 FOREIGN KEY (PART_ID) REFERENCES PARTITIONS (PART_ID) ;
617
618CREATE INDEX PART_COL_STATS_N49 ON PART_COL_STATS (PART_ID);
619
620
621-- Constraints for table PART_PRIVS for class(es) [org.apache.hadoop.hive.metastore.model.MPartitionPrivilege]
622ALTER TABLE PART_PRIVS ADD CONSTRAINT PART_PRIVS_FK1 FOREIGN KEY (PART_ID) REFERENCES PARTITIONS (PART_ID) ;
623
624CREATE INDEX PARTPRIVILEGEINDEX ON PART_PRIVS (PART_ID,PRINCIPAL_NAME,PRINCIPAL_TYPE,PART_PRIV,GRANTOR,GRANTOR_TYPE);
625
626CREATE INDEX PART_PRIVS_N49 ON PART_PRIVS (PART_ID);
627
628
629-- Constraints for table SKEWED_STRING_LIST for class(es) [org.apache.hadoop.hive.metastore.model.MStringList]
630
631-- Constraints for table ROLES for class(es) [org.apache.hadoop.hive.metastore.model.MRole]
632CREATE UNIQUE INDEX ROLEENTITYINDEX ON ROLES (ROLE_NAME);
633
634
635-- Constraints for table PARTITIONS for class(es) [org.apache.hadoop.hive.metastore.model.MPartition]
636ALTER TABLE PARTITIONS ADD CONSTRAINT PARTITIONS_FK1 FOREIGN KEY (TBL_ID) REFERENCES TBLS (TBL_ID) ;
637
638ALTER TABLE PARTITIONS ADD CONSTRAINT PARTITIONS_FK2 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID) ;
639
640CREATE INDEX PARTITIONS_N49 ON PARTITIONS (SD_ID);
641
642CREATE INDEX PARTITIONS_N50 ON PARTITIONS (TBL_ID);
643
644CREATE UNIQUE INDEX UNIQUEPARTITION ON PARTITIONS (PART_NAME,TBL_ID);
645
646
647-- Constraints for table CDS for class(es) [org.apache.hadoop.hive.metastore.model.MColumnDescriptor]
648
649-- Constraints for table VERSION for class(es) [org.apache.hadoop.hive.metastore.model.MVersionTable]
650
651-- Constraints for table GLOBAL_PRIVS for class(es) [org.apache.hadoop.hive.metastore.model.MGlobalPrivilege]
652CREATE UNIQUE INDEX GLOBALPRIVILEGEINDEX ON GLOBAL_PRIVS (PRINCIPAL_NAME,PRINCIPAL_TYPE,USER_PRIV,GRANTOR,GRANTOR_TYPE);
653
654
655-- Constraints for table PART_COL_PRIVS for class(es) [org.apache.hadoop.hive.metastore.model.MPartitionColumnPrivilege]
656ALTER TABLE PART_COL_PRIVS ADD CONSTRAINT PART_COL_PRIVS_FK1 FOREIGN KEY (PART_ID) REFERENCES PARTITIONS (PART_ID) ;
657
658CREATE INDEX PART_COL_PRIVS_N49 ON PART_COL_PRIVS (PART_ID);
659
660CREATE INDEX PARTITIONCOLUMNPRIVILEGEINDEX ON PART_COL_PRIVS (PART_ID,"COLUMN_NAME",PRINCIPAL_NAME,PRINCIPAL_TYPE,PART_COL_PRIV,GRANTOR,GRANTOR_TYPE);
661
662
663-- Constraints for table DB_PRIVS for class(es) [org.apache.hadoop.hive.metastore.model.MDBPrivilege]
664ALTER TABLE DB_PRIVS ADD CONSTRAINT DB_PRIVS_FK1 FOREIGN KEY (DB_ID) REFERENCES DBS (DB_ID) ;
665
666CREATE UNIQUE INDEX DBPRIVILEGEINDEX ON DB_PRIVS (DB_ID,PRINCIPAL_NAME,PRINCIPAL_TYPE,DB_PRIV,GRANTOR,GRANTOR_TYPE);
667
668CREATE INDEX DB_PRIVS_N49 ON DB_PRIVS (DB_ID);
669
670
671-- Constraints for table TAB_COL_STATS for class(es) [org.apache.hadoop.hive.metastore.model.MTableColumnStatistics]
672ALTER TABLE TAB_COL_STATS ADD CONSTRAINT TAB_COL_STATS_FK1 FOREIGN KEY (TBL_ID) REFERENCES TBLS (TBL_ID) ;
673
674CREATE INDEX TAB_COL_STATS_N49 ON TAB_COL_STATS (TBL_ID);
675
676
677-- Constraints for table TYPES for class(es) [org.apache.hadoop.hive.metastore.model.MType]
678CREATE UNIQUE INDEX UNIQUETYPE ON TYPES (TYPE_NAME);
679
680
681-- Constraints for table TBL_PRIVS for class(es) [org.apache.hadoop.hive.metastore.model.MTablePrivilege]
682ALTER TABLE TBL_PRIVS ADD CONSTRAINT TBL_PRIVS_FK1 FOREIGN KEY (TBL_ID) REFERENCES TBLS (TBL_ID) ;
683
684CREATE INDEX TBL_PRIVS_N49 ON TBL_PRIVS (TBL_ID);
685
686CREATE INDEX TABLEPRIVILEGEINDEX ON TBL_PRIVS (TBL_ID,PRINCIPAL_NAME,PRINCIPAL_TYPE,TBL_PRIV,GRANTOR,GRANTOR_TYPE);
687
688
689-- Constraints for table DBS for class(es) [org.apache.hadoop.hive.metastore.model.MDatabase]
690CREATE UNIQUE INDEX UNIQUEDATABASE ON DBS ("NAME");
691
692
693-- Constraints for table TBL_COL_PRIVS for class(es) [org.apache.hadoop.hive.metastore.model.MTableColumnPrivilege]
694ALTER TABLE TBL_COL_PRIVS ADD CONSTRAINT TBL_COL_PRIVS_FK1 FOREIGN KEY (TBL_ID) REFERENCES TBLS (TBL_ID) ;
695
696CREATE INDEX TABLECOLUMNPRIVILEGEINDEX ON TBL_COL_PRIVS (TBL_ID,"COLUMN_NAME",PRINCIPAL_NAME,PRINCIPAL_TYPE,TBL_COL_PRIV,GRANTOR,GRANTOR_TYPE);
697
698CREATE INDEX TBL_COL_PRIVS_N49 ON TBL_COL_PRIVS (TBL_ID);
699
700
701-- Constraints for table DELEGATION_TOKENS for class(es) [org.apache.hadoop.hive.metastore.model.MDelegationToken]
702
703-- Constraints for table SERDES for class(es) [org.apache.hadoop.hive.metastore.model.MSerDeInfo]
704
705-- Constraints for table FUNCS for class(es) [org.apache.hadoop.hive.metastore.model.MFunction]
706ALTER TABLE FUNCS ADD CONSTRAINT FUNCS_FK1 FOREIGN KEY (DB_ID) REFERENCES DBS (DB_ID) ;
707
708CREATE UNIQUE INDEX UNIQUEFUNCTION ON FUNCS (FUNC_NAME,DB_ID);
709
710CREATE INDEX FUNCS_N49 ON FUNCS (DB_ID);
711
712
713-- Constraints for table ROLE_MAP for class(es) [org.apache.hadoop.hive.metastore.model.MRoleMap]
714ALTER TABLE ROLE_MAP ADD CONSTRAINT ROLE_MAP_FK1 FOREIGN KEY (ROLE_ID) REFERENCES ROLES (ROLE_ID) ;
715
716CREATE INDEX ROLE_MAP_N49 ON ROLE_MAP (ROLE_ID);
717
718CREATE UNIQUE INDEX USERROLEMAPINDEX ON ROLE_MAP (PRINCIPAL_NAME,ROLE_ID,GRANTOR,GRANTOR_TYPE);
719
720
721-- Constraints for table TBLS for class(es) [org.apache.hadoop.hive.metastore.model.MTable]
722ALTER TABLE TBLS ADD CONSTRAINT TBLS_FK2 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID) ;
723
724ALTER TABLE TBLS ADD CONSTRAINT TBLS_FK1 FOREIGN KEY (DB_ID) REFERENCES DBS (DB_ID) ;
725
726CREATE INDEX TBLS_N50 ON TBLS (SD_ID);
727
728CREATE UNIQUE INDEX UNIQUETABLE ON TBLS (TBL_NAME,DB_ID);
729
730CREATE INDEX TBLS_N49 ON TBLS (DB_ID);
731
732
733-- Constraints for table SDS for class(es) [org.apache.hadoop.hive.metastore.model.MStorageDescriptor]
734ALTER TABLE SDS ADD CONSTRAINT SDS_FK1 FOREIGN KEY (SERDE_ID) REFERENCES SERDES (SERDE_ID) ;
735
736ALTER TABLE SDS ADD CONSTRAINT SDS_FK2 FOREIGN KEY (CD_ID) REFERENCES CDS (CD_ID) ;
737
738CREATE INDEX SDS_N50 ON SDS (CD_ID);
739
740CREATE INDEX SDS_N49 ON SDS (SERDE_ID);
741
742
743-- Constraints for table PARTITION_EVENTS for class(es) [org.apache.hadoop.hive.metastore.model.MPartitionEvent]
744CREATE INDEX PARTITIONEVENTINDEX ON PARTITION_EVENTS (PARTITION_NAME);
745
746
747-- Constraints for table SORT_COLS
748ALTER TABLE SORT_COLS ADD CONSTRAINT SORT_COLS_FK1 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID) ;
749
750CREATE INDEX SORT_COLS_N49 ON SORT_COLS (SD_ID);
751
752
753-- Constraints for table SKEWED_COL_NAMES
754ALTER TABLE SKEWED_COL_NAMES ADD CONSTRAINT SKEWED_COL_NAMES_FK1 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID) ;
755
756CREATE INDEX SKEWED_COL_NAMES_N49 ON SKEWED_COL_NAMES (SD_ID);
757
758
759-- Constraints for table SKEWED_COL_VALUE_LOC_MAP
760ALTER TABLE SKEWED_COL_VALUE_LOC_MAP ADD CONSTRAINT SKEWED_COL_VALUE_LOC_MAP_FK1 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID) ;
761
762ALTER TABLE SKEWED_COL_VALUE_LOC_MAP ADD CONSTRAINT SKEWED_COL_VALUE_LOC_MAP_FK2 FOREIGN KEY (STRING_LIST_ID_KID) REFERENCES SKEWED_STRING_LIST (STRING_LIST_ID) ;
763
764CREATE INDEX SKEWED_COL_VALUE_LOC_MAP_N50 ON SKEWED_COL_VALUE_LOC_MAP (STRING_LIST_ID_KID);
765
766CREATE INDEX SKEWED_COL_VALUE_LOC_MAP_N49 ON SKEWED_COL_VALUE_LOC_MAP (SD_ID);
767
768
769-- Constraints for table SKEWED_STRING_LIST_VALUES
770ALTER TABLE SKEWED_STRING_LIST_VALUES ADD CONSTRAINT SKEWED_STRING_LIST_VALUES_FK1 FOREIGN KEY (STRING_LIST_ID) REFERENCES SKEWED_STRING_LIST (STRING_LIST_ID) ;
771
772CREATE INDEX SKEWED_STRING_LIST_VALUES_N49 ON SKEWED_STRING_LIST_VALUES (STRING_LIST_ID);
773
774
775-- Constraints for table PARTITION_KEY_VALS
776ALTER TABLE PARTITION_KEY_VALS ADD CONSTRAINT PARTITION_KEY_VALS_FK1 FOREIGN KEY (PART_ID) REFERENCES PARTITIONS (PART_ID) ;
777
778CREATE INDEX PARTITION_KEY_VALS_N49 ON PARTITION_KEY_VALS (PART_ID);
779
780
781-- Constraints for table PARTITION_KEYS
782ALTER TABLE PARTITION_KEYS ADD CONSTRAINT PARTITION_KEYS_FK1 FOREIGN KEY (TBL_ID) REFERENCES TBLS (TBL_ID) ;
783
784CREATE INDEX PARTITION_KEYS_N49 ON PARTITION_KEYS (TBL_ID);
785
786
787-- Constraints for table SKEWED_VALUES
788ALTER TABLE SKEWED_VALUES ADD CONSTRAINT SKEWED_VALUES_FK1 FOREIGN KEY (SD_ID_OID) REFERENCES SDS (SD_ID) ;
789
790ALTER TABLE SKEWED_VALUES ADD CONSTRAINT SKEWED_VALUES_FK2 FOREIGN KEY (STRING_LIST_ID_EID) REFERENCES SKEWED_STRING_LIST (STRING_LIST_ID) ;
791
792CREATE INDEX SKEWED_VALUES_N50 ON SKEWED_VALUES (SD_ID_OID);
793
794CREATE INDEX SKEWED_VALUES_N49 ON SKEWED_VALUES (STRING_LIST_ID_EID);
795
796
797-- Constraints for table SD_PARAMS
798ALTER TABLE SD_PARAMS ADD CONSTRAINT SD_PARAMS_FK1 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID) ;
799
800CREATE INDEX SD_PARAMS_N49 ON SD_PARAMS (SD_ID);
801
802
803-- Constraints for table FUNC_RU
804ALTER TABLE FUNC_RU ADD CONSTRAINT FUNC_RU_FK1 FOREIGN KEY (FUNC_ID) REFERENCES FUNCS (FUNC_ID) ;
805
806CREATE INDEX FUNC_RU_N49 ON FUNC_RU (FUNC_ID);
807
808
809-- Constraints for table TYPE_FIELDS
810ALTER TABLE TYPE_FIELDS ADD CONSTRAINT TYPE_FIELDS_FK1 FOREIGN KEY (TYPE_NAME) REFERENCES TYPES (TYPES_ID) ;
811
812CREATE INDEX TYPE_FIELDS_N49 ON TYPE_FIELDS (TYPE_NAME);
813
814
815-- Constraints for table BUCKETING_COLS
816ALTER TABLE BUCKETING_COLS ADD CONSTRAINT BUCKETING_COLS_FK1 FOREIGN KEY (SD_ID) REFERENCES SDS (SD_ID) ;
817
818CREATE INDEX BUCKETING_COLS_N49 ON BUCKETING_COLS (SD_ID);
819
820
821-- Constraints for table DATABASE_PARAMS
822ALTER TABLE DATABASE_PARAMS ADD CONSTRAINT DATABASE_PARAMS_FK1 FOREIGN KEY (DB_ID) REFERENCES DBS (DB_ID) ;
823
824CREATE INDEX DATABASE_PARAMS_N49 ON DATABASE_PARAMS (DB_ID);
825
826
827-- Constraints for table INDEX_PARAMS
828ALTER TABLE INDEX_PARAMS ADD CONSTRAINT INDEX_PARAMS_FK1 FOREIGN KEY (INDEX_ID) REFERENCES IDXS (INDEX_ID) ;
829
830CREATE INDEX INDEX_PARAMS_N49 ON INDEX_PARAMS (INDEX_ID);
831
832
833-- Constraints for table COLUMNS_V2
834ALTER TABLE COLUMNS_V2 ADD CONSTRAINT COLUMNS_V2_FK1 FOREIGN KEY (CD_ID) REFERENCES CDS (CD_ID) ;
835
836CREATE INDEX COLUMNS_V2_N49 ON COLUMNS_V2 (CD_ID);
837
838
839-- Constraints for table SERDE_PARAMS
840ALTER TABLE SERDE_PARAMS ADD CONSTRAINT SERDE_PARAMS_FK1 FOREIGN KEY (SERDE_ID) REFERENCES SERDES (SERDE_ID) ;
841
842CREATE INDEX SERDE_PARAMS_N49 ON SERDE_PARAMS (SERDE_ID);
843
844
845-- Constraints for table PARTITION_PARAMS
846ALTER TABLE PARTITION_PARAMS ADD CONSTRAINT PARTITION_PARAMS_FK1 FOREIGN KEY (PART_ID) REFERENCES PARTITIONS (PART_ID) ;
847
848CREATE INDEX PARTITION_PARAMS_N49 ON PARTITION_PARAMS (PART_ID);
849
850
851-- Constraints for table TABLE_PARAMS
852ALTER TABLE TABLE_PARAMS ADD CONSTRAINT TABLE_PARAMS_FK1 FOREIGN KEY (TBL_ID) REFERENCES TBLS (TBL_ID) ;
853
854CREATE INDEX TABLE_PARAMS_N49 ON TABLE_PARAMS (TBL_ID);
855
856
857
858-- -----------------------------------------------------------------------------------------------------------------------------------------------
859-- Transaction and Lock Tables
860-- These are not part of package jdo, so if you are going to regenerate this file you need to manually add the following section back to the file.
861-- -----------------------------------------------------------------------------------------------------------------------------------------------
862CREATE TABLE COMPACTION_QUEUE(
863 CQ_ID bigint NOT NULL,
864 CQ_DATABASE nvarchar(128) NOT NULL,
865 CQ_TABLE nvarchar(128) NOT NULL,
866 CQ_PARTITION nvarchar(767) NULL,
867 CQ_STATE char(1) NOT NULL,
868 CQ_TYPE char(1) NOT NULL,
869 CQ_TBLPROPERTIES nvarchar(2048) NULL,
870 CQ_WORKER_ID nvarchar(128) NULL,
871 CQ_START bigint NULL,
872 CQ_RUN_AS nvarchar(128) NULL,
873 CQ_HIGHEST_TXN_ID bigint NULL,
874 CQ_META_INFO varbinary(2048) NULL,
875 CQ_HADOOP_JOB_ID nvarchar(128) NULL,
876PRIMARY KEY CLUSTERED
877(
878 CQ_ID ASC
879)
880);
881
882CREATE TABLE COMPLETED_COMPACTIONS (
883 CC_ID bigint NOT NULL,
884 CC_DATABASE nvarchar(128) NOT NULL,
885 CC_TABLE nvarchar(128) NOT NULL,
886 CC_PARTITION nvarchar(767) NULL,
887 CC_STATE char(1) NOT NULL,
888 CC_TYPE char(1) NOT NULL,
889 CC_TBLPROPERTIES nvarchar(2048) NULL,
890 CC_WORKER_ID nvarchar(128) NULL,
891 CC_START bigint NULL,
892 CC_END bigint NULL,
893 CC_RUN_AS nvarchar(128) NULL,
894 CC_HIGHEST_TXN_ID bigint NULL,
895 CC_META_INFO varbinary(2048) NULL,
896 CC_HADOOP_JOB_ID nvarchar(128) NULL,
897PRIMARY KEY CLUSTERED
898(
899 CC_ID ASC
900)
901);
902
903CREATE TABLE COMPLETED_TXN_COMPONENTS(
904 CTC_TXNID bigint NULL,
905 CTC_DATABASE nvarchar(128) NOT NULL,
906 CTC_TABLE nvarchar(128) NULL,
907 CTC_PARTITION nvarchar(767) NULL
908);
909
910CREATE TABLE HIVE_LOCKS(
911 HL_LOCK_EXT_ID bigint NOT NULL,
912 HL_LOCK_INT_ID bigint NOT NULL,
913 HL_TXNID bigint NULL,
914 HL_DB nvarchar(128) NOT NULL,
915 HL_TABLE nvarchar(128) NULL,
916 HL_PARTITION nvarchar(767) NULL,
917 HL_LOCK_STATE char(1) NOT NULL,
918 HL_LOCK_TYPE char(1) NOT NULL,
919 HL_LAST_HEARTBEAT bigint NOT NULL,
920 HL_ACQUIRED_AT bigint NULL,
921 HL_USER nvarchar(128) NOT NULL,
922 HL_HOST nvarchar(128) NOT NULL,
923 HL_HEARTBEAT_COUNT int NULL,
924 HL_AGENT_INFO nvarchar(128) NULL,
925 HL_BLOCKEDBY_EXT_ID bigint NULL,
926 HL_BLOCKEDBY_INT_ID bigint NULL,
927PRIMARY KEY CLUSTERED
928(
929 HL_LOCK_EXT_ID ASC,
930 HL_LOCK_INT_ID ASC
931)
932);
933
934CREATE TABLE NEXT_COMPACTION_QUEUE_ID(
935 NCQ_NEXT bigint NOT NULL
936);
937
938INSERT INTO NEXT_COMPACTION_QUEUE_ID VALUES(1);
939
940CREATE TABLE NEXT_LOCK_ID(
941 NL_NEXT bigint NOT NULL
942);
943
944INSERT INTO NEXT_LOCK_ID VALUES(1);
945
946CREATE TABLE NEXT_TXN_ID(
947 NTXN_NEXT bigint NOT NULL
948);
949
950INSERT INTO NEXT_TXN_ID VALUES(1);
951
952CREATE TABLE TXNS(
953 TXN_ID bigint NOT NULL,
954 TXN_STATE char(1) NOT NULL,
955 TXN_STARTED bigint NOT NULL,
956 TXN_LAST_HEARTBEAT bigint NOT NULL,
957 TXN_USER nvarchar(128) NOT NULL,
958 TXN_HOST nvarchar(128) NOT NULL,
959 TXN_AGENT_INFO nvarchar(128) NULL,
960 TXN_META_INFO nvarchar(128) NULL,
961 TXN_HEARTBEAT_COUNT int NULL,
962PRIMARY KEY CLUSTERED
963(
964 TXN_ID ASC
965)
966);
967
968CREATE TABLE TXN_COMPONENTS(
969 TC_TXNID bigint NULL,
970 TC_DATABASE nvarchar(128) NOT NULL,
971 TC_TABLE nvarchar(128) NULL,
972 TC_PARTITION nvarchar(767) NULL,
973 TC_OPERATION_TYPE char(1) NOT NULL
974);
975
976ALTER TABLE TXN_COMPONENTS WITH CHECK ADD FOREIGN KEY(TC_TXNID) REFERENCES TXNS (TXN_ID);
977
978CREATE INDEX TC_TXNID_INDEX ON TXN_COMPONENTS (TC_TXNID);
979
980CREATE TABLE AUX_TABLE (
981 MT_KEY1 nvarchar(128) NOT NULL,
982 MT_KEY2 bigint NOT NULL,
983 MT_COMMENT nvarchar(255) NULL,
984 PRIMARY KEY CLUSTERED
985(
986 MT_KEY1 ASC,
987 MT_KEY2 ASC
988)
989);
990
991CREATE TABLE KEY_CONSTRAINTS
992(
993 CHILD_CD_ID BIGINT,
994 CHILD_INTEGER_IDX INT,
995 CHILD_TBL_ID BIGINT,
996 PARENT_CD_ID BIGINT NOT NULL,
997 PARENT_INTEGER_IDX INT NOT NULL,
998 PARENT_TBL_ID BIGINT NOT NULL,
999 POSITION INT NOT NULL,
1000 CONSTRAINT_NAME VARCHAR(400) NOT NULL,
1001 CONSTRAINT_TYPE SMALLINT NOT NULL,
1002 UPDATE_RULE SMALLINT,
1003 DELETE_RULE SMALLINT,
1004 ENABLE_VALIDATE_RELY SMALLINT NOT NULL
1005) ;
1006
1007ALTER TABLE KEY_CONSTRAINTS ADD CONSTRAINT CONSTRAINTS_PK PRIMARY KEY (CONSTRAINT_NAME, POSITION);
1008
1009CREATE INDEX CONSTRAINTS_PARENT_TBL_ID__INDEX ON KEY_CONSTRAINTS(PARENT_TBL_ID);
1010
1011CREATE TABLE WRITE_SET (
1012 WS_DATABASE nvarchar(128) NOT NULL,
1013 WS_TABLE nvarchar(128) NOT NULL,
1014 WS_PARTITION nvarchar(767),
1015 WS_TXNID bigint NOT NULL,
1016 WS_COMMIT_ID bigint NOT NULL,
1017 WS_OPERATION_TYPE char(1) NOT NULL
1018);
1019
1020
1021-- -----------------------------------------------------------------
1022-- Record schema version. Should be the last step in the init script
1023-- -----------------------------------------------------------------
1024INSERT INTO VERSION (VER_ID, SCHEMA_VERSION, VERSION_COMMENT) VALUES (1, '2.3.0', 'Hive release version 2.3.0');
1025"""
Cell 2. Get Connection Parameters.
These are parameters of sql server you have created in Azure.
1val server = "server-name"
2val jdbcDatabase = "meta"
3val username = "admin username"
4val password = "admin password"
Cell 3. Execute the DDL
1import java.util.Properties
2import java.sql.DriverManager
3
4val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
5val jdbcHostname = "server-name.database.windows.net"
6val jdbcPort = 1433
7val jdbcUrl = "jdbc:sqlserver://" + jdbcHostname + ":" + jdbcPort + ";database=" + jdbcDatabase + ";user=" + username + ";password=" + password
8val connectionProperties = new Properties()
9connectionProperties.put("user", username)
10connectionProperties.put("password", password)
11connectionProperties.setProperty("Driver", driverClass)
12
13val connection = DriverManager.getConnection(jdbcUrl, username, password)
14val stmt = connection.createStatement()
15
16stmt.execute(script)
17connection.close()
At this point, you database should be populated with necessary tables.
Enforce Metastore on All Clusters
In order to transparently force all the clusters to use the metastore, you can create a global init script (from admin console). This will run before cluster starts.
Here is the content of the script:
1# Loads environment variables to determine the correct JDBC driver to use.
2source /etc/environment
3# Quoting the label (i.e. EOF) with single quotes to disable variable interpolation.
4cat << 'EOF' > /databricks/driver/conf/00-custom-spark.conf
5[driver] {
6 # Hive specific configuration options.
7 # spark.hadoop prefix is added to make sure these Hive specific options will propagate to the metastore client.
8 # JDBC connect string for a JDBC metastore
9 "spark.hadoop.javax.jdo.option.ConnectionURL" = "jdbc:sqlserver://server-name.database.windows.net:1433;database=meta;encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
10
11 # Username to use against metastore database
12 "spark.hadoop.javax.jdo.option.ConnectionUserName" = "mssql-username"
13
14 # Password to use against metastore database
15 "spark.hadoop.javax.jdo.option.ConnectionPassword" = "mssql-password"
16
17 # Driver class name for a JDBC metastore
18 "spark.hadoop.javax.jdo.option.ConnectionDriverName" = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
19
20 # Spark specific configuration options
21 "spark.sql.hive.metastore.version" = "2.3.7"
22 "spark.sql.hive.metastore.jars" = "builtin"
23}
24EOF
and replace server, database and credentials to yours. Note that this is using built-in hive metastore jars, so your cluster won’t need to download anything unlike the official documentation says.
Enjoy
Restart your clusters and enjoy the custom metastore.
Have feedback or questions? Feel free to email me.