Auditer les données

Voici quelques éléments d’aide pour auditer efficacement les données.

PostgreSQL

Quelques informations sur le cluster de BDD

Vue : liste des BDD et informations.

CREATE OR REPLACE VIEW public.info_cluster_bdd AS
	SELECT
		pgdb.datname as "Nom",
		pga.rolname as "Propriétaire",
		pg_database_size(pgdb.datname) as "Taille en octets", 
		pg_size_pretty(pg_database_size(pgdb.datname)) as "Taille en Byte (Octets)",
		pgdb.encoding as "Encodage",
		pgdb.datcollate as "Collation",
		pgdb.datctype as "Classification des caractères",
		CASE
			WHEN pgdb.datallowconn IS TRUE
				THEN 
					CASE pgdb.datconnlimit
						WHEN -1 THEN 'Nombre illimité de connexion'
						WHEN 1 THEN '1 connexion max'
						ELSE pgdb.datconnlimit || ' connexions simultanées max'
					END
			ELSE 'Connexion non autorisée'
		END as "Connectivité",
		CASE
			WHEN pgdb.datistemplate IS TRUE
				THEN 'Base template'
			ELSE 'Base standard'
		END as "Modèle",
		pgdb.datacl as "Droits"
	FROM
		pg_database pgdb
	LEFT JOIN
		pg_authid as pga
	ON
		pgdb.datdba = pga.oid
	ORDER BY
		pgdb.datname;

Vue : Activité en temps réelle.

CREATE OR REPLACE VIEW public.info_cluster_activite AS
SELECT
	pid as "Processus",
    datname as "BDD",
    usename as "Utilisateur",
    client_addr || ':' || client_port as "Client",
    CASE state
    	WHEN 'active' THEN 'En cours'
        WHEN 'idle' THEN 'En attente du client'
        WHEN 'idle in transaction' THEN 'En transaction sans exécution de requête'
        WHEN 'idle in transaction (aborted)' THEN 'En transaction sans exécution de requête mais l''une des instruction a généré une erreur'
        WHEN 'fastpath function call' THEN 'Le processus exécute une fonction fast-path'
        WHEN 'disabled' THEN 'Traçage d''activité désactivé'
    END as "Etat",
    CASE wait_event_type
		WHEN 'LWLockNamed' THEN 'En attente du verrou léger ' || wait_event
		WHEN 'LWLockTranche' THEN 'En attente d''un verrou léger : ' || wait_event
		WHEN 'Lock' THEN 'En attente du verrou lourd de type ' || wait_event
		WHEN 'BufferPin' THEN 'En attente d''accès à un tampon'
		ELSE 'Aucun verrou'
    END as "Verrou",
    date_trunc('second', justify_hours(age(now(), pg_stat_activity.backend_start))) AS "Démarrage processus",
    date_trunc('second', justify_hours(age(now(), pg_stat_activity.xact_start))) AS "Démarrage transaction",
    date_trunc('second', justify_hours(age(now(), pg_stat_activity.query_start))) AS "Démarrage dernière requête",
    date_trunc('second', justify_hours(age(now(), pg_stat_activity.state_change))) AS "Modification de l''état",
    query as "Requête"
FROM
	pg_stat_activity
ORDER BY
	"Modification de l''état",
	"Démarrage dernière requête",
	"Démarrage processus";

Vue : rôles.

