Criar e alterar tabelas externas do Armazenamento do Microsoft Azure
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 em 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 .create
comando falhará com um erro. Use .create-or-alter
ou .alter
para modificar tabelas existentes.
Permissões
Para .create
requer pelo menos permissões de Usuário de Banco de Dados e para .alter
requer pelo menos permissões de Administração de Tabela.
Para .create-or-alter
uma tabela externa usando a autenticação de identidade gerenciada, é necessário ter permissões AllDatabasesAdmin .
Syntax
(.create
| .create-or-alter
.alter
| ) external
table
Esquema TableName(
)
=
kind
storage
[partition
(
by
Partições )
[pathformat
=
(
PathFormat)
]]dataformat
=
DataFormat (
StorageConnectionString [,
...] )
[with
(
Propriedade [,
...])
]
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 as convenções de sintaxe.
Parâmetros
Nome | Tipo | Obrigatório | Descrição |
---|---|---|---|
TableName | string |
✔️ | Um nome de tabela externa que segue as regras de nomes de entidade . Uma tabela externa não pode ter o mesmo nome que uma tabela regular 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, em que 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. Confira a formatação de partições para saber como esse valor deve ser. | |
PathFormat | string |
Um formato de caminho de URI de pasta de dados externo a ser usado com partições. Confira o formato do caminho. | |
DataFormat | string |
✔️ | O formato de dados, que pode ser qualquer um dos formatos de ingestão. É recomendável usar o Parquet formato para tabelas externas para melhorar o desempenho de consulta e exportação, a menos que você use JSON o mapeamento de caminhos. Ao usar uma tabela externa para o cenário de exportação, você está limitado aos seguintes formatos: CSV , JSON TSV e Parquet . |
StorageConnectionString | string |
✔️ | Um ou mais caminhos separados por vírgulas para Armazenamento de Blobs do Azure contêineres de blob, 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. |
Propriedade | 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. É recomendável 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 baseia-se no cadeia de conexão fornecido 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 | Armazenamento de Blobs do Azure/Data Lake Storage Gen2 | Data Lake Storage Gen1 |
---|---|---|
Representação | Permissões de leitura: Leitor de Dados de Blob de Armazenamento Permissões de gravação: Colaborador de Dados de Blob de Armazenamento |
Permissões de leitura: Leitor Permissões de gravação: Contribuinte |
Identidade gerenciada | Permissões de leitura: Leitor de Dados de Blob de Armazenamento Permissões de gravação: Colaborador de Dados de Blob de Armazenamento |
Permissões de leitura: Leitor Permissões de gravação: Contribuinte |
Token DE ACESSO Compartilhado (SAS) | Permissões de leitura: Lista + Leitura Permissões de gravação: Escrever |
Não há suporte para esse método de autenticação no Gen1. |
Microsoft Entra token de acesso | Nenhuma permissão adicional é necessária. | Nenhuma permissão adicional é necessária. |
Chave de acesso da conta de armazenamento | Nenhuma permissão adicional é necessária. | Não há suporte para esse método de autenticação 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 | Syntax | Observações |
---|---|---|
Coluna virtual | Partitionname: (datetime | string ) |
Leia mais sobre colunas virtuais. |
Valor da coluna de cadeia de caracteres | Partitionname: string = Columnname |
|
Hash do valor da coluna de cadeia de caracteres | Partitionname: long = hash( Columnname, Número) |
O hash é modulo Number. |
Coluna datetime truncada (valor) | Partitionname: datetime = (startofyear | startofday startofweek startofmonth | | ) ( ColumnName) |
Consulte a documentação sobre as funções startofyear, startofmonth, startofweek ou startofday . |
Valor da coluna Datetime truncada (compartimento) | Partitionname: datetime = bin ( Columnname, Timespan) |
Leia mais sobre a função bin . |
Formato do caminho
O parâmetro PathFormat permite que você especifique o formato para o caminho de URI da pasta de dados externa, além de partições. Ele 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 de caminho de arquivo original, os elementos de partição são renderizados como cadeias de caracteres e separados por 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 colchetes. Por exemplo, os dois formatos a seguir são equivalentes:
- 'year='yyyy'/month='MM
- year={yyyy}/month={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 marcar a correção da definição de Partições e PathFormat, 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 (fornecidas para o método do gravador de partitionBy
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.
As tabelas externas dão suporte à 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
Propriedade | Tipo | Descrição |
---|---|---|
folder |
string |
Pasta da tabela |
docString |
string |
Cadeia de caracteres documentando a tabela |
compressed |
bool |
Se definido, indica se os arquivos são compactados como .gz arquivos (usados somente no cenário de exportação ) |
includeHeaders |
string |
Para formatos de texto delimitados (CSV, TSV, ...), indica 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, indica o prefixo dos arquivos. Em operações de gravação, todos os arquivos serão gravados com esse prefixo. Em operações de leitura, somente os arquivos com esse prefixo são lidos. |
fileExtension |
string |
Se definido, indica extensões de arquivo dos arquivos. Na gravação, os nomes dos arquivos terminarão com esse sufixo. Na leitura, somente os arquivos com essa extensão de arquivo serão lidos. |
encoding |
string |
Indica 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 definido, uma das tabelas de resultados de comando contém uma visualização do comando .show external table artifacts . Assim como sampleUri , a opção ajuda a validar os parâmetros Partitions e PathFormat da definição de tabela externa. |
validateNotEmpty |
bool |
Se definido, as cadeias de conexão serão validadas para ter conteúdo nelas. O comando falhará se o local do URI especificado não existir ou se não houver permissões suficientes para acessá-lo. |
dryRun |
bool |
Se definido, a definição de tabela externa não será persistida. Essa opção é útil para validar a definição de tabela externa, especialmente em conjunto com o filesPreview parâmetro ou sampleUris . |
Observação
A tabela externa não é acessada durante a criação, somente durante a consulta e a exportação. Use a propriedade opcional durante a validateNotEmpty
criação para verificar se a definição da tabela é válida e se o armazenamento está acessível.
Dica
Para saber mais sobre a função namePrefix
e fileExtension
as propriedades desempenhadas na filtragem de arquivo de dados durante a consulta, consulte a seção lógica de filtragem de arquivos .
Lógica de filtragem de arquivos
Ao consultar uma tabela externa, o desempenho é aprimorado filtrando arquivos de armazenamento externo irrelevantes. O processo de iterar arquivos e decidir se um arquivo deve ser processado é o seguinte:
Crie um padrão de URI que representa um local onde os arquivos são encontrados. Inicialmente, o padrão de URI é igual a um cadeia de conexão fornecido como parte da definição de tabela externa. Se houver partições definidas, elas serão renderizadas usando PathFormat e acrescentadas ao padrão de URI.
Para todos os arquivos encontrados nos padrões de URI criados, marcar que:
- 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 terminará com
FileExtension
, se essa propriedade for definida.
Depois que todas as condições forem atendidas, o arquivo será buscado e processado.
Observação
O padrão de URI inicial é criado usando valores de predicado 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 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/dd
datetime 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 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, em seguida, por 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 de 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 de arquivo 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
TableName | TableType | Pasta | DocString | Propriedades | ConnectionStrings | Partições | PathFormat |
---|---|---|---|---|---|---|---|
ExternalTable | Blob | ExternalTables | Docs | {"Format":"Csv","Compressed":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",Date) |
Conteúdo relacionado
Comentários
https://aka.ms/ContentUserFeedback.
Em breve: Ao longo de 2024, eliminaremos os problemas do GitHub como o mecanismo de comentários para conteúdo e o substituiremos por um novo sistema de comentários. Para obter mais informações, consulteEnviar e exibir comentários de