All Products
Search
Document Center

CREATE TABLE

Last Updated: Apr 24, 2019

Create tables

Syntax

DLA supports table creation syntax based on OSS source files and is compatible with the syntax CREATE EXTERNAL TABLE of Hive.

  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. The directory of the OSS bucket where the data file is located, instead of the data file. For example, oss://test-bucket-julian/test_csv indicates the directory of the data file, rather than the data file. The data file is in the directory test_csv. Do not place the file directly in the root bucket. Tables that you created by using Open Analytics identify all files in oss://test-bucket-julian/test_csv, but do not scan files in child folders.
  54. Example: 'oss://your-bucket/parent/dir/to/data'

Example

  • Create tables without partitions
  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. ```
  • Create tables with partitions

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

    After you create a partitioned table, run the following SQL statement to refresh the metadata:

    1. MSCK REPAIR TABLE