MISSING QUERY FEATURE from Lookup activity

[Element115]

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.

[uselessai_in]

while the issue has been resolved. we got new issue. you need to fix it while deploying lookup activity form dev to stg. 

check this - https://uselessai.in/microsoft-fabric-interesting-and-hidden-deployment-observations-important-bug-f...

[Anonymous]

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]

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]

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

[Anonymous]

Hi @Element115 ,

Similar to ADF, fabric also has capability to run queries on Azure SQL database connector when we use External Datastore option. Kindly check the below screenshot for reference. 

However , in case you want to query the lakehouse tables, you need to use Script activity. Depending on the connector , features in lookup activity changes . So, kindly consider using Script activity .

Thankyou 

[Element115]

I just tried the Script activity and it won't do.  Look at this, it only gives me access to the default staging DW.  But all my data sits in a lakehouse!  It is the LH SQL endpoint I need to query, not a DW.  

So the Script activity is neither a substitute nor a solution to palliate the shortcomings of the Lookup activity.  I guess all that remains is to use a Notebook.  Can't wait for the fix.

[GetBetterSoon]

Guess what... it is still not fixed. This is the last time I'm working with fabric.

[Anonymous]

Hi @Element115 ,

Take the SQL analytics endpoint from the lakehouse to create a new cloud connection - use External as an option and run the query against the cloud connection within the pipeline. Just ran and works.

Steps to connect to Lakehouse Endpoint using Script Activity:

1. Copy the SQL Endpoint from Lakehouse.

2. In Script, click on External and then Azure SQL Database, fill the details and use Organizational Account to establish connection.

3. Check the connection 

4. Execute it

Note: Support for SQL endpoint for Lakehouse is in our backlog but I don't have a ETA.

Hope this is is helpful. Please let me know incase of further queries.

[Element115 · Accepted solution]

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

[Anonymous]

Hi @Element115 ,

It's expected to have alias so as to get the output as key value pair in json.

Thank you

[Element115]

Yes, once you know that, seems self-evident.  There should be a short 'In a Nutshell' ref website where all this is listed, instead of having to read to 10s and 10s of pages of documentation.

[Element115]

So I tried what you suggested. Unfortunately, it doesn't work when using the following T-SQL statement:

SELECT MAX(stationId) 
FROM dbo.EcoCount_Station

and ends with this error:

If I use this statement instead:

SELECT *
FROM dbo.EcoCount_Station

it works, but it is the wrong statement to use for the goal at hand.

I checked that the first statement is OK by running it from the SQL endpoint editor and there was no issue. 

So there is obviously an issue with the Script activity and T-SQL functions such as MAX(), everything else being equal. 

So we are back to square one or a PySpark Notebook (which I haven't tried yet, so perhaps even a Notebook won't work).

And honestly, I am getting so tired of doing the beta testing on my own time with no pay for Microsoft's QA/QC team.

[Element115]

Yes, I get all that.  However, in Fabric, you have 4 GUIs to interact with a Lakehouse in query mode:  notebook, dataflow, lakehouse view, and the SQL Analytics endpoint.  It is obvious why the latter is provided as the vast majority of users come with a SQL background. THEREFORE if you tell such a user you can look up records with a Lookup activity, they certainly will expect to see a Query or Stored procedure radio button as you are showing when connecting to an Azure SQL DB.  

Whether the source is in the workspace or external should be immaterial.  If the source exposes a SQL interface, then logically that interface should be available from anything that has the name 'Lookup.'   

Now, you speak of using the Script activity.  What language are these scripts written in?  SQL? 

[Element115]

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?