MaxCompute:CREATE TABLE

Dahili tablo

Dahili tablo oluşturma (bölümlenmemiş veya bölümlenmiş)

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

Küme tabanlı tablo

Küme tabanlı tablo oluşturma

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

Harici tablo

Harici tablo oluşturma

Bu örnekte yerleşik metin veri ayrıştırıcısı kullanılarak bir OSS harici tablosu oluşturulur. Diğer biçimler ORC harici tablosu bölümünde ele alınmıştır.

CREATE EXTERNAL TABLE [IF NOT EXISTS] <mc_oss_extable_name> ( 
<col_name> <data_type>, ... ) 
STORED AS '<file_format>' 
[WITH SERDEPROPERTIES (options)]  
LOCATION '<oss_location>';

İşlemsel ve Delta tabloları

• İşlemsel bir tablo oluşturur. Bu tür bir tabloda UPDATE veya DELETE işlemlerini çalıştırabilirsiniz. Ancak işlemsel tabloların sınırlamaları vardır.

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

• Delta tablosu oluşturur. Birincil anahtarla birlikte kullanıldığında upsert işlemleri, artımlı sorgular ve zaman yolculuğu gibi işlemler yapabilirsiniz.

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

  • Birincil anahtarı olan bir Delta tablosunda, birincil anahtarın alt kümesini karma kümeleme anahtarı olarak kullanabilirsiniz.

    Birincil anahtarı olan bir Delta tablosunda sistem varsayılan olarak tüm birincil anahtarı kümeleme anahtarı olarak kullanarak karma kümelemeyi uygular. Sorgular sıkça birincil anahtar sütunlarının alt kümesine göre filtreleme yapıyorsa, filtreleme performansını artırmak amacıyla bu alt kümeyi açıkça kümeleme anahtarı olarak belirtin.

  • Kümeleme anahtarını açıkça belirtirseniz sistem tablo verilerini belirtilen sütunlara göre karma paketlere dağıtır. Kümeleme anahtarı, birincil anahtar sütunlarının bir alt kümesi olmalıdır.

    Kümeleme anahtarını açıkça belirtmezseniz sistem varsayılan kümeleme anahtarı olarak tüm birincil anahtar sütunlarını kullanır.

    Tablo oluşturulduktan sonra kümeleme anahtarını değiştiremezsiniz. Tabloyu oluştururken bunu belirtmeniz gerekir.

CTAS ve LIKE yan tümceleri

• Mevcut bir tabloyu temel alarak yeni bir tablo oluşturur ve verileri kopyalar ancak bölüm özelliklerini kopyalamaz. Bu yan tümce harici tablolara ve harici Lakehouse projelerindeki tablolara uygulanır.

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

• Mevcut bir tabloyla aynı yapıya sahip yeni bir tablo oluşturur ancak verileri kopyalamaz. Bu yan tümce harici tablolara ve harici Lakehouse projelerindeki tablolara uygulanır.

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

Ortak parametreler

DROP TABLE IF EXISTS <tableName>;  -- If the table exists, drop it.
CREATE TABLE <tableName> ...;      -- Create the new table.

Bölümlenmiş tablolar

Küme tabanlı tablolar

Harici tablolar

İşlemsel ve Delta tabloları

Mevcut tablolardan tablo oluşturma

Bölümlenmemiş tablo

• Bölümlenmemiş bir tablo oluşturun.

  CREATE TABLE test1 (key STRING);

• Bölümlenmemiş bir tablo oluşturun ve sütunlar için varsayılan değerler belirtin.

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

Bölümlenmiş tablo

• Standart bölümlenmiş bir tablo oluşturun.

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

• Zaman işlevi kullanarak zamana dayalı bir veri sütununa göre bölümler oluşturan AUTO PARTITION bölümlenmiş bir tablo oluşturun.

  -- The sale_date column is truncated by month to generate a partition column named sale_month. The table is then partitioned by this column.
  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);

• Veri alım zamanına göre bölümler oluşturan AUTO PARTITION bölümlenmiş bir tablo oluşturun. Sistem verilerin MaxCompute'a yazıldığı zamanı otomatik olarak alır ve bir zaman işlevi kullanarak bölümler oluşturur.

  -- After the table is created, when data is written, the system automatically captures the data ingestion time (_partitiontime), truncates it by day, generates a partition column named sale_date, and then partitions the table by this column.
  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');

Karma veya aralık küme tabanlı tablo

• Karma küme tabanlı bölümlenmemiş bir tablo oluşturun.

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

• Karma küme tabanlı bölümlenmiş bir tablo oluşturun.

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

• Aralık küme tabanlı bölümlenmemiş bir tablo oluşturun.

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

• Aralık küme tabanlı bölümlenmiş bir tablo oluşturun.

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

İşlemsel tablo

• Bölümlenmemiş işlemsel bir tablo oluşturun.

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

• Bölümlenmiş işlemsel bir tablo oluşturun.

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

Dahili tablo

• Harici bölümlenmiş bir tablodan veri kopyalayarak dahili bir tablo oluşturun. Dahili tablo bölüm özelliklerini içermez.

  1. Bir OSS harici tablosu ve bir MaxCompute dahili tablosu oluşturun. CREATE TABLE AS kullanarak dahili bir tablo oluşturun.

     -- Create an OSS external table and insert data into it.
     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
     
     -- Create an internal table by using CREATE TABLE AS.
     CREATE TABLE from_exetbl_oss AS SELECT * FROM max_oss_test;
     
     -- Query the new internal table.
     SELECT * FROM from_exetbl_oss;
     
     -- The result shows that all data is copied.
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110

  2. Dahili tablonun şemasını görüntülemek için DESC from_exetbl_oss; komutunu çalıştırın. Komut aşağıdaki çıktıyı döndürür.

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

• Harici bölümlenmiş bir tablodan şema kopyalayarak dahili bir tablo oluşturun. Dahili tablo bölüm özelliklerini içerir.

  1. from_exetbl_like dahili tablosunu oluşturun. OSS harici tablosunu MaxCompute üzerinden sorgulayın. CREATE TABLE LIKE kullanarak dahili bir tablo oluşturun.

     -- Query the OSS external table from MaxCompute.
     SELECT * FROM max_oss_test;
     -- Result
     a    b    c
     101    1    20241108
     102    2    20241109
     103    3    20241110
     
     -- Create an internal table by using CREATE TABLE LIKE.
     CREATE TABLE from_exetbl_like LIKE max_oss_test;
     
     -- Query the new internal table.
     SELECT * FROM from_exetbl_like;
     -- Result: Only the table schema is returned.
     a    b    c

  2. Dahili tablonun şemasını görüntülemek için DESC from_exetbl_like; komutunu çalıştırın. Komut aşağıdaki çıktıyı döndürür.

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

Delta tablosu

• Delta tablosu oluşturun.

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

• Delta tablosu oluşturun ve temel tablo özelliklerini ayarlayın.

  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;

Diğer yöntemler

-- Create a new table named sale_detail_ctas1, copy the data from sale_detail to it, and set a lifecycle.
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;

-- Create a new table named mc_oss_extable_orc_like that has the same schema as the external table 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     |       |                                             |
+------------------------------------------------------------------------------------+

Yeni veri türleri

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;