How do I run a SQL query in Databricks Spark, querying a column entitled "like", when &quo-CodePudding

I just tried running the following SQL query in databricks spark:

SELECT 
        uid_1,
        uid_2,
        CASE WHEN like IS TRUE THEN 1 ELSE 0 AS liked
FROM relevant_table

I run into an error message because "like" is also a SQL term. When working in redshift, one can simply put "like" in double quotes to resolve this issue, but that doesn't work with databricks spark. What's the fix here?

CodePudding user response：

I'm not too familiar with spark, but maybe escaping with backticks does the trick:

SELECT ds,
       st,
       uid,
       other_id,
       CASE WHEN `like` IS TRUE OR super_like IS TRUE THEN 1 ELSE 0 AS liked,
       s_number
FROM   tinder_events.recs_rate_20228

CodePudding user response：

I don't think Databricks needs special treatment of your column named "like". You could double-quote it or use backticks, but I don't believe it should matter.

The problem with the query is that it's a missing the END to the case expression. I believe that will resolve your error.

SELECT ds,
        st,
        uid,
        other_id,
        CASE WHEN like IS TRUE OR super_like IS TRUE THEN 1 ELSE 0 END AS liked,
        s_number
FROM tinder_events.recs_rate_20228