Azure Synapse Analytics Serverless SQL Pool でDelta Lakeをクエリする（Preview）

はじめに

BuildあたりでプレビューがはじまったSynapse Analytics Serverless SQL PoolでのDelta Lakeのチュートリアルを紹介します。

参考のMSDocsのチュートリアルの拡張版的にお使いください。

※2021/6の情報となります

参考：

Query Delta Lake files (preview) using serverless SQL pool in Azure Synapse Analytics

Synapse SQL で外部テーブルを使用する

Query Delta Lake files using T-SQL language in Azure Synapse Analytics

Delta Lake について

まずは手前味噌ですが

Databrikcsのはじめかた

Delta Lake概要
※0.3のころなので古い点あるかも

DatabricksのCSAの方がめちゃめちゃわかりやすく活用方法まで記事を書いてくれています。

@taka_yayoi

Qiita検索結果

サンプルコード

Spark Poolでデータを準備

Azure Open Datasetsからアメリカの人口データを使っていきます。

300万件くらいのデータです。

pyspark

# Create Data Frame and Display
from azureml.opendatasets import UsPopulationCounty

population = UsPopulationCounty()
population_df = population.to_spark_dataframe()
display(population_df.limit(5))

pyspark

# Write format Delta

(population_df.write
.format("delta")
.mode("overwrite")
.save("abfss://datalake@<Azure Data Lake Storage Gen2名を入れてください>.dfs.core.windows.net/bronze/UsPopulationCounty/"))

ちなみに

Spark SQLでテーブル登録
この場合はSparkテーブルとして登録されますが、今のところServerelssSQLPoolからはうまく動作しません。

sql

CREATE TABLE UsPopulationCounty
USING DELTA
LOCATION 'abfss://datalake@<Azure Data Lake Storage Gen2名を入れてください>.dfs.core.windows.net/bronze/UsPopulationCounty/'

Serverless SQL Pool でクエリ

右クリックから100行選択→パスをフォルダに変えてFORMATをDELTAに変更するのが簡単です

sql

SELECT
    TOP 100 *
FROM
    OPENROWSET(
        BULK 'https://dlsanalyticsdemo.dfs.core.windows.net/datalake/bronze/UsPopulationCounty/',
        FORMAT='DELTA'
    ) AS [result]

Serverless SQL Pool 上で外部テーブル化

こちらも右クリックからスクリプトを生成させるのが楽です。

フォーマットがDeltaで、locationがファイルになっているので、変更します。（ハイライト箇所）

修正後のSQL例

sql

IF NOT EXISTS (SELECT * FROM sys.external_file_formats WHERE name = 'SynapseDeltaFormat') 
	CREATE EXTERNAL FILE FORMAT [SynapseDeltaFormat] 
	WITH ( FORMAT_TYPE = delta)
GO

IF NOT EXISTS (SELECT * FROM sys.external_data_sources WHERE name = 'datalake_<>_dfs_core_windows_net') 
	CREATE EXTERNAL DATA SOURCE [datalake_<>_dfs_core_windows_net] 
	WITH (
		LOCATION   = 'https://<Storage Account 名>.dfs.core.windows.net/datalake', 
	)
Go

CREATE EXTERNAL TABLE dbo.UsPopulationCountrySQL (
	[decennialTime] varchar(8000),
	[stateName] varchar(8000),
	[countyName] varchar(8000),
	[population] int,
	[race] varchar(8000),
	[sex] varchar(8000),
	[minAge] int,
	[maxAge] int,
	[year] int
	)
	WITH (
	LOCATION = 'bronze/UsPopulationCounty/',
	DATA_SOURCE = [datalake_<>_dfs_core_windows_net],
	FILE_FORMAT = [SynapseDeltaFormat]
	)
GO

SELECT TOP 100 * FROM dbo.UsPopulationCountrySQL
GO

実行後、外部テーブルとして表示されます。

なお、外部テーブル定義についてはデフォルトだと文字長が最大でとられてしまうので、適宜変更しましょう。