全部产品
云市场

CREATE TABLE

更新时间:2019-03-17 17:16:38

创建表

语法

支持OSS数据源文件的建表语法,兼容Hive的CREATE EXTERNAL TABLE建表语法:

  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. | DECIMAL
  27. | DECIMAL(precision, scale)
  28. | DATE
  29. | VARCHAR
  30. | CHAR
  31. array_type
  32. : ARRAY < data_type >
  33. map_type
  34. : MAP < primitive_type, data_type >
  35. struct_type
  36. : STRUCT < col_name : data_type [COMMENT col_comment], ...>
  37. union_type
  38. : UNIONTYPE < data_type, data_type, ... >
  39. row_format
  40. : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
  41. [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
  42. [NULL DEFINED AS char]
  43. | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
  44. file_format:
  45. : SEQUENCEFILE
  46. | TEXTFILE
  47. | RCFILE
  48. | ORC
  49. | PARQUET
  50. | AVRO
  51. | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
  52. oss_path:
  53. 指向数据文件所在的OSS bucket的目录,而不是数据文件本身!例如:oss://test-bucket-julian/test_csv是数据目录,而不是数据文件本身,数据文件在test_csv目录下面,不要将文件直接放在root bucket下,Open Analytics建的表会识别oss://test-bucket-julian/test_csv目前下的所有文件(不支持递归识别内部文件夹下的文件)。
  54. 示例:'oss://your-bucket/parent/dir/to/data'

示例

  • 创建不带分区的表
  1. ```
  2. CREATE EXTERNAL TABLE nation_text_string (
  3. N_NATIONKEY INT COMMENT 'column N_NATIONKEY',
  4. N_NAME STRING,
  5. N_REGIONKEY INT,
  6. N_COMMENT STRING
  7. )
  8. ROW FORMAT DELIMITED FIELDS TERMINATED BY '|'
  9. STORED AS TEXTFILE LOCATION 'oss://your-bucket/path/to/nation_text';
  10. ```
  • 创建带分区的表

    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. PARTITIONED BY (year INT COMMENT 'default', month INT COMMENT 'default')
    14. ROW FORMAT DELIMITED
    15. FIELDS TERMINATED BY ','
    16. ESCAPED BY '\\'
    17. STORED AS TEXTFILE
    18. LOCATION 'oss://your-bucket/path/to/primitives_text_p';

    分区表创建成功后执行以下SQL,刷新系统中对应的元数据信息。

    1. MSCK REPAIR TABLE