psqlいらず？Redshift用CLIツールのAmazon Redshift RSQLを試してみた
データ・アナリティクス事業本部の森脇です。
先日、Redshift用のコマンドラインインターフェースであるAmazon Redshift RSQLされました。
従来RedshiftのCLIツールと言えばpsqlが定番でしたが、とうとうRedshift用のツールがリリースされました！
発表によると
Amazon Redshift RSQL は、Amazon Redshift 固有の機能の追加セットを備えた PostgreSQL psql コマンドラインツールの機能をサポートしています。
とのことなので、psqlの機能が使えつつ拡張でRedshift固有機能にも対応しているようです。
これであればpsqlからの乗り換えも容易そうです。
早速Amazon Linux2にインストールして試してみました。
インストール
ユーザーズガイドを参照すると、まずはODBC関連ツールをインストール/設定する必要があるようです。
(つまり、RSQLはODBC接続でRedshiftと通信を行うようです)
ODBC関連ツールのインストール
1. unixODBCのインストール
sudo yum install unixODBC
2. Redshift用のODBCドライバーをダウンロード
上記ドキュメント内のリンクを選択し、ドライバーをダウンロードします。
今回は64bit版を選択しました。
3. ダウンロードしたODBCドライバーをインストール
sudo yum -y --nogpgcheck localinstall AmazonRedshiftODBC-64-bit-1.4.34.1000-1.x86_64.rpm
4. ~/.bash_profileに以下を追記
export ODBCINI=~/.odbc.ini export ODBCSYSINI=/opt/amazon/redshiftodbc/Setup export AMAZONREDSHIFTODBCINI=/opt/amazon/redshiftodbc/lib/64/amazon.redshiftodbc.ini
5. 追記した設定の読みこみ
source ~/.bash_profile
RSQLのインストール
ODBC関連ツールがインストールできたので、本体のRSQLをインストールしていきます。
1. RSQLをダウンロード
対象OSがAmazon Linux2なので、rpmを選択しました。
2. インストール
sudo rpm -i AmazonRedshiftRsql-1.0.1-1.x86_64.rpm
which コマンドでrsqlがインストールされたことを確認できました。
which rsql /usr/bin/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:
-h, --host=HOSTNAME database server host (default: "")
-p, --port=PORT database server port (default: "5439")
-U, --username=USERNAME database user name (default: "jmc-dev")
-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.
接続系のオプションはpsqlと互換性がありそうです。
接続
$ rsql -h sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com -U root -d dev Password for user root: DSN-less Connected DBMS Name: Amazon Redshift Driver Name: Amazon Redshift ODBC Driver Driver Version: 1.4.34.1000 Rsql Version: 1.0.1 Redshift Version: 1.0.29551 Type "help" for help. (sample-redshift-cluster) root@dev=#
psqlと同じ使い勝手で接続することができました。
ちなみにPGPASSWORDは使えませんでした。
$ PGPASSWORD='password' rsql -h sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com -U root -d dev Password for user root:
psqlの場合、-pオプションで5439を設定するのが地味に面倒くさかったですが、rsqlはデフォルト値が5439になっているので指定の必要はありませんでした。
また、バックエンドがODBCである強みを生かし、DSNを利用した様々な接続方法にも対応しています。
(以降は、~/.odbc.iniに設定して試しました)
パスワード認証
先程と同様の、ユーザー，パスワードによる認証です。UIDにユーザー名を、PWDにパスワードを指定します。
ODBCの流儀ですね！
[test-password] Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so Host=sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com Port=5439 Database=dev UID=root PWD=password
※ Driverに設定するパスはインストール先のOSによって異なります
-DオプションでDSN名を指定し、接続します。
rsql -D test-password
IAMプロファイル認証
プロファイル名を指定して、IAMにて認証を行うことが可能です。
この方法で認証をする場合、対象のIAMプロファイルにGetClusterCredentials権限が必要です。
設定上は、IAMに1を指定します。
※ここでいう「1」は、真偽値(true/false)のtrueという意味です。
また、DbUserにRedshiftのユーザー名を指定し、Profileに利用するプロファイル名を指定します。
[test-iam-profile] Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so Host=sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com Port=5439 Database=dev DbUser=root IAM=1 Profile=example
rsql -D test-iam-profile
インスタンスプロファイル認証
EC2のインスタンスプロファイルを利用して認証することも可能です。
この場合、Instanceprofileに1を指定します。
※IAMプロファイル認証と同様に、EC2に紐付けるIAMにはGetClusterCredentials権限が必要です。
[test-iam-instanceprofile] Driver=/opt/amazon/redshiftodbc/lib/64/libamazonredshiftodbc64.so Host=sample-redshift-cluster.cjhysixos9pk.ap-northeast-1.redshift.amazonaws.com Port=5439 Database=dev DbUser=root IAM=1 Instanceprofile=1
rsql -D test-iam-instanceprofile
その他
この他にもシングルサインオンによる認証もサポートされているようでした。
SQLの実行
psqlと同じ使い勝手で利用が可能です。
SELECT
(sample-redshift-cluster) root@dev=> select * from public.table_a; id | name | age | weight | married | registration_date | create_date ----+----------+-----+--------+---------+-------------------+--------------------- 1 | name-a | 49 | 45.2 | 1 | 2020-06-13 | 2021-03-09 10:42:35 2 | name-b | 36 | 76.5 | 1 | 2020-02-17 | 2021-03-09 10:42:35 3 | name-c | 34 | 50.6 | 0 | 2020-10-29 | 2021-03-09 10:42:35 4 | name-d | 23 | 61.7 | 1 | 2020-06-02 | 2021-03-09 10:42:35 5 | name-e | 21 | 35.3 | 1 | 2020-02-12 | 2021-03-09 10:42:35 (5 rows)
INSERT
(sample-redshift-cluster) root@dev=> insert into public.table_a values (10, 'name-new', 10, 30, 0, '2020-01-01', '2021-09-16 00:00:00'); 1 rows affected
DELETE
(sample-redshift-cluster) root@dev=> delete public.table_a; 10 rows affected
COPY
(sample-redshift-cluster) root@dev=> COPY public.table_a FROM 's3://foo-bucket/sample.csv' IAM_ROLE 'arn:aws:iam::123456789:role/test-role' FORMAT AS CS V IGNOREHEADER 1; INFO: Load into table 'table_a' completed, 5 record(s) loaded successfully.
コマンド
psqlで用意されているコマンドもそのまま使えます。
テーブル一覧表示(\d)
(sample-redshift-cluster) root@dev=> \d
List of relations
schema | name | type | owner
--------+----------------------+-------+----------
public | table_a | table | root
public | table_b | table | root
public | table_c | table | root
public | table_b | table | root
(4 rows)
テーブルDDL表示(\d TABLE_NAME)
分散キーや圧縮エンコードの情報も表示されます。
(sample-redshift-cluster) root@dev=> \d public.table_a
Table "public.table_a"
Column | Type | Collation | Nullable | Default Value | Encoding | DistKey | SortKey
-------------------+-----------------------------+----------------+----------+---------------+----------+---------+---------
id | smallint | | YES | | none | 0 | 1
name | character varying(8) | case_sensitive | YES | | lzo | 0 | 0
age | smallint | | YES | | az64 | 0 | 0
weight | double precision | | YES | | none | 0 | 0
married | boolean | | YES | | none | 0 | 0
registration_date | date | | YES | | az64 | 0 | 0
create_date | timestamp without time zone | | YES | | az64 | 0 | 0
Diststyle: ALL
Backup: YES
Created: 2021-04-20 06:44:11.012394
拡張ディスプレイ(\x)
(sample-redshift-cluster) root@dev=> \x Expanded display is on. (sample-redshift-cluster) root@dev=> select * from public.table_b; -[ RECORD 1 ]-----+-------------------- id | 1 name | user_1 age | 49 weight | 45.2 married | 1 registration_date | 2020-06-13 create_date | 2021-03-09 10:42:35 -[ RECORD 2 ]-----+-------------------- id | 2 name | user_2 age | 36 weight | 76.5 married | 1 registration_date | 2020-02-17 create_date | 2021-03-09 10:42:35 -[ RECORD 3 ]-----+-------------------- id | 3 name | user_3 age | 34 weight | 50.6 married | 0 registration_date | 2020-10-29 create_date | 2021-03-09 10:42:35 (sample-redshift-cluster) root@dev=> \x Expanded display is off.
まとめ
rsqlを使うためには、ODBC関連ツール(unixODBCやRedshiftのODBCドライバー)を事前にインストールする必要があり、psqlよりも導入に手間がかかりそうでした。
その一方で、IAMプロファイル、EC2のインスタンスプロファイルによる認証が利用可能であり、よりセキュアにRedshiftに接続できました。
また、psqlの操作感で利用可能であり、コマンドも同じように利用できるため、乗り換えはスムーズに行えそうです。
個人的に、\dコマンドによるテーブルDDL表示が分散スタイル，圧縮エンコードも表示されて非常に便利だなと感じました。
![[Redshift] テーブルへのアクセス権付与権限について調べてみた](https://images.ctfassets.net/ct0aopd36mqt/wp-thumbnail-63f1274931b942e9a92e601c1127ad73/cfe87ec6d62fa2fc3c474ed4cb2f6c2e/amazon-redshift)
![[レポート] Data preparation authoring with AWS Glue Studioに参加しました #AWSreInvent #ANT320](https://images.ctfassets.net/ct0aopd36mqt/3IQLlbdUkRvu7Q2LupRW2o/edff8982184ea7cc2d5efa2ddd2915f5/reinvent-2024-sessionreport-jp.jpg)
