Is it possible to read data from Azure Data base table and write it to Azure Data Lake file with Azure functions?

Question

I am trying to implement a time trigger function, which connects to Azure DB and get the contents from table. After this I'm looking out for the possibilities to write this data to a Azure Data lake file using BCP. Below is my function and Json Script

#r "System.Configuration"
#r "System.Data"
#r "System.IO"
using System.Configuration;
using System.Data.SqlClient;
using System.Threading.Tasks;
using System;
using System.IO;
using System.Net;
public static void Run(TimerInfo myTimer, TraceWriter log)
{
    var str = ConfigurationManager.ConnectionStrings["sqldb_connection"].ConnectionString;
    log.Info($"C# Timer trigger function executed at: {DateTime.Now}");
    using (SqlConnection conn = new SqlConnection(str))
    {
    conn.Open();
    System.Diagnostics.Process proc = new System.Diagnostics.Process();
    proc.StartInfo.FileName = "bcp";
    proc.StartInfo.Arguments = "\"select * from Person \""+ "queryout 'DatalakeFilepath' -S test-server.database.windows.net -U username -P password  -d database -N";
    proc.Start();
    }
}


//Json:
{
  "bindings": [
    {
      "name": "myTimer",
      "type": "timerTrigger",
      "direction": "in",
      "schedule": "0 */5 * * * *"
    }
  ],
  "disabled": false
}

2017-06-19T11:29:31.957 Exception while executing function: Functions.OcDataTimerTriggerCSharp. mscorlib: Exception has been thrown by the target of an invocation. System: The system cannot find the file specified.

It is possible. Please elaborate on which exact problem you are facing and what you did to solve it so far. Otherwise, the question is too broad. — Mikhail Shilkov

saveenr saveenr · Accepted Answer · 2017-06-19T13:52:05

(Azure Data Lake team here)

Writing to Azure Data Lake Store (ADLS) from an Azure Function is possible - but not in the way you you are doing it in the code above.

You can't write to the ADLS filesystem as if it is a local drive or fileshare. So, calling BCP with a destination path in an ADLS account will not work.

There are a couple of options for you. An Azure Function could:

Run an ADF pipeline
Start a U-SQL job to read from the Azure SQL and write to ADLS.
Manually read from the SQL DB row-by -row and write the rows into ADLS

Is it possible to read data from Azure Data base table and write it to Azure Data Lake file with Azure functions?

1 Answers