MaxCompute：CREATE TABLE

Batasan

• Tabel partisi dapat memiliki maksimal enam tingkat partisi. Sebagai contoh, jika sebuah tabel menggunakan kolom tanggal sebagai kolom kunci partisi, enam tingkat partisi tersebut adalah tahun/bulan/minggu/hari/jam/menit.

• Secara default, sebuah tabel dapat memiliki maksimal 60.000 partisi. Anda dapat menyesuaikan jumlah maksimum partisi dalam sebuah tabel sesuai dengan kebutuhan bisnis Anda.

Untuk informasi lebih lanjut tentang batasan pada tabel, lihat Batasan SQL MaxCompute.

Sintaksis

Buat tabel internal

CREATE [OR REPLACE] TABLE [IF NOT EXISTS] <nama_tabel> (
<nama_kolom> <tipe_data>, ... )
[comment <komentar_tabel>]
[PARTITIONED BY (<nama_kolom> <tipe_data> [comment <komentar_kolom>], ...)]
;

Buat tabel terkluster

CREATE TABLE [IF NOT EXISTS] <nama_tabel> (
<nama_kolom> <tipe_data>, ... )
[CLUSTERED BY | RANGE CLUSTERED BY (<nama_kolom> [, <nama_kolom>, ...]) 
[SORTED BY (<nama_kolom> [ASC | DESC] [, <nama_kolom> [ASC | DESC] ...])] 
INTO <jumlah_bucket> buckets];

Buat tabel eksternal

Sebagai contoh, buat tabel eksternal OSS menggunakan resolver data teks bawaan.

CREATE EXTERNAL TABLE [IF NOT EXISTS] <nama_tabel_mc_oss_ext> ( 
<nama_kolom> <tipe_data>, ... ) 
STORED AS '<format_file>' 
[WITH SERDEPROPERTIES (opsi)]  
LOCATION '<lokasi_oss>';

Buat tabel dan tentukan jenis tabel

