MySQL & Spark

**설치 방법

https://computingforgeeks.com/how-to-install-mysql-8-on-ubuntu/

 

 **삭제 방법

sudo apt-get purge mysql-server mysql-client mysql-common mysql-server-core-* mysql-client-core-*
sudo rm -rf /etc/mysql  /var/lib/mysql
sudo apt-get autoremove
sudo apt-get autoclean

 

 

**mysql Connector 설치

 https://downloads.mysql.com/archives/get/p/3/file/mysql-connector-java-8.0.24.tar.gz

 

 

** python에서 mysql 연동을 위한 spark 클래스 생성

from pyspark.sql import SparkSession

#.config를 추가하여 'spark.jars'에 jar파일 경로를 적는다
ss = SparkSession.builder.config('spark.jars','/home/hdoop/mysql-connector-java-8.0.24.jar')\
.appName('pyspark_mysql').getOrCreate()

 

** MySQL Driver를 이용한 Spark DataFrameReader객체 생성

emp_loader = ss.read.format('jdbc')\
.option('url','jdbc.mysql://localhost:3306/database_name')\
.option('driver','com.mysql.jdbc.Driver')\
.option('dbtable','table_name')\
.option('user','user_id')\
.option('password','password')

type(emp_loader)  # pyspark.sql.readwriter.DataFrameReader

 

** 특정 db의 table 내용 불러오기

emp_df = emp_loader.load()
emp_df  # DataFrame[id: int, name: string, salary: int, dept: int]

emp_df.show()

Output

 

** 특정 db의 table에 내용 쓰기(덮어쓰기, 추가하기)

# to read -> to write   we need DataFrame (instead of sparkSession -> DataFrame)

import pandas as pd
data = {'id' : 16, 'name' : 'Wan', 'salary' : 3000, 'dept' : 30}
pdf = pd.DataFrame(data = data, index = range(1))
df = ss.createDataFrame(pdf)

# all are same except the part where sparkSession is   and  .mode('append') or .mode('overwrite') can be added
emp_writer = df.write.format('jdbc')\
.option('url', 'jdbc:mysql://localhost:3306/database_name')\
.option('driver', 'com.mysql.jdbc.Driver')\
.option('dbtable', 'table_name')\
.option('user', 'user_id')\
.option('password', 'password')\
.mode('append')

type(emp_writer)  # pyspark.sql.readwriter.DataFrameWriter


# new data saved to the table
emp_df = emp_writer.save()

# Load data that includes new data
emp_df = emp_loader.load()
emp_df.show()

output