Exercício - CRIAR TABELA EXTERNA COMO SELEÇÃO

10 minutos

Neste exercício, utiliza o CREATE EXTERNAL TABLE AS SELECT (CETAS) para:

Exporte uma tabela como Parquet.
Transfira dados frios de um banco de dados para o armazenamento.
Crie uma tabela externa para acessar os dados externos exportados.
Utilize visões ou pesquisa com caracteres universais como estratégias de consulta.
Limite as consultas usando a eliminação de pastas e informações de metadados para melhorar o desempenho.

Pré-requisitos

Uma instância do SQL Server 2025 com conectividade com a Internet e o recurso PolyBase Query Service for External Data instalado e habilitado como para exercícios anteriores.
A base de dados de exemplo AdventureWorks2022 foi restaurada no seu servidor para utilização com dados de amostra.
Uma conta de Armazenamento do Azure com um contentor de Blob chamado data foi criada. Para criar o armazenamento, consulte Guia de início rápido: carregar, baixar e listar blobs com o portal do Azure.
A função Colaborador de Dados de Blobs de Armazenamento do controle de acesso baseado em função (RBAC) do Azure atribuída no Azure. Para obter mais informações, consulte Atribuir uma função do Azure para acesso a dados de blob.
Um token SAS de contentor de blob com permissões READ, WRITE, LIST e CREATE a ser usado para CETAS. Para criar o token SAS, consulte Criar tokens de assinatura de acesso compartilhado (SAS) para seus contêineres de armazenamento.

Utilizar o CETAS para exportar uma tabela em formato Parquet

Imagine que você trabalha 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 de Armazenamento de Blob do Azure. Eles desejam 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 seguinte consulta de exploração de dados para entender quais dados você deseja exportar. Nesse caso, você está procurando dados 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 com âmbito da base de dados e a fonte de dados externa. Substitua os espaços reservados <sas_token> e <storageccount> pela conta de armazenamento e token SAS que você criou 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 o CETAS. A consulta a seguir cria uma tabela externa chamada ext_data_2011_2012 e exporta todos os dados de 2011 e 2012 para o local especificado pela fonte de dados ABS_Data.

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 seu Armazenamento de Blob do Azure no portal do Azure. Você deve ver a seguinte estrutura criada. O SQL Server 2025 cria automaticamente o nome do arquivo com base na quantidade de dados exportada e no formato do arquivo.
Agora você pode acessar a tabela externa como uma tabela regular.
```
SELECT * FROM ex_data_2011_2012
```

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

Utilizar o CETAS para mover dados frios para fora da base 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.

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

Primeiro, clone a tabela original, porque você deseja simular a exportação e 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]

A partir da 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 vão para uma pasta chamada 2011, e 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. Em seguida, abra Bancos de Dados>AdventureWorks2022>Tabelas>Tabelas Externas para ver as tabelas externas.
Confirme se as seguintes pastas aparecem no contêiner de Armazenamento do Azure:
Depois que os dados frios forem exportados, você poderá excluí-los do local original da tabela.
```
DELETE FROM [PURCHASING].[PURCHASEORDERDETAIL_2] 
WHERE YEAR([DUEDATE]) < 2014
```

Dados de consulta que incluem a tabela externa

Você pode usar uma vista ou uma pesquisa curinga para consultar os dados exportados externamente. Cada método tem vantagens e desvantagens. O método view é 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 coringa é mais flexível e mais fácil de usar para fins de exploração.

Usar um modo de 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 o modo de exibição recém-criado, 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 um modo de 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 de T-SQL a seguir usa OPENROWSET para pesquisar arquivos Parquet na fonte de dados ABS_Data, incluindo suas subpastas.

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]

Eliminação de pastas e informações de metadados

As tabelas externas e OPENROWSET podem usar a função filepath para coletar e filtrar informações com base em metadados de arquivo. A função filepath retorna caminhos completos, nomes de pastas e nomes de arquivos. 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 ecrã do SSMS a mostrar 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, você pode 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 pastas, sua consulta acessa apenas as pastas necessárias em vez de verificar toda a fonte de dados, produzindo um melhor desempenho de consulta. Tenha essas informações em mente ao projetar 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 todos os outros.

Como nenhum dado é armazenado no banco de dados, o administrador do banco de dados não precisa projetar 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, entre outros, 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, vistas, pesquisa com caracteres curinga e funções de caminho de ficheiro. Você pode acessar e exportar dados de outros bancos de dados, como SQL Server, Oracle, Teradata e MongoDB, ou do Armazenamento de Blob do Azure, do Armazenamento do Azure Data Lake ou de qualquer armazenamento de objetos compatível com o S3. O CETAS pode ajudá-lo a projetar soluções de desempenho, duráveis e escaláveis em todas as fontes de dados suportadas pelo PolyBase.

Comentários

Esta página foi útil?