Azure AI Search에서 Azure SQL의 데이터를 인덱싱하는 방법

이 문서에서는 Azure SQL Database 또는 Azure SQL 관리되는 인스턴스에서 콘텐츠를 가져오고 Azure AI Search에서 검색할 수 있도록 하는 인덱서를 구성하는 방법에 대해 알아봅니다.

이 문서는 Azure SQL과 관련된 정보로 인덱서 만들기를 보완합니다. REST API를 사용하여 모든 인덱서에 공통적인 세 부분으로 구성된 워크플로(데이터 원본 만들기, 인덱스 만들기, 인덱서 만들기)를 보여 줍니다.

이 문서에서는 다음과 같은 내용도 제공합니다.

참고 항목

인덱서로는 실시간 데이터 동기화를 사용할 수 없습니다. 인덱서는 최대 5분마다 테이블을 다시 인덱싱할 수 있습니다. 데이터 업데이트를 인덱스에 더 빨리 반영해야 하는 경우 업데이트된 행을 직접 푸시하는 것이 좋습니다.

필수 조건

  • 단일 테이블 또는 뷰의 데이터 또는 퍼블릭 엔드포인트가 있는 SQL Managed Instance가 포함된 Azure SQL 데이터베이스입니다.

    데이터가 크거나 SQL의 네이티브 변경 감지 기능을 사용한 증분 인덱싱이 필요한 경우 테이블을 사용합니다.

    여러 테이블의 데이터를 통합해야 하는 경우 뷰를 사용합니다. 대형 뷰는 SQL 인덱서에 적합하지 않습니다. 해결 방법은 Azure AI Search 인덱스로 수집할 새 테이블을 만드는 것입니다. 상위 워터 마크보다 구현하기 쉬운 SQL 통합 변경 내용 추적을 사용할 수 있습니다.

  • 읽기 권한. Azure AI Search는 연결 문자열에 사용자 이름과 암호가 제공되는 SQL Server 인증을 지원합니다. 또는 관리 ID를 설정하고 Azure 역할을 사용할 수 있습니다.

이 문서의 예제를 진행하려면 REST 클라이언트필요합니다.

Azure SQL 인덱서를 만드는 다른 방법으로는 Azure SDK 또는 Azure Portal의 데이터 가져오기 마법사가 있습니다. Azure Portal을 사용하는 경우 Azure SQL 방화벽에 모든 공용 네트워크에 대한 액세스가 서 사용하도록 설정되어 있고 클라이언트가 인바운드 규칙을 통해 액세스할 수 있는지 확인합니다.

데이터 원본 정의

데이터 원본 정의는 인덱싱할 데이터, 자격 증명 및 데이터 변경 내용을 식별하기 위한 정책을 지정합니다. 데이터 원본은 여러 인덱서에서 사용할 수 있도록 독립적인 리소스로 정의됩니다.

  1. 데이터 원본을 생성하거나 데이터 원본을 업데이트하여 정의를 설정합니다.

     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
     }
    
  2. Azure AI Search 명명 규칙을 따르는 데이터 원본의 고유한 이름을 제공합니다.

  3. "type"을 "azuresql"(필수)으로 설정합니다.

  4. "credentials"를 연결 문자열로 설정합니다.

    • Azure Portal에서 모든 권한 연결 문자열을 가져올 수 있습니다. ADO.NET connection string 옵션을 사용합니다. 사용자 이름 및 암호를 설정합니다.

    • 또는 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; 형식의 데이터베이스 비밀을 포함하지 않는 관리 ID 연결 문자열을 지정할 수 있습니다.

    자세한 내용은 관리 ID를 사용하여 Azure SQL Database 인덱서에 연결을 참조하세요.

인덱스에 검색 필드 추가

