GBase 8a通过python UDF从客户端执行SQL语句

GBase 8a 数据库集群从862Build43开始,支持python的UDF自定义函数。本文通过python功能在命令行调用gccli正在执行SQL语句,包括某些不允许在存储过程中执行的。

python自定义函数

drop function if exists executePythonSQL;
create function executePythonSQL(sql_str varchar(8000))
returns varchar
$$
try:
    import commands
    output = commands.getoutput("gccli -ugbase -pgbase20110531 -N -vvv -e\\\"%s\\\"" %sql_str)
except:
    return None
return str(output)

$$ language plpythonu;

执行效果

gbase> select executePythonSQL('load data infile ''sftp://gbase:gbase1234@10.0.2.101/home/gbase/t.txt'' into table testdb.t1 fields terminated by '',''') a;
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| a                                                                                                                                                                                                                                                                |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| --------------
load data infile 'sftp://gbase:gbase1234@10.0.2.101/home/gbase/t.txt' into table testdb.t1 fields terminated by ','
--------------

Query OK, 0 rows affected (Elapsed: 00:00:01.29)
Task 6154 finished, Loaded 0 records, Skipped 1 records

Bye |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:01.32)

gbase> select executePythonSQL('select now()');
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| executePythonSQL('select now()')                                                                                                                             |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| --------------
select now()
--------------

+---------------------+
| 2020-11-25 10:25:17 |
+---------------------+
1 row in set (Elapsed: 00:00:00.00)

Bye |
+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (Elapsed: 00:00:00.04)

gbase>

发表评论

您的电子邮箱地址不会被公开。