Como indexar dados do SQL do Azure na IA do Azure Search
Neste artigo, saiba como configurar um indexador que importa o conteúdo do Banco de Dados SQL do Azure ou uma instância gerenciada de SQL do Azure e o torna pesquisável na IA do Azure Search.
Este artigo complementa Criar um indexador com informações específicas de SQL do Azure. 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 e criar um indexador.
Este artigo também fornece o seguinte:
Uma descrição das políticas de detecção de alterações compatíveis com o indexador do SQL do Azure, para que você possa configurar a indexação incremental.
Uma seção de perguntas frequentes para tirar as dúvidas sobre compatibilidade de recursos.
Observação
A sincronização de dados em tempo real não é possível com um indexador. Um indexador pode reindexar a tabela, no máximo, a cada cinco minutos. Se as atualizações de dados precisarem ser refletidas no índice mais cedo, recomendamos enviar as 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 de 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 detecção de alterações do SQL.
Use uma exibição se você precisar consolidar dados em várias tabelas. As exibições grandes não são indicadas para o indexador do SQL. Uma solução alternativa é criar uma nova tabela apenas para ingestão no índice da IA do Azure Search. Você poderá o controle de alterações integrado do SQL, que é mais fácil de implementar do que a Marca D'água Alta.
Permissões de leitura. A IA do Azure Search dá suporte à autenticação do SQL Server, em que 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 deste artigo, você precisará de um cliente REST.
Outras abordagens para criar um indexador do SQL do Azure incluem os SDKs do Azure ou o assistente de importação de dados no portal do Azure. Se você estiver usando o portal do Azure, verifique se o acesso a todas as redes públicas está habilitado no firewall do SQL do Azure e se o cliente tem acesso por meio de uma regra de entrada.
Definir a fonte de dados
A definição da fonte de dados especifica os dados a serem indexados, as credenciais e as 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.
Crie uma fonte de dados ou Atualize uma fonte de dados para configurar a definição dela:
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 siga as convenções de nomenclatura da IA do Azure Search.
Defina "type" como
"azuresql"(obrigatório).Defina "credentials" como a cadeia de conexão:
Você pode obter uma cadeia de conexão de acesso total no portal do Azure. Use a opção
ADO.NET connection string. 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 inclui 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, confira 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 os campos correspondentes aos campos no banco de dados SQL. Verifique se o esquema do índice de pesquisa é compatível com o esquema de origem, usando tipos de dados equivalentes.
Crie ou atualize um índice para definir os campos de pesquisa que armazenarão os 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 do documento ("key": true) que identifique exclusivamente cada documento de pesquisa. Esse é o único campo obrigatório em um índice de pesquisa. Normalmente, a chave primária da tabela é mapeada no campo de chave de índice. A chave do documento deve ser exclusiva e diferente de nulo. 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. Confira Criar um índice para obter diretrizes.
Mapeamento de tipos de dados
| Tipo de dados SQL | Tipos de campo da IA do Azure Search | Observações |
|---|---|---|
| bit | Edm.Boolean, Edm.String | |
| int, smallint, tinyint | Edm.Int32, Edm.Int64, Edm.String | |
| BIGINT | Edm.Int64, Edm.String | |
| real, float | Edm.Double, Edm.String | |
| smallmoney, numérico decimal dinheiro | Edm.String | Na IA do Azure Search, não há suporte para conversão de tipos decimais em Edm.Double, pois perderia a precisão |
| char, nchar, varchar, nvarchar | Edm.String Collection(Edm.String) |
Uma cadeia de caracteres SQL poderá 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 | Há suporte apenas para instâncias de Geografia do tipo POINT com SRID 4326 (que é o padrão) |
| rowversion | Não aplicável | Colunas row-version não podem ser armazenadas no índice de pesquisa, mas podem ser usadas para o controle de alterações |
| tempo, timespan, binário, varbinário, imagem, xml, geometria, tipos CLR | Não aplicável | Sem suporte |
Configurar e executar o indexador do SQL do Azure
Uma vez que o índice e a fonte de dados forem criados, será possível criar o indexador. A configuração do indexador especifica as entradas, os parâmetros e as propriedades que controlam os comportamentos de tempo de execução.
Crie ou atualize um indexador dando um nome a ele e referenciando a fonte de dados e o í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 para o SQL do Azure:
O tempo limite de consulta padrão para a execução da consulta SQL é de 5 minutos, o que você pode substituir.
"convertHighWaterMarkToRowVersion" otimiza a política de detecção de alterações de Marca D'água Alta. As políticas de detecção de alterações são definidas na fonte de dados. Se você estiver usando a política de detecção de alterações nativa, 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 de detecção de alterações nativa, esse parâmetro não terá efeito.
Especifique mapeamentos de campo se houver diferenças no nome ou tipo de campo, ou se você precisar de várias versões de um campo de origem no índice de pesquisa.
Confira Criar um indexador para obter mais informações sobre outras propriedades.
Um indexador é executado automaticamente depois de criado. Você pode evitar isso definindo "desabilitado" como verdadeiro. Para controlar a execução do indexador, execute um indexador sob demanda ou coloque-o em um agendamento.
Checar o status do indexador
Para monitorar o histórico de execuções e o status do indexador, 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. Ela deve ser parecida com o 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ções contém até 50 execuções mais recentes, classificadas em ordem cronológica inversa, de modo que a execução mais recente apareça em primeiro lugar.
Indexação de linhas novas, alteradas e excluídas
Se o banco de dados SQL for compatível com o controle de alterações, um indexador de pesquisa poderá escolher apenas o conteúdo novo e atualizado nas próximas execuções do indexador.
Para habilitar a indexação incremental, defina a propriedade "dataChangeDetectionPolicy" na definição da fonte de dados. Essa propriedade informa o indexador de que o mecanismo de controle de alterações é usado na tabela ou exibição.
Para indexadores de SQL do Azure, há duas políticas de detecção de alterações:
"SqlIntegratedChangeTrackingPolicy" (aplica-se apenas a tabelas)
"HighWaterMarkChangeDetectionPolicy" (funciona para tabelas e exibições)
Política de controle integrado de alterações do SQL
É recomendável usar "SqlIntegratedChangeTrackingPolicy" por sua eficiência e sua capacidade de identificar linhas excluídas.
Requisitos do banco de dados:
- O SQL Server 2012 SP3 e posterior, se você estiver usando o SQL Server em VMs do Azure
- Banco de Dados SQL do Azure ou Instância Gerenciada de SQL
- Apenas tabelas (sem exibições)
- No banco de dados, habilite o controle de alterações na tabela
- Sem chave primária de composição (uma chave primária que contém 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 a ter um índice clusterizado
As políticas de detecção de alteração são adicionadas às definições de fonte de dados. Para usar essa política, crie ou atualize a fonte de dados da seguinte maneira:
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 integrado de alterações do SQL, não especifique uma política de detecção de exclusão de dados separada. A política de controle integrado de alterações do SQL tem suporte interno para identificar linhas excluídas. No entanto, para que as linhas excluídas sejam detectadas automaticamente, a chave de documento no índice de pesquisa deve ser o mesmo da chave primária na tabela SQL.
Observação
Ao usar TRUNCATE TABLE para remover um grande número de linhas de uma tabela SQL, o indexador precisa ser redefinido para também redefinir o estado de acompanhamento de alterações para coletar exclusões de linhas.
Política de detecção de alteração de marca d’água alta
Essa política de detecção de alteração se baseia em uma coluna de “marca d'água alta” na tabela ou exibição que captura a versão ou a hora em que uma linha foi atualizada pela última vez. Se você estiver usando uma exibição, deverá usar uma política de marca d'água alta.
A coluna de marca d'água alta deve atender aos requisitos a seguir:
- 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 dessa coluna aumenta com cada inserção ou atualização.
- Consultas com as seguintes cláusulas WHERE e ORDER BY podem ser executadas com eficiência:
WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column]
Observação
É 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 será possível assegurar a captura, pelo acompanhamento de alterações, de todas as alterações na presença de transações em execução simultaneamente com uma consulta do indexador. Ao usar rowversion em uma configuração com réplicas somente leitura, você deve apontar o indexador para a réplica primária. Apenas uma réplica primária pode ser usada para cenários de sincronização de dados.
As políticas de detecção de alteração são adicionadas às definições de fonte de dados. Para usar essa política, crie ou atualize a fonte de dados da seguinte maneira:
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]"
}
}
Observação
Se a tabela de origem não tiver um índice na coluna de marca d'água alta, as consultas usadas pelo indexador do SQL poderão atingir o tempo limite. Em particular, a cláusula ORDER BY [High Water Mark Column] requer que um índice seja executado com eficiência quando a tabela contiver muitas linhas.
convertHighWaterMarkToRowVersion
Se você estiver usando um tipo de dados rowversion para a coluna de marca d'água alta, considere definir a propriedade convertHighWaterMarkToRowVersion na configuração do indexador. A definição dessa propriedade como true resulta nos seguintes comportamentos:
Use 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.
Subtraia um do valor rowversion antes da execução da consulta do indexador. Exibições com junções uma para muitas podem ter linhas com valores rowversion 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 indexador queryTimeout como um valor maior que o tempo limite padrão de 5 minutos. Por exemplo, para definir o tempo limite de 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 desabilitar a cláusula ORDER BY [High Water Mark Column]. No entanto, isso não é recomendado porque, se a execução do indexador for interrompida por um erro, o indexador deverá processar novamente todas as linhas se ele for executado mais tarde – mesmo se o indexador já tiver processado quase todas as linhas no momento em que foi interrompido. Para desabilitar a cláusula ORDER BY, use a configuração disableOrderByHighWaterMarkColumn na definição do indexador:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
}
Política de detecção de exclusão de coluna por exclusão reversível
Quando as linhas são excluídas da tabela de origem, provavelmente você deseja excluí-las também do índice de pesquisa. Se você usar a política de controle integrado de alterações 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 relação às linhas excluídas. O que fazer?
Se as linhas forem removidas fisicamente da tabela, a IA do Azure Search não terá como inferir na presença de registros que não existem mais. No entanto, você pode usar a técnica de "exclusão reversível" para excluir logicamente linhas sem removê-las da tabela. Adicione uma coluna à sua tabela ou exiba e marque as linhas como excluídas usando essa coluna.
Ao usar a técnica de exclusão reversível, você pode especificar a política de exclusão reversível da seguinte maneira 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 precisa ser uma cadeia de caracteres na representação JSON da fonte de dados. Use a representação de cadeia de caracteres do valor real. Por exemplo, se você tiver uma coluna de inteiros na qual as linhas excluídas são marcadas com o valor 1, use "1". Se você tiver uma coluna BIT na qual as linhas excluídas são marcadas com o valor true booliano, use a cadeia de caracteres literal "True" ou "true", o caso não importa.
Se você estiver configurando uma política de exclusão reversível no portal do Azure, não adicione aspas ao valor do marcador de exclusão reversível. O conteúdo do campo já é compreendido como uma cadeia de caracteres e será convertido automaticamente em uma cadeia de caracteres JSON para você. Nos exemplos acima, basta digitar 1, True ou true no campo do portal.
Perguntas frequentes
P: posso indexar colunas Always Encrypted?
Não. Atualmente, não há suporte para as colunas Always Encrypted pelos indexadores da IA do Azure 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 o serviço de pesquisa se conecte ao banco de dados. Para obter mais informações, consulte Configurar uma conexão de um indexador da IA do Azure Search ao SQL Server em uma VM do Azure.
P: Posso usar o indexador SQL do Azure com bancos de dados SQL em execução local?
Não diretamente. Não recomendamos nem damos suporte a uma conexão direta, pois isso exigirá a abertura dos bancos de dados ao 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 da IA do Azure 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 a indexação completa de uma tabela ou exibição, você pode usar uma réplica secundária.
Para a indexação incremental, a IA do Azure Search oferece suporte a duas políticas de detecção de alteração: controle de alterações integrado do SQL e Marca D'água Alta.
Em réplicas somente leitura, o Banco de Dados SQL não dá suporte ao controle de alterações integrado. Portanto, você deve usar a política de Marca D'água Alta.
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 MIN_ACTIVE_ROWVERSION, para a qual não há suporte 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:
“Não há suporte para o uso de uma coluna rowversion para o controle de alterações 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 não rowversion alternativa para o controle de alterações de marca d'água alta?
Isso não é recomendável. Somente rowversion permite a sincronização de dados confiável. No entanto, dependendo da lógica do aplicativo, isso poderá ser seguro se:
Você pode garantir que, quando o indexador for executado, não haja transações pendentes na tabela indexada (por exemplo, todas as atualizações de tabela ocorrem como um lote em um agendamento e o agendamento do indexador da IA do Azure Search está definido para evitar sobreposição com o agendamento de atualização da tabela).
Periodicamente, você fizer uma reindexação completa para selecionar as linhas ausentes.