Forum Discussion

Element115's avatar
Element115
Memorable Member
2 years ago
Solved

MISSING QUERY FEATURE from Lookup activity

QUESTION:

 

Is there a way, after pointing a pipeline Lookup activity to a lakehouse, to actually query said LH to get back a singleton?  For ex:  let's say you have a column named INDEX. You want to lookup the maximum INDEX value currently stored in said column.  Can you specify the following SQL query?

 

SELECT MAX([INDEX]) 

FROM LH.TABLE

 

I don't see anything in the Lookup GUI that allows to do this.  Is there a way?

 

ISSUE:

In Azure pipeline, when the Lookup activity references an Azure SQL DB:

 

In Fabric, when the Loopup activity references a lakehouse (let's not forget that a LH has a SQL endpoint, therefore...):

 

So the question is:

 

How are we supposed to lookup anything in a LH table when you we can't even specify a SQL query???  

 

I understand a LH and an Azure SQL DB is different, but since a SQL endpoint is provided to query a LH, it stands to reason to find the same feature, ie creating custom SQL queries, in an activity whose name is Lookup.  I trust I don't need to belabor the point as it should be self-evident.

Data Engineering
  • Element115's avatar
    Element115
    2 years ago

    I found why the following T-SQL statement was not being processed correctly:

     

    SELECT MAX(stationId)
FROM dbo.EcoCount_Station

     

     

    Instead, if you add an alias, like so:

     

    SELECT MAX(stationId) AS result 
FROM dbo.EcoCount_Station

     

    then it works.

     

    This is the kind of stuff that an intelligent engine can auto add transparently and the user will see the activity work properly.  Plus, the GUI could pop up an advisory bubble notifying the user of how to specify the T-SQL statement while at the same time telling the user that it automatically did it for him. Not rocket science. 

     

    To be perfectly honest, I am so sick and tired of this BS, always trying to guess how we are supposed to enter config or SQL statements in the GUI.  I mean it's mindboggling that we are still at this a year after the so-called GA release of Fabric. And this is just basic functionality we are talking about, not landing a rocket on Mars.  ğŸ™ƒ  

15 Replies

  • Element115's avatar
    Element115
    Memorable Member
    2 years ago

    So I just did a test running Lookup with First row unchecked.  And it returns 5,000 rows by default all in some kind of arbitrary order.  Somebody please explain to me how is that useful?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hello Element115 ,

    Thanks for using Fabric Community.
    At this time, we are reaching out to the internal team to get some help on this .
    We will update you once we hear back from them.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Element115 ,

      It is confirmed from internal team that it is as per the design.

      We're always looking for ways to improve. Appreciate if you could share the feedback on our feedback channel. Which would be open for the user community to upvote & comment on. This allows our product teams to effectively prioritize your request against our existing feature backlog and gives insight into the potential impact of implementing the suggested feature.

       

      In the meantime,  if you are looking for a method to query the lakehouse they you can achieve a similar result using a script activity.

      Hope this helps. Please let me know if you have any further queries.

      • Element115's avatar
        Element115
        Memorable Member
        2 years ago

        By design!?!!!!!?????   What the hell kind of design is that? Can you please explain what is the intent of a Lookup activity?