CREATE DATABASE - Amazon Redshift
構文パラメータデータ共有で使用する構文データ共有のための CREATE DATABASE の使用上の注意AWS Glue Data Catalog の CREATE DATABASECREATE DATABASE の制限CREATE DATABASE のためのデータベース照合例

CREATE DATABASE

新しいデータベースを作成します。

データベースを作成するには、スーパーユーザーであるか、CREATEDB 権限を持っている必要があります。

トランザクションブロック (BEGIN ... END) 内で CREATE DATABASE を実行することはできません。トランザクションの詳細については、「直列化可能分離」を参照してください。

構文

CREATE DATABASE database_name [ WITH ]
[ OWNER [=] db_owner ]
[ CONNECTION LIMIT { limit | UNLIMITED } ]
[ COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE } ]
[ ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT } ]
[ [ WITH PERMISSIONS ] FROM DATASHARE datashare_name ] OF [ ACCOUNT account_id ] NAMESPACE namespace_guid 
| [ FROM ARN '<arn>' { WITH DATA CATALOG SCHEMA '<schema>' | WITH NO DATA CATALOG SCHEMA } 
[ IAM_ROLE  default | 'SESSION' | 'arn:aws:iam::<account-id>:role/<role-name>' ] ]

パラメータ

database_name

新しいデータベースの名前。有効な名前の詳細については、「名前と識別子」を参照してください。

WiTH

オプションキーワード

OWNER

データベース所有者を指定します。

=

オプションの文字。

db_owner

データベース所有者のユーザー名。

CONNECTION LIMIT { limit | UNLIMITED }

ユーザーが同時に開けるデータベース接続の最大数。この制限はスーパーユーザーには適用されません。同時接続の最大数を許可するには、UNLIMITED キーワードを使用します。ユーザーごとの接続数の制限が適用される場合もあります。詳細については、「CREATE USER」を参照してください。デフォルトは UNLIMITED です。現在の接続を確認するには、STV_SESSIONSシステムビューに対してクエリを実行します。

注記

ユーザーとデータベースの両方の接続制限が適用される場合は、ユーザーが接続しようとしたときに、両方の制限内に未使用の接続スロットがなければなりません。

COLLATE { CASE_SENSITIVE | CASE_INSENSITIVE }

文字列の検索または比較が CASE_SENSITIVE か CASE_INSENSITIVE かを指定する句。デフォルトは CASE_SENSITIVE です。

ISOLATION LEVEL { SERIALIZABLE | SNAPSHOT }

データベースに対してクエリを実行するときに使用される分離レベルを指定する句。

  • 直列化可能な分離 – 同時トランザクションの完全な直列化機能を提供します。これは、プロビジョニングされたクラスターで作成されたデフォルトのデータベースです。詳細については、「直列化可能分離」を参照してください。

  • スナップショットの分離 – 更新および削除の競合に対する保護機能を備えた分離レベルを提供します。これは、サーバーレス名前空間に作成されたデータベースのデフォルトです。

データベースが実行されている同時実行モデルを、次のように表示できます。

  • STV_DB_ISOLATION_LEVEL カタログビューのクエリを実行します。詳細については、「STV_DB_ISOLATION_LEVEL」を参照してください。

    SELECT * FROM stv_db_isolation_level;

  • PG_DATABASE_INFO ビューに対してクエリを実行します。

    SELECT datname, datconfig FROM pg_database_info;

    データベースごとの分離レベルは、concurrency_model キーの隣に表示されます。1 の値は、SNAPSHOT を表します。2 の値は、SERIALIZABLE を表します。

Amazon Redshift データベースでは、SERIALIZABLE と SNAPSHOT の両方の分離は、直列化可能な分離レベルのタイプです。つまり、ダーティリード、ノンリピータブルリード、ファントムリードは、SQL 標準に従って防止されます。どちらの分離レベルでも、トランザクションが開始されたときに存在するデータのスナップショットに対してトランザクションが実行され、他のトランザクションがそのスナップショットを変更できないことが保証されます。ただし、SNAPSHOT 分離は、異なるテーブル行での書き込みスキューの挿入と更新を妨げないため、完全な直列化機能を提供しません。

次のシナリオは、SNAPSHOT 分離レベルを使用した書き込みスキューの更新を示しています。Numbers という名前のテーブルには、0 と 1 の値を含む digits という名前の列が含まれています。各ユーザーの UPDATE ステートメントは、他のユーザーと重複しません。ただし、0 と 1 の値はスワップされます。実行する SQL は、このタイムラインに従い次の結果になります。

