Exercício – CREATE EXTERNAL TABLE AS SELECT

10 minutos

Neste exercício, você usará CREATE EXTERNAL TABLE AS SELECT (CETAS) para:

Exportar uma tabela como Parquet.
Mover os dados frios de um banco de dados para o armazenamento.
Crie uma tabela externa para acessar os dados externos exportados.
Use exibições ou pesquisa curinga como estratégias de consulta.
Limite as consultas usando informações de eliminação de pastas e metadados para melhorar o desempenho.

Pré-requisitos

Uma instância do SQL Server 2025 com conectividade com a Internet e o recurso Serviço de Consulta do PolyBase para Dados Externos instalado e habilitado como para exercícios anteriores.
O banco de dados de exemplo AdventureWorks2022 restaurado para o servidor a ser usado para dados de exemplo.
Uma conta de Armazenamento do Azure com um contêiner de Armazenamento de Blobs nomeado data criado. Para criar o armazenamento, consulte Início Rápido: Carregar, baixar e listar blobs com o portal do Azure.
A função de Colaborador de Dados de Blobs de Armazenamento do RBAC (controle de acesso baseado em função) do Azure atribuída no Azure. Para obter mais informações, consulte Atribuir uma função do Azure para acesso aos dados de blob.
Um token SAS de contêiner de blob com permissões READ, WRITE, LIST e CREATE a serem usadas para CETAS. Para criar o token SAS, consulte Criar tokens sas (assinatura de acesso compartilhado) para seus contêineres de armazenamento.

Usar o CETAS para exportar uma tabela como Parquet

Imagine que você trabalhe com uma equipe de análise de negócios que deseja exportar dados anteriores a 2012 de uma tabela do SQL Server para um contêiner do Armazenamento de Blobs do Azure. Eles querem executar suas consultas de relatório nesses dados exportados em vez de consultar diretamente o SQL Server.

Habilite o CETAS na instância do SQL Server.

EXEC SP_CONFIGURE @CONFIGNAME = 'ALLOW POLYBASE EXPORT', @CONFIGVALUE = 1;

Captura de tela da configuração da opção para permitir a exportação do PolyBase.

Execute a consulta de exploração de dados a seguir para entender quais dados você deseja exportar. Nesse caso, você está procurando dados que sejam de 2012 ou anteriores. Você deseja exportar todos os dados de 2011 e 2012.
```
-- RECORDS BY YEARS
SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]
```

Crie uma chave mestra de banco de dados para o banco de dados, como nos exercícios anteriores.

Use AdventureWorks2022

