topic Re: How do I query custom schemas in lakehouses using sql magic cell ? in Data Engineering

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

akaur — Wed, 29 Jan 2025 18:52:24 GMT

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

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

nilendraFabric — Wed, 29 Jan 2025 19:20:05 GMT

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

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

nilendraFabric — Wed, 29 Jan 2025 19:24:01 GMT

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

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

akaur — Wed, 29 Jan 2025 19:26:57 GMT

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.

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

nilendraFabric — Wed, 29 Jan 2025 19:30:49 GMT

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

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

v-menakakota — Mon, 03 Feb 2025 07:21:37 GMT

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.

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

v-menakakota — Thu, 06 Feb 2025 07:10:14 GMT

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.

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

akaur — Thu, 06 Feb 2025 13:07:00 GMT

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

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

nilendraFabric — Thu, 06 Feb 2025 13:15:46 GMT

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