こんにちは！インサイトテクノロジーの松尾です。

近年、クラウドや SaaS の普及により、企業のデータは RDB だけでなく、REST API 経由で取得するケースが増えています。しかし、API のレスポンスは JSON 形式 であることが多く、そのままでは SQL で扱いにくいという課題があります。

本記事では、Denodo の Flatten Operation (フラット化操作) を活用し、Slack API の投稿データをリレーショナルテーブルとして扱う方法 を解説します。

Denodo は、異種データソースを統合し、SQL で統一的に扱えるデータ仮想化プラットフォームです。その中でも Flatten Operation は、REST API から取得した ネスト構造の JSON をフラット化 し、SQL で直接クエリできるようにする強力な機能です。

Denodo とは？

Denodo は データ仮想化（Data Virtualization） プラットフォームです。以下の特長により、異種データソースを仮想的に統合し、一元管理を可能にします。

リアルタイムでデータにアクセスし、統合を最適化
ETL 不要で仮想ビューを作成し、データを即時活用
REST API、RDB、CSV などの異種データを SQL で統一的に扱える

特に Flatten Operation (フラット化操作) は、API から取得した ネスト構造の JSON をリレーショナルデータとして扱えるよう変換 し、SQL で直接クエリ可能にする Denodo の強力な機能の一つです。

本記事のゴール

本記事では、次の流れで Slack API の投稿データを Denodo に統合し、SQL でクエリ可能にする 方法を解説します。

Slack API から投稿データを取得
Denodo の JSON データソースとして登録
Flatten Operation を適用し、リレーショナルデータとして扱える形式に変換
SQL でメッセージを取得・分析

この方法を応用すれば、Salesforce、Google Analytics などさまざまな API データも統合可能 になります。

では、さっそく進めていきましょう。

Slack 側の設定：Slack API 利用の準備

Slack API のトークンを取得

以下の手順で Slack API を使用するための設定を行います。

Slack API: Your Apps にアクセス

「Create New App」から新しいアプリを作成

「From scratch」を選択

「App Name」と Slack workspace を選択して「Create App」を選択

「OAuth & Permissions」を選択

以下のスコープを追加
- channels:history
- channels:read
- users:read

「Install to XXXXX」を選択 (XXXXX はワークスペース名)

「許可する」を選択

インストールされると、Bot User OAuth Token xoxb-xxxxxxxxxxxxxxxxxxx が表示されます。これを使って Slack API へアクセスすることになります。

Slack 側でチャンネルに参加させる

アクセスできるようにするためには、チャンネルに参加させておく必要があります。追加したアプリ名をメンションして参加させましょう。

これで Slack API を使用する準備が整いました。

Slack API へのアクセスの確認

curl からSlack API を使って実際にメッセージを取得してみます。
事前に対象のチャンネルの Channel ID を取得しておいてください。

また、対象のチャンネルには、以下のような投稿が存在している状態です。

