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 thou...
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

     

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