Criar e alterar tabelas externas do Armazenamento do Microsoft Azure

Aplica-se a: ✅Microsoft Fabric✅Azure Data Explorer

Os comandos neste artigo podem ser usados para criar ou alterar uma tabela externa do Armazenamento do Azure no banco de dados do qual o comando é executado. Uma tabela externa do Armazenamento do Azure faz referência a dados localizados no Armazenamento de Blobs do Azure, no Azure Data Lake Store Gen1 ou no Azure Data Lake Store Gen2.

Permissões

Requer .create pelo menos permissões de usuário de banco de dados e requer .alter pelo menos permissões de administrador de tabela.

Para .create-or-alter uma tabela externa usando a autenticação de identidade gerenciada, são necessárias permissões AllDatabasesAdmin .

Sintaxe

(.create.alter | | .create-or-alter) external table TableName( Schema) kind = storage [partition(by Partições) [pathformat = (PathFormat)]] = dataformat DataFormat (StorageConnectionString [, ...] ) with([ Propriedade [, ...])]

Saiba mais sobre as convenções de sintaxe.

Parâmetros

Autenticação e autorização

O método de autenticação para acessar uma tabela externa é baseado na cadeia de conexão fornecida durante sua criação, e as permissões necessárias para acessar a tabela variam dependendo do método de autenticação.

A tabela a seguir lista os métodos de autenticação com suporte para tabelas externas do Armazenamento do Azure e as permissões necessárias para ler ou gravar na tabela.

Formatação de partições

A lista de partições é qualquer combinação de colunas de partição, especificada usando um dos formulários mostrados na tabela a seguir.

Formato do caminho

O parâmetro PathFormat permite que você especifique o formato para o caminho do URI da pasta de dados externos, além das partições. Consiste em uma sequência de elementos de partição e separadores de texto. Um elemento de partição refere-se a uma partição declarada na cláusula de partição by e o separador de texto é qualquer texto entre aspas. Elementos de partição consecutivos devem ser separados usando o separador de texto.

[ StringSeparator ] Partição [ StringSeparator ] [Partição [ StringSeparator ] ...]

Para construir o prefixo do caminho do arquivo original, os elementos de partição são renderizados como strings e separados com separadores de texto correspondentes. Você pode usar a datetime_pattern macro (datetime_pattern(DateTimeFormat, PartitionName)) para especificar o formato usado para renderizar um valor de partição datetime. A macro segue a especificação de formato .NET e permite que os especificadores de formato sejam colocados entre chaves. Por exemplo, os dois formatos a seguir são equivalentes:

• 'ano='aaaa'/mês='MM
• ano={aaaa}/mês={MM}

Por padrão, os valores de data e hora são renderizados usando os seguintes formatos:

Colunas virtuais

Quando os dados são exportados do Spark, as colunas de partição (que são fornecidas ao método do gravador de dataframe) partitionBy não são gravadas em arquivos de dados. Esse processo evita a duplicação de dados porque os dados já estão presentes nos nomes de pasta (por exemplo, column1=<value>/column2=<value>/), e o Spark pode reconhecê-los na leitura.

As tabelas externas suportam a leitura desses dados na forma de virtual colums. As colunas virtuais podem ser do tipo string ou datetime, e são especificadas usando a seguinte sintaxe:

.create external table ExternalTable (EventName:string, Revenue:double)  
kind=storage  
partition by (CustomerName:string, Date:datetime)  
pathformat=("customer=" CustomerName "/date=" datetime_pattern("yyyyMMdd", Date))  
dataformat=parquet
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)

Para filtrar por colunas virtuais em uma consulta, especifique nomes de partição no predicado de consulta:

external_table("ExternalTable")
 | where Date between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Propriedades opcionais

Lógica de filtragem de arquivos

Ao consultar uma tabela externa, o desempenho é aprimorado filtrando arquivos de armazenamento externo irrelevantes. O processo de iteração de arquivos e decisão se um arquivo deve ser processado é o seguinte:

1. Crie um padrão de URI que represente um local onde os arquivos são encontrados. Inicialmente, o padrão de URI é igual a uma cadeia de conexão fornecida como parte da definição da tabela externa. Se houver partições definidas, elas serão renderizadas usando PathFormat e, em seguida, acrescentadas ao padrão de URI.

2. Para todos os arquivos encontrados no(s) padrão(ões) de URI criado(s), verifique se:

   • Os valores de partição correspondem aos predicados usados em uma consulta.
   • O nome do blob começa com NamePrefix, se essa propriedade for definida.
   • O nome do blob termina com FileExtension, se essa propriedade for definida.

Depois que todas as condições forem atendidas, o arquivo será buscado e processado.

Exemplos

Tabela externa não particionada

Na tabela externa não particionada a seguir, espera-se que os arquivos sejam colocados diretamente sob os contêineres definidos:

.create external table ExternalTable (x:long, s:string)  
kind=storage 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
) 

Particionado por data

Na tabela externa a seguir particionada por data, espera-se que os arquivos sejam colocados em diretórios do formato yyyy/MM/ddde data e hora padrão:

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage
partition by (Date:datetime = bin(Timestamp, 1d)) 
dataformat=csv 
( 
   h@'abfss://filesystem@storageaccount.dfs.core.windows.net/path;secretKey'
)

Particionado por mês

Na tabela externa a seguir particionada por mês, o formato do diretório é year=yyyy/month=MM:

.create external table ExternalTable (Timestamp:datetime, x:long, s:string) 
kind=storage 
partition by (Month:datetime = startofmonth(Timestamp)) 
pathformat=(datetime_pattern("'year='yyyy'/month='MM", Month)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
) 

Particionado por nome e data

Na tabela externa a seguir, os dados são particionados primeiro pelo nome do cliente e depois pela data, o que significa que a estrutura de diretório esperada é, por exemplo, customer_name=Softworks/2019/02/01:

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerNamePart:string = CustomerName, Date:datetime = startofday(Timestamp)) 
pathformat=("customer_name=" CustomerNamePart "/" Date)
dataformat=csv 
(  
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey' 
)

Particionado por hash e data

A tabela externa a seguir é particionada primeiro pelo hash do nome do cliente (módulo dez) e, em seguida, por data. A estrutura de diretório esperada é, por exemplo, customer_id=5/dt=20190201, , e os nomes dos arquivos de dados terminam com a .txt extensão:

.create external table ExternalTable (Timestamp:datetime, CustomerName:string) 
kind=storage 
partition by (CustomerId:long = hash(CustomerName, 10), Date:datetime = startofday(Timestamp)) 
pathformat=("customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd", Date)) 
dataformat=csv 
( 
   h@'https://storageaccount.blob.core.windows.net/container1;secretKey'
)
with (fileExtension = ".txt")

Filtrar por colunas de partição em uma consulta

Para filtrar por colunas de partição em uma consulta, especifique o nome da coluna original no predicado de consulta:

external_table("ExternalTable")
 | where Timestamp between (datetime(2020-01-01) .. datetime(2020-02-01))
 | where CustomerName in ("John.Doe", "Ivan.Ivanov")

Saída de exemplo

Conteúdo relacionado

• Exporte dados para uma tabela externa.

• Exportação contínua de dados para uma tabela externa.