Azure Synapse SQL Pool(SQL DW)에 대한 SAS Viya(CAS) 액세스
SAS Viya 4 버전에서 Viya(CAS) 사용자는 MS-SQL Server SAS Data Connector를 사용하여 Azure Synapse SQL-Pool(이전의 SQL DW) 데이터베이스 테이블에 액세스할 수 있습니다. 또한 SAS Viya 사용자는 Azure Synapse를 통해 ADLS2에 저장된 다양한 타입의 데이터 파일에서 (parquet, orc, Json 등) CAS를 로드할 수 있습니다. 사용자는 이러한 데이터 파일을 CAS에 로드하기 전에 Azure Synapse 하에 열 데이터 테이블로 읽을 수 있습니다.
이 포스팅은 SAS Viya 4(CAS) 환경에서 Azure Synapse SQL Pool(SQL DW) 데이터베이스에 접근하는 방법에 관해 소개하고자 합니다.
Azure Synapse SQL Pool은 (전 SQL DW) Synapse SQL을 사용할 때 프로비저닝되는 분석 리소스 모음을 나타냅니다. Azure Synapse SQL Pool은 n개의 서버 및 머신에서 분산 모드로 클라우드에서 실행되는 MS-SQL 서버 데이터베이스입니다.
MS-SQL Server SAS Data Connector를 사용하여 Viya(CAS) 사용자가 Azure Synapse 데이터베이스에 액세스할 수 있습니다. 사용자는 직렬 또는 다중 노드 데이터 로드 방식을 통해 Azure Synapse 데이터베이스 테이블에서 CAS를 로드할 수 있습니다. 또한 중간 데이터 파일에 Azure Storage를 사용하여 CAS 테이블에서 Azure Synapse SQL Pool 테이블로의 벌크 데이터 저장을 지원합니다. 벌크 로드 프로세스는 COPY INTO 명령을 사용하여 데이터를 Synapse 데이터베이스 테이블로 푸시하기 전에 대용량 또는 대량 데이터를 ADLS2 Blob 컨테이너에 업로드합니다.
전제 조건
- CAS 노드(POD)에 MS-SQL Server 데이터 커넥터가 있는 SAS Viya 4
- 중앙에 위치한 ODBC.ini 및 Azure 액세스 키의 저장 및 액세스를 위해 AKS Persistence Volume을 사용하여 CAS 노드에 탑재된 Azure 파일 공유
- Azure Synapse 데이터베이스에 액세스하기 위한 odbc.ini의 사용자 지정 DSN 이름
- Azure Synapse 데이터베이스에 대한 사용자 액세스
대량 로드의 경우:
- Storage Blob Data Contributor 역할이 있는 Azure Storage 계정에 대한 사용자 권한
- Azure Data Lake 및 Azure Storage에 액세스할 수 있는 권한이 있는 Azure 사용자 애플리케이션
- Blob Storage에 액세스하기 위해 CAS 노드에서 사용할 수 있는 중앙 위치의 Azure 액세스 키
- Blob Storage에 액세스하기 위해 관리 ID 또는 SAS(Shared Access Signature) 토큰으로 구성된 MS-SQL 서버
데이터 액세스 경로
다음 그림은 Azure Synapse SQL Pool(SQL DW) 데이터베이스 테이블에 대한 SAS Viya 4(CAS) 환경 액세스를 설명합니다. 또한 CAS에서 Azure Synapse Database 테이블로 대량 데이터 업로드를 위한 데이터 경로를 설명합니다. 사용자는 직렬 또는 다중 노드 데이터 로드 방법을 사용하여 Azure Synapse 데이터베이스 테이블로부터 CAS를 로드할 수 있습니다.
크게 보시려면 이미지를 클릭하시기 바랍니다. 모바일 버전: 이미지를 보시려면 페이지 하단의 풀 버전을 선택하세요.
대량 로드에 대한 Azure Storage 계정 구성
CAS 테이블에서 Azure Synapse SQL Pool 데이터베이스로 데이터를 대량 로드하려면 사용자에게 읽기 및 쓰기 권한으로 정의된 Azure 스토리지 위치가 필요합니다. Azure 운영 애플리케이션은 통신을 통해 CAS 노드(POD)에서 Azure 액세스 키를 얻을 수 있습니다.
자세한 단계는 ALDS2 Blob 스토리지에 액세스하기 위해 Azure 사용자 애플리케이션 및 스토리지 계정을 구성하기 위한 내용을 다루는 문서인 Cas Accessing Azure Data-lake Files를 통해 확인하실 수 있습니다.
대량 로드를 위한 Azure MS-SQL Server 구성
Azure Synapse 데이터베이스를 대량 로드하려면 MS-SQL 서버에 읽기 및 쓰기 권한이 있는 Azure ADLS2 Blob Storage에 대한 액세스 권한이 필요합니다. CASLIB 명령문에서 SAS 토큰을 사용하거나 --assign-identity로 MS-SQL 서버를 구성하고 Blob Storage에 대한 원활한 액세스를 위해 Storage Blob Data Contributor 역할을 SQL 서버 principle id에 할당합니다. 다음 Azure CLI는 --assign-identity를 사용하여 MS-SQL Server 생성 및 Server Principle id에 대한 역할 할당에 대해 설명합니다.
CLI 코드:
|
STUDENT=”Myname” AKS_MY_LOCATION=”eastus2” AKS_MY_RESOURCE_GROUP="${STUDENT}viya4" AKS_MY_USER=viyadep
srv=sqlsrv sql_server_name=$AKS_MY_RESOURCE_GROUP$srv sql_server_admin_user=$AKS_MY_USER sql_server_admin_pwd=”XXXXXXXXX” sqldw_name=geldwpool
### Create a SQL Server with –assign-identity #### sql_serv_sts=`az sql server create \ --name $sql_server_name \ --admin-user $sql_server_admin_user \ --admin-password $sql_server_admin_pwd \ --resource-group $AKS_MY_RESOURCE_GROUP \ --location $AKS_MY_LOCATION \ --enable-public-network true \ --assign-identity \ -o tsv `
### Create an Azure Synapse MS-SQL POOL Database in SQL Server ### sql_serv_dw_sts=`az sql dw create \ --name $sqldw_name \ --server $sql_server_name \ --resource-group $AKS_MY_RESOURCE_GROUP \ --service-objective DW100c \ --tags name=$STUDENT \ --zone-redundant false `
### Assign Storage Blob Data Contributor Role to MS-SQL server managed Identity at Resource group level scope .###
spID=`az resource list -n $sql_server_name --query [*].identity.principalId --out tsv ` rgID=`az group show -g $AKS_MY_RESOURCE_GROUP --query id -o tsv`
server_role_tst=`az role assignment create --assignee $spID --role 'Storage Blob Data Contributor' --scope $rgID` |
맞춤형 DSN
호스트 이름, 데이터베이스 이름 및 SSL 라이브러리 경로 등을 사용하여 Azure SQL Pool 데이터베이스에 액세스하기 위한 odbc.ini 파일의 사용자 지정된 DSN 항목입니다.
|
…….… …………… [sqls_gelws] Driver=/opt/sas/viya/home/lib64/accessclients/lib/S0sqls28.so Description=SAS ACCESS to MS SQL Server AEKeyCacheTTL=-1 AEKeystorePrincipalId= AEKeystoreClientSecret= AlternateServers= AlwaysReportTriggerResults=0 AnsiNPW=1 ApplicationIntent=0 ApplicationName= ApplicationUsingThreads=1 AuthenticationMethod=1 BulkBinaryThreshold=32 BulkCharacterThreshold=-1 BulkLoadBatchSize=1024 BulkLoadFieldDelimiter= BulkLoadOptions=2 BulkLoadRecordDelimiter= BulkLoadThreshold=2 ColumnEncryption=Disabled ConnectionReset=0 ConnectionRetryCount=0 ConnectionRetryDelay=3 CryptoProtocolVersion=TLSV1,TLSV1.1,TLSV1.2,SSLv3,SSLv2 Database=geldwpool Domain= EnableBulkLoad=0 EnableQuotedIdentifiers=0 EnableServerSideCursors=1 EnableScrollableCursors=4 EncryptionMethod=1 FailoverGranularity=0 FailoverMode=0 FailoverPreconnect=0 FetchTSWTZasTimestamp=0 FetchTWFSasTime=1 GSSClient=native HostName=utkumaviya4sqlsrv.database.windows.net HostNameInCertificate= #IANAAppCodePage= InitializationString= KeepAlive=0 Language= LoadBalanceTimeout=0 LoadBalancing=0 LoginTimeout=15 LogonID=viyadep MaxPoolSize=100 MinPoolSize=0 MultiSubnetFailover=0 PacketSize=-1 Pooling=0 PortNumber=1433 PRNGSeedFile=/dev/random PRNGSeedSource=0 ProxyHost= ProxyMode=0 ProxyPassword= ProxyPort= ProxyUser= QueryTimeout=0 ReportCodepageConversionErrors=0 SnapshotSerializable=0 TrustStore= TrustStorePassword= ValidateServerCertificate=0 WorkStationID= XMLDescribeType=-10 SSLLibName=/usr/lib64/libssl.so.10 CryptoLibName=/usr/lib64/libcrypto.so.10 …….… …………… |
Azure Synapse SQL Pool 테이블에서 CAS 직렬 로드 및 저장
odbc.ini의 사용자 지정 DSN 항목으로 구성된 Viya4(CAS) 환경에서는 다음 코드를 사용하여 CAS 데이터 테이블을 읽고 Azure Synapse Database에 쓸 수 있습니다. 코드는 직렬 방식을 사용하여 CAS 데이터를 로드하고 저장합니다.
코드:
|
CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true);
%let MYUID='viyadep'; %let MYPWD='XXXXXXX'; %let MYDSN='sqls_gelws' ; %let MYSCHEMA='dbo';
caslib azsqlws desc='Microsoft SQL Server Caslib' dataSource=srctype='sqlserver', username=&MYUID, password=&MYPWD, schema=&MYSCHEMA, sqlserver_dsn=&MYDSN );
/* ## Save CAS data to Azure Synapse pool database table */ proc casutil outcaslib="azsqlws" incaslib="azsqlws"; load data=sashelp.cars casout="cars" replace; save casdata="cars" casout="cars" replace; list files; quit;
/* ## CAS load from Azure Synapse poll database table. */ proc casutil incaslib="azsqlws" outcaslib="azsqlws"; load casdata="cars" casout="cars_new" replace; list tables; quit;
CAS mySession TERMINATE; |
출력:
|
......... .............. 83 84 caslib azsqlws desc='Microsoft SQL Server Caslib' 85 dataSource=(srctype='sqlserver', 86 username=&MYUID, 87 password=&MYPWD, 88 sqlserver_dsn=&MYDSN ); NOTE: Executing action 'table.addCaslib'. NOTE: 'AZSQLWS' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'AZSQLWS'. NOTE: Action 'table.addCaslib' used (Total process time): NOTE: Action to ADD caslib AZSQLWS completed for session MYSESSION. 89
78 /* Save CAS data to Azure Synapse pool database table */ 79 proc casutil outcaslib="azsqlws" incaslib="azsqlws"; NOTE: The UUID 'f1cbd14f-2b65-6842-b82d-581685eb4072' is connected using session MYSESSION. 80 load data=sashelp.cars casout="cars" replace; NOTE: The INCASLIB= option is ignored when using the DATA= option in the LOAD statement. NOTE: Executing action 'table.addTable'. NOTE: Action 'table.addTable' used (Total process time): NOTE: SASHELP.CARS was successfully added to the "AZSQLWS" caslib as "CARS". 81 save casdata="cars" casout="cars" replace; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to SQLNOTE: Cloud Analytic Services saved the file cars in caslib AZSQLWS.NOTE: Action 'table.save' used (Total process time): NOTE: The Cloud Analytic Services server processed the request in 47.475573 seconds.
78 /* CAS load from Azure Synapse poll database table. */ 79 proc casutil incaslib="azsqlws" outcaslib="azsqlws"; NOTE: The UUID 'f1cbd14f-2b65-6842-b82d-581685eb4072' is connected using session MYSESSION. 80 load casdata="cars" casout="cars_new" replace; NOTE: Executing action 'table.loadTable'. NOTE: Performing serial LoadTable action using SAS Data Connector to SQL Server.WARNING: WARNING: [SAS][ODBC SQL Server Wire Protocol driver]Setting QuotedIdentifier to 'OFF' is not supported with this server. NOTE: Cloud Analytic Services made the external data from cars available as table CARS_NEW in caslib azsqlws. NOTE: Action 'table.loadTable' used (Total process time): ........... .................. |
결과 :
Azure Synapse SQL Pool 테이블에서 CAS 다중 노드 로드 및 저장
odbc.ini의 사용자 지정 DSN 항목으로 구성된 Viya4(CAS) 환경에서는 다음 코드를 가지고 다중 노드 방법을 사용하여 Azure Synapse 테이블에서 CAS를 로드할 수 있습니다. CASLIB 매개변수 numreadnodes= 및 numwritenodes=는 다중 노드 데이터 로드 및 저장 방법을 트리거합니다. odbc.ini는 Azure 파일 공유의 중앙에 위치하며 모든 CAS 노드(POD)에서 사용할 수 있습니다.
코드:
|
CAS mySession SESSOPTS=(CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true);
%let MYUID='viyadep'; %let MYPWD='XXXXXXXX'; %let MYDSN='sqls_gelws' ; %let MYSCHEMA='dbo';
caslib azsqlws desc='Microsoft SQL Server Caslib' dataSource=(srctype='sqlserver', username=&MYUID, password=&MYPWD, schema=&MYSCHEMA, sqlserver_dsn=&MYDSN, numreadnodes=10, numwritenodes=10 );
proc casutil incaslib="azsqlws" outcaslib="azsqlws"; load casdata="fish_sas" casout="fish_sas" options=(sliceColumn="weight") replace; list tables; quit;
CAS mySession TERMINATE; |
로그 출력:
|
......... ............ 84 caslib azsqlws desc='Microsoft SQL Server Caslib' 85 dataSource=(srctype='sqlserver', 86 username=&MYUID, 87 password=&MYPWD, 88 sqlserver_dsn=&MYDSN 89 numreadnodes=10,numwritenodes=10, 93 ); NOTE: Executing action 'table.addCaslib'. NOTE: 'AZSQLWS' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'AZSQLWS'. NOTE: Action 'table.addCaslib' used (Total process time):
77 78 proc casutil incaslib="azsqlws" outcaslib="azsqlws"; NOTE: The UUID 'e6ff8b32-53b2-7548-8ea8-ae83d9734618' is connected using session MYSESSION. |
결과 :
CAS 테이블에서 Azure Synapse SQL Pool 테이블로 대량 로드
odbc.ini의 사용자 지정 DSN 항목으로 구성된 SAS Viya 4(CAS) 환경을 통해 ADLS2 Blob Storage에 대한 액세스를 위한 MS-SQL Server 및 디바이스 코드 방법을 사용하여 리소스 액세스를 위한 Azure 사용자 애플리케이션등과 같은 두가지 방식을 통해 다음과 같은 코드로 CAS 테이블을 Azure Synapse 데이터베이스 테이블에 대량 로드합니다. odbc.ini 및 Azure 액세스 키는 Azure 파일 공유의 중앙에 있으며 모든 CAS 노드(Pod)에서 사용 가능합니다.
Azure Synapse에 대한 CAS 테이블 대량 로드는 다단계 프로세스를 거칩니다. 첫 번째 단계에서 CAS는 직렬 데이터베이스 연결을 사용하여 Azure 시냅스 데이터베이스에 빈 테이블를 구성합니다. 두 번째 단계에서 벌크는 모든 CAS 노드에서 ADLS2 스토리지로 데이터 파일을 로드합니다. 세 번째 단계에서는 COPY INTO 명령을 SQL 데이터베이스 서버에 올려 ADLS2 스토리지에서 Synapse 데이터베이스 테이블로 데이터를 푸시합니다.
BULK load 명령문과 함께 CASLIB를 처음 사용하는 경우라면 Azure 인증에 대한 오류 메시지가 나타납니다. SAS 로그에는 Azure 액세스 키를 인증하고 생성하기 위한 Microsoft 장치 인증 URL 및 장치 코드가 표시됩니다.
데이터 요소에 특수 문자가(예: "",',`,!,#,$,@ 등) 포함된 CAS 테이블을 대량 로드하려면 CASLIB 명령문에서 ESCAPE=YES를 작성합니다. 주의해야 할 점은 해당 옵션을 사용하게 되면 데이터 요소의 추가 스캔 및 처리가 필요하므로 성능이 저하될 수 있습니다.
디버깅을 위해 ADLS2 저장 위치 내 임시 파일을 확인하려면 CASLIB 명령에서 deleteDataFiles=false를 사용하면 됩니다.
코드:
|
%let MYSTRGACC="utkumaviya4adls2"; %let MYSTRGFS="fsdata"; %let MYFOLDER="bl_data"; %let MYAZUID="MANAGED IDENTITY"; %let MYDNSSUFFIX="dfs.core.windows.net" ; %let MYTNTID="b1c14d5c-3625-45b3-XXXXXXXXXXXXXXXXXX"; %let MYAPPID="a2e7cfdc-93f8-4f12-XXXXXXXXXXXXXXXXXX";
%let MYUID='viyadep'; %let MYPWD='XXXXXXXXX'; %let MYDSN='sqls_gelws'; %let MYSCHEMA='dbo';
CAS mySession SESSOPTS=(azuretenantid=&MYTNTID CASLIB=casuser TIMEOUT=99 LOCALE="en_US" metrics=true);
caslib azsqlws desc='Microsoft SQL Server Caslib' dataSource=(srctype='sqlserver', username=&MYUID, password=&MYPWD, Schema=&MYSCHEMA, sqlserver_dsn=&MYDSN , identity=&MYAZUID ,bulkload='true', FOLDER=&MYFOLDER, accountname=&MYSTRGACC, filesystem=&MYSTRGFS, dnsSuffix=dfs.core.windows.net, applicationId=&MYAPPID,);
/* ## Save CAS data to Azure Synapse pool database table */ proc casutil outcaslib="azsqlws" incaslib="azsqlws"; load data=sashelp.prdsal2 casout="prdsal2" replace; save casdata="prdsal2" casout="prdsal2" replace; list files; quit;
CAS mySession TERMINATE; |
에러 로그:
|
……… …………………………….. ERROR: To sign in, use a web browser to open the page https://microsoft.com/devicelogin and enter the code XXXXXXXXX to authenticate ……… …………………………….. |
로그 출력:
|
...... ........ 93 94 caslib azsqlws desc='Microsoft SQL Server Caslib' 95 dataSource=(srctype='sqlserver', 96 username=&MYUID, 97 password=&MYPWD, 98 Schema=&MYSCHEMA, 99 sqlserver_dsn=&MYDSN , 100 identity=&MYAZUID , 101 bulkload='true', 102 FOLDER=&MYFOLDER, 103 accountname=&MYSTRGACC, 104 filesystem=&MYSTRGFS, 105 dnsSuffix=dfs.core.windows.net, 106 applicationId=&MYAPPID 108 ); NOTE: Executing action 'table.addCaslib'. NOTE: 'AZSQLWS' is now the active caslib. NOTE: Cloud Analytic Services added the caslib 'AZSQLWS' ..... ............ 117 /* ## Save CAS data to Azure Synapse pool database table */ 118 proc casutil outcaslib="azsqlws" incaslib="azsqlws"; NOTE: The UUID '1b53fcc7-1f8e-1a48-8d22-5e90a0641bb1' is connected using session MYSESSION ....... .................. 120 save casdata="prdsal2" casout="prdsal2" replace; NOTE: Executing action 'table.save'. NOTE: Performing serial SaveTable action using SAS Data Connector to SQL Server. WARNING: WARNING: [SAS][ODBC SQL Server Wire Protocol driver]Setting QuotedIdentifier to 'OFF' is not supported with this server. The value of EnabledQuotedIdentifiers has been changed to 1 WARNING: WARNING: [SAS][ODBC SQL Server Wire Protocol driver]Setting QuotedIdentifier to 'OFF' is not supported with this server. The value of EnabledQuotedIdentifiers has been changed to 1 NOTE: Cloud Analytic Services saved the file prdsal2 in caslib AZSQLWS. NOTE: Action 'table.save' used (Total process time): ..... ...................... |
결과:
다음 스크린샷은 Synapse Database 테이블에 복사하기 전에 Azure ADLS2 Blob Storage에서 생성된 중간 파일을 보여줍니다. 중간 파일은 CASLIB 명령에서 deleteDataFiles =false를 사용하여 Blob Storage 폴더에서 삭제되지 않은 상태이며 기본값으로 true로 설정되어 있습니다.
Azure Synapse CASLIB를 만들기 위한 사용자 인터페이스 옵션
VA 어플리케이션은 새로운 데이터 원본을 연결할 때 "Azure Synapse SQL"이라는 새 원본 타입과 함께 제공합니다.
제약 및 제한 사항
- CAS는 항상 BULK 로드 프로세스를 사용하여 새 Synapse 테이블에 데이터를 저장합니다. 기존 Synapse 데이터베이스 테이블에 CAS 데이터 추가는 지원되지 않습니다.
- 해결 방법으로 사용자는 데이터를 Synapse "Temporary/Staging" 테이블로 BULK 로드하고 별도로 명시적 데이터베이스 SQL을 실행하여 기존 데이터베이스 테이블에 데이터를 추가할 수 있습니다.
- Synapse Bulk Load는 단방향 트래픽에서만 가능하며 즉 프로세스가 CAS로부터 Azure Synapse로 데이터를 업로드합니다. Azure Synapse로부터 CAS로의 대량 다운로드는 불가합니다.
링크:
참고 문헌:
Microsoft SQL Server Data Connector
Bulk Loading with Microsoft SQL Server
What is Azure Synapse SQL Pool ?
참고 논문:
Cas Accessing Azure Data-lake Files
Manage Azure Access Key with AZUREAUTHCACHELOC
여기에서 SAS Global Enablement and Learning에서 더 많은 기사를 찾아보실 수 있습니다.
Special offer for SAS Communities members
Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.
