UDF e policy Snowflake più veloci con 'memoizable'

Feb 17 2023

Scopriamo quali sono le nuove UDF SQL `memoizable', i loro effetti e limiti. Quindi mettiamoli al lavoro con un esempio che mostra come migliorare le prestazioni di policy di sicurezza complesse.

Scopriamo quali `memoizable’sono le nuove UDF SQL, i loro effetti e limiti. Quindi mettiamoli al lavoro con un esempio che mostra come migliorare le prestazioni di policy di sicurezza complesse.

Immagine generata dall'intelligenza artificiale

Gli effetti di `memoizable`

MemoizableLe UDF si comportano come variabili di sessione intelligenti che ricordano i risultati dell'UDF anche oltre una sessione. Queste UDF ricalcoleranno i risultati solo se i dati sottostanti cambiano, portando a prestazioni migliori poiché i risultati non vengono calcolati ripetutamente.

Ad esempio, considera le seguenti due UDF:

create or replace function whale_customers()
returns array
as $$
select array_agg(o_custkey)
from (
    select o_custkey, sum(o_totalprice) total
    from snowflake_sample_data.tpch_sf100.orders
    group by 1
    having total>6900000
)
$$;

create or replace function whale_customers()
returns array
memoizable
as $$
select array_agg(o_custkey)
from (
    select o_custkey, sum(o_totalprice) total
    from snowflake_sample_data.tpch_sf100.orders
    group by 1
    having total>6900000
)
$$;

La differenza di prestazioni può essere significativa, soprattutto se prevedi di utilizzare gli stessi risultati più volte.

Limitazioni

Tieni presente che memoizablele UDF sono attualmente in anteprima pubblica e presentano alcune limitazioni:

Funziona solo su UDF SQL.
Funziona solo per UDF senza parametri.
Funziona solo per UDF che restituiscono scalari o array.
Quando restituisce gli array, l'UDF restituisce un array di stringhe (comportamento corrente dell'anteprima pubblica).
Ogni memoizablefunzione ha un limite di 10 KB per i suoi risultati. È adatto per calcoli che sono costosi da eseguire, ma producono piccoli risultati.

Nota

Esistono anche immutableUDF, che presentano miglioramenti delle prestazioni simili, ma non esattamente uguali: i immutablemiglioramenti delle prestazioni funzionano solo all'interno di una query, ma immutablegli UDF possono accettare parametri e supportare più lingue rispetto alle sole UDF SQL.

Memoizable in azione: governance e politiche efficienti

Consideriamo un semplice scenario per dimostrare come possono essere utili UDF memorizzabili. Immagineremo un account con due ruoli — accountadmine sysadmin. Possono accountadminvedere tutto, ma vogliamo che sysadminvedano solo i dati dei nostri clienti "balena". Possiamo facilmente applicare questa restrizione utilizzando una politica di accesso a livello di riga:

use role accountadmin;

create or replace row access policy customer_policy_row
as (c_custkey integer) 
returns boolean ->
  true
;

alter row access policy customer_policy_row set body -> 
  'ACCOUNTADMIN' = current_role()
  or exists (
    select o_custkey, sum(o_totalprice) total
    from snowflake_sample_data.tpch_sf100.orders
    where c_custkey = o_custkey
    group by 1
    having total>6900000
  )
;

-- create or replace table customer
-- as 
-- select *
-- from snowflake_sample_data.tpch_sf10.customer 
-- order by c_custkey;

-- grant select on table customer to role sysadmin;

alter table customer
add row access policy customer_policy_row on (c_custkey)
;

Le policy a livello di riga, a livello di colonna e di mascheramento dei dati sono facili da definire, testare e gestire in Snowflake . Puoi scriverli una volta, applicarli a molte tabelle e continuare ad aggiornarli in un unico posto. Confronta questo con gli altri tuoi strumenti preferiti, se desideri vedere la differenza.
Le policy a livello di riga, a livello di colonna e di mascheramento dei dati sono potenti e flessibili in Snowflake. Qui stiamo definendo una ricerca che dipende dai risultati di un'altra tabella.
I criteri a livello di riga, a livello di colonna e di mascheramento dei dati richiedono l'account Snowflake Enterprise Edition e versioni successive . Se stai giocando con questi esempi, assicurati di non essere un account della versione standard .
Non dovresti giocare conACCOUNTADMIN . Prova questi esempi solo in un account Playground Enterprise Edition oppure rivolgiti all'amministratore del tuo account per abilitare buone politiche e la delega a securityadmin.

use role accountadmin;
select count(*), current_time()
from customer;
-- 1,500,000, 0.1s

use role sysadmin;
select count(*), current_time()
from customer;
-- 4, 3.4s

      
                
                    
                    
       The sysadmin role can only see 4 rows now, given our security policy

Questo è positivo: la politica a livello di riga ritorna non appena sa che an accountadminsta eseguendo una query, quindi non è necessario calcolare la ricerca lenta.
A sysadminpuò vedere solo 4 righe e la query restituisce in 3,4 secondi.
Questo è positivo, perché la policy a livello di riga funziona, ma abbiamo fornito una policy progettata per essere lenta . 3.4s è piuttosto lento rispetto a 0.1s.
La domanda diventa quindi come fare in modo che il framework delle politiche a livello di riga agisca velocemente come è normalmente, mentre gestisce una ricerca complessa (come quella che ho progettato qui apposta, che aggiunge> 3 secondi quando applicato).

Immagine generata dall'intelligenza artificiale

Ridefiniamo la nostra policy a livello di riga, in modo che possa sfruttare una UDF memorizzabile. Per prima cosa creiamo un UDF memorizzabile come quello che avevamo sopra:

use role accountadmin;
create or replace function whale_customers()
returns array
memoizable
as $$
select array_agg(o_custkey)
from (
    select o_custkey, sum(o_totalprice) total
    from snowflake_sample_data.tpch_sf100.orders
    group by 1
    having total>6900000
)
$$;

alter row access policy customer_policy_row set body -> 
  'ACCOUNTADMIN' = current_role()
  or array_contains(c_custkey::string::variant, whale_customers())
;

use role accountadmin;
select count(*), current_time()
from customer;
-- 1,500,000, 0.1s

use role sysadmin;
select count(*), current_time()
from customer;
-- 4, 0.1s

Note di memorizzazione nella cache

Ho aggiunto current_time()alle query di benchmarking per assicurarmi che la cache dei risultati non venga utilizzata. Potresti essere tentato di alter session set use_cached_result = false;farlo, ma ciò disabiliterà anche la memoizablecache, rendendo questi test discutibili.