テーブルとして選択 (Azure SQL データ ウェアハウス) を作成します。CREATE TABLE AS SELECT (Azure SQL Data Warehouse)

適用対象: XSQL Server XAzure SQL Database○Azure SQL Data Warehouse ○Parallel Data Warehouse THIS TOPIC APPLIES TO: noSQL Server noAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

作成テーブルとして選択 (CTAS) は、使用可能な最も重要な T-SQL 機能の 1 つです。CREATE TABLE AS SELECT (CTAS) is one of the most important T-SQL features available. SELECT ステートメントの出力に基づいた新しいテーブルを作成する完全な並列化された操作です。It is a fully parallelized operation that creates a new table based on the output of a SELECT statement. CTAS は、テーブルのコピーを作成する最も簡単で高速な方法です。CTAS is the simplest and fastest way to create a copy of a table.

たとえばに CTAS を使用します。For example, use CTAS to:

  • 異なるハッシュ ディストリビューション列を含むテーブルを再作成します。Re-create a table with a different hash distribution column.
  • レプリケートされると、テーブルを再作成します。Re-create a table as replicated.
  • テーブル内の列の一部では、列ストア インデックスを作成します。Create a columnstore index on just some of the columns in the table.
  • クエリを実行または外部データをインポートします。Query or import external data.

注意

CTAS は、テーブルを作成するの機能に追加するためこのトピックはテーブルの作成に関するトピックを繰り返さない試行します。Since CTAS adds to the capabilities of creating a table, this topic tries not to repeat the CREATE TABLE topic. 代わりに、CTAS ステートメントと CREATE TABLE ステートメントの違いについて説明します。Instead, it describes the differences between the CTAS and CREATE TABLE statements. 詳細については、CREATE TABLE、次を参照してください。 CREATE TABLE (Azure SQL Data Warehouse)ステートメントです。For the CREATE TABLE details, see CREATE TABLE (Azure SQL Data Warehouse) statement.

トピック リンク アイコン Transact-SQL 構文表記規則Topic link icon Transact-SQL Syntax Conventions

構文Syntax

CREATE TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name   
    [ ( column_name [ ,...n ] ) ]  
    WITH ( 
      <distribution_option> -- required
      [ , <table_option> [ ,...n ] ]    
    )  
    AS <select_statement>   
[;]  

<distribution_option> ::=
    { 
        DISTRIBUTION = HASH ( distribution_column_name ) 
      | DISTRIBUTION = ROUND_ROBIN 
      | DISTRIBUTION = REPLICATE
    }   

<table_option> ::= 
    {   
        CLUSTERED COLUMNSTORE INDEX --default for SQL Data Warehouse 
      | HEAP --default for Parallel Data Warehouse   
      | CLUSTERED INDEX ( { index_column_name [ ASC | DESC ] } [ ,...n ] ) --default is ASC 
    }  
    | PARTITION ( partition_column_name RANGE [ LEFT | RIGHT ] --default is LEFT  
        FOR VALUES ( [ boundary_value [,...n] ] ) ) 

<select_statement> ::=  
    [ WITH <common_table_expression> [ ,...n ] ]  
    SELECT select_criteria  

引数Arguments

詳細については、次を参照してください。、 「引数」セクションCREATE TABLE でします。For details, see the Arguments section in CREATE TABLE.

列のオプションColumn options

column_name [ ,...n ]column_name [ ,...n ]
列名が許可しない、列オプションテーブルの作成に記載します。Column names do not allow the column options mentioned in CREATE TABLE. 代わりに、新しいテーブルの 1 つまたは複数の列名のオプションのリストを指定できます。Instead, you can provide an optional list of one or more column names for the new table. 新しいテーブルの列を指定する名前が使用されます。The columns in the new table will use the names you specify. 列名を指定すると、列リスト内の列の数は、select の結果内の列の数と一致する必要があります。When you specify column names, the number of columns in the column list must match the number of columns in the select results. 任意の列名を指定しない場合、新しいターゲット テーブルは、select ステートメントの結果で、列名を使用します。If you don't specify any column names, the new target table will use the column names in the select statement results.

データ型、照合順序、または null 値許容属性などの列のオプションを指定することはできません。You cannot specify any other column options such as data types, collation, or nullability. これらの各属性の結果から派生したが、SELECTステートメントです。Each of these attributes is derived from the results of the SELECT statement. ただし、SELECT ステートメントを使用して、属性を変更することができます。However, you can use the SELECT statement to change the attributes. 例については、次を参照してください。列属性を変更する使用 CTASです。For an example, see Use CTAS to change column attributes.

テーブルの配布オプションTable distribution options

DISTRIBUTION = HASH( distribution_column_name ) |ROUND_ROBIN |レプリケートDISTRIBUTION = HASH ( distribution_column_name ) | ROUND_ROBIN | REPLICATE
CTAS ステートメントは、配布オプションが必要ですし、既定値はありません。The CTAS statement requires a distribution option and does not have default values. これは、既定値を持つテーブルの作成と異なります。This is different from CREATE TABLE which has defaults.

詳細については、および最適なディストリビューション列を選択する方法を理解するには、「、テーブル配布オプションテーブルの作成」セクション。For details and to understand how to choose the best distribution column, see the Table distribution options section in CREATE TABLE.

テーブル パーティションのオプションTable partition options

