MaxCompute:CREATE TABLE

Internal table

Créer une internal table (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')]
];

Clustered table

Créer une clustered table

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];

External table

Créer une external table

Cet exemple crée une external table OSS à l'aide de l'analyseur de données texte intégré. D'autres formats sont décrits dans la rubrique ORC external table.

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

Transactional and Delta tables

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

  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 Delta table. 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 Delta table dotée d'une clé primaire vous permet d'utiliser un sous-ensemble de la clé primaire comme clé de hachage pour le clustering.

    Dans une Delta table avec clé primaire, le système utilise par défaut le hachage avec la clé primaire complète comme clé de clustering. 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 clustering afin d'améliorer les performances de filtrage.

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

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

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

CTAS and LIKE clauses

• 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 partitionnement. Cette clause s'applique aux external tables et aux tables situées dans 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 sans copier les données. Cette clause s'applique aux external tables et aux tables situées dans des projets Lakehouse externes.

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

Common parameters

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

Partitioned tables

Clustered tables

External tables

Transactional and Delta tables

Create tables from existing tables

Non-partitioned table

• Créez une non-partitioned table.

  CREATE TABLE test1 (key STRING);

• Créez une non-partitioned table 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
  );

Partitioned table

• Créez une standard partitioned table.

  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 AUTO PARTITION partitioned table qui génère des partitions basées sur une colonne de données temporelle à l'aide d'une fonction temporelle.

  -- 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 AUTO PARTITION partitioned table 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 temporelle.

  -- 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');

Hash or range clustered table

• Créez une hash-clustered non-partitioned table.

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

• Créez une hash-clustered partitioned table.

  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 range-clustered non-partitioned table.

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

• Créez une range-clustered partitioned table.

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

Transactional table

• Créez une non-partitioned transactional table.

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

• Créez une partitioned transactional table.

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

Internal table

• Créez une internal table en copiant les données d'une external partitioned table. L'internal table n'inclura pas les propriétés de partitionnement.

  1. Créez une OSS external table et une MaxCompute internal table. Créez une internal table à 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 afficher le schéma de l'internal table. 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 internal table en copiant le schéma d'une external partitioned table. L'internal table inclut les propriétés de partitionnement.

  1. Créez l'internal table from_exetbl_like. Interrogez l'OSS external table depuis MaxCompute. Créez une internal table à 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 afficher le schéma de l'internal table. 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     |                                                     |
     +------------------------------------------------------------------------------------+

Delta table

• Créez une Delta table.

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

• Créez une Delta table 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;

Other methods

-- 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     |       |                                             |
+------------------------------------------------------------------------------------+

New data types

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;