メッセージの取得は以下のように実行できます。CXXXXX と `xoxb-XXXXX は、それぞれ、自身の環境の Channel ID と Bot User OAuth Token に置き換えてください。

curl -s "https://slack.com/api/conversations.history?channel=CXXXXX" \
    -H "Authorization: Bearer xoxb-XXXXX" \
    -H 'Content-Type: application/json; charset=utf-8' \
    | jq

実行結果は以下のような json データが返ってきます。

{
  "ok": true,
  "messages": [
    {
      "subtype": "channel_join",
      "user": "XXXXX",
      "text": "<@XXXXX>さんがチャンネルに参加しました",
      "inviter": "XXXXX",
      "type": "message",
      "ts": "1738898278.551779"
    },
    {
      "user": "XXXXX",
      "type": "message",
      "ts": "1738835026.540309",
      "client_msg_id": "XXXXX",
      "text": "また会いましたね",
      "team": "XXXXX",
      "blocks": [
        {
          "type": "rich_text",
          "block_id": "XXXXX",
          "elements": [
            {
              "type": "rich_text_section",
              "elements": [
                {
                  "type": "text",
                  "text": "また会いましたね"
                }
              ]
            }
          ]
        }
      ]
    },
    {
      "user": "XXXXX",
      "type": "message",
      "ts": "1738834992.009729",
      "client_msg_id": "XXXXX",
      "text": "またあした",
      "team": "XXXXX",
      "blocks": [
        {
          "type": "rich_text",
          "block_id": "XXXXX",
          "elements": [
            {
              "type": "rich_text_section",
              "elements": [
                {
                  "type": "text",
                  "text": "またあした"
                }
              ]
            }
          ]
        }
      ]
    },
:
:

また、thread_ts に値が入っているときは、スレッドのメッセージを取得できます。以下のように、thread_ts をパラメータで指定します。

curl -s "https://slack.com/api/conversations.replies?channel=CXXXXX&ts=1738834979.160759" \
    -H "Authorization: Bearer xoxb-XXXXX" \
    -H 'Content-Type: application/json; charset=utf-8' \
    | jq

実行結果は以下のような json データが返ってきます。

{
  "ok": true,
  "messages": [
    {
      "user": "XXXXX",
      "type": "message",
      "ts": "1738834979.160759",
      "client_msg_id": "XXXXX",
      "text": "こんにちは",
      "team": "XXXXX",
      "thread_ts": "1738834979.160759",
      "reply_count": 3,
      "reply_users_count": 1,
      "latest_reply": "1738835014.263699",
      "reply_users": [
        "XXXXX"
      ],
      "is_locked": false,
      "subscribed": false,
      "blocks": [
        {
          "type": "rich_text",
          "block_id": "XXXXX",
          "elements": [
            {
              "type": "rich_text_section",
              "elements": [
                {
                  "type": "text",
                  "text": "こんにちは"
                }
              ]
            }
          ]
        }
      ]
    },
    {
      "user": "XXXXX",
      "type": "message",
      "ts": "1738835001.006929",
      "client_msg_id": "XXXXX",
      "text": "いい天気ですね",
      "team": "XXXXX",
      "thread_ts": "1738834979.160759",
      "parent_user_id": "XXXXX",
      "blocks": [
        {
          "type": "rich_text",
          "block_id": "XXXXX",
          "elements": [
            {
              "type": "rich_text_section",
              "elements": [
                {
                  "type": "text",
                  "text": "いい天気ですね"
                }
              ]
            }
          ]
        }
      ]
    },
:
:

また、ユーザー情報は以下のようなリクエストで取得できます。

curl -s "https://slack.com/api/users.list" \
    -H "Authorization: Bearer xoxb-XXXXX" \
    -H 'Content-Type: application/json; charset=utf-8' \
    | jq

実行結果は以下のような json データが返ってきます。

{
:
    {
      "id": "XXXXX",
      "team_id": "XXXXX",
      "name": "tmatsuo",
      "deleted": false,
      "color": "235e5b",
      "real_name": "tmatsuo",
      "tz": "Asia/Tokyo",
      "tz_label": "Japan Standard Time",
      "tz_offset": 32400,
      "profile": {
        "title": "",
        "phone": "",
        "skype": "",
        "real_name": "tmatsuo",
        "real_name_normalized": "tmatsuo",
        "display_name": "",
        "display_name_normalized": "",
        "fields": null,
        "status_text": "",
        "status_emoji": "",
        "status_emoji_display_info": [],
        "status_expiration": 0,
        "avatar_hash": "XXXXX",
        "first_name": "tmatsuo",
        "last_name": "",
        "image_24": "XXXXX",
        "image_32": "XXXXX",
        "image_48": "XXXXX",
        "image_72": "XXXXX",
        "image_192": "XXXXX",
        "image_512": "XXXXX",
        "status_text_canonical": "",
        "team": "XXXXX"
      },
      "is_admin": true,
      "is_owner": true,
      "is_primary_owner": true,
      "is_restricted": false,
      "is_ultra_restricted": false,
      "is_bot": false,
      "is_app_user": false,
      "updated": 1731983081,
      "is_email_confirmed": true,
      "who_can_share_contact_card": "EVERYONE"
    },
:
:

Denodo で REST API(JSON) データソースを設定

続いて Denodo 側でデータソースを登録します。

Design Studio を起動
「新規 - データソース」を実行
「JSON (HTTP / OpenAPI)」を選択

「GET」リクエストの URL を設定
- ベース URL: https://slack.com/api/
- HTTP ヘッダー:
  - Authorization: Bearer xoxb-XXXXX
  - Content-Type: application/json; charset=utf-8

「コネクションをテスト」を選択して接続できることを確認
「保存」を選択
「基本ビューを作成」を選択

相対 URL に API の情報を設定
- 相対 URL: conversations.history?channel=CXXXXX
「コネクションをテスト」を選択して接続できることを確認
「OK」を選択

「基本ビューを作成」を選択
「編集」を選択し、ビュー名を変更し、「保存」を選択

「サマリ」を選択

「クエリ」を選択し、「実行」を選択

message フィールドの「(i)」マークを選択

メッセージが取得できていることを確認

フラット化操作( Flatten Operation )によるテーブル化

作成した基本ビューを "テーブル化" するために "フラット化" したビューを作成します。

「新規 - フラット化」

全要素をフラット化するために、ビュー名の横のマークを選択

「出力」でビュー名を設定して「保存」を選択

「クエリ」を選択して「実行」

メッセージリストを取得できるようになりました。

同様にスレッドとユーザー情報を取得できるようビューを作成

続いて、同様にスレッドメッセージも取得します。
基本ビューを作成する際に thread_ts をパラメータとして使用するようにしましょう。他の手順はメッセージ情報の取得と同じです。

さらにその基本ビューをフラット化すると、以下のように実行できます。クエリを実行する際には、thread_ts を指定する必要があります。

指定して実行すると以下のように結果が返ってきます。

さらに、ユーザー情報も同様に基本ビューを定義してフラット化します。

ここまでで以下の情報を取得するフラット化されたビューを定義しました。

メッセージの一覧(iv_slack_messages_flatten)
thread_ts に紐づくスレッドの一覧(iv_slack_threads_flatten_by_thread_ts)
ユーザーの一覧(iv_slack_users_flatten)

SQL でメッセージを取得

さて、このようにテーブル形式でデータが取れるようになると、簡単に JOIN を伴うクエリを実行することもできるようになります。VQL シェルから以下の VQL (Denodo 拡張の SQL) を実行してみましょう。

SELECT msg.ts,
       msg.user_0,
       msg.text,
       thread.ts,
       thread.user_0,
       thread.text
FROM iv_slack_messages_flatten msg
     LEFT OUTER JOIN iv_slack_threads_flatten_by_thread_ts thread
         ON msg.thread_ts = thread.thread_ts;

この VQL は、メッセージの一覧とスレッドがある場合は、その一覧も併せて出力する VQL です。

さらにユーザー名も表示できるよう、ユーザー情報も結合します。頑張って VQL で書いてもいいと思いますが、中間のビューを定義するほうが簡単ですね。例えば、メッセージ一覧、スレッド一覧、それぞれに対してユーザー情報を INNER JOIN で結合したビューを定義します。

ts (タイムスタンプ) の値は例えば以下のような関数定義の出力を追加すれば日時の情報に変換できます。

ADDHOUR(
  ADDSECOND(
    TO_TIMESTAMP ('yyyyMMddHHmmss', '19700101000000'),
    CAST(iv_slack_messages_flatten.ts AS INTEGER)
  ),
  9
)

そして、それらを結合してみると、以下のような出力を得ることができました！

この形式でとれるようになっていると、API を毎回たたくよりは断然便利ですよね！

パフォーマンスの考慮

Denodo では基本的には Denodo Platform にデータを持っていないため、このビューへアクセスするたびにSlack API へアクセスして、そこからデータを取得する形になります。頻繁にアクセスする場合にはキャッシュの利用を検討することになるでしょう。

Web API ソースデータの Pagination

本投稿で最初に Slack API を登録した際には、Pagination を考慮していませんでした。通常 API は結果をすべて返さずに細切れにして(ページ単位)で結果を返すことが多いと思います。そのような API に対して、Pagination を考慮したアクセスを設定することができます。例えば、以下のように設定すると、Slack API の Pagination を考慮したアクセスを行うことができます。

Denodo ではいくつかのパターンの Pagination 処理をサポートしています。詳細は以下を参考にしてください。

Slack API での Pagination の仕様は以下を参考にしてください。

おわりに

本記事では、Denodo の Flatten Operation を活用し、Slack API の投稿データをリレーショナルテーブルとして扱う方法について説明しました。Slack API のような REST API をデータソースとして利用できることは、多様なサービスをデータソースとして活用できることを意味します。さらに、Denodo を介することで、それらをテーブルデータとして統一的に扱えるため、データ活用の幅が一層広がりますね！

もう少し詳しく知りたい方へ

弊社 インサイトテクノロジー は、Denodo 社の公式パートナーとして、Denodo Platform の紹介、販売、導入支援を行っています。ご興味のある方は、以下のリンクから弊社または Denodo 社へお問い合わせください。

また、

「これからデータ統合基盤を整備したい」
「どのようにデータを統合すればよいか迷っている」
「現在進行中のプロジェクトで特定の課題がある（例: Snowflake に SAP からリアルタイムデータを取り込みたい）」

といったお悩みや具体的なご相談も、ぜひお気軽にお問い合わせください。