从配置单元表中检索数据时为空值 [英] Null value when retrieving data from hive table

查看：113
本文介绍了从配置单元表中检索数据时为空值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

  {query：{results：{结果：[{Phone：（787）812-3300，Distance：6.62，MapUrl：http://local.yahoo.com/info-159703323-petsmart-ponce?viewtype =图  的xmlns： 瓮：雅虎：LCL， 分类：空， 标题： PetsMart的， 城市： 庞塞， BusinessClickUrl： HTTP：//stores.petsmart .COM /存储/详细信息/ 2308\" ， CLICKURL： http://local.yahoo.com/info-159703323-petsmart-ponce， ID： 159703323， BusinessUrl：HTTP：/ /stores.petsmart.com/Store/Details/2308\",\"State\":\"PR\",\"Url\":\"http://local.yahoo.com/info-159703323-petsmart-ponce\",\"Rating： {LastReviewIntro：null，TotalReviews：0，TotalRatings：0，LastReviewDate：null，AverageRating：NaN}，Address：2643 Ponce Bypass，Ste 148 ，Latitude：17.994451，Longitude： -  66.638763}，{Phone：（787）878-8800，Distance：35.88，MapUrl：http：// local.yahoo.com/info-159700558-petsmart-hatillo?viewtype=map\",\"xmlns\":\"urn:yahoo:lcl\",\"Categories\":null,\"Title\":\"Petsmart\",\"City ：阿提约 BusinessClickUrl： http://stores.petsmart.com/Store/Details/2272， CLICKURL： http://local.yahoo.com/info-159700558-petsmart-hatillo， ID ： 159700558\" ， BusinessUrl： http://stores.petsmart.com/Store/Details/2272， 国家： PR， URL：http://local.yahoo.com / INFO-159700558-PETSMART  - 阿提约， 等级：{ LastReviewIntro：空， TotalReviews： 0， TotalRatings： 0， LastReviewDate：空， AverageRating： 南 }，Address：506 Truncado St. Hatillo，波多黎各00659，Latitude：18.483111，Longitude： -  66.822868}，{Phone：（787）286-2999 距离： 37.65， MapUrl： http://local.yahoo.com/info-159704175-petsmart-caguas?viewtype=map， 的xmlns： 瓮：雅虎：LCL，分类：空， 标题 ： PetsMart的  城市： 卡瓜斯， BusinessClickUrl： http://stores.petsmart.com/Store/Details/2188， CLICKURL： HTTP：/ /local.yahoo.com/info-159704175-petsmart-caguas\",\"id\":\"159704175\",\"BusinessUrl\":\"http://stores.petsmart.com/Store/Details/2188\",\"State： PR， URL： http://local.yahoo.com/info-159704175-petsmart-caguas ，Rating：{LastReviewIntro：null，TotalReviews：0，TotalRatings：0，LastReviewDate：null，AverageRating：NaN}，Address Rafael Cordero，Latitude：18.244964，Longitude： -  66.019486}，{Phone：（787）282-2966，Distance：39.18，MapUrl ：？//local.yahoo.com/info-159725166-petsmart-san-juan viewtype =图， 的xmlns： 瓮：雅虎：LCL， 分类：空， 标题： PetsMart的， 城市：圣胡安，BusinessClickUrl：http://stores.petsmart.com/Store/Details/2162，ClickUrl：http://local.yahoo.com/info-159725166- PETSMART-SAN娟  ID： 159725166， BusinessUrl： http://stores.petsmart.com/Store/Details/2162， 国家： PR， URL： http://local.yahoo.com/info-159725166-petsmart-san-juan\",\"Rating\":{\"LastReviewIntro\":null,\"TotalReviews\":\"0\",\"TotalRatings\":\"0\",\"LastReviewDate ：null，AverageRating：NaN}，Address：Senorial Plaza Mall South San Juan，波多黎各00926，Latitude：18.37046，Longitude： -  66.067658}，{电话：（787）774-1370，距离：39.17，MapUrl：http：//local.y ahoo.com/info-159699705-petsmart-guaynabo?viewtype=map\",\"xmlns\":\"urn:yahoo:lcl\",\"Categories\":null,\"Title\":\"Petsmart\",\"City\":\"Guaynabo BusinessClickUrl： http://stores.petsmart.com/Store/Details/2160， CLICKURL： http://local.yahoo.com/info-159699705-petsmart-guaynabo， ID ： 159699705， BusinessUrl： http://stores.petsmart.com/Store/Details/2160， 国家： PR， URL：http://local.yahoo.com/信息-159699705-PETSMART  - 瓜伊纳沃， 等级：{ LastReviewIntro：空， TotalReviews： 0， TotalRatings： 0， LastReviewDate：空， AverageRating： 南} ，地址：A2 Calle Ebano，纬度：18.408851，经度： -  66.100197}]}，count：5，created：2014-02-03T05： 18Z，lang：en-US}}

p> 

  CREATE EXTERNAL TABLE数据（
 query STRUCT< 
结果：STRUCT< 
结果：ARRAY < 
 STRUCT< 
电话：STRING，
距离：STRING，
 MapUrl：STRING，
 xmlns：STRING，
类别：STRING，
标题：STRING，
 City：STRING，
 BusinessClickUrl：STRING，
 ClickUrl：STRING，
 id：STRING，
 BusinessUrl：STRING，
州：STRING，
网址：STRING，
等级：STRUCT< 
 LastReviewIntro：STRING，
 TotalReviews：STRING，
 TotalRatings：STRING，
 LastReviewDate：STRING，
 AverageRating：STRING 
> ;, 
地址：STRING，
纬度：STRING，
经度：STRING 
> 
> 
>，
 count：STRING，
 created：STRING，
 lang：STRING 
> 
）ROW FORMAT SERDE'org.openx.data.jsonserde.JsonSerDe'LOCATION'/ user / hive / warehouse / test /';

但是当我说 select * from data
结果列为空。 

  {results：{result：null}，count：5，created： 2014-02-03T05：31：18Z，lang：en-US} 
所用时间：0.127秒，已提取：1行

我也尝试过不同的东西，比如 

  CREATE EXTERNAL TABLE data（
 query string 
）ROW FORMAT SERDE'org.openx.data.jsonserde.JsonSerDe'LOCATION'/ user / hive / warehouse / test /';

对于此select *语句将显示所有结果。但是，我的上面的查询有什么问题？

我也试过从数据中选择get_json_object（'data.query'，'$'）; 这也会提取null。

解决方案

我去了答案，我们应该使用不同的jar来获取所有值。 

 添加JAR /hive/Hive/hive-0.12.0/lib/hive-serdes-1.0-SNAPSHOT.jar;

在代码中，除 ROW FORMAT SERDE'com.cloudera外， hive.serde.JSONSerDe'。我们将获得嵌套块内的所有值。

I want to create a hive table for this json object 

{"query":{"results":{"Result":[{"Phone":"(787) 812-3300","Distance":"6.62","MapUrl":"http://local.yahoo.com/info-159703323-petsmart-ponce?viewtype=map","xmlns":"urn:yahoo:lcl","Categories":null,"Title":"Petsmart","City":"Ponce","BusinessClickUrl":"http://stores.petsmart.com/Store/Details/2308","ClickUrl":"http://local.yahoo.com/info-159703323-petsmart-ponce","id":"159703323","BusinessUrl":"http://stores.petsmart.com/Store/Details/2308","State":"PR","Url":"http://local.yahoo.com/info-159703323-petsmart-ponce","Rating":{"LastReviewIntro":null,"TotalReviews":"0","TotalRatings":"0","LastReviewDate":null,"AverageRating":"NaN"},"Address":"2643 Ponce Bypass, Ste 148","Latitude":"17.994451","Longitude":"-66.638763"},{"Phone":"(787) 878-8800","Distance":"35.88","MapUrl":"http://local.yahoo.com/info-159700558-petsmart-hatillo?viewtype=map","xmlns":"urn:yahoo:lcl","Categories":null,"Title":"Petsmart","City":"Hatillo","BusinessClickUrl":"http://stores.petsmart.com/Store/Details/2272","ClickUrl":"http://local.yahoo.com/info-159700558-petsmart-hatillo","id":"159700558","BusinessUrl":"http://stores.petsmart.com/Store/Details/2272","State":"PR","Url":"http://local.yahoo.com/info-159700558-petsmart-hatillo","Rating":{"LastReviewIntro":null,"TotalReviews":"0","TotalRatings":"0","LastReviewDate":null,"AverageRating":"NaN"},"Address":"506 Truncado St. Hatillo, Puerto Rico 00659","Latitude":"18.483111","Longitude":"-66.822868"},{"Phone":"(787) 286-2999","Distance":"37.65","MapUrl":"http://local.yahoo.com/info-159704175-petsmart-caguas?viewtype=map","xmlns":"urn:yahoo:lcl","Categories":null,"Title":"Petsmart","City":"Caguas","BusinessClickUrl":"http://stores.petsmart.com/Store/Details/2188","ClickUrl":"http://local.yahoo.com/info-159704175-petsmart-caguas","id":"159704175","BusinessUrl":"http://stores.petsmart.com/Store/Details/2188","State":"PR","Url":"http://local.yahoo.com/info-159704175-petsmart-caguas","Rating":{"LastReviewIntro":null,"TotalReviews":"0","TotalRatings":"0","LastReviewDate":null,"AverageRating":"NaN"},"Address":"200 Avenida Rafael Cordero","Latitude":"18.244964","Longitude":"-66.019486"},{"Phone":"(787) 282-2966","Distance":"39.18","MapUrl":"http://local.yahoo.com/info-159725166-petsmart-san-juan?viewtype=map","xmlns":"urn:yahoo:lcl","Categories":null,"Title":"Petsmart","City":"San Juan","BusinessClickUrl":"http://stores.petsmart.com/Store/Details/2162","ClickUrl":"http://local.yahoo.com/info-159725166-petsmart-san-juan","id":"159725166","BusinessUrl":"http://stores.petsmart.com/Store/Details/2162","State":"PR","Url":"http://local.yahoo.com/info-159725166-petsmart-san-juan","Rating":{"LastReviewIntro":null,"TotalReviews":"0","TotalRatings":"0","LastReviewDate":null,"AverageRating":"NaN"},"Address":"Senorial Plaza Mall South San Juan, Puerto Rico 00926","Latitude":"18.37046","Longitude":"-66.067658"},{"Phone":"(787) 774-1370","Distance":"39.17","MapUrl":"http://local.yahoo.com/info-159699705-petsmart-guaynabo?viewtype=map","xmlns":"urn:yahoo:lcl","Categories":null,"Title":"Petsmart","City":"Guaynabo","BusinessClickUrl":"http://stores.petsmart.com/Store/Details/2160","ClickUrl":"http://local.yahoo.com/info-159699705-petsmart-guaynabo","id":"159699705","BusinessUrl":"http://stores.petsmart.com/Store/Details/2160","State":"PR","Url":"http://local.yahoo.com/info-159699705-petsmart-guaynabo","Rating":{"LastReviewIntro":null,"TotalReviews":"0","TotalRatings":"0","LastReviewDate":null,"AverageRating":"NaN"},"Address":"A2 Calle Ebano","Latitude":"18.408851","Longitude":"-66.100197"}]},"count":5,"created":"2014-02-03T05:31:18Z","lang":"en-US"}}

My create table query is 

CREATE EXTERNAL TABLE data (
query STRUCT<
        results: STRUCT<
                Result: ARRAY<
                        STRUCT<
                                Phone: STRING,
                                Distance: STRING,
                                MapUrl: STRING,
                                xmlns: STRING,
                                Categories: STRING,
                                Title: STRING,
                                City: STRING,
                                BusinessClickUrl: STRING,
                                ClickUrl: STRING,
                                id: STRING,
                                BusinessUrl: STRING,
                                State: STRING,
                                Url: STRING,
                                Rating: STRUCT<
                                        LastReviewIntro: STRING,
                                        TotalReviews: STRING,
                                        TotalRatings: STRING,
                                        LastReviewDate: STRING,
                                        AverageRating: STRING
                                >,
                                Address: STRING,
                                Latitude: STRING,
                                Longitude: STRING
                        >
                >
        >,
        count: STRING,
        created: STRING,
        lang: STRING
>
) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION '/user/hive/warehouse/test/';

But when i say select * from data the result column is null. 

{"results":{"result":null},"count":"5","created":"2014-02-03T05:31:18Z","lang":"en-US"}
Time taken: 0.127 seconds, Fetched: 1 row(s)

I also tried different things, like 

CREATE EXTERNAL TABLE data (
query string
) ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe' LOCATION '/user/hive/warehouse/test/';

For this select * statement will display all results. But what is the problem with my above query ?

I also tried select get_json_object('data.query', '$') from data; This also fetches null.

解决方案

I go the answer, We should use a different jar, to get all values. 

 ADD JAR /hive/Hive/hive-0.12.0/lib/hive-serdes-1.0-SNAPSHOT.jar;

and in code everything is same except ROW FORMAT SERDE 'com.cloudera.hive.serde.JSONSerDe'. We will get all values inside nested blocks.

这篇关于从配置单元表中检索数据时为空值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