Place create table and table name in the same line

[Tasselmi]

Describe the Feature
hi bro.
for hive sql, can we put create table and table name in the same line? currently there is a new line after CREATE TABLE IF NOT EXISTS , so the table name will be in another line

Why do you want this feature?
to make the sql code more compact

currently there will be 2 layers of indent

CREATE TABLE IF NOT EXISTS
    `{dbPrefix}wms_mart`.`{tbPrefix}ods_wms_txn_log_details_ri` (
        `kfk_topic` STRING COMMENT 'kafka主题'
      , `kfk_partition` INT COMMENT 'kafka分区号'
      , `kfk_offset` BIGINT COMMENT 'kafka偏移量'
      , `kfk_timestamp` BIGINT COMMENT 'kafka时间戳'
      , `is_valid_msg` INT COMMENT 'message是不是合法的JSON字符串，0非法，1合法'
      , `full_message` STRING COMMENT 'kafka完整消息(当json不合法时，不丢失数据，完整保留在此字段中)'
    ) COMMENT 'wms三级账日志数据' PARTITIONED BY (
        tz_type STRING COMMENT 'local'
      , grass_region STRING COMMENT '国家或地区，根据cid/region_id字段而来，三级账分区都放在sg'
      , grass_date STRING COMMENT 'local日期，格式为yyyy-mm-dd 基于kafka接收时间解析而来'
      , grass_hour STRING COMMENT '00-23小时，前闭后开 基于kafka接收时间解析而来'
    )
ROW FORMAT
    SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'
STORED AS
    TEXTFILE
;

[nene]

Hi. This is something that I've been wanting to change for a long time, but unfortunately the implementation of this touches on the core of how the SQL gets parsed inside formatter, so it's not that simple to change. I've been slowly moving towards this goal, but hard to tell when we get there.

[Tasselmi]

Hi. This is something that I've been wanting to change for a long time, but unfortunately the implementation of this touches on the core of how the SQL gets parsed inside formatter, so it's not that simple to change. I've been slowly moving towards this goal, but hard to tell when we get there.

ok
thank you very much.
It is not a very big problem currently, I can adjust it by hand one by one.

[karlhorky]

Copying over the simpler example from @kiwicopple in #570 for additional illustration of the problem:

---

Input data

Which SQL and options did you provide as input?

create table profiles (
  id bigint primary key,
  name text not null,
  dob date
);

Expected Output

create table profiles (
  id bigint primary key,
  name text not null,
  dob date
);

Actual Output

create table
  profiles (
    id bigint primary key,
    name text not null,
    dob date
  );

[karlhorky]

unfortunately the implementation of this touches on the core of how the SQL gets parsed inside formatter, so it's not that simple to change

I've been slowly moving towards this goal, but hard to tell when we get there.

@nene could you expand with a few paragraphs about the details here? (which files/methods are interesting, where are the challenges and potentially problematic areas)

Maybe a community member would be open to diving in and implementing this.

[nene]

Come to think of this again. I think there's actually a rather trivial fix for this. Just need to move 'CREATE TABLE' pattern from reservedClauses to onelineClauses.

[DerTimonius]

Opened a PR (#688) to implement this fix 🙂

[avishj]

Is there a way for this to be applied to WITH clauses as well? @nene

Maybe exposed via a setting?

The output currently is like:

WITH
    UniqueShipments AS (
        SELECT DISTINCT
            amsh.[shipment_id],
            amsh.[sub_id],
            amsh.[shipment_date]
        FROM
            amazon_shipment amsh
    )

WITH UniqueShipments AS (
    SELECT DISTINCT
        amsh.[shipment_id],
        amsh.[sub_id],
        amsh.[shipment_date]
    FROM
        amazon_shipment amsh
)

[nene]

The problem with WITH clause is that it can have multiple items, like:

WITH
    foo AS (SELECT ...),
    bar AS (SELECT ...)

If same change were to be implemented in WITH-clause as was done with CREATE TABLE, the above would get formatted as:

WITH foo AS (SELECT ...),
bar AS (SELECT ...)

Ideally the formatter would understand how many items a WITH clause has and adjust the formatting accordingly, but unfortunately SQL Formatter doesn't quite understand much about the context. It pretty much looks at the code one keyword at a time and tries to make the best formatting decisions.

[avishj]

That makes sense, but since I tend to write quite long CTE's, I honestly never minded the issue shown in the second example. But, it does make sense as to why that shouldn't be done, it would be an eye sore for shorter CTE's.