SQLと式でデータを変換する

約30分 · フィルター、結合、計算列

ワークフローの作業の大半は、小さな1つの操作の繰り返しです。テーブルを取り込み、整形し、結果を新しいテーブルに書き込みます。このチュートリアルでは、その操作（行の選択とフィルター、2つ目のテーブルの結合、計算列の追加）を、頻繁に使う2つのツール、SQLと列の式を使って学びます。このパターンを一度覚えれば、今後作成するほとんどすべての変換ステップに応用できます。

ここで大切なのは、関数を暗記することではなく、作業の流れと調べる場所を把握することです。PlaidCloudには1,000を超えるSQL関数がありますが、ここでは再掲せず、リファレンスへのリンクで案内します。

作成するもの

未加工のordersテーブルを読み取り、完了した注文だけを残し、customersテーブルと結合して各注文の地域を取得し、計算列net_amountを追加して、結果を新しいorders_cleanテーブルに書き込む、1つのテーブルステップです。

orders     ─┐
            ├─►  select · filter · join · calculated column  ─►  orders_clean
customers  ─┘

ソーステーブルはすべてそのまま残ります。変換ステップは1つ以上のソースから読み取り、ターゲットに書き込みます。元のテーブルは一切変更されないため、ワークフローを安全に再実行し、監査できます。

列を整形する2つの方法

手順に入る前に、押さえておきたい違いが1つあります。

ツール	内容	記述する場所
SQL	テーブル全体を選択、フィルター、結合するクエリ	ステップのSQLエディター
式	1つの列の値を行ごとに生成する単一の数式	列マッパーの式セル

どの行とどのテーブルを使うかはSQLで決めます。1つの列に何を入れるかは式で決めます。多くのステップでは両方を組み合わせられます。SQLでデータセットを整形し、式セルで列を計算します。

前提条件

PlaidCloudのワークスペースと、作業用のプロジェクト（お持ちでない場合は無料トライアルを開始してください）。
プロジェクト内に2つのテーブルがあること。このチュートリアルでは、orders（order_id、customer_id、status、amount、taxを含む）とcustomers（customer_id、regionを含む）を前提とします。お持ちでない場合は、データのロード、変換、公開のチュートリアルで、まずCSVからテーブルをインポートします。
ワークフローエクスプローラーで開いているワークフロー。プロジェクトのワークフロータブを開き、ワークフローをダブルクリックします。まだない場合は、ワークフローを作成するを参照してください。

ステップ1：SQLで選択とフィルターを行う

まずは絞り込みから始め、必要な列と行だけを取り出します。

テーブル変換ステップを追加し、SQLエディターを開きます。
ステップのソースにordersテーブルを指定します。

必要な列だけを残し、不要な行を除外するクエリを書きます。

SELECT
    order_id,
    customer_id,
    amount,
    tax
FROM orders
WHERE status = 'completed'
  AND amount > 0

このWHERE句がフィルターです。完了済みで金額がゼロでない注文以外は、ターゲットに届きません。SELECT *ではなく4つの列だけを選択することで、下流のテーブルが無駄なく保たれ、意図も明確になります。

読みやすいSQLを書きましょう。 1行に1つの句、1行に1つの列とし、CASE / WHENは揃えて記述します。詰め込んだ1行のクエリは避けてください。エディターの整形ボタン（またはShift+Alt+F、macOSではShift+Option+F）を使うと、動作を変えずにその場で整形できます。エディターでコードを整形するを参照してください。

ステップ2：2つ目のテーブルを結合する

結合は、共通のキーで対応付けて、2つ目のテーブルの列を1つ目のテーブルに並べる操作です。ここでは、customersにある各注文のregionが必要です。

customersを2つ目のソースとしてステップに追加します。

共通のキーで結合し、必要な列を取り出すようにクエリを拡張します。

SELECT
    o.order_id,
    o.customer_id,
    o.amount,
    o.tax,
    c.region
FROM orders        AS o
INNER JOIN customers AS c
    ON o.customer_id = c.customer_id
WHERE o.status = 'completed'
  AND o.amount > 0

INNER JOINは、対応する顧客がある注文だけを残します。対応する顧客がなくてもすべての注文を残し、regionをnullのままにしたい場合は、代わりにLEFT JOINを使います。結合キーの不一致や誤りは、結合結果の行数が多すぎる、または少なすぎる原因として最もよくあるものです。先に進む前に、プレビュー（次のステップ）で行数を確認してください。

