Notes d’utilisation de CTAS

Limites

Amazon Redshift applique un quota correspondant au nombre de tables par cluster et par type de nœud.

Le nombre maximal de caractères pour un nom de table est 127.

Le nombre maximal de colonnes que vous pouvez définir dans une seule table est de 1 600.

Héritage des attributs de colonne et de table

Les tables CREATE TABLE AS (CTAS) n’héritent pas des contraintes, des colonnes d’identité, des valeurs de colonne par défaut ou de la clé primaire de la table à partir desquels elles ont été créées.

Vous ne pouvez pas spécifier d’encodage de compression de colonne pour les tables CTAS. Amazon Redshift attribue automatiquement l’encodage de compression comme suit :

• Les colonnes qui sont définies comme des clés de tri se voient attribuer une compression RAW.

• Les colonnes qui sont définies comme des types de données BOOLEAN, REAL, DOUBLE PRECISION ou GEOMETRY ou GEOGRAPHY se voient attribuer une compression RAW.

• Les colonnes qui sont définies comme des types de données SMALLINT, INTEGER, BIGINT, DECIMAL, DATE, TIME, TIMETZ, TIMESTAMP ou TIMESTAMPTZ se voient attribuer une compression AZ64.

• Les colonnes définies comme CHAR, VARCHAR ou VARBYTE sont affectées à la compression LZO.

Pour plus d’informations, consultez encodages de compression et Types de données.

Pour attribuer explicitement des encodages de colonne, utilisez CREATE TABLE.

CTAS détermine le style de distribution et la clé de tri de la nouvelle table basée sur le plan de requête de la clause SELECT.

Pour les requêtes complexes, telles que les requêtes qui incluent les jointures, les regroupements, une clause order by ou une clause limit, CTAS met tout en œuvre pour choisir le style de distribution optimal et la clé de tri basée sur le plan de requête.

Note

Pour de meilleures performances avec de grands ensembles de données ou des requêtes complexes, nous vous recommandons d’effectuer des tests à l’aide d’ensembles de données classiques.

Vous pouvez souvent prévoir la clé de distribution et la clé de tri que CTAS choisit en examinant le plan de requête pour afficher les colonnes que, le cas échéant, l’optimiseur de requête choisit pour le tri et la répartition des données. Si le nœud supérieur du plan de requête est une analyse séquentielle simple d’une seule table (XN Seq Scan), CTAS utilise généralement ensuite le style de distribution et la clé de tri de la table source. Si le nœud supérieur du plan de requête est autre chose qu'un scan séquentiel (tel que XN Limit, XN Sort, XN, etc.) HashAggregate, le CTAS fait de son mieux pour choisir le style de distribution et la clé de tri optimaux en fonction du plan de requête.

Par exemple, supposons que vous créez cinq tables à l’aide des types suivants de clauses SELECT :

• Une instruction select simple

• Une clause limit

• Une clause order by utilisant LISTID

• Une clause order utilisant QTYSOLD

• Une fonction d’agrégation SUM avec une clause group by.

Les exemples suivants illustrent le plan de requête pour chaque instruction CTAS.

explain create table sales1_simple as select listid, dateid, qtysold from sales;
                           QUERY PLAN
----------------------------------------------------------------
 XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=8)
(1 row)


explain create table sales2_limit as select listid, dateid, qtysold from sales limit 100;
                              QUERY PLAN
----------------------------------------------------------------------
 XN Limit  (cost=0.00..1.00 rows=100 width=8)
   ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=8)
(2 rows)


explain create table sales3_orderbylistid as select listid, dateid, qtysold from sales order by listid;
                               QUERY PLAN
------------------------------------------------------------------------
 XN Sort  (cost=1000000016724.67..1000000017155.81 rows=172456 width=8)
   Sort Key: listid
   ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=8)
(3 rows)


explain create table sales4_orderbyqty as select listid, dateid, qtysold from sales order by qtysold;
                               QUERY PLAN
------------------------------------------------------------------------
 XN Sort  (cost=1000000016724.67..1000000017155.81 rows=172456 width=8)
   Sort Key: qtysold
   ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=8)
(3 rows)


explain create table sales5_groupby as select listid, dateid, sum(qtysold) from sales group by listid, dateid;
                              QUERY PLAN
