sql - 需要在Hive中将具有多个定界符的列定界为多行

这是我原来的桌子。我需要定界列段。我在下面显示了我想要的。

我确实尝试过稍后 View 爆炸，但不是像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>

最佳答案

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  |
+----------+