Buenas tardes Somos un equipo de analistas de sistemas en una de las divisiones de gestión de datos de Rostelecom. Nuestra empresa cuenta con más de 300 fuentes de datos heterogéneas; esta variedad es necesaria para respaldar el trabajo de Rostelecom en todas las numerosas áreas. Estudiamos las fuentes de datos y, si es necesario, lo cargamos parcialmente en el bucle de almacenamiento.
Curiosity rover en la superficie. También tiene muchas fuentes de datos heterogéneas. Imagen tomada de therahnuma.com.En este proceso, se distinguen dos subtareas: definir una estrategia para recopilar datos de las tablas de origen en función de sus propiedades y preparar tablas que son "receptores" del almacén de datos. Para hacer esto, utilizamos varias GUI y herramientas de ingeniería inversa. Además, al recopilar información, un analista de sistemas comienza a adquirir un conjunto de consultas auxiliares a las tablas de información DBMS (principalmente Oracle). En este artículo compartiré el "conjunto de caballeros" de los guiones utilizados por nuestro equipo.
Para comenzar, una pequeña explicación de todos los scripts enumerados:
- Muchos scripts usan xmlagg para agregar cadenas, ya que listagg no puede procesar cadenas demasiado largas como resultado de la concatenación.
- En todas las secuencias de comandos, excepto en "Procedimientos, funciones y paquetes", las tablas de destino se establecen a través de la tabla de filtro en el bloque "con". Se llenan el nombre del esquema y el nombre de la tabla.
- Cada secuencia de comandos va acompañada de uno o más casos de uso, una descripción de la especificación (conjunto de resultados) y una lista de tablas del sistema utilizadas (para evaluar la posibilidad de uso en una base de datos particular).
Script "Información sobre tablas"
Especificación :
Tablas de sistema utilizadas: all_tab_columns, all_tab_comments, all_tab_statistics, all_part_key_columns, all_subpart_key_columns.
La consulta es útil para determinar una estrategia para cargar datos desde un sistema fuente. Si la clave primaria se basa en la tabla en consideración, entonces es posible organizar la descarga con la asignación posterior del "incremento" en ella. Si hay una marca de tiempo, por ejemplo, en campos técnicos con información sobre la inserción de datos o la actualización, puede organizar la carga de solo registros modificados / agregados por un período de tiempo. La información sobre la estructura de particiones puede ser útil al crear una tabla similar, el "receptor".
Cuerpo de solicitud: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 "Particiones y subparticiones"
Especificación:
Tablas de sistema utilizadas: all_tab_partitions, all_tab_subpartitions, all_part_key_columns, all_subpart_key_columns.
El script será útil para obtener características (nombre, tamaño) de las particiones cuando se usan directamente como fuentes de datos.
Cuerpo de solicitud: 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 "Composición de atributos de tablas"
Especificación:
Tablas de sistema utilizadas: all_tables, all_constraints, all_cons_columns, all_tab_columns, all_col_comments, v $ nls_parameters.
Este script será útil para preparar las tablas del "receptor" en el almacén de datos cuando se necesite información detallada sobre la tabla, sus relaciones con otras tablas, así como la composición completa de los atributos. La tabla filter2 se usa para filtrar tablas para las que se buscan enlaces (desde y hacia). Por defecto, las tablas se toman de todos los esquemas, excepto los del sistema.
Cuerpo de solicitud: 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 "Procedimientos, funciones y paquetes"
Especificación:Tablas de sistema usadas: all_source
Al analizar la fuente, puede surgir una tarea para estudiar los flujos de datos que circulan en el sistema. Es casi imposible prescindir de una base de código de paquetes, funciones y procedimientos, especialmente con documentación incompleta o faltante. Por conveniencia, los objetos enumerados a través del script se pueden representar en forma de tabla. El resultado de la solicitud que utiliza la utilidad de la consola puede enviarse a un flujo y redirigirse mediante un controlador simple (script de bash) a los archivos para su posterior estudio por parte de su editor favorito. Además, se pueden "colgar" varios controladores en la secuencia de salida: embellecer, desenvolver, etc.
Cuerpo de solicitud: 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 ;
Conclusión
Los scripts descritos anteriormente ayudan a nuestros analistas de sistemas a deshacerse de muchas de las tareas rutinarias de recopilar información sobre la base de datos y centrarse en cosas más creativas, como la estrategia de carga y la estructura de las tablas del receptor. Espero que los guiones te sean útiles también. Sería interesante saber cómo automatizar estas y otras tareas similares.