Dernières nouvelles de Snowflake : Débloquez la conformité et la transparence de l'utilisation grâce à l'analyse du lignage des colonnes

Présentation

Plus tôt cette année, Snowflake a ajouté une nouvelle fonctionnalité appelée Historique des accès. Cette fonctionnalité facilitera la conformité réglementaire et les audits, et fournira des informations sur les tableaux et colonnes les plus consultés et les plus consultés.

Aujourd'hui, nous allons explorer les fonctionnalités de lignage de colonnes de cette fonctionnalité à l'aide de scénarios réels. Mais auparavant, nous décrirons en détail ce qui est disponible dans le cadre de cette fonctionnalité.

Qu'est-ce que l'historique d'accès de Snowflake exactement ?

L'historique des accès fait référence au moment où la requête de l'utilisateur lit les données et au moment où l'instruction SQL effectue une opération d'écriture de données, telle que INSERT, UPDATE et DELETE, ainsi que des variantes de la commande COPY, de l'objet de données source à l'objet de données cible. L'historique des accès des utilisateurs peut être consulté en interrogeant l'utilisation du compte HISTOIRE_D'ACCÈS voir. Les enregistrements de cette vue facilitent l'audit de conformité réglementaire et fournissent des informations sur les tables et les colonnes les plus consultées et les plus consultées, car il existe un lien direct entre l'utilisateur (c'est-à-dire l'opérateur de requête), la requête, la table ou la vue, la colonne et les données.

Chaque ligne de la vue ACCESS_HISTORY contient un seul enregistrement par instruction SQL. L'enregistrement contient

