topic split character string in cell with sql in Data Engineering

split character string in cell with sql

dulu — Fri, 16 Dec 2022 04:44:49 GMT

I have the following input:

I am looking for a way to split the characters in the item_order_detail column into 2 columns itemID and itemName. As below output table uses SQL function in databricks with spark_sql version 3.2.1.

Can someone suggest a solution for me?

Thanks all

Re: split character string in cell with sql

Wassim — Fri, 16 Dec 2022 06:11:45 GMT

Hi, you got to extract itemID and itemName from single column item_order_detail - you can make use of from_json function for that. I hope following code helps,

SELECT order_id, from_json(item_order_details, 'itemid STRING, itemname STRING') as items
FROM orders
 
SELECT order_id, items.itemid, items.itemname
FROM orders
LATERAL VIEW from_json(item_order_details, 'itemid STRING, itemname STRING') items as items

Re: split character string in cell with sql

Wassim — Fri, 16 Dec 2022 06:42:41 GMT

and pyspark for the same-

df = spark.table("orders")
 
df = df.withColumn("items", from_json(df["item_order_details"], "itemid STRING, itemname STRING"))
 
df = df.select("order_id", "items.itemid", "items.itemname")

Re: split character string in cell with sql

dulu — Fri, 16 Dec 2022 16:02:29 GMT

Hi @Wasim Inamdar

I tried it and have error bellow:

SELECT order_id, from_json(item_order_details, 'itemid STRING, itemname STRING') as items
FROM orders

result

and

SELECT order_id, items.itemid, items.itemname
FROM orders
LATERAL VIEW from_json(item_order_details, 'itemid STRING, itemname STRING') items as items

Error in SQL statement: AnalysisException: from_json is expected to be a generator. However, its class is org.apache.spark.sql.catalyst.expressions.JsonToStructs, which is not a generator

Re: split character string in cell with sql

Anonymous — Fri, 16 Dec 2022 20:53:39 GMT

If you have a struct type and not json, you can explode the column and then extract the parts. https://spark.apache.org/docs/latest/api/sql/index.html#explode will unpack or flatmap the array. Just select order and explode("item_order_details")

Re: split character string in cell with sql

sher — Fri, 06 Jan 2023 05:49:05 GMT

you need to use explode function

https://stackoverflow.com/questions/61070630/spark-explode-column-with-json-array-to-rows