Cloud SQL 과 Spark 사용
컨텐츠 정보
- 3,278 조회
- 0 추천
- 목록
본문
✅ Check project permissions
role 확인
☰ > AM & Admin > IAM > Navigation menu > Home
Compute Engine default service account
{project-number}-compute@developer.gserviceaccount.com
에 edit
권한이 있는지 확인
Task 1. Create a Cloud SQL instance
☰ > SQL > Create instance > Choose MySQL
[Create]
Task 2. Create tables
만드는데 시간이 좀 걸립니다.
퀵렙에서 SQL을 보고 퀴즈를 푸는 배려를 제공 합니다.
Connect to the database
Connect to this instance > Open Cloud Shell
클라우드 콘솔 접속
접속
$gcloud sql connect rentals --user=root --quiet
비밀번호를 입력하면 접속이 되면 커서가 바뀝니다.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 40
Server version: 5.7.36-google-log (Google)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
MySQL > SHOW DATABASES;
> SHOW DATABASES;
(Output)
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.17 sec)
테이블을 생성하는 SQL을 복사해서 붙여 넣습니다.
CREATE DATABASE IF NOT EXISTS recommendation_spark;
USE recommendation_spark;
DROP TABLE IF EXISTS Recommendation;
DROP TABLE IF EXISTS Rating;
DROP TABLE IF EXISTS Accommodation;
CREATE TABLE IF NOT EXISTS Accommodation
(
id varchar(255),
title varchar(255),
location varchar(255),
price int,
rooms int,
rating float,
type varchar(255),
PRIMARY KEY (ID)
);
CREATE TABLE IF NOT EXISTS Rating
(
userId varchar(255),
accoId varchar(255),
rating int,
PRIMARY KEY(accoId, userId),
FOREIGN KEY (accoId)
REFERENCES Accommodation(id)
);
CREATE TABLE IF NOT EXISTS Recommendation
(
userId varchar(255),
accoId varchar(255),
prediction float,
PRIMARY KEY(userId, accoId),
FOREIGN KEY (accoId)
REFERENCES Accommodation(id)
);
SHOW DATABASES;
잘 생성 되었습니다.
mysql> SHOW DATABASES;
+----------------------+
| Database |
+----------------------+
| information_schema |
| mysql |
| performance_schema |
| recommendation_spark |
| sys |
+----------------------+
5 rows in set (0.17 sec)
USE recommendation_spark;
SHOW TABLES;
mysql> SHOW TABLES;
+--------------------------------+
| Tables_in_recommendation_spark |
+--------------------------------+
| Accommodation |
| Rating |
| Recommendation |
+--------------------------------+
3 rows in set (0.17 sec)
Task 3. Stage data in Cloud Storage
새창을 열고 아래의 내용을 붙여 넣습니다.
echo "Creating bucket: gs://$DEVSHELL_PROJECT_ID"
gsutil mb gs://$DEVSHELL_PROJECT_ID
echo "Copying data to our storage from public dataset"
gsutil cp gs://cloud-training/bdml/v2.0/data/accommodation.csv gs://$DEVSHELL_PROJECT_ID
gsutil cp gs://cloud-training/bdml/v2.0/data/rating.csv gs://$DEVSHELL_PROJECT_ID
echo "Show the files in our bucket"
gsutil ls gs://$DEVSHELL_PROJECT_ID
echo "View some sample data"
gsutil cat gs://$DEVSHELL_PROJECT_ID/accommodation.csv
Task 4. Load data from Cloud Storage into Cloud SQL tables
생성한 rental
에 접속 한 뒤 아래의 파일 2개를 import 합니다.
- accommodation.csv
- rating.csv
Browse > [Your-Bucket-Name] > accommodation.csv
Task 5. Explore Cloud SQL data
쿼리를 해보는 단계입니다.
예시된 쿼리를 복사 붙여넣기 하면서 실습 할 수 있고,
결과를 보고 퀴즈를 푸는 단계입니다.
Task 6. Launch Dataproc
Munu
☰ > Dataproc
create
[Create cluster]
- Set up Cluster : 이름, 존 설정
- Configure nodes: 머신 설정 (Master Node, Worker Node)
으로 설정하고 생성[Create] 합니다.
Shell 에서 아래의 명령어를 입력합니다.
echo "Authorizing Cloud Dataproc to connect with Cloud SQL"
CLUSTER=rentals
CLOUDSQL=rentals
ZONE=us-central1-c
NWORKERS=2
machines="$CLUSTER-m"
for w in `seq 0 $(($NWORKERS - 1))`; do
machines="$machines $CLUSTER-w-$w"
done
echo "Machines to authorize: $machines in $ZONE ... finding their IP addresses"
ips=""
for machine in $machines; do
IP_ADDRESS=$(gcloud compute instances describe $machine --zone=$ZONE --format='value(networkInterfaces.accessConfigs[].natIP)' | sed "s/\['//g" | sed "s/'\]//g" )/32
echo "IP address of $machine is $IP_ADDRESS"
if [ -z $ips ]; then
ips=$IP_ADDRESS
else
ips="$ips,$IP_ADDRESS"
fi
done
echo "Authorizing [$ips] to access cloudsql=$CLOUDSQL"
gcloud sql instances patch $CLOUDSQL --authorized-networks $ips
prompte 에서 Y
를 입력 한 뒤 ENTER
를 누릅니다.
☰ > SQL > Overview 에서
ip를 확인합니다.
Task 7. Run the ML model
gsutil cp gs://cloud-training/bdml/v2.0/model/train_and_apply.py train_and_apply.py
cloudshell edit train_and_apply.py
train_and_apply.py
에서
# MAKE EDITS HERE
CLOUDSQL_INSTANCE_IP = '' # <---- CHANGE (database server IP)
CLOUDSQL_DB_NAME = 'recommendation_spark' # <--- leave as-is
CLOUDSQL_USER = 'root' # <--- leave as-is
CLOUDSQL_PWD = '' # <---- CHANGE
CLOUDSQL_INSTANCE_IP
와 CLOUDSQL_PWD
를 수정합니다.
그리고 Cloud Shell에서 이 파일을 Cloud Storage 버킷에 복사합니다.
gsutil cp train_and_apply.py gs://$DEVSHELL_PROJECT_ID
Task 8. Run your ML job on Dataproc
메뉴
Dataproc > rentals > Submit job 에서
Job type
: PySparkMain python file
: gs://{bucket-name}/train_and_apply.py/Max restarts per hour
: 1 입력
[Submit] 합니다.
Task 9. Explore inserted rows with SQL
새로운 브라우져 텝에서
메뉴
SQL > rentals > Connect to this instance > Open cloud shell 로 새로운 shell tab을 누릅니다.
실습을 진행 합니다.
SELECT
r.userid,
r.accoid,
r.prediction,
a.title,
a.location,
a.price,
a.rooms,
a.rating,
a.type
FROM Recommendation as r
JOIN Accommodation as a
ON r.accoid = a.id
WHERE r.userid = 10;