如何在 Vertica 中创建外部过程 [英] How to create external procedures in Vertica

问题描述

如何在 Vertica 中创建使用 SQL 和 FROM、WHERE、GROUP BY、ORDER BY、LIMIT 等子句的函数/过程?

解决方案

Vertica 的 create function 语法禁止在 expression 中使用某些子句.

创建函数

CREATE [ OR REPLACE ] 函数... [[db-name.]schema.]function-name ( [ argname argtype [, ...] ] )... RETURN 重新输入... 作为... 开始...... RETURN 表达式；... 结尾;

<块引用>

注意:CREATE FUNCTION 中只允许一个 RETURN 表达式定义.FROM、WHERE、GROUP BY、ORDER BY、LIMIT、聚合、不允许分析和元函数.

要解决这个问题，您可以改用程序.Vertica 中的过程无法与存储过程/PL-SQL 相提并论(Vertica 不支持它们).它们是用另一种语言(例如 Bash)编写的已安装脚本.他们采用语法...

创建过程

CREATE PROCEDURE [[db-name.]schema.]procedure-name (... [ argname ] [ argtype [,...] ] )... AS 'exec-name'... LANGUAGE '语言名称'... 用户操作系统用户"

您可以配置一个过程来使用 bash 调用 vsql 客户端.下面的脚本就是这样做的.您的脚本还可以采用 Vertica 传递的参数.

Bash 程序脚本

#!/bin/bash/opt/vertica/bin/vsql --command 'select count(*) from my_table where condition >价值;'-w 'XXX' --echo-all -h 主机 db_name 用户名退出 0

使用管理工具 GUI 或命令行安装脚本

安装外部脚本

脚本必须有正确的所有者，并且必须设置 setuid 标志.您可以使用 chmod 来做到这一点.

$ admintools -t install_procedure -d vmartdb -f/scratch/helloworld.sh -p ownerpassword安装外部程序...安装外部程序

在数据库中创建脚本然后调用

CREATE PROCEDURE my_proc_name() AS 'my_script.sh' LANGUAGE 'external' USER 'db_user';选择 my_proc_name();

How do I create functions / procedures in Vertica that make use of SQL with clauses such as FROM, WHERE, GROUP BY, ORDER BY, LIMIT etc ?

解决方案

Vertica's create function syntax prohibits the use of certain clauses in the expression.

Create function

CREATE [ OR REPLACE ] FUNCTION
... [[db-name.]schema.]function-name ( [ argname argtype  [, ...] ] )
... RETURN rettype
... AS 
... BEGIN
...... RETURN expression;
... END;

Note: Only one RETURN expression is allowed in the CREATE FUNCTION definition. FROM, WHERE, GROUP BY, ORDER BY, LIMIT, aggregation, analytics and meta function are not allowed.

To get around that you can use a procedure instead. Procedures in Vertica are not comparable to stored-procedures / PL-SQL (Vertica does not support them). They are installed scripts written in another language (such as Bash). They take the syntax...

Create Procedure

CREATE PROCEDURE [[db-name.]schema.]procedure-name ( 
... [ argname ] [ argtype [,...] ] )
... AS 'exec-name'
... LANGUAGE 'language-name'
... USER 'OS-user'

You can configure a procedure to call a vsql client using bash. The following script does that. Your script can also take parameters passed by Vertica.

Bash procedure script

#!/bin/bash
/opt/vertica/bin/vsql --command 'select count(*) from my_table where condition > value;' -w 'XXX' --echo-all -h host db_name user_name
exit 0

Install the script using the admintool GUI or the command line

Install external script

The script must have the right owner and the setuid flag must be set. You can do that using chmod.

$ admintools -t install_procedure -d vmartdb -f /scratch/helloworld.sh -p ownerpassword
Installing external procedure...
External procedure installed

Create the script in the database and then call it

CREATE PROCEDURE my_proc_name() AS 'my_script.sh' LANGUAGE 'external' USER 'db_user';
select my_proc_name();

这篇关于如何在 Vertica 中创建外部过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！