Course

Cloud Engineering

Lab

목록

• Cloud IAM: Qwik Start
• Introduction to SQL for BigQuery and Cloud SQL ⬅️ 오늘의 Lab!
• Multiple VPC Networks
• Cloud Monitoring: Qwik Start
• Managing Deployments Using Kubernetes Engine

---

🌠Introduction to SQL for BigQuery and Cloud SQL

개요

BigQuery에서 런던의 자전거 공유 관련 공개 데이터 세트를 위한 기본 SQL 쿼리 키워드를 학습하고, 데이터 세트의 하위 집합을 CSV 파일로 보내 Cloud SQL에 업로드 해보자.

실습 과정

1. 쿼리 가능한 데이터 업로드

1) 네비게이션 메뉴 > BigQuery > Studio 메뉴 클릭

2) Explorer(탐색기) 아이콘 클릭 > [+ Add data] 버튼 클릭

3) 검색창(Search for resources)에 검색

• bigquery-public-data
• london_bicycles
• cycle_hire
• cycle_stations

참고

나는 검색이 잘 안되어서 cycle_hire와 cycle_stations를 직접 검색 후에 검색 결과 가장 끝에 있는 별(⭐) 표시를 클릭하였다.
이 표시를 하면 나중에 Explorer 아래에 Starred 메뉴를 통해 쉽게 찾고 접근이 가능하다.

---

2. 테이블에서 쿼리 실행

1) 테이블 내용 살펴보기

➡️ Explorer > ⭐ Starred > cycle_hire 클릭 > Preview 탭을 선택하면 테이블 내의 데이터들의 목록을 볼 수 있다.

2) SQL 편집기 열기

➡️ Preview 탭 옆에 [+] 을 클릭하여 SQL 편집기를 생성한다.

3) SELECT 실행

