MaxCompute：Membuat dan menghapus tabel

Parameter umum

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

Parameter untuk tabel partisi

MaxCompute mendukung dua jenis tabel partisi: tabel partisi reguler dan tabel partisi otomatis (AUTO PARTITION). Pilih jenis tabel berdasarkan cara pembuatan kolom kunci partisi sesuai dengan skenario yang digunakan.

Parameter untuk tabel terkluster

Tabel terkluster diklasifikasikan menjadi tabel hash-clustered dan tabel range-clustered.

Parameter untuk tabel eksternal

Contoh berikut menunjukkan parameter untuk membuat tabel eksternal OSS. Untuk informasi lebih lanjut tentang cara membuat tabel eksternal, lihat Tabel Eksternal.

Parameter untuk Tabel Transaksi dan Tabel Delta

Parameter untuk membuat tabel berdasarkan tabel yang ada

• Anda dapat menggunakan pernyataan CREATE TABLE [IF NOT EXISTS] <nama_tabel> [LIFECYCLE <hari>] AS <pernyataan_select>; untuk membuat tabel dan mereplikasi data ke tabel baru.

  • Namun, properti partisi dan properti siklus hidup tabel sumber tidak direplikasi ke tabel yang dibuat. Kolom kunci partisi tabel sumber dianggap sebagai kolom biasa dalam tabel yang dibuat.

  • Anda juga dapat menggunakan parameter siklus hidup untuk mereklaim tabel. Anda juga dapat mengeksekusi pernyataan ini untuk membuat tabel internal dan mereplikasi data tabel eksternal ke tabel internal.

• Anda dapat menggunakan pernyataan CREATE TABLE [IF NOT EXISTS] <nama_tabel> [LIFECYCLE <hari>] LIKE <nama_tabel_eksisting>; untuk membuat tabel yang menggunakan skema yang sama dengan tabel yang ada.

  • Namun, tabel yang dibuat menggunakan pernyataan ini tidak mereplikasi data atau properti siklus hidup tabel sumber.

  • Anda juga dapat menggunakan parameter siklus hidup untuk mereklaim tabel. Anda juga dapat mengeksekusi pernyataan ini untuk membuat tabel internal yang memiliki skema yang sama dengan tabel eksternal yang ada.

Buat tabel non-partisi

• Buat tabel non-partisi

  CREATE TABLE test1 (key STRING);

• Buat tabel non-partisi dan konfigurasikan nilai default untuk bidang tabel.

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

Buat tabel partisi

• Buat tabel partisi reguler.

  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 AUTO PARTITION dan gunakan fungsi perhitungan waktu untuk menghasilkan partisi berdasarkan kolom data bertipe waktu.

  -- Potong kolom sale_date berdasarkan bulan untuk menghasilkan kolom partisi bernama sale_month, dan partisi tabel berdasarkan kolom partisi 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 AUTO PARTITION dan tentukan bahwa partisi dihasilkan berdasarkan waktu penulisan data. Dalam hal ini, sistem secara otomatis mendapatkan waktu ketika data ditulis ke MaxCompute dan menghasilkan partisi menggunakan fungsi perhitungan waktu.

  -- Setelah tabel dibuat, ketika data ditulis ke tabel, sistem secara otomatis mendapatkan waktu penulisan data _partitiontime, memotongnya berdasarkan hari, menghasilkan kolom partisi bernama sale_date, dan mempartisi tabel berdasarkan kolom partisi 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');

Buat tabel baru

• Buat tabel untuk menggantikan tabel yang ada.

  1. Buat tabel asli mytable dan tulis data ke tabel tersebut.

     CREATE OR REPLACE TABLE my_table(a BIGINT);
     
     INSERT INTO my_table(a) VALUES (1),(2),(3);

  2. Gunakan OR REPLACE untuk membuat tabel dengan nama yang sama dan modifikasi bidang tabel.

     CREATE OR REPLACE TABLE my_table(b STRING);

  3. Kueri tabel my_table. Kode berikut menunjukkan hasil yang dikembalikan:

     +------------+
     | b          | 
     +------------+
     +------------+

  Pernyataan SQL berikut tidak valid:

  CREATE OR REPLACE TABLE IF NOT EXISTS my_table(b STRING);
  CREATE OR REPLACE TABLE my_table AS SELECT;
  CREATE OR REPLACE TABLE my_table LIKE newtable;

