HOW TO TD(User Engagement)Treasure Data User Engagement

効果的なtimeカラムの使い方

ホーム » 効果的なtimeカラムの使い方

データマネジメントチームの日下部 佑起です。
本日はTreasure Data CDPのテーブルに必ず存在するtimeカラムについて書きます。

Treasure Data CDPにおいて、timeというカラムはすべてのテーブルに存在するカラムで、唯一インデックスが設定されているカラムです。また、timeカラムには常にUNIX時間という数値型で格納されています。また、テーブルを作成する際にtimeカラムを指定せずに作成をすると、作成した時間が自動的にtimeカラムに格納されます。

UNIX時間について

UNIX時間は、「1970年1月1日午前0時0分0秒(UTC)」からの経過秒数で表現する時間の表現方法です。
UTC(Coordinated Universal Time)は世界協定時刻のことです。以前はGMT(グリニッジ標準時)が利用されておりましたが、1960年代からより精度の高いUTCが使用されるようになりました。
以下の例は、UTCの時間とUNIX時間との比較です。

表1

UNIX時間のままではぱっと見た際に日付が分からないため、関数によってYYYY/MM/DDなどの書式でフォーマットを変えることが出来ます。
変更方法は次の章で説明をしますが、クエリーを書く時間もないという際はutimeというChrome拡張機能が便利です。右上のウィンドウにUNIX時間を貼り付けるだけで、日時を返してくれます。

timeの加工について

TD_TIME_FORMAT

Treasure Data CDPでは、クエリーエンジンとしてPrestoとHiveの2つをサポートしており、使用できる関数もPrestoとHiveそれぞれ使用可能ですが、それ以外にTreasure Data CDP独自の関数が設定されています。
その中でよく使用する関数について記載します。
TD_TIME_FORMATは、第1引数のunix_timestampを第2引数の指定のフォーマットで、第3引数のtimezoneに計算をして表示してくれる関数で恐らく一番良く使う関数です。
日付を表示する際のフォーマットは、年、月、日、時間、分、曜日などを指定することが出来るため、以下に一覧を記載します。MMやmmなど大文字と小文字で内容が変わってくるものもあるため注意が必要です。

表2

関数フォーマット

string TD_TIME_FORMAT(long unix_timestamp,
                      string format
                      [, string timezone = 'UTC'])

EX.
以下の例は、前章の表1にあるUNIX時間「1577836800」を`yyyy-MM-dd HH:mm:ss`のフォーマットで、日本時間で表示する内容です。
前章の表1ではUTC時間なので0時ですが、今回はJST指定で日本時間のためプラス9時間となり、時間が9時となっております。
また、月を表示する場合は大文字のM2つ(MM)で、分を表示する場合は小文字のm2つ(mm)を使用します。

SELECT
  TD_TIME_FORMAT(1577836800, 'yyyy-MM-dd HH:mm:ss', 'JST')
  →  2020-01-01 09:00:00

TD_TIME_FORMAT

yyyy/MM/dd形式の日付からUNIX時間への変換をする際に使用します。

使用タイミング

  • yyyy/MM/dd形式の日付をtimeに格納したい場合
  • TD_TIME_RANGEなどでUNIX時間が指定されている関数を使用する場合など

関数フォーマット

long TD_TIME_PARSE(string time
                   [, string default_timezone = 'UTC'])

EX.

SELECT
  TD_TIME_PARSE('2020-01-01 09:00:00', 'JST')
  →  1577836800

Workflowでのtimeについて

Workflowでは、クエリでは使用できない機能としてJavascriptの関数を使用した計算が可能です。
また、${変数名}のシンタックスで変数を利用する事が出来ます。 Treasure Data CDPに事前に用意されている変数は以下です。

表3

EX.
例えば、1日に1回処理するWorkflowを作成した際に、同じ日に2回処理されてしまうと困る場合があるので、2回実行された場合は同日に処理されたデータを削除して実行を行う方法があります。その場合、session_dateを使用しての以下のような記載が可能です。
最初のタスク(+delete)で、session_dateで絞ったデータ(当日実行したデータ)を削除し、その後のタスク(+insert)でtimeに処理をした日付を入れております。

+main_task:
  +delete:
    td>:
    query: |
      DELETE
      FROM
        summary_table
      WHERE
        time = ${moment(session_date).unix()}

  +insert:
    td>:
    query: |
      SELECT 
        ${moment(session_date).unix()} AS time
        , id
        , name
      FROM
        base_table
    insert_into: summary_table

今回はtimeについての基本的な部分について書きました。
次回は内容を変え、新しくサービス展開を行っているTreasure Insightsについて書く予定です。

日下部 佑起

Data Managementチーム

2010年にデータ分析専業のSIerに入社し、データ活用に特化したSEとして、BI(Business Intelligence)ベンダー選定を始め、要件定義からデータモデル・テーブルの設計およびシステム設計、開発、運用まで一貫した業務を約10年経験。製薬業界、食品業界、小売業界、医療業界、不動産業界等幅広いクライアントに対して、BI・ETLの導入・移行や、統合分析基盤の構築、経営ダッシュボード作成などの業務を担当。2020年よりトレジャーデータに参画。データマネジメントチームにて、データ基盤や施策のデータ集計など設計・開発・運用支援を行う。

得意領域 : Workflow、SQL、ETL、RDBMS、BI選定・構築、DWH構築

Back to top button