Databricks SQL - Unable to Escape Dollar Sign ($) in Column Name

[VVM]

It seems that due to how Databricks processes SQL cells, it's impossible to escape the $ when it comes to a column name.

I would expect the following to work:

%sql
SELECT 'hi' `$id`

The backticks ought to escape everything. And indeed that's exactly what Databricks thinks should happen, too. I was able to create a table outside of SQL commands (from a Dataframe) that had a column called $id . When I did a SHOW CREATE on that table, Databricks suggested using `$id` .

But what seems to happen is that Databricks seizes upon the sight of any $X to do Query Snippeting. So I end up with a blank column name instead.

Query Snippets only seem to respect the \ character as a means of escaping. But this does not bode well in conjunction with the backtick all-encompassing escape method, as the backticks will interpret the \ character literally, leaving me with a fieldname of \$id .

Does anyone have a workaround? If not, how can this be raised to a bug report? Thanks.

[Lakshay]

Hi @Patrick Mascari​ , Just to understand the issue, you have a column named "$id" in your table and when you do "select $id from table" then it is failing. Is my understanding correct?

[Tayyab_Vohra]

Hi @Patrick Mascari​ can you try by using the SQL() function, I have written the code below with an example please check if that works for you.

 You can extract any escaped sequence in the column

data = spark.createDataFrame([("Alberto", 101), 
                              ("Dakota", 202), 
                              ("Kumar", 330),
                              ("Siva", 4),
                              ("Sree", 5),
                              ("Kavin", 6)], 
                                   ["Name", "$id"])
 
data.createOrReplaceTempView("temp_table")
sql("select `$id` as id, name from temp_table").show()

[VVM]

I want to build a table using the CREATE TABLE statement in Databricks SQL. But I can't name the column $id because `$id` does not work (in spite of Databricks' SHOW CREATE saying it should)

I understand that I can probably work around this by swapping to python/scala to run a spark query. If that's the only resort, then Databricks surely has a bug that needs reported and resolved..

[NateAnth]

Thank you for engaging with us on Databricks Community!

When you say you are trying this in Databricks SQL, I do not understand why the create table command is not working for you. Please see my working example with $'s in the column names:

 

To further narrow down what you are facing. Please clarify if you are attempting this in the Databricks SQL persona in the UI via the SQL editor (like in my screenshot above), or in a Notebook within the Data Science & Engineering persona. Screenshots would be helpful to further understand.

[VVM]

Yes, this is in a Notebook. You can see below that it fails because both instances get detected as query snippets and swapped out with blank, so the query effectively becomes CREATE TABLE my_temp_table( `` int, `` string )

[NateAnth]

Thank you for clarifying. I too see the same behavior and have brought this to the attention of our Notebook team to further investigate.

[NateAnth · Accepted solution]

Thank you @Patrick Mascari​ for bringing this to our attention, the appropriate engineering and product team is aware of the issue and will prioritize it accordingly to be addressed in the future. There is no ETA that can be provided at this time.

In the mean time, consider the following work arounds:

• Use spark.sql() in a python cell
• Use the Databricks SQL Editor in the SQL Persona

[AlexRodriges202]

wow

[_Dusk]

I am working with an imported cdc table.

I can select the column `__$operation` in the query editor but not in a SQL notebook.

This is so annoying.

[Lee78]

Is this issue resolved?

[Whit127]

This has not been resolved from what I can see. Databricks team, any progress updates here? Its been 6 months.

[Casper-Bang]

What is the status on this bug report? its been over a year now. 

[Pfizer]

What is the status of this bug? This is affecting user experience.