cancel
Showing results for 
Search instead for 
Did you mean: 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results for 
Search instead for 
Did you mean: 

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

VVM
New Contributor III

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.

1 ACCEPTED SOLUTION

Accepted Solutions

NateAnth
Valued Contributor
Valued Contributor

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

View solution in original post

12 REPLIES 12

Lakshay
Esteemed Contributor
Esteemed Contributor

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
Contributor

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
New Contributor III

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
Valued Contributor
Valued Contributor

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:

image 

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
New Contributor III

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 )

image

NateAnth
Valued Contributor
Valued Contributor

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
Valued Contributor
Valued Contributor

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

wow

_Dusk
New Contributor II

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
New Contributor II

Is this issue resolved?

Whit127
New Contributor II

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

Casper-Bang
New Contributor II

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

Join 100K+ Data Experts: Register Now & Grow with Us!

Excited to expand your horizons with us? Click here to Register and begin your journey to success!

Already a member? Login and join your local regional user group! If there isn’t one near you, fill out this form and we’ll create one for you to join!