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

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

データベースの作成

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/ ファイルという名前のデータ ソースを作成します。

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 パラメーターを使用して、 orders フォルダー内のすべての .csv ファイルの相対パスを指定します。これは、データ ソースによって参照される ファイル フォルダーのサブフォルダーです。

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

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

外部ファイル形式の作成

外部データ ソースを使用すると、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 クエリ セマンティクスを使用してデータに簡単にアクセスできるようになります。