Hologres:User-defined functions (Remote UDFs)

Function overview

Remote functions allow you to extend data processing and analysis capabilities by integrating external functions into Hologres. In Hologres V3.1, remote functions support calls to the Alibaba Cloud Function Compute (FC) service. This feature lets you dynamically call FC to process complex business logic or perform advanced data operations while you query Hologres data.

Remote functions support the following three scenarios:

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

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

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

Prerequisites

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

• You have activated Function Compute, and your FC instance is V3.0. To do this, log on to the Function Compute console.

• You have granted the AliyunServiceRoleForHologresRemoteUDF service-linked role.

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

  2. Select AliyunServiceRoleForHologresRemoteUDF and click Authorize Now. The following figure shows an example.

     

Precautions

• Only scalar user-defined functions (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 constants as input parameters.

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

• This feature does not incur extra fees in Hologres.

Manage user-defined functions

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 interaction format

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

Example of a user-defined function

This example uses the unnest function.

1. Activate Function Compute.

   Log on to the Function Compute console. You can also claim a free resource plan with a specific quota based on the on-screen instructions. For more information, see Trial quotas.

2. Create an FC event-triggered function.

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

   2. On the Functions page, click Create Function. You are then redirected to the Create Function page.

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

      Parameter	Description
      Function Name	Enter a custom name. For example, unnest.
      Runtime	Select Built-in Runtimes / Python / Python 3.10.
      Code Upload Method	Select Upload Code via ZIP Package.
      Code Package	Upload the code package that you have written and packaged. Save the following code to a file named unnest.py and compress it into 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

   You can complete the following steps on the HoloWeb platform. 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)