DatabricksAdvent Calendar 2022

データブリックス・ジャパン株式会社

今さら聞けないSQL

Last updated at 2024-05-13Posted at 2022-11-28

こちらのウェビナーで説明した内容の抜粋です。

ウェビナーで使用したノートブックはこちらにあります。

スライドはこちら。

SQLとは

SQLが何かをお話しする前に、SQLとの関係が深いデータやデータベースの話をさせてください。

データとは

データとは1人または複数の人や物や事象に関する定性的または定量的な値の集まりである。

データ - Wikipedia

例えば、以下の様にさまざまな人に関する属性の集合はデータと言えます。データは必ずしも電子データである必要はありません。この様な属性が紙の台帳に記載されていたとしてもデータであると言えます。

データベースとは

コンピューティングにおいて、データベースは、電子的に保存され、アクセスできる組織化されたデータの集合である。

データベース - Wikipedia

コンピュータで、これらデータを取り扱う際にデータベースを使用することは一般的です。そして、大量データを管理するデータベースに問い合わせるための言語がSQLです。

SQL(Structured Query Language)とは

SQLは、関係データベース管理システム (RDBMS) において、データの操作や定義を行うためのデータベース言語（問い合わせ言語）、ドメイン固有言語である。プログラミングにおいてデータベースへのアクセスのために、他のプログラミング言語と併用される。

SQL - Wikipedia

今では、データベースは生活の様々なシーンで活用されており、これらデータベースを操作するための言語がSQLとなります。

いわゆるクライアントサーバシステムにおいて、クライアントからデータベースに問い合わせる際にSQLが使用されます。

そして、データベースにあるデータをどの様に処理するのかをSQLで記述します。

以下の様な処理をSQLで記述します。

列「Prefecture」のデータをすべてください。
列「pref_no」の昇順で並び替えたデータをください。
列「Prefecture」が「Hokkaido」であるすべての行をください。
列「date_timestamp」から年月を取り出し、「Prefecture」と年月ごとの「Cases」の合計をください。

以下に「列Prefectureのデータをすべてください」のSQLを示します。最もシンプルなSQLと言えます。

SQL

SELECT Prefecture FROM covid_cases;

SQLで何ができる？

データベースにあるデータに対するすべての操作が行えると言っても過言ではありません。できることが多岐に渡るため、ある程度の分類がされています。ここでは、データ分析を行うペルソナの使用頻度が高いDML(Data Manipulation Language)にフォーカスします。

DDL(Data Definition Language)
- データベース、テーブルなどを作成、削除します
- CREATE/DROP/ALTER
DML(Data Manipulation Language)
- テーブルを検索したり、変更します
- SELECT/INSERT/UPDATE/DELETE
DCL(Data Control Language)
- テーブルに対する権限設定などを行います
- GRANT/REVOKE

SQLと言えば、とにもかくにもSELECTからです。SELECT(選択する)の名前が示す通り、取り出すデータを選択するために使用します。通常SELECTはFROM(から)と組み合わせます。FROMではデータを取り出すテーブルを指定します。

SELECTをいろいろカスタマイズしていきます。上の例ではテーブルのすべての行を取り出してしまいます。WHERE(そこでは〜である) と抽出条件を組み合わせることで、取り出す行を限定することができます。

データベースを操作できる環境で以下のSQLを実行して、必要とするデータを取得していきます。

SELECT
GROUP BY
CREATE VIEW
関数
述語(predicate)
CASE
UNION
JOIN

データベースを操作できる環境は色々ありますが、ここではDatabricksのレイクハウスプラットフォームを使用します。

以降で使用するデータに関しては、以下のノートブックを実行してください。ここでは、データがデータベースjapan_covid_takaakiyayoidatabrickscomに格納されているものとします。

SQL基礎 - テーブルの参照

こちらではSQLの以下のSQLのうち、データの取り出しに使用するDML、特に参照系の操作にフォーカスして説明します。

DDL(Data Definition Language)
DML(Data Manipulation Language)
DCL(Data Control Language)

SQL

-- 事前に作成しておいたデータベースを選択します
USE japan_covid_takaakiyayoidatabrickscom;

SELECT

テーブルからデータを取り出す際に必須になるのがSELECTです。

SQL

-- テーブル「covid_cases」から全レコードを取得します
SELECT
  *
FROM
  covid_cases;

SQL

-- レコード件数を取得します
SELECT
  count(*)
FROM
  covid_cases;

