DML-
파이프라인- 어느 데이터를 다른 위치로 보내는 것
mongo_smaple : yes24에서 천재교육 및 경쟁 회사 정보
데이터 엔지니어? (Data Engineer)
1. 데이터 엔지니어가 주로 하는 일은?
-운영을 하기 위해 환경을 만드는 등 데이터를 밀어넣어주기 위해서, 다른 위치에서 사용할 수 있도록 파이프라인을 만들어 주는 것
1-1. 어느 부분까지 관여해야 할까?
- 분석결과 리포트를 만들었을때 (월별 정회원 및 유입 회원의 수) 보고서가 생성되기 직전 보고서가 생성하기 위해 들어간 기존 데이터의 위치에 새로운 데이터를 넣어줘야함.
- 무슨 스킬이 필요한가?
- 파이썬 정도의 언어(계발을 할 줄 알아야함. ex)class를 만들어서 연결하는 등)
- sql, numpy, pandas, 다스크, 스파크
- 시스템 환경 자동화
- 순서 워크 플로우
- 오케스트레이션 (데이터 줄이고 늘리기)
- 아파치 에어 플로우
- 실행한 작업이 잘 결과가 나왔는지 진행중인지 에러가 났는지 모니터링
aws
검색에 athena-좌측 쿼리편집기
데이터의 종류
-
Table Data (엑셀 형식, 관계형 데이터 베이스)
- 눈으로 볼때에는 어떻게 생겼나?
- 컴퓨터가 처리하는 데이터의 모습은?
-
Image Data (픽셀값들이 3차원 행렬의 리스트 형태로 구성)
- 눈으로 볼때에는 어떻게 생겼나?
- 컴퓨터가 처리하는 데이터의 모습은?
-
Natural Language Data (텍스트 데이터, dtm)
- 눈으로 볼때에는 어떻게 생겼나?
- 컴퓨터가 처리하는 데이터의 모습은?
- 그림으로 배우는 서버구조 Server
- 그림으로 배우는 네트워크 Net Work 원리
#들어온 텍스트 전체를 어떠한 기준으로 끊어냄 (지금은 띄어쓰기로 컬롬으로 묶임)
| col 1 | col 2 | col 3 | col 4 | col 5 | col 6 | col 7 | col 8 |
|---|---|---|---|---|---|---|---|
| 그림으로 | 배우는 | 서버구조 | server | ||||
| 그림으로 | 배우는 | 네트워크 | Net | Work | 원리 |
{col 1 : 그림으로, col 2 : 배우는, … col 8 : 원리}
| col 1 | col 2 | col 3 | col 4 | col 5 | col 6 | col 7 | col 8 |
|---|---|---|---|---|---|---|---|
| 1 | 1 | 1 | 1 | 0 | 0 | 0 | 0 |
| 1 | 1 | 0 | 0 | 1 | 1 | 1 | 1 |
#각 컬럼에서 나온 수, document t metrix
#최빈 단어는 가중치를 두어
#앞뒤 단어를 예측 bert
엑셀: 피벗테이블= SQL: GROUP BY
논리적 구조와 기본 구조는 다름
왜? 테이블 크기를 줄여서 얻어오기 위함임.
GROUP BY 데이터 프레임 형상이 완벽하게 만들엊인 이후 보려고 하는 것을 추리는 것
ORDER BY 추려진 상태에서 정렬 (WHERE에서 쳐냈으면 안 들어감)
테이블의 형태
반드시 하나 이상의 컬럼으로 이루어진 행과 열
컬럼의 이름과 데이터 타입 형식 (테이블 생성할때 선언)
컬럼의 이름은 하나의 테이블 내에서 유니크 함
테이블 이름도 하나의 데이터 베이스 내에서 유니크 함
데이터 베이스 이름도 유니크 해야 함
어떤 서버에 접근해서 어떤 데이터 베이스 테이블 컬럼에 접근을 할 수 있는지
테이블 1번과 2번의 컬럼명은 같아도 됨. 테이블로 나누어 지니까
로우는 하나의 관계형 데이터를 의미한다
열(column)
컬럼의 이름과 데이터의 타입은 테이블을 만들 때 미리 정해진다.
컬럼의 이름은 동일한 테이블 내에서 중복될 수 없다.
테이블은 반드시 1개 이상의 컬럼을 가진다.
행(row)
하나의 로우는 하나의 관계된 데이터를 의미한다. (PK가 userid 조건에서, 1개의 row는 1명의 회원의 데이터를 의미)
로우는 동일한 테이블 내에서 동일한 구조를 가진다. (컬럼의 데이터 타입과 구조를 이미 선언했으므로)
데이터의 삽입이 일어날 때, 로우 단위로 삽입이 일어난다. (insert 할때 row 단위, 분산해서 할 경우 pk 기준)
Amazon Athena
표준 SQL을 사용하여 Amazon S3 에 저장된 데이터를 쉽게 분석할 수 있는 대화형 쿼리 서비스
표준SQL(여기서는 프레스토 데이터 베이스 엔진)
저장소 : AWMS
교육 시간 내 SQL 작성 규칙
문장이 끝났다. 세미콜론
문장의 시작부터 끝까지 선택한 후 run버튼
SQL 문법/함수/기능 = 대문자 작성
테이블 명 = 소문자 작성
컬럼 명 = 소문자 작성
값 = 대/소문자 사용 (실제 값)
DDL - 데이터 구조 정의
CREATE ALTER DROP RENAME
DML - 데이터의 조회/입력/수정/삭제 등 데이터를 조작
SELECT INSERT UPDATE DELETE (UPDATE 불가한 데이터베이스 하둡 등 통체로 해야함)
트랜잭션- 중간에 아이디가 바뀌었을때 잘못되면 안되므로
SELECT 시놉시스
SELECT 쿼리의 작성 순서
SELECT
FROM
WHERE
GROUP BY
(HAVING) # 얜 GROUP BY 다음에만 사용
ORDER BY
SELECT 쿼리의 실행 순서
FROM #엑셀파일 연다
WHERE # 내가 찾는 애들
GROUP BY # 그룹핑해서 데이터 형상을 바꾼다
(HAVING) # count에서 1,2,3 있을때 1을 잘라낼 때
SELECT # 데이터 프레임의 형상에서 선택해서
ORDER BY # 어센딩 디센딩 결정
SELECT
- 반드시 컬럼을 선택하는 것이 아니고 데이터를 선택하는 것
컬럼을 선택한다고 하면 안됨
아래와 같이 컬럼단위를 통체로도 선언 가능 집어 넣은 셈
문자의 경우 작은 따움표
FROM
- 테이블을 선택한다. # 테이블 이후 컬럼값은 유니크함
WHERE
- 조건을 지정한다.
- WHERE 이후는 대부분이 연산
- 연산 결과가 TURE은 조건만을 출력함.
LIKE- 이러한 문자열이 포함되어 있으면 선택
NULL 연산은 NULL로만 가능 NULL인지 아닌지
SELECT *
FROM "text_biz_dm"."learning_analytics_user"
WHERE 1 = 1
AND yyyy = '2022'
AND mm = '08'
AND gender != 'X'
AND grade_codename IN ('키즈', '초1', '초2', '초3', '초4', '초5', '초6')
AND memberstatus_change LIKE '-%‘ ＃ 신규회원
AND postalcode IS NOT NULL
;type을 맞춰야함.
아래의 경우 에러 timestamp 형이지만 실제 type이 맞지 않기에.
SELECT *
FROM "text_biz_dm"."learning_analytics_user"
WHERE credate BETWEEN '2022-08-01' AND '2022-08-02';이렇게 적어야함.
SELECT *
FROM "text_biz_dw"."e_test"
WHERE
credate BETWEEN CAST('2022-08-01' AS TIMESTAMP)
AND CAST('2022-08-02' AS TIMESTAMP)
;#공란을 null로 처리 하겠다.
SELECT *
FROM "text_biz_dw"."e_learning_time_proc"
WHERE 1 = 1
AND yyyy = '2022'
AND mm = '08'
AND (mcode IS NULL OR LENGTH(TRIM(mcode)) < 1)
; # null 혹은 이 데이터의 길이가 1보다 작은지 연산
# trim: 좌우의 스페이스 한칸씩 지움
-- LENGTH(TRIM(column)) < 1의 원리
SELECT LENGTH(' '), LENGTH(TRIM(' '));
-- LENGTH(TRIM(mcode)) < 1 과 비슷한 표현
mcode NOT LIKE '_%'SQL을 활용한 데이터분석에서 사용되는 함수 (그룹 함수)
- GROUP BY를 쓸 때 많이 사용하는 함수
GROUP BY
- 컬럼에서 동일한 값을 가지는 로우를 그룹화 한다.
- 엑셀의 피벗테이블과 매우 유사한 기능을 수행.
- GROUP BY가 사용된 쿼리의 SELECT 선언부에는 GROUP BY에 사용된 컬럼과 그룹 함수만 선언 가능! (기타 컬럼이 선언될 경우 에러 발생)
- GROUP BY 이후 HAVING을 활용하여 GROUP BY 출력 결과에 대한 조건을 지정할 수 있다.
- 데이터는 같지만 형상이 달라짐
#컬럼 안에 값들이 유니크하다고 가정하고 각각의 row들을 그룹화
SELECT
subject_name, grade
, COUNT(study_count) AS "차시학습 개수"
, SUM(study_count) AS "학습진행 횟수"
, SUM(study_completed_count) AS "학습완료 개수"
, SUM(study_notcompleted_count) AS "학습미완료 개수"
, ROUND(SUM(CAST(study_completed_count AS DOUBLE)) / SUM(CAST(study_count AS DOUBLE)) * 100, 2) AS "학습완료율"
FROM "text_biz_dm"."learning_analytics_content"
WHERE 1 = 1
AND CONCAT(yyyy, mm) = '202208'
AND subject_name = '수학'
AND grade BETWEEN 1 AND 6
GROUP BY subject_name, grade
HAVING ROUND(SUM(CAST(study_completed_count AS DOUBLE)) / SUM(CAST(study_count AS DOUBLE)) * 100, 2) < 95;
# having을 이용해서 group by에서 쓴 것을 한번 더 거르기
ORDER BY
- order by에서 선언할 수 있는 컬럼은 group by와 select에서 선언 된 것만 가능. group by에 선언한 컬럼만 남아있는 것
SELECT
subject_name, grade
, COUNT(study_count) AS "차시학습 개수"
, SUM(study_count) AS "학습진행 횟수"
, SUM(study_completed_count) AS "학습완료 개수"
, SUM(study_notcompleted_count) AS "학습미완료 개수"
, ROUND(SUM(CAST(study_completed_count AS DOUBLE)) / SUM(CAST(study_count AS DOUBLE)) * 100, 2) AS "학습완료율"
FROM "text_biz_dm"."learning_analytics_content"
WHERE 1 = 1
AND CONCAT(yyyy, mm) = '202208'
AND subject_name = '수학'
AND grade BETWEEN 1 AND 6
GROUP BY subject_name, grade
ORDER BY grade DESC
;기타 중요한 표현들
AS : 데이터에 별명을 지정
LIMIT : 출력할 데이터의 개수를 지정
SELECT a.subject_name From "text_biz_dm"."learning_analytics_content" AS a Limit 10;DISTINCT : 중복 제거하기
#컬럼뿐만 아니라 데이터프레임에도 적용
IF : 조건 만들기
CASE : 다수의 조건 만들기
#파이썬에서는 elif써서 다수의 조건문 가능 그러나 sql에서는 case 사용
SELECT
gender
, CASE
WHEN gender = 'M' THEN 'male' --when : if 조건에 들어가는 연산
WHEN gender = 'F' THEN 'female'
ELSE 'no_data'
END AS "성별"
FROM "text_biz_dm"."learning_analytics_user"
LIMIT 10
;
SQL에서 문자열 데이터와 함께 사용되는 함수
SQL에서 수치형 데이터와 함께 사용되는 함수
SQL에서 날짜형 데이터와 함께 사용되는 함수
날짜형 데이터의 구성
SELECT NOW() AS "현재"
, YEAR(NOW()) AS "연"
, QUARTER(NOW()) AS "분기"
, MONTH(NOW()) AS "월"
, WEEK(NOW()) AS "주"
, DAY(NOW()) AS "일"
, HOUR(NOW()) AS "시"
, MINUTE(NOW()) AS "분"
, SECOND(NOW()) AS "초"
, MILLISECOND(NOW()) AS "밀리초"
;
SELECT NOW()
, DAY_OF_YEAR(NOW()) AS "연 중 몇번째 일"
, DAY_OF_WEEK(NOW()) AS "주 중 몇번째 일"
;날짜형 데이터의 버림
SELECT NOW() AS "현재"
, DATE_TRUNC('year', NOW())
, DATE_TRUNC('quarter', NOW())
, DATE_TRUNC('month', NOW())
, DATE_TRUNC('week', NOW())
, DATE_TRUNC('day', NOW())
, DATE_TRUNC('hour', NOW())
, DATE_TRUNC('minute', NOW())
, DATE_TRUNC('second', NOW())
, DATE_TRUNC('millisecond', NOW())
;날짜형 데이터의 연산
SELECT NOW() AS "현재“
, DATE_ADD('year', 1, NOW())
, DATE_ADD('quarter', 1, NOW())
, DATE_ADD('month', 1, NOW())
, DATE_ADD('week', 1, NOW())
, DATE_ADD('day', 1, NOW())
, DATE_ADD('hour', 1, NOW())
, DATE_ADD('minute', 1, NOW())
, DATE_ADD('second', 1, NOW())
, DATE_ADD('millisecond', 1, NOW())
;
두 날짜형 데이터의 간격
SELECT NOW() AS "현재"
, DATE_DIFF('year', DATE_ADD('year', 1, NOW()), NOW())
, DATE_DIFF('quarter', DATE_ADD('quarter', 1, NOW()), NOW())
, DATE_DIFF('month', DATE_ADD('month', 1, NOW()), NOW())
, DATE_DIFF('week', DATE_ADD('week', 1, NOW()), NOW())
, DATE_DIFF('day', DATE_ADD('day', 1, NOW()), NOW())
, DATE_DIFF('hour', DATE_ADD('hour', 1, NOW()), NOW())
, DATE_DIFF('minute', DATE_ADD('minute', 1, NOW()), NOW())
, DATE_DIFF('second', DATE_ADD('second', 1, NOW()), NOW())
, DATE_DIFF('millisecond', DATE_ADD('millisecond', 1, NOW()), NOW())
; --날짜 연산을 쿼리안에 포함 시켜줘야함 2월 3월 넘어갈때 28일 29일 이렇기 때문날짜형 데이터의 데이터 타입 변경 (date -> string)
SELECT NOW()
, DATE_FORMAT(NOW(), '%Y-%m-%d')
, DATE_FORMAT(NOW(), '%Y%m%d')
, DATE_FORMAT(NOW(), '%Y%m')
, DATE_FORMAT(NOW(), '%H:%i:%s')
, DATE_FORMAT(NOW(), '%r')
, DATE_FORMAT(NOW(), '%T')
; --날짜형 포멧 표현식 확인부터 해야 함d type error
SELECT NOW()
--, CONCAT(CURRENT_DATE, CURRENT_TIME) 두개는 날짜형 데이터 이므로 문자열 데이터 핸들링 함수로는 사용 불가
, CONCAT(DATE_FORMAT(NOW(), '%Y-%m-%d'), DATE_FORMAT(NOW(), '%H:%i:%s'))
;날짜형 데이터의 데이터 타입 변경 (string -> date)
SELECT NOW()
, DATE_PARSE(DATE_FORMAT(NOW(), '%Y-%m-%d'), '%Y-%m-%d')
, DATE_PARSE(DATE_FORMAT(NOW(), '%H:%i:%s'), '%H:%i:%s')
;위에서 타입을 바꿔주었으니 error 뜸
SELECT NOW()
, CONCAT(DATE_PARSE(DATE_FORMAT(NOW(), '%Y-%m-%d'), '%Y-%m-%d'), DATE_PARSE(DATE_FORMAT(NOW(), '%H:%i:%s'), '%H:%i:%s’))
;날짜형 데이터의 데이터 타입 변경 CAST
가변길이의 데이터형으로 바꾸겠다.
SELECT NOW()
, CAST(DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS VARCHAR)
, CAST(NOW() AS VARCHAR)
, CAST(CAST(NOW() AS VARCHAR) AS TIMESTAMP)
;예제문제 풀기
/ 의뢰내용: 1. 2022년 12월의 콘텐츠 학년(grade), 콘텐츠 과목(subject_name), 서비스 메뉴(menu_name)별
1) 학습개수 (총계, 평균, 최대, 최소)
,완료학습개수(총계, 평균, 최대, 최소)
,caliper 기준 학습시간(총계, 평균, 최대, 최소)
,미디어 플레이어 일시정지 횟수 (총계, 평균, 최대, 최소)
,미디어 플레이어 점프 횟수 (총계, 평균, 최대, 최소)
,평가문제 풀이 횟수 (총계, 평균, 최대, 최소)
,평가문제 풀이 점수 (평균, 최대, 최소)
사용 테이블 : "text_biz_dm"."learning_analytics_content"
/
SELECT
grade, subject_name, menu_name
,COUNT(study_count) AS "학습 개수 총계"
,AVG(study_count) AS "학습 개수 평균"
,MAX(study_count) AS "학습 개수 최대"
,MIN(study_count) AS "학습 개수 최소"
,COUNT(study_completed_count) AS "완료 학습 개수 총계"
,AVG(study_completed_count) AS "완료 학습 개수 평균"
,MAX(study_completed_count) AS "완료 학습 개수 최대"
,MIN(study_completed_count) AS "완료 학습 개수 최소"
,COUNT(total_caliper_learning_time) AS "caliper 기준 학습시간 총계"
,AVG(total_caliper_learning_time) AS "caliper 기준 학습시간 평균"
,MAX(total_caliper_learning_time) AS "caliper 기준 학습시간 최대"
,MIN(total_caliper_learning_time) AS "caliper 기준 학습시간 최소"
,COUNT(video_pause_count) AS "미디어 플레이어 일시정지 횟수 총계"
,AVG(video_pause_count) AS "미디어 플레이어 일시정지 횟수 평균"
,MAX(video_pause_count) AS "미디어 플레이어 일시정지 횟수 최대"
,MIN(video_pause_count) AS "미디어 플레이어 일시정지 횟수 최소"
,COUNT(video_jump_count) AS "미디어 플레이어 점프 횟수 총계"
,AVG(video_jump_count) AS "미디어 플레이어 점프 횟수 평균"
,MAX(video_jump_count) AS "미디어 플레이어 점프 횟수 최대"
,MIN(video_jump_count) AS "미디어 플레이어 점프 횟수 최소"
,COUNT(test_count) AS "평가문제 풀이 횟수 총계"
,AVG(test_count) AS "평가문제 풀이 횟수 평균"
,MAX(test_count) AS "평가문제 풀이 횟수 최대"
,MIN(test_count) AS "평가문제 풀이 횟수 최소"
,AVG(test_average_score) AS "평가문제 풀이 점수 평균"
,MAX(test_average_score) AS "평가문제 풀이 점수 최대"
,MIN(test_average_score) AS "평가문제 풀이 점수 최소"
FROM "text_biz_dm"."learning_analytics_content" WHERE CONCAT(yyyy,mm) ='202212'
GROUP BY grade, subject_name, menu_name