Bon après-midi Nous sommes une équipe d'analystes système dans l'une des divisions de gestion des données de Rostelecom. Notre entreprise compte plus de 300 sources de données hétérogènes - une telle variété est nécessaire pour soutenir le travail de Rostelecom dans tous les nombreux domaines. Nous étudions les sources de données et, si nécessaire, téléchargeons partiellement vers la boucle de stockage.
Rover de curiosité en surface. Il possède également de nombreuses sources de données hétérogènes. Image prise à partir de therahnuma.com.Dans ce processus, deux sous-tâches sont distinguées: définir une stratégie de collecte de données à partir des tables source en fonction de leurs propriétés et préparer des tables qui sont des «récepteurs» de l'entrepôt de données. Pour ce faire, nous utilisons diverses interfaces graphiques et outils d'ingénierie inverse. De plus, lors de la collecte d'informations, un analyste système commence à acquérir un pool de requêtes auxiliaires vers les tables d'informations du SGBD (principalement Oracle). Dans cet article, je partagerai le «jeu de gentleman» de ces scripts utilisés par notre équipe.
Pour commencer, une petite explication de tous les scripts listés:
- De nombreux scripts utilisent xmlagg pour agréger des chaînes, car listagg ne peut pas gérer des chaînes trop longues résultant de la concaténation.
- Dans tous les scripts, à l'exception de «Procédures, fonctions et packages», les tables cibles sont définies via la table de filtrage dans le bloc «avec». Le nom du schéma et le nom de la table sont renseignés.
- Chaque script est accompagné d'un ou de plusieurs cas d'utilisation, d'une description de la spécification (jeu de résultats) et d'une liste des tables système utilisées (pour évaluer la possibilité d'utilisation sur une base de données particulière).
Script "Informations sur les tables"
Spécification :
Tables système utilisées: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.
La requête est utile pour déterminer une stratégie de téléchargement de données à partir d'un système source. Si la clé primaire est construite sur la table considérée, il est alors possible d'organiser le déchargement avec l'allocation ultérieure de "l'incrément" sur celle-ci. S'il y a un horodatage - par exemple, dans les domaines techniques contenant des informations sur l'insertion de données ou la mise à jour - vous pouvez organiser le téléchargement des enregistrements modifiés / ajoutés uniquement pendant une période de temps. Les informations sur la structure des partitions peuvent être utiles lors de la création d'une table similaire, le "récepteur".
Organe de demande: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 "Partitions et sous-partitions"
Spécification:
Tables système utilisées: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns.
Le script sera utile pour obtenir les caractéristiques (nom, taille) des partitions lorsqu'elles sont directement utilisées comme sources de données.
Organe de demande: 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 "Composition des attributs des tables"
Spécification:
Tables système utilisées: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v $ nls_parameters.
Ce script sera utile pour préparer les tables «réceptrices» dans l'entrepôt de données lorsque des informations détaillées sur la table, ses relations avec d'autres tables, ainsi que la composition complète des attributs sont nécessaires. La table filter2 est utilisée pour filtrer les tables pour lesquelles des liens sont recherchés (de et vers). Par défaut, les tableaux sont extraits de tous les schémas, à l'exception de ceux du système.
Organe de demande: 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 "Procédures, fonctions et packages"
Spécification:Tables système utilisées: all_source
Lors de l'analyse de la source, une tâche peut se poser pour étudier les flux de données circulant dans le système. Il est presque impossible de se passer d'une base de code de packages, fonctions et procédures, en particulier avec une documentation incomplète ou manquante. Pour plus de commodité, les objets répertoriés via un script peuvent être représentés dans un tableau. Le résultat de la requête utilisant l'utilitaire de console peut être sorti dans un flux et redirigé par un simple gestionnaire (script bash) vers des fichiers pour une étude plus approfondie par votre éditeur préféré. De plus, divers gestionnaires peuvent être «suspendus» sur le flux de sortie - embellissement, déballage, etc.
Organe de demande: 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 ;
Conclusion
Les scripts décrits ci-dessus aident nos analystes système à se débarrasser de la plupart des tâches de routine de collecte d'informations sur la base de données et à se concentrer sur des choses plus créatives, telles que la stratégie de chargement et la structure des tables "de réception". J'espère que les scripts vous seront utiles également. Il serait intéressant de savoir comment automatiser ces tâches et des tâches similaires.