SQL

-- ユニークな県名のみを取得します
SELECT
  DISTINCT Prefecture,
  pref_no
FROM
  covid_cases
ORDER BY -- 県番号の昇順でソートします
  pref_no ASC;

少し複雑になりましたが、DISTINCTでユニークな値を取り出しています。そして、ORDER BYで並び替えをおこなっています。ASCは昇順、DESCは降順です。

GROUP BY

集計キーを指定してテーブルのデータをグルーピングします。グループに対して集計関数(カウントや平均値など)を適用して、グループごとのデータを取得します。

SQL

SELECT
  Area,
  COUNT(*) AS count -- レコード数をカウントし、列名をcountにします
FROM
  covid_cases
GROUP BY -- Areaでグルーピングします
  Area
ORDER BY -- Areaごとの件数の降順でソートします
  count DESC;

ビュー

ビューは保存済みのクエリーと言えます。特定の切り口で参照することが頻繁にある場合には、その切り口でビューを作成しておくと取り回しが楽になります。

SQL

CREATE
OR REPLACE VIEW tokyo_only AS
SELECT
  *
FROM
  covid_cases
WHERE -- Tokyoのレコードのみに限定します
  Prefecture = "Tokyo"

テーブルの様にビューにアクセスすることができます。

SQL

SELECT * FROM tokyo_only;

関数

頻繁に使用する処理は関数によって実現することができます。

SQL

SELECT
  date(date_timestamp) || " : " || Cases AS date_case -- 日付と感染者数を結合して date_case という列を生成します
FROM
  tokyo_only;

述語(predicate)

テーブルから取得するデータを絞り組むには述語を指定します。以下の述語(WHERE句)では以下の条件を指定しています。すべてANDで結合されているので、すべての条件に合致した行のみが抽出されます。

日付date(date_timestamp)が2022-01-01と2022-01-10
県名にshimaを含む。Lower(Prefecture)としているのは、ローマ字の大文字小文字があるので、小文字に揃えているためです。

SQL

SELECT
  *
FROM
  covid_cases
WHERE
  date(date_timestamp) BETWEEN "2022-01-01"
  AND "2022-01-10"
  AND Lower(Prefecture) LIKE "%shima%"

CASE

取得するデータの内容に対して判定を行い、新たなデータを生成することができます。

SQL

SELECT
  Prefecture,
  CASE -- Prefectureの文字列に条件を適用した結果に基づく新たな列を作成します
    WHEN Lower(Prefecture) LIKE "%shima%" THEN "島を含む"
    ELSE "島を含まない"
  END AS shima_included
FROM
  covid_cases
GROUP BY
  Prefecture

UNION

2つ以上のテーブルを連結することができます。テーブルのデータを縦方向に連結するにはUNIONを使います。なお、UNIONで結合するテーブルは同じ列構成である必要があります。

結合するために千葉県のみのデータをビューとして準備します。

SQL

CREATE
OR REPLACE VIEW chiba_only AS
SELECT
  *
FROM
  japan_covid_takaakiyayoidatabrickscom.covid_cases
WHERE
  Prefecture = "Chiba"

SQL

SELECT
  *
FROM
  tokyo_only
UNION
SELECT
  *
FROM
  chiba_only;

JOIN

テーブルのデータを横方向に連結するにはJOINを使います。

以下のテーブルの元データはこちらにあります。

SQL

SELECT * FROM prefectural_capital;

県名Prefectureをキーとして二つのテーブルを連結します。

SQL

SELECT
  cc.*,
  pc.prefectural_capital
FROM
  covid_cases cc
  INNER JOIN prefectural_capital pc ON cc.Prefecture = pc.Prefecture;

これで新たに県庁所在地の列が追加されました。

分析の文脈でのSQLの活用

今では、機械学習やBIの文脈でSQLを活用するケースが増えています。「地方別、日別の感染者数の変化を見たい」というケースでは、以下の様なSQLを実行することになります。

SQL

SELECT
  Area,
  date(date_timestamp) AS date,
  SUM(Cases) AS total_cases
FROM
  covid_cases
GROUP BY
  Area,
  date
ORDER BY
  Area,
  date;

さらに、これをグラフで可視化することも有効です。Databricksでは、取得したデータを簡単に可視化する機能が備わっています。

ノートブックでSQLの実行結果を視覚的に確認しながら、作業を進めることができます。

さらには地図上に感染者数をマッピングすることもできます。

SQL

