Partilhar via


Criar e alterar tabelas externas do Armazenamento do Azure

Aplica-se a: ✅Microsoft FabricAzure Data Explorer

Os comandos neste artigo podem ser usados para criar ou alterar um Armazenamento do Azure de tabela externa no banco de dados a partir 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.

Observação

Se a tabela existir, o comando .create falhará com um erro. Use .create-or-alter ou .alter para modificar tabelas existentes.

Permissões

Para .create requer pelo menos permissões de de Usuário do Banco de Dados e para .alter requer pelo menos permissões de de Administrador de Tabela.

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

Sintaxe

(.create | .alter | .create-or-alter) externaltableTableName(Schema)kind=storage [partitionby(Partitions) [pathformat=(PathFormat)]] dataformat=DataFormat(StorageConnectionString [, ...] ) [with(Property [, ...])]

Observação

kind é storage para todos os tipos de armazenamento de dados externos do Armazenamento do Azure. blob e adl são termos preteridos.

Saiba mais sobre convenções de sintaxe.

Parâmetros

Designação Tipo Necessário Descrição
TableName string ✔️ Um nome de tabela externa que adere aos nomes regras de entidade. Uma tabela externa não pode ter o mesmo nome que uma tabela normal no mesmo banco de dados.
Esquema string ✔️ O esquema de dados externos é uma lista separada por vírgulas de um ou mais nomes de coluna e tipos de dados, onde cada item segue o formato: ColumnName:ColumnType. Se o esquema for desconhecido, use infer_storage_schema para inferir o esquema com base no conteúdo do arquivo externo.
Partições string Uma lista separada por vírgulas de colunas pelas quais a tabela externa é particionada. A coluna de partição pode existir no próprio arquivo de dados ou como parte do caminho do arquivo. Consulte de formatação de partições para saber como esse valor deve parecer.
PathFormat string Um formato de caminho URI de pasta de dados externa para usar com partições. Consulte formato de caminho.
DataFormat string ✔️ O formato de dados, que pode ser qualquer um dos formatos de ingestão . Recomendamos o uso do formato Parquet para tabelas externas para melhorar o desempenho de consulta e exportação, a menos que você use JSON mapeamento de caminhos. Ao usar uma tabela externa para cenário de exportação, você está limitado aos seguintes formatos: CSV, TSV, JSON e Parquet.
StorageConnectionString string ✔️ Um ou mais caminhos separados por vírgulas para contêineres de blob do Armazenamento de Blob do Azure, sistemas de arquivos do Azure Data Lake Gen 2 ou contêineres do Azure Data Lake Gen 1, incluindo credenciais. O tipo de armazenamento de tabela externa é determinado pelas cadeias de conexão fornecidas. Consulte cadeias de conexão de armazenamento.
Property string Um par de propriedades chave-valor no formato PropertyName=PropertyValue. Consulte propriedades opcionais.

Observação

Arquivos CSV com esquema não idêntico podem resultar em dados que aparecem deslocados ou ausentes. Recomendamos separar arquivos CSV com esquemas distintos para separar contêineres de armazenamento e definir uma tabela externa para cada contêiner de armazenamento com o esquema adequado.

Dica

Forneça mais de uma única conta de armazenamento para evitar a limitação de armazenamento enquanto exporta grandes quantidades de dados para a tabela externa. A exportação distribuirá as gravações entre todas as contas fornecidas.

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.

Método de autenticação Azure Blob Storage / Data Lake Storage Gen2 Armazenamento Data Lake Gen1
de falsificação de identidade permissões de leitura: Storage Blob Data Reader
permissões de gravação: Contribuidor de dados de Blob de armazenamento
Permissões de leitura: Reader
Permissões de gravação: Colaborador
Identidade gerenciada permissões de leitura: Storage Blob Data Reader
permissões de gravação: Contribuidor de dados de Blob de armazenamento
Permissões de leitura: Reader
Permissões de gravação: Colaborador
de token de Acesso Compartilhado (SAS) Permissões de leitura: Lista de + Ler
Permissões de gravação: Write
Este método de autenticação não é suportado no Gen1.
de token de acesso do Microsoft Entra Não são necessárias permissões adicionais. Não são necessárias permissões adicionais.
Chave de acesso da conta de armazenamento Não são necessárias permissões adicionais. Este método de autenticação não é suportado no Gen1.

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.

Tipo de partição Sintaxe Observações
Coluna virtual : PartitionName (datetime | string) Leia mais em colunas virtuais.
Valor da coluna de cadeia de caracteres PartitionName:string=ColumnName
Valor da coluna de cadeia de caracteres hash() PartitionName:long=hash(ColumnName,Número) O hash é modulo Número.
Coluna datetime truncada (valor) PartitionName:datetime= (startofyear | startofmonth | startofweek | startofday) (ColumnName) Consulte a documentação sobre de início de ano, de início de mês, de início de semana ou funções de de início de dia.
Valor da coluna Datetime truncado =bin(ColumnName,TimeSpan) Leia mais sobre a função do compartimento de.

Formato do caminho

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

[ StringSeparator ] Partition [ StringSeparator ] [Partition [ StringSeparator ] ...]

Para construir o prefixo do caminho do arquivo original, os elementos da partição são renderizados como cadeias de caracteres e separados com separadores de texto correspondentes. Você pode usar a macro datetime_pattern (datetime_pattern(DateTimeFormat,PartitionName)) para especificar o formato usado para renderizar um valor de partição datetime. A macro adere à especificação de formato .NET e permite que os especificadores de formato sejam colocados entre colchetes. 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 datetime são renderizados usando os seguintes formatos:

