MaxCompute:Membuat dan menghapus tabel

Tabel internal

Membuat 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

Membuat 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

Membuat tabel eksternal

Contoh berikut menunjukkan cara membuat tabel eksternal OSS menggunakan parser data teks bawaan. Untuk informasi lebih lanjut, lihat 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

• Membuat tabel transaksional. Anda dapat menjalankan operasi UPDATE atau DELETE pada jenis tabel ini. Namun, tabel transaksional memiliki 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")];

• Membuat tabel Delta. Ketika digabungkan dengan kunci primer, Anda dapat melakukan 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 kunci primer memungkinkan Anda menggunakan subset dari kunci primer sebagai kunci kluster hash.

    Pada tabel Delta dengan kunci primer, sistem mendistribusikan data menggunakan pengelompokan hash secara default. Kunci kluster default adalah seluruh set kolom kunci primer. Dalam beberapa skenario bisnis, kueri sering kali menyaring data menggunakan subset kolom kunci primer. Untuk skenario tersebut, Anda dapat secara eksplisit menentukan subset kunci primer sebagai kunci kluster. Hal ini memastikan bahwa data didistribusikan dan disimpan berdasarkan subset tersebut, yang meningkatkan performa penyaringan.

  • 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 kunci primer.

    Jika Anda tidak secara eksplisit menentukan kunci kluster, sistem menggunakan seluruh set kolom kunci primer sebagai kunci kluster default.

    Anda tidak dapat mengubah kunci kluster setelah tabel dibuat. Anda harus menentukannya saat membuat tabel.

Klausa CTAS dan LIKE

• Membuat tabel baru berdasarkan tabel yang ada dan menyalin datanya tetapi tidak 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>;

• Membuat tabel baru dengan struktur yang sama seperti tabel yang ada tetapi tidak 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

Membuat tabel (dari yang sudah ada)

Tabel non-partisi

• Membuat tabel non-partisi.

  CREATE TABLE test1 (key STRING);

• Membuat tabel non-partisi dan menentukan 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

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

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

• Membuat 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, dan kemudian 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

• Membuat tabel hash-clustered non-partisi.

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

• Membuat 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;

• Membuat tabel range-clustered non-partisi.

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

• Membuat 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

• Membuat tabel transaksional non-partisi.

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

• Membuat tabel transaksional partisi.

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

Tabel internal

• Membuat tabel internal dengan menyalin data dari tabel partisi eksternal. Tabel internal tidak akan mencakup properti partisi.

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

     -- Membuat tabel eksternal OSS dan memasukkan 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;
     
     -- Hasil
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     
     -- Membuat tabel internal menggunakan CREATE TABLE AS.
     CREATE TABLE from_exetbl_oss AS SELECT * FROM max_oss_test;
     
     -- Mengkueri 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     |       |                                             |
     +------------------------------------------------------------------------------------+

• Membuat tabel internal dengan menyalin skema dari tabel partisi eksternal. Tabel internal mencakup properti partisi.

  1. Buat tabel internal from_exetbl_like. Kueri tabel eksternal OSS dari MaxCompute. Buat tabel internal menggunakan CREATE TABLE LIKE.

     -- Mengkueri tabel eksternal OSS dari MaxCompute.
     SELECT * FROM max_oss_test;
     -- Hasil
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     -- Membuat tabel internal menggunakan CREATE TABLE LIKE.
     CREATE TABLE from_exetbl_like LIKE max_oss_test;
     
     -- Mengkueri tabel internal baru.
     SELECT * FROM from_exetbl_like;
     -- Hasil: 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

• Membuat Tabel Delta.

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

• Membuat Tabel Delta dan mengatur 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

-- Membuat tabel baru bernama sale_detail_ctas1, menyalin data dari sale_detail ke dalamnya, dan mengatur 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;

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