ThoughtSpotのインメモリDBにCSVでデータを投入する

こんにちは、小澤です。

ThoughtSpotでデータを利用する仕組みには外部のDBと接続してデータを取り出すEmbraceと、 内部のインメモリDBにデータを保持するFalconが存在します。

今回はFalconを利用して検索を行えるようにするためにCSVファイルのデータを投入する仕組みを紹介します。

FalconとTQL

Falconを利用するには、あらかじめ内部にデータを格納しておく必要があります。 内部のインメモリDBはRDBと同じような データベース > スキーマ > テーブル の階層でデータを保持できる仕組みになっています。

また、テーブルは主キーや外部キーの設定なども可能となっており、それらが設定されているものに関しては、ThoughtSpot内でそのまま利用可能です。

これらのテーブル定義などはTQLという言語を使って実行可能です。 その使い方はほぼ、SQLと同等です。

テーブル定義を行う

データを投入するに際して、まずはテーブル定義を行います。 TQLを実行可能な環境として、ThoughtSpotが動いている環境にsshでログインします

ssh <ユーザ>@<ThoughtSpotのインスタンス>

ログインして tql コマンドを実行すると、以下のようにTQLのコンソールが起動します。

$ tql

Welcome to ThoughtSpot SQL command line interface.
Press Control-C to clear current command.
Press Control-D or enter exit; to exit.
Enter help or h; or help; for available commands.

Connected to service
(Load) address cluster: classmethod-nonprod-1 zoo servers:  10.100.0.237:2181
(Query) address cluster: classmethod-nonprod-1 zoo servers:  10.100.0.237:2181
(Callosum) address cluster: classmethod-nonprod-1 zoo servers:  10.100.0.237:2181
TQL [database=(none)]>

このコンソールからTQLが実行可能です。 データベースやスキーマ、テーブルの一覧や利用するデータベースの選択はMySQLライクなコマンドで実行できます。

TQL [database=(none)]> show databases;
thoughtspot_internal_stats
e0f482a0-b29d-4032-91a6-db72b11eae2c
FalconUserDataDataBase
2fb91f36-a677-4a52-9077-e27e23e8d090
06ccb8f0-9fc3-4b84-8229-b8cd79edf985
98d6bf30-0817-4ed8-bc75-8200c36baded
retail_japan
Statement executed successfully.
TQL [database=(none)]> use retail_japan;
Changing database context to retail_japan
Statement executed successfully.
TQL [database=retail_japan]> show schemas;
Schema Name          |Guid

---------------------|------------------------------------
falcon_default_schema|0c0e65de-e2c6-438e-a248-a21d15672f43
Statement executed successfully.
TQL [database=retail_japan]> show tables;
Schema Name          |Table Name        |Guid                                |Type
---------------------|------------------|------------------------------------|----------------
falcon_default_schema|Date_Dimension    |902a4c8a-45be-4974-acff-5b97be06384c|UNKNOWN
falcon_default_schema|Product_Dimension |6648296c-a173-409d-9245-77dcc3b0397e|UNKNOWN
falcon_default_schema|Store_Dimension   |d6f330a7-584c-4724-810a-9defa8b56f8e|UNKNOWN
falcon_default_schema|Customer_Dimension|90c06738-5f72-4944-b342-b0a85a886d50|UNKNOWN
falcon_default_schema|Employee_Dimension|282ab9e0-84df-462f-aa5d-c007525c4f4e|UNKNOWN
falcon_default_schema|Retail_Sales_Fact |ce0fbbfe-4224-4f1f-a0c3-b0259b728a43|UNKNOWN
Statement executed successfully.
TQL [database=retail_japan]>

テーブルを作成するには、 create table 文を利用します。

TQL [database=retail_japan]> CREATE TABLE "Date_Dimension" ( 
  "Date_Key" int,
  "Date" date,
  "Full_Date_Description" varchar(255),
...
...
  "Selling_Season" varchar(255),
 PRIMARY KEY ("Date_Key" )
);

こちらも、SQLのような構文であることが確認できるかと思います。 通常は、DWHのようにスタースキーマの形式でFactテーブルとDimensionテーブルを作成することになるかと思います。 Factテーブルには、外部キーを使ってリレーションを定義しておきましょう。

constraint FK_DATE  FOREIGN KEY ("Date_Key" ) REFERENCES "Date_Dimension" ("Date_Key")

詳細な構文は、以下のドキュメントをご参照ください。

create table が正常終了したのちは、ThoughtSpotのUIからもテーブルが作成されていることが確認できます。

tsloadコマンドでCSVファイルからデータを投入する

この状態ではまだ、テーブル定義がされただけでデータが入っていません。 テーブルにデータを投入する方法として、tsload コマンドを使ってバルクインサートしてみましょう。

tsload コマンドでは、投入先のテーブルを指定して、CSVファイルのデータを投入します。 最低限必要なオプションは以下のようになります。

tsload --target_database <データベース名> \
    --target_table <テーブル名> \
    --source_file <CSVファイル>

ファイルの内容にあわせて、これに区切り文字の指定やdateやtimestamp型のデータを投入する際のフォーマットなどの指定可能です。

以下ではタブ区切り、日付は「-」付き、空文字がnull値であることの指定をしています。

tsload --target_database retail_japan \
    --target_table Date_Dimension \
    --source_file=Date_Dimension.csv --v 1 \
    --field_separator="\t" \
    --date_format="%Y-%m-%d" \
    --null_value=""

これでデータが投入され、UI上からも確認可能になります。

tsload コマンドのオプションに関する詳細はドキュメントをご確認ください。

ワークシートの作成

最後にデータを投入したテーブルを利用して、ワークシートを作成します。 この操作は必須ではありませんが、検索から利用しやすくするために作成しておきます。

右上の「・・・」 > Create worksheetを選択すると、ワークシートの作成画面に遷移するので、左メニューからデータソースを追加します。

データを投入したテーブルをデータソースとして選択します。

テーブル間のリレーションはテーブル作成時に定義したものが利用可能です。 特殊な状況になっていない限りは結合に関する設定はそのままで問題ありません。

ソースの選択画面を閉じるとこれらが表示された状態になるので、 ワークシートとして利用する列を選択しつつ、日本語の名称に変更するなどを行います。

また、既存の列をそのまま利用するのではなく、何らかの計算を行った結果を列として持たせたい場合はFormulaを利用して新たな列を作ることも可能です。

適切な名前を入れて、保存すればワークシートが作成されます。

作成されたワークシートは以下のようにテーブルなどと同様に列に関する設定が可能ですので、 必要に応じてAggrigateionの設定や地理属性を追加などが利用可能です。

あとは、通常どおりこのワークシートを使ったデータの検索がそのまま利用可能です。

おわりに

今回はThougthSpotでTQLとtsloadコマンドを使って、FalconのインメモリDBにデータを投入して利用するまでの一連の流れを解説しました。

コマンドからの実行となるため、データの追加や入れ替えなどの処理が定期的に必要な際には自動化しやすくなっているのではないでしょうか。