CREATE TABLE AS SELECT (SQL Data Warehouse do Azure)CREATE TABLE AS SELECT (Azure SQL Data Warehouse)

APLICA-SE A: nãoSQL Server nãoBanco de Dados SQL do Azure simAzure Synapse Analytics (SQL DW) simParallel Data Warehouse APPLIES TO: noSQL Server noAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

CTAS (CREATE TABLE AS SELECT) é um dos recursos mais importantes do T-SQL disponíveis.CREATE TABLE AS SELECT (CTAS) is one of the most important T-SQL features available. É uma operação totalmente em paralelo que cria uma nova tabela com base na saída de uma instrução SELECT.It is a fully parallelized operation that creates a new table based on the output of a SELECT statement. CTAS é a maneira mais rápida e simples de criar uma cópia de uma tabela.CTAS is the simplest and fastest way to create a copy of a table.

Por exemplo, use CTAS para:For example, use CTAS to:

  • Recriar uma tabela com uma coluna de distribuição de hash diferente.Re-create a table with a different hash distribution column.
  • Recriar uma tabela como replicada.Re-create a table as replicated.
  • Criar um índice columnstore em apenas algumas das colunas da tabela.Create a columnstore index on just some of the columns in the table.
  • Consultar ou importar dados externos.Query or import external data.

Observação

Como CTAS complementa os recursos de criação de uma tabela, este tópico tenta não repetir o tópico CREATE TABLE.Since CTAS adds to the capabilities of creating a table, this topic tries not to repeat the CREATE TABLE topic. Ele descreve as diferenças entre as instruções CTAS e CREATE TABLE.Instead, it describes the differences between the CTAS and CREATE TABLE statements. Para obter os detalhes sobre CREATE TABLE, veja a instrução CREATE TABLE (SQL Data Warehouse do Azure).For the CREATE TABLE details, see CREATE TABLE (Azure SQL Data Warehouse) statement.

Ícone de link do tópico Convenções de sintaxe Transact-SQLTopic link icon Transact-SQL Syntax Conventions

SintaxeSyntax

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

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

