MaxCompute:Membuat dan menghapus tabel

Tabel internal

Buat tabel internal (non-partisi atau partisi)

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

Tabel terkluster

Buat tabel terkluster

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

Tabel eksternal

Buat tabel eksternal

Contoh ini membuat tabel eksternal OSS menggunakan parser data teks bawaan. Format lain dibahas di Tabel eksternal 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>';

Tabel transaksional dan Delta

• Buat tabel transaksional. Jenis tabel ini mendukung operasi UPDATE atau DELETE, namun memiliki sejumlah batasan.

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

• Buat tabel Delta. Ketika digabungkan dengan primary key, tabel ini memungkinkan operasi seperti upsert, kueri inkremental, dan 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>];

  • Tabel Delta dengan primary key memungkinkan penggunaan subset dari primary key sebagai kunci kluster hash.

    Pada tabel Delta dengan primary key, sistem secara default menerapkan pengelompokan hash menggunakan seluruh primary key sebagai kunci kluster. Jika kueri sering melakukan filter pada subset kolom primary key, tentukan secara eksplisit subset tersebut sebagai kunci kluster untuk meningkatkan performa filtering.

  • Jika Anda secara eksplisit menentukan kunci kluster, sistem mendistribusikan data tabel ke dalam bucket hash berdasarkan kolom yang ditentukan. Kunci kluster harus merupakan subset dari kolom primary key.

    Jika tidak ditentukan secara eksplisit, sistem menggunakan seluruh set kolom primary key sebagai kunci kluster default.

    Kunci kluster tidak dapat diubah setelah tabel dibuat; Anda harus menentukannya saat pembuatan tabel.

Klausa CTAS dan LIKE

• Buat tabel baru berdasarkan tabel yang ada dan salin datanya, tetapi tanpa menyalin properti partisi. Klausa ini berlaku untuk tabel eksternal dan tabel di Proyek Lakehouse eksternal.

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

• Buat tabel baru dengan struktur yang sama seperti tabel yang ada, tetapi tanpa menyalin datanya. Klausa ini berlaku untuk tabel eksternal dan tabel di Proyek Lakehouse eksternal.

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

Parameter umum

DROP TABLE IF EXISTS <tableName>;  -- Jika tabel ada, hapus.
CREATE TABLE <tableName> ...;      -- Buat tabel baru.

Parameter tabel partisi

Parameter tabel terkluster

Parameter tabel eksternal

Parameter Tabel Transaksional dan Tabel Delta

Buat tabel (dari yang sudah ada)

Tabel non-partisi

• Buat tabel non-partisi.

  CREATE TABLE test1 (key STRING);

• Buat tabel non-partisi dan tentukan nilai default untuk kolom.

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

Tabel partisi

• Buat tabel partisi standar.

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

• Buat tabel partisi AUTO PARTITION yang menghasilkan partisi berdasarkan kolom data berbasis waktu menggunakan fungsi waktu.

  -- Kolom sale_date dipotong berdasarkan bulan untuk menghasilkan kolom partisi bernama sale_month. Tabel kemudian dipartisi berdasarkan kolom ini.
  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);

• Buat tabel partisi AUTO PARTITION yang menghasilkan partisi berdasarkan waktu pemasukan data. Sistem secara otomatis mengambil waktu ketika data ditulis ke MaxCompute dan menghasilkan partisi menggunakan fungsi waktu.

  -- Setelah tabel dibuat, saat data ditulis, sistem secara otomatis menangkap waktu pemasukan data (_partitiontime), memotongnya berdasarkan hari, menghasilkan kolom partisi bernama sale_date, lalu mempartisi tabel berdasarkan kolom ini.
  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');

Tabel hash atau range clustered

• Buat tabel hash-clustered non-partisi.

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

• Buat tabel hash-clustered partisi.

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

• Buat tabel range-clustered non-partisi.

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

• Buat tabel range-clustered partisi.

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

Tabel transaksional

• Buat tabel transaksional non-partisi.

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

• Buat tabel transaksional partisi.

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

Tabel internal

• Buat tabel internal dengan menyalin data dari tabel partisi eksternal. Tabel internal tidak akan menyertakan properti partisi.

  1. Buat tabel eksternal OSS dan tabel internal MaxCompute menggunakan CREATE TABLE AS.

     -- Buat tabel eksternal OSS dan masukkan data ke dalamnya.
     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
     
     
     -- Buat tabel internal menggunakan CREATE TABLE AS.
     CREATE TABLE from_exetbl_oss AS SELECT * FROM max_oss_test;
     
     -- Kueri tabel internal baru.
     SELECT * FROM from_exetbl_oss;
     
     -- Hasil menunjukkan bahwa semua data disalin.
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110

  2. Jalankan perintah DESC from_exetbl_oss; untuk melihat skema tabel internal. Perintah mengembalikan output berikut.

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

• Buat tabel internal dengan menyalin skema dari tabel partisi eksternal. Tabel internal menyertakan properti partisi.

  1. Buat tabel internal from_exetbl_like dengan menjalankan kueri terhadap tabel eksternal OSS dari MaxCompute menggunakan perintah CREATE TABLE LIKE.

     -- Kueri tabel eksternal OSS dari MaxCompute.
     SELECT * FROM max_oss_test;
     -- Result
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     -- Buat tabel internal menggunakan CREATE TABLE LIKE.
     CREATE TABLE from_exetbl_like LIKE max_oss_test;
     
     -- Kueri tabel internal baru.
     SELECT * FROM from_exetbl_like;
     -- Result: Hanya skema tabel yang dikembalikan.
     a    b    c

  2. Jalankan perintah DESC from_exetbl_like; untuk melihat skema tabel internal. Perintah mengembalikan output berikut.

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

Tabel Delta

• Buat tabel Delta.

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

• Buat tabel Delta dan atur properti tabel utama.

  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;

Metode lain

-- Buat tabel baru bernama sale_detail_ctas1, salin data dari sale_detail ke dalamnya, dan atur siklus hidup.
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;

-- Buat tabel baru bernama mc_oss_extable_orc_like yang memiliki skema yang sama seperti tabel eksternal 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     |       |                                             |
+------------------------------------------------------------------------------------+

Tipe data baru

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;