MaxCompute:CREATE TABLE

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 berikut menunjukkan cara membuat tabel eksternal OSS dengan menggunakan parser data teks bawaan. Untuk informasi selengkapnya, 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 beberapa keterbatasan.

  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 dikombinasikan dengan primary key, 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 primary key memungkinkan Anda menggunakan subset dari primary key sebagai kunci kluster hash.

    Pada tabel Delta dengan primary key, sistem mendistribusikan data menggunakan hash clustering secara default. Kunci kluster default adalah seluruh set kolom primary key. Dalam beberapa skenario bisnis, kueri sering kali menyaring data berdasarkan subset kolom primary key. Untuk skenario tersebut, Anda dapat secara eksplisit menentukan subset dari primary key sebagai kunci kluster. Hal ini memastikan bahwa data didistribusikan dan disimpan berdasarkan subset tersebut, sehingga 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 primary key.

    Jika Anda tidak secara eksplisit menentukan kunci kluster, sistem menggunakan seluruh set kolom primary key 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 sudah 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 sudah 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.

Tabel partisi

Tabel terkluster

Tabel eksternal

Tabel transaksional dan Delta

Buat tabel dari tabel 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 non-partisi hash-clustered.

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

• Buat tabel partisi hash-clustered.

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

• Buat tabel non-partisi range-clustered.

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

• Buat tabel partisi range-clustered.

  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. Buat tabel internal 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. Kueri tabel eksternal OSS dari MaxCompute. Buat tabel internal menggunakan 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 dengan 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;