2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@ari_sora
in

【初心者向け】Databricksハンズオン#1：Excel取込から可視化・Deltaテーブル作成まで

2
Posted at

はじめに

どうも、そらです。
最近Databricksに触り始めたので、備忘録として私が実施したハンズオンをまとめました。

Databricksについて概要を学んでいても、実際の画面を開いたときに、

  • どこからデータを取り込むのか
  • NotebookとSQL Editorはどう使い分けるのか
  • Catalog、Schema、Tableは何を表しているのか
  • どのようにデータを加工・可視化するのか

が分からず、最初の一歩で迷いやすいと感じました。

そこで本記事では、Databricksを初めて触る私のような初心者向けに、次の流れを一通り実施します。

  1. 練習用Excelデータを準備する
  2. ExcelをDatabricksへ取り込む
  3. Catalog、Schema、Tableを確認する
  4. NotebookでSQLを実行する
  5. データ品質を確認する
  6. Deltaテーブルを作成する
  7. 集計結果を可視化する
  8. Viewを作成する
  9. Delta Lakeの履歴からデータを復元する

この記事のゴールは、Databricksの各機能を個別に覚えることではなく、元データの取込から分析用データの作成までを一連の処理として理解することです。

対象読者

  • Databricksを初めて触る方
  • SQLの基本的なSELECT、JOIN、GROUP BYを理解している方
  • Snowflake、BigQuery、Azure Synapse Analyticsなど、別のデータ基盤製品を触った経験がある方
  • Databricksの画面と基本操作を短時間で把握したい方

今回作成する構成

test
└── handson
    ├── sales_raw
    ├── product_master
    ├── sales_clean
    ├── sales_clean_lab
    └── v_monthly_sales
オブジェクト 役割
sales_raw Excelから取り込んだ売上明細
product_master 商品名、カテゴリ、原価を持つ商品マスタ
sales_clean 重複や不正値を除去し、商品マスタを結合したデータ
sales_clean_lab Delta Lakeの復元機能を試すための練習用テーブル
v_monthly_sales 月別・地域別・カテゴリ別に集計したView

実務のデータ層に置き換えると、次のイメージです。

データ層 今回のオブジェクト 役割
Raw / Bronze sales_raw 元データをできるだけそのまま保持
Clean / Silver sales_clean 重複除去、型変換、表記補正、マスタ結合
Business / Gold v_monthly_sales 集計・可視化・画面表示向けデータ

事前準備

Databricks側で必要なもの

  • Databricks Workspaceへのアクセス
  • Unity CatalogのCatalogとSchemaを参照する権限
  • テーブルを作成できる権限
  • Notebookを実行できるCompute
  • Excelを直接読み込む場合はDatabricks Runtime 17.1以降

環境によって、左側メニューの名称や配置が多少異なる場合があります。

練習用データ

今回は2種類のデータを使用します。

sales_raw

カラム 内容
order_id 注文ID
order_date 注文日
customer_id 顧客ID
region 地域
channel 販売経路
product_code 商品コード
quantity 数量
unit_price 単価
discount_rate 割引率
status 注文状態

product_master

カラム 内容
product_code 商品コード
product_name 商品名
category 商品カテゴリ
list_price 標準価格
cost 原価

sales_rawには、データ品質確認の練習用として、意図的に次の問題を含めています。

  • order_idの重複
  • quantityの負数
  • customer_idの欠損
  • region末尾の全角スペース

1. Workspaceの基本画面を確認する

Databricksへログインすると、左側のメニューから主な機能へ移動できます。

メニュー 用途
Workspace Notebookやファイルを保存する
Catalog Catalog、Schema、Table、Volumeを確認する
Compute NotebookやSQLを実行する計算環境を確認する
SQL Editor SQLの作成・実行・保存を行う
Jobs & Pipelines 処理の定期実行や複数タスクの制御を行う

Workspace全体
スクリーンショット ワークスペース全体.png
※表示を日本語に変更しています。

Catalog、Schema、Tableの関係

Catalog.Schema.Table

今回作成するテーブル名は次のようになります。

test.handson.sales_raw

Snowflakeの構造と比較すると、概ね次の対応になります。

Snowflake   : DATABASE.SCHEMA.TABLE
Databricks  : CATALOG.SCHEMA.TABLE

2. ハンズオン用スキーマを作成する

左側メニューからカタログを開き、使用するカタログを選択します。今回はtestを例にします。

カタログ
  └── test
      └── スキーマ作成

スキーマ作成画面
スクリーンショット カタログ.png
右上にある青色の「スキーマを作成」ボタンをクリック

スキーマ名には次を入力します。

handson

SQLで作成する場合は、NotebookまたはSQL Editorで次を実行します。

CREATE SCHEMA IF NOT EXISTS test.handson
COMMENT 'Databricks beginner hands-on';

3. Excelからテーブルを作成する

Databricksでは、.xlsや.xlsxを組み込み機能で読み込めます。

