All Objects With and Without Stewards

Note

This query is for Alation Analytics Version 1 (V1).

This query retrieves all objects with Stewards. Any objects without Stewards have null values.

V R5 (5.9.x) Version of the Query

-- The following queries output information on which objects  stewards and which do not
-- We start with potential clean up
DROP TABLE IF EXISTS psCustomTempTable_ObjsWithSteward;
DROP TABLE IF EXISTS psCustomTempTable_allObjs;
DROP INDEX IF EXISTS idx_psMain1;
DROP INDEX IF EXISTS idx_psMain2;
-- Create a temporary table with all the objects with stewards
SELECT DISTINCT
      AO.object_uuid,
      OT1.object_type_name AS objectType,
      AO.object_url,
      FV.object_type_uuid_value AS stewardUUID,
      AU.user_name AS stewardUsername,
      AU.email AS stewardEmail,
      AU.is_active AS stewardActiveFlag,
      AU.display_name AS stewardDisplayname,
      AU.is_admin,
      AU.user_type
INTO
      TEMP TABLE psCustomTempTable_ObjsWithSteward
FROM
      public.alation_object AS AO
-- Join on object UUID and object type ID to get value_fp associated with the catalog object
JOIN
      public.object_field_value AS OFV
ON
      AO.object_uuid = OFV.object_uuid
AND
      AO.object_type_id = OFV.object_type_id
-- Use value_fp to get the attached steward UUID and object type ID
JOIN
      public.field_value AS FV
ON
      OFV.value_fp = FV.value_fp
-- Using object UUID and type ID, we get the value_fp associated to the stewards
JOIN
      public.object_field_value as OFV2
ON
      FV.object_type_uuid_value = OFV2.object_uuid
AND
      FV.object_type_id_value = OFV2.object_type_id
-- Now we get the value of the steward fields
JOIN
      public.field_value AS FV2
ON
      OFV2.value_fp = FV2.value_fp
-- Get user information
JOIN
      public.alation_user AS AU
ON
      AU.email = FV2.text_value
-- Get object type information
JOIN
      public.alation_object_type AS OT1
ON
      AO.object_type_id = OT1.object_type_id
-- Get deletion information
-- First grab the object UUID (uuid + type_id) and get the value_fp (value pointer)
JOIN
      public.object_field_value AS OFV3
ON
      AO.object_uuid = OFV3.object_uuid
AND
      AO.object_type_id = OFV3.object_type_id
-- Get field value
JOIN
      public.field_value AS FV3
ON
      OFV3.value_fp = FV3.value_fp
-- Match on field id to get deletion flag
JOIN
      public.object_field AS OBF
ON
      OFV3.field_id = OBF.field_id
-- Requesting field_id for stewards only
WHERE
      OFV.field_id IN (SELECT field_id FROM public.object_field WHERE field_name = 'steward')
AND
-- ensure only field_name = 'deleted' field values are extracted
      OBF.field_name = 'deleted'
AND
-- Get only objects which are not deleted
      FV3.boolean_value IS False;
-- Create an index for optimzed search
CREATE INDEX idx_psMain1 ON psCustomTempTable_ObjsWithSteward(object_uuid,objectType);
-- Create a temporary table with all the objects of types which allow stewards
SELECT DISTINCT
      AO.object_uuid,
      OT1.object_type_name AS objectType
INTO
      TEMP TABLE psCustomTempTable_allObjs
FROM
      public.alation_object AS AO
-- Get object type information
JOIN
      public.alation_object_type AS OT1
ON
      AO.object_type_id = OT1.object_type_id
-- Get deletion information
-- First grab the object UUID (uuid + type_id) and get the value_fp
 (value pointer)
JOIN
      public.object_field_value AS OFV
ON
      AO.object_uuid = OFV.object_uuid
AND
      AO.object_type_id = OFV.object_type_id
-- Get field value
JOIN
      public.field_value AS FV
ON
      OFV.value_fp = FV.value_fp
-- Match on field id to get deletion flag
JOIN
      public.object_field AS OBF
ON
      OFV.field_id = OBF.field_id
WHERE
      AO.object_type_id
IN
      (SELECT DISTINCT object_type_id FROM
 public.object_field_value WHERE field_id IN (SELECT field_id FROM
 public.object_field WHERE field_name = 'steward'))
AND
-- ensure only field_name = 'deleted' field values are extracted
      OBF.field_name = 'deleted'
AND
-- Get only objects which are not deleted
      FV.boolean_value IS False;
-- Create an index for optimized search
CREATE INDEX idx_psMain2 ON psCustomTempTable_allObjs(object_uuid,objectType);
-- Now time to get the information on which uuid+objecttype
 combinations have stewards
SELECT DISTINCT
      A.*,
      B.object_url,
      stewardUUID,
      stewardUsername,
      stewardEmail,
      stewardActiveFlag,
      stewardDisplayname,
      is_admin,
      user_type,
      CASE
            WHEN stewardUUID IS NULL THEN 'False'
            WHEN stewardUUID IS NOT NULL THEN 'True'
      END AS stewardFlag
FROM
      psCustomTempTable_allObjs AS A
FULL OUTER JOIN
      psCustomTempTable_ObjsWithSteward AS B
ON
     A.object_uuid = B.object_uuid
AND
      A.objectType = B.objectType;
-- Clean up
DROP TABLE IF EXISTS psCustomTempTable_ObjsWithSteward;
DROP TABLE IF EXISTS psCustomTempTable_allObjs;
DROP INDEX IF EXISTS idx_psMain1;
DROP INDEX IF EXISTS idx_psMain2;

