rsql で -v オプションと -c オプションを併用するとエラーになる問題の回避策

事象

• rsql で -v オプションと -c オプションを併用するとエラーになる。

$ rsql -h redshift-cluster-poc-central.ceyg6jv96hfq.ap-northeast-1.redshift.amazonaws.com -U awsuser -d dev  -v var_table_name='version()' -c 'select * from :var_table_name;'

Password for user awsuser:
Failed
[Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42601] ERROR:  syntax error at or near ":"
LINE 1: select * from :var_table_name

• 併用しなければ、成功する。

$ rsql -h redshift-cluster-poc-central.ceyg6jv96hfq.ap-northeast-1.redshift.amazonaws.com -U awsuser -d dev  -v var_table_name='version()'Password for user awsuser:
DSN-less Connected
DBMS Name: Amazon Redshift
Driver Name: Amazon Redshift ODBC Driver
Driver Version: 1.5.9.1011
Rsql Version: 1.0.8
Redshift Version: 1.0.63269
SSL: protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off
Type "help" for help.

(redshift-cluster-poc-central) awsuser@dev=# select * from :var_table_name;
                                                          version
---------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.63269
(1 row)

• -v を指定せずに変数を使うと同じエラーになるので、変数が置換されてないと思われる。

 rsql -h redshift-cluster-poc-central.ceyg6jv96hfq.ap-northeast-1.redshift.amazonaws.com -U awsuser -d devPassword for user awsuser:
DSN-less Connected
DBMS Name: Amazon Redshift
Driver Name: Amazon Redshift ODBC Driver
Driver Version: 1.5.9.1011
Rsql Version: 1.0.8
Redshift Version: 1.0.63269
SSL: protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off
Type "help" for help.

(redshift-cluster-poc-central) awsuser@dev=# select * from :var_table_name;
Failed
[Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42601] ERROR:  syntax error at or near ":"
LINE 1: select * from :var_table_name;
                      ^

回避策

• 実行する SQL をコマンドラインでなくファイルで渡すと成功する。

$ cat test.sql
select * from :var_table_name;
[ec2-user@ip-172-31-0-101 ~]$ rsql -h redshift-cluster-poc-central.ceyg6jv96hfq.ap-northeast-1.redshift.amazonaws.com -U awsuser -d dev  -v var_table_name='version()' -f test.sqlPassword for user awsuser:
                                                          version
---------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 8.0.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.4.2 20041017 (Red Hat 3.4.2-6.fc3), Redshift 1.0.63269
(1 row)