Hive 复杂数据类型Array，Map，Struct-526互联

建表语句，支持嵌套

CREATE TABLE parquet_test (
 id int,
 str string,
 mp MAP<STRING,STRING>,
 lst ARRAY<STRING>,
 strct STRUCT<A:STRING,B:STRING>)
PARTITIONED BY (part string)
STORED AS PARQUET;

测试表结构

CREATE EXTERNAL TABLE IF NOT EXISTS jc.dwd_wildanimal_news_json(
  odsId String,
  dwdId String,
  createDate BIGINT,
  siteName String,
  sourceUrl String,
  caseName String,
  gatherTime BIGINT,
  pubDate BIGINT,
  searchWord String,
  author String,
  content String,
  wildAnimalName Array<String>,
  motivation Array<String>,
  mode Array<String>,
  wildAnimalType Array<String>,
  huntingSite Array<String>,
  wildlifeTradingPlaces Array<String>,
  wildAnimalProtectionClass Array<String>,
  animalProducts Array<String>,
  animalRelatedQualification Array<String>,
  emotional Map<String, String>,
  cityCodes  Array<struct<province:string,provinceCode:String,city:string,cityCode:String,area:string,areaCode:String>>
) COMMENT '野生动物新闻明细数据'
STORED AS parquet
lOCATION '/dwd/news/dwd_wildanimal_baidu_news_parquet_json';

操作：

hive> select * from dwd_wildanimal_news_json limit 1;
74639fbc-955a-3bd3-b8dd-b49efd8a6ded 426b7d1b018844a8b72b43c0e16861a6 1593482566036 NULL https://baijiahao.baidu.com/s?id=1661650331125672620&wfr=spider&for=pc 「生物多样性百科」节肢动物·元首绢蝶:流石滩上的高冷“采花大盗” 1593482566000 1584669480000 动物 绢蝶属 云南网 今天要带大家了解的是节肢动物·元首绢蝶。<br/>元首绢蝶<br/>Parnassius cephalus<br/>节肢动物，昆虫纲，鳞翅目，凤蝶科，绢蝶亚科，绢蝶属。成虫出现于7-8月，多活动在海拔3000m以上的高山地带，国内主要分布于云南、甘肃、青海、四川、西藏等地。照片摄于香格里拉。<br/>照片中的元首绢蝶是在海拔3800米的流石滩上拍到的，可算得上是该物种活动的“低海拔”区域，不少摄影师曾在海拔4500米甚至更高的流石滩上看到过它们的身影。<br/>元首绢蝶的色彩与其生存环境十分相似，为低调的灰白色，后翅缀以红斑。身体上厚实的长毛可以帮助它们保暖。元首绢蝶一生分为卵、幼虫、蛹、成虫4个阶段。成虫把卵产在特定的高寒植物或旁边的岩石上，幼虫破卵而出时就可以吃植物的茎与叶。<br/>绢蝶是十分珍稀的蝶类。中国绢蝶种类已知有30多种，占全世界绢蝶种类的2/3。它们也是世界上有记录分布海拔最高的昆虫之一，活动最高极限可达到海拔5000多米，这是大部分昆虫无法企及的高度。<br/>因为生存环境恶劣，绢蝶十分眷恋阳光，每逢阴雨或多云天气时，它们便避而不出，而一旦遇到阳光，绢蝶就喜欢趴在石头上晒太阳。绢蝶很难寻觅，加之无法确定其行动轨迹，因此被生态摄影师称为最难拍的物种。<br/>牛洋，植物学博士，现任中国科学院昆明植物研究所副研究员，从事高山植物的进化生态学研究。科研之余，热爱自然摄影，希望通过科学和艺术的方式表现自然之美。<br/>统筹：连惠玲<br/>文字整理：田源<br/>海报设计：郑弼尹<br/>》》戳图进入专题了解更多百科《《 ["绢蝶属"] [] [] [] [] [] ["三有保护动物"] [] [] {"emotionalType":"正向","emotionalScore":"0.6016"} []

