Treasure Data CDPのデータ型について

データ型とは

そもそもデータ型とは何でしょう？型システム入門 プログラミング言語と型の理論曰く、下記のような記載があります。

型システムとは、プログラムの各部分を、それが計算する値の種類に沿って分類することにより、プログラムがある種の振る舞いを起こさないことを保証する、計算量的に扱いやすい構文的手法である。

よくわからないという人は、データ型という制限を設けることで安全に処理を行うことができる、とイメージしていただくと良いでしょうか。

例えば実際のデータが文字なのに、数値として処理をしてしまうと意図しない結果となってしまうことは容易に想像できるかと思います。
そういった事態を防止するためにもデータ型という規則があり、利用者は必要に応じて適宜コントロールする必要があります。

Treasure Data CDP以外のDB製品も利用している方はテーブルの各カラムにデータ型を設定する必要があるためふんわりと意識したことがあるかと思います。

Treasure Data CDPを利用する際のデータ型の種類

スキーマレスであるTreasure Data CDPにおいても、実際にデータを取り扱う上でデータ型は意識する必要があります。 まずTreasure Data CDPにおけるデータ型は下記3種類あることをまずは理解しましょう。

1. データを格納しているTreasure Data CDPのデータ型(Plazma DB)
2. クエリエンジンがHiveのときのデータ型
3. クエリエンジンがPrestoのときのデータ型

Treasure Data CDPでは同じデータ(Plazma DB)に対して、HiveとPrestoのどちらを利用してもアクセスすることができます。
データ型の観点で考えると、上図にある通り例えばクエリエンジンとしてPrestoを利用する場合はHiveは関係ないため、Treasure Data CDPのデータ型とPrestoのデータ型を意識していただければと思います。

Treasure Data CDPのデータ型

Treasure Data CDPの場合、スキーマ・オン・リード方式でデータ型をメタデータ管理しているため、Treasure Data CDPコンソール(ブラウザUI)上で各カラムのデータ型をシームレスに設定・変更することができます。
文字列用のstring型に、数値用のint、long、fload、double型があると把握しておけばよいでしょう。

Treasure Data CDPコンソールでデータ型設定

Hiveのデータ型

OSSのHiveのデータ型はドキュメントに記載されています。
特別詳細把握する必要はないかと思いますが、後述するデータ型を意識する必要があるケースの際に参考にしていただければと思います。

Prestoのデータ型

OSSのPrestoのデータ型はドキュメントに記載されています。
こちらも必要に応じて意識していただければと思いますが、Prestoの場合TYPEOF関数で対象データのデータ型を確認することが可能です。

SELECT TYPEOF('test1') as data_type;

TYPEOF関数利用例

各データ型の関係

Treasure Data CDP、Hive、Prestoは全く別のコンポーネントになるため、最低限の互換性を意識する必要があります。
具体的には、Treasure Data CDPのドキュメントに記載されている対応表を見てみましょう(下記転記)。

数値や文字列のデータ型の場合、特に意識しないでも問題ないケースがほとんどかと思います。
一方、意識したほうが良いケースは例えば一番下の行で、Treasure Data CDPのデータ型には残念ながらtimestamp型がありません。

ですが、PrestoやHiveではtimestamp型が存在しており、timestamp型として取り扱わなければいけない・取り扱いたいケースが多々あります。
その場合、データ格納先はTreasure Data CDPになりますのでstring型としてデータを保持しておき、クエリで利用するときにデータ型を変換するという対応が必要です(詳細後述します)。

データ型を意識する必要があるケース

クエリで参照(SELECT文)するとき

実際にTreasure Data CDPに格納されているデータが文字列であろうが数字であろうが、ユーザーは任意のデータ型をカラムに設定することができます。
通常のDB製品であれば格納されているデータに対応したデータ型である必要があるため、これはTreasure Data CDPのメリットとしてよく挙げられます。

ただし、注意が必要なケースがあります。もし格納されているデータが文字列の場合にデータ型が数値(int型など)の場合は、格納されているデータと一致しないためSELECT結果はNULLになります。

実際に検証してみましょう。
まずは下記Prestoのクエリを実行します。作成された test_number テーブルの col1 カラムはstring型になり test1 という文字列が格納されます。
SELECT文で確認するともちろん test1 というデータを参照できます。

