dbtを使用したOceanBaseデータの分析

dbt（data build tool）は、SQLを通じてデータ変換を実現し、コマンドをテーブルやビューに変換できるオープンソースのデータ変換ツールです。本記事では、dbt-oceanbaseを使用して、dbtを通じてOceanBaseデータベース内のデータを分析する方法について説明します。

前提条件

• dbtを使用する前に、dbt-oceanbaseがインストールされていることを確認してください。インストールしていない場合は、以下のステップ2：dbt-oceanbaseのインストールを参照してください。

• OceanBaseデータベースがインストール済みで、MySQLモードのテナントが作成されていること。

手順

ステップ1：データベース接続文字列を取得する

OceanBaseデータベースのデプロイ担当者または管理者から、該当するデータベース接続文字列を取得します。例：

obclient -h$host -P$port -u$user_name -p$password -D$database_name

パラメータ説明：

• $host：OceanBaseデータベースへの接続IPアドレス。OceanBaseデータベースプロキシ(OceanBase Database Proxy、ODP)接続方式ではODPアドレスを使用し、直接接続方式ではOBServerノードのIPアドレスを使用します。

• $port：OceanBaseデータベースへの接続ポート。ODP接続方式のデフォルトポートは2883で、ODPデプロイ時にカスタマイズ可能です。直接接続方式のデフォルトポートは2881で、OceanBaseデータベースのデプロイ時にカスタマイズ可能です。

• $database_name：アクセス対象のデータベース名。

  注意

  テナントに接続するユーザーには、データベースに対する CREATE、INSERT、DROP、および SELECT 権限が付与されていなければなりません。その他のユーザー権限の詳細については、MySQLモードの権限分類を参照してください。

• $user_name：テナントの接続アカウント。ODP接続の一般的な形式：ユーザー名@テナント名#クラスタ名またはクラスタ名:テナント名:ユーザー名。直接接続方式の形式：ユーザー名@テナント名。

• $password：アカウントのパスワード。

その他の接続文字列の情報については、OBClientを使用したOceanBaseテナントへの接続を参照してください。

例：

obclient -hxxx.xxx.xxx.xxx -P2881 -utest_user001@mysql001 -p****** -Dtest

ステップ2：dbt-oceanbaseをインストールする

dbt-oceanbaseは現在PyPIには公開されていないため、リポジトリからインストールする必要があります。リリース後はpipを使用してインストールできます。以下のコマンドを使用して、dbt-oceanbaseをインストールします：

git clone https://github.com/oceanbase/dbt-oceanbase.git --branch dev/1.0.x
cd dbt-oceanbase
pip3 install .

以下のコマンドを使用して、インストール済みのdbtバージョンを確認します：

pip list | grep dbt

期待される実行結果：

dbt                       1.0.0.38.15
dbt-adapters              1.7.0
dbt-common                1.10.0
dbt-core                  1.9.0b2
dbt-extractor             0.5.1
dbt-oceanbase             1.0.0
dbt-semantic-interfaces   0.7.3

プロジェクトの設定

1. 現在のディレクトリで、以下のコマンドを使用して新しいプロジェクトを初期化します：

   dbt init dbtexample
   

