Lambda 와 Aurora 연동

https://medium.com/@justanotherspyy/how-to-connect-your-lambda-function-securely-to-your-private-rds-instances-in-your-vpc-29789220a33

How to connect your Lambda function securely to your private RDS instances in your VPC!

 

https://docs.aws.amazon.com/ko_kr/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.Integrating.LoadFromS3.html

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

 

https://docs.aws.amazon.com/ko_kr/lambda/latest/dg/vpc-rds.html

자습서: Amazon VPC에서 Amazon RDS에 액세스하도록 Lambda 함수 구성 - AWS Lambda

 

* Load Data Files into Aurora RDS from S3 Bucket

SET @file_name = 'file_name'
SET @s3_path = CONCAT('s3://bucket-name/folder-name/', @file_name);
 
SET @q = CONCAT('
SELECT *
FROM table
INTO OUTFILE S3 ''', @s3_path, ''' ;
CHARACTER SET utf8
    FIELDS TERMINATED BY '',''
    OPTIONALLY ENCLOSED BY ''"''
    LINES TERMINATED BY ''\\r\\n''
    OVERWRITE ON;');
 
PREPARE stmt FROM @q;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

 

* load data import DB

query = """ LOAD DATA FROM S3 PREFIX '######/'
INTO TABLE TBL_SA_DATA
CHARACTER SET utf8
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\\n'
IGNORE 1 LINES
(CAMPAIGN_TYPE, CAMPAIGN_NAME, REG_DT, URL, MEDIA_NAME)
"""

 

 

CREATE FUNCTION `split`( str VARCHAR(500), del VARCHAR(2), idx INT ) RETURNS varchar(500) 
BEGIN
    /**
        문자열에서 특정값으로 배열을 잘라주는 함수 (없으면 빈문자열을 리턴함)
        str: 해당 문자열
        del: 구분자 
        idx: 구분자로 구분한 위치 (1부터 시작함)        
    **/
    RETURN SUBSTR(SUBSTRING_INDEX(str, del, idx),LENGTH(SUBSTRING_INDEX(str, del, idx-1))+IF(idx > 1, 2, 1));
END