時間 ユーザー 1 アクション ユーザー 2 アクション
1 BEGIN;
2 BEGIN;
3 SELECT * FROM Numbers;
digits
------
0
1
4 SELECT * FROM Numbers;
digits
------
0
1
5 UPDATE Numbers SET digits=0 WHERE digits=1;
6 SELECT * FROM Numbers;
digits
------
0
0
7 COMMIT;
8 Update Numbers SET digits=1 WHERE digits=0;
9 SELECT * FROM Numbers;
digits
------
1
1
10 COMMIT;
11 SELECT * FROM Numbers;
digits
------
1
0
12 SELECT * FROM Numbers;
digits
------
1
0

直列化可能な分離を使用して同じシナリオを実行する場合、Amazon Redshift は直列化可能な分離違反によりユーザー 2 を終了し、エラー 1023 を返します。詳細については、「直列化可能分離エラーを修正する方法」を参照してください。この場合、ユーザー 1 だけが正常にコミットできます。すべてのワークロードが直列化可能な分離を要件として必要とするわけではありません。その場合、スナップショット分離はデータベースのターゲット分離レベルとして十分です。

ARN '<ARN>'

データベースの作成に使用する AWS Glue データベース ARN。

{ WITH NO DATA CATALOG SCHEMA | DATA CATALOG SCHEMA '<schema>' }
注記

このパラメータは、CREATE DATABASE コマンドで FROM ARN パラメータも使用する場合にのみ適用されます。

AWS Glue Data Catalog でオブジェクトにアクセスしやすくするためにスキーマを使用してデータベースを作成するかどうかを指定します。

IAM_ROLE { default | 'SESSION' | 'arn:aws:iam::<AWS アカウント-id>:role/<role-name>' }
注記

このパラメータは、CREATE DATABASE コマンドで FROM ARN パラメータも使用する場合にのみ適用されます。

CREATE DATABASE コマンドの実行時にクラスターに関連付けられた IAM ロールを指定すると、Amazon Redshift はデータベースに対してクエリを実行するときにロールの認証情報を使用します。

default キーワードを指定することは、デフォルトとして設定されてクラスターに関連付けられている IAM ロールを使用することを意味します。

フェデレーション ID を使用して Amazon Redshift クラスターに接続し、このコマンドを使用して作成された外部スキーマからテーブルにアクセスする場合は、'SESSION' を使用します。フェデレーション ID の使用例については、フェデレーション ID の設定方法を説明している「フェデレーション ID を使用して、ローカルリソースと Amazon Redshift Spectrum の外部テーブルへの Amazon Redshift アクセスを管理する」を参照してください。

クラスターが認証と承認に使用する IAM ロールの Amazon リソースネーム (ARN) を使用します。少なくとも、IAM ロールには、Amazon S3 バケットで LIST オペレーションを実行してアクセスを受ける許可と、バケットに含まれる Amazon S3 オブジェクトで GET オペレーションを実行する許可が必要です。データベース用の AWS Glue Data Catalog を使用してデータベースを作成するときに IAM_ROLE を使用する方法の詳細については、「コンシューマーとして Lake Formation 管理のデータ共有を使用する」を参照してください。

以下に ARN が 1 つの場合の IAM_ROLE パラメータ文字列の構文を示します。

IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-name>'

ロールを連鎖することで、クラスターは別のアカウントに属している可能性がある別の IAM ロールを引き受けることができます。最大10 個までのロールを連鎖できます。詳細については、「Amazon Redshift Spectrum での IAM ロールの連鎖」を参照してください。

この IAM ロールに次のような IAM アクセス許可ポリシーをアタッチします。

{
    "Version": "2012-10-17",
    "Statement": [
        {
            "Sid": "AccessSecret",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetResourcePolicy",
                "secretsmanager:GetSecretValue",
                "secretsmanager:DescribeSecret",
                "secretsmanager:ListSecretVersionIds"
            ],
            "Resource": "arn:aws:secretsmanager:us-west-2:123456789012:secret:my-rds-secret-VNenFy"
        },
        {
            "Sid": "VisualEditor1",
            "Effect": "Allow",
            "Action": [
                "secretsmanager:GetRandomPassword",
                "secretsmanager:ListSecrets"
            ],
            "Resource": "*"
        }
    ]
}

