sql-formatter-org / sql-formatter

A whitespace formatter for different query languages
https://sql-formatter-org.github.io/sql-formatter/
MIT License
2.37k stars 405 forks source link

Formatter doesn't recognize Hive CREATE TABLE modifiers #797

Open melin opened 2 weeks ago

melin commented 2 weeks ago

Input data

Which SQL and options did you provide as input?


CREATE TABLE k8s_demos.dd_dd_dd 
(id string COMMENT 'd', name string COMMENT 'name') 
using paimon COMMENT '122' TBLPROPERTIES (
  'compression' = 'ZSTD',
  'bizOwner' = 'huaixin',
  'dataLevel' = 'P3',
  'dwClassification' = '',
  'subject1' = '',
  'subject2' = '',
  'primary-key' = 'id'
) lifeCycle 122;

Expected Output


CREATE TABLE k8s_demos.dd_dd_dd (
  id string COMMENT 'd', 
  name string COMMENT 'name'
) 
using paimon 
COMMENT '122' 
TBLPROPERTIES (
  'compression' = 'ZSTD',
  'primary-key' = 'id'
) lifeCycle 122;

Actual Output

CREATE TABLE k8s_demos.dd_dd_dd (id string COMMENT 'd', name string COMMENT 'name') using paimon COMMENT '122' TBLPROPERTIES (
  'compression' = 'ZSTD',
  'bizOwner' = 'huaixin',
  'dataLevel' = 'P3',
  'dwClassification' = '',
  'subject1' = '',
  'subject2' = '',
  'primary-key' = 'id'
) lifeCycle 122;

Usage

nene commented 2 weeks ago

Thanks for reporting. There are a few different problems here.

One is that the expression inside the first pair of parenthesis (the columns list) is pretty short, so it gets formatted on a single line. Though if one would just increase it by one character, it would get formatted as:

CREATE TABLE k8s_demos.dd_dd_dd (
  id string COMMENT 'id',
  name string COMMENT 'name'
) using paimon COMMENT '122' TBLPROPERTIES (
  'compression' = 'ZSTD',
  'bizOwner' = 'huaixin',
  'dataLevel' = 'P3',
  'dwClassification' = '',
  'subject1' = '',
  'subject2' = '',
  'primary-key' = 'id'
) lifeCycle 122;

The second is that the formatter doesn't recognize USING, COMMENT and TBLPROPERTIES as entities to be formatted on separate lines. With USING and TBLPROPERTIES that should be easily achievable. However, with COMMENT it's tricky as the formatter won't be able to distinguish between column comment and table comment. So if one is to be formatted on a separate line, all of them would be, which is probably not what you want.

melin commented 2 weeks ago

The solution possible? Judge in ‘create table (...)’ comment in parentheses does not need to add a new line, other comment add a new line.

nene commented 2 weeks ago

Sorry, but I don't quite understand what you are trying to say.