CTAS ステートメントは、ソース テーブルがパーティション分割されている場合でも、既定では、非パーティション テーブルを作成します。The CTAS statement creates a non-partitioned table by default, even if the source table is partitioned. CTAS ステートメントを使用して、パーティション テーブルを作成するには、パーティション オプションを指定する必要があります。To create a partitioned table with the CTAS statement, you must specify the partition option.

詳細については、次を参照してください。、テーブル パーティション オプションテーブルの作成」セクション。For details, see the Table partition options section in CREATE TABLE.

オプションを選択しますSelect options

Select ステートメントは、CTAS と CREATE TABLE の基本的な違いです。The select statement is the fundamental difference between CTAS and CREATE TABLE.

WITHcommon_table_expressionWITH common_table_expression
共通テーブル式 (CTE) と呼ばれる一時的な名前付き結果セットを指定します。Specifies a temporary named result set, known as a common table expression (CTE). 詳細については、次を参照してください。で common_table_expression と #40 です。TRANSACT-SQL と #41 です。.For more information, see WITH common_table_expression (Transact-SQL).

SELECTselect_criteriaSELECT select_criteria
SELECT ステートメントの結果を新しいテーブルを追加します。Populates the new table with the results from a SELECT statement. select_criteria新しいテーブルにコピーするデータを決定する SELECT ステートメントの本文です。select_criteria is the body of the SELECT statement that determines which data to copy to the new table. SELECT ステートメントの概要については、次を参照してくださいSELECT (。TRANSACT-SQL と #41 です。.For information about SELECT statements, see SELECT (Transact-SQL).

PermissionsPermissions

CTAS 必要SELECTで参照されているすべてのオブジェクトに対する権限、 select_criteriaです。CTAS requires SELECT permission on any objects referenced in the select_criteria.

テーブルを作成するアクセス許可は、次を参照してください。権限CREATE TABLE でします。For permissions to create a table, see Permissions in CREATE TABLE.

全般的な解説General Remarks

詳細については、「全般的な解説CREATE TABLE でします。For details, see General Remarks in CREATE TABLE.

制限事項と制約事項Limitations and Restrictions

Azure SQL Data Warehouse では、サポートの自動作成] または [統計の更新を自動まだされていません。Azure SQL Data Warehouse does not yet support auto create or auto update statistics. クエリから最高のパフォーマンスを取得するためには、CTAS を実行した後、およびデータに大幅な変更が加えられた後は、統計をすべてのテーブルのすべての列を作成する必要があります。In order to get the best performance from your queries, it's important to create statistics on all columns of all tables after you run CTAS and after any substantial changes occur in the data. 詳細については、「 CREATE STATISTICS (Transact-SQL)」をご覧ください。For more information, see CREATE STATISTICS (Transact-SQL).

SET ROWCOUNT と #40 です。TRANSACT-SQL と #41 です。 CTAS で影響を与えません。SET ROWCOUNT (Transact-SQL) has no effect on CTAS. 同様の動作を実現するために使用TOP & #40 です。TRANSACT-SQL と #41 です。.To achieve a similar behavior, use TOP (Transact-SQL).

詳細については、「制限事項と制約CREATE TABLE でします。For details, see Limitations and Restrictions in CREATE TABLE.

ロック動作Locking Behavior

詳細については、「ロック動作CREATE TABLE でします。For details, see Locking Behavior in CREATE TABLE.

パフォーマンスPerformance

ハッシュ分散のテーブルの結合と集計のパフォーマンスの向上を実現するために別の配布の列を選択するのに CTAS を使用することができます。For a hash-distributed table, you can use CTAS to choose a different distribution column to achieve better performance for joins and aggregations. 異なるディストリビューション列が目標ではないを選択する場合は、行を再配布これを回避するために、同じディストリビューション列を指定する場合 CTAS パフォーマンスを最適になります。If choosing a different distribution column is not your goal, you will have the best CTAS performance if you specify the same distribution column since this will avoid re-distributing the rows.

指定するかどうかは、CTAS を使用してテーブルを作成して、パフォーマンスは、要素ではありません、ROUND_ROBINディストリビューション列を決定するしなくても済むようにします。If you are using CTAS to create table and performance is not a factor, you can specify ROUND_ROBIN to avoid having to decide on a distribution column.

後続のクエリでデータ移動を避けるためを指定できますREPLICATEが増加する記憶域をすべての計算ノード上のテーブルの完全なコピーを読み込むためが欠点です。To avoid data movement in subsequent queries, you can specify REPLICATE at the cost of increased storage for loading a full copy of the table on each Compute node.

テーブルをコピーするための例Examples for copying a table

A.A. CTAS を使用してテーブルをコピーするにはUse CTAS to copy a table

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

などの最も一般的なのいずれかを使用してCTASDDL を変更することができるように、テーブルのコピーの作成です。Perhaps one of the most common uses of CTAS is creating a copy of a table so that you can change the DDL. たとえば作成していた場合、テーブルとしてROUND_ROBINとしますが、列の分散テーブルを変更しましょうCTASディストリビューション列を変更する方法は、します。If for example you originally created your table as ROUND_ROBIN and now want change it to a table distributed on a column, CTAS is how you would change the distribution column. CTASパーティション分割、インデックス、または列の型を変更するも使用できます。CTAS can also be used to change partitioning, indexing, or column types.

既定値の分布タイプを使用してこのテーブルを作成したとしましょうROUND_ROBIN分散でディストリビューション列が指定されなかったので、CREATE TABLEです。Let's say you created this table using the default distribution type of ROUND_ROBIN distributed since no distribution column was specified in the CREATE TABLE.

CREATE TABLE FactInternetSales
(
    ProductKey int NOT NULL,
    OrderDateKey int NOT NULL,
    DueDateKey int NOT NULL,
    ShipDateKey int NOT NULL,
    CustomerKey int NOT NULL,
    PromotionKey int NOT NULL,
    CurrencyKey int NOT NULL,
    SalesTerritoryKey int NOT NULL,
    SalesOrderNumber nvarchar(20) NOT NULL,
    SalesOrderLineNumber tinyint NOT NULL,
    RevisionNumber tinyint NOT NULL,
    OrderQuantity smallint NOT NULL,
    UnitPrice money NOT NULL,
    ExtendedAmount money NOT NULL,
    UnitPriceDiscountPct float NOT NULL,
    DiscountAmount float NOT NULL,
    ProductStandardCost money NOT NULL,
    TotalProductCost money NOT NULL,
    SalesAmount money NOT NULL,
    TaxAmt money NOT NULL,
    Freight money NOT NULL,
    CarrierTrackingNumber nvarchar(25),
    CustomerPONumber nvarchar(25)
);

クラスター化列ストア テーブルのパフォーマンスの利用できるように、クラスター化列ストア インデックスを持つこのテーブルの新しいコピーを作成するようになりました。Now you want to create a new copy of this table with a clustered columnstore index so that you can take advantage of the performance of clustered columnstore tables. また、この列に結合を予測するために、ProductKey でこのテーブルを配布するし、ProductKey 上の結合時にデータの移動を回避します。You also want to distribute this table on ProductKey since you are anticipating joins on this column and want to avoid data movement during joins on ProductKey. 最後にも追加する OrderDateKey パーティション分割できるように、古いパーティションを削除することにより簡単に古いデータを削除できます。Lastly you also want to add partitioning on OrderDateKey so that you can quickly delete old data by dropping old partitions. 新しいテーブルに、古いテーブルのコピーは、CTAS ステートメントを次に示します。Here is the CTAS statement which would copy your old table into a new table.

CREATE TABLE FactInternetSales_new
WITH
(
    CLUSTERED COLUMNSTORE INDEX,
    DISTRIBUTION = HASH(ProductKey),
    PARTITION
    (
        OrderDateKey RANGE RIGHT FOR VALUES
        (
        20000101,20010101,20020101,20030101,20040101,20050101,20060101,20070101,20080101,20090101,
        20100101,20110101,20120101,20130101,20140101,20150101,20160101,20170101,20180101,20190101,
        20200101,20210101,20220101,20230101,20240101,20250101,20260101,20270101,20280101,20290101
        )
    )
)
AS SELECT * FROM FactInternetSales;

最後に、新しいテーブルでスワップしてから、古いテーブルを削除するテーブルの名前を変更することができます。Finally you can rename your tables to swap in your new table and then drop your old table.

RENAME OBJECT FactInternetSales TO FactInternetSales_old;
RENAME OBJECT FactInternetSales_new TO FactInternetSales;

DROP TABLE FactInternetSales_old;

列のオプションの例Examples for column options

B.B. CTAS を使用して、列属性を変更するにはUse CTAS to change column attributes

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

この例では、CTAS を使用して、データ型、null 値許容属性、および DimCustomer2 テーブル内の複数の列の照合順序を変更します。This example uses CTAS to change data types, nullability, and collation for several columns in the DimCustomer2 table.

-- Original table 
CREATE TABLE [dbo].[DimCustomer2] (  
    [CustomerKey] int NOT NULL,  
    [GeographyKey] int NULL,  
    [CustomerAlternateKey] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL  
)  
WITH (CLUSTERED COLUMNSTORE INDEX, DISTRIBUTION = HASH([CustomerKey]));  

-- CTAS example to change data types, nullability, and column collations  
CREATE TABLE test  
WITH (HEAP, DISTRIBUTION = ROUND_ROBIN)  
AS  
SELECT  
    CustomerKey AS CustomerKeyNoChange,  
    CustomerKey*1 AS CustomerKeyChangeNullable,  
    CAST(CustomerKey AS DECIMAL(10,2)) AS CustomerKeyChangeDataTypeNullable,  
    ISNULL(CAST(CustomerKey AS DECIMAL(10,2)),0) AS CustomerKeyChangeDataTypeNotNullable,  
    GeographyKey AS GeographyKeyNoChange,  
    ISNULL(GeographyKey,0) AS GeographyKeyChangeNotNullable,  
    CustomerAlternateKey AS CustomerAlternateKeyNoChange,  
    CASE WHEN CustomerAlternateKey = CustomerAlternateKey 
        THEN CustomerAlternateKey END AS CustomerAlternateKeyNullable,  
    CustomerAlternateKey COLLATE Latin1_General_CS_AS_KS_WS AS CustomerAlternateKeyChangeCollation  
FROM [dbo].[DimCustomer2]  

-- Resulting table 
CREATE TABLE [dbo].[test] (
    [CustomerKeyNoChange] int NOT NULL, 
    [CustomerKeyChangeNullable] int NULL, 
    [CustomerKeyChangeDataTypeNullable] decimal(10, 2) NULL, 
    [CustomerKeyChangeDataTypeNotNullable] decimal(10, 2) NOT NULL, 
    [GeographyKeyNoChange] int NULL, 
    [GeographyKeyChangeNotNullable] int NOT NULL, 
    [CustomerAlternateKeyNoChange] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, 
    [CustomerAlternateKeyNullable] nvarchar(15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
    [CustomerAlternateKeyChangeCollation] nvarchar(15) COLLATE Latin1_General_CS_AS_KS_WS NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN);

最後の手順として行うこともできます名前の変更 & #40 です。TRANSACT-SQL と #41 です。テーブル名を切り替えるには。As a final step, you can use RENAME (Transact-SQL) to switch the table names. これにより、新しいテーブルを指定する DimCustomer2 です。This makes DimCustomer2 be the new table.

RENAME OBJECT DimCustomer2 TO DimCustomer2_old;
RENAME OBJECT test TO DimCustomer2;

DROP TABLE DimCustomer2_old;

テーブルの配布の例Examples for table distribution

C.C. CTAS を使用して、テーブルの配布方法を変更するにはUse CTAS to change the distribution method for a table

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

この簡単な例は、テーブルの配布方法を変更する方法を示しています。This simple example shows how to change the distribution method for a table. これを行う方法のしくみを表示するには、ラウンド ロビンをハッシュ分散テーブルが変更され、分散ハッシュに戻るラウンド ロビン テーブルを変更します。To show the mechanics of how to do this, it changes a hash-distributed table to round-robin and then changes the round-robin table back to hash distributed. 最終的なテーブルでは、元のテーブルと一致します。The final table matches the original table.

ほとんどの場合は、ラウンド ロビン テーブルにハッシュ分散テーブルを変更する必要はありません。In most cases you won't need to change a hash-distributed table to a round-robin table. 多くの場合、ハッシュ分散テーブルにラウンド ロビン テーブルを変更する必要があります。More often, you might need to change a round-robin table to a hash distributed table. たとえば、ラウンド ロビン方式として新しいテーブルを最初に読み込むように、し、後で結合のパフォーマンスを向上させるハッシュ分散テーブルに移動してください。For example, you might initially load a new table as round-robin and then later move it to a hash-distributed table to get better join performance.

この例では、AdventureWorksDW サンプル データベースを使用します。This example uses the AdventureWorksDW sample database. SQL データ ウェアハウスのバージョンを読み込むには、次を参照してくださいSQL Data Warehouse にサンプル データを読み込む。To load the SQL Data Warehouse version, see Load sample data into SQL Data Warehouse

-- DimSalesTerritory is hash-distributed.
-- Copy it to a round-robin table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

次に、ハッシュ分散テーブルに変更します。Next, change it back to a hash distributed table.

-- You just made DimSalesTerritory a round-robin table.
-- Change it back to the original hash-distributed table. 
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH(SalesTerritoryKey) 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

D.D. CTAS を使用してレプリケートされたテーブルにテーブルを変換するにはUse CTAS to convert a table to a replicated table

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

この例は、レプリケートされたテーブルにラウンド ロビンまたはハッシュ分散テーブルへの変換に適用されます。This example applies for converting round-robin or hash-distributed tables to a replicated table. この例は、ディストリビューションの種類をさらに一歩を変更する前のメソッドを受け取ります。This particular example takes the previous method of changing the distribution type one step further. DimSalesTerritory がディメンションと可能性がありますより小さいテーブルであるために、他のテーブルに結合する際に、データ移動を回避するレプリケートされると、テーブルを再作成できます。Since DimSalesTerritory is a dimension and likely a smaller table, you can choose to re-create the table as replicated to avoid data movement when joining to other tables.

-- DimSalesTerritory is hash-distributed.
-- Copy it to a replicated table.
CREATE TABLE [dbo].[myTable]   
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = REPLICATE 
  )  
AS SELECT * FROM [dbo].[DimSalesTerritory]; 

-- Switch table names

RENAME OBJECT [dbo].[DimSalesTerritory] to [DimSalesTerritory_old];
RENAME OBJECT [dbo].[myTable] TO [DimSalesTerritory];

DROP TABLE [dbo].[DimSalesTerritory_old];

E.E. CTAS を使用して、列数が少ないテーブルを作成するにはUse CTAS to create a table with fewer columns

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

次の例では、という名前のラウンドロビン分散テーブルmyTable (c, ln)です。The following example creates a round-robin distributed table named myTable (c, ln). 新しいテーブルには、2 つの列のみにします。The new table only has two columns. 列の名前、SELECT ステートメントで列の別名を使用します。It uses the column aliases in the SELECT statement for the names of the columns.

CREATE TABLE myTable  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN  
  )  