CREATE OR REPLACE VIEW public.info_cluster_role AS
	SELECT
	    pga.rolname as "Rôle",
	    concat_ws(', ',pga2.rolname) as "Membre du rôle",
	    CASE
	        WHEN pga.rolsuper IS TRUE
	            THEN 'Super utilisateur'
	        ELSE 'Simple utilisateur'
	    END as "Surper administration",
	    CASE
	        WHEN pga.rolcreaterole IS TRUE
	            THEN 'Oui'
	        ELSE 'Non'
	    END as "Création d'autres rôles",
	    CASE
	        WHEN pga.rolcreatedb IS TRUE
	            THEN 'Oui'
	        ELSE 'Non'
	    END as "Création de BDD",
	    CASE
	        WHEN pga.rolcanlogin IS TRUE
	            THEN 'Oui'
	        ELSE 'Non'
	    END as "Possibilité de connexion",
	    CASE
	        WHEN pga.rolconnlimit = -1
	            THEN 'Illimité'
	        WHEN pga.rolconnlimit = 0
	            THEN 'Aucune'
	        WHEN pga.rolconnlimit = 1
	            THEN '1 seule connexion simmultanée'
	        ELSE pga.rolconnlimit || ' connexions simultanées'
	    END as "Nombre de connexions simultanées",
	    CASE
	        WHEN pga.rolvaliduntil::text = 'infinity'
	            THEN 'Infini'
	        ELSE pga.rolvaliduntil::text
	    END as "Validité",
	    pga.rolpassword as "Mot de passe"
	FROM
	    pg_authid as pga
	LEFT JOIN
	    pg_auth_members as pgam
	ON 
	    pga.oid = pgam.member
	LEFT JOIN
	    pg_authid as pga2
	ON 
	    pga2.oid = pgam.roleid
	ORDER BY
	    pga.rolname;

Quelques informations sur la BDD

SELECT : liste des extensions

SELECT 
	t1.extname,
	t1.extversion,
	t2.nspname
FROM 
	pg_extension AS t1
LEFT JOIN 
	pg_namespace AS t2
ON 
	t1.extnamespace = t2.oid;

SELECT : liste des clés étrangères

SELECT 
	tc.constraint_name,
	tc.constraint_type,
	tc.table_name,
	kcu.column_name,
	rc.update_rule AS on_update,
	rc.delete_rule AS on_delete,
	ccu.table_name AS references_table,
	ccu.column_name AS references_field

FROM 
	information_schema.table_constraints tc

LEFT JOIN information_schema.key_column_usage kcu
	ON tc.constraint_catalog = kcu.constraint_catalog
	AND tc.constraint_schema = kcu.constraint_schema
	AND tc.constraint_name = kcu.constraint_name

LEFT JOIN information_schema.referential_constraints rc
	ON tc.constraint_catalog = rc.constraint_catalog
	AND tc.constraint_schema = rc.constraint_schema
	AND tc.constraint_name = rc.constraint_name

LEFT JOIN information_schema.constraint_column_usage ccu
	ON rc.unique_constraint_catalog = ccu.constraint_catalog
	AND rc.unique_constraint_schema = ccu.constraint_schema
	AND rc.unique_constraint_name = ccu.constraint_name

--- any conditions for table etc. filtering
WHERE lower(tc.constraint_type) in ('foreign key', 'primary key')

Quelques statistiques sur la BDD

Attention, il faut faire un ANALYSE sur la BDD avant de lancer les scripts.

Vue : liste des schémas

CREATE OR REPLACE VIEW public.info_bdd_general AS
	SELECT
		it.table_schema as "Schéma",
		count(it.table_name) as "Nombre de tables", 
		sum(pgc.reltuples) as "Nombre d'enregistrement",
		pg_size_pretty(sum(pg_relation_size('"' || it.table_schema || '"."' || it.table_name || '"'))) as "Taille des données",
		pg_size_pretty(sum(pg_total_relation_size('"' || it.table_schema || '"."' || it.table_name || '"'))) as "Taille totale"

	FROM
		information_schema.tables as it
	LEFT JOIN 
		pg_namespace pgn 
	ON 
		pgn.nspname = it.table_schema::name
	LEFT JOIN 
		pg_class pgc 
	ON 
		(pgc.relname = it.table_name::name 
		AND pgc.relnamespace = pgn.oid)
	WHERE 
		table_type = 'BASE TABLE'
	GROUP BY
		it.table_schema
	ORDER BY
		it.table_schema;

