Forum Discussion

akaur's avatar
akaur
Regular Visitor
1 year ago
Solved

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

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 

 

lakehouse
  • v-menakakota's avatar
    v-menakakota
    1 year ago

    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.

8 Replies

  • nilendraFabric's avatar
    nilendraFabric
    Super User
    1 year ago

    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's avatar
      akaur
      Regular Visitor
      1 year ago

      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's avatar
        nilendraFabric
        Super User
        1 year ago

        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 

  • v-menakakota's avatar
    v-menakakota
    Community Support
    1 year ago

    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.

  • v-menakakota's avatar
    v-menakakota
    Community Support
    1 year ago

    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's avatar
      akaur
      Regular Visitor
      1 year ago

      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