<table_option> ::= 
    {   
        CLUSTERED COLUMNSTORE INDEX --default for SQL Data Warehouse 
      | CLUSTERED COLUMNSTORE INDEX ORDER (column[,...n])
      | 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  

<query_hint> ::=
    {
        MAXDOP 
    }

ArgumentosArguments

Para obter detalhes, confira a seção Argumentos em CREATE TABLE.For details, see the Arguments section in CREATE TABLE.

Opções de colunaColumn options

column_name [ ,...n ]column_name [ ,...n ]
Os nomes de coluna não permitem as opções de coluna mencionadas em CREATE TABLE.Column names do not allow the column options mentioned in CREATE TABLE. Nesse caso, você pode fornecer uma lista opcional de um ou mais nomes de coluna para a nova tabela.Instead, you can provide an optional list of one or more column names for the new table. As colunas na nova tabela usarão os nomes que você especificar.The columns in the new table will use the names you specify. Quando você especificar nomes de coluna, o número de colunas na lista de colunas deverá corresponder ao número de colunas nos resultados de select.When you specify column names, the number of columns in the column list must match the number of columns in the select results. Se você não especificar nenhum nome de coluna, a nova tabela de destino usará os nomes de coluna nos resultados da instrução select.If you don't specify any column names, the new target table will use the column names in the select statement results.

Não é possível especificar nenhuma outra opções de coluna, como tipos de dados, ordenação ou nulidade.You cannot specify any other column options such as data types, collation, or nullability. Cada um desses atributos é derivado dos resultados da instrução SELECT.Each of these attributes is derived from the results of the SELECT statement. No entanto, você pode usar a instrução SELECT para alterar os atributos.However, you can use the SELECT statement to change the attributes. Para obter um exemplo, confira Usar CTAS para alterar os atributos da coluna.For an example, see Use CTAS to change column attributes.

Opções de distribuição da tabelaTable distribution options

DISTRIBUTION = HASH ( distribution_column_name ) | ROUND_ROBIN | REPLICATEDISTRIBUTION = HASH ( distribution_column_name ) | ROUND_ROBIN | REPLICATE
A instrução CTAS requer uma opção de distribuição e não têm valores padrão.The CTAS statement requires a distribution option and does not have default values. Isso é diferente de CREATE TABLE, que tem padrões.This is different from CREATE TABLE which has defaults.

Para obter detalhes e entender como escolher a melhor coluna de distribuição, veja a seção Opções de distribuição da tabela em CREATE TABLE.For details and to understand how to choose the best distribution column, see the Table distribution options section in CREATE TABLE.

Opções de partição da tabelaTable partition options

A instrução CTAS cria uma tabela não particionada por padrão, mesmo quando a tabela de origem está particionada.The CTAS statement creates a non-partitioned table by default, even if the source table is partitioned. Para criar uma tabela particionada com a instrução CTAS, você precisa especificar a opção de partição.To create a partitioned table with the CTAS statement, you must specify the partition option.

Para obter detalhes, veja a seção Opções de partição da tabela em CREATE TABLE.For details, see the Table partition options section in CREATE TABLE.

instrução SelectSelect statement

A instrução select é a diferença fundamental entre CTAS e CREATE TABLE.The select statement is the fundamental difference between CTAS and CREATE TABLE.

WITH common_table_expressionWITH common_table_expression
Especifica um conjunto de resultados nomeado temporário, conhecido como uma CTE (expressão de tabela comum).Specifies a temporary named result set, known as a common table expression (CTE). Para obter mais informações, confira WITH common_table_expression (Transact-SQL).For more information, see WITH common_table_expression (Transact-SQL).

SELECT select_criteriaSELECT select_criteria
Popula a nova tabela com os resultados de uma instrução SELECT.Populates the new table with the results from a SELECT statement. select_criteria é o corpo da instrução SELECT que determina quais dados serão copiados para a nova tabela.select_criteria is the body of the SELECT statement that determines which data to copy to the new table. Para obter informações sobre as instruções SELECT, confira SELECT (Transact-SQL).For information about SELECT statements, see SELECT (Transact-SQL).

Dica de consultaQuery hint

Os usuários podem definir MAXDOP como um valor inteiro para controlar o grau máximo de paralelismo.Users can set MAXDOP to an integer value to control the maximum degree of parallelism. Quando MAXDOP é definido como 1, a consulta é executada por um único thread.When MAXDOP is set to 1, the query is executed by a single thread.

PermissõesPermissions

CTAS requer a permissão SELECT em todos os objetos referenciados em select_criteria.CTAS requires SELECT permission on any objects referenced in the select_criteria.

Para obter permissões para criar uma tabela, confira Permissões em CREATE TABLE.For permissions to create a table, see Permissions in CREATE TABLE.

Comentários geraisGeneral Remarks

Para obter detalhes, confira Comentários gerais em CREATE TABLE.For details, see General Remarks in CREATE TABLE.

Limitações e RestriçõesLimitations and Restrictions

O SQL Data Warehouse do Azure ainda não é compatível com as estatísticas de criação automática nem de atualização automática.Azure SQL Data Warehouse does not yet support auto create or auto update statistics. Para obter o melhor desempenho nas consultas, é importante criar estatísticas em todas as colunas de todas as tabelas depois de executar CTAS e depois que ocorrerem alterações significativas nos dados.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. Para obter mais informações, veja CREATE STATISTICS (Transact-SQL).For more information, see CREATE STATISTICS (Transact-SQL).

Um índice columnstore clusterizado ordenado pode ser criado em colunas de qualquer tipo de dados com suporte no SQL Data Warehouse do Azure, exceto por colunas de cadeia de caracteres.An ordered clustered columnstore index can be created on columns of any data types supported in Azure SQL Data Warehouse except for string columns.

SET ROWCOUNT (Transact-SQL) não tem efeito em CTAS.SET ROWCOUNT (Transact-SQL) has no effect on CTAS. Para obter um comportamento semelhante, use TOP (Transact-SQL).To achieve a similar behavior, use TOP (Transact-SQL).

Para obter detalhes, confira Limitações e restrições em CREATE TABLE.For details, see Limitations and Restrictions in CREATE TABLE.

Comportamento de bloqueioLocking Behavior

Para obter detalhes, confira Comportamento de bloqueio em CREATE TABLE.For details, see Locking Behavior in CREATE TABLE.

DesempenhoPerformance

Para uma tabela distribuída por hash, você pode usar CTAS para escolher uma coluna de distribuição diferente para melhorar o desempenho das junções e agregações.For a hash-distributed table, you can use CTAS to choose a different distribution column to achieve better performance for joins and aggregations. Se escolher uma coluna de distribuição diferente não for seu objetivo, você terá o melhor desempenho em CTAS se especificar a mesma coluna de distribuição, pois isso evitará a redistribuição das linhas.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.

Se você estiver usando CTAS para criar a tabela e o desempenho não for um fator, especifique ROUND_ROBIN para não precisar decidir por uma coluna de distribuição.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.

Para evitar a movimentação de dados nas próximas consultas, especifique REPLICATE, às custas do aumento do armazenamento, para carregar uma cópia completa da tabela em cada nó de computação.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.

Exemplos para copiar uma tabelaExamples for copying a table

A.A. Usar CTAS para copiar uma tabelaUse CTAS to copy a table

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

Talvez um dos tipos de uso mais comuns do CTAS é criar uma cópia de uma tabela para que você possa alterar a DDL.Perhaps one of the most common uses of CTAS is creating a copy of a table so that you can change the DDL. Por exemplo, se você criou a tabela originalmente como ROUND_ROBIN e agora deseja alterá-la para uma tabela distribuída em uma coluna, CTAS será a forma de alterar a coluna de distribuição.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 também pode ser usado para alterar os tipos de particionamento, de indexação ou de coluna.CTAS can also be used to change partitioning, indexing, or column types.

Digamos que você criou esta tabela usando o tipo de distribuição padrão de ROUND_ROBIN distribuído porque nenhuma coluna de distribuição foi especificada no 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)
);