• informations sur le colonnes sources la requête consultée directement et indirectement (c'est-à-dire les tables sous-jacentes dont proviennent les données de la requête), et
• le colonnes projetées l'utilisateur voit dans le résultat de la requête. Si la requête n'accède pas à une colonne du résultat de la requête ou ne la projette pas, l'enregistrement de la vue n'enregistre pas cette colonne car les données de cette colonne ne sont pas mises à la disposition de l'utilisateur.

Notez que le Historique des accès est une fonctionnalité de l'édition Enterprise et les vues d'utilisation du compte ne peuvent être interrogées que par défaut par le rôle ACCOUNTADMIN. Dans la dernière partie de cet article, nous expliquerons comment y donner accès à d'autres rôles/utilisateurs.

La vue ACCESS_HISTORY contient les colonnes suivantes :

En particulier, cette vue permet de cartographier OBJETS_MODIFIÉS et le BASE/DIRECT_OBJECTS_ACCESSED par une requête, contient donc le lignage des colonnes pour cette requête.

Pour montrer comment extraire le lignage des colonnes d'une requête, nous utiliserons deux exemples utilisant les données d'une société fictive vendant des cadeaux en ligne en Suisse.

Scénario 1 :

Vous êtes le responsable des analyses et vous souhaitez connaître l'impact de changements commerciaux spécifiques, par exemple le nom d'un changement d'élément de données. Comment cela aura-t-il un impact sur les rapports en aval ?

Il existe une table ORDERS_W_PRICES qui dépend d'une vue (qui dépend de la table ORDERS) et de la table PRODUCT dans la base de données. Voici la définition de la requête pour cette table.

CREATE OR REPLACE TABLE ORDERS_W_PRICES AS 
SELECT     
    O.ORDER_ID,    
    O.SALE_DATE,    
    O.CLIENT_ID,    
    O.PRODUCT_ID,    
    O.QUANTITY,    
    P.SALE_PRICE * O.QUANTITY AS TOTAL_PRICE
FROM ORDERS_AGG O
JOIN PRODUCTS P ON O.PRODUCT_ID = P.ID    
    AND O.SALE_DATE >= P.START_DATE    
    AND O.SALE_DATE <= P.END_DATE;

Vous souhaitez suivre l'incidence d'une modification d'un élément de données dans la table ORDERS_W_PRICES sur la table ORDERS_W_PRICES et ses rapports en aval.

Il est maintenant temps de récupérer le lignage des colonnes de cette requête, mais nous avons d'abord besoin de l'ID de requête. L'ID de requête peut être obtenu à partir des détails de la requête,

la page d'historique des requêtes, ou à l'aide de la commande suivante :

SELECT LAST_QUERY_ID();

Une fois trouvé, nous attribuons l'ID de requête à une variable

SET QID = 'your Query ID';

et nous pouvons explorer le contenu de la vue ACCESSS_HISTORY filtrée pour cet ID de requête.

SELECT
	*
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY 
WHERE QUERY_ID = $QID;

Veuillez noter qu'il y a quelques minutes de latence pour que les informations soient connectées à la vue ACCESS_HISTORY.

En particulier, le lignage des colonnes se trouve dans la colonne OBJECTS_MODIFIED. Lorsque nous cliquons sur la colonne pour développer le résultat, nous obtenons un tableau JSON du lignage :

[  
	{    
		"columns": [      
		{        
			"baseSources": [
                    {            
				"columnName": "ORDER_ID",            
				"objectDomain": "Table",            
				"objectId": 166914,            
				"objectName": "DEMO_LINEAGE.PUBLIC.ORDERS"
                    }        
                ],        
		"columnId": 218019,        
		"columnName": "ORDER_ID",        
		"directSources": [          
			{            
				"columnName": "ORDER_ID",            
				"objectDomain": "View",           
				"objectId": 215599,            
				"objectName": "DEMO_LINEAGE.PUBLIC.ORDERS_AGG"          
                    }        
                ]      
            },            

	.... (other columns were removed to reduce the size of this JSON array.)          
        ],    
		"objectDomain": "Table",    
		"objectId": 214387,    
		"objectName": "DEMO_LINEAGE.PUBLIC.ORDERS_W_PRICES"  
    }
]

‍

Dans ce tableau JSON, nous avons 4 clés :

• ObjectName : le nom de l'objet modifié
• ObjectId : l'ID de l'objet
• ObjectDomain : le type d'objet
• columns : colonnes de l'objet modifié. Pour ce champ, un autre tableau JSON y est imbriqué et contient les informations suivantes :
  
  • BaseSources : objets de base utilisés pour calculer la colonne
  • DirectSources : objets directs utilisés pour calculer la colonne

La différence entre un base et un direct object devient clair lorsque nous regardons la colonne ORDER_ID qui provient de ORDERS_AGG, l'objet direct qui est une vue. Cette vue utilise la table ORDERS. ORDERS est donc l'objet de base de ORDER_ID dans ORDERS_W_PRICES.

Ce tableau JSON peut être aplati dans un tableau pour faciliter l'analyse du lignage des colonnes à l'aide de la requête suivante :

SELECT     
	om.value:"objectName" as target_object_name,
	columns_modified.value: "columnName" as target_column_name,
	directSources.value: "objectName" as direct_Source_object_name,
	directSources.value: "columnName" as direct_Source_column_name,
	baseSources.value: "objectName" as base_Source_object_name,
	baseSources.value: "columnName" as base_Source_column_name
FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY t,
LATERAL FLATTEN(input => t.OBJECTS_MODIFIED) om,
LATERAL FLATTEN(input => om.value: "columns", outer => True) columns_modified,
LATERAL FLATTEN(    
	input => columns_modified.value: "directSources",    
	outer => true  ) 
	directSources,
LATERAL FLATTEN(    
	input => columns_modified.value: "baseSources",    
	outer => true  
    ) baseSources
WHERE t.QUERY_ID = $QID;

L'avant-dernière colonne ci-dessus nous donne les noms des tables de base dans la table ORDERS, et nous pouvons clairement déterminer comment un changement d'élément de données dans la table ORDERS affectera la table.

ORDERS_W_PRICES et ses rapports en aval.

Scénario 2 :

Vous faites partie de l'équipe de conformité et vous souhaitez auditer un tableau spécifique pour vérifier si les données stockées sont conformes aux politiques et réglementations. Par exemple, s'il contient ou non des informations personnelles et sensibles.

Modifions notre exemple précédent en ajoutant une table CLIENTS que nous joindrons à la vue ORDERS_AGG à l'aide de la colonne CLIENT_ID, et en utilisant la colonne ADRESS_CANTON de CLIENTS pour conserver les clients du canton de Genève.

CREATE OR REPLACE TABLE ORDERS_W_PRICES AS 
SELECT     
	O.ORDER_ID,    
    O.SALE_DATE,    
    O.CLIENT_ID,   
    O.PRODUCT_ID,   
    O.QUANTITY,   
    P.SALE_PRICE * O.QUANTITY AS TOTAL_PRICE
FROM ORDERS_AGG O
JOIN PRODUCTS P ON O.PRODUCT_ID = P.ID    
	AND O.SALE_DATE >= P.START_DATE    
    AND O.SALE_DATE <= P.END_DATE
JOIN CLIENTS C ON O.CLIENT_ID = C.CLIENT_ID
WHERE ADRESS_CANTON = 'GE';

Si nous relançons la requête précédente qui présentait le lignage de manière tabulaire, nous obtiendrions exactement le même résultat. C'est parce que nous n'avons utilisé que la colonne OBJETS_MODIFIÉS depuis la vue ACCESS_HISTORY, et la colonne ADRESS_CANTON n'est utilisée pour calculer aucune des colonnes de ORDER_W_PRICES. Cela s'applique également aux colonnes de la jointure. ID, START_DATE et END_DATE de PRODUCTS sont également absentes ici.

Nous devons utiliser les colonnes BASE/DIRECT_OBJECTS_ACCESSED pour obtenir les autres objets auxquels vous avez accédé dans la requête. Voici un exemple de requête utilisant uniquement les objets directs :

WITH OBJECT_MODIFIED AS (    
	SELECT 
    	t.QUERY_ID, 
        om.value:"objectName" as target_object_name,    
        columns_modified.value: "columnName" as target_column_name,    
        directSources.value: "objectId" as direct_Source_object_id,    
        directSources.value: "columnName" as direct_Source_column_name    
     FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY t, 
     LATERAL FLATTEN(input => t.OBJECTS_MODIFIED) om,    
     LATERAL FLATTEN(input => om.value: "columns", outer => True) columns_modified,    
     LATERAL FLATTEN(        
     	input => columns_modified.value: "directSources",        
        outer => true      
        ) directSources
),
DIRECT_OBJECTS AS (    
	SELECT         
    	t.QUERY_ID,       
        om.value:"objectName" as target_object_name,   
        directObjects.value : "objectName" as direct_Object_Name,    
        directObjects.value : "objectId" as direct_Object_id,     
        directColumns.value : "columnName" as direct_Object_column_Name    
    FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY t,    
    LATERAL FLATTEN(input => t.OBJECTS_MODIFIED) om,    
    LATERAL FLATTEN(input => t.DIRECT_OBJECTS_ACCESSED) directObjects,    
    LATERAL FLATTEN(input => directObjects.value: "columns", outer => True) directColumns
)
SELECT    
	DO.target_object_name,  
    OM.target_column_name,   
    DO.direct_Object_Column_Name,   
    DO.direct_Object_Name
FROM DIRECT_OBJECTS DOLEFT 
JOIN OBJECT_MODIFIED OM ON DO.QUERY_ID = OM.QUERY_ID    
	AND DO.direct_Object_id = OM.direct_Source_object_id  
    AND DO.direct_Object_column_Name = OM.direct_Source_column_name
WHERE DO.QUERY_ID = $QID
ORDER BY OM.target_column_name;

‍

Et maintenant, les autres colonnes apparaissent également dans la lignée.

Si vous êtes membre de la conformité et que vous craignez l'inclusion éventuelle d'informations d'identification personnelles dans la table ORDERS_W_PRICES après un filtre utilisant la table CLIENTS, vous pouvez vous détendre car aucune des colonnes CLIENTS n'est présente.

Donnez accès à ACCESS_HISTORY aux utilisateurs ne disposant pas du rôle ACCOUNTADMIN

Comme mentionné précédemment, le ACCESS_HISTORY ne peut être interrogé que par les utilisateurs ayant le rôle ACCOUNTADMIN. L'autorisation SELECT sur cette vue ne peut pas du tout être accordée. Cependant, il se peut que vous deviez donner accès à cette nouvelle fonctionnalité aux utilisateurs ne disposant pas du rôle ACCOUNTADMIN. Nous expliquons ci-dessous comment procéder.

Pour donner accès à cette vue à un autre rôle, une personne possédant le rôle ACCOUNTADMIN peut créer une autre vue au-dessus de ACCESS_HISTORY et accorder l'autorisation SELECT au rôle en question.

Voici un exemple d'une telle vue et de la manière dont le privilège SELECT est accordé à cette vue :

CREATE OR REPLACE VIEW DEMO_LINEAGE.ACCOUNT_USAGE.ACCESS_HISTORY AS
	SELECT *
    FROM SNOWFLAKE.ACCOUNT_USAGE.ACCESS_HISTORY
    WHERE USER_NAME IN (
    	SELECT GRANTEE_NAME 
        FROM SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS    
        WHERE ROLE = CURRENT_ROLE()        
        	AND DELETED_ON IS NULL
);
GRANT SELECT ON VIEW DEMO_LINEAGE.ACCOUNT_USAGE.ACCESS_HISTORY TO LINEAGE_DEMO_ROLE;
GRANT USAGE ON SCHEMA DEMO_LINEAGE.ACCOUNT_USAGE TO LINEAGE_DEMO_ROLE;

En plus du « SELECT * » de ACCESS_HISTORY, nous avons ajouté un filtre de sécurité des lignes pour nous assurer que les utilisateurs ne peuvent voir que leurs requêtes. UNE Politique d'accès aux lignes aurait pu être utilisé au lieu d'écrire le filtre de sécurité dans la vue.

Mots de clôture

Cet article explique comment obtenir le lignage des colonnes dans une base de données Snowflake. Nous espérons que vous pourrez l'implémenter dans votre propre environnement et maîtriser vos analyses d'impact et vos routines de conformité.

‍