Amazon Redshift DB開発者ガイド – データベース使用入門
はじめに
このエントリは下記ドキュメントの翻訳版的位置付けで書いています。
途中『TICKIT』データベースのテーブル作成の辺りでデータの準備やS3からのロードを行う部分がありますが、該当するサンプルデータは用意しておりません。(※実装公式ドキュメントの方でも見つからなかった)流れ的には追って行けると思いますが、もし実データをAmazon S3にアップしRedshiftにロードする...と言った作業を実践してみたいと言う方は以下のエントリをご参照ください。別テーブルの内容ではありますが、一連の該当部分についても解説しています。
- Amazon Redshiftを始めてみよう(入門ガイド翻訳＆実践：前編) ｜ Developers.IO
- Amazon Redshiftを始めてみよう(入門ガイド翻訳＆実践：後編) ｜ Developers.IO
このセクションでは、Amazon Redshiftのデータベース利用を開始するための基本的な手段を説明します。
このセクションの例では、クラスタを作成し、任意のSQLクエリツールからクラスタへの接続を確立し、Amazon Redshiftデータウェアハウスサービスにサインアップしていると仮定します。これらのタスクの詳細については、Amazon Redshift Getting Started Guideをご参照ください。
注意：
この演習用に作成したクラスタは、実稼働します。稼働している間はAWSアカウント利用料金が発生し続けますので
作業が完了したらクラスタを削除してください。練習の最後のステップではその方法を説明します。
目次
- Step 1: Create a database (データベースの作成)
- Step 2: Create a database user (データベースユーザーの作成)
- Step 3: Create a database table (データベーステーブルの作成)
- Step 4: Load sample data (サンプルデータのロード)
- Step 5: Query the system tables (システムテーブルの検索)
- Step 6: Cancel a query (クエリ実行キャンセル)
- Step 7: Clean up your resources (リソースのクリーンアップ)
Step 1: Create a database (データベースの作成)
クラスタが立ち上がり稼働中である事を確認出来たら、まず最初にデータベースを作成しましょう。このデータベースはあなたが実際に作成し、データをロードし、クエリを実行する場所となります。1つのクラスタは、複数のデータベースをホスト出来ます。例えば、TICKITデータベースとは別に、ORDERSデータベースを持つ事が出来ます。
まず最初にデータベースに接続した後、クラスタ起動時に作成したデータベースは、新しいデータベースを作成するためのベースとして、初期データベースを使用します。例えば、tickitという名前のデータベースを作成するには、次のコマンドを実行します。
create database tickit;
この演習では、我々はデフォルト値を受け入れる事にします。コマンドオプションの詳細については、をご参照ください。
TICKITデータベースを作成後、SQLクライアントから新しいデータベースに接続します。データベースへの接続プロセスは、利用しているSQLクライアントに依存します。詳細については、Connecting to a Clusterをご参照ください。
もしTICKITデータベースに接続したくない場合、デフォルトのデータベースを使用して、当セクションの残りの例を試していく事も可能です。
Step 2: Create a database user (データベースユーザーの作成)
データベースユーザーの作成
デフォルトでは、クラスタを開始した時に作成したマスターユーザーは、クラスタ内の最初のデータベースへのアクセス権を持っています。他のユーザーに対してアクセス権を付与するには、1つ以上のユーザーアカウントを作成する必要があります。データベースユーザーアカウントは、クラスタ内の全てのデータベース間でグローバルであり、彼らは個々のデータベースには属していません。
新しいデータベースユーザーを作成するには、CREATE USERコマンドを使います。新しいユーザーを作成する際は、新しいユーザーの名前とパスワードを指定します。パスワードは入力必須項目となります。8〜64文字、英字(少なくとも1文字は大文字、少なくとも1文字は小文字)または数字が含まれている必要があります。
例えば、GUESTという名前のユーザーをパスワード：ABCd4321で作成するには、以下のコマンドを発行します。
create user guest password 'ABCd4321';
その他のコマンドオプションの詳細については、SQLコマンドリファレンスのCREATE USERをご参照ください。
データベースユーザーの削除
このチュートリアルで使っているゲストユーザーアカウントが不要になったら、それを削除する事が出来ます。
もしデータベースユーザーアカウントを削除した場合、ユーザーはもはやクラスタ・データベースのいずれにもアクセス出来なくなります。GUESTユーザーを削除するには、以下のコマンドを発行します。
drop user guest;
クラスタ作成時に作成したマスターユーザーは、データベースへのアクセス権限を持ち続けています。
重要：
Amazon Redshiftではマスターユーザーを削除しない事を強くお奨めします！
コマンドオプションの詳細については、SQLリファレンスのDROP USERをご参照ください。
Step 3: Create a database table (データベーステーブルの作成)
データベーステーブルの作成
新しいデータベースを作成したら、データベースのデータを保持する為のテーブルを作成します。テーブルを作成する際は表の任意の列情報を指定します。
例えば、整数データ型のtestcolという名前の単一の列でtesttableという名前のテーブルを作成するには、以下のコマンドを実行します。
create table testtable (testcol int);
PG_TABLE_DEFシステムテーブルは、クラスタ内の全ての表に関する情報が 含まれます。結果を確認するには、PG_TABLE_DEFシステムテーブルを照会するには、以下のコマンドを実行します。
select * from pg_table_def where tablename = 'testtable';
クエリ実行結果は以下のようになります。
schemaname|tablename|column | type |encoding|distkey|sortkey | notnull ----------+---------+-------+-------+--------+-------+--------+--------- public |testtable|testcol|integer|none |f | 0 | f (1 row)
デフォルトでは、テーブル等の新しいデータベースオブジェクトは、publicという名前のスキーマに作成されます。スキーマの詳細については、『データベース・セキュリティ管理』セクションのSchemasをご参照ください。
encoding,distkey,sortkey列が並列処理の為にAmazon Redshiftによって使用されます。これらの要素を組み込んだテーブル設計の詳細については、Best practices for designing tablesをご参照ください。
テーブルにデータを登録
テーブルを作成した後は、テーブルにデータを登録する事が出来ます。
注意：
INSERTコマンドはデータベーステーブルに個々の行を登録します。
標準のバルクロードを行う場合、COPYコマンドを使います。
詳細については、Using a COPY command to load dataをご参照ください。
例えば、以下の例ではtesttableテーブル(単一の列を含む)に100を挿入しています。
insert into testtable values (100);
テーブルのデータを検索
テーブルを作成＆データ登録後、テーブルに含まれるデータを表示するには、SELECT文を使用します。 SELECT * 文はテーブル内の全てのデータの全ての列名と行の値を返します。直近追加されたデータが正しくテーブルに登録されている事を確認するための良い方法です。
testtableテーブルに入力したデータを表示するには、以下のコマンドを実行します。
select * from testtable;
結果はこのようになります。
testcol --------- 100 (1 row)
テーブルへのSELECTステートメントを使用する方法の詳細については、SQLコマンドリファレンスのSELECTをご参照ください。
Step 4: Load sample data (サンプルデータのロード)
このマニュアルの例の殆どは、TICKITサンプルデータベースを使用します。SQLクエリツールを使用して例をフォローしていきたい場合は、TICKITデータベース用のサンプルデータをロードする必要があります。
これらの例のサンプルデータは、Amazon S3バケットのawssampledbに設けられています。任意の有効なAWSアカウントで、データファイルへの読み取りアクセス件を持ちます。(と仮定します)
注意：
Amazon Redshift入門ガイドの手順に従っていた場合、これらのテーブルは既に存在しています。
TICKITデータベース用のサンプルデータをロードするには、まずテーブルを作成しますが、その後Amazon S3バケットに格納されているサンプルデータを持つ表をロードする為にCOPYコマンドを使用します。詳細については、Loading data from Amazon S3をご参照ください。
カラム名のリストと、それぞれ対でデータ型を定義したCREATE TABLEコマンドを使い、テーブルを作成します。この例でのCREATE TABLE文の多くは、追加でデータ型に対するオプション(not null,distkey,sortkey)を指定しています。これらは、クエリパフォーマンスに対するテーブル最適化の為の列関連属性です。
1.データベースのテーブルを作成
以下のSQLは、USERS, VENUE, CATEGORY, DATE, EVENT, LISTING, SALESの各テーブルを作成します。テーブル構造等の詳細は以下リンクをご参照ください。
create table users(
userid integer not null distkey sortkey,
username char(8),
firstname varchar(30),
lastname varchar(30),
city varchar(30),
state char(2),
email varchar(100),
phone char(14),
likesports boolean,
liketheatre boolean,
likeconcerts boolean,
likejazz boolean,
likeclassical boolean,
likeopera boolean,
likerock boolean,
likevegas boolean,
likebroadway boolean,
likemusicals boolean);
create table venue(
venueid smallint not null distkey sortkey,
venuename varchar(100),
venuecity varchar(30),
venuestate char(2),
venueseats integer);
create table category(
catid smallint not null distkey sortkey,
catgroup varchar(10),
catname varchar(10),
catdesc varchar(50));
create table date(
dateid smallint not null distkey sortkey,
caldate date not null,
day character(3) not null,
week smallint not null,
month character(5) not null,
qtr character(5) not null,
year smallint not null,
holiday boolean default('N'));
create table event(
eventid integer not null distkey,
venueid smallint not null,
catid smallint not null,
dateid smallint not null sortkey,
eventname varchar(200),
starttime timestamp);
create table listing(
listid integer not null distkey,
sellerid integer not null,
eventid integer not null,
dateid smallint not null sortkey,
numtickets smallint not null,
priceperticket decimal(8,2),
totalprice decimal(8,2),
listtime timestamp);
create table sales(
salesid integer not null,
listid integer not null distkey,
sellerid integer not null,
buyerid integer not null,
eventid integer not null,
dateid smallint not null sortkey,
qtysold smallint not null,
pricepaid decimal(8,2),
commission decimal(8,2),
saletime timestamp);
2.テーブルデータのロード
このステップでは、Amazon S3バケットからのデータを使用してテーブルをロードする為にCOPYコマンドを使用します。Amazon S3のバケットawssampledbは、これらの例で使用する為のサンプルデータが含まれています。バケットは、publicでREAD権限を持っており、有効な任意のAWSアカウントがデータへのアクセス権を持っています。(※と仮定します)
これらのCOPYコマンドを使用するには、有効なAWSアカウントの認証情報を使用して
重要：
この例では、米国東部(ヴァージニア北部)地域に位置しているAmazon S3のバケットを使用しています。
COPYコマンドを使用してデータをロードするには、データを含むバケットはクラスタと同じ地域にある必要があります。
クラスタが米国東部以外のリージョンにある場合は、別のバケットを用意する必要があります。
別の領域からサンプルデータをロードする方法については、Amazon Redshift入門ガイドの
Create Tables, Upload Data, and Try Example Queriesをご参照ください。
copy users from 's3://awssampledb/tickit/allusers_pipe.txt' CREDENTIALS 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>' delimiter '|'; copy venue from 's3://awssampledb/tickit/venue_pipe.txt' CREDENTIALS 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>' delimiter '|'; copy category from 's3://awssampledb/tickit/category_pipe.txt' CREDENTIALS 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>' delimiter '|'; copy date from 's3://awssampledb/tickit/date2008_pipe.txt' CREDENTIALS 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>' delimiter '|'; copy event from 's3://awssampledb/tickit/allevents_pipe.txt' CREDENTIALS 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>' delimiter '|' timeformat 'YYYY-MM-DD HH:MI:SS'; copy listing from 's3://awssampledb/tickit/listings_pipe.txt' CREDENTIALS 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>' delimiter '|'; copy sales from 's3://awssampledb/tickit/sales_tab.txt' CREDENTIALS 'aws_access_key_id=<your-access-key-id>;aws_secret_access_key=<your-secret-access-key>' delimiter '\t' timeformat 'MM/DD/YYYY HH:MI:SS';
3.ロードした結果を確認
テーブルが作成され、データがロードされたことを確認するには、次のSELECT文を使用します。SELECT COUNT(*)文は、テーブル内の行数を返します。
select count(*) from users; select count(*) from venue; select count(*) from category; select count(*) from date; select count(*) from event; select count(*) from listing; select count(*) from sales;
Step 5: Query the system tables (システムテーブルの検索)
自分で作成したテーブルに加えて、データベースにはシステムテーブルが幾つか存在します。これらのシステムテーブルは、インストール及びシステム上で実行されている様々なクエリとプロセスに関する情報が含まれています。データベースに関する情報を収集するために、これらのシステムテーブルを照会する事が出来ます。
メモ：
システムテーブルリファレンスの各テーブルの説明は、
テーブルがスーパーユーザー又は一般ユーザーに見えているかどうかを示しています。
スーパーユーザーが見えるテーブルにアクセスする際は、スーパーユーザーとしてログインする必要があります。
Amazon Redshiftは以下のシステムテーブルに関するアクセスを提供しています。
- STL tables for logging
- これらのシステムテーブルは、システムの履歴を提供する為にAmazon Redshiftのログファイルから生成されます。ログテーブルがSTLの接頭辞を持っています。
- STV tables for snapshot data
- これらのテーブルは、現在のシステムデータのスナップショットを含む仮想システムテーブルです。スナップショットのテーブルは、STVの接頭辞を持っています。
- System views
- システムビューは、STLとSTVシステムテーブルの幾つかで検出されたデータのサブセットが含まれています。システムビューはSVVまたはSVLの接頭辞を持っています。
- System catalog tables
- システムカタログテーブルはテーブルや列の情報のようなスキーマのメタデータを格納しています。PGの接頭辞を持っています。
クエリに関するシステムテーブル情報を取得するクエリを、関連付けられたプロセスIDを以って指定・取得する必要が出てくるかもしれません。詳細については、Determine the process ID of a running queryをご参照ください。
テーブル名一覧の表示
例えば、パブリックスキーマ内の全てのテーブルリストを表示するには、PG_TABLE_DEFシステムカタログ表を照会出来ます。
select tablename from pg_table_def where schemaname = 'public';
結果は以下の様な内容となります。
tablename --------- category date event listing sales testtable users venue
データベースユーザーの表示
あなたはユーザーID(USESYSID)とユーザー権限を持つと共に、全てのデータベースユーザーのリストを表示するPG_USERカタログを照会する事が出来ます。
select * from pg_user;
結果は以下の様な内容となります。
usename | usesysid | usecreatedb | usesuper | usecatupd | passwd | valuntil | useconfig -----------+----------+-------------+----------+-----------+----------+----------+---------- masteruser| 100 | t | t | f | ******** | | rdsdb | 1 | t | t | t | ******** | | (2 rows)
最近実行したクエリ情報の表示
前述の例では、masterの為のユーザーID(usesysid)が100である事は分かりました。masteruserによって実行される最新5つのクエリを一覧表示するには、STL_QLOGビューを問い合わせる事が出来ます。SVL_QLOGビューはSTL_QUERYテーブルからのサブセット情報です。あなたは、最近実行したクエリに対してクエリID(QUERY)またはプロセスID(PID)を見つける為に、又はクエリ実行が完了するまでに掛かった時間を表示する為に、このビューを使用する事が出来ます。SVL_QLOGは、あなたが特定のクエリを見付け易くするために、クエリ文字列(SUBSTRING)の最初の60文字が含まれています。5行に結果を制限する為に、SELECT文でLIMIT句を使用します。
select query, pid, elapsed, substring from svl_qlog where userid = 100 order by starttime desc limit 5;
結果は以下の様な内容となります。
query | pid | elapsed | substring --------+-------+----------+-------------------------------------------------------------- 187752 | 18921 | 18465685 | select query, elapsed, substring from svl_qlog order by query 204168 | 5117 | 59603 | insert into testtable values (100); 187561 | 17046 | 1003052 | select * from pg_table_def where tablename = 'testtable'; 187549 | 17046 | 1108584 | select * from STV_WLM_SERVICE_CLASS_CONFIG 187468 | 17046 | 5670661 | select * from pg_table_def where schemaname = 'public'; (5 rows)
実行中クエリのプロセスIDを判別
前の例では、SVL_QLOGビューから完成したクエリに対してクエリIDとプロセスID(PID)を取得する方法を学びました。
あなたは実行中クエリのプロセスIDを取得する必要が出てくるかも知れません。実行する為に時間が掛かり過ぎているクエリをキャンセルする必要がある場合、例えばPIDが必要になったりします。対応するクエリ文字列と併せてクエリを実行する為のプロセスIDのリストを取得する為にSTV_RECENTSシステムテーブルを照会出来ます。クエリが複数のPIDを返した場合、必要なPIDを判断するクエリテキストを見る事が出来ます。
実行中のクエリのPIDを決定するには、以下のSELECT文を実行します。
select pid, user_name, starttime, query from stv_recents where status='Running';
Step 6: Cancel a query (クエリ実行キャンセル)
ユーザーにとってあまりにもクラスタリソースを食いつぶすような、実行時間の長いクエリを発行した場合は、クエリをキャンセルする必要が出てくるかも知れません。例えば、ユーザーが販売したチケットの販売者の名前と件数を含むチケット販売業者のリストを作成したい場合があるとします。以下のクエリは、SALESテーブルとUSERテーブルからデータを選択し、WHERE句でSELLERIDとUSERIDを照合する事によって、2つのテーブルを結合します。(注：これは複雑なクエリです。このチュートリアルでは、このクエリを構築する方法について気にする必要はありません)
select sellerid, firstname, lastname, sum(qtysold) from sales, users where sales.sellerid = users.userid group by sellerid, firstname, lastname order by 4 desc;
この問い合わせは秒単位で実行され、2,102行の結果を返します。これが仮に、ユーザーがWHERE句を置くことを忘れていたとしましょう。
select sellerid, firstname, lastname, sum(qtysold) from sales, users group by sellerid, firstname, lastname order by 4 desc;
結果セットには、USERテーブルの全ての行(49989*3766)を乗じたSALESテーブルの全ての行が含まれます。これは、デカルト結合と呼ばれ、推奨されるものではありません。結果は188万人以上の行となり、実行に長い時間が掛かります。
実行中のクエリをキャンセルするには、クエリのPIDとCANCELコマンドを使用します。
プロセスIDを検索するには、前の手順に示すように、STV_RECENTSテーブルを照会します。次の例では、末尾のスペースをトリミングするTRIM関数を使用し、クエリ文字列の最初の20文字のみを示す事によって、結果を読み易くする事が出来るのを示しています。
select pid, trim(user_name), starttime, substring(query,1,20) from stv_recents where status='Running';
結果は以下のようになります。
pid | btrim | starttime | substring -------+------------+----------------------------+---------------------- 18764 | masteruser | 2013-03-28 18:39:49.355918 | select sellerid, fir (1 row)
PID18764とクエリをキャンセルするには、以下のコマンドを実行します。
cancel 18764;
注意：
CANCELコマンドは、トランザクションを中断しません。
中止もしくはトランザクションのロールバックを行うには、
ABORT又はROLLBACKコマンドを使用する必要があります。
トランザクションに関連付けられているクエリをキャンセルするには、
最初のトランザクションを中断し、クエリをキャンセルします。
あなたがキャンセルしたクエリがトランザクションに関連付けられている場合、ABORT又はROLLBACKを使用しています。トランザクションをキャンセルし、データに加えられた変更を破棄するには、以下を実行します。
abort;
スーパーユーザーとして署名していない限り、あなたが独自で実行したクエリをキャンセルする事が出来ます。また、スーパーユーザーは全てのクエリをキャンセルする事が出来ます。
他のセッションからクエリをキャンセルする
クエリツールが同時にクエリの同時発行をサポートしていない場合は、クエリをキャンセルする為に別のセッションを開始する必要があります。例えばSQL Workbenchは入門ガイドでクエリ実行に使いましたが、複数同時クエリ発行をサポートしていません。SQL Workbenchを使って新しい別のセッションを開始するには、[ファイル]→[新しいウインドウ]で別のセッションを開始し、同じ接続パラメータを使って接続します。そして、PIDを見つけてクエリをキャンセルする事が出来ます。
スーパーユーザーのキューを使ってクエリをキャンセルする
もし現在のセッションがあまりにも多くの同時実行クエリを保持していた場合、別のクエリが終了するまでCANCELコマンドを実行出来ない事があります。その場合、別のわ～kロード管理クエリキューを使ってCANCELコマンドを発行する必要があります。
ワークロード管理を使用すると、別のクエリキューでクエリを実行出来るようになり、完了するために別のクエリの状態を待つ必要が無くなります。ワークロードマネージャを使うと、トラブルシューティングに使用出来る"スーパーキュー"と呼ばれる別のキューを作成します。スーパーキューを使用するには、スーパーユーザでログインし、SETコマンドを使用して"superuser"にクエリグループを設定する必要があります。コマンド実行後は、RESETコマンドでクエリグループをリセットします。
スーパーキューを使ってクエリをキャンセルするには、以下のコマンドを実行します。
set query_group to 'superuser'; cancel 18764; reset query_group;
Step 7: Clean up your resources (リソースのクリーンアップ)
この演習を実行する為にクラスタをデプロイした場合、利用料金がこれ以上掛かってしまうのを避ける為に、演習が終わったらクラスタを削除する必要があります。
クラスタを削除するには、Amazon Redshift管理ガイドのDeleting a Clusterをご参照ください。
クラスタを残しておきたい場合は、参照用のサンプルデータを保持したいと思うかも知れません。このマニュアルの例の殆どは、この演習で作成したテーブルを使用しています。データのサイズは、使用可能なストレージに対して重大な影響を及ぼす事はありません。
クラスタを維持したいがサンプルデータをクリーンアップしたい、という場合は以下のようにTICKITデータベースをドロップしてください。
drop table testtable; drop table users; drop table venue; drop table category; drop table date; drop table event; drop table listing; drop table sales;