Função de partição Formato padrão
startofyear yyyy
startofmonth yyyy/MM
startofweek yyyy/MM/dd
startofday yyyy/MM/dd
bin( Coluna, 1d) yyyy/MM/dd
bin( Coluna, 1h) yyyy/MM/dd/HH
bin( Coluna, 1m) yyyy/MM/dd/HH/mm

Dica

Para verificar de partições e PathFormat a correção da definição, use a propriedade sampleUris ou filesPreview ao criar uma tabela externa.

Colunas virtuais

Quando os dados são exportados do Spark, as colunas de partição (que são fornecidas ao método partitionBy do gravador de dataframe) 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 das pastas (por exemplo, column1=<value>/column2=<value>/), e o Spark pode reconhecê-los após a leitura.

Tabelas externas suportam a leitura desses dados na forma de virtual colums. As colunas virtuais podem ser do tipo string ou datetimee 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

Propriedade Tipo Descrição
folder string Pasta da tabela
docString string String documentando a tabela
compressed bool Apenas relevante para o cenário de exportação .
Se definido como true, os dados são exportados no formato especificado pela propriedade compressionType. Para o caminho de leitura, a compactação é detetada automaticamente.
compressionType string Apenas relevante para o cenário de exportação .
O tipo de compactação de arquivos exportados. Para arquivos que não sejam Parquet, apenas gzip é permitido. Para arquivos Parquet, os valores possíveis incluem gzip, snappy, lz4_raw, brotlie zstd. O padrão é gzip. Para o caminho de leitura, o tipo de compactação é detetado automaticamente.
includeHeaders string Para formatos de texto delimitados (CSV, TSV, ...), especifica se os arquivos contêm um cabeçalho. Os valores possíveis são: All (todos os arquivos contêm um cabeçalho), FirstFile (o primeiro arquivo em uma pasta contém um cabeçalho) None (nenhum arquivo contém um cabeçalho).
namePrefix string Se definido, especifica o prefixo dos arquivos. Nas operações de gravação, todos os arquivos serão gravados com esse prefixo. Em operações de leitura, somente arquivos com esse prefixo são lidos.
fileExtension string Se definido, especifica a extensão dos arquivos. Na gravação, os nomes dos arquivos terminarão com esse sufixo. Na leitura, apenas os arquivos com esta extensão de arquivo serão lidos.
encoding string Especifica como o texto é codificado: UTF8NoBOM (padrão) ou UTF8BOM.
sampleUris bool Se definido, o resultado do comando fornece vários exemplos de URI de arquivos de dados externos simulados, conforme esperado pela definição de tabela externa. Essa opção ajuda a validar se os parâmetros Partitions e PathFormat estão definidos corretamente.
filesPreview bool Se definida, uma das tabelas de resultados do comando contém uma visualização de comando .show external table artifacts. Como sampleUri, a opção ajuda a validar o de partições e PathFormat parâmetros de definição de tabela externa.
validateNotEmpty bool Se definidas, as cadeias de conexão são validadas por terem conteúdo nelas. O comando falhará se o local de URI especificado não existir ou se não houver permissões suficientes para acessá-lo.
dryRun bool Se definida, a definição de tabela externa não será persistida. Esta opção é útil para validar a definição de tabela externa, especialmente em conjunto com o parâmetro filesPreview ou sampleUris.

Observação

A tabela externa não é acessada durante a criação, apenas durante a consulta e exportação. Use a propriedade validateNotEmpty opcional durante a criação para garantir que a definição da tabela seja válida e que o armazenamento esteja acessível.

Dica

Para saber mais sobre a função que namePrefix e fileExtension propriedades desempenham na filtragem de arquivos de dados durante a consulta, consulte seção lógica de filtragem de arquivos.

Lógica de filtragem de ficheiros

Ao consultar uma tabela externa, o desempenho é melhorado filtrando arquivos de armazenamento externos irrelevantes. O processo de iteração de arquivos e decidir 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 URI é igual a uma cadeia de conexão fornecida como parte da definição de tabela externa. Se houver partições definidas, elas serão renderizadas usando PathFormate, em seguida, anexadas ao padrão URI.

  2. Para todos os arquivos encontrados sob o(s) padrão(s) 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 tal propriedade estiver definida.
    • O nome do blob termina com FileExtension, se tal propriedade estiver definida.

Uma vez que todas as condições são atendidas, o arquivo é buscado e processado.

Observação

O padrão de URI inicial é criado usando valores de predicados de consulta. Isso funciona melhor para um conjunto limitado de valores de cadeia de caracteres, bem como para intervalos de tempo fechados.

Exemplos

Tabela externa não particionada

Na seguinte tabela externa não particionada, espera-se que os arquivos sejam colocados diretamente sob o(s) contêiner(es) definido(s):

.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 seguinte tabela externa particionada por data, espera-se que os arquivos sejam colocados em diretórios do formato datetime padrão yyyy/MM/dd:

.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 seguinte tabela externa particionada por mês, o formato de 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órios 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, pela data. A estrutura de diretórios esperada é, por exemplo, customer_id=5/dt=20190201e os nomes de arquivos de dados terminam com a extensão .txt:

.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 da consulta:

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

Exemplo de saída

Nome da tabela Tipo de tabela Pasta DocString Propriedades ConnectionStrings Divisórias PathFormat
Tabela Externa Blob Tabelas Externas Documentos {"Format":"Csv","Compactado":false,"CompressionType":null,"FileExtension":null,"IncludeHeaders":"None","Encoding":null,"NamePrefix":null} ["https://storageaccount.blob.core.windows.net/container1;*******"] [{"Mod":10,"Name":"CustomerId","ColumnName":"CustomerName","Ordinal":0},{"Function":"StartOfDay","Name":"Date","ColumnName":"Timestamp","Ordinal":1}] "customer_id=" CustomerId "/dt=" datetime_pattern("yyyyMMdd",Data)