フェデレーションクエリで使用する IAM ロールを作成するステップについては、「フェデレーテッドクエリを使用するためのシークレットと IAM ロールの作成」を参照してください。

注記

連鎖したロールのリストには空白を含めないでください。

以下に連鎖された 3 つのロールの構文を示します。

IAM_ROLE 'arn:aws:iam::<aws-account-id>:role/<role-1-name>,arn:aws:iam::<aws-account-id>:role/<role-2-name>,arn:aws:iam::<aws-account-id>:role/<role-3-name>'

データ共有で CREATE DATABASE を使用するための構文

次の構文で、同じ AWS アカウント内のデータ共有からデータベースを作成するために使用される、CREATE DATABASE コマンドについて確認できます。

CREATE DATABASE database_name
[ [ WITH PERMISSIONS ] FROM DATASHARE datashare_name ] OF [ ACCOUNT account_id ] NAMESPACE namespace_guid

次の構文で、AWSアカウント間のデータ共有からデータベースを作成するために使用される,CREATE DATABASE コマンドについて確認できます。

CREATE DATABASE database_name
[ [ WITH PERMISSIONS ] FROM DATASHARE datashare_name ] OF ACCOUNT account_id NAMESPACE namespace_guid

データ共有で CREATE DATABASE を使用するためのパラメータ

FROM DATASHARE

データ共有の場所を示すキーワード。

datashare_name

コンシューマーデータベースが作成されるデータ共有の名前。

WITH PERMISSIONS

データ共有から作成されたデータベースに、個々のデータベースオブジェクトにアクセスするためのオブジェクトレベルの許可が必要であることを指定します。この句を指定しないと、データベースに対する USAGE アクセス許可を付与されたユーザーまたはロールは、データベース内のすべてのデータベースオブジェクトに自動的にアクセスできるようになります。

NAMESPACE namespace_guid

データ共有が属しているプロデユーサ名前空間を指定する値。

ACCOUNT account_id

データ共有が属しているプロデユーサアカウントを指定する値。

データ共有のための CREATE DATABASE の使用上の注意

データベーススーパーユーザーとして、CREATE DATABASE を使用して AWS アカウント内のデータ共有からデータベースを作成する場合は、NAMESPACE オプションを指定します。ACCOUNT オプションは省略可能です。CREATE DATABASE を使用して AWS アカウント間のデータ共有からデータベースを作成する場合は、プロデューサーで ACCOUNT と NAMESPACE の両方を指定します。

コンシューマークラスター上の 1 つのデータ共有に対して、作成できるコンシューマデータベースは 1 つだけです。同じデータ共有を参照する、複数のコンシューマーデータベースを作成することはできません。

AWS Glue Data Catalog の CREATE DATABASE

AWS Glue データベース ARN を使用してデータベースを作成するには、CREATE DATABASE で ARN を指定します。

CREATE DATABASE sampledb FROM ARN <glue-database-arn> WITH NO DATA CATALOG SCHEMA;

オプションで、IAM_ROLE パラメータに値を指定することもできます。パラメータおよび許容される値の詳細については、「パラメータ」を参照してください。

IAM ロールを使用して ARN からデータベースを作成する方法を示す例は、以下のとおりです。

CREATE DATABASE sampledb FROM ARN <glue-database-arn> WITH NO DATA CATALOG SCHEMA IAM_ROLE <iam-role-arn>
CREATE DATABASE sampledb FROM ARN <glue-database-arn> WITH NO DATA CATALOG SCHEMA IAM_ROLE default;

DATA CATALOG SCHEMA を使用してデータベースを作成することもできます。

CREATE DATABASE sampledb FROM ARN <glue-database-arn> WITH DATA CATALOG SCHEMA <sample_schema> IAM_ROLE default;

CREATE DATABASE の制限

Amazon Redshift は、データベースに次の制限を適用します。

  • クラスターごとのユーザー定義データベースは最大 60 個です。

  • データベース名は最大 127 バイトです。

  • データベース名を予約語にすることはできません。

データベースの照合

照合とは、データベースエンジンが SQL の文字型データを比較およびソートする方法を定義するための一連の規則です。大文字と小文字が区別されない照合は、最もよく使用されます。Amazon Redshift では、大文字と小文字を区別しない照合を使用して、他のデータウェアハウスシステムからの移行を容易にしています。大文字と小文字を区別しない照合のネイティブサポートにより、Amazon Redshift は、分散キー、ソートキー、範囲が制限されたスキャンなど、重要な調整法や最適化の方法を引き継ぐことができます。

