#14810: Complex datatypes : Proper quote identifiers are not getting applied.

dutondeumesh reported 2016-11-03T11:36:18Z · last modified 2017-05-18T18:38:37Z

Complex datatypes : Proper quote identifiers are not getting applied.

Dev	tomconrad
QA	tariqrahiman

Priority	Low
Complexity	Unknown

Component	Hive
Version	19.0

ADS Version: 18.0.0-alpha-14

Build #: 51724

Build Date: 2016-Nov-02 07:09:21 PM

Operating Environment: Linux (4.4.0-45-generic, amd64) / UTF-8 / en / US / Oracle Corporation 1.8.0_102-b14

Memory: Max=704,643,072; Total=348,651,520; Free=160,567,200; CPUs=8

Steps : Connect to Hive DB (I have used cloudera distribution - CDH 5.7)

Note : make sure that auto-completion is enable

1 - Go to File -> Options -> Scripts -> expand Scripts -> Select Apache Hive option

2 - Select backticks option for Quote Identifier,Quote Identifier for Table Completion and Quote Identifier for Column Completion options.

3 - Open Query analyzer and run below create table script which has complex datatypes.

CREATE TABLE `default`.`table4` (

`full_name` array<string>,

`deductions` map<string, bigint>,

`address` struct<flat_no:int,landmark:array<string>>)

COMMENT 'table contains complex data type'

ROW FORMAT DELIMITED FIELDS TERMINATED BY ',' COLLECTION ITEMS TERMINATED BY '#' MAP KEYS TERMINATED BY '$'

STORED AS TEXTFILE

TBLPROPERTIES ('COLUMN_STATS_ACCURATE'='{\"BASIC_STATS\":\"true\"}', 'numFiles'='0', 'numRows'='0', 'rawDataSize'='0', 'totalSize'='0')

GO

4 - Insert data into the table(please see attached table2.txt)

5 - Now write a query, select * from table4 where -> auto completion pop-up will appear -> select address.flat_no column

Actual Result : backticks applied to entire column i.e `address.flat_no`. Query not works if backticks applied to entire column.

Expected Result : backticks should apply separately to main column and sub column i.e `address`.`flat_no`. Query works fine.

Please note that below quote identifies will not return result in hive

select * from `table4` where 'address'.'flat_no' = 206

select * from `table4` where [address].[flat_no] = 206

select * from `table4` where "address"."flat_no" = 206

ADS	Observation
v15	Y
v16	Y
v17	Y
Y: Issue is observed.

7 attachments

table2.txt

2016-11-03T11:36:18Z

186 B
hivedb.png

2016-11-03T11:36:18Z

294 KB
HiveAutoComplex.png

2017-05-02T20:54:06Z

136 KB
HiveAutoDotIn Name.png

2017-05-02T20:54:30Z

161 KB
14810_sparkSql162.png

2017-05-03T12:04:18Z

104 KB
14810_clouderaHive.png

2017-05-03T12:04:18Z

83 KB
dotincolumnname.png

2017-05-03T12:06:09Z

110 KB

All Comments (3) Change History

tomconrad 2017-05-02T21:05:44Z

svn# 54188 - Added quotes to the children of complex statements like struct.

Hi Umesh, Please test this across different connection types. This should work for children of complex types. See attachment. Realize that some distributions like Cloudera allow a dot in a column name. See attached. In this case the the whole name should be quoted. Let me know if you have questions. Thanks, Tom

dutondeumesh 2017-05-03T12:06:26Z

Verified in ADS 19.0.0-dev-38
Cloudera5.8 - Hive[default ads drivers 1.1] - screenshot
hortonworks2.5 - Sparksql1.6[default ads drivers 1.1] - screenshot

Cloudera allow a dot in a column name but select query not works in ads as well as beeline - screenshot

tariqrahiman 2017-05-18T18:38:37Z

Verified in ADS v19.0.0-alpha-8

Search Tips

Aqua Data Studio / nhilam

Complex datatypes : Proper quote identifiers are not getting applied.

7 attachments

Issue #14810

Completion

Search Tips

Aqua Data Studio / nhilam

Title

Complex datatypes : Proper quote identifiers are not getting applied.

7 attachments

Issue #14810

Completion