Support Questions

Find answers, ask questions, and share your expertise
Celebrating as our community reaches 100,000 members! Thank you!

JsonSerDe: SELECT * works, but field selection doesn't


Hopefully easy question, but this works:


select * from mytable; 



And this doesn't work:

select struct_col.field from mytable;

Error message:


java.lang.ClassNotFoundException: Class not found



This error doesn't make any sense to me because wouldn't the JsonSerDe have to be available to do the first select * statement?  Any tips on how to resolve this? Many thanks.


Using CDH 5.15.



The `show create table mytable` looks like


CREATE EXTERNAL TABLE `mytable`(                  |
|   `name` string COMMENT 'from deserializer',       |
|   `struct_col` struct<field:boolean,other:boolean,thing:boolean> COMMENT 'from deserializer')  |
| ROW FORMAT SERDE                                   |
|   ''        |
| STORED AS INPUTFORMAT                              |
|   'org.apache.hadoop.mapred.TextInputFormat'       |
| OUTPUTFORMAT                                       |
|   '' |
| LOCATION                                           |
|   'hdfs://host:8020/user/bob/mytable' |
| TBLPROPERTIES (                                    |
|   'COLUMN_STATS_ACCURATE'='false',                 |
|   'numFiles'='1',                                  |
|   'numRows'='-1',                                  |
|   'rawDataSize'='-1',                              |
|   'totalSize'='581',                               |
|   'transient_lastDdlTime'='1544062948')            |






I can't find the jar for this file anywhere.


Does anyone know where it is located? 

Rising Star

The JsonSerDe class is in hive-hcatalog-xxx.jar.  So you will have to run "ADD JAR" on this file prior.


JsonSerDe has been made a first class citizen recently via

With this fix you should be able to just use "STORED AS JSONFILE" instead of having to specify the ROW FORMAT SERDE


This fix is available in CDH6.1 ( I believe, dont quote me on this)


Hope this helps. Thanks

Super Guru
SELECT * does not trigger MR job, while SELECT struct_col.field will, which means the jar is probably available on HS2 host, but not available on the NM hosts.

As Naveen mentioned, the class is in hive-hcatalog-xxx.jar, so you can put this jar into Hive AUX directory on HS2 host and then it should be distributed to the cluster while query is running in MR mode.

Regarding JsonSerDe in C6.1, Naveen is right and it has been confirmed.