New
  └── Add or upload data
      └── Create or modify a table

Excelファイルを選択すると、対象のSheet、見出し行数、セル範囲を指定できます。

sales_rawの取込

項目 設定値
Sheet sales_raw
Header rows 1
Catalog test
Schema handson
Table name sales_raw

Excelアップロード画面
スクリーンショット データを追加.png
スクリーンショット テーブル作成.png
カタログ、スキーマ、シートを選択し、テーブル名は任意の名前に設定します。

product_masterの取込

項目 設定値
Sheet product_master
Header rows 1
Catalog test
Schema handson
Table name product_master

※Excelの直接取込が利用できない場合は、CSVへ変換して同じ画面から取り込む方法でも問題ありません。

4. Catalog Explorerでテーブルを確認する

カタログ
  └── test
      └── handson
          └── Tables

以下の2テーブルが表示されれば、取込は完了です。

sales_raw
product_master

sales_rawを選択し、次の内容を確認します。

  • Sample Dataにデータが表示される
  • Columnsに10カラムが表示される
  • order_dateが日付型または日時型である
  • quantityが数値型である
  • discount_rateが数値型である

Catalog Explorerのテーブル詳細
スクリーンショット テーブル詳細.png

5. Notebookを作成する

New
  └── Notebook

Notebook名は次のようにします。

databricks_beginner_handson

Notebook右上のCompute選択欄から、利用可能なComputeを接続します。

  • Serverless
  • All-purpose compute
  • SQL Warehouse

Pythonも実行する場合は、ServerlessまたはAll-purpose computeを使用します。

ノートブックとコンピュート接続
スクリーンショット ノートブックとコンピュート接続.png

6. SQLでテーブルを確認する

USE CATALOG test;
USE SCHEMA handson;

SELECT
  current_catalog() AS current_catalog,
  current_schema() AS current_schema;

SHOW TABLES;

SELECT *
FROM sales_raw
LIMIT 20;

SELECT *
FROM product_master
ORDER BY product_code;

DESCRIBE TABLE EXTENDED sales_raw;

Notebookでは、セル左側の実行ボタンを選択するか、Shift + Enterで現在のセルを実行できます。

7. データ品質を確認する

総件数

SELECT COUNT(*) AS raw_count
FROM sales_raw;

今回の練習データでは124件になる想定です。

注文IDの重複

SELECT
  order_id,
  COUNT(*) AS count_per_order
FROM sales_raw
GROUP BY order_id
HAVING COUNT(*) > 1
ORDER BY
  count_per_order DESC,
  order_id;

O0120が2件表示されます。

顧客IDの欠損と数量の不正値

SELECT *
FROM sales_raw
WHERE customer_id IS NULL
   OR quantity <= 0
ORDER BY order_id;
  • O0122：quantityが-2
  • O0123：customer_idがNULL

地域名末尾の空白

SELECT
  order_id,
  region,
  LENGTH(region) AS before_length,
  LENGTH(regexp_replace(region, '[ 　]+$', '')) AS after_length
FROM sales_raw
WHERE region <> regexp_replace(region, '[ 　]+$', '');

正規表現の[ 　]には、半角スペースと全角スペースを指定しています。

8. データを整形してDeltaテーブルを作成する

CREATE OR REPLACE TABLE sales_clean
USING DELTA
COMMENT 'Cleaned and enriched sales data for Databricks hands-on'
AS
WITH ranked AS (
  SELECT
    *,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY order_date DESC, product_code
    ) AS row_num
  FROM sales_raw
),
valid AS (
  SELECT
    order_id,
    CAST(order_date AS DATE) AS order_date,
    customer_id,
    regexp_replace(region, '[ 　]+$', '') AS region,
    channel,
    product_code,
    CAST(quantity AS INT) AS quantity,
    CAST(unit_price AS DECIMAL(12, 2)) AS unit_price,
    CAST(discount_rate AS DECIMAL(5, 4)) AS discount_rate,
    status
  FROM ranked
  WHERE row_num = 1
    AND customer_id IS NOT NULL
    AND quantity > 0
)
SELECT
  s.order_id,
  s.order_date,
  s.customer_id,
  s.region,
  s.channel,
  s.product_code,
  p.product_name,
  p.category,
  s.quantity,
  s.unit_price,
  s.discount_rate,
  s.status,
  CAST(p.cost AS DECIMAL(12, 2)) AS cost,
  ROUND(
    s.quantity * s.unit_price * (1 - s.discount_rate),
    0
  ) AS sales_amount,
  ROUND(
    s.quantity * s.unit_price * (1 - s.discount_rate)
    - s.quantity * CAST(p.cost AS DECIMAL(12, 2)),
    0
  ) AS gross_profit,
  CURRENT_TIMESTAMP() AS processed_at
FROM valid AS s
LEFT JOIN product_master AS p
  ON s.product_code = p.product_code;

