MaxCompute:Opérations sur les tables

Table interne

Créer une table interne (non partitionnée ou partitionnée)

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <table_name> (
<col_name> <data_type>, ... )
[COMMENT <table_comment>]
[PARTITIONED BY (<col_name> <data_type> [COMMENT <col_comment>], ...)]
[AUTO PARTITIONED 
   BY (<auto_partition_expression> [AS <auto_partition_column_name>])
  [TBLPROPERTIES('ingestion_time_partition'='true')]
];

Table clusterisée

Créer une table clusterisée

CREATE TABLE [IF NOT EXISTS] <table_name> (
<col_name> <data_type>, ... )
[CLUSTERED BY | RANGE CLUSTERED BY (<col_name> [, <col_name>, ...]) 
[SORTED BY (<col_name> [ASC | DESC] [, <col_name> [ASC | DESC] ...])] 
INTO <number_of_buckets> BUCKETS];

Table externe

Créer une table externe

Cet exemple crée une table externe OSS en utilisant l'analyseur de données texte intégré. D'autres formats sont abordés dans la rubrique Table externe ORC.

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> ( 
<col_name> <data_type>, ... ) 
STORED AS '<file_format>' 
[WITH SERDEPROPERTIES (options)]  
LOCATION '<oss_location>';

Tables transactionnelles et Delta

• Crée une table transactionnelle. Vous pouvez exécuter des opérations UPDATE ou DELETE sur ce type de table. Toutefois, les tables transactionnelles présentent certaines limites.

  CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <table_name> (
  <col_name <data_type> [NOT NULL] [DEFAULT <default_value>] [COMMENT <col_comment>], ...   
  [COMMENT <table_comment>]
  [TBLPROPERTIES ("transactional"="true")];

• Crée une table Delta. Lorsqu'elle est combinée à une clé primaire, elle permet d'effectuer des opérations telles que les upserts, les requêtes incrémentielles et le time travel.

  CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <table_name> (
  <col_name <data_type> [NOT NULL] [DEFAULT <default_value>] [COMMENT <col_comment>], ...   
  [PRIMARY KEY (<pk_col_name>[, <pk_col_name2>, ...] )]) 
  [COMMENT <table_comment>]
  [CLUSTERED BY (<pk_col_name>[, <pk_col_name2>, ...] )]
  [TBLPROPERTIES ("transactional"="true" 
  [, "write.bucket.num" = "N", "acid.data.retain.hours"="hours"...])] [LIFECYCLE <days>];

  • Une table Delta dotée d'une clé primaire vous permet d'utiliser un sous-ensemble de la clé primaire comme clé de hachage de cluster.

    Dans une table Delta avec clé primaire, le système utilise par défaut le clustering par hachage avec la clé primaire complète comme clé de cluster. Si vos requêtes filtrent fréquemment sur un sous-ensemble des colonnes de la clé primaire, spécifiez explicitement ce sous-ensemble comme clé de cluster afin d'améliorer les performances de filtrage.

  • Si vous spécifiez explicitement une clé de cluster, le système distribue les données de la table dans des buckets de hachage en fonction des colonnes spécifiées. La clé de cluster doit être un sous-ensemble des colonnes de la clé primaire.

    Si vous ne spécifiez pas explicitement de clé de cluster, le système utilise l'ensemble complet des colonnes de la clé primaire comme clé de cluster par défaut.

    Vous ne pouvez pas modifier la clé de cluster après la création de la table. Vous devez la spécifier lors de la création de la table.

Clauses CTAS et LIKE

• Crée une nouvelle table basée sur une table existante et copie les données, mais ne copie pas les propriétés de partition. Cette clause s'applique aux tables externes et aux tables des projets Lakehouse externes.

  CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] AS <select_statement>;

• Crée une nouvelle table ayant la même structure qu'une table existante, mais ne copie pas les données. Cette clause s'applique aux tables externes et aux tables des projets Lakehouse externes.

  CREATE TABLE [IF NOT EXISTS] <table_name> [LIFECYCLE <days>] LIKE <existing_table_name>;

Paramètres généraux

DROP TABLE IF EXISTS <tableName>;  -- If the table exists, drop it.
    CREATE TABLE <tableName> ...;      -- Create the new table.

Paramètres des tables partitionnées

Paramètres des tables clusterisées

Paramètres des tables externes

Paramètres des tables transactionnelles et Delta Table

Créer une table (à partir d'une existante)

Table non partitionnée

• Créez une table non partitionnée.

  CREATE TABLE test1 (key STRING);

• Créez une table non partitionnée et spécifiez des valeurs par défaut pour les colonnes.

  CREATE TABLE test_default( 
  tinyint_name tinyint NOT NULL default 1Y,
  smallint_name SMALLINT NOT NULL DEFAULT 1S,
  int_name INT NOT NULL DEFAULT 1,
  bigint_name BIGINT NOT NULL DEFAULT 1,
  binary_name BINARY ,
  float_name FLOAT ,
  double_name DOUBLE NOT NULL DEFAULT 0.1,
  decimal_name DECIMAL(2, 1) NOT NULL DEFAULT 0.0BD,
  varchar_name VARCHAR(10) ,
  char_name CHAR(2) ,
  string_name STRING NOT NULL DEFAULT 'N',
  boolean_name BOOLEAN NOT NULL DEFAULT TRUE
  );

Table partitionnée

• Créez une table partitionnée standard.

  CREATE TABLE IF NOT EXISTS sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE)
  PARTITIONED BY (sale_date STRING, region STRING);

