CREATE EXTERNAL TABLE AS SELECT ステートメントを使用してデータファイルを変換する

5 分

SQL 言語には、データを操作できる多くの機能と関数が含まれています。たとえば、SQL を使用して次のことができます。

データセット内の行と列をフィルター処理します。
データフィールドの名前を変更し、データ型間で変換します。
派生データフィールドを計算します。
文字列値を操作します。
データをグループ化して集計します。

Azure Synapse サーバーレス SQL プールを使用して、データを変換し、結果をデータレイク内のファイルとして保持する SQL ステートメントを実行し、さらに処理またはクエリを実行できます。 Transact-SQL 構文に慣れている場合は、目的とする特定の変換を適用する SELECT ステートメントを作成し、SQL を使ってクエリを実行できるメタデータテーブルスキーマを使用して、SELECT ステートメントの結果を選択したファイル形式で格納することができます。

専用 SQL プールまたはサーバーレス SQL プールで CREATE EXTERNAL TABLE AS SELECT (CETAS) ステートメントを使用して、クエリの結果を外部テーブルに保持し、データレイク内のファイルにデータを格納できます。

CETAS ステートメントには、任意の有効なデータソース (データベース内の既存のテーブルまたはビューや、データレイクからファイルベースのデータを読み取る OPENROWSET 関数) にあるデータに対してクエリおよび操作を実行する SELECT ステートメントが含まれています。 SELECT ステートメントの結果は、ファイルに格納されたデータのリレーショナル抽象化を提供するデータベース内のメタデータオブジェクトである、外部テーブルに保持されます。次の図は、この概念を視覚的に示したものです。

A diagram showing a CREATE EXTERNAL TABLE AS SELECT statement saving query results as a file.

この手法を適用すると、SQL を使用してファイルまたはテーブルからデータを抽出して変換し、変換した結果を格納して、ダウンストリームの処理または分析を行うことができます。変換したデータに対する後続の操作は、SQL プールデータベース内のリレーショナルテーブルに対して実行することも、基になるデータファイルに対して直接実行することもできます。

CETAS をサポートする外部データベースオブジェクトの作成

CETAS 式を使用するには、サーバーレスまたは専用 SQL プールのデータベースに次の種類のオブジェクトを作成する必要があります。サーバーレス SQL プールを使用する場合は、組み込みデータベースではなく、カスタムデータベース (CREATE DATABASE ステートメントを使用して作成) にこれらのオブジェクトを作成します。

外部データソース

外部データソースを使用して、データレイク内のファイルシステムの場所への接続をカプセル化します。その後、この接続を使用して、CETAS ステートメントによって作成された外部テーブルのデータファイルを保存する相対パスを指定できます。

CETAS ステートメントのソースデータが同じデータレイクパス内のファイルにある場合、そのクエリに使用する OPENROWSET 関数内で同じ外部データソースを使用できます。または、ソースファイル用に個別の外部データソースを作成するか、OPENROWSET 関数で完全修飾ファイルパスを使用することもできます。

外部データソースを作成するには、次の例に示すように、CREATE EXTERNAL DATA SOURCE ステートメントを使用します。

-- Create an external data source for the Azure storage account
CREATE EXTERNAL DATA SOURCE files
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/files/',
    TYPE = HADOOP, -- For dedicated SQL pool
    -- TYPE = BLOB_STORAGE, -- For serverless SQL pool
    CREDENTIAL = storageCred
);

前の例では、外部データソースを使用するクエリを実行しているユーザーに、ファイルにアクセスするための十分なアクセス許可があることを前提としています。別の方法として、外部データソースに資格情報をカプセル化し、それを使用してファイルデータにアクセスできるようにする方法があり、すべてのユーザーに直接読み取るためのアクセス許可を付与せずに済みます。

CREATE DATABASE SCOPED CREDENTIAL storagekeycred
WITH
    IDENTITY='SHARED ACCESS SIGNATURE',  
    SECRET = 'sv=xxx...';