Vue : liste des tables

D’abord on créé une fonction.

CREATE OR REPLACE FUNCTION public.type_colonne_geom(mon_schema text, ma_table text)
RETURNS text AS

$CORPS$

DECLARE
	nom_colonne text[];
	resultat text;

BEGIN
	nom_colonne = array(
		SELECT
			column_name::text 
		FROM
			information_schema.columns 
		WHERE
			table_schema=mon_schema
		AND
			table_name=ma_table
		AND
			column_name ~ '(^(geom)$|^(GEOM)$|^(the_geom)$|^(THE_GEOM)$)'
		)
	;

	mon_schema = quote_ident(mon_schema);
	ma_table = quote_ident(ma_table);

	IF array_length(nom_colonne,1) > 1
		THEN resultat = '/!\ Plusieurs colonnes géométriques /!\ ';
	ELSEIF array_length(nom_colonne,1) = 1
		THEN 
			EXECUTE '
				SELECT
					''Géometrie ('' || GeometryType(t1."'|| array_to_string(nom_colonne,'') ||'") || '', '' || ST_NDims (t1."'|| array_to_string(nom_colonne,'') ||'") ||''D, '' || ST_SRID (t1."'|| array_to_string(nom_colonne,'') ||'") || '')''
				FROM
					'|| mon_schema || '.' || ma_table ||' as t1;
				'
			INTO
				resultat
			;
	ELSE
		resultat = 'Non géométrique';
	END IF;

	IF resultat IS NULL
		THEN resultat = 'Pas de données dans la table';
	END IF;

	RETURN resultat;
END;

$CORPS$

LANGUAGE PLPGSQL
;

Ensuite on créé la vue qui va bien.

CREATE OR REPLACE VIEW public.info_bdd_table AS
SELECT
	it.table_schema as "Schéma",
	it.table_name as "Table",
	CASE 
		 WHEN pgc.reltuples = 0
		 	THEN 'Aucun enregistrement pour cette table'
		 WHEN pgc.reltuples = 1
		 	THEN pgc.reltuples || ' enregitrement'
		 ELSE pgc.reltuples || ' enregistrements'
	END as "Nombre d'enregistrement",
	pg_size_pretty(pg_relation_size('"' || it.table_schema || '"."' || it.table_name || '"')) as "Taille des données",
	pg_size_pretty(pg_total_relation_size('"' || it.table_schema || '"."' || it.table_name || '"')) as "Taille totale",
	public.type_colonne_geom(it.table_schema, it.table_name) AS "Informations géométriques",
	pgd.description as "Commentaire"

FROM
	information_schema.tables as it

LEFT JOIN
	pg_namespace AS pgn
ON
	it.table_schema = pgn.nspname
LEFT JOIN
	pg_class AS pgc
ON
	(it.table_name = pgc.relname AND pgn.oid = pgc.relnamespace)
LEFT JOIN
	pg_description AS pgd
ON
	(pgc.oid = pgd.objoid AND 0 = pgd.objsubid)
WHERE
	it.table_schema != 'pg_catalog'
AND
	it.table_schema != 'information_schema'
ORDER BY
	it.table_schema,
	it.table_name;

Vue : liste des colonnes

Attention, il faut créer la fonction décrite dans le paragraphe précédent.

CREATE OR REPLACE VIEW public.info_bdd_colonne AS
SELECT
	ic.table_catalog as "Base de données",
	ic.table_schema as "Schéma",
	ic.table_name as "Table",
	ic.column_name as "Colonne",
	CASE
		WHEN ic.data_type = 'USER-DEFINED'
		THEN public.type_colonne_geom(ic.table_schema, ic.table_name)
		ELSE ic.data_type ||
			CASE
				WHEN ic.character_maximum_length IS NOT NULL
				THEN ' (' || ic.character_maximum_length || ')'
				WHEN ic.numeric_precision IS NOT NULL
				THEN ' (' || ic.numeric_precision::text || ', ' || ic.numeric_precision_radix::text || ')'
				ELSE ''
			END
		END
		as "Type de données",
	pgd.description as "Commentaire"
