topic Re: Using "FOR XML PATH" in Spark SQL in sql syntax in Data Engineering

Using "FOR XML PATH" in Spark SQL in sql syntax

oleole — Mon, 27 Mar 2023 04:50:34 GMT

I'm using spark version 3.2.1 on databricks (DBR 10.4 LTS), and I'm trying to convert sql server sql query to a new sql query that runs on a spark cluster using spark sql in sql syntax. However, spark sql does not seem to support XML PATH as a function and I wonder if there is an alternative way to convert this sql server query into a sql query that spark sql will accept. The original sql server sql query looks like this:

DROP TABLE if exists UserCountry;

CREATE TABLE if not exists UserCountry (

UserID INT,

Country VARCHAR(5000)

);

INSERT INTO UserCountry

SELECT

L.UserID AS UserID,

COALESCE(

STUFF(

(SELECT ', ' + LC.Country FROM UserStopCountry LC WHERE L.UserID = LC.UserID FOR XML PATH (''))

, 1, 2, '')

, '') AS Country

FROM

LK_ETLRunUserID L

When I run the query above in databricks spark sql, I get the following error:

ParseException:

mismatched input 'FOR' expecting {')', '.', '[', 'AND', 'BETWEEN', 'CLUSTER', 'DISTRIBUTE', 'DIV', 'EXCEPT', 'GROUP', 'HAVING', 'IN', 'INTERSECT', 'IS', 'LIKE', 'ILIKE', 'LIMIT', NOT, 'OR', 'ORDER', 'QUALIFY', RLIKE, 'MINUS', 'SORT', 'UNION', 'WINDOW', EQ, '<=>', '<>', '!=', '<', LTE, '>', GTE, '+', '-', '*', '/', '%', '&', '|', '||', '^', ':', '::'}(line 6, pos 80)

== SQL ==

INSERT INTO UserCountry

SELECT

L.UserID AS UserID,

COALESCE(

STUFF(

(SELECT ', ' + LC.Country FROM UserStopCountry LC WHERE L.UserID = LC.UserID FOR XML PATH (''))

--------------------------------------------------------------------------------^^^

, 1, 2, '')

, '') AS Country

FROM

LK_ETLRunUserID L

Given that the UserStopCountry looks like this:

I believe the output will be:

Re: Using "FOR XML PATH" in Spark SQL in sql syntax

daniel_sahal — Wed, 29 Mar 2023 09:47:36 GMT

@Jay Yang

You can use a combination of array_join and collect_list. See example below.

Source table:

After transformation:

Re: Using "FOR XML PATH" in Spark SQL in sql syntax

Anonymous — Thu, 30 Mar 2023 07:35:01 GMT

Hi @Jay Yang

Thank you for posting your question in our community! We are happy to assist you.

To help us provide you with the most accurate information, could you please take a moment to review the responses and select the one that best answers your question?

This will also help other community members who may have similar questions in the future. Thank you for your participation and let us know if you need any further assistance!

Re: Using "FOR XML PATH" in Spark SQL in sql syntax

oleole — Thu, 30 Mar 2023 12:59:03 GMT

Posting the solution that I ended up using:

%sql
 DROP TABLE if exists UserCountry;
  CREATE TABLE if not exists UserCountry (
   UserID INT,
   Country VARCHAR(5000)
  );
  INSERT INTO UserCountry
  SELECT 
   L.UserID AS UserID,
   CONCAT_WS(',', collect_list(LC.Country)) AS Country
   COALESCE(
   STUFF(
   (SELECT ', ' + LC.Country FROM UserStopCountry LC WHERE L.UserID = LC.UserID FOR XML PATH (''))
   , 1, 2, '')
   , '') AS Country
  FROM LK_ETLRunUserID L
  INNER JOIN UserStopCountry LC
  ON L.UserID = LC.UserID
  GROUP By L.UserID