逗号分隔的字符串转换为单独的行 - Impala SQL

Question

假设我们有一张表：

Owner   | Pets
------------------------------
Jack    | "dog, cat, crocodile" 
Mary    | "bear, pig"

我想得到的结果是：

Owner   | Pets
------------------------------
Jack    | "dog"
Jack    | "cat"
Jack    | "crocodile" 
Mary    | "bear"
Mary    | "pig"

我通过谷歌搜索找到了一些类似问题的解决方案，但是Impala SQL并没有提供任何这些能力来应用这些建议的解决方案。

非常感谢您的帮助！

- ifotopoulos

---

你能否添加一些细节，说明你尝试了什么以及结果如何，这样人们就不会重复你的失败努力了吗？ - shoover

您想要做的是“扁平化”嵌套数据结构，而该嵌套结构违反了整个实体关系概念。因此它不能使用标准SQL完成；只能使用特定于数据库的扩展，这些扩展可能存在也可能不存在，并且会因数据库而异。很抱歉，Impala并不适用于这种丑陋的ETL任务；您应该使用Pig或Hive或其他工具准备数据，将其加载到Hive表中，然后使用REFRESH <table_name>将其暴露给Impala。 - Samson Scharfrichter

Answer 1

以下内容适用于 Impala：

split_part(string source, string delimiter, bigint n)

您可以在这里找到文档： https://www.cloudera.com/documentation/enterprise/5-9-x/topics/impala_string_functions.html

- Timothée HENRY

---

这个解决方案只适用于一次处理一个标记。如果没有显式地调用每个索引位置，它无法将值清晰地展开到单独的行中。从技术上讲，这可能是一个解决方法，但会相当冗长和混乱。 - eperks

Answer 2

在 Impala 中尝试这个：

    select t,split_part(t,',',RT) cc
    from (
    select '12,33,4' t
    union all 
    select '12,23'   t
    ) T1
    inner join(
    select row_number() over(partition by 1 order by 1 ) RT
    from test.any_table_name limit 10) T2
    where T2.RT<=length(t) - length(replace(t,',','')) +1
    order by t,RT

- Lele Han

Answer 3

你需要使用Hive SQL来完成这个任务。

那么，让我们创建Impala表。

-- imapla sql
CREATE TABLE IF NOT EXISTS tmp.my_example (
  `Owner` VARCHAR,
  Pets VARCHAR
);

INSERT INTO tmp.my_example (`Owner`, `Pets`) VALUES
('Jack', 'dog, cat, crocodile'),
('Mary', 'bear, pig');

然后使用Hive SQL将列拆分为行：

-- hive sql
select f.owner as owner, t1.pos as pos, t1.val AS pet
from tmp.my_example f
lateral view posexplode(split(pets,', ')) t1 

如果您将结果保存为新表，请不要忘记在Impala中运行refresh new_table，以便您的新表可以使用。

附言：对于这个小表，Hive非常慢，但对于真实数据表现合理。

- fc9.30

---

这是我认为的解决方案。请访问以下链接：https://www.edureka.co/community/30472/hadoop-hive-how-to-split-a-single-row-into-multiple-rows - Rajitha Fernando

Answer 4

我想稍微改进一下@Lele Han的回答，以便更清楚地说明如何将其适应到您的使用情况中。

SELECT Owner
    , split_part(Pets, '|', PetPos) AS Pet
FROM (
    SELECT Owner, Pets
        , length(Pets) - length(replace(Pets, ',', '')) + 1 AS PetNumber
    FROM your_table
) t1
INNER JOIN (
    SELECT row_number() over(partition by 1 order by 1) PetPos
    FROM your_table
    LIMIT 100  -- a reasonable limit of number of pets
) t2
WHERE t2.PetPos <= t1.PetNumber

- ronkov