CREATE TABLE - Data Lake Analytics - Deprecated

DLA建立表的文法相容Hive的風格，SQL的文法結構如下：

CREATE EXTERNAL TABLE [IF NOT EXISTS] [db_name.]table_name
    [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
    [COMMENT table_comment]
    [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
    [ROW FORMAT row_format]
    [STORE AS file_format]
        | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
    LOCATION oss_path
data_type
  : primitive_type
  | array_type
  | map_type
  | struct_type
  | union_type
primitive_type
  : TINYINT
  | SMALLINT
  | INT
  | BIGINT
  | BOOLEAN
  | FLOAT
  | DOUBLE
  | STRING
  | BINARY
  | TIMESTAMP
  | DATE
  | VARCHAR
  | CHAR
array_type
  : ARRAY < data_type >
map_type
  : MAP < primitive_type, data_type >
struct_type
  : STRUCT < col_name : data_type [COMMENT col_comment], ...>
union_type
   : UNIONTYPE < data_type, data_type, ... >
row_format
  : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
        [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
        [NULL DEFINED AS char]
  | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
file_format:
  : SEQUENCEFILE
  | TEXTFILE
  | RCFILE
  | ORC
  | PARQUET
  | AVRO
  | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
oss_path:
    指向資料檔案所在的OSS bucket的目錄，而不是資料檔案本身！例如：oss://test-bucket-julian/test_csv是資料目錄，而不是資料檔案本身，資料檔案在test_csv目錄下面，不要將檔案直接放在root bucket下，Open Analytics建的表會識別oss://test-bucket-julian/test_csv目前下的所有檔案（不支援遞迴識別內部檔案夾下的檔案）。
    樣本：'oss://your-bucket/parent/dir/to/data'

OSS建表示例

非分區表：

CREATE EXTERNAL TABLE nation_text_string (
    N_NATIONKEY INT COMMENT 'column N_NATIONKEY',
    N_NAME STRING,
    N_REGIONKEY INT,
    N_COMMENT STRING
)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
STORED AS TEXTFILE LOCATION 'oss://your-bucket/path/to/nation_text/';

分區表：

 CREATE EXTERNAL TABLE primitives_text_p (
    id INT COMMENT 'default',
    bool_col BOOLEAN COMMENT 'default',
    tinyint_col TINYINT COMMENT 'default',
    smallint_col SMALLINT COMMENT 'default',
    int_col INT COMMENT 'default',
    bigint_col BIGINT COMMENT 'default',
    float_col FLOAT COMMENT 'default',
    double_col DOUBLE COMMENT 'default',
    date_string_col STRING COMMENT 'default',
    string_col STRING COMMENT 'default',
    timestamp_col TIMESTAMP COMMENT 'default'
)
PARTITIONED BY (
    year INT COMMENT 'default',
    month INT COMMENT 'default'
)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
ESCAPED BY '\\'
STORED AS TEXTFILE
LOCATION 'oss://your-bucket/path/to/primitives_text_p';

MySQL/SQLServer/Postgresql/Oracle建表示例

MySQL：

create table person (
    id int,
    name varchar(1023),
    age int
);

當底層MySQL的表與DLA中的表名不一樣的時候樣本如下：

create external table person1 (
    id int,
    name varchar(1023),
    age int
) tblproperties(
  table_mapping = 'person'
);

SQLServer/PostgreSQL：

CREATE EXTERNAL TABLE person (
       id int,
       name varchar(1023),
       age int
) tblproperties(
  table_mapping = 'public.person'
);

說明 table_mapping裡面加了個public的首碼，這是SQLServer/PostgreSQL裡面的schema名稱。而MySQL沒有這一層。

OTS建表示例

CREATE EXTERNAL TABLE `nation` (
  `N_NATIONKEY` bigint not NULL ,
  `N_COMMENT` varchar(100) NULL ,
  `N_NAME` varchar(100) NULL ,
  `N_REGIONKEY` bigint NULL ,
  PRIMARY KEY (`N_NATIONKEY`)
);
> PRIMARY KEY (`N_NATIONKEY`)  #PRIMARY KEY這裡務必與ots中的主鍵順序相同，名稱也要對應。

ADB2.0建表示例

CREATE EXTERNAL TABLE etl_ads_dimension_table (
  col1 INT,
  col2 VARCHAR(200),
  col3 INT,
  col4 VARCHAR(200),
  primary key (col1)
);

ADB3.0建表示例

CREATE EXTERNAL TABLE person (
  id int,
  name string,
  age int
);

ADBPG建表示例

create external table products (
       product_no int,
       name varchar(1023),
       price double
) tblproperties(
  table_mapping = 'schema_name.adbpg_tablename' #table_mapping裡面需要加上adbpg對應資料庫裡面的schema名稱以及對應的表名。
);

MongoDB建表示例

CREATE EXTERNAL TABLE person (
  id int,
  name string,
  age int
);

Redis建表示例

CREATE EXTERNAL TABLE dla_person (
    id int,
    name varchar,
    age int
) TBLPROPERTIES (
    COLUMN_MAPPING = 'id,2;name,1;age,0',
    TABLE_MAPPING = 'world_',
    FORMAT = 'csv'
);

TABLE_MAPPING：使DLA層面的表名映射到底層Redis裡面指定模式的一組key。
FORMAT：這裡指定Redis裡面資料的格式，目前支援csv，json 兩種格式。
COLUMN_MAPPING：把DLA層面的列映射到底層的資料上，由於Redis底層沒有column的概念，因此具體映射的方法根據format的不同而不同，比如這裡的CSV, 我們知道CSV的資料被解析之後會形成一個string數組，對應的column_mapping就映射到底層這個數組的index(下標)。比如這裡把ID映射到下標2，把name映射到下標1等等。

Elasticsearch建表示例

 CREATE EXTERNAL TABLE `product_info` (
 `productname` string NULL COMMENT '',
 `annual_rate` string NULL COMMENT '',
 `describe` string NULL COMMENT ''
 )
 TBLPROPERTIES (
 COLUMN_MAPPING = 'productname,productName; '  #由於DLA中欄位名稱是不區分大小寫，所以如果Elasticsearch中的欄位名稱有大寫字母，需要在COLUMN_MAPPING中增加映射。
 );

Druid建表示例

CREATE EXTERNAL TABLE `wikipedia` (
 `__time` TIMESTAMP NULL COMMENT '',
 `added` BIGINT NULL COMMENT '',
 `channel` string NULL COMMENT '',
 `cityname` string NULL COMMENT '',
 `comment` string NULL COMMENT '',
 `countryisocode` string NULL COMMENT '',
 `countryname` string NULL COMMENT '',
 `deleted` BIGINT NULL COMMENT '',
 `delta` BIGINT NULL COMMENT '',
 `isanonymous` string NULL COMMENT '',
 `isminor` string NULL COMMENT '',
 `isnew` string NULL COMMENT '',
 `isrobot` string NULL COMMENT '',
 `isunpatrolled` string NULL COMMENT '',
 `namespace` string NULL COMMENT '',
 `page` string NULL COMMENT '',
 `regionisocode` string NULL COMMENT '',
 `regionname` string NULL COMMENT '',
 `user` string NULL COMMENT ''
);

Table Properties

不同的表支援不同的table properties，具體請參考Table Properties。