Hive - create table - missing EOF at 'SORT' near ')'

Question

I have this error when i try to execute the query (CREATE) below. Any suggest?

ERROR: -------------------------------------------------------------------------

[sshexec] 2022-08-22 11:48:36: >> Query in errore:  
  [sshexec]  CREATE EXTERNAL TABLE OVCDX
  [sshexec]  (
  [sshexec]      `AAA...
  [sshexec] 2022-08-22 11:48:36:  Errore nel parsare la query:line 26:0 missing EOF at 'SORT' near ')'

---

QUERY: -------------------------------------------------------------------------

CREATE EXTERNAL TABLE OVCDX
(
    `AAA`       STRING          COMMENT '<KEY>|XA',
    `BBB`       STRING          COMMENT '<KEY>|XB',
    `CCC`       STRING          COMMENT '<KEY>|XC',
    `DDD`       STRING          COMMENT '<KEY>|XD',
    `EEE`       STRING          COMMENT '<KEY>|XE',
    `FFF`       BIGINT          COMMENT 'XF'
)
PARTITIONED BY (
    `GGG`       STRING       COMMENT '<KEY>|XG',
    `HHH`       STRING       COMMENT '<KEY>|XH',
    `III`       STRING       COMMENT '<KEY>|XI',
    `JJJ`       STRING       COMMENT '<KEY>|XJ',
    `KKK`       STRING       COMMENT '<KEY>|XK',
    `LLL`       STRING       COMMENT '<KEY>|XL'
)
SORT BY (
    `AAA`,
    `BBB`,
    `CCC`,
    `DDD`,
    `EEE`                       
)
COMMENT 'X_ABCDE'
STORED AS PARQUET;

---

Thx, Luca

Answer 1

Are you sure you are using the correct syntax in your CREATE statement? According to the wiki, the SORT BY has to be used together with the CLUSTERED BY, in case it's in a CREATE TABLE statement:

Hive Wiki

CREATE [TEMPORARY] [EXTERNAL] TABLE [IF NOT EXISTS] [db_name.]table_name    -- (Note: TEMPORARY available in Hive 0.14.0 and later)
  [(col_name data_type [column_constraint_specification] [COMMENT col_comment], ... [constraint_specification])]
  [COMMENT table_comment]
  [PARTITIONED BY (col_name data_type [COMMENT col_comment], ...)]
  [CLUSTERED BY (col_name, col_name, ...) [SORTED BY (col_name [ASC|DESC], ...)] INTO num_buckets BUCKETS]
  [SKEWED BY (col_name, col_name, ...)                  -- (Note: Available in Hive 0.10.0 and later)]
     ON ((col_value, col_value, ...), (col_value, col_value, ...), ...)
     [STORED AS DIRECTORIES]