Synapse SQL プールのデータの読み込み戦略Data loading strategies for Synapse SQL pool

従来の SMP SQL プールでは、データの読み込みに抽出、変換、読み込み (ETL) プロセスが使用されています。Traditional SMP SQL pools use an Extract, Transform, and Load (ETL) process for loading data. Azure Synapse Analytics 内の Synapse SQL プールには、コンピューティング リソースとストレージ リソースのスケーラビリティと柔軟性を活用した超並列処理 (MPP) アーキテクチャがあります。Synapse SQL pool, within Azure Synapse Analytics, has a massively parallel processing (MPP) architecture that takes advantage of the scalability and flexibility of compute and storage resources.

抽出、読み込み、変換 (ELT) プロセスを使用すると、MPP を活用し、読み込み前にデータ変換に必要なリソースを排除できます。Using an Extract, Load, and Transform (ELT) process leverages MPP and eliminates the resources needed for data transformation prior to loading.

SQL プールでは、bcpSqlBulkCopy API などの一般的な SQL Server オプションを含む多くの読み込み方法がサポートされていますが、データを読み込むための最速かつ最もスケーラブルな方法は、PolyBase 外部テーブルと COPY ステートメント (プレビュー) を使用することです。While SQL pool supports many loading methods, including popular SQL Server options such as bcp and the SqlBulkCopy API, the fastest and most scalable way to load data is through PolyBase external tables and the COPY statement (preview).

PolyBase と COPY ステートメントを使用すると、Azure Blob Storage または Azure Data Lake Store に格納されている外部データに T-SQL 言語でアクセスできます。With PolyBase and the COPY statement, you can access external data stored in Azure Blob storage or Azure Data Lake Store via the T-SQL language. 読み込み時の柔軟性を最大限に高めるために、COPY ステートメントを使用することをお勧めします。For the most flexibility when loading, we recommend using the COPY statement.

注意

COPY ステートメントは、現在、パブリック プレビュー段階にあります。The COPY statement is currently in public preview. ご意見やご感想は、配布リストの sqldwcopypreview@service.microsoft.com までメールでお寄せください。To provide feedback, send email to the following distribution list: sqldwcopypreview@service.microsoft.com.

ELT とは?What is ELT?

ELT (抽出、読み込み、変換) とは、データがソース システムから抽出されて SQL プールに読み込まれ、その後変換されるプロセスです。Extract, Load, and Transform (ELT) is a process by which data is extracted from a source system, loaded into a SQL pool, and then transformed.

ELT を実装するための基本的な手順は次のとおりです。The basic steps for implementing ELT are:

  1. ソース データをテキスト ファイルに抽出します。Extract the source data into text files.
  2. そのデータを Azure Blob Storage または Azure Data Lake Store に配置します。Land the data into Azure Blob storage or Azure Data Lake Store.
  3. 読み込むデータを準備します。Prepare the data for loading.
  4. PolyBase または COPY コマンドを使用して、ステージング テーブルにデータを読み込みます。Load the data into staging tables with PolyBase or the COPY command.
  5. データを変換します。Transform the data.
  6. 運用環境テーブルにデータを挿入します。Insert the data into production tables.

読み込みのチュートリアルについては、 Azure Blob Storage からのデータの読み込みに関する記事をご覧ください。For a loading tutorial, see loading data from Azure blob storage.

1.ソース データをテキスト ファイルに抽出する1. Extract the source data into text files

ソース システムからのデータの取得方法は、保存場所によって異なります。Getting data out of your source system depends on the storage location. 目標は、サポートされている区切りテキストまたは CSV ファイルにデータを移動することです。The goal is to move the data into supported delimited text or CSV files.

サポートされるファイル形式Supported file formats

PolyBase および COPY ステートメントを使用すると、UTF-8 および UTF-16 でエンコードされた区切りテキストまたは CSV ファイルから、データを読み込むことができます。With PolyBase and the COPY statement, you can load data from UTF-8 and UTF-16 encoded delimited text or CSV files. 区切りテキストまたは CSV ファイルに加え、ORC や Parquet などの Hadoop ファイル形式からも読み込みます。In addition to delimited text or CSV files, it loads from the Hadoop file formats such as ORC and Parquet. また、PolyBase および COPY ステートメントは、Gzip および Snappy 圧縮ファイルからもデータを読み込むことができます。PolyBase and the COPY statement can also load data from Gzip and Snappy compressed files.