검색 인덱스에서 SQL 데이터베이스의 필드에 해당하는 필드를 추가합니다. 동일한 데이터 형식을 사용하여 검색 인덱스 스키마가 원본 스키마와 호환되도록 합니다.

  1. 인덱스를 만들거나 업데이트하여 데이터를 저장할 검색 필드를 정의합니다.

    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
        }
      ]
    }
    
  2. 각 검색 문서를 고유하게 식별하는 문서 키 필드("key": true)를 만듭니다. 이는 검색 인덱스에 필요한 유일한 필드입니다. 일반적으로 테이블의 기본 키는 인덱스 키 필드에 매핑됩니다. 문서 키는 고유하고 null이 아니어야 합니다. 값은 원본 데이터에서 숫자일 수 있지만 검색 인덱스에서 키는 항상 문자열입니다.

  3. 더 많은 필드를 만들어 검색 가능한 콘텐츠를 추가합니다. 참고 자료는 인덱스 만들기를 참조하세요.

데이터 형식 매핑

SQL 데이터 형식 Azure AI Search 필드 형식 주의
bit Edm.Boolean, Edm.String
int, smallint, tinyint Edm.Int32, Edm.Int64, Edm.String
bigint Edm.Int64, Edm.Int64, Edm.String
real, float Edm.Double, Edm.String
smallmoney, money decimal numeric Edm.String Azure AI Search에서는 10진수 형식을 Edm.Double로 변환할 수 없습니다. 이렇게 변환하는 경우 정밀도가 손실되기 때문입니다.
char, nchar, varchar, nvarchar Edm.String
Collection(Edm.String)
SQL 문자열은 문자열이 JSON 배열(["red", "white", "blue"])을 나타내는 경우 Collection(Edm.String) 필드를 채우는 데 사용할 수 있습니다.
smalldatetime, datetime, datetime2, date, datetimeoffset Edm.DateTimeOffset, Edm.String
uniqueidentifer Edm.String
지역 Edm.GeographyPoint SRID가 4326(기본값)인 POINT 형식의 지리 인스턴스만 지원됩니다.
rowversion 해당 없음 행 버전 열은 변경 내용 추적에 사용할 수 있지만 검색 인덱스에 저장할 수는 없습니다.
time, timespan, binary, varbinary, image, xml, geometry, CLR types 해당 없음 지원되지 않음

Azure SQL 인덱서 구성 및 실행

인덱스와 데이터 원본이 만들어지면 인덱서를 만들 준비가 된 것입니다. 인덱서 구성은 런타임 동작을 제어하는 입력, 매개 변수 및 속성을 지정합니다.

  1. 이름을 지정하고 데이터 원본 및 대상 인덱스를 참조하여 인덱서를 만들거나 업데이트합니다.

    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
    }
    
  2. 매개 변수 아래 구성 섹션에는 Azure SQL 관련된 매개 변수가 있습니다.

    • SQL 쿼리 실행에 대한 기본 쿼리 제한 시간은 5분입니다(재정의 가능).

    • "convertHighWaterMarkToRowVersion"은 상위 워터 마크 변경 감지 정책에 최적화됩니다. 변경 감지 정책은 데이터 원본에 설정됩니다. 네이티브 변경 감지 정책을 사용하는 경우 이 매개 변수가 적용되지 않습니다.

    • "disableOrderByHighWaterMarkColumn"은 상위 워터 마크 정책에서 사용하는 SQL 쿼리에서 ORDER BY 절이 생략되도록 합니다. 네이티브 변경 감지 정책을 사용하는 경우 이 매개 변수가 적용되지 않습니다.

  3. 필드 이름 또는 형식이 다르거나 검색 인덱스에서 여러 버전의 원본 필드가 필요한 경우 필드 매핑을 지정합니다.

  4. 다른 속성에 대한 자세한 내용은 인덱서 만들기를 참조하세요.

인덱서가 만들어지면 자동으로 실행됩니다. 이는 "disabled"를 true로 설정하여 방지할 수 있습니다. 인덱서 실행을 제어하려면 요청 시 인덱서를 실행하거나 일정에 배치합니다.

인덱서 상태 확인

인덱서 상태 및 실행 기록을 모니터링하려면 인덱서 상태 가져오기 요청을 보냅니다.

GET https://myservice.search.windows.net/indexers/myindexer/status?api-version=2020-06-30
  Content-Type: application/json  
  api-key: [admin key]

응답에는 상태 및 처리된 항목 수가 포함됩니다. 다음 예와 유사해야 합니다.

    {
        "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
        ]
    }

실행 기록에는 최대 50개의 가장 최근에 완료된 실행이 포함되며, 가장 최근의 실행이 먼저 나오도록 시간 역순으로 정렬됩니다.

