複数のタイムフォーマットがあるテーブルでそれぞれ適切にRedshiftへデータ取り込みやってみた #CSアナリティクス

みなさんこんにちは！クルトンです。

今回は複数のカラムで別々のTIMEFORMATが指定されている場合のデータ取り込みについてご紹介いたします。データの型を変えているし出来るのかなと思っていたのですが、エラーが出てしまったので動作検証してみた内容を記載します。

データ取り込みに関しては弊社サービスCSA: Job Management Console(以下ではCSA JMCと呼称)の画面を使ってのご紹介ですが、RedshiftでSQLを実行する環境(例えばDBeaverを使うなど)をご用意している場合でも参考になる内容かと思います。

結論

先に結論を書くと、Invalid timestamp format or value [YYYY/MM/DD HH:MI:SS]というエラーが出る場合、取り込み用のSQLクエリのTIMEFORMATに対して'auto'という指定をすればデータ取り込み可能です。

ではどのような場合に、エラーが出るのかについて以下では検証していますのでご興味おありの方、ぜひ続けてご覧ください。

事前準備

データ取り込み前に以下の3つを準備します。

• テーブル作成用のSQLクエリを実行してテーブルを作っておく
• データの準備をしてS3へアップロード
• データ取り込み用のSQLクエリを作成し所定のS3へアップロードしておく

テーブル作成

まずはデータを入れる簡単なテーブルを作成しておきます。Redshiftのご自身の環境で実行してみてください。

create table <スキーマ名>.<テーブル名> (
    time_stamp timestamp
    , date date
    , time time
)

自分はテーブル名はcroutons_multiple_timeformatで作ってみました。 time_stampという名前のtimestamp型のカラム、timeという名前のtime型のカラムがTIMEFORMATが複数ある状態を作っています。

データの準備

以下のようなデータを作ります。

CSVファイルとして保存し、S3へアップロードします。

JMCの日時実行をするので、dailyフォルダ以下にdaily/yyyy/mm/ddのように年月日でフォルダを作ります。

ご参考までに自分が作ったS3のファイルパスはdaily/2023/12/01/sample.csvとしました。(ファイル名をsample.csvで保存しました。)

データ取り込みSQLクエリを作成

データ取り込みに失敗するSQLクエリと成功するSQLクエリを作り、動作検証をしていきます。 作り終わったら、<バケット名>/sqlフォルダ配下に.sqlの拡張子で保存したファイルをアップロードしておきます。

まずは失敗するSQLクエリについてです。

COPY <スキーマ名>.<テーブル名>
FROM 's3://<バケット名>/daily/2023/12/01/sample.csv'
IAM_ROLE 'arn:aws:iam::<アカウントID>:role/<role_name>'
DELIMITER ','
IGNOREHEADER 1
DATEFORMAT 'YYYY/MM/DD'
TIMEFORMAT 'YYYY/MM/DD HH:MI:SS'
;

次に成功するSQLクエリについてです。TIMEFORMATの部分を書き換えているのみです。

COPY <スキーマ名>.<テーブル名>
FROM 's3://<バケット名>/daily/2023/12/01/sample.csv'
IAM_ROLE 'arn:aws:iam::<アカウントID>:role/<role_name>'
DELIMITER ','
IGNOREHEADER 1
DATEFORMAT 'YYYY/MM/DD'
TIMEFORMAT 'auto'
;

上記2つについてそれぞれ作り終わったらS3へアップロードを忘れずにしておきましょう！

CSA JMC使って実際に取り込んでみた

取り込みには以下の手順で実行します。

• ジョブ作成
• ジョブの実行
• データ取り込み時の動作検証

ジョブ作成

まずはJMCの画面においてジョブの追加というボタンをクリックします。

クリック後に出てくる画面でジョブ名をお好きな名前で設定し、画面右下にある追加ボタンをクリックします。クリック後に画面が遷移します。

遷移後の画面で、自身が入力したジョブ名が表示されている事をご確認ください。 自分は[ブログ用]croutons_multiple_timeformatという名前で作成しました。念のため、説明欄に何をするジョブかも書いていますがここはお好みで大丈夫です。

ページ下部にある構成要素と書かれている箇所で、編集ボタンをクリックします。

データ取り込みするためのSQLを登録する画面が登場しますので、S3へあらかじめアップロードしておいたSQLファイルを選択します。自分は片方ずつ実行したかったので、SQLのジョブへの登録からジョブ実行までをそれぞれで行ないました。 (片方実行後に、選択しているSQLファイルをジョブから削除して残りもう一つを登録からジョブ実行までしました。)

ジョブの実行

ジョブを実行する方法についてご紹介します。ジョブ一覧画面でまずは作成したジョブを見つけます。(検索機能でジョブを絞り込めます。)

「リンク」の中にある矢印のボタンをクリックしてください。

クリック後、表示される画面でジョブ実行時の設定をします。データをdailyフォルダ以下にアップロードしていますので、その日時のデータを使うよう設定し、画面右下にある確認ボタンから、ジョブについて確認してOKなら実行してください。

データ取り込み時の動作検証

まずは失敗するSQLクエリの方の結果です。ジョブが失敗していますね。

ジョブ実行の結果が書かれている行の右端にあるログ確認画面へ遷移するボタンをクリックし、エラー内容を確認しました。

以下のようなエラー内容が書かれていました。

【ERROR】
COPYエラーが発生します。
ファイル： s3://<バケット名>/daily/2023/12/01/sample.csv
行番号：2
カラム名： time
カラム値： 10:00:00
エラー内容: Invalid timestamp format or value [YYYY/MM/DD HH:MI:SS]
エラーコード： 1206

どうやらデータ取り込みが失敗する原因は、TIMEFORMATそのままのデータでないと取り込めないからのようです。time型ですと時間のみを取り扱うのですが、その場合でもTIMEFORMATに従うデータでないといけないようです。

つまり時刻のみのデータに対してtime型のカラムへデータ取り込みをしようとした場合であっても、TIMEFORMATの指定に沿わない場合はデータ取り込みに失敗するようです。

次に成功するSQLクエリの動作検証です。データ取り込みを実行したところ、成功していますね。

実際にデータが入っているか以下のSELECT文を実行してみました。

select * from <スキーマ名>.croutons_multiple_timeformat;

実行結果は以下のような出力がされました。

2023-12-06 10:00:00.000 2023-12-06  10:00:00
2023-12-06 10:00:10.000 2023-12-06  10:00:10
2023-12-06 10:00:20.000 2023-12-06  10:00:20

データ取り込みができているようですね！ ただし日付データがスラッシュ区切りでなくなっているので、スラッシュ区切りをしたい場合はさらに変換が必要そうです。

終わりに

同一テーブル内に複数のTIMEFROMATが設定されている場合に、どうやってデータを取り込むのかについて動作検証してみました。

データ取り込みさえできたら、あとはデータの形を変換するSQLを実行するなどすれば望みのデータの形で格納出来そうです。

本日はここまで。ご参考になったならば幸いです。

それでは、また！