ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행
ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행
· Version : SQL Server, Azure CosmosDB
Azure CosmosDB는 기존의 SQL Server와 같은 클래식 데이터베이스에서 CosmosDB 컬렉션을 조회할 수 있는 ODBC 드라이버를 제공한다. 이번 포스트에서는 Transaction-SQL을 사용하여 SQL Server에서 CosmosDB 컬렉션을 쿼리하는 방법을 설명한다.
SQL Server에서 CosmosDB를 쿼리하는 이유는 무엇일까? CosmosDB는 문서 및 기타 비관계형 데이터 유형을 저장 및 검색하고, 필터링하고 정렬할 수 있는 SQL / API를 제공한다. 그러나 경우에 따라 GROUP BY, HAVING, 분석 기능이 있는 더 복잡한 쿼리를 실행하거나 CosmosDB의 비관계형 데이터를 SQL Server 테이블에 저장하는 데이터와 조인해야 하는 경우가 있다. 이 경우 CosmosDB의 데이터를 쿼리하기 위해 Transaction-SQL의 모든 기능을 활용할 수 있다.
[CosmosDB 설정]
먼저 CosmosDB계정을 생성하고 컬렉션에 실습할 데이터를 추가 한다. 데이터베이스 WWI 및 Orders 라는 콜렉션을 생성하고, 세 개의 문서를 추가한다.
실습 데이터 등록이 완료 되었으면, SQL API를 사용하여 컬렉션에 액세스한다.
[드라이버 설정]
SQL Server가 설치된 컴퓨터에 CosmosDB용 ODBC 드라이버를 설치해야한다. 이 드라이버를 설치하면 DSN에서 ODBC 소스를 설정하고 연결을 테스트해야한다.
· ODBC 드라이버 다운로드 : https://aka.ms/cosmos-odbc-64x64
[CosmosDB 쿼리]
모든 설정 후에 기존 SQL Server에서 OPENROWSET 함수를 사용하여 CosmosDB DSN을 지정하여 CosmosDB 데이터를 쿼리 할 수 있다.
실습 데이터의 결과를 보면 CosmosDB에서 3개의 문서를 나타내는 3개의 행을 얻게 된다. 누락된 필드는 NULL로 반한된다. 또한 아래 스크립트처럼 필터를 사용할 수도 있다.
|
SELECT a.* FROM OPENROWSET('MSDASQL', 'DSN=cosmosdb1', 'select * from Orders where billto_Name = ''John Smith''') as a |
실습데이터의 JSON 형식을 보면 shipTo 또는 billTo와 같이 복잡한 JSON 객체가 모두 동일한 ROW로 표시되고 있는 것을 알 수 있다. 즉, 모든 필드가 <object name>_ <field name> 형식으로 반환된다는 것이다. 하위 오프젝트가 있는 것으로 예상되는 경우 사용자는 이를 알고 있어야 한다. 또한 필자의 경우 태그와 같은 배열 속성이 매핑되거나 반환되지 않는다.
[결론]
CosmosDB용 ODBC 드라이버를 사용하면 CosmosDB 데이터에 대해 Transact-SQL 쿼리를 실행할 수 있다. 이는 CosmosDB에 저장된 원격 데이터에 대해서 데이터 분석이 필요한 경우 유용햘 수 있다. 이 경우 결과를 필터링 하고 필요한 필드만 선택한 다음 전체 Transact-SQL 언어를 사용하여 SQL Server에서 다양한 분석을 수행하는 조건자를 사용하여 CosmosDB에 보낼 수 있다.
(이 글을 쓰는 시점에는) CosmosDB드라이버는 Azure SQL에 설치되어 있지 않으며 자체 드라이버를 추가할 수 없기 때문에 이는 Azure SQL 데이터베이스가 아닌 SQL Server에서만 가능하다.
[참고자료]
2019-09-09 / Sungwook Kang / http://sungwookkang.com
Azure CosmosDB, SQL Server에서 Azure CosmosDB 연결하기, CosmosDB ODBC