Azure DatabricksでVIEWの作成手順

目次
1. はじめに
2. 必要条件
3. VIEWの作成方法
4. VIEWアクセス権限の割り当て方法
動的VIEW
5. VIEWのパフォーマンスの最適化方法
6. メダリオンアーキテクチャにてVIEW使用のサンプル
7. まとめ
この記事を読んだ方へのオススメコンテンツはこちら

1. はじめに

皆さんこんにちは。

今回はDatabricksのVIEWを紹介いたします。DatabricksのVIEWの利用用途は他のRDBMSと同じです。

・ビューを用いることで、複雑なクエリを簡単な形に抽象化できます。ユーザーはビューにクエリを実行するだけで、複数のテーブルから必要な情報を効率良く取得できます。

・ビューを使って、ユーザーが見ることができる列や行を制限することができます。これにより、データのセキュリティが向上します。

今回は以下の内容について説明していきます。

・DatabricksでVIEWを作成する方法

・DatabricksでVIEWの使用時にパフォーマンスを強化する方

・VIEWのアクセス権限を割り当てする方法

・メダリオンアーキテクチャにてVIEWを使用するサンプル

2. 必要条件

本記事のサンプルを実施できる必要条件を説明します。
・Premium レベルの Azure Databricks ワークスペースが必要です。
・ユーザーはカタログの USE CATALOG 権限、およびスキーマの USE SCHEMA 権限を有している必要があります。
・ビューがワークスペースローカルの Hive メタストア内のテーブルを参照している場合、そのビューにアクセスできるのは、ワークスペースローカルテーブルが含まれるワークスペースからに限られます。そのため、Databricks では、Unity Catalog メタストア内にあるテーブルまたはビューからのみビューを作成することをお勧めします。
・ビュー作成の必要条件を参照してください。

3. VIEWの作成方法

今回は、「tpch」スキーマというDatabricksのサンプルデータを使用します。

会社の総収益を分析するビューを作成します。custkeyフィールドを使用して、order 及び customerテーブルを結合します。

order 及び customerテーブルの構成は以下の通りです。

新規Notebooksを作成し、以下のコマンドを実行してVIEWを作成します。

文法

CREATE [ OR REPLACE ] [ TEMPORARY ] VIEW [ IF NOT EXISTS ] view_name

[ column_list ]

[ COMMENT view_comment ]

[ TBLPROPERTIES clause ] AS query

CREATE [ OR REPLACE ] [ TEMPORARY ] VIEW [ IF NOT EXISTS ] view_name

[ column_list ]

[ COMMENT view_comment ]

[ TBLPROPERTIES clause ] AS query

サンプル

例

CREATE OR REPLACE VIEW democatalog.tpch_clone.nation_consumer

AS SELECT 
	c_custkey, c_name, c_nationkey, o_orderkey, o_totalprice, o_orderpriority, o_shippriority

FROM 
	democatalog.tpch_clone.customer

INNER JOIN 
	democatalog.tpch_clone.orders

ON c_custkey = o_custkey;

CREATE OR REPLACE VIEW democatalog.tpch_clone.nation_consumer

AS SELECT

c_custkey, c_name, c_nationkey, o_orderkey, o_totalprice, o_orderpriority, o_shippriority

FROM

democatalog.tpch_clone.customer

INNER JOIN

democatalog.tpch_clone.orders

ON c_custkey = o_custkey;

コマンドの実行結果は以下の通りです。

VIEWの作成が成功しました。VIEWのSELECT文の結果が表示されます。

目的のデータを取得する際、JOIN句を使用するより、あらかじめ結合済みのVIEWからデータ取得するほうが簡単です。

JOIN句を利用したSQL

select 
	c_nationkey, SUM(o_totalprice) 
from
	(SELECT 
		c_nationkey, o_orderkey, o_totalprice
	FROM 
		democatalog.tpch_clone.customer
	INNER JOIN 
		democatalog.tpch_clone.orders
	ON c_custkey = o_custkey) as temp
group by c_nationkey;

select

c_nationkey, SUM(o_totalprice)

from

(SELECT

c_nationkey, o_orderkey, o_totalprice

FROM

democatalog.tpch_clone.customer

INNER JOIN

democatalog.tpch_clone.orders

ON c_custkey = o_custkey) as temp

group by c_nationkey;

VIEWを使用してSQL

select 
	c_nationkey, SUM(o_totalprice) 
from 
	democatalog.tpch_clone.nation_consumer 
group by 
	c_nationkey;

select

c_nationkey, SUM(o_totalprice)

from

democatalog.tpch_clone.nation_consumer

group by

c_nationkey;

クエリ結果が同じです。

4. VIEWアクセス権限の割り当て方法

Databricks ワークスペースの Data explore UI、または NotebookでVIEW権限を割り当てることができます。

Databricks ワークスペースの Data explore UIでVIEWアクセス権限の割り当て方法は、以下の通りです。

Data explore タブに移動する > Unity Catalog > Schema > VIEWを開く。VIEWの概要メニューで「Permission」タブにアクセスします。