Agora você deseja criar uma nova cópia dessa tabela com um índice columnstore clusterizado para poder usufruir do desempenho das tabelas columnstore clusterizadas.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. Você também deseja distribuir essa tabela em ProductKey pois prevê que haverá junções nessa coluna e deseja evitar a movimentação de dados durante a junções em 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. Por fim, você também deseja adicionar o particionamento em OrderDateKey para que seja possível excluir rapidamente os dados antigos descartando as partições antigas.Lastly you also want to add partitioning on OrderDateKey so that you can quickly delete old data by dropping old partitions. Aqui está a instrução CTAS que copiaria a tabela antiga em uma nova tabela.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;

Por fim, você pode renomear as tabelas para fazer a troca pela nova tabela e, em seguida, remover a tabela antiga.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;

Exemplos de opções de colunaExamples for column options

B.B. Usar CTAS para alterar atributos de colunaUse CTAS to change column attributes

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

Este exemplo usa CTAS para alterar os tipos de dados, a nulidade e a ordenação para várias colunas na tabela 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);

Como etapa final, você pode usar RENAME (Transact-SQL) para mudar os nomes das tabelas.As a final step, you can use RENAME (Transact-SQL) to switch the table names. Assim, DimCustomer2 torna-se a nova tabela.This makes DimCustomer2 be the new table.

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

DROP TABLE DimCustomer2_old;

Exemplos de distribuição da tabelaExamples for table distribution

C.C. Usar CTAS para alterar o método de distribuição de uma tabelaUse CTAS to change the distribution method for a table

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