hive> select emotional from dwd_wildanimal_news_json limit 10;
{"emotionalType":"正向","emotionalScore":"0.6016"}
{"emotionalType":"负向","emotionalScore":"0.2132"}
{"emotionalType":"正向","emotionalScore":"0.8125"}
{"emotionalType":"正向","emotionalScore":"0.8415"}
{"emotionalType":"负向","emotionalScore":"0.4621"}
{"emotionalType":"正向","emotionalScore":"0.7759"}
{"emotionalType":"负向","emotionalScore":"0.0695"}
{"emotionalType":"负向","emotionalScore":"0.4624"}
{"emotionalType":"正向","emotionalScore":"0.8791"}
{"emotionalType":"负向","emotionalScore":"0.3399"}
Time taken: 0.135 seconds, Fetched: 10 row(s)
hive> select emotional,cityCodes from dwd_wildanimal_news_json limit 10;
{"emotionalType":"正向","emotionalScore":"0.8856"} [{"province":"","provinceCode":"0","city":"","cityCode":"0","area":"","areaCode":"0"}]
{"emotionalType":"正向","emotionalScore":"0.6013"} [{"province":"","provinceCode":"0","city":"","cityCode":"0","area":"","areaCode":"0"}]
{"emotionalType":"负向","emotionalScore":"0.2561"} [{"province":"","provinceCode":"0","city":"","cityCode":"0","area":"","areaCode":"0"}]
{"emotionalType":"正向","emotionalScore":"0.7446"} [{"province":"湖南省","provinceCode":"430000","city":"怀化市","cityCode":"431200","area":"","areaCode":"0"}]
{"emotionalType":"正向","emotionalScore":"0.6136"} [{"province":"云南省","provinceCode":"530000","city":"红河哈尼族彝族自治州","cityCode":"532500","area":"金平苗族瑶族傣族自治县","areaCode":"532530"}]
{"emotionalType":"正向","emotionalScore":"0.5473"} [{"province":"北京市","provinceCode":"110000","city":"市辖区","cityCode":"110100","area":"","areaCode":"0"}]
{"emotionalType":"正向","emotionalScore":"0.5836"} [{"province":"河南省","provinceCode":"410000","city":"南阳市","cityCode":"411300","area":"内乡县","areaCode":"411325"}]
{"emotionalType":"正向","emotionalScore":"0.6054"} [{"province":"福建省","provinceCode":"350000","city":"","cityCode":"0","area":"","areaCode":"0"}]
{"emotionalType":"正向","emotionalScore":"0.5582"} [{"province":"北京市","provinceCode":"110000","city":"市辖区","cityCode":"110100","area":"","areaCode":"0"}]
{"emotionalType":"负向","emotionalScore":"0.4759"} [{"province":"四川省","provinceCode":"510000","city":"雅安市","cityCode":"511800","area":"宝兴县","areaCode":"511827"}]
Time taken: 0.142 seconds, Fetched: 10 row(s)
hive> select emotional["emotionalType"] from dwd_wildanimal_news_json limit 10;
OK
正向
正向
负向
正向
正向
正向
正向
正向
正向
负向
hive> select a.emotional["emotionalType"],a.cityCodes.provinceCode from dwd_wildanimal_news_json a limit 10;
正向 ["0"]
正向 ["0"]
负向 ["0"]
正向 ["430000"]
正向 ["530000"]
正向 ["110000"]
正向 ["410000"]
正向 ["350000"]
正向 ["110000"]
负向 ["510000"]
hive> select a.emotional["emotionalType"],a.cityCodes[0].provinceCode from dwd_wildanimal_news_json a limit 10;
正向 0
正向 0
负向 0
正向 430000
正向 530000
正向 110000
正向 410000
正向 350000
正向 110000
负向 510000