CREATE EXTERNAL DATA SOURCE secureFiles
WITH (
    LOCATION = 'https://mydatalake.blob.core.windows.net/data/secureFiles/'
    CREDENTIAL = storagekeycred
);

ヒント

SAS 認証に加えて、"マネージド ID" (Azure Synapse ワークスペースで使用される Microsoft Entra ID)、特定の Microsoft Entra プリンシパル、または、クエリを実行しているユーザーの ID に基づくパススルー認証を使用する (既定の認証タイプ) 資格情報を定義できます。サーバーレス SQL プールでの資格情報の使用の詳細については、Azure Synapse Analytics ドキュメントの「Azure Synapse Analytics でサーバーレス SQL プールのストレージアカウントアクセスを制御する」を参照してください。

外部ファイル形式

CETAS ステートメントにより、データがファイルに格納されたテーブルを作成します。外部ファイル形式として作成するファイルの形式を指定する必要があります。

外部ファイル形式を作成するには、次の例に示すように、CREATE EXTERNAL FILE FORMAT ステートメントを使用します。

CREATE EXTERNAL FILE FORMAT ParquetFormat
WITH (
        FORMAT_TYPE = PARQUET,
        DATA_COMPRESSION = 'org.apache.hadoop.io.compress.SnappyCodec'
    );

ヒント

この例では、ファイルは Parquet 形式で保存されます。その他の種類のファイルを表す外部ファイル形式を作成することもできます。詳細については、「CREATE EXTERNAL FILE FORMAT (Transact-SQL)」を参照してください。

CETAS ステートメントの使用

外部データソースと外部ファイル形式を作成した後、CETAS ステートメントを使用してデータを変換し、結果を外部テーブルに格納できます。

たとえば、変換するソースデータが、データレイク内のフォルダーに格納されたコンマ区切りテキストファイル内の販売注文で構成されているとします。 "特別注文" としてマークされている注文のみを含むようにデータをフィルター処理し、変換されたデータを Parquet ファイルとして同じデータレイク内の別のフォルダーに保存します。この例に示すように、ソースと宛先の両方のフォルダーに同じ外部データソースを使用できます。

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'sales_orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

前の例の LOCATION および BULK パラメーターは、それぞれ結果とソースのファイルの相対パスです。パスは、ファイル外部データソースによって参照されるファイルシステムの場所を基準にしています。

理解すべき重要な要点は、外部データソースを使用して、外部テーブルの変換されたデータを保存する場所を指定する "必要がある" ということです。ファイルベースのソースデータが同じフォルダー階層に格納されている場合は、同じ外部データソースを使用できます。それ以外の場合は、次の例に示すように、2 つ目のデータソースを使用してソースデータへの接続を定義するか、完全修飾パスを使用できます。

CREATE EXTERNAL TABLE SpecialOrders
    WITH (
        -- details for storing results
        LOCATION = 'special_orders/',
        DATA_SOURCE = files,
        FILE_FORMAT = ParquetFormat
    )
AS
SELECT OrderID, CustomerName, OrderTotal
FROM
    OPENROWSET(
        -- details for reading source files
        BULK 'https://mystorage.blob.core.windows.net/data/sales_orders/*.csv',
        FORMAT = 'CSV',
        PARSER_VERSION = '2.0',
        HEADER_ROW = TRUE
    ) AS source_data
WHERE OrderType = 'Special Order';

外部テーブルの削除

変換されたデータを含む外部テーブルが不要になった場合は、次に示すように、DROP EXTERNAL TABLE ステートメントを使用してデータベースから削除できます。

DROP EXTERNAL TABLE SpecialOrders;

ただし、外部テーブルは、実際のデータを含むファイルに対するメタデータ抽象化であることを理解しておくことが重要です。外部テーブルを削除しても、基になるファイルは削除 "されません"。

CREATE EXTERNAL TABLE AS SELECT ステートメントを使用してデータ ファイルを変換する

CETAS をサポートする外部データベース オブジェクトの作成