V R6 (5.10.x) Version of the Query

DROP TABLE IF EXISTS psCustomTempTable_ObjsNotDeleted;
DROP TABLE IF EXISTS psCustomTempTable_ObjsWithSteward;
DROP TABLE IF EXISTS psCustomTempTable_allObjs;
DROP INDEX IF EXISTS idx_psMain1;
DROP INDEX IF EXISTS idx_psMain2;
DROP INDEX IF EXISTS idx_psMain3;

-- Create a temporary table with all the objects that are not deleted
SELECT AO.object_uuid,
OT1.object_type_name AS objectType
INTO
TEMP TABLE psCustomTempTable_ObjsNotDeleted
FROM
public.alation_object AS AO
-- Get deletion information
-- First grab the object UUID (uuid + type_id) and get the value_fp (value pointer)
JOIN
public.object_field_value AS OFV3
ON
AO.object_uuid = OFV3.object_uuid
AND
AO.object_type_id = OFV3.object_type_id
-- Get field value
JOIN
public.field_value AS FV3
ON
OFV3.value_fp = FV3.value_fp
-- Match on field id to get deletion flag
JOIN
public.object_field AS OBF
ON
OFV3.field_id = OBF.field_id
-- Get object type information
JOIN
public.alation_object_type AS OT1
ON
AO.object_type_id = OT1.object_type_id
WHERE
-- ensure only field_name = 'deleted' field values are extracted
OBF.field_name = 'deleted'
AND
-- Get only objects which are not deleted
FV3.boolean_value IS False;

-- Create an index for optimzed search
CREATE INDEX idx_psMain1 ON psCustomTempTable_ObjsNotDeleted(object_uuid,objectType);

-- Create a temporary table with all the objects with stewards
SELECT DISTINCT
AO.object_uuid,
OT1.object_type_name AS objectType,
AO.object_url,
FV.object_type_uuid_value AS stewardUUID,
AU.user_name AS stewardUsername,
AU.email AS stewardEmail,
AU.is_active AS stewardActiveFlag,
AU.display_name AS stewardDisplayname,
AU.is_admin,
AU.user_type
INTO
TEMP TABLE psCustomTempTable_ObjsWithSteward
FROM
public.alation_object AS AO
-- Join on object UUID and object type ID to get value_fp associated with the catalog object
JOIN
public.object_field_value AS OFV
ON
AO.object_uuid = OFV.object_uuid
AND
AO.object_type_id = OFV.object_type_id
-- Use value_fp to get the attached steward UUID and object type ID
JOIN
public.field_value AS FV
ON
OFV.value_fp = FV.value_fp
-- Get user information
JOIN
public.alation_user AS AU
ON
CAST(LPAD(TO_HEX((AU.user_id)), 32, '0') AS UUID) = FV.object_type_uuid_value
and FV.object_type_id_value = 33
-- Get object type information
JOIN
public.alation_object_type AS OT1
ON
AO.object_type_id = OT1.object_type_id
-- Requesting field_id for stewards only
WHERE
OFV.field_id IN (SELECT field_id FROM public.object_field WHERE field_name = 'steward');

-- Create an index for optimzed search
CREATE INDEX idx_psMain2 ON psCustomTempTable_ObjsWithSteward(object_uuid,objectType);


-- Create a temporary table with all the objects of types which allow stewards
SELECT DISTINCT
AO.object_uuid,
OT1.object_type_name AS objectType
INTO
TEMP TABLE psCustomTempTable_allObjs
FROM
public.alation_object AS AO
-- Get object type information
JOIN
public.alation_object_type AS OT1
ON
AO.object_type_id = OT1.object_type_id
-- Get deletion information
-- First grab the object UUID (uuid + type_id) and get the value_fp (value pointer)
JOIN
public.object_field_value AS OFV
ON
AO.object_uuid = OFV.object_uuid
AND
AO.object_type_id = OFV.object_type_id
-- Get field value
JOIN
public.field_value AS FV
ON
OFV.value_fp = FV.value_fp
-- Match on field id to get deletion flag
JOIN
public.object_field AS OBF
ON
OFV.field_id = OBF.field_id
WHERE
AO.object_type_id
IN
(SELECT DISTINCT object_type_id FROM public.object_field_value WHERE field_id IN (SELECT field_id FROM public.object_field WHERE field_name = 'steward'))
AND
-- ensure only field_name = 'deleted' field values are extracted
OBF.field_name = 'deleted'
AND
-- Get only objects which are not deleted
FV.boolean_value IS False;
-- Create an index for optimized search
CREATE INDEX idx_psMain3 ON psCustomTempTable_allObjs(object_uuid,objectType);

-- Now time to get the information on which uuid+objecttype combinations have stewards
SELECT DISTINCT
A.*,
B.object_url,
stewardUUID,
stewardUsername,
stewardEmail,
stewardActiveFlag,
stewardDisplayname,
is_admin,
user_type,
CASE
WHEN stewardUUID IS NULL THEN 'False'
WHEN stewardUUID IS NOT NULL THEN 'True'
END AS stewardFlag
FROM
psCustomTempTable_allObjs AS A
FULL OUTER JOIN
(
SELECT DISTINCT S.*
FROM psCustomTempTable_ObjsWithSteward
INNER JOIN psCustomTempTable_ObjsNotDeleted D
ON S.object_uuid = D.object_uuid and S.objectType = D.objectType
)AS B
ON
A.object_uuid = B.object_uuid
AND
A.objectType = B.objectType;

Related Topics

Alation Analytics QUERY INDEX