数据字典介绍与回顾

如同《从关系型数据库到大数据，谈谈数据字典的故事》所介绍的，在操作数据仓库的过程中，数据的管理和维护必不可少。在进行此类管理时，有很多场景要求使用者统计集群中各种元数据的存在状态和数量信息，比如统计某种类型表的数量、查看某用户对指定元数据的权限、估算某些数据的空间占用量等。推出数据字典之前，Inceptor缺少一种方便简单的途径在集群范围内实现特定元信息的锁定和访问，不能做到信息的广泛收集。星环推出数据字典的目的就在于方便使用者对各种元信息进行单维、多维或复合查询分析。

数据字典由各种不同的字典表组成，而字典表实则是视图，可以被SQL和PL/SQL访问查询。不同字典表存放着不同种类的元数据信息，例如字典表databases_v提供所有数据库的元信息、tables_v提供所有表的信息等。这些字典表都提供于名为system的数据库中。数据字典的发布使用户通过SQL或PL/SQL就可以满足对各种信息访问需求，在维持操作简易度的基础上，既提高了范围的广度也增强了目标信息的针对性。

《从关系型数据库到大数据，谈谈数据字典的故事》仅对数据字典做了简单的理论性介绍，但是究竟如何合理使用与利用数据字典进行信息查询呢，我们在本文中将通过用户Alice的实践示例来具体说明。

  任务实战

Alice的老板要求她在周五下班之前对整个公司范围中的现存元数据情况进行一些分析，集群系统中存放了各类元数据如表、数据库、视图、字段、存储过程等，每个种类的元数据个数达成百上千。Alice必须找到有效的办法完成以下五项任务：

查看所有关于A项目的表，列举出其名字、所在数据库、类型、是否是事务表，这些表名都以“A_”起始。

统计数据库trans_platform中普通表、临时表、永久UDF的个数。

衡量名称为A_customer、A_product、A_order的表中占据空间最大的字段、实际平均占用量，以及表从属的数据库。

查找创建过表A_customers或者B_customers的员工还创建过哪些数据库和视图。

查看任意员工对于哪些表有哪些权限。

实战任务一 查看所有关于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

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_customer*_customer的owner这条线索把数据库字典表、视图字典表分别和表字典表相关联。

与此任务有关的各字典表中的字段有：

任务四的实现依靠子查询和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，对元数据信息进行各方面的提取处理整合。而且由于用户不允许修改字典表的内容，数据的完整性和安全性以及查询结果的可靠性将因此得到保证。《数据字典使用手册》更加详细的介绍了数据字典的原理、安装方法、字典表内容，读者可以根据需要至星环科技论坛进行查阅。

 

对此篇文章如有任何问题，欢迎以邮件形式联系我们：bigdataopenlab@transwarp.io