2. コマンドラインの指示に従って、OceanBaseデータベースの接続情報を順次入力します。

   ステップ1の期待される実行結果：

   06:00:31  Running with dbt=1.8.3
   06:00:31  Your new dbt project "dbtexample" was created!
   
   For more information on how to configure the profiles.yml file,
   please consult the dbt documentation here:
   
   https://docs.getdbt.com/docs/configure-your-profile
   
   One more thing:
   
   Need help? Don't hesitate to reach out to us via GitHub issues or on Slack:
   
   https://community.getdbt.com/
   
   Happy modeling!
   
   06:00:31  Setting up your profile.
   Which database would you like to use?
   [1] oceanbase_mysql
   
   (Don't see the one you want? https://docs.getdbt.com/docs/available-adapters)
   
   Enter a number: 1
   host (hostname for the instance): xx.xxx.xxx.xx
   port: 2503
   user (username@tenant#cluster): username@tenant#cluster
   pass (password):
   database (default database that dbt will build objects in): test
   threads (1 or more) [1]:
   06:02:20  Profile dbtexample written to /Users/username/.dbt/profiles.yml using target's profile_template.yml and your supplied values. Run 'dbt debug' to validate the connection.
   

3. 全ての入力が完了したら、以下のコマンドを使用してdbtの接続状況を確認します：

   dbt debug
   

   期待される実行結果：

   06:02:35 dbt version: 1.8.3
   06:02:35 python version: 3.11.5
   06:02:35 python path: /Users/username/miniconda3/bin/python
   06:02:35 os info: macOS-14.2.1-arm64-arm-64bit
   06:02:35 Using profiles dir at /Users/username/.dbt
   06:02:35 Using profiles.yml file at /Users/username/.dbt/profiles.yml
   06:02:35 Using dbt_project.yml file at /Users/username/projects/dbtexample/dbt_project.yml
   06:02:35 adapter type: oceanbase_mysql
   06:02:35 adapter version: 1.0.0
   06:02:35 Configuration:
   06:02:35   profiles.yml file [OK found and valid]
   06:02:35   dbt_project.yml file [OK found and valid]
   06:02:35 Required dependencies:
   06:02:35  - git [OK found]
   
   06:02:35 Connection:
   06:02:35   host: xx.xxx.xxx.xx
   06:02:35   port: 2503
   06:02:35   user: username
   06:02:35   retries: 1
   06:02:35   schema: test
   06:02:35   database: test
   06:02:35 Registered adapter: oceanbase_mysql=1.0.0
   06:02:36  Connection test: [OK connection ok]
   
   06:02:36 All checks passed!
   

テストデータの準備

1. 以下のコマンドを使用して、dbt公式サイトからサンプルデータをダウンロードします。

   wget https://dbt-tutorial-public.s3-us-west-2.amazonaws.com/jaffle_shop_customers.csv
   wget https://dbt-tutorial-public.s3-us-west-2.amazonaws.com/jaffle_shop_orders.csv
   wget https://dbt-tutorial-public.s3-us-west-2.amazonaws.com/stripe_payments.csv
   

2. データをdbtexample/seeds/ディレクトリに配置します。以下のコマンドを使用して、データをインポートします。

   dbt seed
   

3. （オプション）以下のコマンドを使用して、不要なmodels/exampleを削除します。

   # optional
   rm -rf  models/example/
   

4. modelsディレクトリ内にcustomers.sqlを新規作成し、以下の内容を書き込みます。

   {{
     config(
       materialized='view'
     )
   }}
   
   with customers as (
   
       select
           id as customer_id,
           first_name,
           last_name
   
       from jaffle_shop_customers
   
   ),
   
   orders as (
   
       select
           id as order_id,
           user_id as customer_id,
           order_date,
           status
   
       from jaffle_shop_orders
   
   ),
   
   customer_orders as (
   
       select
           customer_id,
   
           min(order_date) as first_order_date,
           max(order_date) as most_recent_order_date,
           count(order_id) as number_of_orders
   
       from orders
   
       group by 1
   
   ),
   
   final as (
   
       select
           customers.customer_id,
           customers.first_name,
           customers.last_name,
           customer_orders.first_order_date,
           customer_orders.most_recent_order_date,
           coalesce(customer_orders.number_of_orders, 0) as number_of_orders
   
       from customers
   
       left join customer_orders using (customer_id)
   
   )
   
   select * from final
   

5. 以下のコマンドを使用して、models配下のファイルを自動的に検出し実行します。

   dbt run
   

   期待される実行結果は次のとおりです。

   06:37:08  Running with dbt=1.8.3
   06:37:08  Registered adapter: oceanbase_mysql=1.0.0
   06:37:08  [WARNING]: Configuration paths exist in your dbt_project.yml file which do not apply to any resources.
   There are 1 unused configuration paths:
   - models.dbtexample.example
   06:37:08  Found 1 model, 393 macros
   06:37:08
   06:37:10  Concurrency: 1 threads (target='dev')
   06:37:10
   06:37:10  1 of 1 START sql view model customers ........................................ [RUN]
   06:37:11  1 of 1 OK created sql view model customers .............................. [SUCCESS-1 in 1.25s]
   06:37:12
   06:37:12  Finished running 1 view model in 0 hours 0 minutes and 3.79 seconds (3.79s).
   06:37:12
   06:37:12  Completed successfully
   06:37:12
   06:37:12  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1
   

上記のコマンドが正常に実行され、ビューが作成されたことが確認できます。OceanBaseデータベースで確認することができます。