테이블, 대량 가져오기 및 쿼리 데이터 만들기

  • 10분

대학에는 현재 텍스트 파일에 있는 데이터를 저장할 장소가 필요합니다. 데이터 액세스 기능을 향상하기 위해 관계형 데이터를 만들려고 합니다. Azure SQL Database의 단일 데이터베이스를 이 데이터의 스토리지 서비스로 선택했습니다. SQL Database를 살펴보고 데이터를 업로드 및 쿼리하는 방법을 알아보겠습니다.

Azure Portal을 사용하여 단일 데이터베이스 만들기

SQL Database는 최신의 안정적인 Microsoft SQL Server 데이터베이스 엔진 버전을 기반으로 하는 관계형 데이터베이스 서비스입니다. SQL Database는 안정적이고 안전하며 사용하기 쉬운 고성능 데이터베이스입니다. SQL Database를 사용하여 선택한 프로그래밍 언어로 새로운 앱, 웹 사이트 및 마이크로 서비스를 구축할 수 있으며 인프라를 관리할 필요가 없습니다.

Azure Portal이나 Azure PowerShell 또는 CLI를 사용하여 단일 데이터베이스를 만들 수 있습니다.

  1. Azure Portal 메뉴에서 리소스 만들기를 선택합니다.

    Screenshot of Azure portal menu and Create a resource option.

  2. 데이터베이스를 선택한 다음, SQL Database를 선택합니다.

    Screenshot of the Databases and SQL Database options.

  3. CLI를 사용하려면 az sql server create 및 az sql db create 명령을 실행합니다.

  4. PowerShell을 사용하려면 New-AzSqlServer 및 New-AzSqlDatabase 명령을 실행합니다.

단일 데이터베이스를 만드는 경우 데이터베이스를 관리할 서버를 지정하라는 메시지가 표시됩니다. 새 서버를 만들거나 기존 서버를 사용할 수 있습니다.

새 서버를 만들고 SQL 인증을 사용하도록 선택하면 서버 관리자 사용자 이름과 암호를 지정하라는 메시지가 표시됩니다. 이러한 자격 증명을 사용하여 서버에 연결하여 관리 작업을 수행하고 서버가 제어하는 데이터베이스에 액세스합니다. SQL Database는 Microsoft Entra 인증도 지원합니다. SQL 및 Microsoft Entra 인증을 모두 사용하도록 선택할 수도 있습니다. 그러나 새로운 서버를 생성할 때 항상 관리자를 설정하거나 관리자 계정을 생성해야 합니다. 그런 다음 Microsoft Entra ID에 저장된 계정에 대한 액세스 권한을 부여합니다.

잠재적으로 악성인 프로세스를 차단하기 위해 각 데이터베이스 서버는 방화벽으로 보호됩니다. 다른 Azure 서비스에 대해 방화벽을 열 수 있습니다. 또한 IP 주소나 주소 범위를 기준으로 다른 컴퓨터에 대한 액세스를 선택적으로 허용할 수 있습니다. SQL Database는 다음을 수행할 수 있는 고급 데이터 보안도 제공합니다.

  • 테이블의 개별 열에 있는 데이터의 민감도를 지정합니다.
  • 데이터베이스의 취약성을 평가하고 필요한 수정 단계를 수행합니다.
  • 위협이 감지되면 경고를 보냅니다.

할당할 메모리, I/O 및 CPU 리소스를 지정하는 vCore(가상 코어) 모델을 사용하여 리소스를 구성합니다. 컴퓨팅 및 스토리지 리소스를 독립적으로 크기 조정할 수 있습니다. 또는 DTU(데이터베이스 트랜잭션 단위)를 기준으로 리소스를 할당할 수 있습니다. DTU는 벤치마킹된 트랜잭션을 수행하는 데 필요한, 리소스의 조정된 비용을 측정하는 단위입니다.

여러 데이터베이스가 있고 데이터베이스의 리소스 요구가 변동되는 경우 SQL 탄력적 풀을 사용할 수 있습니다. 이 기능을 사용하면 수요에 따라 풀링된 데이터베이스 간에 리소스 풀을 공유할 수 있습니다.

데이터베이스를 생성할 때 데이터 수집 방법도 지정합니다. 데이터 정렬은 데이터베이스에서 데이터를 정렬하고 비교하는 데 사용하는 규칙을 정의합니다. 텍스트 데이터에 사용할 문자 집합도 지정합니다. 데이터베이스를 만든 후에 데이터 정렬을 변경할 수 있지만, 데이터베이스에 데이터가 포함된 후에는 데이터 정렬을 변경하지 않는 것이 가장 좋습니다.

테이블 만들기

다음 도구 중 하나를 사용하여 테이블을 만들 수 있습니다.

  • Azure Portal의 쿼리 편집기
  • sqlcmd 유틸리티 및 Cloud Shell
  • SQL Server Management Studio

