topic Querying columns with special characters in Data Engineering

Querying columns with special characters

JamieN — Mon, 07 Nov 2022 22:43:34 GMT

I'm trying to do a select on a column with special characters in Databricks sql.

I've tried the following

%sql
select ex$col 
from database.table
limit 10;
 
%sql
select `ex$col `
from database.table
limit 10;

They both don't work and will return "ex does not exist. Did you mean the following/ .... "

Is there a way to do this? or is the functionality not currently supported?

Re: Querying columns with special characters

LandanG — Tue, 08 Nov 2022 03:33:18 GMT

It might depend on how your table was created. I was able to get the below to work:

CREATE OR REPLACE TABLE database.special_chars (`ex$col` int);
INSERT INTO database.special_chars VALUES (1);
 
SELECT `ex$col`
FROM database.special_chars
LIMIT 10;

Re: Querying columns with special characters

JamieN — Wed, 09 Nov 2022 04:21:29 GMT

Many thanks for your reply.

Ah, we are creating the table using a delta location

 
s = f"create table {database}.{dataset_name} using delta location '{location}'"
spark.sql(s)

I can still query the special character using pyspark which good for me now, but a lot of our users will want to use sql. The only option seems to be to change the schema.

Re: Querying columns with special characters

Anonymous — Mon, 21 Nov 2022 06:18:48 GMT

Hi @Jamie Nathan

Hope all is well!

Just wanted to check in if you were able to resolve your issue and would you be happy to share the solution or mark an answer as best? Else please let us know if you need more help.

We'd love to hear from you.

Thanks!

Re: Querying columns with special characters

arun_pamulapati — Thu, 23 Mar 2023 14:10:25 GMT

@LandanG . The issue is with %sql .