支持多数据源联合查询的SQL运行引擎sycnany-SQL添加使用自定义函数

在微服务和云原生愈发流行的今天，数据的分布也愈发脱离单库单机而更加复杂，使用的数据库类型也会更多，但业务的复杂依然会带来了大量的数据查询和导出需求，而很多时候我们很难为数据量的大部分系统创建完整的BI数仓系统，这时候你是不是觉得为这些需求查询和导出数据就会是一个十分困难且耗时的工作？syncany-SQL就是这样一个工具，来在不依赖数据库的情况下完成不同库表、不同机器和不同数据库类型间直接关联查询和聚合计算后直接导出到常用文件的工具。

安装和配置

sycnany-SQL内置了大部分MySQL常用函数，以及其他常用内置函数，具体函数列表可查看:

https://github.com/snower/syncany-sql/blob/main/docs/functions.md

对于内置函数不能满足的需求，可以在SQL中导入Python扩展模块中自定义函数来完成自定义功能。

• 以下代码示例可查看 https://github.com/snower/syncany-sql/tree/main/examples/import_python。

导入自定义函数方法

1. 配置文件中通过加载扩展注册自定义函数

可在配置文件“extensions”数组中配置需要加载的Python模块扩展，在扩展中即可用”@register_calculater“装饰器来注册自定义函数。

例如：

在用户目录的".syncany"中添加文件”syncany_ext.py“，编辑文件并添加以下内容完成自定义函数定义。

# -*- coding: utf-8 -*-
# 2023/2/8
# create by: snower

from syncanysql import Calculater, register_calculater

@register_calculater("ext_sum_func") #注册函数，参数为注册的函数名称
class SumCalculater(Calculater):
   '''通过扩展注册的函数应该是一个继承Calculater的class并实现calculate添加自定义代码'''

   def calculate(self, a, b):
       return a + b

# 该文件注册了一个自定义函数”ext_sum_func“

然后在配置文件中追加或修改”extensions“配置添加加载扩展”syncany_ext“。

extensions:
 - syncany_ext

# 导入的扩展模块可以从当前目录、用户目录下默认配置目录”.syncany“及其他配置的可导入Python模块的路径中加载。

此时在打开或重启程序”syncany-sync“，即可在SQL中正常使用该函数。

select ext_sum_func(1, 2);

2. SQL中使用use语句导入Python模块

导入扩展方式注册自定义函数为全局配置，且不止直接使用Python函数，也可以在SQL中直接使用”use“语句导入Python模块（SQL使用数据库必须写全名，所以”use”语句不再用于指定当前数据库信息），此种方法导入的Python模块既可以使用”@register_calculater“注册的自定义函数，也可以直接调用Python模块定义的函数和访问属性值，具体语法如下：

use `math`; /* 导入标准库的math模块 */
use `numpy as np`; /* 导入numpy模块并使用别名np */

