Boa tarde Somos uma equipe de analistas de sistemas em uma das divisões de gerenciamento de dados da Rostelecom. Nossa empresa possui mais de 300 fontes de dados heterogêneas - é necessária uma variedade suficiente para apoiar o trabalho da Rostelecom em todas as áreas. Estudamos fontes de dados e, se necessário, carregamos parcialmente no loop de armazenamento.
Rover de curiosidade na superfície. Ele também possui muitas fontes de dados heterogêneas. Imagem tirada em therahnuma.com.Nesse processo, duas subtarefas são diferenciadas: definindo uma estratégia para coletar dados das tabelas de origem, dependendo de suas propriedades e preparando tabelas que são "receptoras" do armazém de dados. Para fazer isso, usamos várias GUIs e ferramentas de engenharia reversa. Além disso, ao coletar informações, um analista de sistemas começa a adquirir um conjunto de consultas auxiliares nas tabelas de informações do DBMS (principalmente Oracle). Neste artigo, compartilharei o "conjunto de cavalheiros" desses scripts usados por nossa equipe.
Para começar, uma pequena explicação de todos os scripts listados:
- Muitos scripts usam xmlagg para agregar strings, pois o listagg não pode lidar com strings muito longas resultantes da concatenação.
- Em todos os scripts, exceto em "Procedures, Functions and Packages", as tabelas de destino são especificadas na tabela de filtros no bloco "with". O nome do esquema e o nome da tabela são preenchidos.
- Cada script é acompanhado por um ou mais casos de uso, uma descrição da especificação (conjunto de resultados) e uma lista de tabelas de sistema usadas (para avaliar a possibilidade de uso em um banco de dados específico).
Script "Informações sobre tabelas"
Especificação :
Tabelas de sistema usadas: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.
A consulta é útil para determinar uma estratégia para carregar dados de um sistema de origem. Se a chave primária for criada na tabela em consideração, é possível organizar o descarregamento com a alocação subsequente do "incremento" nela. Se houver um registro de data e hora - por exemplo, em campos técnicos com informações sobre inserção de dados ou atualização - você poderá organizar o upload de apenas registros alterados / adicionados por um período de tempo. Informações sobre a estrutura das partições podem ser úteis ao criar uma tabela semelhante, o "receptor".
Organismo de solicitação:with filter (owner, table_name) as ( select 'SCHEMA_NAME_1', t.* from table( sys.odcivarchar2list( 'TABLE_NAME_1' , 'TABLE_NAME_2' ) ) t union all select owner , table_name from all_tables where owner = 'SCHEMA_NAME_2' ) select a.owner as schema_name , a.table_name , e.comments , b.height , c.width , d.datetime_columns , b.avg_row_len , p.part_key , s.subpart_key from filter a left join ( select owner , table_name , num_rows as height , avg_row_len from all_tab_statistics where object_type = 'TABLE' ) b on a.table_name = b.table_name and a.owner = b.owner left join ( select owner , table_name , count(1) as width from all_tab_columns group by owner , table_name ) c on a.table_name = c.table_name and a.owner = c.owner left join ( select owner , table_name , listagg( column_name || ' (' || data_type || ')' , ', ' ) within group (order by column_id) as datetime_columns from all_tab_columns where data_type = 'DATE' or data_type like 'TIMESTAMP%' or data_type like 'INTERVAL%' or lower(column_name) like '%period%' or lower(column_name) like '%date%' or lower(column_name) like '%time%' group by owner , table_name ) d on a.table_name = d.table_name and a.owner = d.owner left join ( select owner , table_name , comments from all_tab_comments where table_type = 'TABLE' ) e on a.table_name = e.table_name and a.owner = e.owner left join ( select owner , name as table_name , listagg( column_name , ', ' ) within group (order by column_position) as part_key from all_part_key_columns where object_type = 'TABLE' group by owner , name ) p on a.owner = p.owner and a.table_name = p.table_name left join ( select owner , name as table_name , listagg( column_name , ', ' ) within group (order by column_position) as subpart_key from all_subpart_key_columns where object_type = 'TABLE' group by owner , name ) s on a.owner = s.owner and a.table_name = s.table_name order by e.owner , e.table_name ;
Script "Partições e subpartições"
Especificação:
Tabelas de sistema usadas: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns.
O script será útil para obter características (nome, tamanho) das partições quando elas forem usadas diretamente como fontes de dados.
Organismo de solicitação: with filter (owner, table_name) as ( select 'SCHEMA_NAME_1', t.* from table( sys.odcivarchar2list( 'TABLE_NAME_1' , 'TABLE_NAME_2' ) ) t union all select owner , table_name from all_tables where owner = 'SCHEMA_NAME_2' ) select f.owner as schema_name , f.table_name , p.part_key , pc.partition_name , pc.partition_position , pc.num_rows as partition_height , s.subpart_key , sc.subpartition_name , sc.subpartition_position , sc.num_rows as subpartition_height from filter f join ( select owner , name as table_name , listagg( column_name , ', ' ) within group (order by column_position) as part_key from all_part_key_columns where object_type = 'TABLE' group by owner , name ) p on f.owner = p.owner and f.table_name = p.table_name left join all_tab_partitions pc on p.table_name = pc.table_name and p.owner = pc.table_owner left join ( select owner , name as table_name , listagg( column_name , ', ' ) within group (order by column_position) as subpart_key from all_subpart_key_columns where object_type = 'TABLE' group by owner , name ) s on p.owner = s.owner and p.table_name = s.table_name left join all_tab_subpartitions sc on f.owner = sc.table_owner and f.table_name = sc.table_name and pc.partition_name = sc.partition_name order by f.owner , f.table_name ;
Script "Composição de atributo das tabelas"
Especificação:
Tabelas de sistema usadas: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v $ nls_parameters.
Esse script será útil para preparar as tabelas “receptoras” no data warehouse, quando forem necessárias informações detalhadas sobre a tabela, seus relacionamentos com outras tabelas, bem como a composição completa dos atributos. A tabela filter2 é usada para filtrar tabelas pelas quais os links são pesquisados (de e para). Por padrão, as tabelas são obtidas de todos os esquemas, exceto os do sistema.
Organismo de solicitação: with filter (owner, table_name) as ( select 'SCHEMA_NAME_1', t.* from table( sys.odcivarchar2list( 'TABLE_NAME_1' , 'TABLE_NAME_2' ) ) t union all select owner , table_name from all_tables where owner = 'SCHEMA_NAME_2' ) , filter2 (owner, table_name) as ( select owner, table_name from all_tables where owner not in ('MDSYS', 'CTXSYS', 'SYSTEM', 'XDB', 'SYS') ) , refs as ( select b.constraint_type as from_constraint_type , b.constraint_name as from_constraint_name , d.position as from_position , d.column_name as from_column_name , b.table_name as from_table_name , b.owner as from_owner , a.owner as to_owner , a.table_name as to_table_name , c.column_name as to_column_name , c.position as to_position , a.constraint_name as to_constraint_name , a.constraint_type as to_constraint_type from all_constraints a left join all_constraints b on a.r_constraint_name = b.constraint_name and a.r_owner = b.owner left join all_cons_columns c on a.constraint_name = c.constraint_name and a.table_name = c.table_name and a.owner = c.owner left join all_cons_columns d on b.constraint_name = d.constraint_name and b.table_name = d.table_name and b.owner = d.owner where a.constraint_type = 'R' and b.constraint_type in ('P', 'U') and c.position = d.position ) , depends as ( select rtrim( xmlagg( xmlelement( e , to_owner || '.' || to_table_name || '.' || to_column_name , ', ' ).extract('//text()') order by to_owner ).getclobval() , ', ' ) as val , from_owner as owner , from_table_name as table_name , from_column_name as column_name from refs where (to_owner, to_table_name) in (select * from filter2) group by from_table_name , from_column_name , from_owner ) , impacts as ( select rtrim( xmlagg( xmlelement( e , from_owner || '.' || from_table_name || '.' || from_column_name , ', ' ).extract('//text()') order by from_owner ).getclobval() , ', ' ) as val , to_owner as owner , to_table_name as table_name , to_column_name as column_name from refs where (from_owner, from_table_name) in (select * from filter2) group by to_table_name , to_column_name , to_owner ) select f.owner as schema_name , f.table_name , a.column_id , a.column_name , a.data_type , b.comments as column_comment , decode ( a.data_type , 'NUMBER', nvl(a.data_scale, 0) , '' ) as scale , decode ( a.data_type , 'NUMBER', nvl(a.data_precision, 38) , '' ) as precision , a.data_length as byte_length , case when a.data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') then d.value end as encoding , case when a.data_type in ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2') then a.char_length
Script "Procedimentos, funções e pacotes"
Especificação:Tabelas de sistema usadas: all_source
Ao analisar a fonte, pode surgir uma tarefa para estudar os fluxos de dados que circulam no sistema. É quase impossível ficar sem uma base de código de pacotes, funções e procedimentos, especialmente com documentação incompleta ou ausente. Por conveniência, os objetos listados no script podem ser representados na forma de uma tabela. O resultado da solicitação usando o utilitário do console pode ser enviado para um fluxo e redirecionado por um manipulador simples (script bash) para arquivos para um estudo mais aprofundado pelo seu editor favorito. Além disso, vários manipuladores podem ser "pendurados" no fluxo de saída - embelezamento, desembrulhamento etc.
Organismo de solicitação: select t.owner as schema_name , t.name as name , xmlagg( xmlelement( e , t.text , '' ).extract('//text()') order by t.line asc ).getclobval() as body , f.wrapped , t.type as type from ( select owner, name, type , case when lower(text) like '%wrapped%' then 1 else 0 end as wrapped from all_source where type in ( 'PACKAGE BODY' , 'PACKAGE' , 'FUNCTION' , 'PROCEDURE' ) and line = 1 and owner not in ('MDSYS', 'CTXSYS', 'SYSTEM', 'XDB', 'SYS') ) f join all_source t on f.owner = t.owner and f.name = t.name and f.type = t.type group by t.owner , t.name , t.type , f.wrapped order by t.owner , t.name , t.type ;
Conclusão
Os scripts descritos acima ajudam nossos analistas de sistema a se livrarem de muitas das tarefas rotineiras de coleta de informações sobre o banco de dados e a se concentrarem em coisas mais criativas, como a estratégia de carregamento e a estrutura das tabelas "receptoras". Espero que os scripts também sejam úteis para você. Seria interessante saber como você automatiza essas e outras tarefas semelhantes.