none
Condition in Output statement.

    Question

  • Hi! I have annoying problem. The function that previously worked correctly does not want to compile on DLA now. Locally this function still works correctly. What happens? Visual Studio displays error:  E_CSC_USER_UDOEXCEPTION: User Error: Object reference not set to an instance of an object.

    DROP PROCEDURE IF EXISTS SaveSingleEndRowset;
    
    CREATE PROCEDURE SaveSingleEndRowset(@compress Boolean = false, @destination string, @SRR TABLE(name string, sequence string, optionalName string, qualityScore string))
    AS
    BEGIN
        
    REFERENCE ASSEMBLY [NGSQualityControl.Helper];
    REFERENCE ASSEMBLY [NGSQualityControl.Domain];
        
    OUTPUT @SRR
    TO @destination
    USING @compress ? NGSQualityControl.Domain.Factories.OutputtersFactory.GetGzipFastqOutputter() : NGSQualityControl.Domain.Factories.OutputtersFactory.GetFastqOutputter();
    
    END;

    Friday, May 20, 2016 7:36 PM

Answers

  • Ok... here is the result of my investigation.

    First I checked with built-in UDOs:

    DECLARE @test bool = true;
    
    @searchlog =
        EXTRACT UserId          int,
                Start           DateTime,
                Region          string,
                Query           string,
                Duration        int?,
                Urls            string,
                ClickedUrls     string
        FROM "/Samples/Data/SearchLog.tsv"
        USING Extractors.Tsv();
    
    @res =
        SELECT UserId,
               @test AS boolval
        FROM @searchlog;
    
    OUTPUT @res   
    TO "/output/test.csv"
    USING @test ? Outputters.Csv() : Outputters.Tsv();
    

    This however is today not supported, since the built-in UDOs are not implemented as C# factory methods but are natively implemented in the U-SQL runtime. We added a work item to our backlog to eventually support this scenario.

    Then I used a custom UDO (from this UDO code) in the following script:

    DECLARE @withtypes bool = false; // Create some interesting rowsets @simple_rs = SELECT  *
    FROM (VALUES(1, "string", (int?) 2, DateTime.Parse("2015-12-15"))                    , (2, "string2", (int?) 3, DateTime.Parse("2015-12-18"))                ) AS T(i, str, ni, dt); OUTPUT @simple_rs TO "/output/simple.txt" USING (@withtypes ? new HeaderOutputter.HeaderOutputter(quoting:false, with_types:true) : new HeaderOutputter.HeaderOutputter(quoting:false, with_types:false));

    This works in the local execution but now fails against the cluster with your error.

    I filed a bug on your behalf to get it investigated and hopefully fixed.


    Michael Rys

    Friday, June 17, 2016 12:57 AM
    Moderator

All replies

  • If it works fine locally, I'm wondering if its a problem with registering assemblies. Can you please make sure your assemblies are registered in the database you are executing your ADLA job against?

    How to verify this?

    1. In Server Explorer in Visual Studio, navigate to your ADLA account.

    2. Expand Databases and double click on the database you are running your query against.

    3. Double click on assemblies and verify if you have NGSQualityControl.Helper and NGSQualityControl.Domain registered.

    Also, can you please confirm if OutputtersFactory is  a static class and GetGzipFastqOutputter is a static function (looks like they ran successfully, but making sure). Does this work fine when you don't have the condition in your output statement?


    • Edited by Rukmani G Friday, May 20, 2016 9:33 PM
    Friday, May 20, 2016 9:14 PM
  • Yes, OutputtersFactory  is a static class and  GetGzipFastqOutputter is a static function. All assemblies are registered. I have splitted this procedure into two separate procedures without condition in OUTPUT statement and those function works fine, but I am pretty sure that this procedure works correctly earlier, but I have changed subsciption, so I had to register all assemblies, procedures and functions again.

    I tried also compile script with OUTPUT statement using build-in outputters, but still does not work on DLA account.

    @SRR_1 =
        SELECT name_r1,
               sequence_r1,
               optionalName_r1,
               qualityScore_r1
        FROM @SRR_1_2;
        
    OUTPUT @SRR_1
    TO @forward_destination
    USING true ? Outputters.Text() : Outputters.Text();


    Friday, May 20, 2016 10:49 PM
  • Thanks for reporting. It looks like some binding may have changed. I am following up with the engineering team and investigate it.

    Michael Rys

    Wednesday, June 15, 2016 11:39 PM
    Moderator
  • Ok... here is the result of my investigation.

    First I checked with built-in UDOs:

    DECLARE @test bool = true;
    
    @searchlog =
        EXTRACT UserId          int,
                Start           DateTime,
                Region          string,
                Query           string,
                Duration        int?,
                Urls            string,
                ClickedUrls     string
        FROM "/Samples/Data/SearchLog.tsv"
        USING Extractors.Tsv();
    
    @res =
        SELECT UserId,
               @test AS boolval
        FROM @searchlog;
    
    OUTPUT @res   
    TO "/output/test.csv"
    USING @test ? Outputters.Csv() : Outputters.Tsv();
    

    This however is today not supported, since the built-in UDOs are not implemented as C# factory methods but are natively implemented in the U-SQL runtime. We added a work item to our backlog to eventually support this scenario.

    Then I used a custom UDO (from this UDO code) in the following script:

    DECLARE @withtypes bool = false; // Create some interesting rowsets @simple_rs = SELECT  *
    FROM (VALUES(1, "string", (int?) 2, DateTime.Parse("2015-12-15"))                    , (2, "string2", (int?) 3, DateTime.Parse("2015-12-18"))                ) AS T(i, str, ni, dt); OUTPUT @simple_rs TO "/output/simple.txt" USING (@withtypes ? new HeaderOutputter.HeaderOutputter(quoting:false, with_types:true) : new HeaderOutputter.HeaderOutputter(quoting:false, with_types:false));

    This works in the local execution but now fails against the cluster with your error.

    I filed a bug on your behalf to get it investigated and hopefully fixed.


    Michael Rys

    Friday, June 17, 2016 12:57 AM
    Moderator
  • Thank you for your investigation.
    Friday, June 17, 2016 1:36 PM