クエリ内のテーブル名。 各ソースは、ステップが付けた名前で参照します。デフォルトのLakehouseウェアハウスでは、物理テーブルIDにハイフンが含まれることがあります。その場合は、エンジンが1つの識別子として読み取れるように、バッククォートで囲んでください（例：`analyzetable_9f3c...`）。

ステップ3：式で計算列を追加する

どちらのソースにもない値を計算します。計算列（式の列）は、行ごとに1つの数式を実行し、結果を新しい列に書き込みます。税引後の金額であるnet_amountを追加しましょう。

ステップの列マッパーで、net_amountという名前の新しいターゲット列を追加します。
その式セルに、次の数式を書きます。
```
amount - tax
```
条件が必要な場合も、式で通常のロジックを記述できます。たとえば、配送フラグは次のようになります。
```
(
    "free"
    if amount >= 100
    else "standard"
)
```

式セルは単一の数式であり、スクリプトではありません。 式セルは1つの素の式として評価されるため、複数行の式には外側の括弧が必要です（上の例のとおり）。括弧がないと、実行時に失敗します。各セルには1つの列の値だけを記述してください。行やテーブルをまたぐ処理にはSQLステップを使います。

ある関数が存在するかどうか、また引数が正確にどう動作するかは、プロジェクトが実行されているLakehouseの世代によって異なります。2つの世代は、それぞれ異なるアップストリームのエンジンに準拠しているためです。関数名を推測せず、調べてください。

Lakehouse	関数ライブラリ
v1	Databend SQL関数
v2	StarRocks 4.1 SQL関数

入り口となるのは式のリファレンスです。文字列操作、日付計算、条件ロジック、型変換、集計を扱い、各世代の標準的なアップストリーム構文へのリンクが付いています。最初から最後まで読むものではなく、必要なときに戻って調べる場所として活用してください。

整形は式セルにも使えます。 標準的なPythonのスペースと改行が適用されるため、長い条件式に便利です。

ステップ4：実行前にプレビューする

ステップが意図どおりに動作したかを確かめるために、ワークフロー全体を実行する必要はありません。データプレビュードロワーには、ステップの出力（型付きの列を持つ最初の100行）が、ワークフローエクスプローラー内にそのまま表示されます。

このステップだけを実行して、出力テーブルを生成します。（一度も実行していないうちは、プレビューに*「No data yet — run the step to generate this table.」*と表示されます。）
プレビューを開きます。ステップを選択し、出力データをプレビューを選びます（ステップの右クリックメニューにもあります）。
結果を確認します。選択した列がそろっていること、結合によってregionが取り込まれていること、net_amountが正しく計算されていること、行数がフィルターを反映していることを確かめます。

ドロワーを開いている間は、別のステップを選択すると、プレビューがそのステップの出力に切り替わります。ステップの連なりをたどり、各段階でデータの形がどう変わるかを確認できます。最初の100行を超えて確認したい場合や、スライスやフィルターを行いたい場合は、テーブルエクスプローラーで開くをクリックします。詳細は、ステップデータのプレビューを参照してください。

パターンの一般化

ここまでの操作は、すべて同じ基本形です。

1つ以上のソーステーブルを読み取り、整形して、新しいターゲットを書き込みます。 行とテーブルはSQLが決め、各列の値は式が決め、全体を実行する前にプレビューで結果を確認します。これをいくつか連ねればパイプラインになり、各ステップのターゲットが次のステップのソースになります。

後続のステップやダッシュボードがデータを実体として必要とする場合、中間結果はテーブルに書き込みます。読み取り時に計算してストレージを節約したい場合は、ビュー に書き込みます。ビューはそれ自体にデータを持たず、ソースが変わると自動的に更新されます。

独自のPython、外部パッケージ、カスタムロジックなど、変換がSQLと式だけでは収まらなくなった場合は、User Defined Transformステップを使います。これは例外的な手段であり、日常的な整形作業の大半はSQLと式でまかなえます。

次のステップ

式のリファレンス — 計算列で使えるすべての関数をLakehouseの世代別に掲載しています。
ワークフローステップのリファレンス — 変換、インポート、エクスポートの各ステップの一覧です。
ワークフローガイド — ステップを取り巻く条件、ループ、変数、エラー処理を扱います。
ステップデータのプレビュー — ドロワーの詳細です。
データのロード、変換、公開 — このステップが組み込まれる、エンドツーエンドのパイプラインです。