DECLARE @randomWord VARCHAR(64) = NEWID();
DECLARE @createMasterKey NVARCHAR(500) = N'
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = ''##MS_DatabaseMasterKey##'')
    CREATE MASTER KEY ENCRYPTION BY PASSWORD = '  + QUOTENAME(@randomWord, '''')
EXEC sp_executesql @createMasterKey;

SELECT * FROM sys.symmetric_keys;

Crie as credenciais no escopo do banco de dados e a fonte de dados externa. Substitua os <sas_token> espaços reservados e <storageccount> os espaços reservados pela conta de armazenamento e pelo token SAS criado no Azure.

-- DATABASE SCOPED CREDENTIAL
CREATE DATABASE SCOPED CREDENTIAL blob_storage
      WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
      SECRET = '<sas_token>';

-- AZURE BLOB STORAGE DATA SOURCE
CREATE EXTERNAL DATA SOURCE ABS_Data
WITH
(
 LOCATION = 'abs://<storageaccount>.blob.core.windows.net/data/chapter3'
,CREDENTIAL = blob_storage
);

Crie o formato de arquivo externo para Parquet.

-- PARQUET FILE FORMAT
CREATE EXTERNAL FILE FORMAT ffParquet
WITH (FORMAT_TYPE = PARQUET);

Crie a tabela externa usando CETAS. A consulta a seguir cria uma tabela externa nomeada ext_data_2011_2012 e exporta todos os dados de 2011 e 2012 para o local especificado pela fonte ABS_Datade dados.

CREATE EXTERNAL TABLE ex_data_2011_2012
WITH(
        LOCATION = 'data_2011_20122',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
	[PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL] 
WHERE YEAR([DUEDATE]) < 2013
GO

Verifique o Armazenamento de Blobs do Azure no portal do Azure. Você deve ver a estrutura a seguir criada. O SQL Server 2025 cria automaticamente o nome do arquivo com base na quantidade de dados exportadas e no formato de arquivo.
Agora você pode acessar a tabela externa como uma tabela regular.
```
SELECT * FROM ex_data_2011_2012
```

Os dados agora são exportados para Parquet e são facilmente acessíveis por meio da tabela externa. A equipe de análise de negócios pode consultar a tabela externa ou apontar sua ferramenta de relatório no arquivo Parquet.

Usar o CETAS para mover dados frios para fora do banco de dados

Para manter os dados gerenciáveis, sua empresa decide mover dados anteriores a 2014 do banco de dados do SQL Server. No entanto, todos os dados ainda devem estar acessíveis.

Para este exemplo, você exporta os dados por meio do CETAS e gera várias tabelas externas que podem ser consultadas posteriormente. Você pode usar uma exibição com instruções UNION para consultar os dados ou criar uma única tabela externa e usar um curinga para pesquisar pelas subpastas dos dados exportados.

Primeiro, clone a tabela original, pois você deseja simular a exportação e a remoção dos dados, mas não necessariamente deseja excluir a fonte de dados atual. Execute a seguinte instrução:

-- CLONE TABLE
SELECT * INTO [PURCHASING].[PURCHASEORDERDETAIL_2] FROM [PURCHASING].[PURCHASEORDERDETAIL]

Na primeira consulta de exploração de dados, você sabe que há 5551 registros de 2014. Tudo antes de 2014 deve ser exportado para uma pasta identificada por ano. Os dados de 2011 entram em uma pasta chamada 2011e assim por diante.

Para criar as tabelas externas, execute os seguintes comandos:

CREATE EXTERNAL TABLE ex_2011
WITH(
        LOCATION = '2011',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2011;

CREATE EXTERNAL TABLE ex_2012
WITH(
        LOCATION = '2012',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2012;

CREATE EXTERNAL TABLE ex_2013
WITH(
        LOCATION = '2013',
        DATA_SOURCE = ABS_Data,
        FILE_FORMAT = ffParquet
)AS
SELECT
    [PurchaseOrderID]
      ,[PurchaseOrderDetailID]
      ,[DueDate]
      ,[OrderQty]
      ,[ProductID]
      ,[UnitPrice]
      ,[LineTotal]
      ,[ReceivedQty]
      ,[RejectedQty]
      ,[StockedQty]
      ,[ModifiedDate]
FROM  [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) = 2013;

Depois de executar esses comandos, atualize o Pesquisador de Objetos do SSMS. Emseguida, abra tabelas externas de tabelasadventureworks2022 de>> bancos> de dados para ver as tabelas externas.
Confirme se as seguintes pastas aparecem no contêiner do Armazenamento do Azure:
Depois que os dados frios forem exportados, você poderá excluí-los do local da tabela original.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Consultar dados que incluem a tabela externa

Você pode usar uma exibição ou uma pesquisa curinga para consultar os dados externos exportados. Cada método tem vantagens e desvantagens. O método de exibição é recomendado para solicitações repetitivas porque geralmente tem um desempenho melhor e também pode ser combinado com tabelas físicas. O método de pesquisa curinga é mais flexível e fácil de usar para fins de exploração.

Usar uma exibição para consultar os dados

Agora que os dados antigos são exportados e excluídos do banco de dados, você pode usar o T-SQL para criar uma exibição que consulta todas as tabelas externas e os dados atuais em seu banco de dados.

CREATE VIEW vw_purchaseorderdetail 
AS
SELECT * FROM ex_2011
UNION ALL
SELECT * FROM ex_2012
UNION ALL
SELECT * FROM ex_2013
UNION ALL
SELECT * FROM  [PURCHASING].[PURCHASEORDERDETAIL_2]

Você pode executar a consulta de exploração de dados original, desta vez usando a exibição recém-criada, para ver os mesmos resultados.

SELECT  COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM vw_purchaseorderdetail 
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Usar uma pesquisa curinga para consultar os dados

No exemplo anterior, você usou uma exibição com instruções UNION para unir as três tabelas externas. Outra maneira de obter os resultados desejados é usar uma pesquisa curinga para verificar a estrutura de pastas, incluindo subpastas, para quaisquer dados de um tipo específico.

O exemplo T-SQL a seguir usa OPENROWSET para pesquisar na ABS_Data fonte de dados, incluindo suas subpastas, para arquivos Parquet.

SELECT COUNT(*) AS QTY, DATEPART(YYYY, [DUEDATE]) AS [YEAR]
FROM OPENROWSET 
    (BULK '**'
    , FORMAT = 'PARQUET'
    , DATA_SOURCE = 'ABS_Data')
    AS [cc]
GROUP BY DATEPART(YYYY, [DUEDATE])
ORDER BY [YEAR]

Informações sobre eliminação de pastas e metadados

Ambas as tabelas externas e OPENROWSET podem usar a filepath função para coletar e filtrar informações com base em metadados de arquivo. A filepath função retorna caminhos completos, nomes de pasta e nomes de arquivo. Você pode usar essas informações para melhorar os recursos de pesquisa da tabela externa e dos comandos OPENROWSET.

SELECT
    r.filepath(1) 'folder_name'
    ,r.filepath() 'full_path'
    ,r.filepath(2) 'file_name'
FROM OPENROWSET(
        BULK '*/*.parquet',
        DATA_SOURCE = 'ABS_Data',
        FORMAT = 'parquet'
    ) as [r]
GROUP BY
    r.filepath(2),r.filepath(1), r.filepath()
ORDER BY 
    r.filepath(2)

Captura de tela do SSMS mostrando a função filepath.

Se você quiser recuperar dados de uma pasta específica e ainda usar a funcionalidade do método de pesquisa curinga, poderá usar a seguinte consulta:

SELECT  *
FROM OPENROWSET(
 BULK '*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('2011')

Os resultados finais são os mesmos, mas usando os metadados de eliminação de pasta, sua consulta acessa apenas as pastas necessárias em vez de verificar toda a fonte de dados, produzindo melhor desempenho de consulta. Tenha essas informações em mente ao criar arquiteturas de armazenamento para usar melhor os recursos do PolyBase.

Por exemplo, dada a seguinte arquitetura de pasta:

Captura de tela mostrando um exemplo de arquitetura de pasta em um contêiner de armazenamento.

Você pode usar a seguinte consulta:

SELECT  *
FROM OPENROWSET(
 BULK 'year=*/month=*/*.parquet',
DATA_SOURCE = 'ABS_Data',
FORMAT = 'parquet'
 ) AS r
WHERE
 r.filepath(1) IN ('<year>')
 r.filepath(2) IN ('<month>')

Para fins dessa consulta, não importa o tamanho da fonte de dados. O SQL Server carrega, lê e consulta apenas os dados da pasta selecionada, ignorando todas as outras.

Como nenhum dado é armazenado no banco de dados, o administrador do banco de dados não precisa criar uma estratégia específica para gerenciar esses dados. A empresa ainda deve tomar todas as precauções necessárias para manter os dados com segurança, incluindo, mas não se limitando a backups, disponibilidade e permissões.

Resumo

Neste exercício, você usou o CETAS para mover dados frios de um banco de dados para o Armazenamento do Azure e exportar uma tabela como formato de arquivo Parquet. Você aprendeu maneiras de consultar os dados externos para exploração e otimizar o desempenho.

Você pode usar o CETAS para combinar OPENROWSET, tabelas externas, exibições, pesquisa curinga e funções de caminho de arquivo. Você pode acessar e exportar dados de outros bancos de dados, como SQL Server, Oracle, Teradata e MongoDB, ou do Armazenamento de Blobs do Azure, do Azure Data Lake Storage ou de qualquer armazenamento de objetos compatível com S3. O CETAS pode ajudá-lo a criar soluções escalonáveis, duráveis e de desempenho em todas as fontes de dados com suporte do PolyBase.

Comentários

Esta página foi útil?