Hive Query: соответствие значений столбца из массива строк для создания флагов

Question

У меня есть несколько записей, где каждая строка принадлежит некоторым категориям (тип данных - массив строк) и отдельный список уникальных категорий (тип данных - строка). Мне нужно сопоставить каждую строку с уникальным списком и создать для него флаги.

Input:
------
ID   Category
1    ["Physics","Math"]
2    ["Math"]
3    ["Math,"Chemistry"]
4    ["Physics","Computer"]

Теперь у меня есть отдельный список уникальных списков категорий в Excel, как показано ниже:

Unique Category
["Physics"]
["Math"]
["Chemistry"]
["Computer"]

Окончательный вывод должен выглядеть так:

ID   Category                  Math_F  Physics_F  Computer_F  Chemistry_F
1    ["Physics","Math"]          1         1          0           0
2    ["Math"]                    1         0          0           0
3    ["Math,"Chemistry"]         1         0          0           1
4    ["Physics","Computer"]      0         1          1           0

Может кто-нибудь, пожалуйста, помогите с запросом, шаги и объяснения. Я новичок в Улей.

Answer 1

С помощью array_contains():

SELECT ID,
       Category,
       CASE
           WHEN array_contains(Category, 'Math') THEN 1
           ELSE 0
       END Math_F,
       CASE
           WHEN array_contains(Category, 'Physics') THEN 1
           ELSE 0
       END Physics_F,
       CASE
           WHEN array_contains(Category, 'Computer') THEN 1
           ELSE 0
       END Computer_F,
       CASE
           WHEN array_contains(Category, 'Chemistry') THEN 1
           ELSE 0
       END Chemistry_F
FROM TABLE t;

И если вы хотите, чтобы столбцы создавались динамически с использованием массива уникальных категорий, используйте другой инструмент для построения запроса. Например, это можно сделать с помощью сценария оболочки.

Посмотрите этот пример построения SQL на основе предварительно определенного массива. Вы можете легко добавить свой массив чтения из файла:

#!/bin/bash

#define array
array=( Physics Math Computer Chemistry )

#initial sql
sql="select ID,   
       Category,"

#get length of array
arraylength=${#array[@]}

#get first flag column
columns="CASE
  WHEN array_contains(Category,'${array[0]}') THEN 1 
  ELSE 0 
          END ${array[0]}_F"

#attach all other flags:
for (( i=1; i<=$(( $arraylength-1 )); i++ ))
do 
    columns="$columns,
CASE
    WHEN array_contains(Category,'${array[$i]}') THEN 1
    ELSE 0 
 END ${array[$i]}_F"
done

#final SQL
sql="$sql 
$columns
from table t;
"
#print result
 echo "$sql"

Результат:

 SELECT ID,
       Category,
       CASE
           WHEN array_contains(Category, 'Physics') THEN 1
           ELSE 0
       END Physics_F,
       CASE
           WHEN array_contains(Category, 'Math') THEN 1
           ELSE 0
       END Math_F,
       CASE
           WHEN array_contains(Category, 'Computer') THEN 1
           ELSE 0
       END Computer_F,
       CASE
           WHEN array_contains(Category, 'Chemistry') THEN 1
           ELSE 0
       END Chemistry_F
FROM TABLE t;

Вы можете добавить вызов Hive к приведенному выше сценарию: hive -e "$sql" выполнить или сохранить вместо этого в файл.