Working with Suspended Users and Deleted Metadata in Alation Analytics¶
Alation Cloud Service Applies to Alation Cloud Service instances of Alation
Customer Managed Applies to customer-managed instances of Alation
Sample Queries to Filter Deleted Objects from ObjectSet Custom Field Columns¶
ObjectSet custom fields hold references to various object types within Alation. While deleted objects persist in the database with existing references in ObjectSet fields, the user interface displays only active objects or marks deleted ones with a GONE prefix or suffix.
Alation Analytics extracts and retains all object references, including deleted ones, in ObjectSet field columns. Consequently, any queries involving these columns in Alation Analytics require filtering to exclude deleted object references.
Refer to the following sample queries that filters out both soft-deleted and hard-deleted objects from the result:
Filter Out Suspended Users and Deleted Groups from an Objectset Field¶
Consider a scenario where the steward column is a PeopleSet custom field that stores references to users and group profiles within the rdbms_tables table.
The following SQL query retrieves the table ID along with the assigned steward users and steward groups referenced in this field, excluding suspended users and deleted groups:
WITH rdbms_tables_stewards AS (
SELECT
table_id,
substring(object_key from '^(.*)_\d+$') AS steward_type,
substring(object_key from '_(\d+)$')::int AS steward_id
FROM rdbms_tables,
unnest(steward) AS object_key
WHERE object_key IS NOT NULL
AND object_key <> ''
AND object_key ~ '^.+_\d+$'
)
SELECT t.table_id, t.steward_type, t.steward_id
FROM rdbms_tables_stewards t
LEFT JOIN users u
ON t.steward_type = 'user'
AND t.steward_id = u.user_id
AND u.suspended = false
LEFT JOIN alation_group g
ON t.steward_type = 'groupprofile'
AND t.steward_id = g.groupprofile_id
AND g.deleted = false
WHERE u.user_id IS NOT NULL
OR g.groupprofile_id IS NOT NULL
ORDER BY t.table_id;
Query Result¶
table_id
steward_type
steward_id
225
user
688
225
user
678
225
groupprofile
5
225
groupprofile
9
15732
user
662
15732
user
678
15732
user
1017
15732
groupprofile
11
Filter Out Deleted Business Policies from an Objectset Field¶
Consider a scenario where the applicable_policies column is a custom field that stores references to business policies within the rdbms_schemas table.
The following SQL query retrieves the schema ID along with the associated business policies referenced in this field, excluding any policies that have been marked as deleted:
WITH rdbms_schemas_policies AS (
SELECT
schema_id,
substring(object_key from '^(.*)_\d+$') AS object_type,
substring(object_key from '_(\d+)$')::int AS object_id
FROM rdbms_schemas,
unnest(applicable_policies) AS object_key
WHERE object_key IS NOT NULL
AND object_key <> ''
AND object_key ~ '^.+_\d+$'
)
SELECT s.schema_id, p.policy_id
FROM rdbms_schemas_policies s
LEFT JOIN business_policy p
ON s.object_type = 'business_policy'
AND s.object_id = p.policy_id
AND p.deleted = false
WHERE p.policy_id IS NOT NULL
ORDER BY s.schema_id;
Query Result¶
schema_id
policy_id
127
4
127
5
140
5
Filter Out Deleted RDBMS Objects from an Objectset Field¶
Consider a scenario where the db_objects column is a custom field that stores references to RDBMS objects including data sources, schemas, tables, and columns within the article table.
The following SQL query retrieves the article ID along with the associated RDBMS objects referenced in this field, excluding any entries that have been marked as deleted:
WITH articles_rdbms_objects AS (
SELECT
article_id,
substring(object_key from '^(.*)_\d+$') AS object_type,
substring(object_key from '_(\d+)$')::int AS object_id
FROM article,
unnest(db_objects) AS object_key
WHERE object_key IS NOT NULL
AND object_key <> ''
AND object_key ~ '^.+_\d+$'
)
SELECT a.article_id, a.object_type, a.object_id
FROM articles_rdbms_objects a
LEFT JOIN rdbms_datasources d
ON a.object_type = 'data'
AND a.object_id = d.ds_id
AND d.deleted = false
LEFT JOIN rdbms_schemas s
ON a.object_type = 'schema'
AND a.object_id = s.schema_id
AND s.deleted = false
LEFT JOIN rdbms_tables t
ON a.object_type = 'table'
AND a.object_id = t.table_id
AND t.deleted = false
LEFT JOIN rdbms_columns c
ON a.object_type = 'attribute'
AND a.object_id = c.column_id
AND c.deleted = false
WHERE d.ds_id IS NOT NULL
OR s.schema_id IS NOT NULL
OR t.table_id IS NOT NULL
OR c.column_id IS NOT NULL
ORDER BY a.article_id;
Query Result¶
article_id
object_type
object_id
1
data
2
1
data
3
1
schema
25
2
schema
28
2
table
103
3
table
142
3
attribute
1235
3
attribute
1437
Filter Out Deleted Articles from an Objectset Field¶
Consider a scenario where the related_articles column is a custom field that stores references to articles within the bi_folder table.
The following SQL query retrieves the BI folder ID along with the associated article objects referenced in this field, excluding any entries that have been marked as deleted:
WITH bi_folder__articles AS (
SELECT
bi_folder_id,
substring(object_key from '^(.*)_\d+$') AS object_type,
substring(object_key from '_(\d+)$')::int AS object_id
FROM bi_folder,
unnest(related_articles) AS object_key
WHERE object_key IS NOT NULL
AND object_key <> ''
AND object_key ~ '^.+_\d+$'
)
SELECT b.bi_folder_id, a.article_id
FROM bi_folder__articles b
LEFT JOIN article a
ON b.object_type = 'article'
AND b.object_id = a.article_id
AND a.deleted = false
WHERE a.article_id IS NOT NULL
ORDER BY b.bi_folder_id;
Query Result¶
bi_folder_id
article_id
6
1022
7
1013
7
1014
Filter Out Deleted Documents or Folders from an Objectset Fields¶
Consider a scenario where the related_docs column is a custom field that stores references to both documents and folders within the bi_folder table.
The following SQL query retrieves the BI folder ID along with the associated document and folder objects referenced in this field, excluding any entries that have been marked as deleted:
WITH bi_folder__docs AS (
SELECT
bi_folder_id,
substring(object_key from '^(.*)_\d+$') AS object_type,
substring(object_key from '_(\d+)$')::int AS object_id
FROM bi_folder,
unnest(related_docs) AS object_key
WHERE object_key IS NOT NULL
AND object_key <> ''
AND object_key ~ '^.+_\d+$'
)
SELECT b.bi_folder_id, b.object_type, b.object_id
FROM bi_folder__docs b
LEFT JOIN glossaries g
ON b.object_type = 'glossary_v3'
AND b.object_id = g.glossary_id
AND g.deleted = false
LEFT JOIN terms t
ON b.object_type = 'glossary_term'
AND b.object_id = t.term_id
AND t.deleted = false
WHERE g.glossary_id IS NOT NULL
OR t.term_id IS NOT NULL
ORDER BY b.bi_folder_id;
Query Result¶
bi_folder_id
object_type
object_id
10
glossary_v3
100
10
glossary_term
1235
58
glossary_v3
101
58
glossary_v3
102
58
glossary_term
7110