「Grant」ボタンをクリックします。

グループ・ユーザーを選択して、SELECTの権限にチェックを入れます。「Grant」ボタンをクリックします。

以下のコマンドを実行して、Databricks workspaceでVIEWのアクセス権限を割り当てします。

文法

GRANT SELECT ON VIEW View-name TO principal-name

1	GRANT SELECT ON VIEW View-name TO principal-name

以下のコメントを実行することで、VIEWのアクセス権限割り当ての実行結果を確認できます。

[SHOW GRANTS ON VIEW <view-name>]

動的VIEW

動的VIEWは、ユーザー毎に行列レベルのアクセス権限を割り当てることができます。

注意事項：Single User アクセスのクラスターでは動的VIEWは稼働できません。

列アクセス権限について

文法

CREATE VIEW view_name
AS
SELECT field_name1
CASE WHEN is_account_group_member('group_name') THEN field_name
ELSE 'REDACTED' END AS field_name, field_name2
FROM table

CREATE VIEW view_name

SELECT field_name1

CASE WHEN is_account_group_member('group_name') THEN field_name

ELSE 'REDACTED' END AS field_name, field_name2

FROM table

以下の例では「data-analyst-gr」のユーザーである場合、「c_custkey」カラムの値が表示されます。それ以外、「c_custkey」カラムの値が「REDACTED」となります。

以下のコマンドを実行して、「data-analyst-gr」のユーザーに対して「c_custkey」カラムの値を確認します。

「data-analyst-gr」グループにVIEWのSELECT権限を割り当てます。

「data-analyst-gr」ユーザーではないので、「c_custkey」フィールドの値が「REDACTED」となります。

「data-analyst-gr」ユーザーであるので、「c_custkey」フィールドの値が表示されます。

行アクセス権限について

文法

CREATE VIEW view_name
AS
SELECT field_name, field_name1, field_name2
FROM table
WHERE
CASE
WHEN is_account_group_member(group_name) THEN TRUE
ELSE condition
END;

CREATE VIEW view_name

SELECT field_name, field_name1, field_name2

FROM table

WHERE

CASE

WHEN is_account_group_member(group_name) THEN TRUE

ELSE condition

END;

以下の例では合計金額の100000以上の行が「data-analyst-gr」ユーザーのみに表示されます。

「data-analyst-gr」ユーザーにVIEWのSELECT権限を割り当てます。

「data-analyst-gr」ユーザーではない場合、合計金額の100000以上の行が表示されません。

「data-analyst-gr」ユーザーである場合、合計金額の100000以上の行が表示されます。

5. VIEWのパフォーマンスの最適化方法

VIEWを作成する際に、インデックスを作成している列でテーブルを結合するとレスポンスがよくなります。

「Customer」テーブルの「c_custkey」列に対して Z-order インデックスを作成します。

「Orders」テーブルの「c_custkey」列に対して Z-order インデックスを実行します。

Z-orderを作成した後の検索：1.06秒

Z-orderを作成する前の検索：1.18秒

VIEWを使わない：1.94秒

6. メダリオンアーキテクチャにてVIEW使用のサンプル

Databricksのホームページに掲載された図から、レイヤーごとに分かりやすい例を含む図を提供します。

ご参考のリンク

Databricksのサンプルデータはシルバーレイヤー（silver Layer）なので、今回はデータをシルバーレイヤー（Gold layer）からゴールドレイヤーまでフィルタリングする方法について説明していきます。VIEWを使用してレポートを出力します。

・クエリ分の複雑さを陥落します。

・VIEWのデータ管理がしやすくなります。

会社の総収益のレポートを取得します。レポートの目的によってデータの抽出方法が異なります。

silver layerの既存テーブル:

データ分析のために、Silver layerの3NF(第3正規形)テーブルをStarスキーマ構造のテーブルに変換します。

Gold layerのStarスキーマ構造のテーブル

会社の総収益のレポートを出力しるためのVIEWのフィールドが次のように含まれます。

データを変換します。

実際には、DIM 、およびFact テーブルに毎日データが追加されますが、今回はVIEWの作成に関する説明であるので、旧テーブルに基づいて新規テーブルを作成します。データ変換について実践例は別途で説明します。

「orders」テーブルのデータから「order DIM」テーブルのデータに変換します。「order DIM」テーブルから「o_custkey」フィールドを削除します。

コマンド

CREATE TABLE IF NOT EXISTS democatalog.tpch_clone.orders
AS SELECT 
	o_orderkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment
FROM 
	samples.tpch.orders;

CREATE TABLE IF NOT EXISTS democatalog.tpch_clone.orders

AS SELECT

o_orderkey, o_orderstatus, o_totalprice, o_orderdate, o_orderpriority, o_clerk, o_shippriority, o_comment

FROM

samples.tpch.orders;

コマンドの実行結果

Unity Catalogにデータをコピーします。

customerテーブルをコピーします。

コマンド

CREATE TABLE IF NOT EXISTS democatalog.tpch_clone.customer AS SELECT * FROM samples.tpch.customer