새 행, 변경된 행 및 삭제된 행 인덱싱

SQL 데이터베이스에서 변경 내용 추적을 지원하는 경우 검색 인덱서는 후속 인덱서 실행에서 새 콘텐츠와 업데이트된 콘텐츠만 선택할 수 있습니다.

증분 인덱싱을 사용하도록 설정하려면 데이터 원본 정의에서 "dataChangeDetectionPolicy" 속성을 설정합니다. 이 속성은 테이블 또는 뷰에 사용되는 변경 내용 추적 메커니즘을 인덱서에 알려줍니다.

Azure SQL 인덱서의 경우 두 가지 변경 감지 정책이 있습니다.

  • "SqlIntegratedChangeTrackingPolicy"(테이블에만 적용됨)

  • "HighWaterMarkChangeDetectionPolicy"(테이블 및 뷰에 적용됨)

SQL 통합 변경 내용 추적 정책

효율적이고 삭제된 행을 식별할 수 있는 "SqlIntegratedChangeTrackingPolicy"를 사용하는 것이 좋습니다.

데이터베이스 요구 사항:

  • SQL Server 2012 SP3 이상(Azure VM에서 SQL Server를 사용하는 경우)
  • Azure SQL Database와 SQL Managed Instance
  • 테이블만(뷰 제외)
  • 데이터베이스에서 테이블에 대해 변경 내용 추적 설정
  • 테이블에 복합 기본 키(두 개 이상의 열을 포함하는 기본 키) 없음
  • 테이블에 클러스터된 인덱스가 없습니다. 이 문제를 해결하려면 모든 클러스터형 인덱스를 삭제하고 비클러스터형 인덱스로 다시 만들어야 하지만 클러스터형 인덱스 사용과 비교할 때 원본에서 성능이 영향을 받을 수 있습니다.

변경 감지 정책은 데이터 원본 정의에 추가됩니다. 이 정책을 사용하려면 다음과 같이 데이터 원본을 만들거나 업데이트합니다.

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"
    }

SQL 통합 변경 내용 추적 정책을 사용하는 경우 별도의 데이터 삭제 감지 정책을 지정하지 마세요. SQL 통합 변경 내용 추적 정책은 삭제된 행을 식별하는 지원 기능을 기본 제공합니다. 그러나 삭제된 행이 자동으로 감지되려면 검색 인덱스의 문서 키가 SQL 테이블의 기본 키와 동일해야 합니다.

참고 항목

SQL 테이블에서 많은 수의 행을 제거하기 위해 TRUNCATE TABLE을 사용할 때 인덱서를 다시 설정하여 행 삭제를 선택하도록 변경 추적 상태를 다시 설정해야 합니다.

상위 워터마크 변경 검색 정책

이 변경 감지 정책은 행이 마지막으로 업데이트된 버전 또는 시간을 캡처하는 테이블 또는 뷰의 “상위 워터 마크” 열을 사용합니다. 뷰를 사용하는 경우 상위 워터 마크 정책을 사용해야 합니다.

상위 워터 마크 열은 다음 요구 사항을 충족해야 합니다.

  • 모든 삽입 시 열의 값을 지정합니다.
  • 항목에 대한 모든 업데이트는 열의 값도 변경합니다.
  • 삽입 또는 업데이트할 때마다 이 열의 값이 증가합니다.
  • 다음 WHERE 및 ORDER BY 절이 포함된 쿼리를 효율적으로 실행할 수 있습니다. WHERE [High Water Mark Column] > [Current High Water Mark Value] ORDER BY [High Water Mark Column]

참고 항목

상위 워터 마크 열에는 rowversion 데이터 형식을 사용하는 것이 좋습니다. 다른 데이터 형식을 사용하는 경우 변경 추적이 인덱서 쿼리와 동시에 실행되는 트랜잭션의 모든 변경 내용을 캡처한다는 보장은 없습니다. 읽기 전용 복제본이 있는 구성에 rowversion을 사용하는 경우 주 복제본에서 인덱서를 가리켜야 합니다. 데이터 동기화 시나리오에는 주 복제본만 사용할 수 있습니다.