• Buat tabel, replikasi data dari tabel yang ada ke tabel baru, lalu konfigurasikan siklus hidup untuk tabel baru.

  -- Buat tabel bernama sale_detail_ctas1, replikasi data dari tabel sale_detail ke tabel sale_detail_ctas1, dan konfigurasikan siklus hidup.
  SET odps.sql.allow.fullscan=true;
  CREATE TABLE sale_detail_ctas1 LIFECYCLE 10 AS SELECT * FROM sale_detail;

  Anda dapat mengeksekusi perintah DESC EXTENDED sale_detail_ctas1; untuk melihat struktur tabel, siklus hidup, dan detail lainnya.

  Catatan

  Tabel sumber sale_detail adalah tabel partisi. Namun, ketika tabel baru sale_detail_ctas1 dibuat menggunakan pernyataan CREATE TABLE ... AS select_statement ..., properti partisi tidak direplikasi. Kolom kunci partisi tabel sumber dianggap sebagai kolom biasa. Oleh karena itu, sale_detail_ctas1 adalah tabel non-partisi dengan lima kolom.

• Buat tabel dan gunakan konstanta sebagai nilai kolom dalam klausa SELECT.

  Catatan

  Jika Anda menggunakan konstanta sebagai nilai kolom dalam klausa SELECT, kami merekomendasikan Anda menentukan nama kolom. Kolom keempat dan kelima dari tabel sale_detail_ctas3 yang dibuat serupa dengan _c4 dan _c5.

  • Nama kolom ditentukan.

    SET odps.sql.allow.fullscan=true;
    CREATE TABLE sale_detail_ctas2
    AS
    SELECT shop_name, customer_id, total_price, '2013' AS sale_date, 'China' AS region
    FROM sale_detail;

  • Nama kolom tidak ditentukan.

    SET odps.sql.allow.fullscan=true;
    
    CREATE TABLE sale_detail_ctas3
    AS
    SELECT shop_name, customer_id, total_price, '2013', 'China' 
    FROM sale_detail;

• Buat tabel yang menggunakan skema yang sama dengan tabel yang ada dan konfigurasikan siklus hidup untuk tabel baru.

  CREATE TABLE sale_detail_like LIKE sale_detail LIFECYCLE 10;

  Anda dapat mengeksekusi perintah DESC EXTENDED sale_detail_like; untuk melihat struktur tabel, siklus hidup, dan detail lainnya.

  Catatan

  Skema tabel sale_detail_like sama dengan tabel sale_detail. Kedua tabel memiliki properti yang sama, seperti nama kolom, komentar kolom, dan komentar tabel, selain dari siklus hidup. Namun, data dalam tabel sale_detail tidak direplikasi ke tabel sale_detail_like.

• Buat tabel yang memiliki skema yang sama dengan tabel eksternal.

  -- Buat tabel bernama mc_oss_extable_orc_like yang memiliki skema yang sama dengan tabel eksternal mc_oss_extable_orc.
  CREATE TABLE mc_oss_extable_orc_like LIKE mc_oss_extable_orc;

  Anda dapat mengeksekusi perintah DESC mc_oss_extable_orc_like; untuk melihat struktur tabel dan detail lainnya.

  +------------------------------------------------------------------------------------+
  | Pemilik: ALIYUN$****@***.aliyunid.com | Proyek: max_compute_7u************yoq              |
  | KomentarTabel:                                                                      |
  +------------------------------------------------------------------------------------+
  | WaktuPembuatan:               2022-08-11 11:10:47                                      |
  | WaktuDDLTerakhir:              2022-08-11 11:10:47                                      |
  | WaktuModifikasiTerakhir:         2022-08-11 11:10:47                                      |
  +------------------------------------------------------------------------------------+
  | TabelInternal: YA      | Ukuran: 0                                                  |
  +------------------------------------------------------------------------------------+
  | KolomAsli:                                                                    |
  +------------------------------------------------------------------------------------+
  | Bidang           | Jenis       | Label | Komentar                                     |
  +------------------------------------------------------------------------------------+
  | id              | string     |       |                                             |
  | name            | string     |       |                                             |
  +------------------------------------------------------------------------------------+

Buat tabel yang menggunakan 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;

Buat tabel hash-clustered 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); 

Buat tabel transaksional

• Membuat tabel transaksional non-partisi.

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

