Aqua Data Studio 17.0.0-rc-20
Build #: 46507
Built on: 2015-Oct-20 01:36:02 PM
Database: Hive 1.2.1
Hortonworks HDP 2.3.0 - Hive 1.2.1
Drivers Link- Hive 1.2.1
[ACID Transactions enabled]
Connection Type: HiveServer 2
Distribution: Hortonworks
Issue: Hive- Quote identifier issue -Wrong script generated by-Table- Script Object To Window As->Create
Steps:
1. Create a Table using below script in Query Analyzer.
CREATE TABLE table2(a string, b string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
"quoteChar" = "'",
"separatorChar" = "\t",
"escapeChar" = "\\"
)
STORED AS TEXTFILE
Go
Please refer sqlscript
or Create Table Visual Editor-
a]Right click on Table, Select Create Table
b]In Create Table window- Enter Table Name- table2
c]Enter column names as a , b with both string data type
d] Click on Storage Tab,
Change Storage Format: Row Format SerDe
SerDe Name: org.apache.hadoop.hive.serde2.OpenCSVSerde
SerDe Properties: "quoteChar" = "'", "separatorChar" = "\t", "escapeChar" = "\\"
- Note: quotechar value is "'" [ single quote is inside the double quotes]
e] Click on Preview SQL Tab, observe the script generated
- Here quoteChar value ' is inside double quotes.
Preview SQL:
CREATE TABLE `default`.`table2` (
`a` string,
`b` string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ("quoteChar" = "'", "separatorChar" = "\t", "escapeChar" = "\\")
STORED AS TEXTFILE
f] Click on 'Create' button. Table is created
2. Right click on table, select 'Script Object To Window As->Create'
Script is generated in Query Analyzer, To create another table- change Table name -table4
and Execute this query.
It displays error message, because there is difference in generated script, it doesn't match with 'Preview SQL' script.
Please refer screenshot: screenshot1.png
CREATE TABLE default.table4 (
a string COMMENT 'from deserializer' ,
b string COMMENT 'from deserializer' )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ('quoteChar'=''' ,'escapeChar'='\\', ,'separatorChar'='\t')
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
GO
@Dev:
1] In the generated Create Table script - quoteChar is - ' ' ' [single quote inside single quotes]
Expected: [single quote should be inside double quotes " ' " ]
2] Quote identifier option setting [File->Options->Scripts-> Apache Hive ] doesn't affect the behaviour for this scenario.
SQL Log:
Statement:
CREATE TABLE default.table4 (
a string COMMENT 'from deserializer' ,
b string COMMENT 'from deserializer' )
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde' WITH SERDEPROPERTIES ('escapeChar'='\\', 'quoteChar'=''', 'separatorChar'='\t')
STORED AS INPUTFORMAT 'org.apache.hadoop.mapred.TextInputFormat' OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
GO
Exception:
org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 4:121 mismatched input 'separatorChar' expecting ) near '''', '' in table properties
at org.apache.hive.jdbc.Utils.verifySuccess(Utils.java:256)
at org.apache.hive.jdbc.Utils.verifySuccessWithInfo(Utils.java:242)
at org.apache.hive.jdbc.HiveStatement.execute(HiveStatement.java:254)
at com.aquafold.aquacore.open.rdbms.drivers.hive.C⠌⢳⢾ꉲ.execute(Unknown Source)
at \\.\\.\\हिñçêČάй語简�?한\\.Tꌯᢥꐖꄇelsebreak.execute(Unknown Source)
at \\.\\.\\हिñçêČάй語简�?한\\.R7ꑻꏑ̷public6.a(Unknown Source)
at com.aquafold.datastudio.queryanalyzer.Cꏀ̈́ꁋͪint.run(Unknown Source)
Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: ParseException line 4:121 mismatched input 'separatorChar' expecting ) near '''', '' in table properties
at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:315)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:112)
at org.apache.hive.service.cli.operation.SQLOperation.runInternal(SQLOperation.java:181)
at org.apache.hive.service.cli.operation.Operation.run(Operation.java:257)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementInternal(HiveSessionImpl.java:388)
at org.apache.hive.service.cli.session.HiveSessionImpl.executeStatementAsync(HiveSessionImpl.java:375)
at org.apache.hive.service.cli.CLIService.executeStatementAsync(CLIService.java:274)
at org.apache.hive.service.cli.thrift.ThriftCLIService.ExecuteStatement(ThriftCLIService.java:486)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1313)
at org.apache.hive.service.cli.thrift.TCLIService$Processor$ExecuteStatement.getResult(TCLIService.java:1298)
at org.apache.thrift.ProcessFunction.process(ProcessFunction.java:39)
at org.apache.thrift.TBaseProcessor.process(TBaseProcessor.java:39)
at org.apache.hive.service.auth.TSetIpAddressProcessor.process(TSetIpAddressProcessor.java:56)
at org.apache.thrift.server.TThreadPoolServer$WorkerProcess.run(TThreadPoolServer.java:285)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1145)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:615)
at java.lang.Thread.run(Thread.java:745)
Caused by: java.lang.RuntimeException: org.apache.hadoop.hive.ql.parse.ParseException:line 4:121 mismatched input 'separatorChar' expecting ) near '''', '' in table properties
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:205)
at org.apache.hadoop.hive.ql.parse.ParseDriver.parse(ParseDriver.java:166)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:396)
at org.apache.hadoop.hive.ql.Driver.compile(Driver.java:308)
at org.apache.hadoop.hive.ql.Driver.compileInternal(Driver.java:1122)
at org.apache.hadoop.hive.ql.Driver.compileAndRespond(Driver.java:1116)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:110)
... 15 more
Build: ADS17.0.0-rc-22 - Verified
Build: ADS18.0.0-rc-6 - Reopened
The changes are not fixed in ADS18 build.
Please refer screenshot: Reopened-ADS18-dev-6.png