Edit

Share via

Integrate Azure Database for PostgreSQL with Azure Machine Learning Services

  • Article

Azure AI extension gives the ability to invoke any machine learning models deployed on Azure Machine Learning online endpoints from within SQL. These models can be from the Azure Machine Learning catalog or custom models that are trained and deployed.

Prerequisites

Note

You can explore Azure Machine Learning samples.

Configure Azure Machine Learning endpoint

In the Azure Machine Learning studio, under Endpoints > Pick your endpoint > Consume you can find the endpoint URI and Key for the online endpoint. Use these values to configure the azure_ai extension to use the online inferencing endpoint.

select azure_ai.set_setting('azure_ml.scoring_endpoint','<URI>');
select azure_ai.set_setting('azure_ml.endpoint_key', '<Key>');

azure_ml.invoke

Scores the input data invoking an Azure Machine Learning model deployment on an online endpoint.

azure_ml.invoke(input_data jsonb, timeout_ms integer DEFAULT NULL, throw_on_error boolean DEFAULT true, deployment_name text DEFAULT NULL)

Arguments

input_data

jsonb json containing the request payload for the model.

deployment_name

text name of the deployment corresponding to the model deployed on the Azure Machine Learning online inference endpoint

timeout_ms

integer DEFAULT NULL timeout in milliseconds after which the operation is stopped. The deployment of a model itself can have a timeout specified that is a lower value than the timeout parameter in the user defined function. If this timeout is exceeded, the scoring operation would fail.

throw_on_error

boolean DEFAULT true on error should the function throw an exception resulting in a rollback of wrapping transactions.

max_attempts

integer DEFAULT 1 number of times the extension retries calling the Azure Machine Learning endpoint if it fails with any retryable error.

retry_delay_ms

integer DEFAULT 1000 amount of time (milliseconds) that the extension waits, before calling the Azure Machine Learning endpoint, when it fails with any retryable error.

Return type

jsonb scoring output for the model that was invoked in JSONB.

Examples

Invoke the machine learning model

This calls the model with the input_data and returns a jsonb payload.

-- Invoke model, input data depends on the model.
  SELECT * FROM azure_ml.invoke('
  {
    "input_data": [
      [1,2,3,4,5,6,7,8],
      [-1,-2,-3,-4,-5,-6,-7,-8]
    ],
    "params": {}
  }', deployment_name=>'Housingprediction' )

-- Get JSON elements from model output
SELECT jsonb_array_elements(invoke.invoke) as MedianHousePrediction
FROM azure_ml.invoke('
{
  "input_data": [
    [1,2,3,4,5,6,7,8],
    [-1,-2,-3,-4,-5,-6,-7,-8]
  ],
 "params": {}
}', deployment_name=>'Housingprediction' )

Related content