변경 감지 정책은 데이터 원본 정의에 추가됩니다. 이 정책을 사용하려면 다음과 같이 데이터 원본을 만들거나 업데이트합니다.

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]"
        }
    }

참고 항목

원본 테이블의 상위 워터 마크 열에 인덱스가 없는 경우 SQL 인덱서에서 사용하는 쿼리는 시간 초과될 수 있습니다. 특히 테이블에 행이 많은 경우 ORDER BY [High Water Mark Column] 절에 따라 인덱스가 효율적으로 실행돼야 합니다.

convertHighWaterMarkToRowVersion

상위 워터 마크 열에 rowversion 데이터 형식을 사용하는 경우 인덱서 구성에 convertHighWaterMarkToRowVersion 속성을 설정하는 것이 좋습니다. 이 속성을 true로 설정하면 다음과 같은 동작이 발생합니다.

  • 인덱서 SQL 쿼리의 상위 워터 마크 열에는 rowversion 데이터 형식을 사용합니다. 올바른 데이터 형식을 사용하면 인덱서 쿼리 성능이 향상됩니다.

  • 인덱서 쿼리를 실행하기 전에 rowversion 값에서 1을 뺍니다. 일 대 다 조인이 있는 뷰에 중복된 rowversion 값이 있는 행이 있을 수 있습니다. 1을 빼면 인덱서 쿼리에서 이러한 행을 누락하지 않습니다.

이 속성을 사용하도록 설정하려면 다음 구성을 사용하여 인덱서를 만들거나 업데이트합니다.

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "convertHighWaterMarkToRowVersion" : true } }
    }

queryTimeout

시간 초과 오류가 발생하는 경우 queryTimeout 인덱서 구성 설정을 기본값(5분)보다 높은 값으로 설정합니다. 예를 들어 제한 시간을 10분으로 설정하려면 다음 구성을 사용하여 인덱서를 만들거나 업데이트합니다.

    {
      ... other indexer definition properties
     "parameters" : {
            "configuration" : { "queryTimeout" : "00:10:00" } }
    }

disableOrderByHighWaterMarkColumn

ORDER BY [High Water Mark Column] 절을 사용하지 않도록 설정할 수도 있습니다. 그러나 인덱서 실행이 오류로 인해 중단된 경우 해당 시점에 거의 모든 행을 처리했더라도 나중에 실행될 때 모든 행을 다시 처리해야 하므로 이는 권장되지 않습니다. ORDER BY 절을 사용하지 않도록 설정하려면 인덱서 정의에서 disableOrderByHighWaterMarkColumn 설정을 사용합니다.

    {
     ... other indexer definition properties
     "parameters" : {
            "configuration" : { "disableOrderByHighWaterMarkColumn" : true } }
    }

Soft Delete 열 삭제 검색 정책

원본 테이블에서 행이 삭제된 경우 검색 인덱스에서도 해당 행을 삭제할 수 있습니다. SQL 통합 변경 내용 추적 정책을 사용하는 경우 이 작업이 자동으로 수행됩니다. 그러나 상위 워터 마크 변경 내용 추적 정책은 삭제된 행을 지원하지 않습니다. 그렇다면 어떻게 해야 할까요?

행이 테이블에서 실제로 제거된 경우 Azure AI Search에서 더 이상 존재하지 않는 레코드의 현재 상태를 유추할 방법이 없습니다. 그러나 "일시 삭제" 기술을 사용하여 테이블에서 제거하지 않고 논리적으로 행을 삭제할 수 있습니다. 테이블 또는 뷰에 열을 추가하고 이 열을 사용하여 행을 삭제된 것으로 표시합니다.

일시 삭제 기술을 사용하는 경우 데이터 원본을 만들거나 업데이트할 때 다음과 같이 일시 삭제 정책을 지정할 수 있습니다.

    {
        …,
        "dataDeletionDetectionPolicy" : {
           "@odata.type" : "#Microsoft.Azure.Search.SoftDeleteColumnDeletionDetectionPolicy",
           "softDeleteColumnName" : "[a column name]",
           "softDeleteMarkerValue" : "[the value that indicates that a row is deleted]"
        }
    }

