0

I've table that contains JSON column_A.
Instead of setting column_A to a struct, I set column_A as a string to query JSON.
The problem is when I query column_A I receive the data in lowercase.

CREATE EXTERNAL TABLE `table_test`(
  `userid` string COMMENT 'from deserializer', 
  `column_a` string COMMENT 'from deserializer', 
  `createdat` string COMMENT 'from deserializer', 
  `updatedat` string COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 
  'paths'='column_a,userId') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-stackoverflow/'
TBLPROPERTIES (
  'classification'='json', 
  'transient_lastDdlTime'='1567353697')

I understand that related to SerDe but I don't know what should I change to.
What can I do to solve it?


The reasons I don't set column_A as struct are:
1. The key is changing every time and as far as I know, I need to set the key values when I define the struct.
2. I've empty string and key - Got errors for query struct with "" as key.

Thanks.

dtolnay
  • 9,621
  • 5
  • 41
  • 62
DonSaada
  • 767
  • 2
  • 8
  • 18

1 Answers1

1

Found the solution.
The problem is with SerDe
https://docs.aws.amazon.com/athena/latest/ug/json.html


need to add 'case.insensitive'= "FALSE" to Serde properties.

CREATE EXTERNAL TABLE `table_test`(
  `userid` string COMMENT 'from deserializer', 
  `column_a` string COMMENT 'from deserializer', 
  `createdat` string COMMENT 'from deserializer', 
  `updatedat` string COMMENT 'from deserializer')
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ( 
  'case.insensitive'= "FALSE",
  'paths'='column_a,userId') 
STORED AS INPUTFORMAT 
  'org.apache.hadoop.mapred.TextInputFormat' 
OUTPUTFORMAT
  'org.apache.hadoop.hive.ql.io.HiveIgnoreKeyTextOutputFormat'
LOCATION
  's3://test-stackoverflow/'
TBLPROPERTIES (
  'classification'='json', 
  'transient_lastDdlTime'='1567353697')

Hope this will help someone.

DonSaada
  • 767
  • 2
  • 8
  • 18