MaxCompute:CREATE TABLE

Tabla interna

Crear una tabla interna (no particionada o particionada)

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

Tabla agrupada en clúster

Crear una tabla agrupada en clúster

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

Tabla externa

Crear una tabla externa

Este ejemplo crea una tabla externa de OSS mediante el analizador sintáctico de datos de texto integrado. Otros formatos se describen en 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>';

Tablas transaccionales y Delta

• Crea una tabla transaccional. En este tipo de tabla se ejecutan operaciones UPDATE o DELETE. Sin embargo, las tablas transaccionales tienen limitaciones.

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

• Crea una tabla Delta. Al combinarla con una clave primaria, se realizan operaciones como upserts, consultas incrementales y 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>];

  • Una tabla Delta con clave primaria permite usar un subconjunto de dicha clave como clave de clúster hash.

    En una tabla Delta con clave primaria, el sistema utiliza agrupación en clúster hash de forma predeterminada y toma la clave primaria completa como clave de clúster. Si las consultas filtran frecuentemente por un subconjunto de columnas de la clave primaria, especifique explícitamente ese subconjunto como clave de clúster para mejorar el rendimiento del filtrado.

  • Si especifica explícitamente una clave de clúster, el sistema distribuye los datos de la tabla en buckets hash según las columnas indicadas. La clave de clúster debe ser un subconjunto de las columnas de la clave primaria.

    Si no especifica explícitamente una clave de clúster, el sistema utiliza el conjunto completo de columnas de la clave primaria como clave de clúster predeterminada.

    No es posible modificar la clave de clúster después de crear la tabla. Debe especificarla durante la creación.

Cláusulas CTAS y LIKE

• Crea una nueva tabla basada en una existente y copia los datos, pero no copia las propiedades de partición. Esta cláusula aplica a tablas externas y a tablas en proyectos externos de Lakehouse.

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

• Crea una nueva tabla con la misma estructura que una existente, pero sin copiar los datos. Esta cláusula aplica a tablas externas y a tablas en proyectos externos de Lakehouse.

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

Parámetros comunes

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

Tablas particionadas

Tablas agrupadas en clúster

Tablas externas

Tablas transaccionales y Delta

Crear tablas a partir de tablas existentes

Tabla sin particiones

• Cree una tabla sin particiones.

  CREATE TABLE test1 (key STRING);

• Cree una tabla sin particiones y especifique valores predeterminados para las columnas.

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

Tabla con particiones

• Cree una tabla con particiones estándar.

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

• Cree una tabla con particiones AUTO PARTITION que genere particiones basadas en una columna de datos temporales mediante una función de tiempo.

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

• Cree una tabla con particiones AUTO PARTITION que genere particiones basadas en el momento de ingesta de datos. El sistema recupera automáticamente la hora en que los datos se escriben en MaxCompute y genera particiones mediante una función de tiempo.

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

Tabla agrupada por hash o rango

• Cree una tabla sin particiones agrupada por hash.

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

• Cree una tabla con particiones agrupada por hash.

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

• Cree una tabla sin particiones agrupada por rango.

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

• Cree una tabla con particiones agrupada por rango.

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

Tabla transaccional

• Cree una tabla transaccional sin particiones.

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

• Cree una tabla transaccional con particiones.

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

Tabla interna

• Cree una tabla interna copiando datos de una tabla externa con particiones. La tabla interna no incluirá propiedades de partición.

  1. Cree una tabla externa de OSS y una tabla interna de MaxCompute. Cree una tabla interna mediante 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. Ejecute el comando DESC from_exetbl_oss; para consultar el esquema de la tabla interna. El comando devuelve la siguiente salida.

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

• Cree una tabla interna copiando el esquema de una tabla externa con particiones. La tabla interna incluye propiedades de partición.

  1. Cree la tabla interna from_exetbl_like. Consulte la tabla externa de OSS desde MaxCompute. Cree una tabla interna mediante 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. Ejecute el comando DESC from_exetbl_like; para consultar el esquema de la tabla interna. El comando devuelve la siguiente salida.

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

Tabla Delta

• Cree una tabla Delta.

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

• Cree una tabla Delta y establezca propiedades clave de la tabla.

  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;

Otros métodos

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

Nuevos tipos de datos

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;