MaxCompute:Criar e excluir tabelas

Tabela interna

Criar uma tabela interna (não particionada ou 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')]
];

Tabela clusterizada

Criar uma tabela clusterizada

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

Tabela externa

Criar uma tabela externa

Este exemplo cria uma tabela externa OSS usando o analisador de dados de texto integrado. Outros formatos são abordados em Tabela externa 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>';

Tabelas transacionais e Delta

• Cria uma tabela transacional. É possível executar operações UPDATE ou DELETE nesse tipo de tabela. No entanto, tabelas transacionais possuem limitações.

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

• Cria uma tabela Delta. Quando combinada com uma chave primária, permite realizar operações como upserts, consultas incrementais e 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>];

  • Uma tabela Delta com chave primária permite usar um subconjunto da chave primária como chave de cluster hash.

    Em uma tabela Delta com chave primária, o sistema usa clustering hash por padrão, utilizando a chave primária completa como chave de cluster. Se as consultas filtrarem frequentemente por um subconjunto de colunas da chave primária, especifique explicitamente esse subconjunto como chave de cluster para melhorar o desempenho da filtragem.

  • Se você especificar explicitamente uma chave de cluster, o sistema distribuirá os dados da tabela em buckets hash com base nas colunas especificadas. A chave de cluster deve ser um subconjunto das colunas da chave primária.

    Caso nenhuma chave de cluster seja especificada explicitamente, o sistema usará o conjunto completo de colunas da chave primária como chave de cluster padrão.

    Não é possível modificar a chave de cluster após a criação da tabela. Especifique-a durante a criação.

Cláusulas CTAS e LIKE

• Cria uma nova tabela baseada em uma tabela existente e copia os dados, mas não copia as propriedades de partição. Esta cláusula aplica-se a tabelas externas e tabelas em projetos Lakehouse externos.

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

• Cria uma nova tabela com a mesma estrutura de uma tabela existente, mas sem copiar os dados. Esta cláusula aplica-se a tabelas externas e tabelas em projetos Lakehouse externos.

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

Parâmetros gerais

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

Parâmetros de tabela particionada

Parâmetros de tabela clusterizada

Parâmetros de tabela externa

Parâmetros de Transactional Table e Delta Table

Criar tabela (a partir de existente)

Tabela não particionada

• Crie uma tabela não particionada.

  CREATE TABLE test1 (key STRING);

• Crie uma tabela não particionada e especifique valores padrão para colunas.

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

Tabela particionada

• Crie uma tabela particionada padrão.

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

• Crie uma tabela particionada AUTO PARTITION que gera partições com base em uma coluna de dados temporal usando uma função de tempo.

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

• Crie uma tabela particionada AUTO PARTITION que gera partições com base no tempo de ingestão de dados. O sistema recupera automaticamente o horário em que os dados são gravados no MaxCompute e gera partições usando uma função de tempo.

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

Tabela com cluster hash ou de intervalo

• Crie uma tabela não particionada com cluster hash.

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

• Crie uma tabela particionada com cluster hash.

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

• Crie uma tabela não particionada com cluster de intervalo.

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

• Crie uma tabela particionada com cluster de intervalo.

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

Tabela transacional

• Crie uma tabela transacional não particionada.

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

• Crie uma tabela transacional particionada.

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

Tabela interna

• Crie uma tabela interna copiando dados de uma tabela externa particionada. A tabela interna não incluirá propriedades de partição.

  1. Crie uma tabela externa OSS e uma tabela interna MaxCompute. Crie uma tabela interna usando 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. Execute o comando DESC from_exetbl_oss; para visualizar o esquema da tabela interna. O comando retorna a seguinte saída.

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

• Crie uma tabela interna copiando o esquema de uma tabela externa particionada. A tabela interna inclui propriedades de partição.

  1. Crie a tabela interna from_exetbl_like. Consulte a tabela externa OSS a partir do MaxCompute. Crie uma tabela interna usando 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. Execute o comando DESC from_exetbl_like; para visualizar o esquema da tabela interna. O comando retorna a seguinte saída.

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

Tabela Delta

• Crie uma tabela Delta.

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

• Crie uma tabela Delta e defina propriedades-chave da tabela.

  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;

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

Novos tipos de dados

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;