Azure SQL Database 끼리 JOIN 사용하기

Elastic Database Query for cross- Azure SQL Database queries

지난번 포스트에서 Azure SQL Database 끼리 JOIN 이 안된다고 언급을 했었다.

하지만 지원을 하게 되었다. 언제부터인지는 모르겠지만. ㅎㅎㅎ

 

Azure SQL Database 끼리 JOIN 하는 방법을 알아보자.

잠시 아키텍쳐를 보자.

각 Azure SQL Database V12 개별 데이터베이스에 Elastic Query를 할 수 있다.

자 해보자.

각 Azure SQL Database 를 2개 만들었다.

각 데이터베이스에 아래와 같이 테이블 및 데이터를 생성하였다.

 

 

-- GHOSTSQL

CREATE TABLE T1 (COL1 INT)

INSERT INTO T1 VALUES (1),(2),(3)

-- OVERTOPSQL

CREATE TABLE T2 (COL1 INT)

INSERT INTO T2 VALUES (1),(4),(5)

 

 

아래부터 생성하여 JOIN을 만들어 보자

 

-- 생성하는 순서이다.

CREATE MASTER KEY

CREATE DATABASE SCOPED CREDENTIAL

CREATE/DROP EXTERNAL DATA SOURCE

CREATE/DROP EXTERNAL TABLE

 

-- GHOSTSQL 세션에서 만들기

 

1. Database scoped master key and credentials

CREATE MASTER KEY

CREATE DATABASE SCOPED CREDENTIAL

--DROP DATABASE SCOPED CREDENTIAL <credential_name>;

--DROP MASTER KEY;

 

-- 데이터베이스 마스터 키 확인하자.

select key_guid, * from sys.symmetric_keys

/*

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password';

CREATE DATABASE SCOPED CREDENTIAL <credential_name> WITH IDENTITY = '<username>',

SECRET = '<password>'

*/

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';

CREATE DATABASE SCOPED CREDENTIAL SqlUser WITH IDENTITY = 'ghost', SECRET = 'qwer1234!'

 

 

2. External data sources

/*

<External_Data_Source> ::=

CREATE EXTERNAL DATA SOURCE <data_source_name> WITH

(TYPE = RDBMS,

LOCATION = '<fully_qualified_server_name>',

DATABASE_NAME = '<remote_database_name>',

CREDENTIAL = <credential_name>

) [;]

-- DROP EXTERNAL DATA SOURCE <data_source_name>[;]

*/

 

 

CREATE EXTERNAL DATA SOURCE RemoteReferenceData

WITH

(

    TYPE=RDBMS,

    LOCATION='overtopsvr.database.windows.net',

    DATABASE_NAME='OVERTOPSQL',

    CREDENTIAL= SqlUser

);

 

select * from sys.external_data_sources;

 

3.External Tables

/*

CREATE EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name . ] table_name

( { <column_definition> } [ ,...n ])

{ WITH ( <rdbms_external_table_options> ) }

)[;]

 

<rdbms_external_table_options> ::=

DATA_SOURCE = <External_Data_Source>,

[ SCHEMA_NAME = N'nonescaped_schema_name',]

[ OBJECT_NAME = N'nonescaped_object_name',]

GO

 

DROP EXTERNAL TABLE [ database_name . [ schema_name ] . | schema_name. ] table_name[;]

GO

 

*/

 

CREATE EXTERNAL TABLE [dbo].[ExT_T2](

col1 int NOT NULL,

)

WITH

(

    DATA_SOURCE = RemoteReferenceData,

    SCHEMA_NAME = 'dbo',

    OBJECT_NAME = 'T2'

);

 

select * from sys.external_tables;

 

-- OVERTOPSQL 데이터베이스의 T2 데이터불러오기

select * from dbo.ExT_T2

 

 

-- 번외.1) 컬럼을 같이 만들어야 되나?

    답변 : 같은 테이블스키마로 생성되어야 한다.인덱스 기타 필요한지는 모르겠다.

 

    CREATE EXTERNAL TABLE [dbo].[ExT_T2_ADD](

     col1 int NOT NULL,

     col2 char(10) null

    )

    WITH

    (

        DATA_SOURCE = RemoteReferenceData,

        SCHEMA_NAME = 'dbo',

        OBJECT_NAME = 'T2'

    );

    GO

 

    select * from dbo.ExT_T2_ADD

    /*

    메시지 46823, 수준 16, 상태 2, 줄 100

    Error retrieving data from one or more shards. The underlying error message received was: 'Invalid column name 'col2'.'.

    */

 

-- JOIN 활용해보자. 2개의 Azure SQL DataBases 를 JOIN 해보자.

 

select * from GHOSTSQL.dbo.T1

    select * from OVERTOPSQL.dbo.T2

    /*

    Reference to database and/or server name in 'OVERTOPSQL.dbo.T2' is not supported in this version of SQL Server.

    */

 

SELECT A.*, B.*

FROM GHOSTSQL.dbo.T1 as A

INNER JOIN ExT_T2 as B on B.col1 = A.col1

 

오호 이렇게 JOIN 하면 된다.

 

좀 더 다양한 방법이 있을 것 같다.

추후 포스팅할 예정이다.

 -- 번외.2) 같은 테이블명으로 생성해도 가능하다. WITH 옵션에서 오브젝트정보를 빼고 생성가능하다.

CREATE EXTERNAL TABLE [dbo].[T2]( 

   col1 int NOT NULL, 

) 

WITH 

( 

DATA_SOURCE = RemoteReferenceData,

); 

-- 번외.3) 이미 T2 테이블이 존재하면 생성이 안된다.

CREATE EXTERNAL TABLE [dbo].[T2]( 

   col1 int NOT NULL, 

) 

WITH 

( 

DATA_SOURCE = RemoteReferenceData,

); 

/*

테이블명이 존재하면 아래와 같이 오류가 발생하다.

There is already an object named 'T2' in the database.

*/

-- 번외.4) WITH옵션에서 오브젝트를 정의하지 않고 테이블명을 다르게 하게 되면 오류가 발생한다.

CREATE EXTERNAL TABLE [dbo].[T2_EXternal]( 

   col1 int NOT NULL, 

) 

WITH 

( 

DATA_SOURCE = RemoteReferenceData,

); 

/*

메시지 46823, 수준 16, 상태 2, 줄 143

Error retrieving data from one or more shards.  The underlying error message received was: 'Invalid object name 'dbo.T2_'.'.

*/

 

[참고문서]

Azure SQL Database elastic database query overview (preview)

https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-overview/?wt.mc_id=WW_CE_DM_OO_BLOG_NONE

Elastic database query for cross-database queries (vertical partitioning)

https://azure.microsoft.com/en-us/documentation/articles/sql-database-elastic-query-vertical-partitioning/

Cross-Database Queries in Azure SQL Database

https://azure.microsoft.com/ko-kr/blog/querying-remote-databases-in-azure-sql-db/