AS SELECT CustomerKey AS c, LastName AS ln  
    FROM dimCustomer;  

クエリ ヒントの例Examples for query hints

F.F. 作成するテーブルとの間でのクエリ ヒントを使用する (CTAS) を選択します。Use a Query Hint with CREATE TABLE AS SELECT (CTAS)

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

このクエリは、CTAS ステートメントを使用してクエリの結合ヒントを使用するための基本構文を示しています。This query shows the basic syntax for using a query join hint with the CTAS statement. クエリが送信されると、 SQL データ ウェアハウスSQL Data Warehouse各個 々 の配布のクエリ プランを生成するときに、ハッシュ結合の方法を適用します。After the query is submitted, SQL データ ウェアハウスSQL Data Warehouse applies the hash join strategy when it generates the query plan for each individual distribution. ハッシュ結合のクエリ ヒントの詳細については、次を参照してください。 OPTION 句と #40 です。TRANSACT-SQL と #41 です。.For more information on the hash join query hint, see OPTION Clause (Transact-SQL).

CREATE TABLE dbo.FactInternetSalesNew  
WITH   
  (   
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = ROUND_ROBIN   
  )  
AS SELECT T1.* FROM dbo.FactInternetSales T1 JOIN dbo.DimCustomer T2  
ON ( T1.CustomerKey = T2.CustomerKey )  
OPTION ( HASH JOIN );  

