pyspark Hive SQL转换阵列（Map（varchar，varchar））到串行的字符串

Question

我想

   array(map(varchar, varchar))

从Jupyter Notebook Python3中编程地将Prestpark Hive SQL在Presto db上的表行转换为Presto DB上的一行。

示例

user_id     sport_ids
 'aca'       [ {'sport_id': '5818'}, {'sport_id': '6712'}, {'sport_id': '1065'} ]

的结果

  user_id.    sport_ids
  'aca'.          '5815'
  'aca'.          '5712'
  'aca'.          '1065'

我尝试过

     sql_q= """
            select distinct, user_id, transform(sport_ids, x -> element_at(x, 'sport_id')
            from tab """
            
     spark.sql(sql_q)

，但出现了错误：

   '->' cannot be resolved  

我也尝试过，

  sql_q= """
            select distinct, user_id, sport_ids
            from tab"""
            
     spark.sql(sql_q)

但出错了：

    org.apache.spark.sql.AnalysisException: Cannot have map type columns in DataFrame which calls set operations(intersect, except, etc.), but the type of column request_features[0] is map<string,string>;;

我错过了什么吗？

我尝试过，但是很有帮助 hive convert convert arnay＆lt arnay＆lt and string，string string，string＆gt;＆gt;＆gt;到字符串 提取地图（varchar，array（varray（var charh））-Hive sql

谢谢

Answer 1

让我们尝试使用高阶功能查找地图值并爆炸到单个行中

df.withColumn('sport_ids', explode(expr("transform(sport_ids, x->map_values(x)[0])"))).show()


+-------+---------+
|user_id|sport_ids|
+-------+---------+
|    aca|     5818|
|    aca|     6712|
|    aca|     1065|
+-------+---------+

Answer 2

您可以处理JSON数据（JSON_PARSE，铸造为JSON和JSON_EXTRACT_SCALAR - 有关更多JSON函数 - 请参阅此处）和扁平（ unnest ）

-- sample data
WITH dataset(user_id, sport_ids) AS (
    VALUES 
        ('aca', '[ {"sport_id": "5818"}, {"sport_id": "6712"}, {"sport_id": "1065"} ]')
) 

-- query
select user_id,
    json_extract_scalar(record, '$.sport_id') sport_id
from dataset,
    unnest(cast(json_parse(sport_ids) as array(json))) as t(record)

输出：

USER_ID	Sport_ID
ACA	5818
ACA	6712
ACA	1065