CSA JMCで実践！テーブル作成(Amazon Redshift環境でのテーブル作成をコード記述無しで行う) #データ統合基盤 #CSアナリティクス
当エントリは『クラスメソッド CSアナリティクス Advent Calendar 2020』20日目のエントリです。
- クラスメソッド CSアナリティクス Advent Calendar 2020 - Qiita
- クラスメソッド CSアナリティクス Advent Calendar 2020 ｜ 特集カテゴリー ｜ Developers.IO
- ビッグデータ分析支援のカスタマーストーリーアナリティクス｜クラスメソッド｜クラスメソッドのサービス
当エントリでは、クラスメソッドが展開しているデータ統合基盤サービス『CSアナリティクス』(以降"CSA")のプロダクト「CSA JMC(Job Management Console)」でAmazon Redshift環境上にテーブルを作成する際の手順について紹介します。
目次
Amazon Redshiftにおけるテーブル作成の進め方と課題
Amazon Redshiftにおけるテーブル作成はRedshiftのSQLコマンドとして用意されている「CREATE TABLE」を使って行います。関連オプションはこの他にも幾つかありますが、基本的にはこのコマンドを使う形となっています。
テーブル作成の際にCREATE TABELコマンドを用いる、というのはデータベースの世界ではほぼ標準的なものとなっていますが、実際この手順を使ってテーブル作成を行う際には、以下のような問題・課題がつきまとってきます。多少なりともデータベースやデータウェアハウスを用いたシステムを構築・運用されてきた方々であれば思い当たる節もあるかと思います。
- テーブルの列に相当する項目毎の属性や内容(文字列の場合であれば桁数、数値の場合であれば最大値)を把握していないといけない
- ファイルの場合、そもそも関連する定義書的なものが存在しているかどうか分からない
- (定義書的なものが)存在していたとしても、現行最新版であるか、ファイルやテーブルの仕様的に合っているかどうかが分からない
- 資料があったとしても、CREATE TABLE文をそこから書き起こさないといけない
- テーブル毎の列の数が多くなってくる、またはテーブルの数自体が増えてくるとこれらの情報を精査する・個別対応するのがそもそも面倒だ
CSA JMCではテーブル作成を良い感じにシステム側で対応します
CSA JMCでは、この「テーブル作成(CREATE TABLE文の準備と実行)」にまつわる手間や労力を削減するために「入力情報を元に、システム側でテーブル作成をよしなに対応してしまう」方式を採用しました。
ここでの「入力情報」は「作成するテーブルに取り込む際のデータファイル」となります。所定の場所に、作成するテーブルのデータ元となるファイルを単一または複数個配備しておき、画面操作で必要な設定を行うことで、前述掲載したような「テーブル作成(CREATE TABLE)を行う際の諸々の面倒な作業」無しでテーブルを用意することが可能となります。
CSA JMCでのテーブル作成実演
ここからは、CSA JMCで取り得る事が出来る「テーブル作成」の手順についてそれぞれ紹介していきます。
テーブル作成時に用いるサンプルデータはAmazon S3に予めアップロードしておいたファイルを用います。(今回はExploratoryで利用出来るデータカタログの中から相撲の取り組み結果に関するデータを使いました)
サイトで指定した「Amazon S3連携バケット」に配備したinit/任意のテーブルを想定したフォルダ配下の内容をCSA JMCは認識してテーブル作成を行います。テーブル作成に用いるファイルは「任意のフォルダ配下のファイル群」を対象としますので、識別可能なフォルダ配下のファイルは1つ以上、且つ同じカラム情報を持つもので無ければなりません。
画面操作によるテーブル作成(ログ取り込みモード)
前述した「入力情報を元に、システム側でテーブル作成をよしなに対応してしまう」機能が「テーブル作成」機能です。機能を利用する際は、CSA JMCのメニューから[リソース]→[テーブル作成(ファイルから)]を選択します。
「ファイルからのテーブル作成」はその文言の通り、Amazon S3にアップロードされているファイルを元にテーブルを作り出す機能です。CSA JMCで選択出来るモードは以下の3つ。ここでは一番上の「ログ取り込みモード」を指定します。この「ログ取り込みモード」は、対象データの桁数や最大サイズが把握出来ていない、分からない(例えばログファイルのようなもの)データを対象として取り込みたい、データのカラム毎の最大値を意識しない形で、"桁数あふれ"による取りこぼしがない形でテーブルにデータを取り込みたいという場合に有効です。
- ログ取り込みモード：カラム毎の桁数・サイズに関する定義を「属性毎に取り得る最大値」で指定したテーブル作成を行う
- DB取り込みモード：カラム毎の桁数・サイズに関する定義を「対象データにおける最大値」で指定したテーブル作成を行う
- カスタムモード：上記2つの方法以外でテーブル作成を行う／構成要素(SQL)による実践へ案内(※後述)
「ログ取り込みモード」を選択すると、以下のような形で入力フォームが現れます。ここでは、テーブル対象となる入力情報となるファイル群がアップロードされているS3バケットや、「作成する対象のテーブル」に関する各種属性情報を画面経由で指定します。項目群を見て頂くと、列毎の定義に関する指定項目が存在しないことがお分かり頂けるかと思います。
それぞれの値を指定した上で、[作成]を押下。
テーブル作成処理が実行されました。
ジョブ実行履歴画面では、テーブル作成の進行情報についても確認可能です。作成直後はステータスが[実行中]となっていますが、
程なくするとステータスが[成功]となり、作成が完了します。(※処理時間は入力情報や対象データのボリュームにも依ります)
実行完了後の履歴からログを確認する事が出来ます。履歴をスクロールしていくと、以下のような形で「作成に用いたCREATE TABLE文」を確認出来ます。
該当DDL文の行を抜粋したのが以下の内容です。前述したように、カラムの属性毎の最大値(文字列型であればVARCHAR(65535)、数値であればbigint／これらはAmazon Redshiftにおけるデータ型の最大値から設定しています)を以て列毎の定義が為されてることが確認出来ます。
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ------------------------------------------------------------------------------------------------------------------------------------------------
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: [DDL]
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ------------------------------------------------------------------------------------------------------------------------------------------------
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: CREATE TABLE IF NOT EXISTS "csademo"."sumo_match_results_logmode" (
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: "basho_id" bigint NOT NULL
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"day" bigint encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"name" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"link" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"rank" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"status" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"star" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"decide" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"opponent_name" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"opponent_link" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"opponent_rank" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"opponent_status" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"year" character varying(65535) NOT NULL
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"basho" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,709] {base_task_runner.py:97} INFO - Subtask: ,"player_id" bigint NOT NULL
[2020-12-20 xx:xx:xx,710] {base_task_runner.py:97} INFO - Subtask: ,"opponent_id" bigint encode bytedict NOT NULL
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"latest_name" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"first_name_player" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"heya_player" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"real_name_player" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"name_changes_player" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"latest_rank" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"birthday_player" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"home_1_player" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"home_2_player" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"height_player" double precision encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"weight_player" double precision encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"favorite_technique_player" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"opponent_latest_name" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,715] {base_task_runner.py:97} INFO - Subtask: ,"first_name_opponent" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"heya_opponent" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"real_name_opponent" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"name_changes_opponent" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"opponent_latest_rank" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"birthday_opponent" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"home_1_opponent" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"home_2_opponent" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"height_opponent" double precision encode bytedict
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"weight_opponent" double precision encode bytedict
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"favorite_technique_opponent" character varying(65535) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"csa_dwh_file_path" character varying(200) encode zstd
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ,"csa_dwh_created_at" timestamp without time zone encode az64
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: , PRIMARY KEY ("basho_id", "year", "player_id", "opponent_id")
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: )
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: DISTSTYLE even
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: compound SORTKEY ("year", "basho_id", "player_id")
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ;
[2020-12-20 xx:xx:xx,716] {base_task_runner.py:97} INFO - Subtask: ------------------------------------------------------------------------------------------------------------------------------------------------
実際に作成されたテーブルを、Amazon Redshift上のSQLコマンドでも確認してみます。以下のような形で、DDL文に基づいた形で定義されていることが確認出来ました。
# \d csademo.sumo_match_results_logmode;
Table "csademo.sumo_match_results_logmode"
Column | Type | Collation | Nullable | Default
-----------------------------+-----------------------------+-----------+----------+---------
basho_id | bigint | | not null |
day | bigint | | |
name | character varying(65535) | | |
link | character varying(65535) | | |
rank | character varying(65535) | | |
status | character varying(65535) | | |
star | character varying(65535) | | |
decide | character varying(65535) | | |
opponent_name | character varying(65535) | | |
opponent_link | character varying(65535) | | |
opponent_rank | character varying(65535) | | |
opponent_status | character varying(65535) | | |
year | character varying(65535) | | not null |
basho | character varying(65535) | | |
player_id | bigint | | not null |
opponent_id | bigint | | not null |
latest_name | character varying(65535) | | |
first_name_player | character varying(65535) | | |
heya_player | character varying(65535) | | |
real_name_player | character varying(65535) | | |
name_changes_player | character varying(65535) | | |
latest_rank | character varying(65535) | | |
birthday_player | character varying(65535) | | |
home_1_player | character varying(65535) | | |
home_2_player | character varying(65535) | | |
height_player | double precision | | |
weight_player | double precision | | |
favorite_technique_player | character varying(65535) | | |
opponent_latest_name | character varying(65535) | | |
first_name_opponent | character varying(65535) | | |
heya_opponent | character varying(65535) | | |
real_name_opponent | character varying(65535) | | |
name_changes_opponent | character varying(65535) | | |
opponent_latest_rank | character varying(65535) | | |
birthday_opponent | character varying(65535) | | |
home_1_opponent | character varying(65535) | | |
home_2_opponent | character varying(65535) | | |
height_opponent | double precision | | |
weight_opponent | double precision | | |
favorite_technique_opponent | character varying(65535) | | |
csa_dwh_file_path | character varying(200) | | |
csa_dwh_created_at | timestamp without time zone | | |
Indexes:
"sumo_match_results_logmode_tmp_for_ginga_pkey1" PRIMARY KEY, btree ("year", basho_id, player_id, opponent_id)
ついでにデータ内容も確認してみます。このあたりは作成時の指定内容に依りますが、今回はテーブル作成後のデータは残したままとしていますので、そのあたりの設定も反映されていることが実データからも確認出来ています。
# SELECT * FROM csademo.sumo_match_results_logmode LIMIT 10;
basho_id | day | name | link | rank | status | star | decide | opponent_name | opponent_link | opponent_rank | opponent_status | year | basho | player_id | opponent_id | latest_name | first_name_player | heya_player | real_name_player | name_changes_player | latest_rank | birthday_player | home_1_player | home_2_player | height_player | weight_player | favorite_technique_player | opponent_latest_name | first_name_opponent | heya_opponent | real_name_opponent | name_changes_opponent | opponent_latest_rank | birthday_opponent | home_1_opponent | home_2_opponent | height_opponent | weight_opponent | favorite_technique_opponent | csa_dwh_file_path | csa_dwh_created_at
----------+-----+----------+---------------------------------+------------+------------+------+----------+---------------+------------------------------------+---------------+-----------------+--------------+----------+-----------+-------------+-------------+-------------------+-------------+------------------+---------------------+-------------+-----------------+---------------+---------------+---------------+---------------+---------------------------+----------------------+---------------------+---------------+------------------------------+-----------------------+----------------------+-------------------+-----------------+-----------------+-----------------+-----------------+-----------------------------+----------------------------------------------------------------------------------------------+---------------------
533 | 6 | 土佐ノ海 | /ResultRikishiData/profile?id=5 | 前頭九枚目 | （2勝4敗） | 勝 | 寄り切り | 千代白鵬 | /ResultRikishiData/profile?id=2101 | 前頭 | （3勝3敗） | 平成二十一年 | 一月場所 | 5 | 2101 | 土佐ノ海 | 敏生 | 伊勢ノ海 | 山本 敏生 | 土佐ノ海 | 関脇 | 昭和47年2月16日 | 高知 | 安芸市 | 185 | 141 | 突き・押し | 千代白鵬 | 大樹 | 九重 | 柿内 大樹 | 柿内 → 千代白鵬 | 前頭 | 昭和58年4月21日 | 熊本 | 山鹿市 | 182 | 135 | 突き・押し | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
533 | 9 | 土佐ノ海 | /ResultRikishiData/profile?id=5 | 前頭九枚目 | （3勝6敗） | 負 | 押し出し | 北勝力 | /ResultRikishiData/profile?id=1034 | 前頭 | （6勝3敗） | 平成二十一年 | 一月場所 | 5 | 1034 | 土佐ノ海 | 敏生 | 伊勢ノ海 | 山本 敏生 | 土佐ノ海 | 関脇 | 昭和47年2月16日 | 高知 | 安芸市 | 185 | 141 | 突き・押し | 北勝力 | 英樹 | 八角 | 木村 英樹 | 北勝力 | 関脇 | 昭和52年10月31日 | 栃木 | 大田原市 | 182 | 136 | 突き・押し | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
533 | 10 | 土佐ノ海 | /ResultRikishiData/profile?id=5 | 前頭九枚目 | （4勝6敗） | 勝 | 叩き込み | 豊響 | /ResultRikishiData/profile?id=2763 | 前頭 | （3勝7敗） | 平成二十一年 | 一月場所 | 5 | 2763 | 土佐ノ海 | 敏生 | 伊勢ノ海 | 山本 敏生 | 土佐ノ海 | 関脇 | 昭和47年2月16日 | 高知 | 安芸市 | 185 | 141 | 突き・押し | 豊響 | 隆太 | 境川 | 門元 隆太 | 門元 → 豊響 | 十両 | 昭和59年11月16日 | 山口 | 下関市 | 185 | 181 | 突き・押し | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
533 | 4 | 土佐ノ海 | /ResultRikishiData/profile?id=5 | 前頭九枚目 | （1勝3敗） | 負 | 叩き込み | 阿覧 | /ResultRikishiData/profile?id=2964 | 前頭 | （2勝2敗） | 平成二十一年 | 一月場所 | 5 | 2964 | 土佐ノ海 | 敏生 | 伊勢ノ海 | 山本 敏生 | 土佐ノ海 | 関脇 | 昭和47年2月16日 | 高知 | 安芸市 | 185 | 141 | 突き・押し | 阿覧 | 欧虎 | 春日野 | ガバライエフ・アラン | 阿覧 | 関脇 | 昭和59年1月31日 | ロシア | ウラジカフカス | 187 | 142 | 右四つ・寄り・押し | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
533 | 14 | 土佐ノ海 | /ResultRikishiData/profile?id=5 | 前頭九枚目 | （5勝9敗） | 負 | 寄り切り | 栃ノ心 | /ResultRikishiData/profile?id=2895 | 前頭 | （7勝7敗） | 平成二十一年 | 一月場所 | 5 | 2895 | 土佐ノ海 | 敏生 | 伊勢ノ海 | 山本 敏生 | 土佐ノ海 | 関脇 | 昭和47年2月16日 | 高知 | 安芸市 | 185 | 141 | 突き・押し | 栃ノ心 | 剛史 | 春日野 | レヴァニ・ゴルガゼ | 栃ノ心 | 前頭 | 昭和62年10月13日 | ジョージア | ムツケタ | 191 | 173 | 右四つ・上手投げ | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
533 | 3 | 土佐ノ海 | /ResultRikishiData/profile?id=5 | 前頭九枚目 | （1勝2敗） | 負 | 上手投げ | 栃乃洋 | /ResultRikishiData/profile?id=22 | 前頭 | （3勝0敗） | 平成二十一年 | 一月場所 | 5 | 22 | 土佐ノ海 | 敏生 | 伊勢ノ海 | 山本 敏生 | 土佐ノ海 | 関脇 | 昭和47年2月16日 | 高知 | 安芸市 | 185 | 141 | 突き・押し | 栃乃洋 | 泰一 | 春日野 | 後藤 泰一 | 後藤 → 栃乃洋 | 関脇 | 昭和49年2月26日 | 石川 | 七尾市 | 186 | 160 | 左四つ・寄り | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
533 | 13 | 土佐ノ海 | /ResultRikishiData/profile?id=5 | 前頭九枚目 | （5勝8敗） | 負 | 押し出し | 玉乃島 | /ResultRikishiData/profile?id=1783 | 前頭 | （10勝3敗） | 平成二十一年 | 一月場所 | 5 | 1783 | 土佐ノ海 | 敏生 | 伊勢ノ海 | 山本 敏生 | 土佐ノ海 | 関脇 | 昭和47年2月16日 | 高知 | 安芸市 | 185 | 141 | 突き・押し | 玉乃島 | 新 | 片男波 | 岡部 新 | 玉ノ洋 → 玉乃島 | 関脇 | 昭和52年9月15日 | 福島 | 西白河郡泉崎村 | 186 | 161 | 左四つ・寄り | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
533 | 14 | 魁皇 | /ResultRikishiData/profile?id=6 | 大関 | （8勝6敗） | 負 | 寄り切り | 朝青龍 | /ResultRikishiData/profile?id=100 | 横綱 | （14勝0敗） | 平成二十一年 | 一月場所 | 6 | 100 | 魁皇 | 博之 | 友綱 | 古賀 博之 | 古賀 → 魁皇 | 大関 | 昭和47年7月24日 | 福岡 | 直方市 | 185 | 164 | 左四つ・寄り・上手投げ | 朝青龍 | 明徳 | 高砂 | ドルゴルスレン・ダグワドルジ | 朝青龍 | 横綱 | 昭和55年9月27日 | モンゴル | ウランバートル | 184 | 154 | 右四つ・寄り・突っ張り | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
533 | 8 | 魁皇 | /ResultRikishiData/profile?id=6 | 大関 | （5勝3敗） | 負 | 押し出し | 日馬富士 | /ResultRikishiData/profile?id=2308 | 大関 | （3勝5敗） | 平成二十一年 | 一月場所 | 6 | 2308 | 魁皇 | 博之 | 友綱 | 古賀 博之 | 古賀 → 魁皇 | 大関 | 昭和47年7月24日 | 福岡 | 直方市 | 185 | 164 | 左四つ・寄り・上手投げ | 日馬富士 | 公平 | 伊勢ヶ濱 | ダワーニャム・ビャンバドルジ | 安馬 → 日馬富士 | 横綱 | 昭和59年4月14日 | モンゴル | ゴビアルタイ | 186 | 137 | 右四つ・寄り | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
533 | 9 | 魁皇 | /ResultRikishiData/profile?id=6 | 大関 | （6勝3敗） | 勝 | 寄り切り | 千代大海 | /ResultRikishiData/profile?id=30 | 大関 | （6勝3敗） | 平成二十一年 | 一月場所 | 6 | 30 | 魁皇 | 博之 | 友綱 | 古賀 博之 | 古賀 → 魁皇 | 大関 | 昭和47年7月24日 | 福岡 | 直方市 | 185 | 164 | 左四つ・寄り・上手投げ | 千代大海 | 龍二 | 九重 | 須藤 龍二 | 廣嶋 → 千代大海 | 大関 | 昭和51年4月29日 | 大分 | 大分市 | 180 | 145 | 突き・押し | s3://csa-jmc-v5demo-redshift-copy/init/exploratory-data-catalog/sumo-wrestler-match-results/ | 2020-12-20 xx:xx:xx
(10 rows)
画面操作によるテーブル作成(DB取り込みモード)
CSA JMCでは「ログ取り込みモード」の他にもう1つ「DB取り込みモード」でテーブルを作成することが出来ます。この「ログ取り込みモード」は、テーブル定義が存在しているようなデータ(任意のDBからエクスポートされたファイル)、桁数定義がしっかりしているようなデータを取り込む際に有効な方式となります。
「ログ取り込みモード」との違いは、画面操作上では「モードの選択内容」が異なるのみです。「作成されたテーブルの定義」に関する違いがあるのですが、それは後述します。ここでは作成するテーブル名をログ取り込みモードのものとは変えて(それ以外の条件は同じにして)作成してみます。
処理完了後、作成されたテーブルの定義内容を確認してみます。「DB取り込みモード」では、列毎に「対象ファイルの内容を走査した上で、その中で最も大きい値を判断材料として定義を行う」形を採っています。このため、nameであればVARCHAR(15)、linkであればVARCHAR(34)、player_idであればsmallint...という定義になっています。
------------------------------------------------------------------------------------------------------------------------------------------------
[2020-12-20 xx:xx:xx,788] {base_task_runner.py:97} INFO - Subtask: [DDL]
[2020-12-20 xx:xx:xx,788] {base_task_runner.py:97} INFO - Subtask: ------------------------------------------------------------------------------------------------------------------------------------------------
[2020-12-20 xx:xx:xx,788] {base_task_runner.py:97} INFO - Subtask: CREATE TABLE IF NOT EXISTS "csademo"."sumo_match_results_dbmode" (
[2020-12-20 xx:xx:xx,788] {base_task_runner.py:97} INFO - Subtask: "basho_id" smallint NOT NULL
[2020-12-20 xx:xx:xx,788] {base_task_runner.py:97} INFO - Subtask: ,"day" smallint encode az64
[2020-12-20 xx:xx:xx,788] {base_task_runner.py:97} INFO - Subtask: ,"name" character varying(15) encode zstd
[2020-12-20 xx:xx:xx,788] {base_task_runner.py:97} INFO - Subtask: ,"link" character varying(34) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"rank" character varying(18) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"status" character varying(19) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"star" character varying(9) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"decide" character varying(21) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"opponent_name" character varying(15) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"opponent_link" character varying(34) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"opponent_rank" character varying(12) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"opponent_status" character varying(19) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"year" character varying(18) NOT NULL
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"basho" character varying(24) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"player_id" smallint NOT NULL
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"opponent_id" smallint encode bytedict NOT NULL
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"latest_name" character varying(15) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"first_name_player" character varying(9) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"heya_player" character varying(12) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"real_name_player" character varying(102) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"name_changes_player" character varying(62) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"latest_rank" character varying(12) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"birthday_player" character varying(22) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"home_1_player" character varying(15) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"home_2_player" character varying(24) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"height_player" double precision encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"weight_player" double precision encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"favorite_technique_player" character varying(36) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"opponent_latest_name" character varying(15) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"first_name_opponent" character varying(9) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"heya_opponent" character varying(12) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"real_name_opponent" character varying(102) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"name_changes_opponent" character varying(62) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"opponent_latest_rank" character varying(12) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"birthday_opponent" character varying(22) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"home_1_opponent" character varying(15) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"home_2_opponent" character varying(24) encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"height_opponent" double precision encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"weight_opponent" double precision encode bytedict
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"favorite_technique_opponent" character varying(36) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"csa_dwh_file_path" character varying(200) encode zstd
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: ,"csa_dwh_created_at" timestamp without time zone encode az64
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: , PRIMARY KEY ("basho_id", "year", "player_id", "opponent_id")
[2020-12-20 xx:xx:xx,789] {base_task_runner.py:97} INFO - Subtask: )
[2020-12-20 xx:xx:xx,790] {base_task_runner.py:97} INFO - Subtask: DISTSTYLE even
[2020-12-20 xx:xx:xx,790] {base_task_runner.py:97} INFO - Subtask: compound SORTKEY ("year", "basho_id", "player_id")
[2020-12-20 xx:xx:xx,790] {base_task_runner.py:97} INFO - Subtask: ;
ログ取り込みモード同様、SQL上でもテーブル定義を確認してみます。
# \d csademo.sumo_match_results_dbmode;
Table "csademo.sumo_match_results_dbmode"
Column | Type | Collation | Nullable | Default
-----------------------------+-----------------------------+-----------+----------+---------
basho_id | smallint | | not null |
day | smallint | | |
name | character varying(15) | | |
link | character varying(34) | | |
rank | character varying(18) | | |
status | character varying(19) | | |
star | character varying(9) | | |
decide | character varying(21) | | |
opponent_name | character varying(15) | | |
opponent_link | character varying(34) | | |
opponent_rank | character varying(12) | | |
opponent_status | character varying(19) | | |
year | character varying(18) | | not null |
basho | character varying(24) | | |
player_id | smallint | | not null |
opponent_id | smallint | | not null |
latest_name | character varying(15) | | |
first_name_player | character varying(9) | | |
heya_player | character varying(12) | | |
real_name_player | character varying(102) | | |
name_changes_player | character varying(62) | | |
latest_rank | character varying(12) | | |
birthday_player | character varying(22) | | |
home_1_player | character varying(15) | | |
home_2_player | character varying(24) | | |
height_player | double precision | | |
weight_player | double precision | | |
favorite_technique_player | character varying(36) | | |
opponent_latest_name | character varying(15) | | |
first_name_opponent | character varying(9) | | |
heya_opponent | character varying(12) | | |
real_name_opponent | character varying(102) | | |
name_changes_opponent | character varying(62) | | |
opponent_latest_rank | character varying(12) | | |
birthday_opponent | character varying(22) | | |
home_1_opponent | character varying(15) | | |
home_2_opponent | character varying(24) | | |
height_opponent | double precision | | |
weight_opponent | double precision | | |
favorite_technique_opponent | character varying(36) | | |
csa_dwh_file_path | character varying(200) | | |
csa_dwh_created_at | timestamp without time zone | | |
Indexes:
"sumo_match_results_dbmode_tmp_for_ginga_pkey1" PRIMARY KEY, btree ("year", basho_id, player_id, opponent_id)
その他(SQLによるテーブル作成など)
CSA JMCでの画面操作によるテーブル作成を行うことで「作成時に用いたCREATE TABLE文」の情報を得る事が出来ます。なので、このCREATE TABLE文があればまぁ後はやりたいように出来るわけ訳ですね。
「DB取り込みモード」でのテーブル作成は「列毎の最大値」からデータ型における最大値を定義しています。なので「その値(最大値)だとちょっと不安なので、ちょっと余裕を持たせた定義にしたい」のであれば、以下のように別途CREATE TABLE文を用意し、Amazon Redshiftにアクセス可能なSQLクライアントツール等でこれをそのまま流しても良いでしょう。
CREATE TABLE IF NOT EXISTS "csademo"."sumo_match_results_custommode" (
"basho_id" smallint NOT NULL
,"day" smallint encode az64
,"name" character varying(50) encode zstd
,"link" character varying(100) encode zstd
,"rank" character varying(30) encode zstd
,"status" character varying(19) encode bytedict
,"star" character varying(9) encode zstd
,"decide" character varying(21) encode bytedict
,"opponent_name" character varying(15) encode bytedict
,"opponent_link" character varying(34) encode zstd
,"opponent_rank" character varying(12) encode zstd
,"opponent_status" character varying(19) encode bytedict
,"year" character varying(18) NOT NULL
,"basho" character varying(24) encode zstd
,"player_id" smallint NOT NULL
,"opponent_id" smallint encode bytedict NOT NULL
,"latest_name" character varying(15) encode zstd
,"first_name_player" character varying(9) encode zstd
,"heya_player" character varying(12) encode zstd
,"real_name_player" character varying(102) encode zstd
,"name_changes_player" character varying(62) encode zstd
,"latest_rank" character varying(12) encode zstd
,"birthday_player" character varying(22) encode zstd
,"home_1_player" character varying(15) encode zstd
,"home_2_player" character varying(24) encode zstd
,"height_player" double precision encode zstd
,"weight_player" double precision encode zstd
,"favorite_technique_player" character varying(36) encode zstd
,"opponent_latest_name" character varying(15) encode bytedict
,"first_name_opponent" character varying(9) encode bytedict
,"heya_opponent" character varying(12) encode bytedict
,"real_name_opponent" character varying(102) encode zstd
,"name_changes_opponent" character varying(62) encode zstd
,"opponent_latest_rank" character varying(12) encode bytedict
,"birthday_opponent" character varying(22) encode bytedict
,"home_1_opponent" character varying(15) encode bytedict
,"home_2_opponent" character varying(24) encode bytedict
,"height_opponent" double precision encode bytedict
,"weight_opponent" double precision encode bytedict
,"favorite_technique_opponent" character varying(36) encode zstd
,"csa_dwh_file_path" character varying(200) encode zstd
,"csa_dwh_created_at" timestamp without time zone encode az64
, PRIMARY KEY ("basho_id", "year", "player_id", "opponent_id")
)
DISTSTYLE even
compound SORTKEY ("year", "basho_id", "player_id")
;
また、CSA JMCでは任意のSQLを処理の「構成要素」として定義しておく機能も存在します。ファイルからのテーブル作成で選択可能な「カスタムモード」を選択すると、この「構成要素(SQL)」のメニューに遷移する形となります。機能としては「SQL処理をファイルとして用意し、システムとして認識させることで再利用可能な処理構成要素として利用出来る」ものなのですが、これについては後日エントリを改めて紹介したいと思います。
「作成したテーブルの定義を変えたい」という場合であれば、CSA JMCには「Amazon Redshiftテーブル定義の変更」を行う機能もあります。これについても、後日どこかのタイミングで紹介出来ればと思います。
まとめ
という訳で、『クラスメソッド CSアナリティクス Advent Calendar 2020』20本目のエントリ、「CSA JMC(Job Management Console)」でAmazon Redshift環境上にテーブルを作成する際の手順に関する内容の紹介でした。
CSA Data Uploaderは1ヶ月間のトライアル利用が可能となっています。興味をお持ち頂いた方は是非無料版ダウンロードページからインストーラを入手頂き、お試し頂けますと幸いです。また、CSA JMCに関しても下記バナーから製品ページにアクセスする事が出来ます。是非御覧ください。
では、明日(21日目)のエントリもお楽しみに！