• Créez une table partitionnée AUTO PARTITION qui génère des partitions basées sur une colonne de données temporelle à l'aide d'une fonction de temps.

  -- The sale_date column is truncated by month to generate a partition column named sale_month. The table is then partitioned by this column.
  CREATE TABLE IF NOT EXISTS auto_sale_detail(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE,
   sale_date    DATE )
  AUTO PARTITIONED BY (TRUNC_TIME(sale_date, 'month') AS sale_month);

• Créez une table partitionnée AUTO PARTITION qui génère des partitions basées sur l'heure d'ingestion des données. Le système récupère automatiquement l'heure à laquelle les données sont écrites dans MaxCompute et génère des partitions à l'aide d'une fonction de temps.

  -- After the table is created, when data is written, the system automatically captures the data ingestion time (_partitiontime), truncates it by day, generates a partition column named sale_date, and then partitions the table by this column.
  CREATE TABLE IF NOT EXISTS auto_sale_detail2(
   shop_name     STRING,
   customer_id   STRING,
   total_price   DOUBLE,
   _partitiontime TIMESTAMP_NTZ)
  AUTO PARTITIONED BY (TRUNC_TIME(_partitiontime, 'day') AS sale_date)
  TBLPROPERTIES('ingestion_time_partition'='true');

Table clusterisée par hachage ou par plage

• Créez une table non partitionnée clusterisée par hachage.

  CREATE TABLE t1 (a STRING, b STRING, c BIGINT) CLUSTERED BY (c) SORTED BY (c) INTO 1024 buckets;

• Créez une table partitionnée clusterisée par hachage.

  CREATE TABLE t2 (a STRING, b STRING, c BIGINT) 
  PARTITIONED BY (dt STRING) CLUSTERED BY (c) SORTED BY (c) INTO 1024 buckets;

• Créez une table non partitionnée clusterisée par plage.

  CREATE TABLE t3 (a STRING, b STRING, c BIGINT) RANGE CLUSTERED BY (c) SORTED BY (c) INTO 1024 buckets;

• Créez une table partitionnée clusterisée par plage.

  CREATE TABLE t4 (a STRING, b STRING, c BIGINT) 
  PARTITIONED BY (dt STRING) RANGE CLUSTERED BY (c) SORTED BY (c); 

Table transactionnelle

• Créez une table transactionnelle non partitionnée.

  CREATE TABLE t5(id BIGINT) TBLPROPERTIES ("transactional"="true");

• Créez une table transactionnelle partitionnée.

  CREATE TABLE IF NOT EXISTS t6(id BIGINT) 
  PARTITIONED BY (ds STRING) TBLPROPERTIES ("transactional"="true");

Table interne

• Créez une table interne en copiant les données d'une table externe partitionnée. La table interne n'inclura pas les propriétés de partition.

  1. Créez une table externe OSS et une table interne MaxCompute. Créez une table interne à l'aide de CREATE TABLE AS.

     -- Create an OSS external table and insert data into it.
     CREATE EXTERNAL TABLE max_oss_test(a INT, b INT, c INT) 
     STORED AS TEXTFILE
     LOCATION "oss://oss-cn-hangzhou-internal.aliyuncs.com/<bucket_name>";
     
     INSERT INTO max_oss_test VALUES 
     (101, 1, 20241108),
     (102, 2, 20241109),
     (103, 3, 20241110);
     
     SELECT * FROM max_oss_test;
     
     -- Result
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     -- Create an internal table by using CREATE TABLE AS.
     CREATE TABLE from_exetbl_oss AS SELECT * FROM max_oss_test;
     
     -- Query the new internal table.
     SELECT * FROM from_exetbl_oss;
     
     -- The result shows that all data is copied.
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110

  2. Exécutez la commande DESC from_exetbl_oss; pour consulter le schéma de la table interne. La commande renvoie la sortie suivante.

     +------------------------------------------------------------------------------------+
     | Owner:                    ALIYUN$***********                                       |
     | Project:                  ***_*****_***                                            |
     | TableComment:                                                                      |
     +------------------------------------------------------------------------------------+
     | CreateTime:               2023-01-10 15:16:33                                      |
     | LastDDLTime:              2023-01-10 15:16:33                                      |
     | LastModifiedTime:         2023-01-10 15:16:33                                      |
     +------------------------------------------------------------------------------------+
     | InternalTable: YES      | Size: 919                                                |
     +------------------------------------------------------------------------------------+
     | Native Columns:                                                                    |
     +------------------------------------------------------------------------------------+
     | Field           | Type       | Label | Comment                                     |
     +------------------------------------------------------------------------------------+
     | a               | string     |       |                                             |
     | b               | string     |       |                                             |
     | c               | string     |       |                                             |
     +------------------------------------------------------------------------------------+