FROM
	information_schema.columns AS ic
LEFT JOIN
	pg_namespace AS pgn
ON
	ic.table_schema = pgn.nspname
LEFT JOIN
	pg_class AS pgc
ON
	(ic.table_name = pgc.relname AND pgn.oid = pgc.relnamespace)
LEFT JOIN
	pg_description AS pgd
ON
	(pgc.oid = pgd.objoid AND ic.ordinal_position = pgd.objsubid)
WHERE
	ic.table_schema != 'pg_catalog'
AND
	ic.table_schema != 'information_schema'
ORDER BY
	ic.table_catalog,
	ic.table_schema,
	ic.table_name,
	ic.column_name,
	ic.ordinal_position;

Vue : liste des table et colonnes portants des commentaires

CREATE OR REPLACE VIEW public.info_bdd_commentaire AS
SELECT 
	it.table_schema AS "Nom schéma",
	it.table_name AS "Nom table",
	CASE
		WHEN ic.column_name IS NULL
		THEN '-TABLE-'
		ELSE ic.column_name
	END AS "Nom colonne",
	pgd.description AS "Commentaire"
FROM
	pg_description AS pgd
LEFT JOIN
	pg_class AS pgc
ON
	pgd.objoid = pgc.oid
LEFT JOIN
	information_schema.tables AS it
ON
	pgc.relname = it.table_name
LEFT JOIN
	information_schema.columns AS ic
ON
	(pgc.relname = ic.table_name AND ic.ordinal_position = pgd.objsubid)
WHERE
	it.table_schema != 'pg_catalog'
AND
	it.table_schema != 'information_schema'
ORDER BY
	it.table_schema,
	it.table_name,
	ic.ordinal_position;

Oracle

Quelques informations sur la BDD

SELECT : liste des tables et informations

Tips : il est possible d’ajouter une clause WHERE pour limiter le résultat à un seul schéma.

SELECT 
	t1.owner AS "Proprietaire",
	t1.table_name AS "Table",
	t1.tablespace_name AS "Tablespace",
	t1.num_rows AS "Nbr ligne",
	round(t1.blocks*t2.BLOCK_SIZE/1024,2) AS "Taille ko",
	round(t1.blocks*t2.BLOCK_SIZE/1024/1024,2) AS "Taille Mo",
	round(t1.blocks*t2.BLOCK_SIZE/1024/1024/1024,2) AS "Taille Go"
FROM
	all_tables t1
LEFT JOIN 
	user_tablespaces t2
	ON t1.tablespace_name = t2.tablespace_name
ORDER BY 1,2;

Les fichiers

Lister les fichiers et leurs informations

Voici un petit script Batch à copier dans un fichier .bat qui va analyser les fichiers présents dans le répertoire et les sous répertoires du .bat et créer un fichier CSV avec cette liste de fichier.

Ce script ne listera que les fichiers dont l’extension est définie dans la première partie.

Il est possible d’ajouter des extensions et d’ajouter des information pour chaque extension (avec la clé param.type[X].info).

@echo off
chcp 1252

SETLOCAL enabledelayedexpansion

REM ========================
REM Définition des variables
REM ========================

