230313_DB복습

안녕하세요. 보랏입니다. 

 

오늘 또 새로운 한 주가 시작되었습니다

오늘은 FTP 클라이언트 프로그램, 리눅스 DB백업, 한국복지 패널조사 데이터분석,귀무가설vs대립가설을 배웠습니다.

 

바로 복습 시작하겠습니다.

 

1. FTP클라이언트 프로그램 (Filezilla)

• web에서는 FTP를 사용하지 않지만 네트워크 공유서버, 웹서버, 파일 서버 등에는 FTP를 통해 파일 교한이 필요합니다. 
• 따라서, 파일 전송시에는 FTP클라이언트 프로그램을 사용하여 FTP서버 접속이 가능합니다. 
• 그 중 가장 유명한 FTP클라이언트 프로그램이 'Filezilla'로 오픈소스 기반으로 모든 소스 코드가 공개되어 있으며, FTP, FTPS, SFTP등을 지원합니다. 

• 이제 기존 가상머신에 설치하였던 리눅스 가상머신을 활용하여 가상머신 내 SQL에 데이터베이스를 적재하도록 하겠습니다. FileZilla를 다운받은 후 아래 설정을 해서 데이터 임포트를 진행해야 합니다.
  1. 프로토콜 : SFTP로 설정
  2. 호스트 : 리눅스 서버 
  3. 사용자/비밀번호 : 리눅스 가상머신 설정 로그인/비밀번호

• 이후 로컬 사이트에서 올리고자 하는 데이터폴더를 선택하여 '데이터 업로드'를 진행하면 리모트사이트에 해당 파일이 임포트됩니다. 

 

2. 리눅스 SQL 백업 자료 복원

