容量制限つきのスキーマを作成して、ユーザーが利用できるようにする。
- superuser でスキーマ作成権限を持つユーザーを作成する。
$ psql -a "host=redshift-cluster-poc-ads.********.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=sampledb port=5439" psql (14.3, server 8.0.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. -- 管理ユーザー用 Role を作成する sampledb=# create role dm_admin_role; CREATE ROLE -- 管理ユーザー用 Role に権限を付与する sampledb=# grant create schema to role dm_admin_role; GRANT sampledb=# grant create user to role dm_admin_role; GRANT -- 管理ユーザーを作成して、Role を付与する sampledb=# create user dm_admin_user password 'Password1'; CREATE USER sampledb=# grant role dm_admin_role to dm_admin_user; GRANT -- 管理ユーザーに sampledb へのアクセス権を付与する sampledb=# grant all on database sampledb to dm_admin_user; GRANT
- 管理者ユーザーでユーザーを作成する。
$ psql -a "host=redshift-cluster-poc-ads.********.ap-northeast-1.redshift.amazonaws.com user=dm_admin_user dbname=sampledb port=5439" Password for user dm_admin_user: psql (14.3, server 8.0.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. -- データマート利用ユーザーを作成する sampledb=> create user dm_user password 'Password1' ; CREATE USER --ストレージ容量の上限 2GB でスキーマを作成する。 sampledb=> create schema dm_schema authorization dm_user quota 2 gb; CREATE SCHEMA
- データマート利用ユーザーは2GBを超えて書込みができない。
$ psql -a "host=redshift-cluster-poc-ads.********.ap-northeast-1.redshift.amazonaws.com user=dm_user dbname=sampledb port=5439" Password for user dm_user: psql (14.3, server 8.0.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. sampledb=> CREATE TABLE dm_schema.lineorder ( lo_orderkey INTEGER NOT NULL, lo_linenumber INTEGER NOT NULL, lo_custkey INTEGER NOT NULL, lo_partkey INTEGER NOT NULL, lo_suppkey INTEGER NOT NULL, lo_orderdate INTEGER NOT NULL, lo_orderpriority VARCHAR(15) NOT NULL, lo_shippriority VARCHAR(1) NOT NULL, lo_quantity INTEGER NOT NULL, lo_extendedprice INTEGER NOT NULL, lo_ordertotalprice INTEGER NOT NULL, lo_discount INTEGER NOT NULL, lo_revenue INTEGER NOT NULL, lo_supplycost INTEGER NOT NULL, lo_tax INTEGER NOT NULL, lo_commitdate INTEGER NOT NULL, lo_shipmode VARCHAR(10) NOT NULL ); CREATE TABLE sampledb=> copy dm_schema.lineorder from 's3://awssampledb-yoheia/ssbgz/lineorder' iam_role 'arn:aws:iam::123456789012:role/redshift-spectrum-s3-fullaccess' gzip compupdate off region 'ap-northeast-1'; INFO: Load into table 'lineorder' completed, 600037902 record(s) loaded successfully. ERROR: Transaction 6931776 is aborted due to exceeding the disk space quota in schema(s): (Schema: dm_schema, Quota: 2048, Current Disk Usage: 25096). ★ Free up disk space or request increased quota for the schema(s). ★
- スキーマのストレージ使用量制限を確認する
% psql -a "host=redshift-cluster-poc-ads.*******.ap-northeast-1.redshift.amazonaws.com user=awsuser dbname=sampledb port=5439" Password for user awsuser: psql (14.3, server 8.0.2) SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off) Type "help" for help. sampledb=# select * from SVV_SCHEMA_QUOTA_STATE ; schema_id | schema_name | schema_owner | quota | disk_usage | disk_usage_pct -----------+----------------------------------------------------------------------------------------------------------------------------------+--------------+-------+------------+---------------- 394209 | dm_schema | 218 | 2048 | 0 | 0 sampledb=# select * from STL_SCHEMA_QUOTA_VIOLATIONS; ownerid | userid | xid | pid | schema_id | schema_name | quota | disk_usage | disk_usage_pct | timestamp | disk_usage_diff ---------+--------+-----+-----+-----------+-------------+-------+------------+----------------+-----------+----------------- (0 rows)