Metadata SQL Views
Zur Abfrage von Metadaten wird eine Integrationsschicht auf Datenbankebene in Form von SQL Views (PostgreSQL) angeboten.
In den nachfolgenden Beispielen wird zur Veranschaulichung der Server https://www.myserver.com verwendet (z.B. im Pfad https://www.myserver.com/web/admin) und muss in echten Aufrufen entsprechend ersetzt werden.
Je nach Konfiguration muss die URL gegebenenfalls noch um den Context-Path erweitert werden (z.B. https://www.myserver.com/dataspot/web/admin).
Das physische Design des Datenmodells von dataspot ist stark geprägt von folgenden Anforderungen:
- Persistierung von Metadatenobjekten in einer objektorientierten Vererbungshierarchie
- Beziehungen zwischen Metadatenobjekten auf unterschiedlichen Ebenen in der Objekt-Hierarchie
- Vollständige Versionierung der Metadatenobjekte (einschließlich Public und Draft Versionen)
- Mehrsprachigkeit der Inhalte
- Mehrmandantenfähigkeit
Die Metadata SQL Views bieten die Möglichkeit, Metadatenobjekte abzufragen oder zu exportieren, um sie gegebenenfalls in anderen Tools (z.B. mittels BI-Software) weiterzuverarbeiten bzw. auszuwerten. Die Metadata SQL Views sind jedoch nicht geeignet, um Änderungen an den Metadatenobjekten durchzuführen. Für diesen Zweck kann das Metadata REST/API verwendet werden.
Überblick
Die Views können in folgende drei Kategorien eingeteilt werden:
- Asset Views (z.B.
attribute) - Link Views (z.B.
derivedfrom) - Sonstige Views (z.B.
customproperties,timeseries)
Jede dieser Views gibt es in drei Ausprägungen:
- Latest View (z.B.
attribute_view) - Public View (z.B.
attribute_public_view) - Version View (z.B.
attribute_version_view)
Die Menge an Ergebnissen, welche diese Views liefern, kann durch folgende Session-Variablen in PostgreSQL beeinflusst werden:
| Session-Variable | Beschreibung |
|---|---|
dataspot.language | Der ISO Code der Sprache Default: Nur die Ergebnisse in der Default-Sprache werden zurückgeliefert |
dataspot.tenants | Eine Liste von UUIDs der Mandanten, nach denen die Ergebnisse gefiltert werden sollen Default: Alle Mandanten |
dataspot.version | Der Zeitpunkt, zu dem Metadaten abgefragt werden sollen (nur relevant für Version View) Default: Aktueller Zeitpunkt |
Die Views sind vollständig dokumentiert. Bei Verwendung eines geeigneten SQL-Abfrage-Tools können sowohl die Beschreibungen der Views als auch die Beschreibungen ihrer Spalten angezeigt werden.
Metamodell
Die SQL-Abfragen richten sich nach dem Metamodell.
Das Metamodell dokumentiert alle Eigenschaften und Beziehungen der Metadatenobjekte und bildet die Grundlage für alle Metadata SQL Views.
Das Metamodell ist in der Datenbank meta unter folgender URL erreichbar:
https://www.myserver.com/web/meta
Die Datenbank meta kann in der Admin-Konsole (https://www.myserver.com/web/admin) erzeugt werden.
Beispiele und Best Practices
Am besten kann die Verwendung der Metadata SQL Views anhand von Beispielen verdeutlicht werden. Deshalb werden im Folgenden exemplarisch für typische Anwendungsfälle SQL-Statements angeführt, mithilfe derer Metadaten abgefragt bzw. ausgewertet werden können.
Die Beispiele gehen davon aus, dass search_path in PostgreSQL auf das Schema dataspot wie folgt gesetzt wurde:
SET search_path TO dataspot;
Alternativ kann der Name der View mit dem Präfix dataspot. explizit ausqualifiziert werden.
SQL-Statements
-- Anzahl Geschäftsobjekte
SELECT COUNT(*)
FROM classifier_view
WHERE _type = 'BusinessObject';
-- Veröffentlichte Attribute
SELECT classifier.label, attribute.label
FROM attribute_public_view attribute
JOIN classifier_public_view classifier
ON classifier.id = attribute.has_domain;
-- Attribute, die Bestandteile von Datasets sind
SELECT dataset.label, asset.label
FROM asset_view asset
JOIN composedof_view composedof
ON composedof.composed_of = asset.id
JOIN dataset_view dataset
ON composedof.resource_id = dataset.id
WHERE asset._type = 'BusinessAttribute';
-- Attribute in einem bestimmten Modell
SELECT attribute.label
FROM attribute_view attribute
JOIN scheme_view scheme
ON scheme.id = attribute.model_id
WHERE scheme.label = 'Fachdatenmodell';
-- Assets mit einer bestimmten benutzerspezifischen Eigenschaft
SELECT asset._type, asset.label
FROM asset_view asset
JOIN customproperties_view customproperties
ON customproperties.resource_id = asset.id
AND customproperties.name = 'securityClass'
AND customproperties.value = '"SK0"';
-- Volltext-Suche nach Assets
SELECT asset._type, asset.label, asset.title, asset.description
FROM asset_view asset
JOIN fulltext_view fulltext
ON fulltext.resource_id = asset.id
JOIN TO_TSQUERY('Person') query
ON fulltext.tokens @@ query;
-- Zeitreihen für Referenzwerte von Referenzobjekten
SELECT timeseries.valid_from, timeseries.valid_to, timeseries.code, timeseries.short_text, timeseries.long_text
FROM timeseries_view timeseries
JOIN enumeration_view enumeration
ON enumeration.label = 'Land'
JOIN literal_view literal
ON literal.literal_of = enumeration.id
AND literal.id = timeseries.resource_id;
-- Assets (inkl. untergeordnete), für die eine Person eine Rolle hat
WITH RECURSIVE results AS (
SELECT asset.id, asset._type, asset.model_id, asset.label
FROM asset_view asset
JOIN attributedto_view attributedto
ON attributedto.resource_id = asset.id
JOIN post_view post
ON post.id = attributedto.attributed_to
JOIN role_view role
ON role.id = post.has_role
AND role.label = 'Data Steward'
JOIN holdspost_view holdspost
ON holdspost.holds_post = post.id
JOIN person_view person
ON person.family_name = 'Kainz'
AND person.given_name = 'Barbara'
AND holdspost.resource_id = person.id
UNION SELECT asset.id, asset._type, asset.model_id, asset.label
FROM asset_view asset
JOIN results
ON results.id = asset.parent_id)
SELECT scheme.label, results._type, results.label
FROM results
JOIN scheme_view scheme
ON scheme.id = results.model_id;
-- Alle Geschäftsobjekte samt der Personen (einschließlich ihrer Rolle),
-- die für das Geschäftsobjekt (oder für ein übergeordnetes Asset) verantwortlich sind
WITH recursive parent (id, parent_id, resource_id) AS (
SELECT id, parent_id, id
FROM dataspot.asset_view
UNION
SELECT parent.id, asset.parent_id, asset.id
FROM dataspot.asset_view asset
JOIN parent
ON parent.parent_id = asset.id)
SELECT asset.id, dataspot.asset_to_natural_key(asset.id) AS asset_label,
attribution.attributed_as, dataspot.asset_to_natural_key(attribution.attributed_as) AS attributed_as_label,
attribution.attributed_to, dataspot.asset_to_natural_key(attribution.attributed_to) AS attributed_to_label,
attribution.agent_id, dataspot.asset_to_natural_key(attribution.agent_id) as person_label
FROM dataspot.asset_view asset
JOIN parent
ON parent.id = asset.id
JOIN dataspot.attributedagent_view attribution
ON attribution.resource_id = parent.resource_id
AND EXISTS (SELECT FROM dataspot.person_view person WHERE person.id = attribution.agent_id)
WHERE asset._type = 'BusinessObject'
ORDER by asset_label, attributed_as_label, attributed_to_label, person_label;
-- Attribute zu einem bestimmten Zeitpunkt (d.h. Version)
SET dataspot.version = '2021-11-11T11:11:11';
SELECT classifier.label, attribute.label
FROM attribute_version_view attribute
JOIN classifier_version_view classifier
ON classifier.id = attribute.has_domain;
-- Liste aller Mandanten
SELECT id, tenant_name FROM tenant;
-- Attribute eines oder mehrerer Mandanten
SET dataspot.tenants = '{"e7a30eac-5b94-4567-a0c7-f99d0d712884", "a782c41a-4aeb-49ea-905e-27c517621457"}';
SELECT classifier.label, attribute.label
FROM attribute_view attribute
JOIN classifier_view classifier
ON classifier.id = attribute.has_domain;
-- Abfrage für Verantwortlichkeiten für Systeme
select sv.label, sv.title, av.family_name, av.given_name, av.email from dataspot.attributedagent_view aav
join dataspot.system_view sv on aav.resource_id = sv.id
join dataspot.agent_view av on aav.agent_id = av.id where av._type='Person'
-- Abfrage von Verantwortlichkeiten für ein bestimmtes Asset
-- Gibt die verantwortlichen Personen, die in der jeweiligen Rolle für ein Asset verantwortlich sind aus. Berücksichtigt werden nicht nur die direkt zugeordneten Verantwortlichkeiten, sondern auch die hierarchisch übergeordneten Verantwortlichkeiten. Posten werden aufgelöst und die jeweilige Person angezeigt.
select r.label as Rolle, concat(p.given_name, ' ', p.family_name) as Person
from attributedasset_view a, person_view p, role_view r
where a.id = 'f0f9a6b6-241f-3419-88b4-f57fec5a41aa' -- UUID des betroffenen Assets
and a.attributed_to = p.id
and a.attributed_as = r.id
-- Abfrage aller Geschäftsobjekte in einer bestimmten Sammlung
with recursive collections (collection_id) as
(select id from dataspot.collection_view
where id = 'a0877ee3-096f-36ec-96c0-f6bd65e901a0' -- UUID der auszuwertenden Sammlung
union
select id from dataspot.collection_view
join collections
on in_collection = collections.collection_id)
select * from dataspot.classifier_view
join collections
on in_collection = collections.collection_id
-- Abfrage aller DQIs eines bestimmten Modells (durch UUID identifizierbar) mit Ausgabe der in einer Rolle verantwortlichen Personen (Posten werden ignoriert bzw. aufgelöst)
select a.label as DQI, c.label as Rolle, concat (d.given_name, ' ', d.family_name) as Person
from asset_view a, attributedasset_view b, agent_view c, agent_view d
where a._type = 'Indicator'
and a.model_id = 'd8e01570-18b1-3c03-9ab7-840e33dc6d2e' -- UUID des auszuwertenden Modells
and a.id = b.id
and b.attributed_as = c.id
and b.attributed_to = d.id
and d._type = 'Person'
order by a.label
-- Abfrage aller Assets die derzeit ein Schlagwort verwenden
select htv.name, htv.resource_id, av.label, av._type from dataspot.hastag_view htv
join dataspot.asset_view av on htv.resource_id = av.id
-- Abfrage aller Personen, die für ein GEschäftsobjekt verantwortlich sind (inkl. geerbter Verantwortungen und aufgelöster Posten/Gruppen)
with recursive parent (id, parent_id, resource_id) as (
select id, parent_id, id
from dataspot.asset_view
union
select parent.id, asset.parent_id, asset.id
from dataspot.asset_view asset
join parent
on parent.parent_id = asset.id)
select asset.id, dataspot.asset_to_natural_key(asset.id) as asset_label,
attribution.attributed_as, dataspot.asset_to_natural_key(attribution.attributed_as) as attributed_as_label,
attribution.attributed_to, dataspot.asset_to_natural_key(attribution.attributed_to) as attributed_to_label,
attribution.agent_id, dataspot.asset_to_natural_key(attribution.agent_id) as person_label
from dataspot.asset_view asset
join parent
on parent.id = asset.id
join dataspot.attributedagent_view attribution
on attribution.resource_id = parent.resource_id
and exists (select from dataspot.person_view person where person.id = attribution.agent_id)
where asset._type = 'BusinessObject'
order by asset_label, attributed_as_label, attributed_to_label, person_label;
-- Abfrage aller unbesetzen Posten (Posten wird derzeit durch keine Person besetzt)
select id as UUID, label as Postenname from post_view p
where not exists (select 1 from holdspost_view h where h.holds_post = p.id)
-- Abfrage der gruppierten Statistik der letzten Änderungen ab einem bestimmten Vergleichsdatum
select modified_by as Geaendert_von, _type as Asset_Typ, count(*) from asset_view
where lower(version_range)::timestamp > to_timestamp('2024-08-06', 'YYYY-MM-DD') -- Vergleichsdatum
group by modified_by, _type;
-- Abfrage aller Änderungen ab einem bestimmten Vergleichsdatum
select * from asset_view
where lower(version_range)::timestamp > to_timestamp('2024-08-06', 'YYYY-MM-DD') -- Vergleichsdatum
and modified_by = 'lwagner' -- Benutzerkennung / User-ID
-- Abfrage auf alle Assets, die eine leere Beschreibung haben
select id, _type, description, label
from asset_view
where description ISNULL
-- Historische Abfrage eines Assets inklusive Label und Title
with v as (
select distinct resource_id, lower(version_range) as version
from dataspot.asset_get_versions('b440668b-f772-3053-a5f4-5950844f1af5'))
select v.version, a.label, a.title
from v
join lateral dataspot.asset_version_at(v.resource_id, v.version) as a on true
order by v.version desc
-- Historische Abfrage von custom properties
with v as (
select distinct resource_id, lower(version_range) as version
from dataspot.asset_get_versions('b440668b-f772-3053-a5f4-5950844f1af5'))
select v.version, c.name, c.value
from v
join lateral dataspot.customproperties_version_at(v.resource_id, v.version) as c on true
order by v.version desc
-- Historische Abfrage von Links
with v as (
select distinct resource_id, lower(version_range) as version
from dataspot.asset_get_versions('b440668b-f772-3053-a5f4-5950844f1af5'))
select v.version, l._predicate, l.target_id
from v
join lateral dataspot.link_version_at(v.resource_id, v.version) as l on true
order by v.version desc
-- Abfrage aller veröffentlichten Geschäftsobjekte inklusive der historischen Versionen
with ids as (
select id as asset_id
from classifier_view
where _type = 'BusinessObject'
and status = 'PUBLISHED'
),
v as (
select distinct ids.asset_id, gv.resource_id, lower(gv.version_range) as version
from ids
cross join lateral dataspot.asset_get_versions(ids.asset_id) as gv
)
select v.asset_id, v.version, a.label, a.title
from v
join lateral dataspot.asset_version_at(v.resource_id, v.version) as a on true
order by v.asset_id, v.version desc