外部テーブルの例Examples for external tables

G.G. CTAS を使用して Azure Blob ストレージからデータをインポートするにはUse CTAS to import data from Azure Blob storage

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

外部テーブルからデータをインポートするには、だけで作成表を使用する AS 外部テーブルからを選択します。To import data from an external table, simply use CREATE TABLE AS SELECT to select from the external table. 外部テーブルからデータを選択するための構文 SQL データ ウェアハウスSQL Data Warehouseは通常のテーブルからデータを選択するための構文と同じです。The syntax to select data from an external table into SQL データ ウェアハウスSQL Data Warehouse is the same as the syntax for selecting data from a regular table.

次の例では、Azure blob ストレージ アカウント内のデータに対して、外部テーブルを定義します。The following example defines an external table on data in an Azure blob storage account. 使用して、テーブルとして選択の作成、外部テーブルからを選択します。It then uses CREATE TABLE AS SELECT to select from the external table. これには、Azure blob ストレージのテキスト区切りファイルからデータをインポートされ、新しいデータを格納 SQL データ ウェアハウスSQL Data Warehouseテーブル。This imports the data from Azure blob storage text-delimited files and stores the data into a new SQL データ ウェアハウスSQL Data Warehouse table.

--Use your own processes to create the text-delimited files on Azure blob storage.  
--Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url varchar(50),  
    event_date date,  
    user_IP varchar(50)  
)  
WITH (  
    LOCATION='/logs/clickstream/2015/',  
    DATA_SOURCE = MyAzureStorage,  
    FILE_FORMAT = TextFileFormat)  