어떤 도구를 선택하든 CREATE TABLE T-SQL(Transact-SQL) 명령을 사용하여 테이블을 정의합니다. SQL Database는 테이블에서 기본 키, 외래 키, 인덱스 및 트리거를 지원합니다. 다음 샘플 코드는 관련 테이블 쌍과 비클러스터형 인덱스를 만듭니다. 이러한 명령은 쿼리 편집기 또는 sqlcmd 유틸리티에서 일괄 처리로 실행할 수 있습니다.

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

CREATE TABLE MyTable2
(
    AnotherColumn1 INT NOT NULL,
    AnotherColumn2 INT NOT NULL REFERENCES MyTable,
    AnotherColumn3 VARCHAR(50) NULL,
    PRIMARY KEY (AnotherColumn1, AnotherColumn2)
);

CREATE INDEX cci ON MyTable2(AnotherColumn3);

Azure Portal에서 쿼리 편집기에 액세스하려면 데이터베이스 페이지로 이동한 다음, 쿼리 편집기를 선택합니다. 자격 증명을 묻는 메시지가 표시됩니다. 인증 형식을 SQL Server 인증으로 설정하고, 데이터베이스를 만들 때 설정한 사용자 이름과 암호를 입력할 수 있습니다. 또는 Active Directory 암호 인증을 선택하고 Microsoft Entra ID에 권한 부여된 사용자의 자격 증명을 제공할 수 있습니다. Active Directory Single Sign-On을 사용하도록 설정한 경우 Azure ID를 사용하여 연결할 수 있습니다.

The SQL Database sign-in page in the Azure portal.

쿼리 창에 T-SQL 코드를 입력한 다음 실행을 선택하여 실행합니다. T-SQL 문이 쿼리인 경우에는 반환되는 행이 결과 창에 나타납니다. 메시지 창에는 반환된 행 수, 발생한 오류 등의 정보가 표시됩니다.

The query editor in the Azure portal with the various panes highlighted.

sqlcmd 유틸리티를 사용하려면 Cloud Shell로 이동하여 다음 명령을 실행합니다. <server>는 직접 만든 데이터베이스 서버의 이름으로 바꾸고, <database>는 해당 데이터베이스 이름으로 바꾸고, <user name>과 <password>는 해당 자격 증명으로 바꿉니다.

sqlcmd -S <server>.database.windows.net -d <database> -U <username> -P <password>

로그인 명령이 성공하면 1> 프롬프트가 표시됩니다. T- SQL 명령을 여러 줄에 입력하고 GO를 입력하여 실행할 수 있습니다.

bcp를 사용하여 데이터를 대량으로 가져오기

Microsoft는 SQL 데이터베이스에 데이터를 업로드하는 데 사용할 수 있는 몇 가지 도구를 제공합니다.

  • SSIS(SQL Server Integration Services)
  • SQL BULK INSERT 문
  • bcp(대량 복사 프로그램) 유틸리티

bcp 유틸리티는 편리하고, 데이터를 여러 테이블로 가져오도록 쉽게 스크립팅할 수 있기 때문에 자주 사용됩니다. bcp 유틸리티는 데이터베이스에서 데이터를 가져오고 내보내는 데 사용할 수 있는 명령줄 도구입니다. 데이터를 가져오려면 bcp에 다음 세 가지가 필요합니다.

  • 업로드할 원본 데이터입니다.
  • 대상 데이터베이스의 기존 테이블.
  • 데이터의 형식과 데이터를 대상 테이블의 열에 매핑하는 방법을 정의하는 형식 파일입니다.

bcp 유틸리티는 유연합니다. 거의 모든 구조화된 형식을 원본 데이터로 사용할 수 있습니다. 형식 파일은 데이터의 레이아웃과 데이터가 이진 또는 문자 기반인지 여부를 나타냅니다. 또한 각 항목의 유형과 길이, 데이터 구분 방법을 지정합니다. 서식 파일은 파일의 각 항목을 테이블의 열에 매핑하는 방법도 지정합니다. 이 파일의 내용을 올바르게 정의하는 것이 중요합니다. 올바르게 정의하지 않으면 데이터를 가져오지 못하거나 데이터를 잘못된 열로 읽어올 수 있습니다.

mydata.csv 파일에 다음 데이터가 있으며, 이 데이터를 앞에서 만든 MyTable 테이블로 가져오려 한다고 가정합니다.

Column1,Column2
99,some text
101,some more text
97,another bit of text
87,yet more text
33,a final bit of text

첫 번째 줄에는 테이블의 열과 다른 필드 이름이 포함되어 있습니다. 데이터는 쉼표로 구분되며 각 행은 개행 문자로 종료됩니다. 파일의 열 순서가 테이블과 다를 수 있다는 것에 유의합니다. 이 예제에서는 다음과 같이 테이블의 첫 번째 열이 숫자이고 두 번째 열이 문자열입니다.

CREATE TABLE MyTable
(
    MyColumn1 INT NOT NULL PRIMARY KEY,
    MyColumn2 VARCHAR(50) NOT NULL
);