COLLATE 句により、データベース内のすべての CHAR および VARCHAR 列に対する、デフォルトの照合手段を指定します。CASE_INSENSITIVE が指定されている場合、すべての CHAR または VARCHAR 列では、大文字と小文字を区別しない照合が使用されます。照合の詳細については、「照合順序」を参照してください。

大文字と小文字を区別しない列に挿入または取り込んだデータは、元の大文字と小文字を維持します。ただし、ソートやグループ化など、すべての比較ベースの文字列操作は、大文字と小文字を区別しません。LIKE 述語、および正規表現関数などのパターンマッチング操作でも、大文字と小文字を区別しません。

次の SQL オペレーションでは、適切な照合セマンティクスをサポートします。

  • 比較演算子:=、<>、<、<=、>、>=

  • LIKE 演算子

  • ORDER BY 句

  • GROUP BY 句

  • MIN、MAX、LISTAGG など、文字列比較を使用する集計関数

  • PARTITION BY 句や ORDER BY 句などのウィンドウ関数

  • スカラー関数: greatest() 、least()、STRPOS()、REGEXP_COUNT()、REGEXP_REPLACE()、REGEXP_INSTR()、REGEXP_SUBSTR()

  • Distinct 句

  • UNION、INTERSECT、および EXCEPT

  • IN LIST

Amazon Redshift Spectrum および Aurora PostgreSQL のフェデレーティッドクエリを含む外部クエリの場合、VARCHAR または CHAR 列の照合は、現行のデータベースレベルの照合と同じです。

次の例では、Amazon Redshift Spectrum テーブルをクエリしています。

SELECT ci_varchar FROM spectrum.test_collation
WHERE ci_varchar = 'AMAZON';

ci_varchar
----------
amazon
Amazon
AMAZON
AmaZon
(4 rows)

データベースの照合を使用してテーブルを作成する方法については、「CREATE TABLE」を参照してください。

COLLATE 関数の詳細については、「COLLATE 関数」を参照してください。

データベース照合の制限

Amazon Redshift でデータベース照合を使用する場合、以下の制限事項があります。

  • PG カタログテーブルや Amazon Redshift システムテーブルなど、すべてのシステムテーブルまたはビューでは、大文字と小文字が区別されます。

  • コンシューマデータベースとプロデューサデータベースに異なるデータベースレベルの照合順序がある場合、Amazon Redshift はデータベース間クエリとクラスター間クエリをサポートしません。

  • Amazon Redshift は、リーダーノードのみのクエリで大文字と小文字を区別しない照合をサポートしていません。

    以下に、サポートされていない大文字と小文字を区別しないクエリと、Amazon Redshift から送信されるエラーの例を示します。

    SELECT collate(usename, 'case_insensitive') FROM pg_user;
ERROR:  Case insensitive collation is not supported in leader node only query.

  • Amazon Redshift は、比較、関数、結合、設定オペレーションなど、大文字と小文字を区別する列と区別しない列間でのやり取りはサポートしていません。

    次に、大文字と小文字を区別する列と区別しない列の間で、やり取りした際に発生するエラーの例を示します。

    CREATE TABLE test
  (ci_col varchar(10) COLLATE case_insensitive,
   cs_col varchar(10) COLLATE case_sensitive,
   cint int,
   cbigint bigint);
    SELECT ci_col = cs_col FROM test;
ERROR:  Query with different collations is not supported yet.
    SELECT concat(ci_col, cs_col) FROM test;
ERROR:  Query with different collations is not supported yet.
    SELECT ci_col FROM test UNION SELECT cs_col FROM test;
ERROR:  Query with different collations is not supported yet.
    SELECT * FROM test a, test b WHERE a.ci_col = b.cs_col;
ERROR:  Query with different collations is not supported yet.
    Select Coalesce(ci_col, cs_col) from test;
ERROR:  Query with different collations is not supported yet.
    Select case when cint > 0 then ci_col else cs_col end from test;
ERROR:  Query with different collations is not supported yet.

  • Amazon Redshift は SUPER データ型の照合をサポートしていません。大文字と小文字を区別しないデータベースでの SUPER 列の作成、および SUPER 列と大文字文字を区別しない列間のやり取りはサポートされていません。

    次の例では、大文字と小文字を区別しないデータベースに、データ型に SUPER を使用するテーブルを作成しています。

    CREATE TABLE super_table (a super);
