Remote UDFs

Overview

Remote functions extend the data processing and analysis capabilities of Hologres by integrating external functions from services like Alibaba Cloud Function Compute (FC). This integration allows you to dynamically call FC functions when querying Hologres data to process complex business logic or perform advanced data operations.

Remote functions support the following use cases:

• Real-time data processing: Call external functions during data queries to perform data cleansing, format conversion, or complex calculations.

• Third-party service integration: Use Function Compute to interact with other Alibaba Cloud services or third-party APIs, using your Hologres data.

• Advanced analytics: Use Function Compute to run advanced analytics algorithms, such as model inference and machine learning, and write the results directly back to Hologres.

Prerequisites

• You have a Hologres instance of V3.1 or later. For more information, see Purchase a Hologres instance.

• You have activated Function Compute and are using a V3.0 instance. To activate the service, log on to the Function Compute console.

• The AliyunServiceRoleForHologresRemoteUDF service-linked role is authorized.

  1. Log on to the Hologres console. In the left-side navigation pane, click Create a Service-linked Role.

  2. Select AliyunServiceRoleForHologresRemoteUDF and click Authorize Now.

Limitations

• Only scalar UDFs and user-defined table-valued functions (UDTFs) are supported. User-defined aggregate functions (UDAFs) are not supported.

• Only the following data types and their corresponding array types are supported: BOOLEAN, INTEGER, BIGINT, REAL, DOUBLE PRECISION, and TEXT.

• Remote functions do not support constant input parameters.

• This feature incurs costs in Function Compute (FC). For more information, see Billing.

• This feature does not incur extra costs in Hologres.

• Remote UDFs can be executed only in the Hologres query engine (HQE). They cannot be used in the same SQL statement with built-in functions that are executed only in the PostgreSQL-compatible query engine (PQE), such as encryption and decryption (PGCRYPTO) extension functions. Using them together causes an engine execution conflict and returns the following error.

  ERROR: ORCA failed to produce a plan : No plan has been computed for required properties

  To use a Remote UDF and a PQE-only built-in function at the same time, split the operations into separate SQL statements and execute them individually.

Manage remote UDFs

CREATE EXTENSION [ IF NOT EXISTS ] function_compute;

CREATE [ OR REPLACE ] FUNCTION <function_name>
  ( [ [ argmode ] [ argname ] <argtype> [ { DEFAULT | = } default_expr ] [, ...] ] )
    [ RETURNS [SETOF] rettype | RETURNS TABLE ( column_name column_type) ]
  LANGUAGE function_compute
  AS '<fc_endpoint>/<func_name>'
  {
    { CALLED ON NULL INPUT | RETURNS NULL ON NULL INPUT | STRICT }
    | SET function_compute.qualifier TO <qualifier>
    | SET function_compute.compression TO <compression>
    | SET function_compute.max_batch_size TO <max_batch_size>
  } ...

SELECT
  CASE
    WHEN p.proretset = 'f' THEN 'scalar UDF'
    WHEN p.proretset = 't' THEN 'UDTF'
  END AS function_type,
  n.nspname AS schema_name,
  p.proname AS function_name,
  pg_get_function_arguments(p.oid) AS arguments,
  pg_get_function_result(p.oid) AS return_type,
  p.proisstrict AS is_strict,
  p.proconfig AS config,
  pg_get_functiondef(p.oid) AS definition
FROM
  pg_proc p
  JOIN pg_language l ON p.prolang = l.oid
  JOIN pg_namespace n ON p.pronamespace = n.oid
WHERE
  l.lanname = 'function_compute'
  AND p.prokind != 'p'
ORDER BY
  function_type,
  schema_name,
  function_name;

 DROP FUNCTION [ IF EXISTS ] <function_name> [ ( [ [ argmode ] [ argname ] <argtype> [, ...] ] ) ] [, ...]

DROP FUNCTION rf_add(INTEGER, INTEGER);

Data exchange format

{  
  "data": [  
    ["foo", 100, true],
    [null, null, false],
    ["bar", 200, false]
  ]
}  

Example

This example uses the unnest function.

1. Activate Function Compute.

   Log on to the Function Compute console. You can also claim a free resource package based on the on-screen prompts. For more information, see Free Tier.

2. Create an FC Event Function.

   1. In the left-side navigation pane, click Functions. Switch to the region where your Hologres instance resides.

   2. On the Functions page, click Create Function. The Create Function page opens.

   3. Select Event Function and configure the following parameters. Retain the default settings for other parameters. For more information, see Create an event-triggered function.

      Parameter	Description
      Function name	A custom name. For example, unnest.
      Runtime	Select Built-in Runtimes / Python / Python 3.10.
      Code upload method	Select Upload ZIP.
      Code package	Upload the code package. Save the following code as unnest.py and compress it into a file named unnest.zip. import json def unnest(event, context): evt = json.loads(event) data = evt.get('data', None) if data is None: raise ValueError('no "data" key in event.') if not isinstance(data, list): raise ValueError('data is not a list.') res = list() for i in range(len(data)): if len(data[i]) != 1 or not isinstance(data[i], list): raise ValueError('the item in data is not a list.') for item in data[i][0]: res.append([i, item]) return json.dumps({'results': res})
      Handler	Enter unnest.unnest.

3. Create a Hologres remote function.

   Note

   Perform the following steps in HoloWeb. For more information, see Connect to HoloWeb.

   CREATE EXTENSION IF NOT EXISTS function_compute;
   CREATE OR REPLACE FUNCTION rf_unnest(INTEGER [])
     RETURNS SETOF INTEGER
   STRICT
   LANGUAGE function_compute
   AS 'xxxxxxxxxxxxxxxxx.cn-shanghai-internal.fc.aliyuncs.com/unnest';

4. Prepare test data.

   CREATE TABLE test_array (
       numbers INTEGER[]
   );
   INSERT INTO test_array (numbers) VALUES
     (ARRAY[1, 3]),
     (ARRAY[2, 4]),
     ('{}'),  
     (ARRAY[]::INTEGER[]),  
     (NULL);

5. Call the remote function.

   SELECT numbers, rf_unnest(numbers) FROM test_array;
    numbers | rf_unnest
   ---------+-----------
    {2,4}   |         2
    {2,4}   |         4
    {1,3}   |         1
    {1,3}   |         3
   (4 rows)