CREATE TABLE test_number
AS SELECT 'test1' AS col1
;

では次にTreasure Data CDPコンソールでcol1カラムのデータ型をstring型からint型に変更します。

データ型をint型へ変更

変更後下記クエリでデータを参照してみましょう。

SELECT col1
  FROM test_number
;

格納したはずのtest1という文字列が返らずにNULLが返っています。もちろんTreasure Data CDP側のデータ型をstring型に戻せばtest1が返ります。

間違ったデータ型を設定するとNULLが返る

データを格納するとき(クエリで)

例えばCREATE TABLE文を用いてデータを格納するケースを考えます。

下記Prestoクエリはtimestamp型として 2021-02-25 17:00:00 というデータをTreasure Data CDPに格納しようとしますが、先述した通りTreasure Data CDPにはtimestamp型は存在しません。

CREATE TABLE test_timestamp
AS SELECT timestamp '2021-02-25 17:00:00' AS col1
;

そのため実際に実行してみると下記メッセージが出力されて失敗してしまいます。
メッセージの意味は、カラムのタイプ(型)として timestamp はサポートされていないとなっていて想定通りですね。

Query 20210225_xxxxxx_xxxxx_xxxxx failed: Column type: timestamp not supported

回避策としては、下記のようにCAST関数を使ってtimestamp型からvarchar型に変換してあげることが挙げられます。

CREATE TABLE test_timestamp
AS SELECT CAST(timestamp '2021-02-25 17:00:00' AS VARCHAR) AS col1;

注意点として、col1カラムは単純な文字列のカラムと区別がつかないため、参照するときには明示的に逆方向にデータ型を変換する必要があります。

SELECT CAST(col1 AS TIMESTAMP) AS col1
  FROM test_timestamp;

関数を利用するとき

もともとのデータを全て必要な形で用意することは難しいので、クエリでデータを加工・集計して業務に必要なデータを抽出していると思います。
クエリ(Hive、Presto)にはDB製品などと同様に便利な関数がたくさん用意されていますが、関数を利用する上でデータ型に注意を払う必要があります。UPPER関数を例に説明します。

upper(string) → varchar

Converts string to uppercase.

注目するポイントは2点あります。

1. 引数(括弧の中)

stringと記載されているので、その名の通り引数は文字列(数値などではなく)のデータ型である必要があります。

2. 返り値(結果)

矢印の先が varchar となっているのでUPPER関数を適用した結果はvarchar型となります。

UPPER関数は引数で与えられた文字列を全て大文字で返す関数なので、インプットとなる引数は文字列のデータ型である制約を設けていると考えると良いかもしれません。

また、大文字にした結果を返すので、関数適用後のデータ型が文字列であるvarchar型であるのもイメージしやすいかと思います。
実際に動作確認してみましょう。正しくUPPER関数を利用する下記クエリは、結果としてABCを返します。

SELECT UPPER('abc') AS col1
;

ですが、下記のように数値を引数として実行してみると Unexpected parameters (integer) for function upper. Expected: upper(char(x)) , upper(varchar(x)) というエラーで失敗します。

SELECT UPPER(123) AS col1
;

このように関数を利用する際には引数と返り値のデータ型を考慮する必要があります。
エラーとならない場合は意識する必要はありませんが、先述したようなエラーが発生した際はデータ型を確認してみてください。

NULLを格納するとき

テーブルを作成する際の初期データとしてNULLを入れておきたいケースがあるかと思います。
NULL以外の場合と同じようなクエリを考えると下記のようになるかと思いますが、failed: line 1:1: Column type is unknown: col1というエラーで失敗してしまいます。

CREATE TABLE test_null
AS SELECT null AS col1
;

これはエラーメッセージ通りではあるのですが、NULLはデータ型が文字列なのか数値なのかもわからないことに起因しています。
そのため、将来同じカラム(上記例ではcol1)に入ってくるデータと同じデータ型に変換してエラー回避してください。下記クエリであればエラーになりません。

CREATE TABLE test_null
AS SELECT CAST(null AS VARCHAR) AS col1
;

最後に

Treasure Data CDPはHiveとPrestoという2種類のクエリエンジンを利用できるメリットがある一方で、Treasure Data CDP(Plazma DB)を含めた各コンポーネントでのデータ型を考える必要があります。
その際本記事が理解の助けとなると幸いです。