• Membuat tabel transaksional partisi.

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

Buat tabel internal

• Buat tabel internal dan replikasi data tabel partisi eksternal ke tabel internal. Properti partisi tidak direplikasi ke tabel internal.

  1. Buat tabel eksternal OSS dan tabel internal MaxCompute.

     -- Buat tabel eksternal OSS dan masukkan data.
     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
     
     
     -- Eksekusi pernyataan CREATE TABLE AS untuk membuat tabel internal.
     CREATE TABLE from_exetbl_oss AS SELECT * FROM max_oss_test;
     
     -- Kueri tabel internal.
     SELECT * FROM from_exetbl_oss;
     
     -- Semua data dalam tabel internal dikembalikan.
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110

  2. Eksekusi DESC from_exetbl_as_par； untuk menanyakan struktur tabel internal. Informasi berikut dikembalikan:

     +------------------------------------------------------------------------------------+
     | Pemilik:                    ALIYUN$***********                                       |
     | Proyek:                  ***_*****_***                                            |
     | KomentarTabel:                                                                      |
     +------------------------------------------------------------------------------------+
     | WaktuPembuatan:               2023-01-10 15:16:33                                      |
     | WaktuDDLTerkahir:              2023-01-10 15:16:33                                      |
     | WaktuModifikasiTerakhir:         2023-01-10 15:16:33                                      |
     +------------------------------------------------------------------------------------+
     | TabelInternal: YA      | Ukuran: 919                                                |
     +------------------------------------------------------------------------------------+
     | KolomAsli:                                                                    |
     +------------------------------------------------------------------------------------+
     | Bidang           | Jenis       | Label | Komentar                                     |
     +------------------------------------------------------------------------------------+
     | a               | string     |       |                                             |
     | b               | string     |       |                                             |
     | c               | string     |       |                                             |
     +------------------------------------------------------------------------------------+

• Buat tabel internal dan replikasi data tabel partisi eksternal ke tabel internal. Properti partisi direplikasi ke tabel internal.

  1. Buat tabel internal bernama from_exetbl_like.

     -- Kueri tabel eksternal danau data dari MaxCompute.
     SELECT * FROM max_oss_test;
     -- Hasil
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     -- Eksekusi pernyataan CREATE TABLE LIKE untuk membuat tabel internal.
     CREATE TABLE from_exetbl_like LIKE max_oss_test;
     
     -- Kueri tabel internal.
     SELECT * FROM from_exetbl_like;
     -- Hanya skema tabel internal yang dikembalikan.
     a    b    c

  2. Eksekusi DESC from_exetbl_like； untuk menanyakan struktur tabel internal. Informasi berikut dikembalikan:

     +------------------------------------------------------------------------------------+
     | Pemilik:                    ALIYUN$************                                      |
     | Proyek:                  ***_*****_***                                            |
     | KomentarTabel:                                                                      |
     +------------------------------------------------------------------------------------+
     | WaktuPembuatan:               2023-01-10 15:09:47                                      |
     | WaktuDDLTerkahir:              2023-01-10 15:09:47                                      |
     | WaktuModifikasiTerakhir:         2023-01-10 15:09:47                                      |
     +------------------------------------------------------------------------------------+
     | TabelInternal: YA      | Ukuran: 0                                                  |
     +------------------------------------------------------------------------------------+
     | KolomAsli:                                                                    |
     +------------------------------------------------------------------------------------+
     | Bidang           | Jenis       | Label | Komentar                                     |
     +------------------------------------------------------------------------------------+
     | a               | string     |       |                                             |
     | b               | string     |       |                                             |
     +------------------------------------------------------------------------------------+
     | KolomPartisi:                                                                 |
     +------------------------------------------------------------------------------------+
     | c               | string     |                                                     |
     +------------------------------------------------------------------------------------+

Buat tabel Delta

• Buat tabel Delta.

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

• Buat tabel Delta dan konfigurasikan properti tabel utama.

  CREATE TABLE mf_tt2 ( 
    pk BIGINT NOT NULL, 
    pk2 BIGINT NOT NULL, 
    val BIGINT, 
    val2 BIGINT, 
    PRIMARY KEY (pk, pk2)
  ) 
  TBLPROPERTIES (
    "transaksional"="true", 
    "write.bucket.num" = "64", 
    "acid.data.retain.hours"="120"
  ) LIFECYCLE 7;