AnalyticDB:UDFs

When the REMOTE_CALL() function is called, AnalyticDB for MySQL sends data to Function Compute in JSON format. After the data is processed, Function Compute returns the result in JSON format. You must write the user-defined function in Function Compute to conform to the specified data sending and returning formats.

{
  "rowCount": 3, 
  "compressed":true, 
  "data": 
  [
      [1, "a", "2023-08-22 11:30:00"],
      [2, "b", "2023-08-22 12:30:00"],
      [3, "c", null]
  ]
}

{
  "success": true,   
  "message": "",  
  "result" : [  ] 
}

In this example, the user-defined function created in Function Compute is named ConcactNumberWithCompress. The following is the sample code:

public class App
        implements StreamRequestHandler, FunctionInitializer
{
    public static final Logger log = Logger.getLogger(App.class.getName());

    public void initialize(Context context)
            throws IOException
    {
        // TODO
    }

    @Override
    public void handleRequest(
            InputStream inputStream, OutputStream outputStream, Context context)
            throws IOException
    {
        InputStream notCompressedInputStream1;
        notCompressedInputStream1 = tryUnCompress(inputStream);

        JSONObject response = new JSONObject();
        try {
            JSONObject requestJson = JSONObject.parseObject(IOUtils.toString(notCompressedInputStream1));
            if (requestJson.containsKey("data")) {
                JSONArray result = new JSONArray();
                JSONArray data = requestJson.getJSONArray("data");
                for (int i = 0; i < data.size(); i++) {
                    JSONArray row = data.getJSONArray(i);
                    // Implement the business logic of Function Compute here.
                    // Assume that the function has two input parameters, a and b. Each row of data sent from AnalyticDB for MySQL contains two numbers. The first number is a, and the second number is b.
                    if (row.size() == 2) {
                        result.add(testFunc(row.getInteger(0), row.getInteger(1)));
                    }
                    else {
                        throw new RuntimeException("row size is not 2");
                    }
                    // The business logic of Function Compute ends here.
                }

                response.put("result", result);
                response.put("success", true);
                response.put("message", "");
            }
            else {
                response.put("success", false);
                response.put("message", "no data inside");
            }
        }
        catch (Exception e) {
            log.info("error happened" + e.getMessage());
            response.put("success", false);
            response.put("message", e.getMessage());
        }

        outputStream.write(tryCompress(response.toJSONString().getBytes()));
    }

    private String testFunc(int a, int b)
    {
        // The test function concatenates the input parameters a and b and the number 1 using ampersands (&).
        return String.valueOf(a) + '&' + b + '&' + 1;
    }

    public static byte[] tryCompress(byte[] bytes)
    {
        ByteArrayOutputStream byteOutputStream = new ByteArrayOutputStream();
        try {
            GZIPOutputStream gzipOutputStream = new GZIPOutputStream(byteOutputStream);
            gzipOutputStream.write(bytes);
            gzipOutputStream.close();
        }
        catch (IOException e) {
            throw new RuntimeException(e);
        }

        return byteOutputStream.toByteArray();
    }

    public static InputStream tryUnCompress(InputStream inputStream)
            throws IOException
    {
        GZIPInputStream gzipInputStream;
        gzipInputStream = new GZIPInputStream(inputStream);
        return gzipInputStream;
    }
}

Examples of calling the REMOTE_CALL function in AnalyticDB for MySQL

• If no global parameters are configured, call the ConcactNumberWithCompress user-defined function to concatenate 1 and 2 with an ampersand (&):

  SELECT remote_call('varchar','ConcactNumberWithCompress','{endpoint:"1234567890000****.cn-zhangjiakou-internal.fc.aliyuncs.com"}',1,2);

  The following result is returned:

  1&2&1

• If no global parameters are configured, call the ConcactNumberWithCompress user-defined function to concatenate 3 and 4 with an ampersand (&):

  SELECT remote_call('varchar','ConcactNumberWithCompress','{endpoint:"1234567890000****.cn-zhangjiakou-internal.fc.aliyuncs.com",compressed:false,max_batch_size:5000000}',3,4);

  The following result is returned:

  3&4&1

• If global parameters are configured, call the ConcactNumberWithCompress user-defined function to concatenate 5 and 6 with an ampersand (&):

  SELECT remote_call('varchar','ConcactNumberWithCompress',null,5,6);

  The following result is returned:

  5&6&1

• AnalyticDB for MySQL compresses data into the GZIP format and sends it to Function Compute. However, the user-defined function created in Function Compute does not contain decompression code. As a result, Function Compute cannot parse the data.

• AnalyticDB for MySQL sends uncompressed data to Function Compute. After processing, Function Compute compresses the result using the GZIP format and returns it to AnalyticDB for MySQL. As a result, AnalyticDB for MySQL cannot parse the data.