;  

--Use CREATE TABLE AS SELECT to import the Azure blob storage data into a new   
--SQL Data Warehouse table called ClickStreamData  
CREATE TABLE ClickStreamData   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;  

H.H. CTAS を使用して、外部テーブルから Hadoop のデータをインポートするにはUse CTAS to import Hadoop data from an external table

適用されます。 Parallel Data WarehouseParallel Data WarehouseApplies to: Parallel Data WarehouseParallel Data Warehouse

外部テーブルからデータをインポートするには、だけで作成表を使用する AS 外部テーブルからを選択します。To import data from an external table, simply use CREATE TABLE AS SELECT to select from the external table. 外部テーブルからデータを選択するための構文 Parallel Data WarehouseParallel Data Warehouseは通常のテーブルからデータを選択するための構文と同じです。The syntax to select data from an external table into Parallel Data WarehouseParallel Data Warehouse is the same as the syntax for selecting data from a regular table.

次の例では、Hadoop クラスター上、外部テーブルを定義します。The following example defines an external table on a Hadoop cluster. 使用して、テーブルとして選択の作成、外部テーブルからを選択します。It then uses CREATE TABLE AS SELECT to select from the external table. これには、Hadoop テキスト区切りファイルからデータをインポートされ、新しいデータを格納 Parallel Data WarehouseParallel Data Warehouseテーブル。This imports the data from Hadoop text-delimited files and stores the data into a new Parallel Data WarehouseParallel Data Warehouse table.

-- Create the external table called ClickStream.  
CREATE EXTERNAL TABLE ClickStreamExt (   
    url varchar(50),  
    event_date date,  
    user_IP varchar(50)  
)  
WITH (  
    LOCATION = 'hdfs://MyHadoop:5000/tpch1GB/employee.tbl',  
    FORMAT_OPTIONS ( FIELD_TERMINATOR = '|')  
)  
;  

-- Use your own processes to create the Hadoop text-delimited files 
-- on the Hadoop Cluster.  

-- Use CREATE TABLE AS SELECT to import the Hadoop data into a new 
-- table called ClickStreamPDW  
CREATE TABLE ClickStreamPDW   
WITH  
  (  
    CLUSTERED COLUMNSTORE INDEX,  
    DISTRIBUTION = HASH (user_IP)  
  )  
AS SELECT * FROM ClickStreamExt  
;   

CTAS を使用して、SQL Server のコードを置換する例Examples using CTAS to replace SQL Server code

いくつかサポートされていない機能を回避するには、CTAS を使用します。Use CTAS to work around some unsupported features. データ ウェアハウスで、コードを実行できるだけでなく、CTAS を使用する既存のコードの書き直し通常パフォーマンスが向上します。Besides being able to run your code on the data warehouse, rewriting existing code to use CTAS will usually improve performance. これは、完全に並列化された設計の結果です。This is a result of its fully parallelized design.

注意

考えてみてください"CTAS 最初"です。Try to think "CTAS first". 使用して、問題を解決できると思われる場合CTASしする場合は、一般に対処する最善の方法でもより多くのデータを記述するためです。If you think you can solve a problem using CTAS then that is generally the best way to approach it - even if you are writing more data as a result.

I.I. CTAS を使用して、選択ではなく.にUse CTAS instead of SELECT..INTO

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

