Forum Discussion

Element115's avatar
Element115
Icon for Memorable Member rankMemorable 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 ...
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.  ğŸ™ƒ  

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
Element115
Icon for Memorable Member rankMemorable Member
2 years ago

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.