-- !!!注意需要用`包裹方能符合SQL语法

例如：

在用户目录的".syncany"中添加文件”util_helpers.py“，编辑文件并添加以下内容完成自定义函数定义。

# -*- coding: utf-8 -*-
# 2023/2/8
# create by: snower

import time
from syncanysql import Calculater, register_calculater

@register_calculater("util_helpers_sum") #注册全局函数，参数为注册的函数名称
class SumCalculater(Calculater):
   '''注册全局函数应该是一个继承Calculater的class并实现calculate添加自定义代码'''

   def calculate(self, a, b):
       return a + b

def sum(a, b):
   return a + b

LoadTime = time.time()

# 该文件注册了一个自定义函数”util_helpers_sum“和添加了一个Python普通函数”sum“以及定义了一个Python模块变量”LoadTime“

导入上面定义的模块有3中方法：

• *在当前SQL中使用”use“语句加载模块

use `math`; /* 导入标准库的math模块 */
use `numpy as np`; /* 导入numpy模块并使用别名np */
use `util_helpers as uh`;

-- 导入的扩展模块可以从当前目录、用户目录下默认配置目录”.syncany“及其他配置的可导入Python模块的路径中加载。*

• *在配置文件中追加或修改”imports“配置加载模块

imports:
  uh: util_helpers

-- 导入的扩展模块可以从当前目录、用户目录下默认配置目录”.syncany“及其他配置的可导入Python模块的路径中加载。*

• 在配置文件中追加或修改”executes“配置初始化SQL脚本，然后SQL初始化脚本中使用”use“语句加载模块

executes:
  - init.sql

# 初始SQL脚本可位于当前目录及用户目录下默认配置目录”.syncany“中。

此时在打开或重启程序”syncany-sync“，即可在SQL中正常使用以上模块函数。

select math$pow(2, 3),  np$array(10);
select util_helpers_sum(1, 2);
select uh$sum(1, 2), uh$load_time();

-- ”$“表示属性访问，语义和大多数语言中的”.“相同
-- 属性查找大小写不敏感，且"_"字符连接同时转化为驼峰命名后查找属性

自定义函数编写

1. 自定义普通函数

自定义普通函数分两种：

• 在Python模块中编写一个class继承自Calculater并实现calculate函数，中calculate函数中添加自定义计算逻辑，最后用“@register_calculater”注册为全局SQL函数
• 正常在Python模块中添加函数，然后导入模块后直接通过模块调用函数

例如：

# -*- coding: utf-8 -*-
# 2023/2/8
# create by: snower

import time
from syncanysql import Calculater, register_calculater

@register_calculater("util_helpers_sum") #注册全局函数，参数为注册的函数名称
class SumCalculater(Calculater):
   '''注册全局函数应该是一个继承Calculater的class并实现calculate添加自定义代码'''
   
   def calculate(self, a, b):
       return a + b
   
def sum(a, b):
   return a + b

# 该文件注册了一个自定义函数”util_helpers_sum“和添加了一个Python普通函数”sum“

以上代码通过“@register_calculater”注册了一个全局SQL函数“util_helpers_sum”，及定义了一个普通Python函数“sum”。

• 特别注意：通过“@register_calculater”注册的全局SQL函数会被创建为单例，所以需要慎重使用实例属性。

注册的全局SQL函数可以通过在配置文件中添加"extensions"配置后加载扩展来注册，也可以在配置文件中添加“imports”导入模块完成注册，在初始化SQL脚本或当前SQL脚本中使用“use”语句导入模块也可以完成注册，注册后即可在SQL中正常使用该函数。

如：

select util_helpers_sum(1, 2);

普通函数需导入模块后，可通过模块名"$"调用该函数。

如：

use `util_helpers as uh`;

select uh$sum(1, 2);
select uh$SUM(3, 4) as value;

-- ”$“表示属性访问，语义和大多数语言中的”.“相同

模块属性查找规则：

• 大小写不敏感，查找到的第一个属性有效。
• "_"连字符转化为驼峰命名方式，大小写也不敏感。

2. 读取模块属性

导入Python模块后，以无参数函数方式调用模块中定义的变量时，会认为对该变量的getter访问，此时函数返回该变量的值。

# -*- coding: utf-8 -*-
# 2023/2/8
# create by: snower

LoadTime = time.time()

# 该文件注册了一个自定义函数定义了一个Python模块变量”LoadTime“

以上代码定义了一个变量”LoadTime“，在配置文件中添加“imports”导入模块完成注册，在初始化SQL脚本或当前SQL脚本中使用“use”语句导入模块，导入后可通过无参函数调用读取该变量。

如：

use `util_helpers as uh`;

select uh$load_time();
select uh$loadTime();
select uh$loadtime();
select uh$LoadTime();

-- 以上方法都可以访问到该变量

模块属性查找规则同上。

3. 自定义聚合函数

待完成

可查看示例：https://github.com/snower/syncany-sql/tree/main/examples/aggregate

4. 自定义yield迭代器函数

待完成

可查看示例：https://github.com/snower/syncany-sql/tree/main/examples/yield_data

5. 自定义transform变换函数

待完成

可查看示例：https://github.com/snower/syncany-sql/tree/main/examples/transform

6. 自定义窗口函数

待完成

可查看示例：https://github.com/snower/syncany-sql/tree/main/examples/window_aggregate