Schéma d’information de la base de données Redshift
Introduction
Cet article approfondit le schéma de la base de données Redshift, en se concentrant spécifiquement sur la mise en œuvre de son schéma d’information. Nous explorerons comment il se compare à des outils similaires dans d’autres systèmes de bases de données, tels que Microsoft SQL Server et PostgreSQL. À la fin de ce guide, vous aurez une compréhension solide de la manière de tirer parti des tables système de Redshift pour optimiser vos stratégies de gestion des données.
Qu’est-ce qu’un schéma d’information dans MS SQL Server ?
Avant de plonger dans les spécificités de Redshift, commençons par un point de référence familier : le schéma d’information de Microsoft SQL Server.
Comprendre les bases
Dans MS SQL Server, le schéma d’information est un ensemble de vues qui fournissent des métadonnées sur les objets d’une base de données. C’est un moyen standardisé d’accéder à des informations sur les tables, les colonnes, les vues et d’autres objets de la base de données.
Par exemple, pour afficher toutes les tables d’une base de données en utilisant le schéma d’information de MS SQL Server, vous pouvez utiliser une requête comme celle-ci :
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE';
Cette requête renvoie une liste de toutes les tables de base de la base de données actuelle.
Schéma de base de données Redshift : Outils d’information
Maintenant, tournons-nous vers Redshift, un entrepôt de données à grande échelle d’Amazon Web Services. Bien que Redshift soit basé sur PostgreSQL, il a ses propres ensembles de tables système et de vues qui servent un objectif similaire au schéma d’information dans d’autres systèmes de bases de données.
Tables système dans Redshift
Redshift fournit un ensemble de tables système qui stockent des métadonnées sur les données cloud, ses tables et autres objets. Ces tables système sont préfixées par “PG_”, “STL_”, “STV_” ou “SVV_”.
Voici quelques tables système clés dans Redshift :
- PG_TABLE_DEF : Contient des informations sur les définitions de tables.
- SVV_COLUMNS: Fournit une vue de toutes les colonnes de la base de données.
- SVV_TABLES : Offre une vue de toutes les tables de la base de données.
Voyons un exemple d’utilisation de ces tables :
SELECT tablename, "column", type, encoding FROM pg_table_def WHERE schemaname = 'public';
Cette requête renverra des informations sur toutes les colonnes des tables dans le schéma ‘public’, y compris leurs noms, types de données et encodages.
Requêtes de schéma de base de données Redshift
Pour obtenir une vue complète de votre schéma de base de données Redshift, vous pouvez utiliser des requêtes qui combinent des informations provenant de plusieurs tables système. En voici un exemple :
SELECT
n.nspname AS schema_name,
c.relname AS table_name,
a.attname AS column_name,
t.typname AS data_type
FROM
pg_catalog.pg_class c
JOIN
pg_catalog.pg_namespace n ON n.oid = c.relnamespace
JOIN
pg_catalog.pg_attribute a ON a.attrelid = c.oid
JOIN
pg_catalog.pg_type t ON t.oid = a.atttypid
WHERE
c.relkind = 'r' -- Seules les tables régulières
AND n.nspname NOT IN ('pg_catalog', 'information_schema')
AND a.attnum > 0 -- Exclure les colonnes système
ORDER BY
schema_name, table_name, a.attnum;Cette requête fournit une vue détaillée de votre schéma de base de données Redshift, y compris les noms des schémas, les noms des tables, les noms des colonnes et les types de données.
Comparaison des outils d’information de Redshift et PostgreSQL
Étant donné que Redshift est basé sur PostgreSQL, il est naturel de se demander quelles sont les similitudes et les différences dans leurs outils de schéma d’information.
Schéma d’information PostgreSQL
PostgreSQL, comme MS SQL Server, dispose d’un INFORMATION_SCHEMA conforme à la norme SQL. Il fournit des vues qui offrent des informations sur tous les objets de la base de données.
Par exemple, pour lister toutes les tables dans PostgreSQL, vous pourriez utiliser :
SELECT table_name FROM information_schema.tables WHERE table_schema = 'public';
Redshift vs PostgreSQL
Bien que Redshift soit basé sur PostgreSQL, il n’inclut pas le standard INFORMATION_SCHEMA. Au lieu de cela, il fournit ses propres tables système et vues. Cela est dû à la nature spécialisée de Redshift en tant qu’entrepôt de données colonnaire, qui nécessite des outils d’optimisation et de gestion différents.
Cependant, de nombreux concepts sont similaires. Par exemple, là où PostgreSQL a information_schema.tables, Redshift a SVV_TABLES. Les deux fournissent des métadonnées sur les tables de la base de données, mais les spécificités des informations disponibles et la manière dont elles sont accessibles peuvent différer.
Exploiter les tables système de Redshift pour l’optimisation des performances
Comprendre les tables système de Redshift peut vous aider à optimiser les performances de votre base de données. Explorons quelques applications pratiques.
Identifier l’asymétrie des tables
L’asymétrie des tables se produit lorsque les données sont inégalement réparties entre les slices dans Redshift. Cela peut entraîner des problèmes de performances. Vous pouvez utiliser des tables système pour identifier l’asymétrie :
SELECT
trim(name) AS table,
slice,
count(*) AS num_values,
cast(100 * ratio_to_report(count(*)) over () AS decimal(5,2)) AS pct_of_total
FROM svv_diskusage
WHERE name IN ('your_table_name')
GROUP BY name, slice
ORDER BY name, slice;Cette requête montre la répartition des données entre les slices pour une table spécifique, vous aidant à identifier d’éventuels problèmes d’asymétrie.
Surveiller les performances des requêtes
Les tables STL_QUERY et SVL_QUERY_SUMMARY de Redshift peuvent vous aider à surveiller les performances des requêtes :
SELECT q.query, q.starttime, q.endtime, q.elapsed/1000000 AS elapsed_seconds, s.segment, s.step, s.maxtime/1000000 AS step_seconds, s.rows, s.bytes FROM stl_query q JOIN svl_query_summary s ON q.query = s.query WHERE q.starttime >= DATEADD(hour, -1, GETDATE()) ORDER BY q.query, s.segment, s.step;
Cette requête fournit des informations détaillées sur les requêtes exécutées dans la dernière heure, y compris leur temps d’exécution et l’utilisation des ressources.
Meilleures pratiques pour l’utilisation du schéma d’information de Redshift
Pour tirer le meilleur parti des tables et vues système de Redshift, tenez compte des meilleures pratiques suivantes :
- Surveillez régulièrement les statistiques des tables en utilisant SVV_TABLE_INFO pour garantir l’optimisation de vos tables.
- Utilisez STL_ALERT_EVENT_LOG pour identifier et résoudre de manière proactive les problèmes de performances.
- Tirez parti de SVV_VACUUM_PROGRESS pour surveiller et gérer les opérations de VACUUM.
- Utilisez SVV_DATASHARE_OBJECTS pour gérer le partage de données entre les clusters Redshift.
Rappelez-vous que, bien que ces tables système fournissent des informations précieuses, les interroger fréquemment peut avoir un impact sur les performances. Utilisez-les judicieusement et envisagez de mettre en cache les résultats lorsque c’est approprié.
Conclusion
Comprendre et utiliser efficacement les outils de schéma d’information de Redshift est crucial pour gérer et optimiser votre entrepôt de données. Bien qu’il diffère du schéma d’information standard présent dans SQL Server et PostgreSQL, les tables et vues système de Redshift offrent des capacités puissantes pour surveiller, résoudre les problèmes et optimiser votre base de données.
En exploitant ces outils, vous pouvez obtenir des informations approfondies sur votre schéma de base de données Redshift, surveiller les performances et prendre des décisions éclairées concernant la gestion des données et l’optimisation des requêtes. Comme avec tout outil puissant, utilisez ces capacités avec sagesse pour équilibrer la collecte de données et les performances globales du système.
Pour ceux qui recherchent des outils avancés de sécurité et de conformité des bases de données, envisagez d’explorer DataSunrise. Nos solutions conviviales et flexibles offrent une protection complète de la base de données. Visitez notre site Web pour une démo en ligne et découvrez comment vous pouvez améliorer la sécurité de votre base de données dès aujourd’hui.