Esse exemplo simples mostra como alterar o método de distribuição de uma tabela.This simple example shows how to change the distribution method for a table. Para mostrar o mecanismo de como fazer isso, ele altera uma tabela distribuída por hash para round robin e, em seguida, altera a tabela de round robin para distribuída por hash.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. A tabela final corresponde à tabela original.The final table matches the original table.

Na maioria dos casos, não será necessário converter uma tabela distribuída por hash em uma tabela de round robin.In most cases you won't need to change a hash-distributed table to a round-robin table. Com um frequência maior, pode ser necessário converter uma tabela de round robin em uma tabela distribuída por hash.More often, you might need to change a round-robin table to a hash distributed table. Por exemplo, você pode carregar inicialmente uma nova tabela como round robin e, mais tarde, convertê-la em uma tabela distribuída por hash para melhorar o desempenho da junção.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.

Este exemplo usa o banco de dados de exemplo AdventureWorksDW.This example uses the AdventureWorksDW sample database. Para carregar a versão do SQL Data Warehouse, confira Carregar dados de exemplo no SQL Data WarehouseTo 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];

Em seguida, converta-a novamente em uma tabela distribuída por hash.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. Usar CTAS para converter uma tabela em uma tabela replicadaUse CTAS to convert a table to a replicated table

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

Este exemplo aplica-se para converter tabelas round robin ou distribuídas por hash em uma tabela replicada.This example applies for converting round-robin or hash-distributed tables to a replicated table. Esse exemplo específico avança mais uma etapa do método anterior de alterar o tipo de distribuição.This particular example takes the previous method of changing the distribution type one step further. Como DimSalesTerritory é uma dimensão e provavelmente uma tabela menor, você pode escolher recriar a tabela como replicada para evitar a movimentação de dados ao unir a outras tabelas.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. Usar CTAS para criar uma tabela com menos colunasUse CTAS to create a table with fewer columns

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

O exemplo a seguir cria uma tabela distribuída de round robin denominada myTable (c, ln).The following example creates a round-robin distributed table named myTable (c, ln). A nova tabela tem apenas duas colunas.The new table only has two columns. Ela usa os aliases de coluna na instrução SELECT para os nomes das colunas.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;  
  

Exemplos de dicas de consultaExamples for query hints

F.F. Usar uma dica de consulta com CTAS (CREATE TABLE AS SELECT)Use a Query Hint with CREATE TABLE AS SELECT (CTAS)

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

Esta consulta mostra a sintaxe básica para usar uma dica de junção de consulta com a instrução CTAS.This query shows the basic syntax for using a query join hint with the CTAS statement. Depois que a consulta é enviada, o SQL Data WarehouseSQL Data Warehouse aplica a estratégia de junção de hash ao gerar o plano de consulta para cada distribuição individual.After the query is submitted, SQL Data WarehouseSQL Data Warehouse applies the hash join strategy when it generates the query plan for each individual distribution. Para obter mais informações sobre a dica de consulta de junção de hash, confira Cláusula OPTION (Transact-SQL).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 );  

Exemplos de tabelas externasExamples for external tables

G.G. Usar CTAS para importar dados do Armazenamento de Blobs do AzureUse CTAS to import data from Azure Blob storage

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

Para importar dados de uma tabela externa, simplesmente use CREATE TABLE AS SELECT para selecionar a tabela externa.To import data from an external table, simply use CREATE TABLE AS SELECT to select from the external table. A sintaxe para selecionar dados de uma tabela externa no SQL Data WarehouseSQL Data Warehouse é a mesma que a sintaxe para selecionar dados de uma tabela regular.The syntax to select data from an external table into SQL Data WarehouseSQL Data Warehouse is the same as the syntax for selecting data from a regular table.

O exemplo a seguir define uma tabela externa usando dados em uma conta de Armazenamento de Blobs do Azure.The following example defines an external table on data in an Azure blob storage account. Em seguida, ele usa CREATE TABLE AS SELECT para selecionar na tabela externa.It then uses CREATE TABLE AS SELECT to select from the external table. Essa ação importa os dados dos arquivos de texto delimitados do Armazenamento de Blobs do Azure e armazena os dados em uma nova tabela do SQL Data WarehouseSQL Data Warehouse.This imports the data from Azure blob storage text-delimited files and stores the data into a new SQL Data WarehouseSQL 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. Usar CTAS para importar dados do Hadoop de uma tabela externaUse CTAS to import Hadoop data from an external table

