Unable to resolve column error while trying to query the view

[nkrom456]

• I have a federated table from snowflake in data bricks say employee.
• When i executed print schema i am able to see schema as "employeeid": long,"employeename":string
• Tried to create a view as 
• create view vw_emp with schema binding as select `"employeeid"` from employee 
• now when i query the view in sql editor select * from vw_emp its giving the unable to resolve column
• but same query if i try in it notebook with all purpose cluster %sql select * from  vw_emp no issues it gives me the output.
• Is there any way we can also get the output with sql editor.

[szymon_dybczak]

Hi @nkrom456 ,

Try something like this. If you are using backticks it treats a column name exactly as you type (in this case it treats double quotes as a part of a colum name)

create view vw_emp with schema binding as select `employeeid` from employee 

OR

create view vw_emp with schema binding as select employeeid from employee 

[nkrom456]

Hi Thanks for the prompt reply no its also not working the issue is federated table itself having column like "employeeid" with quotes so i have used backtick

[szymon_dybczak]

Hi @nkrom456 ,

Thanks for clarification. Ok, so it looks like identifiers in Snowflake could be case-sensitive. So for example employeeid could be stored in Snowflake metadata as EMPLOYEEID. If that is the case you need to use double quotes to preserve the case. Could you try that?

Run federated queries on Snowflake (OAuth) | Databricks Documentation

Identifier requirements | Snowflake Documentation

[nkrom456]

Thanks a lot for the response. But the same query works on notebook only we are having issue with sql editor. 

[szymon_dybczak · Accepted solution]

Maybe SQL Editor resolves column names in a different way compared to regular notebook. Anyway, if I were you I would check first how this attribute was defined in Snowflake.

If it was defined using unquoted identifier, like below

CREATE TABLE employee(employeeid INT)

 Then this attribute will be stored and resolved in uppercase. 

If your table attribute was defined using double-quoted identifier then it will preserve case-sensitivity, as you can see in following example from Snowflake documentation.

Now, if it was defined using double-quoted identifier then you have to use double quote as they suggest in databricks documentation, otherwise it won't work.

[nkrom456]

Thanks a lot . Yup I have checked the snowflake table and column names has double quotes, but when i executed the below query 

CREATE or replace VIEW vw_employee

WITH SCHEMA BINDING

AS select """employeeid""" from employee 

Now its not throwing any error but all the values have become employeeid.

[szymon_dybczak]

Hi @nkrom456 ,

So this is exactly what I expected. You have a case-sensitive identifier then. But I think what they meant by double quotes is to use this character " and not specifying it double times like you  did.
I don't have an access to snowflake to test it, but I think you should have something like this:

CREATE or replace VIEW vw_employee
WITH SCHEMA BINDING
AS select "employeeid" from "employee"