All Products
Search
Document Center

Data Lake Analytics - Deprecated:DLA data types and syntax

Last Updated:May 21, 2020

This topics lists the Hive data types and describes the CREATE EXTERNAL TABLE syntax compatible with Alibaba Cloud Data Lake Analytics (DLA).

Hive data types

Hive data types that are compatible with DLA can be divided into five major types:

1. Values

  • TINYINT. 1 byte, integer data, with a signed range of -128 to 127.
  • SMALLINT. 2 bytes, integer data, with a signed range of -32,768 to 32,767.
  • INT/INTEGER. 4 bytes, integer data, with a signed range of -2,147,483,648 to 2,147,483,647.
  • BIGINT. 8 bytes, integer data, with a signed range of -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807
  • FLOAT. 4 bytes, single-precision floating point numbers.
  • DOUBLE. 8 bytes, double-precision floating point numbers.
  • DECIMAL(p, s) . Fixed precision and scale numbers.

2. Characters

  • STRING. Stores large variable-length text characters that can be enclosed in single or double quotation marks.
  • VARCHAR. Stores variable-length text, ranging from 1 to 65,355.
  • CHAR. Stores fixed-length characters.

3. Date and time

  • TIMESTAMP. A UNIX nanosecond timestamp.
  • DATE. Used to store date data in the UNIX format, such as YYYY-­MM-­DD.
  • INTERVAL. Used to store fixed-length characters.

4. Boolean

  • BOOLEAN. Matches only two values: true and false.

5. Collection

  • ARRAY. Accesses elements starting with 0.
  • MAP. A set of key-value tuples, in which the key can only be a basic type, and the value can be any type. You can access elements in the maps using [], for example, map[‘key1’].
  • STRUCT . Object-like. Where a data type is defined as colum1 STRUCT{var1 STRING,var2 int}, use colum1.var2 to access.

CREATE EXTERNAL TABLE Syntax

CREATE EXTERNAL TABLE syntax compatible with DLA can be demonstrated by the following:

  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

CREATE EXTERNAL TABLE syntax can be used to create both partitioned and non-partitioned tables. The syntax is demonstrated as follows.

  1. Create a non-partitioned table.

    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';
  2. Create a partitioned table.

    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. date_string_col STRING COMMENT 'default',
    6. string_col STRING COMMENT 'default',
    7. timestamp_col TIMESTAMP COMMENT 'default'
    8. )
    9. PARTITIONED BY (year INT COMMENT 'default', month INT COMMENT 'default')
    10. ROW FORMAT DELIMITED
    11. FIELDS TERMINATED BY ','
    12. ESCAPED BY '\\'
    13. STORED AS TEXTFILE
    14. LOCATION 'oss://your-bucket/path/to/primitives_text_p';

For more information about CREATE EXTERNAL TABLE, see Create table.