Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Query Folding with Databricks.Catalogs Connector

Hi all,

 

I'm using Databricks.Catalogs connector to get data from Databricks Database Tables in the import mode (using the online Dataflows). 

The problem is that my query is not folded even though I do not use any complex transformation logic. However, when I inspect the Query plan for the last step of the query, I can see only nodes which are folded.

 

What may be the causes behind the query not folding in this scenario?

 

 

 

Thank you.

 

Boris.

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    I found a great solution and does not break Databricks Query Folding 

     

    use Databricks.Query instead of Databricks.Catalogs

     

    If you want to lightweight your loading process

     

    create a parameter  like date_filter

     

    let
    Source = Databricks.Query("warehouses" , [] ),
    part1 = "select * from TYPE YOUR TABLE",
    part2 = " WHERE snapshot_date > "&"'"&date_filter&"'",
    MyQuery = Source(part1 & part2)
in
    MyQuery

     

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    I found a great solution and does not break Databricks Query Folding 

     

    use Databricks.Query instead of Databricks.Catalogs

     

    If you want to lightweight your loading process

     

    create a parameter  like date_filter

     

    let
    Source = Databricks.Query("warehouses" , [] ),
    part1 = "select * from TYPE YOUR TABLE",
    part2 = " WHERE snapshot_date > "&"'"&date_filter&"'",
    MyQuery = Source(part1 & part2)
in
    MyQuery

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hello gustavoleo_RJ,

      thank you. This seems to help with my issue indeed.

      For some reason I can't find the documentation of the Databricks.Query in the M language reference. Do you know if it is there?

       

      Thank you.

       

      Kind regards,

       

      Boris.

    • cristiano_lopes's avatar
      cristiano_lopes
      New Member
      3 years ago

      Done:

      = let
      Source= Databricks.Query("xxxxx.cloud.databricks.com", "/sql/1.0/warehouses/xxxxxxx", [Catalog="hive_metastore", Database="schema_xxxx", EnableAutomaticProxyDiscovery=null]),

      hive_metastore_Database = Fonte("SELECT * FROM hive_metastore.schema_xxxx.tablename T")
      in
      hive_metastore_Database

      • Adrien_Vetterl1's avatar
        Adrien_Vetterl1
        Regular Visitor
        9 months ago

        Just a clarification from the above post. The Databricks.Query becomes a method which needs to be called with the query string, the above is usingSource/Fonte (Source in Spanish):

        = let
MyDatabricksConnector = Databricks.Query("workspace_url", "warehouse_endpoint", [Catalog="mycatalog", Database=null, EnableAutomaticProxyDiscovery=null]),
Data = MyDatabricksConnector("SELECT * FROM mycatalog.myschema.mytablename")
in
Data

         query 

  • badac002's avatar
    badac002
    Frequent Visitor
    2 years ago

    Bumping this topic. Can someone point me to a guide on query folding in Power Query with the Azure Databricks connector?

     

    The behavior mentioned in the above post is still present in 2024 in Power Query Online. In my opinion, it is unacceptable that simply selecting a table from Unity Catalog yields a "Will not fold" indicator. Even in Power BI Desktop, users will not see the "View Native Query" option, which would indicate the query is folding. I should not have to instruct users to use the Azure Databricks connector and then edit their M code to switch to Databricks.Query (which has no documentation other than this random LinkedIn post https://www.linkedin.com/uas/login?session_redirect=https%3A%2F%2Fwww.linkedin.com%2Fpulse%2Fquery-folding-azure-databricks-tushar-desai)

    I can't be the only only one having trouble with this. I am trying to instill best practices with my community, and it is difficult when the Azure Databricks connector behaves so differently from others. Can anyone point me to a proper guide on how users can be sure their queries are folding with Databricks?

    • Stachu's avatar
      Stachu
      Community Champion
      2 years ago

      It seems that when the native query is provided in the connection UI the fold is also not breaking. PowerBI wraps the Databricks.Catalog in Value.NativeQuery, like this:

      let
    Source = Value.NativeQuery(Databricks.Catalogs(server, http, [Catalog="samples", Database=null, EnableAutomaticProxyDiscovery=null]){[Name="samples",Kind="Database"]}[Data], "select * from nyctaxi.trips", null, [EnableFolding=true])
in
    Source

      When analysed using the Diagnose feature it shows the source query is folded even though the View Native Query option is greyed out, but I must say I didn't verify it on the Databricks end

  • v-jingzhang's avatar
    v-jingzhang
    Community Support
    4 years ago

    Hi Anonymous 

     

    Can you show the M code of this query? You can open Advanced Editor and find all code there. Or you can use Query folding indicators to check which steps are preventing your query from folding. Reorder them if necessary to increase folding.

     

    Best Regards,
    Community Support Team _ Jing

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hello Jing,

       

      thank you for your message.

       

      • the M code is:
      let
      Source = Databricks.Catalogs(#"Server Hostname", #"HTTP Path"),
        Navigation = Source{[Name = #"Database Name", Kind = "Database"]}[Data],
        #"Navigation 1" = Navigation{[Name = #"Schema Name", Kind = "Schema"]}[Data],
        #"Navigation 2" = #"Navigation 1"{[Name = #"Table Name", Kind = "Table"]}[Data]
      in
        #"Navigation 2"
       
      Unfortunately, I'm strungling to understand which step migh prevent the query folding, as there is no other data transformation besides the unpacking of the content of the [Data] columns into the final table.
       
       
      Kind regards,
      Boris.