如何在Vertica中创建外部程序?

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中创建使用SQL的函数/过程，包括FROM、WHERE、GROUP BY、ORDER BY、LIMIT等子句?

2 个解决方案

#1

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

Vertica的create函数语法禁止在表达式中使用某些子句。

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.

注意:在CREATE函数定义中只允许有一个返回表达式。不允许使用FROM、WHERE、GROUP BY、ORDER BY、LIMIT、aggregation、analytics和meta函数。

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...

为了解决这个问题，你可以使用一个过程。Vertica中的过程不能与存储过程/ PL-SQL (Vertica不支持它们)相比。它们是用另一种语言(如Bash)编写的安装脚本。他们的语法……

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调用vsql客户端。下面的脚本可以做到这一点。脚本还可以接受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

使用admintool GUI或命令行安装脚本

Install external script

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

脚本必须具有正确的所有者，必须设置setuid标志。

$ 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();

#2

Vertica does not support Stored Procedures as you have in some DBs. Instead it has User Defined Functions. You write them in Java or whatever but it runs INSIDE the database as if it were an SP. (it also supported External Procedures to run stuff outside the DB if you what you want)

Vertica不像在某些DBs中那样支持存储过程。相反，它有用户定义的函数。您可以用Java或其他方式编写它们，但它在数据库中运行，就好像它是一个SP。