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

6 分

OPENROWSET 関数は、組み込みのサーバーレス SQL プールの既定のマスター データベースで実行される SQL クエリで使用して、データレイク内のデータを探すことができます。ただし、頻繁にクエリを実行する必要があるデータレイク内の外部データの操作を容易にするオブジェクトを含むカスタムデータベースを作成したい場合があります。

データベースの作成

SQL Server インスタンスの場合と同様に、サーバーレス SQL プールにデータベースを作成できます。グラフィカルインターフェイスは、Synapse Studio または CREATE DATABASE ステートメントで使用できます。 1 つの考慮事項は、ファイル内のテキストデータから適切な Transact-SQL データ型への変換をサポートするように、データベースの照合順序を設定することです。

次のコード例では、UTF-8 でエンコードされたテキストデータを VARCHAR 列にインポートしやすくする照合順序を持つ salesDB という名前のデータベースを作成します。

CREATE DATABASE SalesDB
    COLLATE Latin1_General_100_BIN2_UTF8

外部データソースを作成する

OPENROWSET 関数を BULK パスと共に使用して、マスター データベースの場合と同様に、独自のデータベースからファイルデータを照会できます。ただし、同じ場所のデータに頻繁にクエリを実行する場合は、その場所を参照する外部データソースを定義する方が効率的です。たとえば、次のコードでは、架空の https://mydatalake.blob.core.windows.net/data/files/ フォルダーの files という名前のデータソースを作成します。

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

外部データソースの利点の 1 つは、OPENROWSET クエリを簡略化して、データソースと、クエリを実行するフォルダーまたはファイルへの相対パスの組み合わせを使用できることです。

SELECT *
FROM
    OPENROWSET(
        BULK 'orders/*.csv',
        DATA_SOURCE = 'files',
        FORMAT = 'csv',
        PARSER_VERSION = '2.0'
    ) AS orders

この例では、BULK パラメーターを使用して、データソースによって参照される files フォルダーのサブフォルダーである orders フォルダー内のすべての.csv ファイルの相対パスを指定します。

データソースを使用するもう 1 つの利点は、基になるストレージにアクセスするときに使用するデータソースの資格情報を割り当てることで、ユーザーがストレージアカウント内のデータに直接アクセスすることを許可することなく、SQL を介してデータへのアクセスを提供できることです。たとえば、次のコードでは、データレイクをホストする基になる Azure ストレージアカウントに対して、Shared Access Signature (SAS) を使用して認証を行う資格情報を作成します。

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

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

ヒント

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

外部ファイル形式を作成する

外部データソースは OPENROWSET 関数を使用してファイルにアクセスするために必要なコードを簡略化しますが、アクセスするファイルの形式の詳細を指定する必要があります。これには、区切りテキストファイルの複数の設定を含めることができます。次のように、外部ファイル形式でこれらの設定をカプセル化できます。

CREATE EXTERNAL FILE FORMAT CsvFormat
    WITH (
        FORMAT_TYPE = DELIMITEDTEXT,
        FORMAT_OPTIONS(
            FIELD_TERMINATOR = ',',
            STRING_DELIMITER = '"'
        )
    );
GO

作業する必要がある特定のデータファイルのファイル形式を作成した後、次に説明するように、ファイル形式を使用して外部テーブルを作成できます。

外部のテーブルの作成

データレイク内のファイルから大量の分析やレポートを実行する必要がある場合は、OPENROWSET 関数を使用すると、データソースとファイルパスを含む複雑なコードが作成される場合があります。データへのアクセスを簡略化するために、ユーザーとレポートアプリケーションが他のデータベーステーブルと同様に標準の SQL SELECT ステートメントを使用してクエリを実行できるテーブルに、ファイルをカプセル化することができます。外部テーブルを作成するには、CREATE EXTERNAL TABLE ステートメントを使用し、標準テーブルと同様に列スキーマを指定し、データの外部データソース、相対パス、外部ファイル形式を指定する WITH 句を含めます。

CREATE EXTERNAL TABLE dbo.products
(
    product_id INT,
    product_name VARCHAR(20),
    list_price DECIMAL(5,2)
)
WITH
(
    DATA_SOURCE = files,
    LOCATION = 'products/*.csv',
    FILE_FORMAT = CsvFormat
);
GO

-- query the table
SELECT * FROM dbo.products;

このユニットで説明する外部オブジェクトを含むデータベースを作成することで、データレイク内のファイルに対してリレーショナルデータベースレイヤーを提供できるため、多くのデータアナリストやレポートツールが標準の SQL クエリセマンティクスを使用してデータに簡単にアクセスできるようになります。

続行

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

データベースの作成

外部データ ソースを作成する

外部ファイル形式を作成する

外部のテーブルの作成

フィードバック

外部データソースを作成する