Amazon S3 버킷의 텍스트 파일에서 Amazon Aurora MySQL DB 클러스터로 데이터 로드

LOAD DATA FROM S3 또는 LOAD XML FROM S3 문을 사용하여 Amazon S3 버킷에 저장된 파일에서 데이터를 로드할 수 있습니다. Aurora MySQL에서는 먼저 파일을 로컬 디스크에 저장한 다음 데이터베이스로 가져옵니다. 데이터베이스로 가져오기가 완료되면 로컬 파일은 삭제됩니다.

Aurora에 Amazon S3에 대한 액세스 권한 부여

Amazon S3 버킷에서 데이터를 로드하기 전에 Aurora MySQL DB 클러스터에 Amazon S3에 액세스할 권한을 부여해야 합니다.

Amazon Aurora MySQL에서 데이터 로드 권한 부여

LOAD DATA FROM S3 또는 LOAD XML FROM S3 문을 실행하는 데이터베이스 사용자에게는 둘 중 한 명령문을 실행할 수 있도록 특별한 역할이나 권한이 부여되어야 합니다. Aurora MySQL 버전 3에서는 AWS_LOAD_S3_ACCESS 역할을 부여합니다. Aurora MySQL 버전 2에서는 LOAD FROM S3 권한을 부여합니다. DB 클러스터의 관리자 사용자에게는 기본적으로 적절한 역할이나 권한이 부여됩니다. 다음 명령문 중 하나를 사용하여 다른 사용자에게 권한을 부여할 수 있습니다.

Aurora MySQL 버전 3에 대해 다음 명령문을 사용합니다.

GRANT AWS_LOAD_S3_ACCESS TO 'user'@'domain-or-ip-address'

Aurora MySQL 버전 2에 대해 다음 문을 사용합니다.

GRANT LOAD FROM S3 ON *.* TO 'user'@'domain-or-ip-address'

AWS_LOAD_S3_ACCESS 역할 및 LOAD FROM S3 권한은 Amazon Aurora에만 적용되며, 외부 MySQL 데이터베이스 또는 RDS for MySQL DB 인스턴스에서는 사용할 수 없습니다. 복제 소스인 Aurora DB 클러스터와 복제 클라이언트인 MySQL 데이터베이스 간에 복제를 설정한 경우, 역할 또는 권한에 대한 GRANT 문으로 인해 오류가 생겨 복제가 중단됩니다. 오류를 건너뛰고 복제를 계속 진행하셔도 됩니다. RDS for MySQL 인스턴스에서 오류를 건너뛰려면 mysql_rds_skip_repl_error 프로시저를 사용하세요. 외부 MySQL 데이터베이스에서 오류를 건너뛰려면 slave_skip_errors 시스템 변수(Aurora MySQL 버전 2) 또는 replica_skip_errors 시스템 변수(Aurora MySQL 버전 3)를 사용하세요.

Amazon S3 버킷으로의 경로(URI) 지정

Amazon S3 버킷에 저장된 파일로의 경로(URI)를 지정하는 구문은 다음과 같습니다.

s3-region://amzn-s3-demo-bucket/file-name-or-prefix

경로에는 다음 값이 포함됩니다.

S3에서 데이터 로드

LOAD DATA FROM S3 문을 사용하여 쉼표로 구분되는 텍스트 데이터와 같이 MySQL LOAD DATA INFILE 문에서 지원하는 모든 텍스트 파일 형식의 데이터를 로드할 수 있습니다. 압축 파일은 지원되지 않습니다.

구문

LOAD DATA [FROM] S3 [FILE | PREFIX | MANIFEST] 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

파라미터

LOAD DATA FROM S3 문은 다음과 같은 필수 파라미터와 선택 사항인 파라미터를 사용합니다. MySQL 설명서의 LOAD DATA 문에서 이러한 파라미터에 대한 자세한 내용을 볼 수 있습니다.

Amazon S3 버킷에서 데이터를 로드 중인 경우 LOAD DATA FROM S3 문의 LOCAL 키워드를 사용할 수 없습니다.

매니페스트 파일을 이용해 로드할 데이터 파일 지정

LOAD DATA FROM S3 문과 MANIFEST 키워드를 사용하여 DB 클러스터에 있는 테이블에 로드할 텍스트 파일 목록을 표시하는 JSON 형식의 메니페스트 파일을 지정할 수 있습니다.

다음 JSON 스키마는 매니페스트 파일의 형식 및 내용을 설명합니다.

{
    "$schema": "http://json-schema.org/draft-04/schema#",
    "additionalProperties": false,
    "definitions": {},
    "id": "Aurora_LoadFromS3_Manifest",
    "properties": {
        "entries": {
            "additionalItems": false,
            "id": "/properties/entries",
            "items": {
                "additionalProperties": false,
                "id": "/properties/entries/items",
                "properties": {
                    "mandatory": {
                        "default": "false",
                        "id": "/properties/entries/items/properties/mandatory",
                        "type": "boolean"
                    },
                    "url": {
                        "id": "/properties/entries/items/properties/url",
                        "maxLength": 1024,
                        "minLength": 1,
                        "type": "string"
                    }
                },
                "required": [
                    "url"
                ],
                "type": "object"
            },
            "type": "array",
            "uniqueItems": true
        }
    },
    "required": [
        "entries"
    ],
    "type": "object"
}