bcp 명령을 사용하여 가져오기 서식 파일을 만들 수 있습니다. bcp 명령은 데이터베이스에 있는 대상 테이블의 스키마를 기준으로 서식 파일을 만들 수 있습니다. 그런 다음, 원본 파일의 데이터와 일치하도록 파일을 편집할 수 있습니다.

다음 명령을 실행하여 서식 파일을 만듭니다. 꺾쇠 괄호 안의 항목을 해당 데이터베이스, 서버, 사용자 이름 및 암호 값으로 바꿉니다.

bcp <database>.dbo.mytable format nul -c -f mytable.fmt -t, -S <server>.database.windows.net -U <username> -P <password>

bcp 유틸리티에는 유틸리티 기능을 제어하는 몇 가지 매개 변수가 있습니다. 다음을 지정할 수 있습니다.

  • 대상 테이블(<database>.<schema>.<table>)
  • 가져올 데이터 및 데이터 세부 정보(format nul -c -f mytable.fmt -t,)
  • 데이터베이스 연결 세부 정보(-S <server>.database.windows.net -U <username> -P <password>)

유틸리티의 전체 구문 및 명령줄 매개 변수는 도움말 문서를 참조하세요.

이 명령어는 다음과 같은 콘텐츠로 mytable.fmt 형식 파일을 생성합니다.

14.0
2
1       SQLCHAR             0       12      ","    1     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   2     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

첫 번째 줄에는 SQL Database의 내부 버전 번호가 표시됩니다. 두 번째 줄에는 원본 테이블의 열 수가 표시됩니다. 마지막 두 줄은 원본 파일의 데이터를 이러한 열에 매핑하는 방법을 나타냅니다.

두 라인 모두 테이블의 열 번호인 번호로 시작합니다. 두 번째 필드(SQLCHAR)는 이 서식 파일을 사용하여 데이터를 가져올 때, 원본 파일의 각 필드에 문자 데이터가 포함되도록 지정합니다. bcp 유틸리티는 이 데이터를 테이블의 해당 열에 대한 적절한 유형으로 변환하려고 시도합니다. 다음 필드(12와 50)는 데이터베이스의 각 열에 있는 데이터의 길이입니다. 이 필드를 변경하지 마세요! 다음 항목(“,” 및 “\n”)은 각각 원본 파일의 필드 종결자와 줄 바꿈 문자입니다. 다음 열은 원본 파일의 필드 번호입니다. 끝에서 두 번째 필드(MyColumn1 및 MyColumn2)는 데이터베이스의 열 이름입니다. 마지막 필드는 사용할 데이터 정렬이며, 데이터베이스의 문자 데이터에만 적용됩니다.

원본 파일의 필드는 데이터베이스의 열과 순서가 다릅니다. 따라서 서식 파일을 편집하여 필드 번호를 다음과 같이 변경해야 합니다.

14.0
2
1       SQLCHAR             0       12      ","    2     MyColumn1                                ""
2       SQLCHAR             0       50      "\n"   1     MyColumn2                                SQL_Latin1_General_CP1_CI_AS

원본 파일의 필드 2에 있는 데이터는 데이터베이스의 첫 번째 열에 매핑됩니다. 필드 1은 두 번째 열에 매핑됩니다.

이제 다음과 같이 bcp 명령을 사용하여 데이터를 가져올 수 있습니다.

bcp <database>.dbo.mytable in mydata.csv -f mytable.fmt -S <server>.database.windows.net -U <username> -P <password> -F 2

in 플래그는 bcp를 사용하여 데이터를 가져오도록 지정합니다. out을 사용하면 데이터베이스의 데이터를 파일로 전송할 수 있습니다. -F 2 플래그는 가져오기 작업이 원본 파일의 두 번째 줄에서 시작되도록 지정합니다. 첫 번째 줄에는 데이터가 아닌 헤더가 포함되어 있습니다.

명령이 성공적으로 실행되고 다음 예제와 유사한 메시지가 반환됩니다.

Starting copy...

5 rows copied.
Network packet size (bytes): 4096
Clock Time (ms.) Total     : 46     Average : (108.7 rows per sec.)

출력에서 중요한 줄은 "5개 행이 복사되었습니다."입니다. 가져온 데이터가 포함된 원본 파일의 라인 수입니다. 이 개수가 다르거나 0이면 서식 파일이 잘못되었을 수 있습니다.

데이터 쿼리

데이터를 쿼리하여 가져오기가 성공했는지 확인합니다. Azure Portal에서 쿼리 편집기를 사용할 수 있습니다. 또는 sqlcmd 유틸리티를 사용하여 명령줄에서 데이터베이스에 연결할 수 있습니다. 두 경우 모두, SELECT 문을 다음과 같이 실행합니다.

SELECT *
FROM MyTable;

다음과 같은 결과가 나타납니다.

The query editor in the Azure portal shows the results of a query.