AnalyticDB:UDFs

After you invoke the REMOTE_CALL() function, AnalyticDB for MySQL sends data to Function Compute in the JSON format. Function Compute computes the data and returns the result in the JSON format. You must create a custom function in Function Compute based on the formats in which data is sent and returned.

{
  "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, a custom function named ConcactNumberWithCompress is created in Function Compute. 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);
                    // The beginning of the Function Compute business logic. 
                    // For example, if the custom function contains two input parameters a and b, each row of data that is sent by AnalyticDB for MySQL contains two data items a and b. 
                    if (row.size() == 2) {
                        result.add(testFunc(row.getInteger(0), row.getInteger(1)));
                    }
                    else {
                        throw new RuntimeException("row size is not 2");
                    }
                    // The end of the Function Compute business logic. 
                }

                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)
    {
        // Return input parameters a and b and a value of 1 by combining them with 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;
    }
}

Invoke the REMOTE_CALL() function in AnalyticDB for MySQL

• If you did not specify global parameters, invoke the ConcactNumberWithCompress function that is created in Function Compute to combine 1 and 2 by using ampersands (&).

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

  Sample result:

  1&2&1

• If you did not specify global parameters, invoke the ConcactNumberWithCompress function that is created in Function Compute to combine 3 and 4 by using ampersands (&).

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

  Sample result:

  3&4&1

• If you specify global parameters, invoke the ConcactNumberWithCompress function that is created in Function Compute to combine 5 and 6 by using ampersands (&).

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

  Sample result:

  5&6&1

• AnalyticDB for MySQL sends data to Function Compute in the GZIP format, but the custom function that is created in Function Compute does not include decompression code. As a result, Function Compute fails to parse the data.

• AnalyticDB for MySQL sends uncompressed data to Function Compute, but Function Compute sends the compressed result to AnalyticDB for MySQL in the GZIP format. As a result, AnalyticDB for MySQL fails to parse the data.