SELECT end_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire`;

➡️ 편집기에 쿼리를 작성 후 Run(Ctrl+Enter) 실행하면 쿼리 결과가 나온다.

4) WHERE 조건이 있는 SELECT 실행

SELECT * FROM `bigquery-public-data.london_bicycles.cycle_hire` WHERE duration>=1200;

➡️ 3)과 달리, 지속시간(duration) 값이 1200 이상인 값만 조회하였고, 우측 하단에 보면 조회된 개수가 더 줄어들었음을 확인할 수 있다.

---

3. 심화 SQL 키워드 사용

1) GROUP BY 사용

SELECT start_station_name FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

GROUP BY✨

공통 기준(예: 열 값)을 공유하는 결과 집합 행을 집계하며 이러한 기준에 대해 찾은 모든 고유 항목을 반환한다.

2) COUNT 사용

SELECT start_station_name, COUNT(*) FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

COUNT✨

동일한 기준(예: 열 값)을 공유하는 행 수를 반환한다.

3) AS 사용

SELECT start_station_name, COUNT(*) AS num_starts FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name;

AS✨

테이블 또는 열의 별칭을 생성하는 기능으로, 별칭은 AS가 지정하는 반환된 열이나 테이블에 부여되는 새 이름이다.
2) 에서 COUNT로 출력된 칼럼명이 AS로 부여한 num_starts로 변경됐다.

4) ORDER BY 사용

//start_station_name 기준으로 정렬 (default: ASC)
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY start_station_name;

//num 기준으로 정렬 (default: ASC)
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num;

//num 기준으로 정렬 (DESC: 내림차순)
SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

ORDER BY✨

데이터를 지정된 조건 또는 열 값에 따라 오름차순(AESC, default) 또는 내림차순(DESC)로 정렬한다.

• ASEC: (1, 2, 3, 4), (가, 나, 다, 라), (a, b, c, d)
• DESC: (4, 3, 2, 1), (라, 다, 나, 가), (d, c, b, a)

➡️ Database에 따라, NULL 값이 맨 앞이나 맨 뒤에 위치할 수 있다.

---

4. BigQuery 데이터를 CSV 파일로 내보내기

1) SQL 에디터 내 Save results 버튼 클릭

2) Local download > CSV 선택

저장해야 하는 쿼리 데이터🤓

• start_station_name.csv

SELECT start_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY start_station_name ORDER BY num DESC;

• end_station_name.csv

SELECT end_station_name, COUNT(*) AS num FROM `bigquery-public-data.london_bicycles.cycle_hire` GROUP BY end_station_name ORDER BY num DESC;

---

5. Cloud Storage Bucket에 CSV 업로드 하기

1) Cloud Storage > Bucket 메뉴 클릭

2) Bucket 생성

➡️ 버킷의 고유한 이름(Project ID)만 설정 후 다른 모든 설정은 기본 값으로 그대로 둔 채 생성

3) Bucket에 CSV 파일 업로드

➡️ Upload > file 클릭 후 저장한 CSV 파일 업로드 (2개)

---

6. Cloud SQL 인스턴스 생성

1) 네비게이션 메뉴 > Cloud SQL > Instances 클릭

2) 개발 인스턴스 만들기(Create Development instance) 클릭

3) 인스턴스 생성

선택 항목

• Edition preset: Development ✅
• Database version: MySQL 8.0 ✅
• Instance ID: my-demo (다른 아이디로 설정 가능)
• Password: ChangeMe1! (다른 비밀번호로 설정 가능)
• Zone availability: Multiple zones(Highly available) ✅
  • Specify zones: asia-southeast1-b (Primary zone)만 선택하고, Secondary zone은 Any로 그대로 둔다.

4) 인스턴스 생성 확인

---

7. Cloud SQL 데이터베이스 및 테이블 만들기

1) 인스턴스의 Cloud Shell 열기

2) 명령어를 사용하여 SQL 인스턴스에 연결

# Instance ID를 다른 걸로 설정했다면 my-demo 대신 해당 아이디 입력
gcloud sql connect my-demo --user=root --quiet

3) 비밀번호 입력하여 MySQL에 접속

➡️ 비밀번호는 6-3. 에서 만든 비밀번호!

4) 데이터베이스 생성

CREATE DATABASE bike;

5) 테이블 생성

//bike 데이터베이스에 접속 > london1 이라는 테이블 생성
USE bike;
CREATE TABLE london1 (start_station_name VARCHAR(255), num INT);

//bike 데이터베이스에 접속 > london2 이라는 테이블 생성
USE bike;
CREATE TABLE london2 (end_station_name VARCHAR(255), num INT);

6) 테이블 생성 확인

SELECT * FROM london1;
SELECT * FROM london2;

➡️ 데이터를 로드하지 않았기 때문에, 결과는 Empty로 뜨는 게 정상!

---

8. 테이블에 CSV 파일 업로드하기

1) Cloud SQL 콘솔 접속

2) CSV 파일 import 하기

참고

• File format: CSV
• Select source file: Select from Google Cloud Storage
  • Cloud Storage에서 생성한 Bucket 선택 후, 업로드한 CSV 파일 클릭
• Database: bike
• Table
  • start_station_data.csv 파일: london1
  • end_station_data.csv 파일: london2

3) 테이블 데이터 확인

SELECT * FROM london1;
SELECT * FROM london2;

➡️ Instance에 연결된 Cloud Shell 세션에서 해당 쿼리를 실행하면 처음 테이블 생성했을 때는 Empty 였던 데이터들이 각 테이블마다 import한 .csv 데이터로 채워진 것을 확인할 수 있다.

---

9. Cloud SQL에서 데이터 쿼리 실행하기

1) DELETE 키워드

DELETE FROM london1 WHERE num=0;
DELETE FROM london2 WHERE num=0;

2) INSERT INTO 키워드

INSERT INTO london1 (start_station_name, num) VALUES ("test destination", 1);

3) UNION 키워드

SELECT start_station_name AS top_stations, num FROM london1 WHERE num>100000
UNION
SELECT end_station_name, num FROM london2 WHERE num>100000
ORDER BY top_stations DESC;