Aplica-se a: Parallel Data WarehouseParallel Data WarehouseApplies to: Parallel Data WarehouseParallel Data Warehouse

Para importar dados de uma tabela externa, simplesmente use CREATE TABLE AS SELECT para selecionar a tabela externa.To import data from an external table, simply use CREATE TABLE AS SELECT to select from the external table. A sintaxe para selecionar dados de uma tabela externa no Parallel Data WarehouseParallel Data Warehouse é a mesma que a sintaxe para selecionar dados de uma tabela regular.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.

O exemplo a seguir define uma tabela externa em um cluster do Hadoop.The following example defines an external table on a Hadoop cluster. Em seguida, ele usa CREATE TABLE AS SELECT para selecionar na tabela externa.It then uses CREATE TABLE AS SELECT to select from the external table. Essa ação importa os dados dos arquivos de texto delimitados do Hadoop e armazena os dados em uma nova tabela do 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  
;   

Exemplos que usam CTAS para substituir o código do SQL ServerExamples using CTAS to replace SQL Server code

Use CTAS para solucionar a não compatibilidade com alguns recursos.Use CTAS to work around some unsupported features. Além de permitir a execução do código no data warehouse, reescrever o código existente para usar CTAS normalmente melhora o desempenho.Besides being able to run your code on the data warehouse, rewriting existing code to use CTAS will usually improve performance. Este é um resultado de seu design totalmente em paralelo.This is a result of its fully parallelized design.

Observação

Tente imaginar "CTAS primeiro".Try to think "CTAS first". Se você achar que é possível resolver um problema usando CTAS, geralmente essa será a melhor maneira de abordá-lo, mesmo que mais dados sejam escritos como resultado.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. Usar CTAS em vez de SELECT..INTOUse CTAS instead of SELECT..INTO

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

O código do SQL Server normalmente usa SELECT..INTO para popular uma tabela com os resultados de uma instrução SELECT.SQL Server code typically uses SELECT..INTO to populate a table with the results of a SELECT statement. Este é um exemplo de uma instrução SELECT..INTO do SQL Server.This is an example of a SQL Server SELECT..INTO statement.

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

Essa sintaxe não é compatível com o SQL Data Warehouse nem com o Parallel Data Warehouse.This syntax is not supported in SQL Data Warehouse and Parallel Data Warehouse. Este exemplo mostra como reescrever a instrução SELECT..INTO anterior como uma instrução CTAS.This example shows how to rewrite the previous SELECT..INTO statement as a CTAS statement. Você pode escolher uma das opções de DISTRIBUTION descritas na sintaxe de CTAS.You can choose any of the DISTRIBUTION options described in the CTAS syntax. Este exemplo usa o método de distribuição de 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. Usar CTAS e junções implícita para substituir as junções de ANSI na cláusula FROM de uma instrução UPDATEUse CTAS and implicit joins to replace ANSI joins in the FROM clause of an UPDATE statement

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

É possível que você tenha uma atualização complexa que una mais de duas tabelas usando a sintaxe de junção ANSI para executar UPDATE ou DELETE.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 que você precisasse atualizar esta tabela: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
)
;

A consulta original seria semelhante a esta: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]
;

Como o SQL Data Warehouse não é compatível com junções ANSI na cláusula FROM de uma instrução UPDATE, não é possível usar esse código do SQL Server sem alterá-lo um pouco.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.

Você pode usar uma combinação de um CTAS e uma junção implícita para substituir este código: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. Usar CTAS para especificar quais dados para manter em vez de usar ANSI junções na cláusula FROM de uma instrução DELETEUse CTAS to specify which data to keep instead of using ANSI joins in the FROM clause of a DELETE statement

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

