Я новичок в HiveQL, и я немного застрял: S
У меня есть таблица следующей схемы. Один столбец с именем res и три разделенных на разделы столбца partion_column с именем filed.
create table results( res string) PARTITIONED BY (field STRING);
Затем я импортировал данные в эту таблицу
insert overwrite table results PARTITION (field= 'title') SELECT explode(line) AS myNewCol FROM titles ;
insert overwrite table results PARTITION (field= 'artist') SELECT explode(line) AS myNewCol FROM artist;
insert overwrite table results PARTITION (field= 'albums') SELECT explode(line) AS myNewCol FROM albums;
Я пытаюсь подсчитать уникальные трубы в трех разделах.
Например, эта команда подсчитывает количество существования определенных заголовков в наборе данных.
SELECT res, count(1) AS counttotal FROM results where field='title' GROUP BY res ORDER BY counttotal;
и выводит что-то вроде
title count
Hit me Baby More time 9
Как я могу распространить это на кортежи (название, альбом, исполнитель)? Если я хочу получить вывод, например:
title album artist count
Baby one more time hit me baby one more time britney spears 9
Весь мой код:
CREATE EXTERNAL TABLE IF NOT EXISTS hivetesttable (
xmldata STRING)
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
location '/user/sdasd/hivetestdata/';
create view xmlout(line) as select * from hivetesttable;
CREATE VIEW TITLES(line) as select xpath(line,'/MC/SC/*/@ttl') from xmlout;
CREATE VIEW ARTIST(line) as select xpath(line,'/MC/SC/*/@art') from xmlout;
CREATE VIEW ALBUMS( line) as select xpath(line,'/MC/SC/*/@art') from xmlout;
create table results( res string) PARTITIONED BY (field STRING);
insert overwrite table results PARTITION (field= 'title') SELECT explode(line) AS myNewCol FROM titles ;
insert overwrite table results PARTITION (field= 'artist') SELECT explode(line) AS myNewCol FROM artist;
insert overwrite table results PARTITION (field= 'albums') SELECT explode(line) AS myNewCol FROM albums;
SELECT res, count(1) AS counttotal FROM results where field='title' GROUP BY res ORDER BY counttotal;
строка данных xml похожа на
<?xml version="1.0" encoding="UTF-8"?><MC><SC><S uid="2" gen="" yr="2011" art="Samsung" cmp="<unknown>" fld="/mnt/sdcard/Samsung/Music" alb="Samsung" ttl="Over the horizon"/><S uid="37" gen="" yr="2010" art="Jason Derulo" cmp="<unknown>" fld="/mnt/sdcard/Music/Jason Derulo/Jason Derulo" alb="Jason Derulo" ttl="Whatcha Say"/><S uid="38" gen="" yr="2010" art="Jason Derulo" cmp="<unknown>" fld="/mnt/sdcard/Music/Jason Derulo/Jason Derulo" alb="Jason Derulo" ttl="In My Head"/><S uid="39" gen="" yr="2011" art="Alexandra Stan" cmp="<unknown>" fld="/mnt/sdcard/Music/Alexandra Stan/Mr_ Saxobeat - Single" alb="Mr. Saxobeat - Single" ttl="Mr. Saxobeat (Extended Version)"/><S uid="40" gen="" yr="2011" art="Bushido" cmp="<unknown>" fld="/mnt/sdcard/Music/Bushido/Jenseits von Gut und Böse (Premium Edition)" alb="Jenseits von Gut und Böse (Premium Edition)" ttl="Wie ein Löwe"/><S uid="41" gen="" yr="2011" art="Bushido" cmp="<unknown>" fld="/mnt/sdcard/Music/Bushido/Jenseits von Gut und Böse (Premium Edition)" alb="Jenseits von Gut und Böse (Premium Edition)" ttl="Verreckt"/><S uid="42" gen="" yr="2011" art="Lucenzo" cmp="<unknown>" fld="/mnt/sdcard/Music/Lucenzo/Danza Kuduro (feat_ Don Omar) [From _Fast & Furious 5_] - Single" alb="Danza Kuduro (feat. Don Omar) [From "Fast & Furious 5"] - Single" ttl="Danza Kuduro (feat. Don Omar) [From "Fast & Furious 5"]"/><S uid="121" gen="" yr="701" art="Michael Jackson" cmp="<unknown>" fld="/mnt/sdcard/external_sd/Music/Michael Jackson/Bad [Bonus Tracks]" alb="Bad [Bonus Tracks]" ttl="Voice-Over Intro/Quincy Jones Interview #1 [*]"/></SC><PC/></MC>
