Indexar dados de SQL do Azure
Neste artigo, saiba como configurar um indexador que importa conteúdo da Base de Dados do SQL do Azure ou de uma instância gerida SQL do Azure e o torna pesquisável no Azure Cognitive Search.
Este artigo complementa Criar um indexador com informações específicas para SQL do Azure. Utiliza as APIs REST para demonstrar um fluxo de trabalho de três partes comum a todos os indexadores: criar uma origem de dados, criar um índice e criar um indexador.
Este artigo também fornece:
Uma descrição das políticas de deteção de alterações suportadas pelo indexador de SQL do Azure para que possa configurar a indexação incremental.
Uma secção de perguntas mais frequentes (FAQ) para obter respostas a perguntas sobre compatibilidade de funcionalidades.
Nota
A sincronização de dados em tempo real não é possível com um indexador. Um indexador pode reindexar a sua tabela a cada cinco minutos. Se as atualizações de dados precisarem de ser refletidas no índice mais cedo, recomendamos que envie diretamente linhas atualizadas.
Pré-requisitos
Uma base de dados SQL do Azure com dados numa única tabela ou vista.
Utilize uma tabela se os seus dados forem grandes ou se precisar de indexação incremental com as capacidades de deteção de alterações nativas do SQL.
Utilize uma vista se precisar de consolidar dados de várias tabelas. As vistas grandes não são ideais para o indexador SQL. Uma solução é criar uma nova tabela apenas para ingestão no índice cognitive search. Poderá utilizar o controlo de alterações integrado do SQL, que é mais fácil de implementar do que a Marca de Água Elevada.
Permissões de leitura. Azure Cognitive Search suporta SQL Server autenticação, onde o nome de utilizador e a palavra-passe são fornecidos na cadeia de ligação. Em alternativa, pode configurar uma identidade gerida e utilizar funções do Azure.
Para analisar os exemplos neste artigo, precisará de um cliente REST, como o Postman.
Outras abordagens para criar um indexador de SQL do Azure incluem os SDKs do Azure ou o assistente importar dados no portal do Azure. Se estiver a utilizar portal do Azure, certifique-se de que o acesso a todas as redes públicas está ativado na firewall SQL do Azure e de que o cliente tem acesso através de uma regra de entrada.
Definir a origem de dados
A definição da origem de dados especifica os dados para indexar, credenciais e políticas para identificar alterações nos dados. Uma origem de dados é definida como um recurso independente para que possa ser utilizada por vários indexadores.
Criar origem de dados ou Atualizar origem de dados para definir a respetiva 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 Cognitive 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 origem de dados que se segue Azure Cognitive Search convenções de nomenclatura.
Defina "type" como
"azuresql"(obrigatório).Defina "credenciais" para uma cadeia de ligação:
Pode obter uma cadeia de ligação de acesso completo a partir do portal do Azure. Utilize a opção
ADO.NET connection string. Defina o nome de utilizador e a palavra-passe.Em alternativa, pode especificar uma cadeia de ligação de identidade gerida que não inclua segredos da base 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, veja Connect to SQL do Azure Database indexer using a managed identity (Ligar ao indexador da Base de Dados do SQL do Azure com uma identidade gerida).
Adicionar campos de pesquisa a um índice
Num índice de pesquisa, adicione campos que correspondam aos campos na base de dados SQL. Certifique-se de que o esquema do índice de pesquisa é compatível com o esquema de origem através de tipos de dados equivalentes.
Criar ou atualizar um índice para definir campos de pesquisa que irão armazenar 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": verdadeiro) que identifique exclusivamente cada documento de pesquisa. Este é o único campo necessário num índice de pesquisa. Normalmente, a chave primária da tabela é mapeada para o campo de chave de índice. A chave do documento tem de ser exclusiva e não nula. Os valores podem ser numéricos nos dados de origem, mas num índice de pesquisa, uma chave é sempre uma cadeia.
Crie mais campos para adicionar mais conteúdo pesquisável. Veja Criar um índice para obter orientações.
Tipos de dados de mapeamento
| Tipo de dados SQL | Tipos de campo do Cognitive Search | Notas |
|---|---|---|
| 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, dinheiro decimal numérico | Edm.String | Azure Cognitive Search não suporta a conversão de tipos decimais em Edm.Double porque fazê-lo perderia precisão |
| char, nchar, varchar, nvarchar | Edm.String Coleção (Edm.String) |
Uma cadeia DE SQL pode ser utilizada para preencher um campo Coleção(Edm.String) se a cadeia representar uma matriz JSON de cadeias: ["red", "white", "blue"] |
| smalldatetime, datetime, datetime2, date, datetimeoffset | Edm.DateTimeOffset, Edm.String | |
| uniqueidentifer | Edm.String | |
| geografia | Edm.GeographyPoint | Apenas são suportadas instâncias geográficas do tipo POINT com SRID 4326 (que é a predefinição) |
| rowversion | Não aplicável | As colunas da versão de linha não podem ser armazenadas no índice de pesquisa, mas podem ser utilizadas para controlo de alterações |
| time, timespan, binary, varbinary, image, xml, geometry, CLR types | Não aplicável | Não suportado |
Configurar e executar o indexador de SQL do Azure
Assim que o índice e a origem de dados tiverem sido criados, está 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 ao atribuir-lhe um nome e referenciar a origem 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 secção de configuração tem parâmetros específicos de SQL do Azure:
O tempo limite de consulta predefinido para a execução de consultas SQL é de 5 minutos, o que pode substituir.
"convertHighWaterMarkToRowVersion" otimiza para a política de deteção de alterações de Marca de Água Elevada. As políticas de deteção de alterações são definidas na origem de dados. Se estiver a utilizar a política de deteção de alterações nativa, este parâmetro não tem qualquer efeito.
"disableOrderByHighWaterMarkColumn" faz com que a consulta SQL utilizada pela política de marca de água elevada omita a cláusula ORDER BY. Se estiver a utilizar a política de deteção de alterações nativa, este parâmetro não tem qualquer efeito.
Especifique os mapeamentos de campos se existirem 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.
Veja Criar um indexador para obter mais informações sobre outras propriedades.
Um indexador é executado automaticamente quando é criado. Pode impedir esta situação ao definir "desativado" como verdadeiro. Para controlar a execução do indexador, execute um indexador a pedido ou coloque-o numa agenda.
Verificar o estado do indexador
Para monitorizar o estado do indexador e o histórico de execuções, envie um pedido Obter Estado 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 estado e o número de itens processados. Deverá ter um aspeto 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ções contém até 50 das execuções concluídas mais recentemente, que são ordenadas por ordem cronológica inversa para que a execução mais recente seja a primeira.
Indexar linhas novas, alteradas e eliminadas
Se a base de dados SQL suportar o controlo de alterações, um indexador de pesquisa pode recolher apenas o conteúdo novo e atualizado nas execuções subsequentes do indexador.
Para ativar a indexação incremental, defina a propriedade "dataChangeDetectionPolicy" na definição da origem de dados. Esta propriedade indica ao indexador que mecanismo de controlo de alterações é utilizado na sua tabela ou vista.
Para SQL do Azure indexadores, existem duas políticas de deteção de alterações:
"SqlIntegratedChangeTrackingPolicy" (aplica-se apenas a tabelas)
"HighWaterMarkChangeDetectionPolicy" (funciona para tabelas e vistas)
Política de Controlo de Alterações Integrada do SQL
Recomendamos que utilize "SqlIntegratedChangeTrackingPolicy" para a sua eficiência e capacidade de identificar linhas eliminadas.
Requisitos da base de dados:
- SQL Server 2012 SP3 e posterior, caso esteja a utilizar o SQL Server em VMs do Azure
- SQL do Azure Base de Dados ou SQL Managed Instance
- Apenas tabelas (sem vistas)
- Na base de dados, ative o controlo de alterações para a tabela
- Nenhuma chave primária composta (uma chave primária que contenha mais do que uma coluna) na tabela
- Não existem índices agrupados na tabela. Como solução, qualquer índice agrupado teria de ser removido e recriado como índice não agrupado, no entanto, o desempenho pode ser afetado na origem em comparação com ter um índice em cluster
As políticas de deteção de alterações são adicionadas às definições de origem de dados. Para utilizar esta política, crie ou atualize a sua origem 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 utilizar a política de controlo de alterações integrada do SQL, não especifique uma política de deteção de eliminação de dados separada. A política de controlo de alterações integrada do SQL tem suporte incorporado para identificar linhas eliminadas. No entanto, para que as linhas eliminadas sejam detetadas automaticamente, a chave de documento no índice de pesquisa tem de ser igual à chave primária na tabela SQL.
Nota
Ao utilizar a TABELA TRUNCATE para remover um grande número de linhas de uma tabela SQL, o indexador tem de ser reposto para repor o estado de controlo de alterações para recolher eliminações de linhas.
Política de Deteção de Alterações de Marca de Água Elevada
Esta política de deteção de alterações baseia-se numa coluna "marca de água elevada" na sua tabela ou vista que captura a versão ou 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 de água elevada tem de cumprir os seguintes requisitos:
- Todas as inserções especificam um valor para a coluna.
- Todas as atualizações para um item também alteram o valor da coluna.
- O valor desta coluna aumenta com 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
Recomendamos vivamente a utilização do tipo de dados rowversion para a coluna de marca de água elevada. Se for utilizado outro tipo de dados, o controlo de alterações não é garantido para capturar todas as alterações na presença de transações executadas em simultâneo com uma consulta de indexador. Ao utilizar a rowversion numa configuração com réplicas só de leitura, tem de apontar o indexador para a réplica primária. Apenas uma réplica primária pode ser utilizada para cenários de sincronização de dados.
As políticas de deteção de alterações são adicionadas às definições de origem de dados. Para utilizar esta política, crie ou atualize a sua origem 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 de água elevada, as consultas utilizadas pelo indexador SQL poderão exceder o limite de tempo. 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 estiver a utilizar um tipo de dados de rowversion para a coluna de marca de água elevada, considere definir a propriedade na configuração do convertHighWaterMarkToRowVersion indexador. Definir esta propriedade como verdadeiro resulta nos seguintes comportamentos:
Utiliza o tipo de dados rowversion para a coluna de marca de água elevada na consulta SQL do indexador. Utilizar o tipo de dados correto melhora o desempenho da consulta do indexador.
Subtrai um do valor de rowversion antes da execução da consulta do indexador. As vistas com associações um-para-muitos podem ter linhas com valores de rowversion duplicados. Subtrair uma garante que a consulta do indexador não perde estas linhas.
Para ativar esta propriedade, crie ou atualize o indexador com a seguinte configuração:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "convertHighWaterMarkToRowVersion" : true } }
}
queryTimeout
Se encontrar erros de tempo limite, defina a definição de configuração do queryTimeout indexador para um valor superior ao tempo limite predefinido 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
Também pode desativar a ORDER BY [High Water Mark Column] cláusula. No entanto, isto não é recomendado porque, se a execução do indexador for interrompida por um erro, o indexador terá de voltar a processar todas as linhas se for executada mais tarde, mesmo que o indexador já tenha processado quase todas as linhas no momento em que foi interrompido. Para desativar a ORDER BY cláusula, utilize a disableOrderByHighWaterMarkColumn definição na definição do indexador:
{
... other indexer definition properties
"parameters" : {
"configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
}
Política de Deteção de Eliminação de Colunas de Eliminação Recuperável
Quando as linhas são eliminadas da tabela de origem, provavelmente também pretende eliminar essas linhas do índice de pesquisa. Se utilizar a política de controlo de alterações integrada do SQL, esta ação é feita por si. No entanto, a política de controlo de alterações de marca de água elevada não o ajuda com linhas eliminadas. O que fazer?
Se as linhas forem removidas fisicamente da tabela, Azure Cognitive Search não tem como inferir a presença de registos que já não existem. No entanto, pode utilizar a técnica "soft-delete" para eliminar logicamente linhas sem as remover da tabela. Adicione uma coluna à sua tabela ou vista e marque linhas como eliminadas com essa coluna.
Ao utilizar a técnica de eliminação recuperável, pode especificar a política de eliminação recuperável da seguinte forma ao criar ou atualizar a origem 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 tem de ser uma cadeia na representação JSON da sua origem de dados. Utilize a representação de cadeia do valor real. Por exemplo, se tiver uma coluna de número inteiro em que as linhas eliminadas são marcadas com o valor 1, utilize "1". Se tiver uma coluna BIT em que as linhas eliminadas são marcadas com o valor booleano verdadeiro, utilize a cadeia literal "True" ou "true", o caso não importa.
Se estiver a configurar uma política de eliminação recuperável do portal do Azure, não adicione aspas à volta do valor do marcador de eliminação recuperável. Os conteúdos do campo já são entendidos como uma cadeia de carateres e serão traduzidos automaticamente para uma cadeia JSON. Nos exemplos acima, basta escrever 1ou Truetrue entrar no campo do portal.
FAQ
P: Posso indexar colunas Always Encrypted?
N.º Always Encrypted colunas não são atualmente suportadas por indexadores da Pesquisa Cognitiva.
P: Posso utilizar SQL do Azure indexador com bases de dados SQL em execução em VMs IaaS no Azure?
Sim. No entanto, tem de permitir que o seu serviço de pesquisa se ligue à base de dados. Para obter mais informações, veja Configurar uma ligação de um indexador de Azure Cognitive Search para SQL Server numa VM do Azure.
P: Posso utilizar SQL do Azure indexador com bases de dados SQL em execução no local?
Não diretamente. Não recomendamos nem suportamos uma ligação direta, uma vez que tal exigiria que abrisse as bases de dados para o tráfego da Internet. Os clientes foram bem-sucedidos com este cenário através de tecnologias de bridge, como Azure Data Factory. Para obter mais informações, veja Enviar dados para um índice de Azure Cognitive Search com Azure Data Factory.
P: Posso utilizar uma réplica secundária num cluster de ativação pós-falha como uma origem de dados?
Depende. Para indexação completa de uma tabela ou vista, pode utilizar uma réplica secundária.
Para indexação incremental, Azure Cognitive Search suporta duas políticas de deteção de alterações: controlo de alterações integrado do SQL e Marca de Água Elevada.
Nas réplicas só de leitura, Base de Dados SQL não suporta o controlo de alterações integrado. Por conseguinte, tem de utilizar a política de Marca de Água Elevada.
A nossa recomendação padrão é utilizar o tipo de dados rowversion para a coluna de marca de água elevada. No entanto, a utilização de rowversion depende da MIN_ACTIVE_ROWVERSION função, que não é suportada em réplicas só de leitura. Por conseguinte, tem de apontar o indexador para uma réplica primária se estiver a utilizar a rowversion.
Se tentar utilizar a rowversion numa réplica só de leitura, verá o seguinte erro:
"A utilização de uma coluna de rowversion para controlo de alterações não é suportada em réplicas de disponibilidade secundárias (só de leitura). Atualize a origem de dados e especifique uma ligação à réplica de disponibilidade primária. A propriedade "Updateability" da base de dados atual é "READ_ONLY".
P: Posso utilizar uma coluna alternativa não rowversion para controlo de alterações de marca de água elevada?
Não é recomendado. Apenas a rowversion permite a sincronização de dados fiável. No entanto, dependendo da lógica da aplicação, poderá ser seguro se:
Pode garantir que, quando o indexador é executado, não existem transações pendentes na tabela que estejam a ser indexadas (por exemplo, todas as atualizações de tabelas ocorrem como um lote numa agenda e a agenda do indexador de Azure Cognitive Search está definida para evitar sobreposição com a agenda de atualização da tabela).
Faz periodicamente um reindex completo para recolher quaisquer linhas perdidas.