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