Às vezes, a melhor abordagem para excluir dados é usar CTAS.Sometimes the best approach for deleting data is to use CTAS. Em vez de excluir os dados, simplesmente selecione os dados que você deseja manter.Rather than deleting the data simply select the data you want to keep. Isso é verdadeiro principalmente para instruções DELETE que usam a sintaxe de junção ANSI, pois o SQL Data Warehouse não é compatível com junções ANSI na cláusula FROM de uma instrução 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.

Um exemplo de uma instrução DELETE convertida está disponível abaixo: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. Usar CTAS para simplificar instruções de mesclagemUse CTAS to simplify merge statements

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

As instruções de mesclagem podem ser substituídas, pelo menos em parte, usando CTAS.Merge statements can be replaced, at least in part, by using CTAS. Você pode consolidar INSERT e UPDATE em uma única instrução.You can consolidate the INSERT and the UPDATE into a single statement. Os registros excluídos precisariam ser separados em uma segunda instrução.Any deleted records would need to be closed off in a second statement.

Um exemplo de um UPSERT está disponível abaixo: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.[DimProduct_upsert]  TO [DimProduct];

M.M. Declarar explicitamente o tipo de dados e a nulidade de saídaExplicitly state data type and nullability of output

Aplica-se a: SQL Data Warehouse do Azure e Parallel Data WarehouseApplies to: Azure SQL Data Warehouse and Parallel Data Warehouse

Ao migrar o código do SQL Server para o SQL Data Warehouse, você poderá encontrar este tipo de padrão de codificação: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
;

Instintivamente você poderá pensar que deve migrar este código para um CTAS, e isso estará correto.Instinctively you might think you should migrate this code to a CTAS and you would be correct. No entanto, há um problema oculto aqui.However, there is a hidden issue here.

O código a seguir NÃO produz o mesmo resultado: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
;

Observe que a coluna "resultado" transfere os valores de tipo de dados e de nulidade da expressão.Notice that the column "result" carries forward the data type and nullability values of the expression. Se você não tiver cuidado, isso poderá levar a variações sutis nos valores.This can lead to subtle variances in values if you aren't careful.

Tente o seguinte como exemplo:Try the following as an example:

SELECT result,result*@d
from result
;

SELECT result,result*@d
from ctas_r
;

O valor armazenado para o resultado é diferente.The value stored for result is different. Como o valor persistente na coluna de resultado é usado em outras expressões, o erro torna-se ainda mais significativo.As the persisted value in the result column is used in other expressions the error becomes even more significant.

Resultados de CREATE TABLE AS SELECT

Isso é importante principalmente para migrações de dados.This is particularly important for data migrations. Embora a segunda consulta seja indiscutivelmente mais precisa, há um problema.Even though the second query is arguably more accurate there is a problem. Os dados seriam diferentes em comparação com o sistema de origem, o que levantaria dúvidas sobre a integridade da migração.The data would be different compared to the source system and that leads to questions of integrity in the migration. Este é um dos raros casos em que a resposta "errada" é, na verdade, a melhor!This is one of those rare cases where the "wrong" answer is actually the right one!

O motivo pelo qual existe essa discrepância entre os dois resultados, ocorre devido à conversão de tipo implícito.The reason we see this disparity between the two results is down to implicit type casting. No primeiro exemplo, a tabela define a definição de coluna.In the first example the table defines the column definition. Quando a linha é inserida, ocorre uma conversão implícita de tipo.When the row is inserted an implicit type conversion occurs. No segundo exemplo não há nenhuma conversão implícita de tipo, pois a expressão define o tipo de dados da coluna.In the second example there is no implicit type conversion as the expression defines data type of the column. Observe também que a coluna no segundo exemplo foi definida como uma coluna que não permite valor nulo, mas no primeiro exemplo ela não foi.Notice also that the column in the second example has been defined as a NULLable column whereas in the first example it has not. Quando a tabela foi criada no primeiro exemplo, a nulidade da coluna foi definida explicitamente.When the table was created in the first example column nullability was explicitly defined. No segundo exemplo, isso ficou a cargo da expressão, o que, por padrão, resultaria em uma definição NULL.In the second example it was just left to the expression and by default this would result in a NULL definition.