拡張 ASCII、固定幅形式、および WinZip や XML などの入れ子形式は、サポートされていません。Extended ASCII, fixed-width format, and nested formats such as WinZip or XML aren't supported. SQL Server からエクスポートする場合は、bcp コマンドライン ツールを使用して、区切りテキスト ファイルにデータをエクスポートすることができます。If you're exporting from SQL Server, you can use the bcp command-line tool to export the data into delimited text files.

2.データを Azure Blob Storage または Azure Data Lake Store に配置する2. Land the data into Azure Blob storage or Azure Data Lake Store

Azure Storage にデータを配置するには、Azure Blob Storage または Azure Data Lake Store Gen2 にデータを移動します。To land the data in Azure storage, you can move it to Azure Blob storage or Azure Data Lake Store Gen2. どちらの場合も、データはテキスト ファイルに格納されている必要があります。In either location, the data should be stored in text files. PolyBase および COPY ステートメントは、どちらの場所からも読み込むことができます。PolyBase and the COPY statement can load from either location.

Azure Storage へのデータの移動で使用できるツールやサービスは、次のとおりです。Tools and services you can use to move data to Azure Storage:

  • Azure ExpressRoute サービス - ネットワークのスループット、パフォーマンス、予測可能性を向上させます。Azure ExpressRoute service enhances network throughput, performance, and predictability. ExpressRoute は、専用プライベート接続を通してデータを Azure にルーティングするサービスです。ExpressRoute is a service that routes your data through a dedicated private connection to Azure. ExpressRoute 接続では、パブリック インターネットを通してデータをルーティングすることはありません。ExpressRoute connections do not route data through the public internet. ExpressRoute 接続は、パブリック インターネットを通る一般的な接続に比べて安全性と信頼性が高く、待機時間も短く、高速です。The connections offer more reliability, faster speeds, lower latencies, and higher security than typical connections over the public internet.
  • AZCopy ユーティリティ - パブリック インターネットを通してデータを Azure Storage に移動します。AZCopy utility moves data to Azure Storage over the public internet. このユーティリティは、データ サイズが 10 TB より小さい場合に機能します。This works if your data sizes are less than 10 TB. AZCopy を使用して読み込みを定期的に実行するには、ネットワーク速度をテストして、許容可能かどうかを確認してください。To perform loads on a regular basis with AZCopy, test the network speed to see if it is acceptable.
  • Azure Data Factory (ADF) - ゲートウェイをローカル サーバーにインストールできます。Azure Data Factory (ADF) has a gateway that you can install on your local server. その後、ローカル サーバーから Azure Storage にデータを移動するためのパイプラインを作成できます。Then you can create a pipeline to move data from your local server up to Azure Storage. SQL プールで Data Factory を使用する方法については、SQL プールのデータの読み込みに関するページを参照してください。To use Data Factory with SQL pool, see Loading data for SQL pool.

3.読み込むデータを準備する3. Prepare the data for loading

読み込む前に、ストレージ アカウントのデータを準備してクリーンアップすることが必要になる場合があります。You might need to prepare and clean the data in your storage account before loading. データの準備は、データがソース内にあるとき、データをテキスト ファイルにエクスポートするとき、またはデータが Azure Storage に配置された後に実施できます。Data preparation can be performed while your data is in the source, as you export the data to text files, or after the data is in Azure Storage. データの操作は、プロセスの早い段階の方が、最も簡単に行えます。It is easiest to work with the data as early in the process as possible.

テーブルを定義するDefine the tables

COPY ステートメントを使用する場合は、最初に SQL プールで読み込み先のテーブルを定義する必要があります。You must first defined the table(s) you are loading to in your SQL pool when using the COPY statement.

PolyBase を使用している場合は、読み込み前に、SQL プールに外部テーブルを定義する必要があります。If you are using PolyBase, you need to define external tables in your SQL pool before loading. PolyBase は、外部テーブルを使用して Azure Storage のデータを定義し、それにアクセスします。PolyBase uses external tables to define and access the data in Azure Storage. 外部テーブルは、データベースのビューに似ています。An external table is similar to a database view. 外部テーブルにはテーブル スキーマが含まれており、SQL プールの外部に格納されているデータを指します。The external table contains the table schema and points to data that is stored outside the SQL pool.

外部テーブルを定義するには、データ ソース、テキスト ファイルの形式、テーブル定義を指定する必要があります。Defining external tables involves specifying the data source, the format of the text files, and the table definitions. 必要な T-SQL 構文の参照記事は次のとおりです。T-SQL syntax reference articles that you will need are:

Parquet ファイルを読み込む場合、次の SQL データ型マッピングを使用します。Use the following SQL data type mapping when loading Parquet files:

Parquet 型Parquet type Parquet 論理型 (注釈)Parquet logical type (annotation) SQL データ型SQL data type
BOOLEANBOOLEAN bitbit
BINARY / BYTE_ARRAYBINARY / BYTE_ARRAY varbinaryvarbinary
DOUBLEDOUBLE floatfloat
FLOATFLOAT realreal
INT32INT32 INTint
INT64INT64 bigintbigint
INT96INT96 datetime2datetime2
FIXED_LEN_BYTE_ARRAYFIXED_LEN_BYTE_ARRAY binarybinary
BINARYBINARY UTF8UTF8 nvarcharnvarchar
BINARYBINARY STRINGSTRING nvarcharnvarchar
BINARYBINARY ENUMENUM nvarcharnvarchar
BINARYBINARY UUIDUUID UNIQUEIDENTIFIERuniqueidentifier
BINARYBINARY DECIMALDECIMAL decimaldecimal
BINARYBINARY JSONJSON nvarchar(MAX)nvarchar(MAX)
BINARYBINARY BSONBSON varbinary(max)varbinary(max)
FIXED_LEN_BYTE_ARRAYFIXED_LEN_BYTE_ARRAY DECIMALDECIMAL decimaldecimal
BYTE_ARRAYBYTE_ARRAY INTERVALINTERVAL varchar(max)varchar(max),
INT32INT32 INT(8, true)INT(8, true) smallintsmallint
INT32INT32 INT(16, true)INT(16, true) smallintsmallint
INT32INT32 INT(32, true)INT(32, true) INTint
INT32INT32 INT(8, false)INT(8, false) tinyinttinyint
INT32INT32 INT(16, false)INT(16, false) INTint
INT32INT32 INT(32, false)INT(32, false) bigintbigint
INT32INT32 DATEDATE datedate
INT32INT32 DECIMALDECIMAL decimaldecimal
INT32INT32 TIME (MILLIS)TIME (MILLIS ) timetime
INT64INT64 INT(64, true)INT(64, true) bigintbigint
INT64INT64 INT(64, false)INT(64, false ) decimal (20,0)decimal(20,0)
INT64INT64 DECIMALDECIMAL decimaldecimal
INT64INT64 TIME (MICROS / NANOS)TIME (MICROS / NANOS) timetime
INT64INT64 TIMESTAMP (MILLIS / MICROS / NANOS)TIMESTAMP (MILLIS / MICROS / NANOS) datetime2datetime2
複合型Complex type リストLIST varchar(max)varchar(max)
複合型Complex type MAPMAP varchar(max)varchar(max)

外部オブジェクトの作成の例については、外部テーブルの作成に関する記事を参照してください。For an example of creating external objects, see Create external tables.

テキスト ファイルの書式設定Format text files

PolyBase を使用する場合、定義する外部オブジェクトは、外部テーブルおよびファイル形式の定義に合わせて、テキスト ファイルの行を配置する必要があります。If you are using PolyBase, the external objects defined need to align the rows of the text files with the external table and file format definition. テキスト ファイルの各行のデータは、テーブル定義と一致させる必要があります。The data in each row of the text file must align with the table definition. テキスト ファイルを書式設定するには、次の処理を行います。To format the text files:

  • データが非リレーショナル ソースから読み込まれる場合は、データを行と列に変換する必要があります。If your data is coming from a non-relational source, you need to transform it into rows and columns. データの読み込み元がリレーショナル ソースの場合も、非リレーショナル ソースの場合も、データを読み込むテーブルの列定義に合わせてデータを変換する必要があります。Whether the data is from a relational or non-relational source, the data must be transformed to align with the column definitions for the table into which you plan to load the data.
  • 変換先テーブルの列とデータ型に合わせて、テキスト ファイルのデータを書式設定します。Format data in the text file to align with the columns and data types in the destination table. 外部テキスト ファイルと SQL プール テーブルの間でデータ型の不整合があると、読み込みの際に行が拒否されます。Misalignment between data types in the external text files and the SQL pool table causes rows to be rejected during the load.
  • テキスト ファイル内のフィールドは終端記号で区切ります。Separate fields in the text file with a terminator. ソース データに含まれていない文字または文字シーケンスを使用するようにしてください。Be sure to use a character or a character sequence that isn't found in your source data. CREATE EXTERNAL FILE FORMAT で指定した終端記号を使用します。Use the terminator you specified with CREATE EXTERNAL FILE FORMAT.