• Buat tabel transaksional. Anda dapat memperbarui atau menghapus data tabel setelah tabel dibuat. Namun, batasan tertentu diberlakukan pada tabel transaksional. Kami merekomendasikan Anda membuat tabel transaksional sesuai kebutuhan.

  CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <nama_tabel> (
  <nama_kolom <tipe_data> [NOT NULL] [DEFAULT <nilai_default>] [comment <komentar_kolom>], ...   
  [comment <komentar_tabel>]
  [TBLPROPERTIES ("transactional"="true")];

• Buat tabel Delta. Anda dapat menggunakan kunci utama untuk melakukan operasi pada tabel. Sebagai contoh, Anda dapat melakukan upsert, kueri inkremental, dan kueri perjalanan waktu pada tabel.

  CREATE [EXTERNAL] TABLE [IF NOT EXISTS] <nama_tabel> (
  <nama_kolom <tipe_data> [NOT NULL] [DEFAULT <nilai_default>] [comment <komentar_kolom>], ...   
  [PRIMARY KEY (<pk_nama_kolom>[, <pk_nama_kolom2>, ...] )]) 
  [comment <komentar_tabel>]
  [TBLPROPERTIES ("transactional"="true" 
  [, "write.bucket.num" = "N", "acid.data.retain.hours"="jam"...])] [LIFECYCLE <hari>];

Buat tabel baru berdasarkan tabel yang ada

• Buat tabel berdasarkan tabel yang ada dan replikasi data dari tabel yang ada ke tabel baru. Properti partisi tidak direplikasi. Anda dapat membuat tabel berdasarkan tabel eksternal yang ada atau tabel proyek eksternal yang digunakan untuk menerapkan solusi danau data terpadu.

  CREATE TABLE [IF NOT EXISTS] <nama_tabel> [LIFECYCLE <hari>] AS <pernyataan_select>;

• Buat tabel yang memiliki skema yang sama dengan tabel yang ada. Data dalam tabel yang ada tidak direplikasi. Anda dapat membuat tabel berdasarkan tabel eksternal yang ada atau tabel proyek eksternal yang digunakan untuk menerapkan solusi danau data terpadu.

  CREATE TABLE [IF NOT EXISTS] <nama_tabel> [LIFECYCLE <hari>] LIKE <nama_tabel_yang_ada>;

Parameter

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

Contoh

Buat tabel non-partisi

CREATE TABLE test1 (key STRING);

Buat tabel partisi

Buat tabel partisi bernama sale_detail.

CREATE TABLE IF NOT EXISTS sale_detail(
 nama_toko     STRING,
 id_pelanggan   STRING,
 total_harga   DOUBLE)
PARTITIONED BY (tanggal_penjualan STRING, wilayah STRING);

Buat tabel baru untuk menggantikan tabel asli

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 baru dan replikasi data dari tabel yang ada ke tabel baru

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 mengambil informasi rinci tentang skema dan siklus hidup tabel dengan mengeksekusi perintah DESC EXTENDED sale_detail_ctas1;.

Buat tabel baru dan gunakan konstanta dalam pernyataan SELECT sebagai nilai kolom

• Nama kolom ditentukan.

  SET odps.sql.allow.fullscan=true;
  CREATE TABLE sale_detail_ctas2
  AS
  SELECT nama_toko, id_pelanggan, total_harga, '2013' AS tanggal_penjualan, 'China' AS wilayah
  FROM sale_detail;

• Nama kolom tidak ditentukan.

  SET odps.sql.allow.fullscan=true;
  
  CREATE TABLE sale_detail_ctas3
  AS
  SELECT nama_toko, id_pelanggan, total_harga, '2013', 'China' 
  FROM sale_detail;

Buat tabel baru dengan skema yang sama dengan tabel yang ada

Buat tabel dengan 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 mengambil informasi rinci tentang skema dan siklus hidup tabel dengan menjalankan perintah DESC EXTENDED sale_detail_like;.

Buat tabel baru dengan 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 mengambil informasi rinci tentang skema tabel dengan mengeksekusi perintah DESC mc_oss_extable_orc_like;.

+------------------------------------------------------------------------------------+
| Pemilik:                    ALIYUN$***********                                       |
| Proyek:                  ***_*****_***                                              |
| 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                                                  |
+------------------------------------------------------------------------------------+
| Kolom Asli:                                                                    |
+------------------------------------------------------------------------------------+
| Bidang           | Jenis       | Label | Komentar                                     |
+------------------------------------------------------------------------------------+
| id              | string     |       |                                             |
| nama            | string     |       |                                             |
+------------------------------------------------------------------------------------+

Buat tabel dengan 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

• Tabel non-partisi

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

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

• Tabel non-partisi

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

• Tabel partisi

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

Buat tabel transaksional

• Tabel non-partisi

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

• Tabel partisi

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

Buat tabel non-partisi dan tentukan nilai default untuk bidang

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 internal dan replikasi data dari tabel eksternal partisi

• Tabel internal tidak berisi properti partisi.

  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 perintah DESC from_exetbl_as_par; untuk mengambil skema tabel internal.

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

• Tabel internal berisi properti partisi.

  1. Buat tabel internal 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 perintah DESC from_exetbl_like; untuk mengambil skema tabel internal.

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

Perintah terkait

• ALTER TABLE: Modifikasi operasi tabel.

• TRUNCATE: Bersihkan data dari tabel yang ditentukan.

• DROP TABLE: Hapus tabel partisi atau non-partisi.

• DESC TABLE/VIEW: Ambil informasi tentang tabel internal MaxCompute, tampilan, tampilan materialisasi, tabel eksternal, tabel terkluster, dan tabel transaksional.

• SHOW: Ambil pernyataan DDL SQL tabel, daftar semua tabel dan tampilan dalam proyek, atau daftar semua partisi dalam tabel.