Como indexar dados do SQL do Azure no Azure AI Search
Neste artigo, saiba como configurar um indexador que importa conteúdo do Banco de Dados SQL do Azure ou de uma instância gerenciada do SQL do Azure e o torna pesquisável no Azure AI Search.
Este artigo complementa Criar um indexador com informações específicas do Azure SQL. Ele usa as APIs REST para demonstrar um fluxo de trabalho de três partes comum a todos os indexadores: criar uma fonte de dados, criar um índice, criar um indexador.
Este artigo também prevê:
Uma descrição das políticas de deteção de alterações suportadas pelo indexador SQL do Azure para que possa configurar a indexação incremental.
Uma seção de perguntas frequentes (FAQ) para respostas a perguntas sobre compatibilidade de recursos.
Nota
A sincronização de dados em tempo real não é possível com um indexador. Um indexador pode reindexar sua tabela no máximo a cada cinco minutos. Se as atualizações de dados precisarem ser refletidas no índice mais cedo, recomendamos enviar linhas atualizadas diretamente.
Pré-requisitos
Um banco de dados SQL do Azure com dados em uma única tabela ou exibição ou uma Instância Gerenciada SQL com um ponto de extremidade público.
Use uma tabela se os dados forem grandes ou se você precisar de indexação incremental usando os recursos nativos de deteção de alterações do SQL.
Use um modo de exibição se precisar consolidar dados de várias tabelas. Exibições grandes não são ideais para o indexador SQL. Uma solução alternativa é criar uma nova tabela apenas para ingestão em seu índice de Pesquisa de IA do Azure. Você poderá usar o controle de alterações integrado ao SQL, que é mais fácil de implementar do que a High Water Mark.
Permissões de leitura. O Azure AI Search dá suporte à autenticação do SQL Server, onde o nome de usuário e a senha são fornecidos na cadeia de conexão. Como alternativa, você pode configurar uma identidade gerenciada e usar funções do Azure.
Para trabalhar com os exemplos neste artigo, você precisa de um cliente REST.
Outras abordagens para criar um indexador SQL do Azure incluem SDKs do Azure ou o assistente de Importação de Dados no portal do Azure. Se estiver a utilizar o portal do Azure, certifique-se de que o acesso a todas as redes públicas está ativado na firewall SQL do Azure e que o cliente tem acesso através de uma regra de entrada.
Definir a fonte de dados
A definição da fonte de dados especifica os dados a serem indexados, credenciais e políticas para identificar alterações nos dados. Uma fonte de dados é definida como um recurso independente para que possa ser usada por vários indexadores.
Criar fonte de dados ou Atualizar fonte de dados para definir sua definição:
POST https://myservice.search.windows.net/datasources?api-version=2020-06-30 Content-Type: application/json api-key: admin-key { "name" : "myazuresqldatasource", "description" : "A database for testing Azure AI Search indexes.", "type" : "azuresql", "credentials" : { "connectionString" : "Server=tcp:<your server>.database.windows.net,1433;Database=<your database>;User ID=<your user name>;Password=<your password>;Trusted_Connection=False;Encrypt=True;Connection Timeout=30;" }, "container" : { "name" : "name of the table or view that you want to index", "query" : null (not supported in the Azure SQL indexer) }, "dataChangeDetectionPolicy": null, "dataDeletionDetectionPolicy": null, "encryptionKey": null, "identity": null }Forneça um nome exclusivo para a fonte de dados que segue as convenções de nomenclatura do Azure AI Search.
Defina "type" como
"azuresql"(obrigatório).Defina "credenciais" para uma cadeia de conexão:
Você pode obter uma cadeia de conexão de acesso completo no portal do Azure. Use a
ADO.NET connection stringopção. Defina o nome de usuário e a senha.Como alternativa, você pode especificar uma cadeia de conexão de identidade gerenciada que não inclua segredos de banco de dados com o seguinte formato:
Initial Catalog|Database=<your database name>;ResourceId=/subscriptions/<your subscription ID>/resourceGroups/<your resource group name>/providers/Microsoft.Sql/servers/<your SQL Server name>/;Connection Timeout=connection timeout length;.
Para obter mais informações, consulte Conectar-se ao indexador do Banco de Dados SQL do Azure usando uma identidade gerenciada.
Adicionar campos de pesquisa a um índice
Em um índice de pesquisa, adicione campos que correspondam aos campos no banco de dados SQL. Verifique se o esquema de índice de pesquisa é compatível com o esquema de origem usando tipos de dados equivalentes.
Crie ou atualize um índice para definir campos de pesquisa que armazenarão dados:
POST https://[service name].search.windows.net/indexes?api-version=2020-06-30 Content-Type: application/json api-key: [Search service admin key] { "name": "mysearchindex", "fields": [{ "name": "id", "type": "Edm.String", "key": true, "searchable": false }, { "name": "description", "type": "Edm.String", "filterable": false, "searchable": true, "sortable": false, "facetable": false, "suggestions": true } ] }Crie um campo de chave de documento ("chave": true) que identifique exclusivamente cada documento de pesquisa. Este é o único campo obrigatório em um índice de pesquisa. Normalmente, a chave primária da tabela é mapeada para o campo de chave de índice. A chave do documento deve ser exclusiva e não nula. Os valores podem ser numéricos nos dados de origem, mas em um índice de pesquisa, uma chave é sempre uma cadeia de caracteres.
Crie mais campos para adicionar mais conteúdo pesquisável. Consulte Criar um índice para obter orientação.
Mapeando tipos de dados
| Tipo de dados SQL | Tipos de campo do Azure AI Search | Notas |
|---|---|---|
| bit | Edm.Booleano, Edm.String | |
| int, smallint, tinyint | Edm.Int32, Edm.Int64, Edm.String | |
| bigint | Edm.Int64, Edm.String | |
| real, flutuar | Edm.Double, Edm.String | |
| smallmoney, dinheiro decimal numérico | Edm.String | O Azure AI Search não suporta a conversão de tipos decimais em Edm.Double porque isso perderia precisão |
| char, nchar, varchar, nvarchar | Edm.String Collection(Edm.String) |
Uma cadeia de caracteres SQL pode ser usada para preencher um campo Collection(Edm.String) se a cadeia de caracteres representar uma matriz JSON de cadeias de caracteres: ["red", "white", "blue"] |
| smalldatetime, datetime, datetime2, date, datetimeoffset | Edm.DateTimeOffset, Edm.String | |
| uniqueidentifer | Edm.String | |
| geografia | Edm.GeographyPoint | Somente instâncias geográficas do tipo POINT com SRID 4326 (que é o padrão) são suportadas |
| versão de linha | Não aplicável | As colunas de versão de linha não podem ser armazenadas no índice de pesquisa, mas podem ser usadas para controle de alterações |
| time, timepan, binary, varbinary, image, xml, geometry, CLR types | Não aplicável | Não suportado |
Configurar e executar o indexador SQL do Azure
Depois que o índice e a fonte de dados forem criados, você estará pronto para criar o indexador. A configuração do indexador especifica as entradas, parâmetros e propriedades que controlam os comportamentos de tempo de execução.
Crie ou atualize um indexador dando-lhe um nome e fazendo referência à fonte de dados e ao índice de destino:
POST https://[service name].search.windows.net/indexers?api-version=2020-06-30 Content-Type: application/json api-key: [search service admin key] { "name" : "[my-sqldb-indexer]", "dataSourceName" : "[my-sqldb-ds]", "targetIndexName" : "[my-search-index]", "disabled": null, "schedule": null, "parameters": { "batchSize": null, "maxFailedItems": 0, "maxFailedItemsPerBatch": 0, "base64EncodeKeys": false, "configuration": { "queryTimeout": "00:04:00", "convertHighWaterMarkToRowVersion": false, "disableOrderByHighWaterMarkColumn": false } }, "fieldMappings": [], "encryptionKey": null }Em parâmetros, a seção de configuração tem parâmetros específicos do Azure SQL:
O tempo limite de consulta padrão para a execução da consulta SQL é de 5 minutos, que pode ser substituído.
"convertHighWaterMarkToRowVersion" otimiza para a política de deteção de alteração de marca d'água alta. As políticas de deteção de alterações são definidas na fonte de dados. Se você estiver usando a política nativa de deteção de alterações, esse parâmetro não terá efeito.
"disableOrderByHighWaterMarkColumn" faz com que a consulta SQL usada pela política de marca d'água alta omita a cláusula ORDER BY. Se você estiver usando a política nativa de deteção de alterações, esse parâmetro não terá efeito.
Especifique mapeamentos de campo se houver diferenças no nome ou tipo de campo ou se precisar de várias versões de um campo de origem no índice de pesquisa.
Consulte Criar um indexador para obter mais informações sobre outras propriedades.
Um indexador é executado automaticamente quando é criado. Você pode evitar isso definindo "desativado" como true. Para controlar a execução do indexador, execute um indexador sob demanda ou coloque-o em uma programação.
Verificar o estado do indexador
Para monitorar o status do indexador e o histórico de execução, envie uma solicitação Obter Status do Indexador:
GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2020-06-30
Content-Type: application/json
api-key: [admin key]
A resposta inclui o status e o número de itens processados. Deve ser semelhante ao seguinte exemplo:
{
"status":"running",
"lastResult": {
"status":"success",
"errorMessage":null,
"startTime":"2022-02-21T00:23:24.957Z",
"endTime":"2022-02-21T00:36:47.752Z",
"errors":[],
"itemsProcessed":1599501,
"itemsFailed":0,
"initialTrackingState":null,
"finalTrackingState":null
},
"executionHistory":
[
{
"status":"success",
"errorMessage":null,
"startTime":"2022-02-21T00:23:24.957Z",
"endTime":"2022-02-21T00:36:47.752Z",
"errors":[],
"itemsProcessed":1599501,
"itemsFailed":0,
"initialTrackingState":null,
"finalTrackingState":null
},
... earlier history items
]
}
O histórico de execução contém até 50 das execuções concluídas mais recentemente, que são classificadas na ordem cronológica inversa para que a execução mais recente venha primeiro.
Indexação de linhas novas, alteradas e excluídas
Se o seu banco de dados SQL oferecer suporte ao controle de alterações, um indexador de pesquisa poderá pegar apenas o conteúdo novo e atualizado em execuções subsequentes do indexador.
Para habilitar a indexação incremental, defina a propriedade "dataChangeDetectionPolicy" na definição da fonte de dados. Essa propriedade informa ao indexador qual mecanismo de controle de alterações é usado em sua tabela ou exibição.
Para indexadores SQL do Azure, há duas políticas de deteção de alterações:
"SqlIntegratedChangeTrackingPolicy" (aplica-se apenas a tabelas)
"HighWaterMarkChangeDetectionPolicy" (funciona para tabelas e exibições)
Política de controle de alterações integrada do SQL
Recomendamos o uso de "SqlIntegratedChangeTrackingPolicy" por sua eficiência e sua capacidade de identificar linhas excluídas.
Requisitos da base de dados:
- SQL Server 2012 SP3 e posterior, caso esteja a utilizar o SQL Server em VMs do Azure
- Banco de Dados SQL do Azure ou Instância Gerenciada SQL
- Apenas tabelas (sem vistas)
- No banco de dados, habilite o controle de alterações para a tabela
- Nenhuma chave primária composta (uma chave primária contendo mais de uma coluna) na tabela
- Nenhum índice clusterizado na tabela. Como solução alternativa, qualquer índice clusterizado teria que ser descartado e recriado como índice não clusterizado, no entanto, o desempenho pode ser afetado na origem em comparação com ter um índice clusterizado
As políticas de deteção de alterações são adicionadas às definições da fonte de dados. Para usar essa política, crie ou atualize sua fonte de dados da seguinte forma:
POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
Content-Type: application/json
api-key: admin-key
{
"name" : "myazuresqldatasource",
"type" : "azuresql",
"credentials" : { "connectionString" : "connection string" },
"container" : { "name" : "table name" },
"dataChangeDetectionPolicy" : {
"@odata.type" : "#Microsoft.Azure.Search.SqlIntegratedChangeTrackingPolicy"
}
Ao usar a política de controle de alterações integrada ao SQL, não especifique uma política de deteção de exclusão de dados separada. A política de controle de alterações integrada ao SQL tem suporte interno para identificar linhas excluídas. No entanto, para que as linhas excluídas sejam detetadas automaticamente, a chave do documento no índice de pesquisa deve ser a mesma que a chave primária na tabela SQL.
Nota
Ao usar TRUNCATE TABLE para remover um grande número de linhas de uma tabela SQL, o indexador precisa ser redefinido para redefinir o estado de controle de alterações para pegar exclusões de linha.
Política de Deteção de Alterações de Marcas de Água Altas
Essa política de deteção de alterações depende de uma coluna "marca d'água alta" em sua tabela ou exibição que captura a versão ou a hora em que uma linha foi atualizada pela última vez. Se estiver a utilizar uma vista, tem de utilizar uma política de marca de água elevada.
A coluna de marca d'água alta deve atender aos seguintes requisitos:
- Todas as inserções especificam um valor para a coluna.
- Todas as atualizações de um item também alteram o valor da coluna.
- O valor desta coluna aumenta a cada inserção ou atualização.
- As consultas com as seguintes cláusulas WHERE e ORDER BY podem ser executadas de forma eficiente:
WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column]
Nota
É altamente recomendável usar o tipo de dados rowversion para a coluna de marca d'água alta. Se qualquer outro tipo de dados for usado, não é garantido que o controle de alterações capture todas as alterações na presença de transações executadas simultaneamente com uma consulta indexadora. Ao usar rowversion em uma configuração com réplicas somente leitura, você deve apontar o indexador para a réplica primária. Somente uma réplica primária pode ser usada para cenários de sincronização de dados.
As políticas de deteção de alterações são adicionadas às definições da fonte de dados. Para usar essa política, crie ou atualize sua fonte de dados da seguinte forma:
POST https://myservice.search.windows.net/datasources?api-version=2020-06-30
Content-Type: application/json
api-key: admin-key
{
"name" : "myazuresqldatasource",
"type" : "azuresql",
"credentials" : { "connectionString" : "connection string" },
"container" : { "name" : "table or view name" },
"dataChangeDetectionPolicy" : {
"@odata.type" : "#Microsoft.Azure.Search.HighWaterMarkChangeDetectionPolicy",
"highWaterMarkColumnName" : "[a rowversion or last_updated column name]"
}
}
Nota
Se a tabela de origem não tiver um índice na coluna de marca d'água alta, as consultas usadas pelo indexador SQL poderão atingir o tempo limite. Em particular, a ORDER BY [High Water Mark Column] cláusula requer que um índice seja executado de forma eficiente quando a tabela contém muitas linhas.
convertHighWaterMarkToRowVersion
Se você estiver usando um tipo de dados rowversion para a coluna de marca d'água alta, considere definir a propriedade na configuração do convertHighWaterMarkToRowVersion indexador. Definir essa propriedade como true resulta nos seguintes comportamentos:
Usa o tipo de dados rowversion para a coluna de marca d'água alta na consulta SQL do indexador. O uso do tipo de dados correto melhora o desempenho da consulta do indexador.
Subtrai um do valor rowversion antes da execução da consulta do indexador. As visualizações com uniões um-para-muitos podem ter linhas com valores de versão de linha duplicados. Subtrair um garante que a consulta do indexador não perca essas linhas.
Para habilitar essa propriedade, crie ou atualize o indexador com a seguinte configuração:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "convertHighWaterMarkToRowVersion" : true } }
}
queryTimeout
Se você encontrar erros de tempo limite, defina a configuração do queryTimeout indexador para um valor maior do que o tempo limite padrão de 5 minutos. Por exemplo, para definir o tempo limite para 10 minutos, crie ou atualize o indexador com a seguinte configuração:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "queryTimeout" : "00:10:00" } }
}
disableOrderByHighWaterMarkColumn
Você também pode desativar a ORDER BY [High Water Mark Column] cláusula. No entanto, isso não é recomendado porque, se a execução do indexador for interrompida por um erro, o indexador terá que reprocessar todas as linhas se for executado posteriormente, mesmo que o indexador já tenha processado quase todas as linhas no momento em que foi interrompido. Para desativar a ORDER BY cláusula, use a disableOrderByHighWaterMarkColumn configuração na definição do indexador:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
}
Política de Deteção de Exclusão de Coluna de Exclusão Suave
Quando as linhas são excluídas da tabela de origem, você provavelmente deseja excluir essas linhas do índice de pesquisa também. Se você usar a política de controle de alterações integrada do SQL, isso será resolvido para você. No entanto, a política de controle de alterações de marca d'água alta não ajuda você com linhas excluídas. O que fazer?
Se as linhas forem fisicamente removidas da tabela, o Azure AI Search não terá como inferir a presença de registros que não existem mais. No entanto, você pode usar a técnica "soft-delete" para excluir logicamente linhas sem removê-las da tabela. Adicione uma coluna à sua tabela ou vista e marque as linhas como eliminadas utilizando essa coluna.
Ao usar a técnica de exclusão suave, você pode especificar a política de exclusão suave da seguinte forma ao criar ou atualizar a fonte de dados:
{
…,
"dataDeletionDetectionPolicy" : {
"@odata.type" : "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy",
"softDeleteColumnName" : "[a column name]",
"softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
}
}
O softDeleteMarkerValue deve ser uma cadeia de caracteres na representação JSON da sua fonte de dados. Use a representação de cadeia de caracteres do seu valor real. Por exemplo, se você tiver uma coluna inteira onde as linhas excluídas são marcadas com o valor 1, use "1". Se você tiver uma coluna BIT onde as linhas excluídas são marcadas com o valor verdadeiro booleano, use a cadeia de caracteres literal "True" ou "true", o caso não importa.
Se você estiver configurando uma política de exclusão flexível do portal do Azure, não adicione aspas em torno do valor do marcador de exclusão suave. O conteúdo do campo já é entendido como uma string e será traduzido automaticamente em uma string JSON para você. Nos exemplos acima, basta digitar 1, True ou true no campo do portal.
FAQ
P: Posso indexar colunas Sempre Encriptadas?
N.º Atualmente, as colunas Sempre Criptografadas não são suportadas pelos indexadores do Azure AI Search.
P: Posso usar o indexador SQL do Azure com bancos de dados SQL em execução em VMs IaaS no Azure?
Sim. No entanto, você precisa permitir que seu serviço de pesquisa se conecte ao seu banco de dados. Para obter mais informações, consulte Configurar uma conexão de um indexador do Azure AI Search com o SQL Server em uma VM do Azure.
P: Posso usar o indexador SQL do Azure com bancos de dados SQL em execução no local?
Não diretamente. Não recomendamos nem damos suporte a uma conexão direta, pois isso exigiria que você abrisse seus bancos de dados para o tráfego da Internet. Os clientes tiveram sucesso com esse cenário usando tecnologias de ponte como o Azure Data Factory. Para obter mais informações, consulte Enviar dados por push para um índice do Azure AI Search usando o Azure Data Factory.
P: Posso usar uma réplica secundária em um cluster de failover como fonte de dados?
Depende. Para indexação completa de uma tabela ou exibição, você pode usar uma réplica secundária.
Para indexação incremental, o Azure AI Search dá suporte a duas políticas de deteção de alterações: controle de alterações integrado ao SQL e High Water Mark.
Em réplicas somente leitura, o Banco de dados SQL não oferece suporte ao controle integrado de alterações. Portanto, você deve usar a política High Water Mark.
Nossa recomendação padrão é usar o tipo de dados rowversion para a coluna de marca d'água alta. No entanto, o uso de rowversion depende da função, que não é suportada MIN_ACTIVE_ROWVERSION em réplicas somente leitura. Portanto, você deve apontar o indexador para uma réplica primária se estiver usando rowversion.
Se você tentar usar rowversion em uma réplica somente leitura, verá o seguinte erro:
"O uso de uma coluna rowversion para controle de alterações não é suportado em réplicas de disponibilidade secundárias (somente leitura). Atualize a fonte de dados e especifique uma conexão com a réplica de disponibilidade primária. A propriedade 'Updateability' do banco de dados atual é 'READ_ONLY'".
P: Posso usar uma coluna alternativa, sem versão de linha, para o rastreamento de alterações de marca d'água alta?
Não é recomendado. Somente rowversion permite uma sincronização de dados confiável. No entanto, dependendo da lógica do seu aplicativo, pode ser seguro se:
Você pode garantir que, quando o indexador é executado, não há transações pendentes na tabela que está sendo indexada (por exemplo, todas as atualizações de tabela acontecem como um lote em uma agenda, e a agenda do indexador do Azure AI Search é definida para evitar sobreposição com a agenda de atualização da tabela).
Você periodicamente faz uma reindexação completa para pegar as linhas perdidas.