AWS Athena case sensitive orc.column.index.access=false?

user_6995114 0
Viewed 836

I've created a table in Athena with below config

CREATE EXTERNAL TABLE `extern` (`FirstName` STRING, `LastName` STRING, `Email` STRING, `Phone` STRING, `AddressLine1` STRING, `City` STRING, `State` STRING, `PostalCode` STRING,  
`time_on_page` DECIMAL(10,3), `page` STRING, `login_time` TIMESTAMP) ROW FORMAT SERDE 'org.apache.hadoop.hive.ql.io.orc.OrcSerde' 
WITH SERDEPROPERTIES ('orc.column.index.access'='false') 
STORED AS INPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcInputFormat' 
OUTPUTFORMAT 'org.apache.hadoop.hive.ql.io.orc.OrcOutputFormat'
LOCATION 's3://location/' tblproperties ("orc.compress"="ZLIB")

and when I query it with select * from extern I get empty values for all columns except for time_on_page, page and login_time. However, this returns data in all columns if I use ('orc.column.index.access'='true'). The schema of the column names in the ORC file are exact with what's defined in the create statement. 

File Version: 0.12 with ORC_135
Rows: 13559
Compression: ZLIB
Compression size: 131072
Type: struct<FirstName:string,LastName:string,Email:string,Phone:string,AddressLine1:string,City:string,State:string,PostalCode:string,time_on_page:decimal(10,3),page:string,login_time:timestamp>

The question is, could it be possible that when orc.column.index.access=false the engine tries to read column names with case sensitive constraints?

presto amazon-athena hive amazon-web-services
1 Answers

Apparently Presto version of Amazon is based on PrestoDB and this feature doesn't seem to be available on Prestodb as of this writing; Prestosql, however, seem to have had this support from Release 312 and onwards (ref: https://github.com/prestosql/presto/issues/802). The solution is to make sure the schema in the ORC file uses lowercase column name for it to be matched with names from hive metastore (which are always lowercased).

Debug code for presto db: https://github.com/prestodb/presto/blob/6647e13f64883f7cfa89221d91b981bcc3a57618/presto-hive/src/main/java/com/facebook/presto/hive/HiveUtil.java#L984 https://github.com/prestodb/presto/blob/95bcb3947cad1570e19a0adaebc58009aa362ada/presto-orc/src/main/java/com/facebook/presto/orc/OrcReader.java#L130

user_69951140
Related