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; ^
回避策
$ 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)
Amazon Linux 2 に rsql をインストールする
環境
$ cat /etc/system-release Amazon Linux release 2 (Karoo) $ uname -r 5.10.130-118.517.amzn2.x86_64
インストールする
sudo yum -y install unixODBC curl -L -O https://s3.amazonaws.com/redshift-downloads/drivers/odbc/1.5.9.1011/AmazonRedshiftODBC-64-bit-1.5.9.1011-1.x86_64.rpm sudo yum -y --nogpgcheck localinstall AmazonRedshiftODBC-64-bit-1.5.9.1011-1.x86_64.rpm vi ~/.bash_profile export ODBCINI=~/.odbc.ini export ODBCSYSINI=/opt/amazon/redshiftodbc/Setup export AMAZONREDSHIFTODBCINI=/opt/amazon/redshiftodbc/lib/64/amazon.redshiftodbc.ini source ~/.bash_profile curl -L -O https://s3.amazonaws.com/redshift-downloads/amazon-redshift-rsql/1.0.8/AmazonRedshiftRsql-1.0.8.x86_64.rpm sudo rpm -i AmazonRedshiftRsql-1.0.8.x86_64.rpm
インストールできたことを確認する
$ which rsql /usr/bin/rsql $ rsql -? rsql is the Amazon Redshift interactive terminal. Usage: rsql [OPTION]... [DBNAME [USERNAME]] General options: -c, --command=COMMAND run only single command (SQL or internal) and exit -d, --dbname=DBNAME database name to connect to (default: "dev") -f, --file=FILENAME execute commands from file, then exit -l, --list list available databases, then exit -v, --set=, --variable=NAME=VALUE set rsql variable NAME to VALUE (e.g., -v ON_ERROR_STOP=1) -V, --version output version information, then exit -X, --no-psqlrc do not read startup file (~/.psqlrc) -1 ("one"), --single-transaction execute as a single transaction (if non-interactive) -?, --help[=options] show this help, then exit --help=commands list backslash commands, then exit --help=variables list special variables, then exit Input and output options: -a, --echo-all echo all input from script -b, --echo-errors echo failed commands -e, --echo-queries echo commands sent to server -E, --echo-hidden display queries that internal commands generate -L, --log-file=FILENAME send session log to file -n, --no-readline disable enhanced command line editing (readline) -o, --output=FILENAME send query results to file (or |pipe) -q, --quiet run quietly (no messages, only query output) -s, --single-step single-step mode (confirm each query) -S, --single-line single-line mode (end of line terminates SQL command) Output format options: -A, --no-align unaligned table output mode --csv CSV (Comma-Separated Values) table output mode -F, --field-separator=STRING field separator for unaligned output (default: "|") -H, --html HTML table output mode -P, --pset=VAR[=ARG] set printing option VAR to ARG (see \pset command) -R, --record-separator=STRING record separator for unaligned output (default: newline) -t, --tuples-only print rows only -T, --table-attr=TEXT set HTML table tag attributes (e.g., width, border) -x, --expanded turn on expanded table output -z, --field-separator-zero set field separator for unaligned output to zero byte -0, --record-separator-zero set record separator for unaligned output to zero byte Connection options: DSN: -D DSN ODBC dsn name DSN-less: -h, --host=HOSTNAME database server host (default: "") -p, --port=PORT database server port (default: "5439") -U, --username=USERNAME database user name (default: "ec2-user") -w, --no-password never prompt for password -W, --password force password prompt (should happen automatically) For more information, type "\?" (for internal commands) from within rsql, or consult the rsql section in the Amazon Redshift documentation.
Redshift に接続してみる
rsql -h redshift-cluster-poc-central.ceyg6jv96hfq.ap-northeast-1.redshift.amazonaws.com -U awsuser -d dev
国内トラベルグッズメモ
電子機器
- MacBook Pro 14インチ(M1)
- Shokz
- AirPods Pro
- Apple Watch
- 充電アダプター・ケーブル
- モバイルバッテリー
スポーティ
フォーマル
アウター
- ダウン
- パーカー(モンベル)
パンツ
- デニム 濃色ブルー(EDWIN Jerseys)
- デニム ブラック(EDWIN Jerseys)
アンダーウェアー
生活
- ボディタオル
- 歯間ブラシ
- 電気シェーバー
- 化粧水
- 乳液
- アトマイザー
- ヘアクリーム
- ニベア
- 鼻毛ハサミ
- 爪切り
- くし
- サプリ
sed でファイルの先頭行に文字列を挿入する
sed でファイルの先頭行に文字列を挿入したメモ。
- 文字列を挿入する
$ ls ks*|while read LINE; do sed -i '1s/^/CONSISTENCY LOCAL_QUORUM;\n/' ${LINE}; done
- 確認する
$ head ks_insert_000 CONSISTENCY LOCAL_QUORUM; INSERT INTO poc.partition_id_dist_test (partition_id, date_time) VALUES (001, '2024-01-10 17:30:00'); INSERT INTO poc.partition_id_dist_test (partition_id, date_time) VALUES (002, '2024-01-10 17:30:00'); INSERT INTO poc.partition_id_dist_test (partition_id, date_time) VALUES (003, '2024-01-10 17:30:00'); INSERT INTO poc.partition_id_dist_test (partition_id, date_time) VALUES (004, '2024-01-10 17:30:00'); INSERT INTO poc.partition_id_dist_test (partition_id, date_time) VALUES (005, '2024-01-10 17:30:00'); INSERT INTO poc.partition_id_dist_test (partition_id, date_time) VALUES (006, '2024-01-10 17:30:00'); INSERT INTO poc.partition_id_dist_test (partition_id, date_time) VALUES (007, '2024-01-10 17:30:00'); INSERT INTO poc.partition_id_dist_test (partition_id, date_time) VALUES (008, '2024-01-10 17:30:00'); INSERT INTO poc.partition_id_dist_test (partition_id, date_time) VALUES (009, '2024-01-10 17:30:00');
split コマンドでファイルを分割する
split コマンドでファイルを分割したメモ。
$ split -d -a 3 -l 1000 base_insert.cql ks_insert_ $ ls |head base_insert.cql ks_insert_000 ks_insert_001 ks_insert_002 ks_insert_003 ks_insert_004 ks_insert_005 ks_insert_006 ks_insert_007