REM Liste des types analysés
REM Vecteurs
SET param.type[0].ext=.shp
SET param.type[1].ext=.shx
SET param.type[2].ext=.dbf
SET param.type[3].ext=.tab
SET param.type[4].ext=.mif
SET param.type[5].ext=.mid
SET param.type[6].ext=.dxf
SET param.type[7].ext=.dwg
SET param.type[8].ext=.mdb
SET param.type[8].info=ESRI Geodatabase personnelle
SET param.type[9].ext=.thf
SET param.type[9].info=Edigéo
SET param.type[10].ext=.bna
SET param.type[10].info=Atlas BNA
SET param.type[11].ext=.xls
SET param.type[12].ext=.xlsx
SET param.type[13].ext=.gpx
SET param.type[13].info=GPS eXchange format
SET param.type[14].ext=.geojson
SET param.type[15].ext=.mdb
SET param.type[15].info=Access
SET param.type[16].ext=.kml
SET param.type[17].ext=.kmz
SET param.type[18].ext=.mbtiles
SET param.type[18].info=Mapbox tiles
SET param.type[19].ext=.dgn
SET param.type[19].info=Microstation DGN
SET param.type[20].ext=.sqlite
SET param.type[20].info=SQLite
SET param.type[21].ext=.db
SET param.type[21].info=SQLite
SET param.type[22].ext=.sqlite3
SET param.type[22].info=SQLite
SET param.type[23].ext=.db3
SET param.type[23].info=SQLite
SET param.type[24].ext=.csv

 

REM Raster
SET param.type[30].ext=.ecw
SET param.type[31].ext=.tif
SET param.type[32].ext=.tiff
SET param.type[33].ext=.jp2
SET param.type[34].ext=.j2k
SET param.type[35].ext=.xyz
SET param.type[36].ext=.asc
SET param.type[37].ext=.grd
SET param.type[38].ext=.dem
SET param.type[39].ext=.vrt



REM ===========================
REM Fin de configuration
REM ===========================



REM ===========================
REM Gestion des paramètres
REM ===========================

REM Liste des paramètres
SET param.dir_audit[0]=FALSE
SET param.dir_audit[1]=


REM Récupération des paramètres
SET parametre=%*

REM Initialisation de la prise en compte des paramètres
SET param_en_cours=
SET /a num_param=0

REM Récuépration des paramètres dans une variable spécifique
FOR %%a in (%parametre%) do (
	
	SET /a num_param+=1
	SET var=%%a

	IF "!var:~0,1!"=="-" (
		SET param_en_cours=%%a
		SET num_param=0
	)

	REM Récupération du paramètre d'emplacement des fichiers à analyser
	IF "!param_en_cours:~0,10!"=="-dir_audit" (
		SET param.dir_audit[0]=TRUE
		
		SET valeur=!var:~11!
		REM Inversion des slash
		SET valeur=!valeur:/=\!

		REM Retrait des guillemets
		SET valeur=!valeur:"=!

		REM Retrait du slash initial
		IF "!valeur:~0,1!"=="\" (
			SET valeur=!valeur:~1!
		)

		REM Retrait du slash final
		IF "!valeur:~-1!"=="\" (
			SET valeur=!valeur:~0,-1!
		)
		
		SET param.dir_audit[1]=!valeur!\
	)

)



REM ===========================
REM Variables internes
REM ===========================

REM Nettoyage vecteurs
FOR /L %%i in (0, 1, 29) do (

	SET param.type[%%i].type=Vecteur

	IF "!param.type[%%i].ext!"=="" (
		SET param.type[%%i].ext=
	)

	IF "!param.type[%%i].info!"=="" (
		SET param.type[%%i].info=
	)
)

REM Nettoyage rasters
FOR /L %%i in (30, 1, 39) do (

	SET param.type[%%i].type=Raster

	IF "!param.type[%%i].ext!"=="" (
		SET param.type[%%i].ext=
	)

	IF "!param.type[%%i].info!"=="" (
		SET param.type[%%i].info=
	)
)



REM Définition de la date et de l'heure
REM Heure de démarrage
SET var_start_time=%time%
SET var_start_date=%date%

SET var_date=%var_start_date:~6,4%-%var_start_date:~3,2%-%var_start_date:~0,2%
SET var_time=%var_start_time:~0,2%-%var_start_time:~3,2%-%var_start_time:~6,2%
SET var_date_time=%var_date%_%var_time%


REM Titre
SET titre=Audit de fichier
title %titre%


