sql - 需要将具有多个分隔符的列分隔为 Hive 中的多行

Question

这是我原来的 table 。我需要分隔列段。我在下面显示了我想要的。

我确实尝试过稍后查看爆炸，但它不是像 ABC-DEF 这样的字符串，而是在单独的行中给我 A、B、C、-、D、...。

<table border="1">
<caption>What I Have</caption>
  <tr>
    <th>Unique-Key </th>
    <th>PNR </th>
    <th>Segments </th>
  </tr>
  <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>ABC-DEF;GHI-JKL| JKL-GHI;DEF-ABC</td>
  </tr>
</table>




<table border="1">
<caption>What I want</caption>
  <tr>
    <th>Unique-Key </th>
    <th>PNR </th>
    <th> New Segments </th>
  </tr>
  <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>ABC-DEF</td>
  </tr>
  <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>GHI-JKL</td>
  </tr>
  <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>JKL-GHI</td>
  </tr>
    <tr>
    <td>ABC-12345-BLAH1234</td>
    <td>BLAH1234</td>
    <td>DEF-ABC</td>
  </tr>
</table>

Answer 1

with t as (select 'ABC-DEF;GHI-JKL| JKL-GHI;DEF-ABC' as col)

select  e.col as segments

from    t lateral view explode (split(t.col,'\\s*[;|]\\s*')) e
;

+----------+
| segments |
+----------+
| ABC-DEF  |
| GHI-JKL  |
| JKL-GHI  |
| DEF-ABC  |
+----------+