How do I query custom schemas in lakehouses using sql magic cell ?

[akaur]

I am unable to query it using sql magic cell. I verified the schema and the table below exists in this lakehouse. I got this error with 

%%sql  

 select * from sampleLAKEHOUSE.AJ.tblFacility   ;

[TABLE_OR_VIEW_NOT_FOUND] The table or view `sampleLAKEHOUSE`.`AJ`.`tblFacility` cannot be found. Verify the spelling and correctness of the schema and catalog. If you did not qualify the name with a schema, verify the current_schema() output, or qualify the name with the correct schema and catalog. To tolerate the error on drop use DROP VIEW IF EXISTS or DROP TABLE IF EXISTS.; line 1 pos 14; 'Project [*] +- 'UnresolvedRelation [sampleLAKEHOUSE, AJ, tblFacility], [], false

however, I am able to view this same data with dataframes and with sql endpoint 

[nilendraFabric]

@akaur You accepted the followup thread rather than real the solution. 🙂

[v-menakakota · Accepted solution]

Hi @akaur ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[akaur]

this isnt resolved yet. I can do cross workspace but calling custom schema in LH using spark sql does not work. I can only do dbo. but I will mark it complete as its still in preview 

[v-menakakota]

Hi @akaur ,

As @nilendraFabric  mentioned,it is in preview. Thank you for the documentation. If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Regards,
Menaka.

[nilendraFabric]

Read this for more details, remember this is in preview, so there may be limitations and bugs

https://learn.microsoft.com/en-us/fabric/data-engineering/lakehouse-schemas

[nilendraFabric]

hello @akaur 

Try this

%%sql
SELECT *
FROM YourWorkspace.SampleLakehouse.AJ.tblFacility

If the default lakehouse or schema isn’t set, you must either specify the lakehouse name in your query or explicitly set it before running the query.

see if this works 

thanks

[akaur]

So far I have tried workspace.lakehuse.schema.tablename , doesnt work. 

I have tried USE SCHEMA and then queried, doesnt work

I have confirmed dbo schemas can be queried but not custom ones. 

[nilendraFabric]

Interesting @akaur  , just to make sure you have read rights to this ws and table

MS doc says 

Cross-workspace Spark SQL queries

Use the namespace "workspace.lakehouse.schema.table” to refer to tables in your code. This way, you can join tables from different workspaces if the user who runs the code has permission to access the tables.

SELECT * 
    FROM operations.hr.hrm.employees as employees 
    INNER JOIN global.corporate.company.departments as departments
    ON employees.deptno = departments.deptno;

If this don't work please raise a support ticket with MS

Please accept this answer if this is useful. So community users can find the right information quickly