Suddivisione di dati semistrutturati in colonne SUPER con viste materializzate

Con Amazon Redshift puoi migliorare le prestazioni delle query suddividendo i dati in colonne SUPER utilizzando viste materializzate. La suddivisione si riferisce al processo di divisione di tipi di dati complessi come JSON o XML semistrutturati in colonne più piccole e piatte. Le colonne SUPER sono una forma specializzata di archiviazione a colonna ottimizzata per la scansione rapida dei dati suddivisi.

Nelle sezioni seguenti vengono descritte le fasi e le considerazioni per la suddivisione dei dati in colonne SUPER utilizzando viste materializzate in Amazon Redshift.

L’esempio seguente mostra la definizione di una vista materializzata che suddivide i dati annidati con le colonne risultanti ancora del tipo di dati SUPER.

SELECT c.c_name, o.o_orderstatus
FROM customer_orders_lineitem c, c.c_orders o;

L’esempio seguente mostra la definizione di una vista materializzata che crea colonne scalari Amazon Redshift convenzionali dai dati suddivisi.

SELECT c.c_name, c.c_orders[0].o_totalprice
FROM customer_orders_lineitem c;

È possibile creare una singola vista materializzata super_mv per accelerare entrambe le query.

Per rispondere alla prima query, è necessario materializzare l'attributo o_orderstatus. È possibile omettere l'attributo c_name perché non comporta la navigazione nidificata né l'annullamento della nidificazione. È inoltre necessario includere nella vista materializzata l'attributo c_custkey di customer_orders_lineitem per poter unire la tabella di base alla vista materializzata.

Per rispondere alla seconda query, è necessario materializzare anche l'attributo o_totalprice e l'indice di array o_idx di c_orders. Pertanto, è possibile accedere all'indice 0 di c_orders.

CREATE MATERIALIZED VIEW super_mv distkey(c_custkey) sortkey(c_custkey) AS (
  SELECT c_custkey, o.o_orderstatus, o.o_totalprice, o_idx
  FROM customer_orders_lineitem c, c.c_orders o AT o_idx
);

Gli attributi o_orderstatus e o_totalprice della vista materializzata super_mv sono SUPER.

La vista materializzata super_mv verrà aggiornata in modo incrementale dopo le modifiche apportate alla tabella di base customer_orders_lineitem.

REFRESH MATERIALIZED VIEW super_mv;
INFO: Materialized view super_mv was incrementally updated successfully.

Per riscrivere la prima query PartiQL come una normale query SQL, unire customer_orders_lineitem con super_mv come segue.

SELECT c.c_name, v.o_orderstatus
FROM customer_orders_lineitem c 
JOIN super_mv v ON c.c_custkey = v.c_custkey;

Allo stesso modo, è possibile riscrivere la seconda query PartiQL. Nell'esempio seguente viene utilizzato un filtro su o_idx = 0.

SELECT c.c_name, v.o_totalprice
FROM customer_orders_lineitem c 
JOIN super_mv v ON c.c_custkey = v.c_custkey
WHERE v.o_idx = 0;

Nel comando CREATE MATERIALIZED VIEW specificare c_custkey come chiave di distribuzione e chiave di ordinamento per super_mv. Amazon Redshift esegue un join di unione efficiente, supponendo che c_custkey sia anche la chiave di distribuzione e la chiave di ordinamento di customer_orders_lineitem. In caso contrario, è possibile specificare c_custkey come chiave di ordinamento e chiave di distribuzione di customer_orders_lineitem come segue.

ALTER TABLE customer_orders_lineitem
ALTER DISTKEY c_custkey, ALTER SORTKEY (c_custkey);

Utilizza l'istruzione EXPLAIN per verificare che Amazon Redshift esegua un join di unione nelle query riscritte.

EXPLAIN
      SELECT c.c_name, v.o_orderstatus
      FROM customer_orders_lineitem c JOIN super_mv v ON c.c_custkey = v.c_custkey;
      
      QUERY PLAN                                              
      ------------------------------------------------------------------------------------------------------
      XN Merge Join DS_DIST_NONE  (cost=0.00..34701.82 rows=1470776 width=27)
      Merge Cond: ("outer".c_custkey = "inner".c_custkey)
      ->  XN Seq Scan on mv_tbl__super_mv__0 derived_table2  (cost=0.00..14999.86 rows=1499986 width=13)
      ->  XN Seq Scan on customer_orders_lineitem c  (cost=0.00..999.96 rows=99996 width=30)
      (4 rows)