SQL Server のコードは、通常、SELECT を使用.INTO テーブルの SELECT ステートメントの結果に設定することです。SQL Server code typically uses SELECT..INTO to populate a table with the results of a SELECT statement. これは、SQL Server SELECT の例です.INTO ステートメントです。This is an example of a SQL Server SELECT..INTO statement.

SELECT *
INTO    #tmp_fct
FROM    [dbo].[FactInternetSales]

この構文は SQL Data Warehouse と Parallel Data Warehouse でサポートされていません。This syntax is not supported in SQL Data Warehouse and Parallel Data Warehouse. この例は、以前の選択を書き換える方法を示しています.CTAS ステートメントとしてステートメントです。This example shows how to rewrite the previous SELECT..INTO statement as a CTAS statement. CTAS の構文では説明されている配布オプションのいずれかを選択できます。You can choose any of the DISTRIBUTION options described in the CTAS syntax. この例では、ROUND_ROBIN 配布方法を使用します。This example uses the ROUND_ROBIN distribution method.

CREATE TABLE #tmp_fct
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
AS
SELECT  *
FROM    [dbo].[FactInternetSales]
;

J.J. CTAS と暗黙の結合に ANSI 結合の置換を使用して、FROMの句、UPDATEステートメントUse CTAS and implicit joins to replace ANSI joins in the FROM clause of an UPDATE statement

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

一緒に ANSI 結合構文を使用して更新または削除を実行する 2 つ以上のテーブルを結合する複雑な更新があることがあります。You may find you have a complex update that joins more than two tables together using ANSI joining syntax to perform the UPDATE or DELETE.

このテーブルを更新する必要がありました想像してください。Imagine you had to update this table:

CREATE TABLE [dbo].[AnnualCategorySales]
(   [EnglishProductCategoryName]    NVARCHAR(50)    NOT NULL
,   [CalendarYear]                  SMALLINT        NOT NULL
,   [TotalSalesAmount]              MONEY           NOT NULL
)
WITH
(
    DISTRIBUTION = ROUND_ROBIN
)
;

次のように、元のクエリが説明しました。The original query might have looked something like this:

UPDATE  acs
SET     [TotalSalesAmount] = [fis].[TotalSalesAmount]
FROM    [dbo].[AnnualCategorySales]     AS acs
JOIN    (
        SELECT  [EnglishProductCategoryName]
        ,       [CalendarYear]
        ,       SUM([SalesAmount])              AS [TotalSalesAmount]
        FROM    [dbo].[FactInternetSales]       AS s
        JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
        JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
        JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
        JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
        WHERE   [CalendarYear] = 2004
        GROUP BY
                [EnglishProductCategoryName]
        ,       [CalendarYear]
        ) AS fis
ON  [acs].[EnglishProductCategoryName]  = [fis].[EnglishProductCategoryName]
AND [acs].[CalendarYear]                = [fis].[CalendarYear]
;

結合を ANSI SQL データ ウェアハウスがサポートされていないため、FROMの句、UPDATEステートメントでは、少し変更することがなく経由では、この SQL Server コードを使用することはできません。Since SQL Data Warehouse does not support ANSI joins in the FROM clause of an UPDATE statement, you cannot use this SQL Server code over without changing it slightly.

組み合わせを使用することができます、CTASと暗黙の結合にこのコードを置き換えます。You can use a combination of a CTAS and an implicit join to replace this code:

-- Create an interim table
CREATE TABLE CTAS_acs
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT  ISNULL(CAST([EnglishProductCategoryName] AS NVARCHAR(50)),0)    AS [EnglishProductCategoryName]
,       ISNULL(CAST([CalendarYear] AS SMALLINT),0)                      AS [CalendarYear]
,       ISNULL(CAST(SUM([SalesAmount]) AS MONEY),0)                     AS [TotalSalesAmount]
FROM    [dbo].[FactInternetSales]       AS s
JOIN    [dbo].[DimDate]                 AS d    ON s.[OrderDateKey]             = d.[DateKey]
JOIN    [dbo].[DimProduct]              AS p    ON s.[ProductKey]               = p.[ProductKey]
JOIN    [dbo].[DimProductSubCategory]   AS u    ON p.[ProductSubcategoryKey]    = u.[ProductSubcategoryKey]
JOIN    [dbo].[DimProductCategory]      AS c    ON u.[ProductCategoryKey]       = c.[ProductCategoryKey]
WHERE   [CalendarYear] = 2004
GROUP BY
        [EnglishProductCategoryName]
,       [CalendarYear]
;

-- Use an implicit join to perform the update
UPDATE  AnnualCategorySales
SET     AnnualCategorySales.TotalSalesAmount = CTAS_ACS.TotalSalesAmount
FROM    CTAS_acs
WHERE   CTAS_acs.[EnglishProductCategoryName] = AnnualCategorySales.[EnglishProductCategoryName]
AND     CTAS_acs.[CalendarYear]               = AnnualCategorySales.[CalendarYear]
;

--Drop the interim table
DROP TABLE CTAS_acs
;

K.K. CTAS を使用して、DELETE ステートメントの FROM 句で ANSI を使用する代わりに保持するデータの結合の指定Use CTAS to specify which data to keep instead of using ANSI joins in the FROM clause of a DELETE statement

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

