如何在sparksql查询中使用正则表达式从表中的数据中提取数字-解网

问：

我有一个表，它有一个名为的列，其中包含这种格式的数据actual_result

列中的示例数据，每行值由下面的管道分隔符分隔：actual_result

> actual_result
++
|{"count": 0}  
|{"count": 0} 
|{"data_dt": "20230101"}  
|{"count": 0}     
|{"count": 0}    
|{"count": 0}
|{"az_cust_id": 0, "percent_null": 0}
|{"ndc": 0}
|{"az_prod_id": 0, "percent_null": 0}
|{"sp_nm": 0, "percent_null": 0}
|{"az_mkt_id": 0, "percent_null": 0}
|{"cust_zip": 0, "percent_null": 0}
|{"file_typ": 0, "percent_null": 0}
|{"ndc": 0, "percent_null": 0}
|{"zs_drug_cd": 0, "percent_null": 0}
|{"az_brd_id": 0, "percent_null": 0}
|{"prod_nm": 0, "percent_null": 0}
|{"mkt_nm": 0, "percent_null": 0}  
|{"ther_area_nm": 0, "percent_null": 0}   
|{"prod_lvl": 0, "percent_null": 0}   
|{"pueblo_exc_brd_ind": 0, "percent_null": 0}  
|{"invc_no": 0}          
|{"kaiser_ind": 0, "percent_null": 0}   
|{"fld_exc_ind": 0, "percent_null": 0}   
|{"sd_nm": 0}     
|{"tot_sls_units": 0}

我希望输出采用这种格式

(0,0)

我尝试使用所有 json 函数，但我要么得到空值，要么没有结果。谁能帮我解决这个问题？这是我尝试过的示例代码之一，但这给出了 null 值。regexp_extract

sql_df = spark.sql("""
    SELECT 
        *,
        REGEXP_EXTRACT(actual_result, '\\d+') AS extracted_numbers
    FROM schema.table
    WHERE actual_result LIKE '%:%'
""")
sql_df.show(10,0)

python sql 正则表达式 apache-spark pyspark

df = spark.createDataFrame([
    (1, '{"prod_id": 12, "percent_null": 34}'),
    (2, '{"prod_id": 0, "percent_null": 0}')
], ['id', 'actual_result'])
df.createOrReplaceTempView("table")

spark.sql("""
    select actual_result, struct(
        regexp_extract(actual_result, '([0-9]+)[^0-9]*([0-9]+)', 1) as prod_id,
        regexp_extract(actual_result, '([0-9]+)[^0-9]*([0-9]+)', 2) as percent_null
    ) as extracted_numbers from table
""").show(truncate=False)

+-----------------------------------+-----------------+
|actual_result                      |extracted_numbers|
+-----------------------------------+-----------------+
|{"prod_id": 12, "percent_null": 34}|{12, 34}         |
|{"prod_id": 0, "percent_null": 0}  |{0, 0}           |
+-----------------------------------+-----------------+

注意：您也可以按如下方式使用：from_json

spark.sql("""
    select from_json(
         actual_result,
         'struct<prod_id:STRING, percent_null:DOUBLE>'
    ) as extracted_numbers
    from table
""").show()

+-----------------+
|extracted_numbers|
+-----------------+
|       {12, 34.0}|
|         {0, 0.0}|
+-----------------+

非常感谢您提供的帮助。但是，json 方法将不起作用，因为您提供的列名在表中的每一行数据值中都会有所不同。json 数据中没有定义的值。这将仅对单行值给出准确的答案。对于第一个解决方案，我仍然没有得到任何结果。Reg表达式似乎无法识别此值中的数字。

0赞 Oli 9/27/2023

这很奇怪，它对我有用。尝试替换为 .您使用的是哪个版本的 Spark？\d[0-9]

0赞 anmol hans 9/27/2023

我也尝试用 [0-9] 替换 \d，它仍然没有从数据内部挑选数字。我认为这与数字的存储方式有关。我使用的 Spark 版本是“2.4.6-amzn-0”

0赞 Oli 9/27/2023

表的架构是什么？（或至少是实际结果的类型）

0赞 anmol hans 9/27/2023

实际结果列为字符串类型。

上一个：MYSQL REGEXP 不同时搜索两个条件 [重复]

下一个：错误：正则表达式无效：postgresql中的字符范围无效

如何在sparksql查询中使用正则表达式从表中的数据中提取数字

How to extract numbers from data in table using regular expression in sparksql query

评论

评论