메니페스트의 각 url에는 접두사뿐 아니라 파일의 버킷 이름과 전체 객체 경로를 포함한 URL을 지정해야 합니다. 메니페스트를 이용하면 다른 버킷, 다른 리전이나 접두사가 다른 파일에서 파일을 불러올 수 있습니다. URL에서 리전이 지정되지 않았다면, 대상 Aurora DB 클러스터의 리전을 사용합니다. 다음은 다른 버킷에서 파일 4개를 로드하는 메니페스트 파일 예제입니다.

{
  "entries": [
    {
      "url":"s3://aurora-bucket/2013-10-04-customerdata", 
      "mandatory":true
    },
    {
      "url":"s3-us-west-2://aurora-bucket-usw2/2013-10-05-customerdata",
      "mandatory":true
    },
    {
      "url":"s3://aurora-bucket/2013-10-04-customerdata", 
      "mandatory":false
    },
    {
      "url":"s3://aurora-bucket/2013-10-05-customerdata"
    }
  ]
}

선택 사항인 mandatory 플래그는 파일이 없을 때 LOAD DATA FROM S3의 오류 반환 여부를 결정합니다. mandatory 플래그의 기본값은 false입니다. mandatory 설정 여부와 상관없이, 파일이 발견되지 않으면 LOAD DATA FROM S3은 종료됩니다.

메니페스트 파일은 확장자를 제한하지 않습니다. 다음은 앞 예제에서 사용한 LOAD DATA FROM S3 매니페스트를 이용해 customer.manifest 문을 실행하는 예제입니다.

LOAD DATA FROM S3 MANIFEST 's3-us-west-2://aurora-bucket/customer.manifest'
    INTO TABLE CUSTOMER
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, EMAIL);

문이 완료되면 성공적으로 로드한 각 파일이 aurora_s3_load_history에 기록됩니다.

aurora_s3_load_history 테이블을 이용해 로드 파일 확인

LOAD DATA FROM S3 문이 완료될 때마다 aurora_s3_load_history 스키마에 있는 mysql 테이블에 각 로드 파일 항목이 업데이트됩니다.

LOAD DATA FROM S3 문을 실행하면, aurora_s3_load_history 테이블을 쿼리해 로드한 파일을 확인할 수 있습니다. 이 문을 한 번 반복하여 로드한 파일을 확인하려면 WHERE 절을 사용하여 Amazon S3 URI의 기록에서 문에 사용된 매니페스트 파일을 필터링합니다. 이전에 같은 메니페스트 파일을 사용한 적이 있다면, timestamp 필드로 결과를 필터링하십시오.

select * from mysql.aurora_s3_load_history where load_prefix = 'S3_URI';

다음 표는 aurora_s3_load_history 테이블 필드를 설명합니다.

예시

다음 문은 Aurora DB 클러스터와 동일한 리전에 있는 Amazon S3 버킷에서 데이터를 로드합니다. 이 문은 amzn-s3-demo-bucket Amazon S3 버킷에 있는 파일 customerdata.txt에서 쉼표로 구분된 데이터를 읽은 다음 store-schema.customer-table 표로 해당 데이터를 로드합니다.

LOAD DATA FROM S3 's3://amzn-s3-demo-bucket/customerdata.csv' 
    INTO TABLE store-schema.customer-table
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, ADDRESS, EMAIL, PHONE);

다음 문은 Aurora DB 클러스터와 다른 리전에 있는 Amazon S3 버킷에서 데이터를 로드합니다. 이 문은 us-west-2 리전에 있는 amzn-s3-demo-bucket Amazon S3 버킷의 employee-data 객체 접두사와 일치하는 모든 파일에서 쉼표로 구분된 데이터를 읽은 다음 employees 표로 해당 데이터를 로드합니다.

LOAD DATA FROM S3 PREFIX 's3-us-west-2://amzn-s3-demo-bucket/employee_data'
    INTO TABLE employees
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (ID, FIRSTNAME, LASTNAME, EMAIL, SALARY);              

다음 문은 q1_sales.json이라는 JSON 메니페스트 파일에서 지정한 파일의 데이터를 sales 테이블로 로드합니다.

LOAD DATA FROM S3 MANIFEST 's3-us-west-2://amzn-s3-demo-bucket1/q1_sales.json'
    INTO TABLE sales
    FIELDS TERMINATED BY ','
    LINES TERMINATED BY '\n'
    (MONTH, STORE, GROSS, NET);

S3에서 XML 로드

LOAD XML FROM S3 문을 사용하여 Amazon S3 버킷에 저장된 XML 파일에서 다음과 같은 세 가지 XML 형식 중 하나로 데이터를 로드할 수 있습니다.

구문

LOAD XML FROM S3 'S3-URI'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [PARTITION (partition_name,...)]
    [CHARACTER SET charset_name]
    [ROWS IDENTIFIED BY '<element-name>']
    [IGNORE number {LINES | ROWS}]
    [(field_name_or_user_var,...)]
    [SET col_name = expr,...]

파라미터

LOAD XML FROM S3 문은 다음과 같은 필수 파라미터와 선택 사항인 파라미터를 사용합니다. MySQL 설명서의 LOAD XML 문에서 이러한 파라미터에 대한 자세한 내용을 볼 수 있습니다.