All Products
Search

This Product

    Data Lake Analytics - Deprecated:CREATE TABLE

    Document Center

    Data Lake Analytics - Deprecated:CREATE TABLE

    Last Updated:Apr 23, 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
    10. data_type
    11.   : primitive_type
    12.   | array_type
    13.   | map_type
    14.   | struct_type
    15.   | union_type
    17. primitive_type
    18.   : TINYINT
    19.   | SMALLINT
    20.   | INT
    21.   | BIGINT
    22.   | BOOLEAN
    23.   | FLOAT
    24.   | DOUBLE
    25.   | STRING
    26.   | BINARY 
    27.   | TIMESTAMP  
    28.   | DECIMAL 
    29.   | DECIMAL(precision, scale)
    30.   | DATE 
    31.   | VARCHAR 
    32.   | CHAR 
    34. array_type
    35.   : ARRAY < data_type >
    37. map_type
    38.   : MAP < primitive_type, data_type >
    40. struct_type
    41.   : STRUCT < col_name : data_type [COMMENT col_comment], ...>
    43. union_type
    44.    : UNIONTYPE < data_type, data_type, ... >
    46. row_format
    47.   : DELIMITED [FIELDS TERMINATED BY char [ESCAPED BY char]] [COLLECTION ITEMS TERMINATED BY char]
    48.         [MAP KEYS TERMINATED BY char] [LINES TERMINATED BY char]
    49.         [NULL DEFINED AS char]
    50.   | SERDE serde_name [WITH SERDEPROPERTIES (property_name=property_value, property_name=property_value, ...)]
    52. file_format:
    53.   : SEQUENCEFILE
    54.   | TEXTFILE 
    55.   | RCFILE 
    56.   | ORC   
    57.   | PARQUET
    58.   | AVRO   
    59.   | INPUTFORMAT input_format_classname OUTPUTFORMAT output_format_classname
    61. oss_path:
    62.     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.
    63.     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