全部产品

CREATE TABLE

更新时间:2020-06-15 11:16:09

CREATE TABLE

DLA创建表的语法是兼容的Hive的风格,SQL的语法如下:

  1. CREATE EXTERNAL TABLE [IF NOT EXISTS] [db_name.]table_name
  2. [(col_name data_type [COMMENT col_comment], ... [constraint_specification])]
  3. [COMMENT table_comment]
  4. [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  5. [ROW FORMAT row_format]
  6. [STORE AS file_format]
  7. | STORED BY 'storage.handler.class.name' [WITH SERDEPROPERTIES (...)]
  8. LOCATION oss_path
  9. data_type
  10. : primitive_type
  11. | array_type
  12. | map_type
  13. | struct_type
  14. | union_type
  15. primitive_type
  16. : TINYINT
  17. | SMALLINT
  18. | INT
  19. | BIGINT
  20. | BOOLEAN
  21. | FLOAT
  22. | DOUBLE
  23. | STRING
  24. | BINARY
  25. | TIMESTAMP
  26. | DATE
  27. | VARCHAR
  28. | CHAR
  29. array_type
  30. : ARRAY < data_type >
  31. map_type
  32. : MAP < primitive_type, data_type >
  33. struct_type
  34. : STRUCT < col_name : data_type [COMMENT col_comment], ...>
  35. union_type
  36. : UNIONTYPE < data_type, data_type, ... >
  37. row_format
  38. : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
  39. [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  40. [NULL DEFINED AS char]
  41. | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
  42. file_format:
  43. : SEQUENCEFILE
  44. | TEXTFILE
  45. | RCFILE
  46. | ORC
  47. | PARQUET
  48. | AVRO
  49. | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
  50. oss_path:
  51. 指向数据文件所在的OSS bucket的目录,而不是数据文件本身!例如:oss://test-bucket-julian/test_csv是数据目录,而不是数据文件本身,数据文件在test_csv目录下面,不要将文件直接放在root bucket下,Open Analytics建的表会识别oss://test-bucket-julian/test_csv目前下的所有文件(不支持递归识别内部文件夹下的文件)。
  52. 示例:'oss://your-bucket/parent/dir/to/data'

示例

OSS建表语句

非分区表:

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

分区表:

  1. CREATE EXTERNAL TABLE primitives_text_p (
  2. id INT COMMENT 'default',
  3. bool_col BOOLEAN COMMENT 'default',
  4. tinyint_col TINYINT COMMENT 'default',
  5. smallint_col SMALLINT COMMENT 'default',
  6. int_col INT COMMENT 'default',
  7. bigint_col BIGINT COMMENT 'default',
  8. float_col FLOAT COMMENT 'default',
  9. double_col DOUBLE COMMENT 'default',
  10. date_string_col STRING COMMENT 'default',
  11. string_col STRING COMMENT 'default',
  12. timestamp_col TIMESTAMP COMMENT 'default'
  13. )
  14. PARTITIONED BY (
  15. year INT COMMENT 'default',
  16. month INT COMMENT 'default'
  17. )
  18. ROW FORMAT DELIMITED
  19. FIELDS TERMINATED BY ','
  20. ESCAPED BY '\\'
  21. STORED AS TEXTFILE
  22. LOCATION 'oss://your-bucket/path/to/primitives_text_p';

MySQL/SQLServer/Postgresql/Oracle建表语句

MySQL:

  1. create table person (
  2. id int,
  3. name varchar(1023),
  4. age int
  5. );

当底层MySQL的表与DLA中的表名不一样的时候:

  1. create external table person1 (
  2. id int,
  3. name varchar(1023),
  4. age int
  5. ) tblproperties(
  6. table_mapping = 'person'
  7. );

SQLServer/PostgreSQL:

  1. CREATE EXTERNAL TABLE person (
  2. id int,
  3. name varchar(1023),
  4. age int
  5. ) tblproperties(
  6. table_mapping = 'public.person'
  7. );

注意这里 table_mapping 里面加了个 public 的前缀,这是SQLServer/PostgreSQL里面的schema名称。而MySQL没有这一层。

OTS建表语句

  1. CREATE EXTERNAL TABLE `nation` (
  2. `N_NATIONKEY` bigint not NULL ,
  3. `N_COMMENT` varchar(100) NULL ,
  4. `N_NAME` varchar(100) NULL ,
  5. `N_REGIONKEY` bigint NULL ,
  6. PRIMARY KEY (`N_NATIONKEY`)
  7. );
  8. > PRIMARY KEY (`N_NATIONKEY`) ---- PRIMARY KEY这里务必与ots中的主键顺序相同;名称的话也要对应

ADB2.0建表语句

  1. CREATE EXTERNAL TABLE etl_ads_dimension_table (
  2. col1 INT,
  3. col2 VARCHAR(200),
  4. col3 INT,
  5. col4 VARCHAR(200),
  6. primary key (col1)
  7. );

ADB3.0建表语句

  1. CREATE EXTERNAL TABLE person (
  2. id int,
  3. name string,
  4. age int
  5. );

ADBPG建表语句

  1. create external table products (
  2. product_no int,
  3. name varchar(1023),
  4. price double
  5. ) tblproperties(
  6. table_mapping = 'schema_name.adbpg_tablename'
  7. );

table_mapping 里面需要加上 adbpg 对应数据库里面的 schema 名称以及对应的表名。

MongoDB建表语句

  1. CREATE EXTERNAL TABLE person (
  2. id int,
  3. name string,
  4. age int
  5. );

Redis建表语句

  1. CREATE EXTERNAL TABLE dla_person (
  2. id int,
  3. name varchar,
  4. age int
  5. ) TBLPROPERTIES (
  6. COLUMN_MAPPING = 'id,2;name,1;age,0',
  7. TABLE_MAPPING = 'world_',
  8. FORMAT = 'csv'
  9. );
  • 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建表语句

  1. CREATE EXTERNAL TABLE `product_info` (
  2. `productname` string NULL COMMENT '',
  3. `annual_rate` string NULL COMMENT '',
  4. `describe` string NULL COMMENT ''
  5. )
  6. TBLPROPERTIES (
  7. COLUMN_MAPPING = 'productname,productName; '
  8. );
  • COLUMN_MAPPING: 由于DLA中字段名称是不区分大小写的,所以如果ElasticSearch中的字段名称有大写字母,需要在COLUMN_MAPPING中增加映射。

Druid建表语句

  1. CREATE EXTERNAL TABLE `wikipedia` (
  2. `__time` TIMESTAMP NULL COMMENT '',
  3. `added` BIGINT NULL COMMENT '',
  4. `channel` string NULL COMMENT '',
  5. `cityname` string NULL COMMENT '',
  6. `comment` string NULL COMMENT '',
  7. `countryisocode` string NULL COMMENT '',
  8. `countryname` string NULL COMMENT '',
  9. `deleted` BIGINT NULL COMMENT '',
  10. `delta` BIGINT NULL COMMENT '',
  11. `isanonymous` string NULL COMMENT '',
  12. `isminor` string NULL COMMENT '',
  13. `isnew` string NULL COMMENT '',
  14. `isrobot` string NULL COMMENT '',
  15. `isunpatrolled` string NULL COMMENT '',
  16. `namespace` string NULL COMMENT '',
  17. `page` string NULL COMMENT '',
  18. `regionisocode` string NULL COMMENT '',
  19. `regionname` string NULL COMMENT '',
  20. `user` string NULL COMMENT ''
  21. );

Table Properties

我们各种不同的表支持不同的table properties,详见:Table Properties