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에서만 가능하다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/11/running-azure-cosmosdb-queries-from-sql-server-using-odbc-driver/

 

2019-09-09 / Sungwook Kang / http://sungwookkang.com

 

Azure CosmosDB, SQL Server에서 Azure CosmosDB 연결하기, CosmosDB ODBC