• Créez une table interne en copiant le schéma d'une table externe partitionnée. La table interne inclut les propriétés de partition.

  1. Créez la table interne from_exetbl_like. Interrogez la table externe OSS depuis MaxCompute. Créez une table interne à l'aide de CREATE TABLE LIKE.

     -- Query the OSS external table from MaxCompute.
     SELECT * FROM max_oss_test;
     -- Result
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     -- Create an internal table by using CREATE TABLE LIKE.
     CREATE TABLE from_exetbl_like LIKE max_oss_test;
     
     -- Query the new internal table.
     SELECT * FROM from_exetbl_like;
     -- Result: Only the table schema is returned.
     a    b    c

  2. Exécutez la commande DESC from_exetbl_like; pour consulter le schéma de la table interne. La commande renvoie la sortie suivante.

     +------------------------------------------------------------------------------------+
     | Owner:                    ALIYUN$************                                      |
     | Project:                  ***_*****_***                                            |
     | TableComment:                                                                      |
     +------------------------------------------------------------------------------------+
     | CreateTime:               2023-01-10 15:09:47                                      |
     | LastDDLTime:              2023-01-10 15:09:47                                      |
     | LastModifiedTime:         2023-01-10 15:09:47                                      |
     +------------------------------------------------------------------------------------+
     | InternalTable: YES      | Size: 0                                                  |
     +------------------------------------------------------------------------------------+
     | Native Columns:                                                                    |
     +------------------------------------------------------------------------------------+
     | Field           | Type       | Label | Comment                                     |
     +------------------------------------------------------------------------------------+
     | a               | string     |       |                                             |
     | b               | string     |       |                                             |
     +------------------------------------------------------------------------------------+
     | Partition Columns:                                                                 |
     +------------------------------------------------------------------------------------+
     | c               | string     |                                                     |
     +------------------------------------------------------------------------------------+

Table Delta

• Créez une table Delta.

  CREATE TABLE mf_tt (pk BIGINT NOT NULL PRIMARY KEY, val BIGINT) 
    TBLPROPERTIES ("transactional"="true");

• Créez une table Delta et définissez les propriétés clés de la table.

  CREATE TABLE mf_tt2 ( 
    pk BIGINT NOT NULL, 
    pk2 BIGINT NOT NULL, 
    val BIGINT, 
    val2 BIGINT, 
    PRIMARY KEY (pk, pk2)
  ) 
  TBLPROPERTIES (
    "transactional"="true", 
    "write.bucket.num" = "64", 
    "acid.data.retain.hours"="120"
  ) LIFECYCLE 7;

Autres méthodes

-- Create a new table named sale_detail_ctas1, copy the data from sale_detail to it, and set a lifecycle.
SET odps.sql.allow.fullscan=true;
CREATE TABLE sale_detail_ctas1 LIFECYCLE 10 AS SELECT * FROM sale_detail;

CREATE TABLE sale_detail_like LIKE sale_detail LIFECYCLE 10;

-- Create a new table named mc_oss_extable_orc_like that has the same schema as the external table mc_oss_extable_orc.
CREATE TABLE mc_oss_extable_orc_like LIKE mc_oss_extable_orc;

+------------------------------------------------------------------------------------+
| Owner: ALIYUN$****@***.aliyunid.com | Project: max_compute_7u************yoq              |
| TableComment:                                                                      |
+------------------------------------------------------------------------------------+
| CreateTime:               2022-08-11 11:10:47                                      |
| LastDDLTime:              2022-08-11 11:10:47                                      |
| LastModifiedTime:         2022-08-11 11:10:47                                      |
+------------------------------------------------------------------------------------+
| InternalTable: YES      | Size: 0                                                  |
+------------------------------------------------------------------------------------+
| Native Columns:                                                                    |
+------------------------------------------------------------------------------------+
| Field           | Type       | Label | Comment                                     |
+------------------------------------------------------------------------------------+
| id              | string     |       |                                             |
| name            | string     |       |                                             |
+------------------------------------------------------------------------------------+

Nouveaux types de données

SET odps.sql.type.system.odps2=true;
CREATE TABLE test_newtype (
    c1 TINYINT,
    c2 SMALLINT,
    c3 INT,
    c4 BIGINT,
    c5 FLOAT,
    c6 DOUBLE,
    c7 DECIMAL,
    c8 BINARY,
    c9 TIMESTAMP,
    c10 ARRAY<MAP<BIGINT,BIGINT>>,
    c11 MAP<STRING,ARRAY<BIGINT>>,
    c12 STRUCT<s1:STRING,s2:BIGINT>,
    c13 VARCHAR(20))
LIFECYCLE 1;