使用してデータを削除するための最善の方法がありますCTASです。Sometimes the best approach for deleting data is to use CTAS. 単にデータを削除するのではなく、残しておきたいデータを選択します。Rather than deleting the data simply select the data you want to keep. この特にDELETEansi で結合を SQL Data Warehouse では、ANSI をサポートしていないため、結合の構文を使用するステートメント、FROMの句、DELETEステートメントです。This especially true for DELETE statements that use ansi joining syntax since SQL Data Warehouse does not support ANSI joins in the FROM clause of a DELETE statement.

変換された DELETE ステートメントの例は、下入手できます。An example of a converted DELETE statement is available below:

CREATE TABLE dbo.DimProduct_upsert
WITH
(   Distribution=HASH(ProductKey)
,   CLUSTERED INDEX (ProductKey)
)
AS -- Select Data you wish to keep
SELECT     p.ProductKey
,          p.EnglishProductName
,          p.Color
FROM       dbo.DimProduct p
RIGHT JOIN dbo.stg_DimProduct s
ON         p.ProductKey = s.ProductKey
;

RENAME OBJECT dbo.DimProduct        TO DimProduct_old;
RENAME OBJECT dbo.DimProduct_upsert TO DimProduct;

L.L. CTAS を使用して、merge ステートメントを簡略化Use CTAS to simplify merge statements

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

Merge ステートメントで置換できる、少なくとも一部を使用してCTASです。Merge statements can be replaced, at least in part, by using CTAS. 統合することができます、INSERTUPDATE単一のステートメントにします。You can consolidate the INSERT and the UPDATE into a single statement. 削除されたレコードは、オフ、2 番目のステートメントで終了する必要があります。Any deleted records would need to be closed off in a second statement.

例、UPSERTは以下を使用します。An example of an UPSERT is available below:

CREATE TABLE dbo.[DimProduct_upsert]
WITH
(   DISTRIBUTION = HASH([ProductKey])
,   CLUSTERED INDEX ([ProductKey])
)
AS
-- New rows and new versions of rows
SELECT      s.[ProductKey]
,           s.[EnglishProductName]
,           s.[Color]
FROM      dbo.[stg_DimProduct] AS s
UNION ALL  
-- Keep rows that are not being touched
SELECT      p.[ProductKey]
,           p.[EnglishProductName]
,           p.[Color]
FROM      dbo.[DimProduct] AS p
WHERE NOT EXISTS
(   SELECT  *
    FROM    [dbo].[stg_DimProduct] s
    WHERE   s.[ProductKey] = p.[ProductKey]
)
;

RENAME OBJECT dbo.[DimProduct]          TO [DimProduct_old];
RENAME OBJECT dbo.[DimpProduct_upsert]  TO [DimProduct];

M.M. データ型を明示的に状態と出力の null 値許容属性Explicitly state data type and nullability of output

対象: Azure SQL Data Warehouse と並列データ ウェアハウスApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

SQL Server のコードを SQL Data Warehouse に移行したときにこの種類のコーディング パターンの間で実行することがあります。When migrating SQL Server code to SQL Data Warehouse, you might find you run across this type of coding pattern:

DECLARE @d decimal(7,2) = 85.455
,       @f float(24)    = 85.455

CREATE TABLE result
(result DECIMAL(7,2) NOT NULL
)
WITH (DISTRIBUTION = ROUND_ROBIN)

INSERT INTO result
SELECT @d*@f
;

本能、CTAS にこのコードを移行する必要があります。 また正しいはすると思われる場合があります。Instinctively you might think you should migrate this code to a CTAS and you would be correct. ただし、ここで非表示の問題があります。However, there is a hidden issue here.

次のコードでは、同じ結果を生成しません。The following code does NOT yield the same result:

DECLARE @d decimal(7,2) = 85.455
,       @f float(24)    = 85.455
;

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT @d*@f as result
;

「結果」列が式のデータ型と null 許可属性の値で実行フォワードことに注意してください。Notice that the column "result" carries forward the data type and nullability values of the expression. これは、注意が必要でない場合、値の微妙な差異に可能性があります。This can lead to subtle variances in values if you aren't careful.

例として、次を試してください。Try the following as an example:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

格納されている結果の値が異なるです。The value stored for result is different. 結果列に永続化された値が他の式で使用されるので、エラーは、さらに大きなになります。As the persisted value in the result column is used in other expressions the error becomes even more significant.

CREATE TABLE AS SELECT の結果

これは、データ移行のため特に重要です。This is particularly important for data migrations. 2 番目のクエリはより正確な記述しても問題があります。Even though the second query is arguably more accurate there is a problem. データが異なることが、ソース システムと比較して、移行での整合性の質問につながります。The data would be different compared to the source system and that leads to questions of integrity in the migration. これは、「が正しくありません」の応答が実際には右側の 1 つをそれらのまれなケースのいずれかです。This is one of those rare cases where the "wrong" answer is actually the right one!

2 つの結果の間でこのような違いが見理由は、暗黙的な型キャストまでです。The reason we see this disparity between the two results is down to implicit type casting. 最初の例では、テーブルは、列定義を定義します。In the first example the table defines the column definition. 行が挿入されたときに、暗黙的な型変換が発生します。When the row is inserted an implicit type conversion occurs. 2 番目の例ではありません暗黙の型変換式、列のデータ型を定義します。In the second example there is no implicit type conversion as the expression defines data type of the column. また一方、最初の例でことはできませんがある 2 番目の例では、列を null 許容の列として定義されていることに注意してください。Notice also that the column in the second example has been defined as a NULLable column whereas in the first example it has not. テーブルが、最初の例の列の null 値で作成されたときを明示的に定義されました。When the table was created in the first example column nullability was explicitly defined. 2 番目のだけ残さ式および既定でこの例は、NULL 定義になります。In the second example it was just left to the expression and by default this would result in a NULL definition.