• 이제 리모트사이트에 임포트된 데이터를 리눅스 계정과 연결되어 있는 SQL에 적재하도록 하겠습니다. 우선 리눅스 mysql에 접속합니다. 
  1. 리눅스 mysql 접속 : mysql -uroot(아이디) -p1234(비밀번호)## 계정 오류 발생 시 : sql > select user, host from mysql.user;
  2. show databases : test database 확인
  3. database backup
     • mysqldump -uroot -p1234 database_name > test_backup.sql
     • vim test_backup.sql
  4. vim 내용 수정
     • 명령어 모드 : set nu(문자열 숫자 확인)
     • 비주얼 모드 : G(맨 마지막 이동), gg(맨 처음 이동
  5. vim 파일명.sql
     • 명령어 모드 : %s/DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci/DEFAULT CHARSET=utf8 (치환)
     • 저장 후 나가기
  6. 복원하기 : mysql -uroot -p1234 database_name < 파일명.sql  ### 오류 발생
  7. vim 파일명.sql
     • 명령어 모드 : 111(111번째 줄로 이동)
     • 명령어 모드 : /CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci (찾기모드)
     • %s/CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci/ (치환)
     • 저장 후 나가기
  8. mysql -uroot -p1234 database_name < 파일명.sql

---

### 추가 공부 

• mysql DB 모든 내용 덤프하기
  • 단일 : mysqldump -uroot -p database_name > database.sql
  • 여러 개 : mysqldump -uroot -p database_name_a database_name_b > database_a_b.sql
  • 모두(all) : mysqldump -uroot -p --all-databases > all_databases.sql
  • 압축 : mysqldump database_name | gzip > database_name.sql.gz
  • DB table 생성 정보(create 명령어)와 table 내 정보 (insert 명령)가 모두 아웃풋.sql에 저장
• mysql DB 복원하기 
  • mysql database_name < file.sql
  • DB생성 후 덤프내용 가져오기 :
    • mysql -u root -p -e "create database database name";
    • mysql -u root -p database_name < database_name.sql;
• 백업 자동화 : 지정된 시간에 mysqldump 명령을 실행하는 cron작업을 생성
  • 사용자 홈 디렉토리에 my.cnf파일 생성 : sudo nano ~/ .my.cnf
  • .my.cnf파일에 붙여넣기 : user = dbuser, password = dbpasswd
  • 사용자 엑세스 할 수 있도록 자격 파일 사용 권한 제한 : chmod 600 ~/ .my.cnf
  • 백업 저장 디렉터리 생성 : mkdir ~/db_backups
  • 사용자 crontab파일 열기 : crontab -e
  • 매일 오전 3시 데이터베이스 이름 mydb의 백업을 생성하고 다음 cron작업 수행 : 0 3 * * * /usr/bin/mysqldump -u dbuser mydb > /home/username/db_backups/mydb-$(date +\%Y\%m\%d).sql

 

3. 한국복지 패널조사 데이터 분석

• 강사님께서 공유주신 2021년 한국복지 패널조사 데이터를 활용한 데이터 분석을 진행하였습니다. 해당 자료는 spss로 작성되어 있어 해당 파일을 읽을 수 있는 'pyreadstat' 라이브러리를 설치하였습니다.

### pandas에서 spss읽기 라이브러리
!pip install pyreadstat

### 파일 불러오기
df = pd.read_spss("./(2021년 16차 한국복지패널조사) 데이터(beta1)_spss/Koweps_hpc16_2021_beta1.sav")

### 수치형 자료 확인
df.describe()

• 이제 성별('h16_g3') 에 대해 확인을 하면 1.0 혹은 2.0으로 구성되어 있습니다. 1.0은 남성, 2.0은 여성으로 되어 있는데 이는 'label encoding'으로 구성되어 있습니다.
  • label encoding : 범주형 변수를 수치형 변수로 변경 / n개의 범주형 데이터를 0부터 n-1개 까지의 연속적 수치 데이터로 변경
  • one-hot encoding : n개의 범주형 데이터를 n개의 비트(0,1)벡터로 표현 / 서로 다른 범주 데이터는 독립적인 관계라는 것을 나타낼 수 있음

### 특정 데이터 추출 및 컬럼명 변경

col = {'h16_reg7' : '지역',
    "h16_g3" : "성별",
"h16_g4" : "출생연도",
"h16_g6" : "교육수준1",
"h16_g7" : "교육수준2",
"h16_g10" : "혼인여부",
"h16_g11" : "종교",
"h16_med8" : "건강검진",
"h16_eco6" : "근로형태",
"h16_eco_7_1" : "근로계약기간_설정여부",
"h16_eco9" : "직종",
"h16_inc2" : "총급여액",
"h16_pers_income4" : "부업소득",}

extract_col = ["h16_reg7", "h16_g3","h16_g4","h16_g6","h16_g7","h16_g10","h16_g11","h16_med8","h16_eco6","h16_eco_7_1","h16_eco9","h16_inc2","h16_pers_income4"]

data = df[extract_col].copy()

### 데이터프레임 컬럼명 col1대로 변경
data.rename(columns = col, inplace = True)

• 이제 데이터분석에 필요한 데이터 컬럼을 선택한 후 가시성이 좋도록 컬럼명을 변경하였습니다. 이후 성별 평균금액과 나이별 구분을 진행하였습니다. 

### 성별 평균금액
data.groupby(['성별'])[['총급여액']].mean()

### 성별 평균금액 막대그래프 표현
data.groupby(['성별'])[['총급여액']].mean().plot(kind='bar')

### 연령대별 구분

#방법 1
data['연령대'] = 2021 - data['출생연도']
data['연령대'] = data.나이.apply(lambda x : '초년' if x < 30 else ('중년' if x <= 60 else '노년'))

# 방법2

def tmp(age) : 
    aged = 2021 - year
    if (aged < 30) : 
        return '초년'
    elif aged < 60 : 
        return '중년'
    else : 
        return '노년'
        
### 연령대별 비율
data['연령대'].value_counts(normalize=True)

### 연령대별, 성별 총급여액 확인
data.groupby(['연령대', '성별'])['총급여액'].describe()

 

4. 귀무가설 vs 대립가설 

• 통계의 가설 기초인 귀무가설과 대립가설을 배웠습니다. 
• 귀무가설(H0) : 실험결과가 우연에 의한 것이라고 가정
• 대립가설(H1) : 실험결과가 우연이 아닌 다른 원인에 있다는 것을 가정
  • 예시1) 동전 던지기 실험
  • 귀무가설(H0): 동전은 앞면과 뒷면이 나올 확률이 같다.
  • 대립가설(H1): 동전은 앞면과 뒷면이 나올 확률이 같지 않다.
  • 동전을 100번 던져서 앞면이 60번, 뒷면이 40번 나왔다고 가정해봅시다. 이때 귀무가설이 맞다면, 이러한 결과는 우연에 의한 것입니다. 하지만 대립가설이 맞다면, 동전이 공정하지 않기 때문에 이러한 결과가 나온 것입니다. 이를 통해 귀무가설과 대립가설이 서로 경쟁하는 것을 확인할 수 있습니다.
  • 예시2) 특정 집단의 평균 신장에 대한 실험
  • 귀무가설(H0): 이 집단의 평균 신장은 A와 같다.
  • 대립가설(H1): 이 집단의 평균 신장은 A와 다르다.
  • 어떤 집단의 평균 신장이 170cm인 것으로 알려져 있습니다. 이 집단의 표본을 뽑아서 평균 신장을 측정하였더니 175cm가 나왔다고 가정해봅시다. 이때 귀무가설이 맞다면, 이러한 결과는 우연에 의한 것입니다. 하지만 대립가설이 맞다면, 이 집단의 평균 신장이 170cm가 아니기 때문에 이러한 결과가 나온 것입니다. 이를 통해 귀무가설과 대립가설이 서로 경쟁하는 것을 확인할 수 있습니다.

 

오늘은 이렇게 복습 마치겠습니다.

 

감사합니다.