역할이나 그룹별로 스키마 접근 권한을 주는 경우가 많다. (RBAC : Role Based Access Control을 많이 쓴다.)
개인정보에 관련된 테이블의 경우 별도 스키마를 설정한다.
코드 예시
/*두번쨰 명령이 메인 명령이다.*/
/*그룹에 대해서 스키마 권한을 주고, 스키마 내의 테이블의 권한을 부여한다.*/
GRANT USAGE ON SCHEMA analytics TO GROUP analytics_authors
GRANT SELECT ON ALL TABLES IN SCHEMA analytics TO GROUP analytics_users;
GRANT ALL ON ALL TABLES IN SCHEMA adhoc TO GROUP analytics_users
GRANT ALL ON SCHEMA adhoc to GROUP analytics_users;
컬럼 레벨 보안 : 테이블 내의 특정 컬럼을 사용자나 특정 그룹에만 접근하도록 만드는 것.
보안이 필요한 컬럼을 별도 테이블로 생성하는 방법을 사용한다
가장 좋은 것은 보안이 필요한 정보를 데이터 시스템으로 로딩하지 않는 것이다.
레코드(Row) 레벨 보안 : 특정 레코드들을 사용자나 특정한 그룹에만 접근하도록 만드는 것.
특정 사용자의 테이블을 대상으로하는 SELECT, UPDATE, DELETE 작업에 추가 조건을 다는 형태로 동작한다.
생성 : CREATE RLS POLICY 명령을 사용한다.
부여 : ATTACH RLS POLICY
백업과 테이블 복구
고정 비용 Redshift
Snapshot : 마지막 백업으로부터 바뀐 것들만 저장하는 방식 → 과거 시점의 내용으로 클러스터 생성도 가능하다.
자동 백업 : 최대 35일까지의 변경을 백업하도록 할 수 있다.
다른 지역의 S3에 하려면 Cros-regional snapshot copy를 설정해야한다. → 재난 상황같은 경우에 유용하다.
Redshift 클러스터의 Maintenance → Backup details → Edit을 통해 보관 기간을 설정한다.
매뉴얼 백업 : 원하는 경우에 만드는 백업이다. 명시적으로 삭제 전까지 유지된다.
백업에서 테이블 복구
Restore table 선택 → 복구 대상이 있는 Snapshot선택
원본 테이블을 선택하고 복구 할 타겟 테이블을 선택한다.
가변 비용 Redshift Serverless
Snapshot 이전에 Recovery Points가 존재한다. (24시간만 유지된다.)
자동으로 Snapshot이 생기지 않기 때문에 직접 생성해주어야한다.
Recovery points를 통해서 snapshot을 생성하는 것과 달리 Snapsots에서 직접 생성하게되면 메뉴얼 백업이 생성된다.
Redshift Spectrum
S3에 매우 큰 데이터가 있어 Redshift에 로딩하기 버거운 경우에 사용한다.
Fact, Dimension 테이블
Fact : 분석의 초점이되는 양적 정보를 포함하는 중앙 테이블
보통 비즈니스 결정에 사용되는 테이블
외래 키를 통해서 여러 Dimension 테이블에 연결된다.
Fact 테이블의 크기가 DImension 테이블보다 큰 경우가 많다.
예) Order 테이블, 상품 주문 정보가 들어간 테이블
DImension : Fact 테이블에 대한 상세 정보를 제공하는 테이블
사용자가 다양한 방식으로 Fact 테이블의 데이터를 조각내어 분석하게 해준다.
Dimension 테이블은 PK를 갖는다.
예) User 테이블, Order테이블에서 주문을 한 사용자에 대한 정보
Spectrum use case
S3에 대용량 Fact 테이블이 존재
Fact 테이블을 Redshift로 적재하지 않고 사용하는 경우
별도 설정 하는 것이 아니라 Redshift의 확장기능이다.
외부 테이블
DB엔진이 외부에 저장된 데이터를 내부 테이블처럼 사용하는 방법
SQL을 사용해서 DB에 외부 테이블 생성이 가능하다.
데이터 러치 후 결과를 DB에 적재하는데 사용한다.
보안적인 부분에서 고려가 필요하다.
S3 Fact 데이터를 외부 테이블로 정의해야한다.
Redshift ML
AWS SageMaker
ML 모델 개발을 처음부터 끝까지 지원하는 서비스 (MLOps 프레임워크)
4가지의 기능을 제공한다.
트레이닝 셋 준비
모델 훈련
모델 검증
모델 배포와 관리
다양한 머신러닝 프레임워크를 지원한다.
다양한 개발방식을 지원한다.
AutoPilot : AutoML 기능 → 데이터 셋을 제공하면 자동으로 모델을 제작해준다.
사용 권한 지정 → Redshift cluster
Role 설정
Role을 사용할 수 있는 서비스로 Redshift를 지정
Redshift 권한으로 지정 (SagemakerFullAccess)
Principal에 있어서 sagemaker와 redshift가 모두 access 가능하게 변경해주어야 한다.
ARN을 통해 활용
Redshift 중지/제거하기
가변 비용에는 제거만 있다.
유지보수
고정 비용 옵션에서만 있음.
Maintenance window라고 부른다.
테이블 청소와 최적화 - VACUUM
테이블 데이터 정렬 → 데이터를 정렬해서 남아있는 행을 모아준다.
디스크 공간 해제 : 필요하지 않은 행들을 제거한다.
클러스터 중지&제거
중지 : 당분간 필요 없는 경우에 사용한다. → 스토리지 비용만 부담하게된다.
제거 : 아에 필요없는 경우에 사용한다. Snapshot으로 s3에 저장할 것인지를 물어본다.
벌크 업데이트 지원 : S3와 연계해서 Copy 커맨드로 Redshfit에 복사가 가능하다.
Datashare 지원
PK uniqueness를 보장하지 않는다.
SQL 기반이며 PostgreSQL 8.x을 호환한다.
두 대 이상의 노드로 구성되면 복잡해진다.
분산 저장되어야 한다.
노드에 데이터가 적절하게 분배되도록 사용자가 지정해주어야한다.
레코드 분배와 저장 방식
두 대 이상의 노드로 구성되면 테이블 최적화가 중요하다.
Distkey : 레코드가 어떤 컬럼을 기준으로 배포되는지 나타낸다. → DIststyle이 key → 컬럼 선택이 중요하다. (Skew : 한쪽 쏠림 이 발생가능하다.)
Diststyle : 레코드 분배 방식을 결정 (all, even, key)
Sortkey : 레코드가 한 노드 안에서 어떤 컬럼을 기준으로 정렬되는지 나타낸다. (타임스탬프가 되는 경우가 많음)
Redshift의 벌크 업데이트 방식
데이터 추출
파이프라인을 통해 S3에 업데이트
S3 → Redshift로 복사
참고) VARCHAR의 경우 한중일 언어는 1CHAR가 3Byte이기 때문에 주의해야 한다.
고급 데이터 타입
GEOMETRY
HLLSKETCH
SUPER
JSON은 VARCHAR로 받아서 Parsing을 해야한다.
Serverless 설치
주의) 3달 or $300 비용 초과인 경우에 Free Trial이 만료됨!
참고) 비용 측면에서 free trial은 서울이 비싸기 때문에 오레곤으로 시작했다.
Colab 연결
엔드 포인트
Admin 계정 생성
연결을 위해 endpoint, username, password 정보가 필요하다.
Public access 설정 - 작업 그룹 구성에 진입 - 네트워크 및 보안의 퍼블릭 액세스를 켜야한다. - VPC 보안 그룹의 세팅 변경이 필요하다. - 인바운드 규칙 변경 - 규칙 추가를 진행 → 포트 넘버 5439 선택, 소스의 경우 모든 접근을 설정하기 위해 0.0.0.0으로 설정한다.
초기설정
스키마 : 폴더 컨셉의 구조 생성
목적에 맞는 스키마를 생성한다.
생성 : CREATE SCHEMA <schema_name>
리스트하기 : SELECT * FROM pg_namespace;
public은 default 스키마다.
그룹
한 사용자는 다수의 그룹에 속할 수 있다.
그룹은 계승이 안되기 때문에 관리가 필요하다.
그룹을 활용해 접근권한을 설정하는 방식을 많이 사용한다.
그룹 생성 : CREATE GROUP
그룹에 사용자 추가 : ALTER GROUP group_name ADD USER username
그룹 리스트하기 : SELECT * FROM pg_group;
접근 권한은 grant를 사용해서 부여한다.
역할 설정
그룹과 다르게 계승 구조가 가능하다.
사용자에게 부여될 수도 있고, 역할에 부여도 가능하다.
한 사용자가 다수의 역할에 소속가능하다.
역할 리스트 : SELECT * FROM SVV_ROLES;
역할 생성 : CREATE ROLE role_name
부여 : GRANT ROLE staff TO ROLE role_name 또는 TO username
유저 생성
사용자 리스트하기 : SELECT * FROM pg_user;
사용자 생성 : CREATE USER username password ‘…’;
Copy 명령으로 테이블에 레코드 적재하기
raw_data 테이블 생성하기 : ETL을 통해 데이터 소스를 복사하는 형태로 이루어진다.
S3에서 Redshift에 접근하기 위해서는 접근 권한 생성이 필요
IAM → 역할 생성
AWS service를 선택하고 하단의 Redshift를 선택한다.
다음으로 Redshift가 갖게 될 권한을 선택해야한다. → 이번에는 AmazonS3FullAccess를 선택하여 진행한다.
사용할 역할 이름을 입력하고 최종 생성한다.
Redshift → 네임스페이스 구성에 접근
보안 및 암호화에 IAM 역할관리를 선택
IAM 역할 연결을 선택하고, 앞서 만든 IAM을 지정한다.
COPY 명령을 사용하여 raw_data 스키마 아래의 3개 테이블에 레코드를 적재합니다.
SELECT * FROM stl_load_errors ORDER BY starttime DESC;
analytics 테스트 테이블 생성하기
raw_data에 있는 테이블들을 조인해서 새로 만든다. or CTAS로 가능하다.
CREATE TABLE analytics.mau_summary AS
SELECT
TO_CHAR(A.ts,'YYYY-MM') AS month,
COUNT(DISTINCT B.userid) AS mau
FROM raw_data.session_timestamp A
JOIN raw_data.usersession_channel B ON A.sessionid = B.sessionid
GROUP BY 1
ORDER BY 1 DESC;
