GSQL是如何让GaussDB(DWS) 像执行SHELL一样执行SQL的?GSQL的动态变量功能实践说明
GSQL动态变量功能背景
Teradata中国客户中,大量使用一种动态sql(Dsql)工具来执行SQL脚本,Dsql支持在SQL脚本中使用“变量”,通过SELECT语句的结果赋值到变量中,后续的SQL可以使用变量拼接,轻松实现存储过程中复杂的传参动作,同时提供简单的元命令功能,使SQL脚本能简单实现循环,使用操作系统命令等,功能强大、简单易读。
在Teradata迁移DWS过程中,DWS客户端工具GSQL能执行一般的SQL语句脚本,无法对SQL进行传入“变量”的动作。因Teradata的Dsql功能强大且易用,在客户要求下,DWS对GSQL进行了适配改造,使之支持动态SQL功能。
GSQL本身支持元命令,为适配Teradata Dsql的逻辑判断、循环等,新增了\if \label \goto等元命令,除此以外,还新增了for循环,更方便地实现循环逻辑,让SQL跟shell脚本一样方便快捷好用
GSQL动态变量功能使用场景
-
迁移替换TERADATA的Dsql
因Teradata的Dsql在国内普遍使用,为能顺利迁移,gsql工具对此做了适配改造,增加控制流的元命令、动态变量功能
-
替换存储过程
存储过程能实现变量逻辑、IF判断逻辑、语句报错等,以实现业务逻辑。但存储过程在测试、调试、调优等都比较困难,SQL较复杂时,排查问题等比较费劲。
GSQL的动态变量功能,能覆盖存储过程的变量、判断等复杂逻辑同时,每个SQL独立执行,能很方便地调试、调优、排查问题。
GSQL动态变量功能介绍
GSQL动态变量新增功能点
- 变量功能
通过SELECT语句、\set value key的方式赋值,用于后续SQL语句使用
- 控制功能
通过元命令判断语句报错、跳转、退出等
- 跳转功能
通过元命令实现语句的跳转,以及for循环
GSQL动态变量功能常用启用命令
gsql -h <ipaddr> -p <port> -d <db> -U <user> -W <pass> -D -e --set-file=<a.cfg> -v k1=v1 -v k2=v2 -f <script.sql>
gsql命令行动态变量说明如下图:
以下是具体每个功能点的详细功能和例子说明:
GSQL动态变量功能介绍-变量功能(变量定义)
动态变量定义方式 | 注意事项 | 具体方法 | 样例 |
SELECT语句定义变量 |
SELECT语句定义变量时,as后面若不使用双引号,数据库自动返回小写,而变量使用时是区分大小写的 |
直接SELECT定义变量 | |
通过表SELECT定义变量 |
|||
元命令\set语法定义 |
可以读取操作系统命令结果作为变量,使用``符号执行 |
\set 变量 值 | |
\set 变量 `操作系统命令` | |||
gsql 命令定义变量 |
GSQL启动时可以通过-v 或--set-file预置公共的变量 |
通过命令行增加 -v key1 = value1 方式定义变量 |
|
通过--set-file xx.cfg读取配置文件的方式定义变量 |
|
GSQL动态变量功能介绍-变量功能(变量使用)
动态变量使用方式 | 说明 | 样例 |
变量用在过滤条件上 | 在SQL的过滤条件、表名、或者\if判断语法中,填写相关的变量,在GSQL启用-D 时执行,变量会自动替换为具体数值。 |
|
变量用在表名称上 | ||
变量用在判断语句上 |
GSQL动态变量功能介绍-IF判断功能
GSQL新增\if 判断,能轻松对报错进行异常处理,对结果判断进行不同分支处理等,以下是常用的\if判断功能例子:
- 语句报错\if退出处理
GSQL脚本:
GSQL日志:
- IF/ELSE功能样例
GSQL动态变量功能介绍-控制功能
- GSQL新增\goto \label 标签跳转功能
能实现SQL块的跳转,实现复杂逻辑,如下图gsql脚本,通过不同label来实现两个不同的逻辑分支
GSQL脚本:
日志结果:
- FOR循环功能
能通过SQL的结果作为循环的输入,例子如下:
GSQL脚本:
GSQL动态变量功能介绍-常见的内置参数说明
参数名称 |
设置方法 |
参数说明 |
参数使用场景 |
ROW_COUNT |
SQL返回的记录数,不建议手动设置 |
•ERROR_LEVEL为statement时,表示上一条SQL语句执行返回的行数或受影响的行数。
•ERROR_LEVEL为transaction时,如果事务结束时内部有错,表示事务内最后一个SQL语句执行返回的行数或受影响的行数,否则表示上一条SQL语句执行返回的行数或受影响的行数。
|
该参数常用于记录数\if判断 \if ${ROW_COUNT} == 0 |
VAR_MAX_LENGTH |
\set VAR_MAX_LENGTH variable |
用于控制变量值的长度,默认为4096。如果变量值的长度超过该值,变量值会被截断,并输出告警信息。 |
部分场景使用的参数比较长(如拼接SQL等),可设置64000等较长参数,最大不要超过int类型的长度 |
ERROR_LEVEL |
\set ERROR_LEVEL transaction | statement |
表示ERROR标识成功或者失败类型,默认为transaction。
•statement:ERROR记录上一条SQL语句是否执行成功。
•transaction:ERROR记录上一条SQL语句是否执行成功,或上一个事务内部执行是否出错。
|
需要记录事务内的SQL报错时的${ERROR}参数,将该参数修改为statement |
VAR_NOT_FOUND |
\set VAR_NOT_FOUND default | null | error |
可以设置为default、null、error之一以控制引用变量不存在时的处理方式。
•default:不做变量替换,保持原有字符串。
•null:将原有字符串替换为空字符串。
•error:输出报错信息,保持原有字符串。
|
一般设置为null,将不存在的参数结果返回null值 |
COMPARE_STRATEGY |
\set COMPARE_STRATEGY default | natural | equal |
用于控制\if表达式中大小比较的策略,默认为default。
•default:默认的比较策略,只支持字符串或数字比较,不支持混合比较。单引号内的按照字符串处理,单引号外的按照数字处理。
•natural:在default的基础上,包含动态变量的按照字符串处理。当比较操作符有一侧是数字,尝试将另一侧转换为数字,然后比较。如果转换失败,报错且比较结果为假。
•equal:只支持等值比较,所有情况按照字符串比较。
|
\if 逻辑的比较策略,默认是不支持字符与数字的比较,一般设置为natrual让字符和数字比较。 |
SQLSTATE |
SQL返回的错误代码,不建议手动设置 |
•ERROR_LEVEL为statement时,表示上一条SQL语句执行的状态码。
•ERROR_LEVEL为transaction时,如果事务结束时内部有错,表示事务内最后一个SQL语句执行的状态码,否则表示上一条SQL语句执行的状态码。
|
获取报错代码用于后续处理 |
ON_ERROR_ROLLBACK |
\set ON_ERROR_ROLLBACK on | interactive | off |
如果是on,当一个事务块里的语句产生错误的时候,这个错误将被忽略而事务继续。 如果是interactive,这样的错误只是在交互的会话里忽略。 如果是off(缺省),事务块里一个语句生成的错误将会回滚整个事务。 |
事务中SQL报错需要不会滚时设置 |
ON_ERROR_STOP |
\set ON_ERROR_STOP on | off |
on:命令执行错误时会立即停止,在交互模式下,gsql会立即返回已执行命令的结果。 off(缺省):命令执行错误时将会跳过错误继续执行。 |
一般设置为on,让SQL脚本执行时报错会退出,后续逻辑会继续执行,可能引起数据错误等问题 |
GSQL动态变量功能实践(常见逻辑搭配)
下面举一些常见的GSQL使用搭配例子,能更快更好地理解和使用好gsql的动态变量功能
样例说明 | SQL例子 |
使用LABEL处理SQL语句报错,若报错则退出脚本 |
|
通过操作系统命令获取变量值 |
GSQL执行结果: |
GSQL的元命令本身支持导入/导出/调用SQL脚本 |
|
批量循环创建不同名表,常见的变量场景,能轻松创建大量测试表 |
最后总结一下:
GSQL动态变量新增功能点
- 变量功能
通过SELECT语句、\set value key的方式赋值,用于后续SQL语句使用
- 控制功能
通过元命令判断语句报错、跳转、退出等
- 跳转功能
通过元命令实现语句的跳转,以及for循环
相比存储过程,更轻量,无结构,更容易调试,真的能像shell一样轻松实现多样控制、循环等功能,真心强力推荐使用!!!
更多GSQL的用法,参考官方产品文档:
- 点赞
- 收藏
- 关注作者
评论(0)