1	CREATE TABLE IF NOT EXISTS democatalog.tpch_clone.customer AS SELECT * FROM samples.tpch.customer

コマンドの実行結果

nationテーブルをコピーします。

コマンド

CREATE TABLE IF NOT EXISTS democatalog.tpch_clone.company
AS
SELECT 
	n_nationkey as co_companykey, n_name as co_name, n_regionkey as co_regionkey, n_comment as co_comment
FROM 
	samples.tpch.nation

CREATE TABLE IF NOT EXISTS democatalog.tpch_clone.company

SELECT

n_nationkey as co_companykey, n_name as co_name, n_regionkey as co_regionkey, n_comment as co_comment

FROM

samples.tpch.nation

コマンドの実行結果

Factテーブルを作成します。

コマンド

CREATE TABLE IF NOT EXISTS democatalog.tpch_clone.order_fact
AS
	SELECT 
		o_orderkey AS f_orderkey, f_custkey, f_companykey, o_totalprice AS f_totalprice, o_orderdate AS f_orderdate
	FROM 
		samples.tpch.orders
	INNER JOIN
		(SELECT 
			c_custkey AS f_custkey, n_companykey AS f_companykey
		FROM 
			samples.tpch.customer
		INNER JOIN 
			samples.tpch.company
		WHERE 
			c_companykey = n_companykey)
	WHERE f_custkey = o_custkey

CREATE TABLE IF NOT EXISTS democatalog.tpch_clone.order_fact

SELECT

o_orderkey AS f_orderkey, f_custkey, f_companykey, o_totalprice AS f_totalprice, o_orderdate AS f_orderdate

FROM

samples.tpch.orders

INNER JOIN

(SELECT

c_custkey AS f_custkey, n_companykey AS f_companykey

FROM

samples.tpch.customer

INNER JOIN

samples.tpch.company

WHERE

c_companykey = n_companykey)

WHERE f_custkey = o_custkey

コマンドの実行結果

VIEWを作成します。

コマンド

CREATE OR REPLACE VIEW democatalog.tpch_clone.company_revenue_view
AS
	SELECT 
		v_companykey, v_totalrevenue, v_companyname
	FROM
		(SELECT 
			v_companykey, v_totalrevenue, co_name as v_companyname
		FROM
			(SELECT 
				f_companykey as v_companykey, SUM(f_totalprice) as v_totalrevenue
			FROM 
				democatalog.tpch_clone.order_fact
			GROUP BY f_companykey)
	INNER JOIN democatalog.tpch_clone.company
	WHERE v_companykey = co_companykey)

CREATE OR REPLACE VIEW democatalog.tpch_clone.company_revenue_view

SELECT

v_companykey, v_totalrevenue, v_companyname

FROM

(SELECT

v_companykey, v_totalrevenue, co_name as v_companyname

FROM

(SELECT

f_companykey as v_companykey, SUM(f_totalprice) as v_totalrevenue

FROM

democatalog.tpch_clone.order_fact

GROUP BY f_companykey)

INNER JOIN democatalog.tpch_clone.company

WHERE v_companykey = co_companykey)

コマンドの実行結果

レポートを取得します。

コマンド

SELECT * FROM democatalog.tpch_clone.company_revenue_view ORDER BY v_totalrevenue DESC

1	SELECT * FROM democatalog.tpch_clone.company_revenue_view ORDER BY v_totalrevenue DESC

コマンドの実行結果

グループ・ユーザーのアクセス権限を割り当てます。

コマンド

GRANT SELECT ON VIEW democatalog.tpch_clone.company_revenue_view TO `data-analyst-gr`

1	GRANT SELECT ON VIEW democatalog.tpch_clone.company_revenue_view TO `data-analyst-gr`

コマンドの実行結果

7. まとめ

・DatabricksでのVIEWの機能は、RDBSでのVIEWの機能と同じです。
‣　クエリの複雑さを下がります。
‣　データの管理がしやすくなるために、ユーザーの権限を割り当てます。
・DatabricksでのVIEW作成構文は、RDBSでのVIEW作成構文と同じです。
・Databricksに動的VIEWがあります。動的VIEWが基本的にVIEWですが、グループ・ユーザーに応じてVIEWのカラム・行のアクセス権限を割り当てることができます。
・注意事項：
‣　VIEWを作成する時に、クラスタリングしているフィールドでテーブルを結合した方が良いです。
‣　VIEWがワークスペースローカルの Hive メタストア内のテーブルを参照している場合、そのVIEWにアクセスできるのは、ワークスペースローカルテーブルが含まれるワークスペースからに限られます。そのため、Databricks では、Unity Catalog メタストア内にあるテーブルまたはVIEWからのみVIEWを作成することをお勧めします。

今回の記事が少しでも皆さんの新しい知識や業務のご参考になれば幸いです。

日商エレクトロニクスでは、Azure Databricksの環境構築パッケージを用意しています。
Azure DatabricksやAzure活用、マイクロソフト製品の活用についてご相談事がありましたら
是非お問い合わせください！

Azure Databricks連載記事のまとめはこちら

お問い合わせはこちら