配置单元查询：匹配字符串数组中的列值以生成标志

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

有人可以帮忙查询，步骤和解释。我是Hive的新手。

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;

而且，如果您希望使用唯一类别的数组动态构建列，请使用其他一些工具来构建查询。例如，可以使用shell脚本来完成。

请参见此基于预定义数组构建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"执行该脚本，或将其保存到文件中。