ERROR: SUPER column is not supported in case insensitive database.

    次の例では、SUPER データと比較しながら、大文字と小文字を区別しない文字列を使用するデータをクエリしています。

    CREATE TABLE test_super_collation
(s super, c varchar(10) COLLATE case_insensitive, i int);
    SELECT s = c FROM test_super_collation;
ERROR:  Coercing from case insensitive string to SUPER is not supported.

これらのクエリを機能させるには、COLLATE 関数を使用して、ある列に対する照合を、別の列と一致するように変換します。詳細については、「COLLATE 関数」を参照してください。

例

データベースを作成する

次の例では、TICKIT という名前のデータベースを作成し、その所有権を DWUSER というユーザーに与えます。

create database tickit
with owner dwuser;

データベースに関する詳細を表示するために、PG_DATABASE_INFO カタログテーブルに対しクエリを実行しています。

select datname, datdba, datconnlimit
from pg_database_info
where datdba > 1;

 datname     | datdba | datconnlimit
-------------+--------+-------------
 admin       |    100 | UNLIMITED
 reports     |    100 | 100
 tickit      |    100 | 100

次の例では、スナップショット分離レベルを使用して sampledb という名前のデータベースを作成します。

CREATE DATABASE sampledb ISOLATION LEVEL SNAPSHOT;

次の例では、データ共有 salesshare からデータベース sales_db を作成しています。

CREATE DATABASE sales_db FROM DATASHARE salesshare OF NAMESPACE '13b8833d-17c6-4f16-8fe4-1a018f5ed00d';

データベース照合の例

大文字と小文字を区別しないデータベースの作成

次の例では、データベース sampledb 、およびテーブル T1 を作成し、そのテーブル T1 にデータを挿入します。

create database sampledb collate case_insensitive;

SQL クライアントを使用して、先ほど作成した新しいデータベースに接続します。Amazon Redshift クエリエディタ v2 を使用している場合は、エディタで sampledb を選択します。RSQL を使用している場合は、次のようなコマンドを使用します。

\connect sampledb;
CREATE TABLE T1 (
  col1 Varchar(20) distkey sortkey
);
INSERT INTO T1 VALUES ('bob'), ('john'), ('Mary'), ('JOHN'), ('Bob');

その後、クエリが John を含む結果を検索します。

SELECT * FROM T1 WHERE col1 = 'John';

 col1
 ------
 john
 JOHN
(2 row)
大文字と小文字を区別しない規則での順序付け

次の例は、テーブル T1 での、大文字と小文字を区別しない順序付けを示しています。大文字と小文字を区別しない列では、Bob と bob あるいは John と john は同一に扱われるので、その順序は確定しません。

SELECT * FROM T1 ORDER BY 1;

 col1
 ------
 bob
 Bob
 JOHN
 john
 Mary
(5 rows)

同様に次の例では、GROUP BY 句に対する、大文字と小文字を区別しない順序付けを示しています。Bob と Bob は等し認識されるので、同じグループに属します。どちらが結果に表示されるかは非決定的です。

SELECT col1, count(*) FROM T1 GROUP BY 1;

 col1 | count
 -----+------
 Mary |  1
 bob  |  2
 JOHN |  2
(3 rows)
大文字と小文字を区別しない列でウィンドウ関数を使用してクエリを実行する

次の例では、大文字と小文字を区別しない列に対し、ウィンドウ関数によるクエリを実行します。

SELECT col1, rank() over (ORDER BY col1) FROM T1;

 col1 | rank
 -----+------
 bob  |   1
 Bob  |   1
 john |   3
 JOHN |   3
 Mary |   5
(5 rows)
DISTINCT キーワードを使用したクエリ

次の例に、DISTINCT キーワードを使用しながら、T1テーブルに対しクエリを実行します。

SELECT DISTINCT col1 FROM T1;

 col1
 ------
 bob
 Mary
 john
(3 rows)
UNION 句を使用したクエリ

次の例に、テーブル T1 および T2 に対し UNIONN 句を使用した場合の結果を示します。

CREATE TABLE T2 AS SELECT * FROM T1;
SELECT col1 FROM T1 UNION SELECT col1 FROM T2;

 col1
 ------
 john
 bob
 Mary
(3 rows)