Call Azure Cosmos DB UDF function from a powershell script

Question

I have a UDF function in Cosmos DB , it takes a parameter and returns the documents that meets the condition based on the parameter.

Each document returned by this UDF has 3 fields,

• Customer ID
• Modified Date
• Customer Status

I need this information in a SQL Server SP present in another database.

I am thinking of having a powershell script to bring this data from the Cosmos DB , store it in a table local to the SQL server database , and then use this table eventually in the SP.

I wondering if my above approach to fetch data from Cosmos DB to SQL Server database is right, and if so could I know if we can execute a cosmos DB UDF from a powershell script and use the result set returned by the UDF.

Answer 1

Based on your description,maybe you could use Azure Data Factory.

Step1: Follow the article to create Copy activity.

Step2: Configure Cosmos db source data:

sql:

SELECT udf.adf(c.fields).CustomerID,
udf.adf(c.fields).ModifiedDate,
udf.adf(c.fields).CustomerStatus FROM c

Then,please follow the steps from this doc:

Step 3: Configure your Sink dataset:

Step 4: Configure Sink section in copy activity as follows:

Step 5: In your database, define the table type with the same name as sqlWriterTableType. Notice that the schema of the table type should be same as the schema returned by your input data.

    CREATE TYPE [dbo].[CsvType] AS TABLE(
    [ID] [varchar](256) NOT NULL,
    [Date] [varchar](256) NOT NULL,
    [Status ] [varchar](256) NOT NULL
)

Step 6: In your database, define the stored procedure with the same name as SqlWriterStoredProcedureName. It handles input data from your specified source, and merge into the output table. Notice that the parameter name of the stored procedure should be the same as the "tableName" defined in dataset.

Create PROCEDURE convertCsv @ctest [dbo].[CsvType] READONLY
AS
BEGIN
  MERGE [dbo].[adf] AS target
  USING @ctest AS source
  ON (1=1)
  WHEN NOT MATCHED THEN
      INSERT (id,data,status)
      VALUES (source.ID,source.Date,source.Status );
END