SELECT
  Prefecture,
  SUM(Cases) AS totale_cases
FROM
  covid_cases
GROUP BY
  Prefecture

SQL基礎 - テーブルの更新

次に、以下のSQLを実行してテーブルを更新していきます。

INSERT
UPDATE
DELETE
SELECT AS OF

こちらではSQLのうち、データの取り出しに使用するDML、特にテーブル更新の操作にフォーカスして説明します。

テーブルの作成

更新処理を行うダミーのテーブルを作成します。なお、こちらのDROP TABLEやCREATE TABLEはDDLです。

SQL

-- テーブルが存在する場合には削除
DROP TABLE IF EXISTS dummy_covid;

SQL

-- covid_casesから取得したデータを使って新たにdummy_covidテーブルを作成します
CREATE TABLE dummy_covid COMMENT 'これは更新処理向けのダミーテーブルです。' AS
SELECT
  *
FROM
  covid_cases;

SQL

-- テーブルのメタデータを確認します
DESCRIBE TABLE EXTENDED dummy_covid;

SQL

-- 都道府県別の合計感染者数を合計します
SELECT
  Prefecture,
  sum(cases) AS total_cases
FROM
  dummy_covid
GROUP BY
  Prefecture;

UPDATE

テーブルのデータを更新するにはUPDATEを使用します。

SQL

-- Hokkaidoの感染者数を100倍に更新します
UPDATE
  dummy_covid
SET
  cases = cases * 100
WHERE
  Prefecture = "Hokkaido";

SQL

-- 都道府県別の合計感染者数を合計します
SELECT
  Prefecture,
  sum(cases) AS total_cases
FROM
  dummy_covid
GROUP BY
  Prefecture;

DELETE

データを削除するにはDELETEを使用します。削除対象を限定するためにはWHEREを使用します。

SQL

-- Hokkaidoのデータをすべて削除します
DELETE FROM
  dummy_covid
WHERE
  Prefecture = "Hokkaido";

SQL

-- 都道府県別の合計感染者数を合計します
SELECT
  Prefecture,
  sum(cases) AS total_cases
FROM
  dummy_covid
GROUP BY
  Prefecture;

INSERT

テーブルにデータを追加するにはINSERTを使います。

SQL

INSERT INTO
  dummy_covid(Prefecture, date_timestamp, cases, pref_no, Area)
VALUES("ぷりぷり県", "2022-10-28", 10, 99, "不明");

SQL

SELECT
  *
FROM
  dummy_covid
ORDER BY
  pref_no DESC;

更新処理には注意が必要です

誤ってデータを消してしまった、上書きしてしまったということを避けることは正直困難です。
そんな場合に頼りになる機能があります。Delta Lakeのタイムトラベルです。
ここまで操作してきたテーブルの実態はDelta Lakeというソフトウェアによって実装されているものです。
Delta Lakeはトランザクション保証、インデックスなどの機能がサポートされていますが、上の様なケースで役立つのがデータのバージョン管理であるタイムトラベルです。
Delta Lakeのテーブルに実行される更新処理はすべて記録され、任意のタイミングにロールバックすることができます。

参考資料 Delta Lakeにダイビング：トランザクションログを読み解く

DESCRIBE HISTORY

Delta Lakeにおけるテーブルユーティリティコマンド - Qiita

SQL

-- テーブルの更新履歴を確認します
DESCRIBE HISTORY dummy_covid;

これまでのテーブル更新処理がすべて記録されています。

SQL

-- バージョン0、テーブル作成直後の状態を参照します
SELECT
  *
FROM
  dummy_covid VERSION AS OF 0
ORDER BY
  pref_no DESC;

バージョン0では、行が追加されていないことが確認できます。

SELECT AS OF

SQL

-- 都道府県別の合計感染者数を合計します
SELECT
  Prefecture,
  sum(cases) AS total_cases
FROM
  dummy_covid VERSION AS OF 0
GROUP BY
  Prefecture;

UPDATE、DELETE前の状態を参照することができます。

RESTORE

RESTORE (Delta Lake on Databricks) | Databricks on AWS

SQL

-- バージョン0の状態にレストアします
RESTORE TABLE dummy_covid TO VERSION AS OF 0;

SQL

-- テーブルの更新履歴を確認します
DESCRIBE HISTORY dummy_covid;

このように、Delta Lakeでデータを保持することで、安心してデータの操作が行える様になります。

コラム1：SQLの読み方