このSQLでは次の処理を行っています。

  1. order_idごとの重複除去
  2. customer_idがNULLの行を除外
  3. quantityが0以下の行を除外
  4. region末尾の半角・全角スペースを除去
  5. product_masterを結合
  6. 売上金額と粗利を計算
  7. Delta形式で保存
SELECT COUNT(*) AS clean_count
FROM sales_clean;

今回のデータでは121件になります。

9. Notebook上で可視化する

SELECT
  DATE_TRUNC('MONTH', order_date) AS sales_month,
  region,
  SUM(sales_amount) AS sales_amount
FROM sales_clean
WHERE status = '完了'
GROUP BY
  DATE_TRUNC('MONTH', order_date),
  region
ORDER BY
  sales_month,
  region;

SQL実行後、結果欄のTableの横にある+を選択し、可視化を追加します。

可視化追加
スクリーンショット 可視化を選択.png
可視化エディタ
スクリーンショット 左の.png

項目 設定値
Visualization type Line
X column sales_month
Y column sales_amount
Group by region

カテゴリ別の粗利も集計します。

SELECT
  category,
  SUM(sales_amount) AS sales_amount,
  SUM(gross_profit) AS gross_profit,
  ROUND(
    SUM(gross_profit)
    / NULLIF(SUM(sales_amount), 0)
    * 100,
    1
  ) AS gross_margin_pct
FROM sales_clean
WHERE status = '完了'
GROUP BY category
ORDER BY gross_profit DESC;
項目 設定値
Visualization type Bar
X column category
Y column gross_profit

カテゴリ別
image.png

10. 分析用Viewを作成する

CREATE OR REPLACE VIEW v_monthly_sales AS
SELECT
  DATE_TRUNC('MONTH', order_date) AS sales_month,
  region,
  category,
  SUM(sales_amount) AS sales_amount,
  SUM(gross_profit) AS gross_profit,
  COUNT(DISTINCT order_id) AS order_count
FROM sales_clean
WHERE status = '完了'
GROUP BY
  DATE_TRUNC('MONTH', order_date),
  region,
  category;

SELECT *
FROM v_monthly_sales
ORDER BY
  sales_month,
  region,
  category;
種類 保存するもの
Table データそのもの
View データを取得するSQLの定義
Temporary View 現在のセッション内だけで使用するSQLの定義

11. Delta Lakeの履歴と復元を試す

CREATE OR REPLACE TABLE sales_clean_lab
USING DELTA
AS
SELECT *
FROM sales_clean;

SELECT COUNT(*) AS before_delete
FROM sales_clean_lab;

DELETE FROM sales_clean_lab
WHERE region = '関東';

SELECT COUNT(*) AS after_delete
FROM sales_clean_lab;

DESCRIBE HISTORY sales_clean_lab;

DESCRIBE HISTORYの結果
変更履歴.png

作成直後のVersionが0の場合、次のSQLで元に戻せます。

RESTORE TABLE sales_clean_lab
TO VERSION AS OF 0;

SELECT COUNT(*) AS after_restore
FROM sales_clean_lab;

ハンズオン完了時の確認項目

  • Excelからsales_rawを作成できた
  • Excelからproduct_masterを作成できた
  • Catalog、Schema、Tableの関係を理解できた
  • NotebookをComputeへ接続できた
  • 重複、NULL、負数、末尾スペースを検出できた
  • sales_cleanをDeltaテーブルとして作成できた
  • Notebook上で折れ線グラフと棒グラフを作成できた
  • v_monthly_salesを作成できた
  • Deltaテーブルの履歴を確認できた
  • DELETEしたデータを復元できた

つまずきやすい点

CatalogまたはSchemaが表示されない

管理者へ次の権限を確認します。

  • USE CATALOG
  • USE SCHEMA
  • CREATE TABLE
  • SELECT

Notebookを実行できない

Notebook右上でComputeへ接続されているか確認します。

Excelを取り込めない

Excelの直接読込には対応するDatabricks RuntimeやWorkspace側の機能が必要です。利用できない場合は、ExcelをCSVへ変換して取り込みます。

order_dateが文字列型になった

CAST(order_date AS DATE)

のように明示的に変換します。

まとめ

今回のハンズオンでは、Databricksの基本的な画面操作から、Excelの取込、データ品質確認、Deltaテーブル作成、可視化、View作成、履歴復元までを実施しました。

データを取り込む
  ↓
Catalogで管理する
  ↓
Notebookで確認・加工する
  ↓
Deltaテーブルへ保存する
  ↓
集計・可視化する
  ↓
履歴で処理を追跡する

次のステップでは、今回作成したテーブルを使い、以下を実施します。

  • 売上やデータ品質の異常を検知するSQL Alert
  • Notebookを定期実行するLakeflow Jobs
  • 複数タスクを依存関係付きで実行するJob
  • Raw、Clean、集計の処理を管理するLakeflow Pipeline
  • JobやPipelineが失敗した場合の通知
  • データ追加をきっかけに処理を実行するTrigger

参考資料

2
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?