softDeleteMarkerValue는 데이터 원본에서 JSON 형식으로 표현된 문자열이어야 합니다. 실제 값의 문자열 표현을 사용합니다. 예를 들어 삭제된 행이 값 1로 표시된 정수 열이 있는 경우 "1"을 사용합니다. 삭제된 행이 부울 true 값으로 표시된 BIT 열이 있는 경우 문자열 리터럴 "True" 또는 "true"를 사용합니다. 대소문자는 상관 없습니다.

Azure Portal에서 일시 삭제 정책을 설정하는 경우 일시 삭제 마커 값 주위에 따옴표를 추가하지 마세요. 필드 콘텐츠는 이미 문자열로 인식되었으며 자동으로 JSON 문자열로 변환됩니다. 위의 예제에서 포털의 필드에 1, True 또는 true를 입력하면 됩니다.

FAQ

Q: Always Encrypted 열을 인덱싱할 수 있나요?

아니요. Always Encrypted 열은 현재 Azure AI Search 인덱서에서 지원되지 않습니다.

Q: Azure의 IaaS VM에서 실행되는 SQL 데이터베이스에서 Azure SQL 인덱서를 사용할 수 있습니까?

예. 그러나 검색 서비스에서 데이터베이스에 연결할 수 있도록 허용해야 합니다. 자세한 내용은 Azure VM에서 Azure AI Search 인덱서로부터 SQL Server로의 연결 구성을 참조하세요.

Q: 온-프레미스에서 실행되는 SQL 데이터베이스에서 Azure SQL 인덱서를 사용할 수 있습니까?

직접 끌 수는 없습니다. 직접 연결은 권장되거나 지원되지 않습니다. 이렇게 하려면 데이터베이스를 인터넷 트래픽에 개방해야 하기 때문입니다. 고객은 Azure Data Factory와 같은 브리지 기술을 사용하여 이 시나리오를 성공적으로 수행했습니다. 자세한 내용은 Azure Data Factory를 사용하여 Azure AI Search 인덱스에 데이터 푸시를 참조하세요.

Q: 장애 조치(failover) 클러스터에서 데이터 원본으로 보조 복제본을 사용할 수 있습니까?

경우에 따라 다릅니다. 테이블 또는 뷰의 전체 인덱싱에 대해 보조 복제본을 사용할 수 있습니다.

증분 인덱싱의 경우 Azure AI Search는 SQL 통합 변경 내용 추적 및 상위 워터 마크라는 두 가지 변경 검색 정책을 지원합니다.

읽기 전용 복제본에서 SQL Database는 통합된 변경 내용 추적을 지원하지 않습니다. 따라서 상위 워터 마크 정책을 사용해야 합니다.

상위 워터 마크 열에는 rowversion 데이터 형식을 사용하는 것이 일반적으로 권장됩니다. 그러나 rowversion 사용 시, 읽기 전용 복제본에서는 지원되지 않는 MIN_ACTIVE_ROWVERSION 함수에 의존합니다. 따라서 rowversion를 사용하는 경우 인덱서가 주 복제본을 가리키도록 해야 합니다.

읽기 전용 복제본에서 rowversion을 사용하려고 하면 다음 오류가 표시됩니다.

"변경 내용 추적에 rowversion 열을 사용하는 것은 보조(읽기 전용) 가용성 복제본에서 지원되지 않습니다. 데이터 원본을 업데이트하고 주 가용성 복제본에 대한 연결을 지정하세요. 현재 데이터베이스 '업데이트 가능 여부' 속성은 'READ_ONLY'입니다."

Q: 상위 워터 마크 변경 내용 추적에 대체의 rowversion이 아닌 열을 사용할 수 있습니까?

권장되지 않습니다. 신뢰할 수 있는 데이터 동기화를 위해서는 rowversion만 허용됩니다. 그러나 애플리케이션 논리에 따라 다음과 같은 경우 안전할 수 있습니다.

  • 인덱서가 실행될 때 인덱싱되는 테이블에 미해결 트랜잭션이 있는지 확인할 수 있습니다(예를 들어 모든 테이블 업데이트가 일정에 따라 일괄 처리되고 Azure AI Search 인덱서 일정이 테이블 업데이트 일정과 겹치지 않도록 설정됨).

  • 모든 누락된 행을 선택하기 위해 전체 다시 인덱싱을 정기적으로 수행합니다.