個人の意見・感想です。

英語の文法としてSQLを読むと理解しやすいです。

SQLの柔軟性は素晴らしいものですが、その代償としていくらでも複雑になり得ます。
複雑なSQLを理解するには、
- フォーマットして可読性を上げ、
- 英語の文を読む語順 (SELECT -> FROM -> WHERE -> GROUP BY -> HAVING -> ORDER BY) でブロックを把握し、
  - FROMに記述されているテーブルから、
  - WHEREに記述されている抽出条件に基づいてレコードを抽出し、
  - GROUP BYに指定されている集約キーでグルーピングし、
  - HAVINGに記述されている条件に基づいてグループを絞り込み、
  - ORDER BYに指定されているソートキーで並び替えて、
  - SELECTに指定されている列
- を取得する。
という流れで私は読んでいます。

サンプルで説明します。

以下のようなSQLを想定します。
(Databricksを含め)大抵のIDEにはフォーマッター(formatter)が搭載されているのでそれを活用します。
以下のように整形されますので、前述の順序で処理を理解します。

コラム2：ローコード・ノーコードツール

「SQLを知らなくてもローコード・ノーコードツールがあるじゃないか」と思われるかもしれません。

確かにローコード・ノーコードツールは便利です。定型文を書く手間を省くことができるなど、試行錯誤の過程で生産性を高める役に立ちます。

しかし、ノーコードツールで作成したロジックを本格運用する際には、以下を検討した上で全体的なワークフローを設計すべきです。

なぜ我々はコードを書くのか - Qiita

機能性 コードはノーコードツールよりもリッチであり、多くの場合、5倍から10倍豊富です。同じソフトウェアにおいて、ドラッグ&ドロップのUIとコードベースのAPIを比較してください。ソフトウェアの開発者は、ノーコードインタフェースに組み込む前にAPIに新機能を組み込みます。多くの機能はノーコードバージョンに取り込まれることはありません。これは、数多くのオペレーターをグラフィカルなUIに取り込むには制限があるためです。
柔軟性 ノーコードツールは一部の機能しかサポートしていないため、開発者は重要と考えるものにフォーカスします。シンプルな分析を行っているのであれば問題ありません。そうでなければ不幸なことになります。コードは無制限の柔軟性を持っています。これが、データサイエンス向けのすべてのノーコードツールに、ユーザーがコードを挿入できるようなコードノード機能がある理由です。
透明性 コードはありのままであり、検査に対してオープンです。ノーコードツールではこれは常に真となりません。データサイエンティストは、自分の作業の精度に対して説明責任を持ちます。分析が適切ではなかったとしてもツールに文句を言うことはできません。コーディングをしているのであれば、データから洞察に至る処理パイプラインは完全に可視化されます。
効率性 誰もゼロからコーディングはしません。データサイエンスチームは再利用なコードのコンポーネントを整理し共有します。実行性能を改善し、計算インフラストラクチャへのインパクトを最小化するために、コードを調整・チューニングすることができます。これはノーコードツールでは不可能です。

なお、Databricksでもローコードのデータ加工ツール(bamboolib)を提供しています。この場合、pandasデータフレームをGUIで操作できますが、対応するPythonコードが自動で生成されるので、後段の開発にコードを活用することができる様になっています。

SQL応用編

前のセクションではテーブルのデータに対してSQLを使用しましたが、処理の要件が複雑化するに伴い、さまざまな拡張が為されてます。

JSONなど1行の中にネストされたデータの取り扱い
関数を引数に取る高階関数
地理空間情報の取り扱い

以下のノートブックでこれらの内容をウォークスルーしています。

まとめ

SQLは手段であって目的ではありません。SQLは何かしらの目的のもとでデータを加工、取得するために用いるものです。

ダッシュボードに表示するデータを準備するためにSQLを用い、ダッシュボードからビジネス上の洞察を得る。
機械学習のトレーニングデータを準備するためにSQLを用い、機械学習モデルに品質の良いデータを入力することで、精度の高いモデルを構築し、高精度な需要予測を実現する。

Databricksでは、以下のメリットを享受いただきながらSQLを活用いただくことができます。

Databricks SQL
ノートブックでのSQLサポート
SQLの結果をダッシュボード、機械学習などとシームレスに連携
並列分散処理による高速化
高度な要件に答えるSQL拡張

Databricks 無料トライアル

You get articles that match your needs
You can efficiently read back useful information
You can use dark theme

What you can do with signing up