4.PolyBase または COPY ステートメントを使用してデータを読み込む4. Load the data using PolyBase or the COPY statement

これがステージング テーブルにデータを読み込むための最善の方法です。It is best practice to load data into a staging table. ステージング テーブルを使用すると、運用環境のテーブルに支障をきたすことなく、エラーを処理することができます。Staging tables allow you to handle errors without interfering with the production tables. また、ステージング テーブルを使用すれば、運用環境のテーブルにデータを挿入する前に、SQL プールの並列処理アーキテクチャを使用してデータを変換することができます。A staging table also gives you the opportunity to use the SQL pool parallel processing architecture for data transformations before inserting the data into production tables.

読み込みのオプションOptions for loading

データを読み込むには、次のいずれかの読み込みオプションを使用できます。To load data, you can use any of these loading options:

  • COPY ステートメントは、シームレスかつ柔軟にデータを読み込むことができるため、推奨されている読み込みユーティリティです。The COPY statement is the recommended loading utility as it enables you to seamlessly and flexibly load data. このステートメントには、PolyBase では提供されない追加の読み込み機能が多数あります。The statement has many additional loading capabilities that PolyBase does not provide.
  • T-SQL を使用したPolyBase では、外部データ オブジェクトを定義する必要があります。PolyBase with T-SQL requires you to define external data objects.
  • Azure Data Factory (ADF) を使用した PolyBase および COPY ステートメント - もう 1 つのオーケストレーション ツールです。PolyBase and COPY statement with Azure Data Factory (ADF) is another orchestration tool. このツールはパイプラインを定義し、ジョブのスケジュールを設定します。It defines a pipeline and schedules jobs.
  • SSIS を使用した PolyBase は、ソースデータが SQL Server にある場合に適しています。PolyBase with SSIS works well when your source data is in SQL Server. SSIS は、移動元テーブルと移動先テーブルのマッピングを定義するほか、読み込みの調整も行います。SSIS defines the source to destination table mappings, and also orchestrates the load. SSIS パッケージが既にある場合、そのパッケージが移動先の新しいデータ ウェアハウスで機能するように変更できます。If you already have SSIS packages, you can modify the packages to work with the new data warehouse destination.
  • Azure Databricks を使用した PolyBase - PolyBase を使用して、テーブルから Databricks データ フレームにデータを転送することや、Databricks データ フレームからテーブルにデータを書き込むことができます。PolyBase with Azure Databricks transfers data from a table to a Databricks dataframe and/or writes data from a Databricks dataframe to a table using PolyBase.

その他の読み込みオプションOther loading options

PolyBase と COPY ステートメントの他に、bcp または SqlBulkCopy API を使用できます。In addition to PolyBase and the COPY statement, you can use bcp or the SqlBulkCopy API. bcp では、Azure Blob Storage を通さずにデータベースに直接読み込みます。また、小規模の読み込みのみを対象とします。bcp loads directly to the database without going through Azure Blob storage, and is intended only for small loads.

注意

これらのオプションの読み込みパフォーマンスは、PolyBase および COPY ステートメントと比べて低速です。The load performance of these options is slower than PolyBase and the COPY statement.

5.データの変換5. Transform the data

データがステージング テーブルにある間に、ワークロードに必要な変換を実行します。While data is in the staging table, perform transformations that your workload requires. その後、運用環境テーブルにデータを移動します。Then move the data into a production table.

6.運用環境テーブルにデータを挿入する6. Insert the data into production tables

INSERT INTO ...SELECT ステートメントを実行すると、データがステージング テーブルから永続テーブルに移動します。The INSERT INTO ... SELECT statement moves the data from the staging table to the permanent table.

ETL プロセスを設計する際は、小規模のテスト サンプルでプロセスを実行してみてください。As you design an ETL process, try running the process on a small test sample. テーブルから 1 つのファイルに 1000 行を抽出し、Azure に移動してから、ステージング テーブルに読み込んでみてください。Try extracting 1000 rows from the table to a file, move it to Azure, and then try loading it into a staging table.

パートナー読み込みソリューションPartner loading solutions

パートナーの多くが読み込みソリューションを提供しています。Many of our partners have loading solutions. 詳細については、ソリューション パートナーの一覧をご覧ください。To find out more, see a list of our solution partners.

次のステップNext steps

読み込みのガイダンスについては、データの読み込みのガイダンスに関するページを参照してください。For loading guidance, see Guidance for loading data.