これらの問題を解決する必要があります明示的に設定する型変換とで null 値許容属性、SELECTの部分、CTASステートメントです。To resolve these issues you must explicitly set the type conversion and nullability in the SELECT portion of the CTAS statement. 作成するテーブルの部分では、これらのプロパティを設定できません。You cannot set these properties in the create table part.

次の例では、コードを修正する方法を示します。The example below demonstrates how to fix the code:

DECLARE @d decimal(7,2) = 85.455
,       @f float(24)    = 85.455

CREATE TABLE ctas_r
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT ISNULL(CAST(@d*@f AS DECIMAL(7,2)),0) as result

次のことを考慮してください。Note the following:

  • CAST または CONVERT を使用できたCAST or CONVERT could have been used
  • COALESCE ではない null 許容属性を強制的に ISNULL を使用します。ISNULL is used to force NULLability not COALESCE
  • ISNULL は最も外側の関数ISNULL is the outermost function
  • ISNULL の 2 番目の部分は、定数つまり 0The second part of the ISNULL is a constant i.e. 0

注意

正しく設定される null 値許容属性が使用する重要ISNULLおよび notCOALESCEです。For the nullability to be correctly set it is vital to use ISNULL and not COALESCE. COALESCE決定的関数ではないため、式の結果には null 値が必ず.COALESCE is not a deterministic function and so the result of the expression will always be NULLable. ISNULL異なります。ISNULL is different. これは、決定的です。It is deterministic. そのためときの 2 番目の部分、ISNULL関数は、定数またはリテラルは、結果の値は NOT NULL します。Therefore when the second part of the ISNULL function is a constant or a literal then the resulting value will be NOT NULL.

このヒントは、計算の結果の整合性の確保に役立つだけではありません。This tip is not just useful for ensuring the integrity of your calculations. テーブル パーティション切り替え用に重要です。It is also important for table partition switching. ファクトとして定義されているこのテーブルがある場合を想像してください。Imagine you have this table defined as your fact:

CREATE TABLE [dbo].[Sales]
(
    [date]      INT     NOT NULL
,   [product]   INT     NOT NULL
,   [store]     INT     NOT NULL
,   [quantity]  INT     NOT NULL
,   [price]     MONEY   NOT NULL
,   [amount]    MONEY   NOT NULL
)
WITH
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101,20020101
                    ,20030101,20040101,20050101
                    )
                )
)
;

ただし、値フィールドは、ソース データの一部ではない計算式です。However, the value field is a calculated expression it is not part of the source data.

こうことができます、パーティション分割されたデータセットを作成します。To create your partitioned dataset you might want to do this:

CREATE TABLE [dbo].[Sales_in]
WITH    
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
    [date]    
,   [product]
,   [store]
,   [quantity]
,   [price]   
,   [quantity]*[price]  AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create')
;

クエリは、適切に機能が実行されます。The query would run perfectly fine. この問題は、partition switch を実行しようとするときに得られます。The problem comes when you try to perform the partition switch. テーブルの定義が一致しません。The table definitions do not match. テーブルの定義を CTAS を一致させるには、変更する必要があります。To make the table definitions match the CTAS needs to be modified.

CREATE TABLE [dbo].[Sales_in]
WITH    
(   DISTRIBUTION = HASH([product])
,   PARTITION   (   [date] RANGE RIGHT FOR VALUES
                    (20000101,20010101
                    )
                )
)
AS
SELECT
    [date]    
,   [product]
,   [store]
,   [quantity]
,   [price]   
,   ISNULL(CAST([quantity]*[price] AS MONEY),0) AS [amount]
FROM [stg].[source]
OPTION (LABEL = 'CTAS : Partition IN table : Create');

そのため、型の整合性と、CTAS で null 値許容プロパティを維持するがわかりますエンジニア リング最適なことをお勧めします。You can see therefore that type consistency and maintaining nullability properties on a CTAS is a good engineering best practice. 計算の結果の整合性を維持するのに役立ち、パーティションの切り替えができるようになります。It helps to maintain integrity in your calculations and also ensures that partition switching is possible.

参照See Also

CREATE EXTERNAL DATA SOURCE (Transact-SQL) CREATE EXTERNAL DATA SOURCE (Transact-SQL)
CREATE EXTERNAL FILE FORMAT (Transact-SQL) CREATE EXTERNAL FILE FORMAT (Transact-SQL)
CREATE EXTERNAL TABLE (Transact-SQL) CREATE EXTERNAL TABLE (Transact-SQL)
EXTERNAL TABLE AS SELECT ( を作成します。TRANSACT-SQL と #41 です。 CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
TABLE ( を作成します。Azure SQL Data Warehouse ) TABLE ( を削除TRANSACT-SQL と #41 です。 CREATE TABLE (Azure SQL Data Warehouse) DROP TABLE (Transact-SQL)
外部テーブル ( を削除します。TRANSACT-SQL と #41 です。 DROP EXTERNAL TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
ALTER EXTERNAL TABLE & #40 です。TRANSACT-SQL と #41 です。ALTER EXTERNAL TABLE (Transact-SQL)