[HIVE-22723] SHOW CREATE TABLE omits backtics, results in syntax error

• Type: Bug
• Status: Resolved
• Priority: Minor
• Resolution: Fixed
• Component/s: Hive

Environment

Run SHOW CREATE TABLE via AWS Athena

• Assignee: Miklos Gergely
• Reporter: Alex Yankelevich
• Created: 13/Jan/20 22:49
• Updated: 28/Jan/20 12:00
• Resolved: 28/Jan/20 12:00

Description

CREATE EXTERNAL TABLE `alerts1`(
 `_id` string COMMENT 'from deserializer', `org` string COMMENT 'from deserializer', `collector` string COMMENT 'from deserializer', `source` string COMMENT 'from deserializer', `entitytype` string COMMENT 'from deserializer', `entity` string COMMENT 'from deserializer', `rule` string COMMENT 'from deserializer', `findingdescid` string COMMENT 'from deserializer',

 `details` struct<parameters:struct<host_version:string,build:struct<__numberint:string>>,findingtype:string,producttype:string,entityname:string,extra:struct<version:string,build:string,updaterev:string>,entityparams:struct<deployment:array<string>,version:string,build:string>> COMMENT 'from deserializer', `seenat` array<struct<__numberlong:string>> COMMENT 'from deserializer', `execution` array<struct<__numberlong:string>> COMMENT 'from deserializer', `lastmodified` struct<__numberint:string> COMMENT 'from deserializer'

) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' WITH SERDEPROPERTIES ( 'paths'='_id,collector,details,entity,entityType,execution,findingDescId,lastModified,org,rule,seenAt,source') STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat' LOCATION 's3://redshift-poc-ay/alerts' TBLPROPERTIES ( 'CrawlerSchemaDeserializerVersion'='1.0', 'CrawlerSchemaSerializerVersion'='1.0', 'UPDATED_BY_CRAWLER'='redshift-poc-alerts-ay', 'averageRecordSize'='9619', 'classification'='json', 'compressionType'='none', 'objectCount'='1', 'recordCount'='21028', 'sizeKey'='202270843', 'transient_lastDdlTime'='1578946186', 'typeOfData'='file')

    __numberint, __numberlong columns are not generated enclosed in backticks even though they were explicitly created with them.  Therefore, the generated DDL is syntactically incorrect.