Para resolver esses problemas, você precisa definir explicitamente a conversão de tipo e a nulidade na parte SELECT da instrução CTAS.To resolve these issues you must explicitly set the type conversion and nullability in the SELECT portion of the CTAS statement. Não é possível definir essas propriedades na parte create table.You cannot set these properties in the create table part.

O exemplo abaixo demonstra como corrigir o código: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

Observe o seguinte:Note the following:

  • CAST ou CONVERT poderia ter sido usadoCAST or CONVERT could have been used
  • ISNULL é usado para forçar a nulidade, não COALESCEISNULL is used to force NULLability not COALESCE
  • ISNULL é a função mais distanteISNULL is the outermost function
  • A segunda parte de ISNULL é uma constante, ou seja, 0The second part of the ISNULL is a constant i.e. 0

Observação

Para que a nulidade seja definida corretamente, é essencial usar ISNULL e não COALESCE.For the nullability to be correctly set it is vital to use ISNULL and not COALESCE. COALESCE não é uma função determinística, portanto, o resultado da expressão sempre permitirá valor nulo.COALESCE is not a deterministic function and so the result of the expression will always be NULLable. ISNULL é diferente.ISNULL is different. Ele é determinístico.It is deterministic. Portanto, quando a segunda parte da função ISNULL for uma constante ou um literal, o valor resultante será 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.

Além dessa dica ser útil para garantir a integridade dos cálculos,This tip is not just useful for ensuring the integrity of your calculations. ela também é importante para a alternância de partição de tabela.It is also important for table partition switching. Imagine que você tenha esta tabela definida como o fato: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
                    )
                )
)
;

No entanto, o campo de valor é uma expressão calculada, ele não faz parte dos dados de origem.However, the value field is a calculated expression it is not part of the source data.

Para criar o conjunto de dados particionado, é possível fazer o seguinte: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')
;

A consulta seria executada de forma perfeitamente normal.The query would run perfectly fine. O problema aparece quando você tenta executar a alternância de partição.The problem comes when you try to perform the partition switch. As definições de tabela não correspondem.The table definitions do not match. Para fazer com que as definições da tabela correspondam, o CTAS precisa ser modificado.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');

Portanto, veja que manter a consistência de tipo e manter as propriedades de nulidade em um CTAS são uma prática recomendada de engenharia.You can see therefore that type consistency and maintaining nullability properties on a CTAS is a good engineering best practice. Isso ajuda a manter a integridade em seus cálculos e também garante que a alternância de partição seja possível.It helps to maintain integrity in your calculations and also ensures that partition switching is possible.

N.N. Criar um índice columnstore clusterizado ordenado com MAXDOP 1Create an ordered clustered columnstore index with MAXDOP 1

CREATE TABLE Table1 WITH (DISTRIBUTION = HASH(c1), CLUSTERED COLUMNSTORE INDEX ORDER(c1) )
AS SELECT * FROM ExampleTable
OPTION (MAXDOP 1);

Consulte TambémSee 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)
CREATE EXTERNAL TABLE AS SELECT (Transact-SQL) CREATE EXTERNAL TABLE AS SELECT (Transact-SQL)
CREATE TABLE (SQL Data Warehouse do Azure) DROP TABLE (Transact-SQL) CREATE TABLE (Azure SQL Data Warehouse) DROP TABLE (Transact-SQL)
DROP EXTERNAL TABLE (Transact-SQL) DROP EXTERNAL TABLE (Transact-SQL)
ALTER TABLE (Transact-SQL) ALTER TABLE (Transact-SQL)
ALTER EXTERNAL TABLE (Transact-SQL)ALTER EXTERNAL TABLE (Transact-SQL)