Can't we upload documents/Image using USQL Custom Code and usql?

Question

Situation : We have created database say "CLSTrackOMeter" and table say "Customer_Information" in Azure data lake Analytics.

Customer_Information, stores the path of image in staging folder( For now i've hard code the source image path in class library).

Agenda : use that value from CustInfo to upload data to Azure data lake store "Customer_Image" folder

Tried Solution - Created usql class library, using .net sdk to upload files(Able to execute this class library in console application), and deployed in azure data lake store. - Added new USQL script and referenced this Class library

Called Class library in cs file of usql script

Code of Class Library

 using Microsoft.Analytics.Interfaces;
    using Microsoft.Analytics.Types.Sql;
    using System;
    using System.Collections.Generic;
    using System.IO;
    using System.Linq;
    using System.Text;
    using Microsoft.Azure.Management.DataLake.Store;
    using Microsoft.Azure.Management.DataLake.Store.Models;
    using Microsoft.IdentityModel.Clients.ActiveDirectory;
    using Microsoft.Rest.Azure.Authentication;
    using Microsoft.Azure.Management.DataLake.StoreUploader;
    using System.Threading;
    using System.Diagnostics;
    using System.Collections;
    using System.Threading.Tasks;

    namespace USQLCSharpProject1
    {
        public static class  Program
        {
            private static DataLakeStoreAccountManagementClient _adlsClient;
            private static DataLakeStoreFileSystemManagementClient _adlsFileSystemClient;

            private static string _adlsAccountName;
            private static string _resourceGroupName;
            private static string _location;
            private static string _subId;

            //private static void Main(string[] args)
            public static string UploadFileWithS2S_WithClientSecret(string s)
            {

                try
                {
                    _adlsAccountName = "<DATA-LAKE-STORE-NAME>"; // TODO: Replace this value with the name of your existing Data Lake Store account.
                    _resourceGroupName = "<RESOURCE-GROUP-NAME>"; // TODO: Replace this value with the name of the resource group containing your Data Lake Store account.
                    _location = "East US 2";
                    _subId = "<SUBSCRIPTION-ID>";



                    string localFolderPath = @"D:\Harry\PSR\study\TEST"; // TODO: Make sure this exists and can be overwritten.
                    string localFilePath = Path.Combine(localFolderPath, "fileTwo.txt"); // TODO: Make sure this exists and can be overwritten.
                    string remoteFolderPath = "/Samples/OUTPUT";
                    //string remoteFilePath = Path.Combine(remoteFolderPath, "file.txt");


                    // Service principal / appplication authentication with client secret / key
// Use the client ID of an existing AAD "Web App" application.
SynchronizationContext.SetSynchronizationContext(new SynchronizationContext());

var domain = "<AAD-directory-domain>";
var webApp_clientId = "<AAD-application-clientid>";
var clientSecret = "<AAD-application-client-secret>";
 var clientCredential = new ClientCredential(webApp_clientId, clientSecret);
var creds = ApplicationTokenProvider.LoginSilentAsync(domain,clientCredential).Result;









                    // Create client objects and set the subscription ID
                    _adlsClient = new DataLakeStoreAccountManagementClient(creds) { SubscriptionId = _subId };
                    _adlsFileSystemClient = new DataLakeStoreFileSystemManagementClient(creds);

                    var parameters = new UploadParameters(localFolderPath, remoteFolderPath, _adlsAccountName, isOverwrite: true); // the default  maxSegmentLength is 256M, we can set by ourself.
                    var frontend = new DataLakeStoreFrontEndAdapter(_adlsAccountName, _adlsFileSystemClient);
                    var uploader = new DataLakeStoreUploader(parameters, frontend);
                    uploader.Execute();
                    return s;
                }

                catch (Exception ex)
                {
                    return "";
                }
            }


        }
    }

Code of Usql

USE CLSTrackOMeter;
REFERENCE ASSEMBLY USQLCSharpProject1;
@result =
     SELECT  USQLUploadFile.myFirstClass.myFirstFunction(AgeGender)AS myFirstFunction_CB
    FROM CLSTrackOMeter.dbo.Customer_Information;

Code of USQL cs File

using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace USQLUploadFile
{
    public class myFirstClass
    {
        public static string myFirstFunction(string s)
        {
            try
            {

                string aa = USQLCSharpProject1.Program.UploadFileWithS2S_WithClientSecret("rajni");
                return aa;

            }
            catch (Exception ex)
            {
                return "";
            }




        }
    }
}

Project Image

Error Image

Error While using PROCESS Expression

USQL Code for PROCESS Expression

USE CLSTrackOMeter;
REFERENCE ASSEMBLY USQLCSharpProject1;
   @result = SELECT AgeGender
    FROM CLSTrackOMeter.dbo.Customer_Information;

    @rs=
    PROCESS @result
    PRODUCE AgeGender
    USING new USQLUploadFile.myFirstClass();


    OUTPUT @rs   
    TO "/output/Harry.csv"
      USING Outputters.Csv();

USQL CS File Process Expression Code

using Microsoft.Analytics.Interfaces;
using Microsoft.Analytics.Types.Sql;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace USQLUploadFile
{
    [SqlUserDefinedProcessor]
    public class myFirstClass : IProcessor
    {
        public override IRow Process(IRow input, IUpdatableRow output) 
        {
            try
            {
                string AgeGender = input.Get<string>("AgeGender");
                //USQLCSharpProject1.Class1 obj = new ClassLibrary1.Class1();
                //string aa = USQLCSharpProject1.Program.UploadFileWithS2S_WithClientSecret("rajni");
                //return aa;
                string aa=USQLCSharpProject1.Program.UploadFileWithS2S_WithClientSecret("AgeGender");
                output.Set<string>("AgeGender", AgeGender);
                return output.AsReadOnly();

                //return obj.newTest(s);
            }
            catch (Exception ex)
            {
                return null;

            }



        }
    }
}

addition:

After Registring .Net SDK libraries and referring them in USQL

Submitting job,shows below text in Output

System.InvalidOperationException: Collection was modified; enumeration operation may not execute.
   at System.ThrowHelper.ThrowInvalidOperationException(ExceptionResource resource)
   at System.Collections.Generic.List`1.Enumerator.MoveNextRare()
   at System.Collections.Generic.List`1.Enumerator.MoveNext()
   at Microsoft.Cosmos.ScopeStudio.VsExtension.ProjectSystem.ScopeProjectNode.get_ReferenceInfoList()
   at Microsoft.Cosmos.ScopeStudio.BusinessObjects.Common.ScriptToProjectTable.GetProjectReferenceList(String scriptFilePath)
   at Microsoft.Cosmos.ScopeStudio.UserInterface.SQLIP.BaseSubmissionViewModel`1.GetScriptContentsWithReference(ProductFunctionType productType)
   at Microsoft.Cosmos.ScopeStudio.UserInterface.SQLIP.DataLakeJobSubmissionViewModel`1.DoJobSubmission()

Adding Additional Reference Img

Register Assembly in ADLS

Michael Rys Michael Rys · Accepted Answer · 2017-05-12T07:37:15

I am a bit puzzled in what you are trying to achieve. Are you trying to call the Azure SDK calls from within a U-SQL User-defined operator? That will not work since the U-SQL containers do not allows calling web services APIs, including the data lake REST APIs.

Can't we upload documents/Image using USQL Custom Code and usql?

2 Answers