----------------------------------------------------------------------
 XN HashAggregate  (cost=3017.98..3226.75 rows=83509 width=8)
   ->  XN Seq Scan on sales  (cost=0.00..1724.56 rows=172456 width=8)
(2 rows)

Pour afficher la clé de distribution et la clé de tri de chaque table, interrogez la table catalogue système PG_TABLE_DEF, comme illustré ci-après.

select * from pg_table_def where tablename like 'sales%';

      tablename       |   column   | distkey | sortkey
----------------------+------------+---------+---------
 sales                | salesid    | f       |       0
 sales                | listid     | t       |       0
 sales                | sellerid   | f       |       0
 sales                | buyerid    | f       |       0
 sales                | eventid    | f       |       0
 sales                | dateid     | f       |       1
 sales                | qtysold    | f       |       0
 sales                | pricepaid  | f       |       0
 sales                | commission | f       |       0
 sales                | saletime   | f       |       0
 sales1_simple        | listid     | t       |       0
 sales1_simple        | dateid     | f       |       1
 sales1_simple        | qtysold    | f       |       0
 sales2_limit         | listid     | f       |       0
 sales2_limit         | dateid     | f       |       0
 sales2_limit         | qtysold    | f       |       0
 sales3_orderbylistid | listid     | t       |       1
 sales3_orderbylistid | dateid     | f       |       0
 sales3_orderbylistid | qtysold    | f       |       0
 sales4_orderbyqty    | listid     | t       |       0
 sales4_orderbyqty    | dateid     | f       |       0
 sales4_orderbyqty    | qtysold    | f       |       1
 sales5_groupby       | listid     | f       |       0
 sales5_groupby       | dateid     | f       |       0
 sales5_groupby       | sum        | f       |       0
            

Le tableau suivant résume les résultats. Pour simplifier, nous choisissons d’ignorer les détails des coûts, des lignes et de la largeur du plan d’explication.

Tableau	Instruction select CTAS	Nœud supérieur du plan d’explication	Clé de distribution	Clé de tri
S1_SIMPLE	select listid, dateid, qtysold from sales	XN Seq Scan on sales ...	LISTID	DATEID
S2_LIMIT	select listid, dateid, qtysold from sales limit 100	XN Limit ...	Aucun (EVEN)	Aucun
S3_ORDER_BY_LISTID	select listid, dateid, qtysold from sales order by listid	XN Sort ... Sort Key: listid	LISTID	LISTID
S4_ORDER_BY_QTY	select listid, dateid, qtysold from sales order by qtysold	XN Sort ... Sort Key: qtysold	LISTID	QTYSOLD
S5_GROUP_BY	select listid, dateid, sum(qtysold) from sales group by listid, dateid	XN HashAggregate ...	Aucun (EVEN)	Aucun

Vous pouvez spécifier explicitement le style de distribution et la clé de tri dans l’instruction CTAS. Par exemple, l’instruction suivante crée une table à l’aide de la distribution EVEN et spécifie SALESID en tant que clé de tri.

create table sales_disteven
diststyle even
sortkey (salesid)
as
select eventid, venueid, dateid, eventname
from event;

Encodage de compression

ENCODE AUTO est utilisé comme valeur par défaut pour les tables. Amazon Redshift gère automatiquement l’encodage de compression pour toutes les colonnes de la table.

Distribution des données entrantes

Lorsque le schéma de distribution de hachage des données entrantes correspond à celui de la table cible, aucune distribution physique des données n’est réellement nécessaire lors du chargement des données. Par exemple, si une clé de distribution est définie pour la nouvelle table et que les données sont insérées à partir d’une autre table qui est distribuée sur la même colonne de clé, les données sont chargées en place, à l’aide des mêmes nœuds et tranches. Cependant, si les tables source et cible sont toutes deux définies sur la distribution EVEN, les données sont redistribuées dans la table cible.

Opérations ANALYZE automatiques

Amazon Redshift analyse automatiquement les tables que vous créez avec les commandes CTAS. Vous n’avez pas besoin d’exécuter la commande ANALYZE sur ces tables lorsqu’elles sont créées initialement. Si vous les modifiez, vous devez les analyser de la même manière que les autres tables.