REM Récupération de l'emplacement du script
SET var_dir_install=%~dp0


REM Log
REM Nom du fichier de log
SET var_log_filename=%var_date_time%_audit_fichier.log

REM Localisation du fichier de log
SET var_log_dir=%var_dir_install%
SET var_log_file=%var_log_dir%%var_log_filename%


REM Nom du fichier d'audit
SET var_audit_filename=%var_date_time%_audit_fichier.csv

REM Localisation du fichier d'audit
SET var_audit_dir=%var_dir_install%
SET var_audit_file=%var_log_dir%%var_audit_filename%
 
IF %param.dir_audit[0]%==TRUE (
	SET var_repertoire_audit=%param.dir_audit[1]%
) ELSE (
	SET var_repertoire_audit=%var_dir_install%
)



REM ===========================
REM Fichiers en sortie
REM ===========================

REM Init log d'audit
(
	echo =======================================
	echo ==         %titre%          ==
	echo =======================================
	echo.
	echo Date d'execution : %var_start_date:~0,2%-%var_start_date:~3,2%-%var_start_date:~6,4%
	echo Heure d'execution : %var_start_time:~0,2%:%var_start_time:~3,2%:%var_start_time:~6,5%
	echo.
	echo Emplacement du script : 
	echo 	%var_dir_install%
	echo Emplacement du fichier d'audit : 
	echo 	%var_audit_file%
	echo Emplacement des fichiers audités : 
	echo 	%var_repertoire_audit%
	echo.
	echo.
	echo.
) > "%var_log_file%"



REM Init fichier d'audit
REM On définit les titre de cahque colonne
(
	echo Type;Fichier;Extension;Taille (octet^);Emplacement;Informations
) > "%var_audit_file%" 

REM Variables de statistiques
REM Nombre de fichiers
SET var_nb_fichier_total=0
REM Nombre de fichiers
SET var_nb_fichier_audit=0
REM Taille des fichiers
SET var_taille_fichier=0



REM ===========================
REM Traitement
REM ===========================

REM Pour chaque fichier
FOR /f "usebackq delims=|" %%f in (`dir /b /s /a-d "%var_repertoire_audit%"`) do (
	REM Comptage du nombre de fichiers analysés
	SET /a var_nb_fichier_total+=1

	REM Affichage du nombre de fichiers analysés
	REM echo !time:~0,2!:!time:~3,2!:!time:~6,5! : !var_nb_fichier_total! fichiers analysés

	REM Pour chaque type de fichier listé précédemment
	FOR /L %%i in (0, 1, 39) do (

		REM Si le type de fichier correspond
		IF "%%~xf"=="!param.type[%%i].ext!" (

			REM Mise à jour des statistiques
			SET /a var_nb_fichier_audit+=1
			SET /a var_taille_fichier+=%%~zf

			(echo !param.type[%%i].type!;%%~nf;%%~xf;%%~zf;%%~pf;!param.type[%%i].info!) >> "%var_audit_file%"
		)
	)
)


REM Calcul de la taille totale en Mégaoctets
SET /a var_taille_fichier_mo=%var_taille_fichier%/1024/1024

REM Statistiques
(
	echo Nombre total de fichiers analysés : %var_nb_fichier_total%
	echo Nombre total de fichiers SIG trouvés : %var_nb_fichier_audit%
	echo Taille totale de fichiers SIG trouvés : %var_taille_fichier_mo% Mo (%var_taille_fichier% octets^)
	echo.
	echo.
	echo Fin du traitement à %time:~0,2%:%time:~3,2%:%time:~6,5%
) >> "%var_log_file%" 


:eof
ENDLOCAL

Cet article vous a plu ?

N'hésitez pas à le partager, il interessera surement certains de vos contacts.

Les thèmes suivants contiennent des articles en lien avec celui-ci, allez faire un tour :

BDDOraclePostGISPostgreSQLSIG