Hadoop集群上如何使用数据字典做元数据管理（应用实战）
使用数据仓库的过程中，对数据的合理管理和维护必不可少。在进行管理时，有很多场景要求使用者统计数据库各种元数据的存在状态和数量信息，比如统计某种类型表的数量、查看某用户对指定元数据的权限、估算某些数据的空间占用量等。推出数据字典之前，Inceptor缺少一种方便简单的途径在数据库范围内实现特定元信息的锁定和访问，用户能够想到的方法想必也是依靠执行SHOW、DESCRIBE语句实现，但是这两种SQL语句的查看范围毕竟仅限于当前被查询的对象，不能做到信息的广泛收集，而且想获得某种特定元信息时，也只能在大量的查询结果中依靠肉眼查看。
TDH 4.6推出数据字典，其目的就在于方便使用者对各种元信息进行单维、多维或复合查询分析。数据字典由各种不同的字典表组成，字典表由字段和数据构成，可以完全像普通的表一样被操作。不同字典表存放着不同种类的元信息，例如字典表databases_v提供所有数据库的元信息、tables_v提供所有表的元信息等，这些字典表都提供于名为system的数据库中。数据字典的发布使用户通过SQL或PL/SQL就可以满足对各种信息访问需求，在维持操作简易度的基础上，既提高了范围的广度也增强了目标信息的针对性。
之前的文章仅对数据字典做了简单的理论性介绍，但是究竟如何合理使用与利用数据字典进行信息查询呢，我们在本文中将通过用户Alice的实践示例来具体说明。
Alice的老板要求她在周五下班之前对整个公司范围中的现存元数据情况进行一些分析，数据库系统存放了各类元数据如表、数据库、视图、字段、存储过程等，每个种类的元数据个数达成百上千。Alice必须找到有效的办法完成以下五项任务：
1. 查看所有关于A项目的表，列举出其名字、所在数据库、类型、是否是事务表，这些表名都以“A_”起始。
2. 统计数据库trans_platform中普通表、临时表、永久UDF的个数。
3. 衡量名称为A_customer、A_product、A_order的表中占据空间最大的字段、实际平均占用量，以及表从属的数据库。
4. 查找创建过表A_customers或者B_customers的员工还创建过哪些数据库和视图。
5. 查看任意员工对于哪些表有哪些权限。
Ø 实战任务一
查看所有关于A项目的表，列举出其名字、所在数据库、类型、是否是事务表，这些表名都以“A_”起始。 |
Alice最初打算用SQL帮助语句“SHOW TABLES LIKE”进行查找，但是尝试后发现这种方法的查找范围仅限在一个数据库中，不能全范围的查看，由于数据库数量很多局限性相当大。Alice很苦恼，于是向同事Tom寻求帮助，他向Alice介绍了数据字典，并保证数据字典可以完成她本次工作所有任务。在阅读了《数据字典使用手册》后，Alice发现了字典表tables_v，该字典表全面提供了各个数据库中的各个表的信息，而且恰巧通过table_name、database_name、table_type和transactional这四个字段提供了表名、从属数据库名、表类型、是否是事务表的信息，利用SQL对它进行访问就能跨越不同数据库查找A项目的相关表。
与此任务有关的字典表和其中的字段有：
Alice对table信息的查询进行了小试，通过如下语句完成了任务一：
USE system;
SELECT table_name, database_name, table_type, transactional FROM tables_v WHERE table_name LIKE “A_*” ORDER BY table_name;
|
Ø 实战任务二
统计数据库trans_platform中普通表、临时表、永久UDF的个数。 |
Alice发现在数据字典中，普通表、临时表、永久UDF的信息被分别存放于字典表tables_v、temporary_tables_v、permanent_udfs_v里，这些字典表分别用名称形如“*_name”的字段记录各元数据唯一的标识名称。因此，她决定在这四张字典表中分别找到从属trans_platform的相关对象，并通过COUNT不同类型对象的name数目从而统计各类型元数据数量。
与此任务有关的字典表和其中的字段有：
实现任务二采用的语句如下所示：
USE system;
SELECT ‘tables_cnt’ AS name, COUNT(table_name) AS value FROM tables_v WHERE database_name = ‘trans_platform’ UNION SELECT ‘temp_table_cnt’ AS name, COUNT(table_name) AS value FROM temporary_tables_v WHERE database_name = ‘trans_platform’ UNION SELECT ‘perm_udfs_cnt’ AS name, COUNT(udf_name) AS value FROM permanent_udfs_v WHERE database_name = ‘trans_platform’;
|
Ø 实战任务三
衡量名称为A_customer、A_product、A_order的表中占据空间最大的字段、实际平均占用量，以及该表从属的数据库。 |
Alice发现目前在数据字典中，数据库中所有字段的长度信息由table_column_statistics字典表中的avg_col_len（字段平均长度）和max_col_len（字段最大长度）两个字段提供，因为是估算空间占用比例，所以采用avg_col_len更合理。另外table_column_statistics还通过column_name、db_name、table_name分别提供了列名、数据库名、表名信息。
与此任务有关的字典表和其中的字段有：
因此构造了如下查询语句来实现任务三：
USE system;
SELECT a.column_name, a.db_name, a.table_name, a.avg_col_len FROM table_column_statistics_v a WHERE table_name IN ('A_customer’, 'A_product’, ‘A_order’) AND avg_col_len = ( SELECT MAX(avg_col_len) FROM table_column_statistics_v WHERE table_name = a.table_name AND db_name = a.db_name );
|
Ø 实战任务四
查找创建过表A_customers或者B_customers的员工还创建过哪些数据库和视图。 |
查看数据库对象的创建者其实就是查找对应owner。Alice分析实现任务四时不仅要查找存储数据库、表、视图信息的字典表databases_v、tables_v、views_v，而且要通过表A_customers、B_customer的owner这条线索把数据库字典表、视图字典表分别和表字典表相关联。
与此任务有关的各字典表中的字段有：
因此Alice认为任务四的实现要依靠子查询和UNION实现，对应的语句应该为：
USE system;
SELECT ‘database’ AS typename, database_id AS id, database_name AS name FROM databases_v WHERE owner_name IN ( SELECT owner_name FROM tables_v WHERE table_name IN (‘A_customer’, ‘A_order’) ) UNION ALL SELECT ‘view’ AS typename, view_id AS id, view_name AS name FROM views_v WHERE owner_name IN ( SELECT owner_name FROM tables_v WHERE table_name IN (‘A_customer’, ‘A_order’) );
|
Ø 实战任务五
查看任意员工对于指定表有哪些权限。 |
表的各种权限信息被存放在table_privilege_v中。Alice决定依靠这张字典表，找到各个员工对于指定table的权限。目前在权限字典表中，每行记录对应一条权限，table_privileges_v中部分字段的部分内容如下：
Alice计划用PL/SQL实现该权限查询任务。创建一个存储过程，输入参数为目标用户、目标表、目标数据库，输出参数是相应权限。例如：查询员工Bob对system中udfs_v表的权限时，输入Bob, udfs_v, system，输出结果如下所示。以此来增强结果的阅读效果。
User: Bob Table Name: udfs_v Database: system Privilege(s): CREATE|DROP |
Alice对任务五的实现语句如下：
USE system; CREATE OR REPLACE PROCEDURE privilege_search( user_name IN STRING, object_name IN STRING, db_name IN STRING, privilege OUT STRING ) IS BEGIN IF user_name != '' AND object_name != '' AND db_name != '' THEN privilege := '' dbms_output.put_line('User: ' || user_name || ' Table Name: ' || object_name || 'Database: ' || db_name) FOR t IN ( SELECT table_privilege FROM table_privileges_v WHERE table_name = object_name AND principal_name = user_name AND database_name = db_name ) LOOP privilege := privilege || ' ' || t.table_privilege END LOOP IF privilege = '' THEN privilege := 'None' END IF ELSE dbms_output.put_line('parameter(s) incorrect!') END IF END;
DECLARE privilege STRING BEGIN privilege_search( 'Bob', 'udfs_v', 'system', privilege ) dbms_output.put_line( 'Privileges: ' || privilege ) END;
|
到此为止，Alice利用数据字典完成了老板下达的所有任务，对所得结果进行了分析完成了任务报告，并汇报给上级。
总结
上述示例表示，数据字典对于元信息查询方面的应用十分有效，用户可以通过编写不同的SQL或PL/SQL，对元数据信息进行各方面的提取处理整合。而且由于用户不允许修改字典表的内容，数据的完整性和安全性以及查询结果的可靠性将因此得到保证。《数据字典使用手册》更加详细的介绍了数据字典的原理、安装方法、字典表内容，读者可以根据需要至星环科技论坛进行查阅。
欢迎您把在使用过程中遇到的问题反映至邮箱
或者在我们的论坛中反馈。