02_03. SQL 기초

sparkSQL의 본격적인 사용 전, 기본적인 SQL 기초를 정리하고 간다.

 

from pyspark.sql import SparkSession

spark = SparkSession.builder.master("local").appName("learn-sql").getOrCreate()

• sparkSQL 사용시 local에 ‘learn-sql’이란 이름으로 spark session 생성

 

stocks = [
    ('Google', 'GOOGL', 'USA', 2984, 'USD'), 
    ('Netflix', 'NFLX', 'USA', 645, 'USD'),
    ('Amazon', 'AMZN', 'USA', 3518, 'USD'),
    ('Tesla', 'TSLA', 'USA', 1222, 'USD'),
    ('Tencent', '0700', 'Hong Kong', 483, 'HKD'),
    ('Toyota', '7203', 'Japan', 2006, 'JPY'),
    ('Samsung', '005930', 'Korea', 70600, 'KRW'),
    ('Kakao', '035720', 'Korea', 125000, 'KRW'),
]

# 스키마 정의
stockSchema = ["name", "ticker", "country", "price", "currency"]

# 데이터 타입을 알아서 정하게끔 데이터프레임 만듦
df = spark.createDataFrame(data=stocks, schema=stockSchema)

df.dtypes
df.show()

df.createOrReplaceTempView("stocks")

• dataframe에서 sql을 사용하려면 temperary view에 등록해야함
• 위 예시는 stocks란 이름의 tempview에 등록한 것

spark.sql("select name from stocks").show()
spark.sql("select name, price from stocks).show()

# where -> 조건문
spark.sql("select name, price from stocks where country = 'Korea'").show()
spark.sql("select name, price from stocks where price > 2000").show()
spark.sql("select name, price from stocks where price > 2000 and country = 'USA'").show()

# like -> 일부 조건문

# U로 시작하는 나라가 조건
spark.sql("select name, price from stocks where country like 'U%'").show()

# U로 시작하고, 중간이 e가 포함되지 않은 나라가 조건
spark.sql("select name, price from stocks where country like 'U%' and name not like '%e%'").show()

# 1000과 10000 사이의 가격 
spark.sql("select name, price from stocks where price between 1000 and 10000").show()

spark.sql("select name, price from stocks where country='USA'").show()

# 조건: Tesla의 가격보다 높고, 화폐가 USD인 
spark.sql("select name, price, currency from stocks \
where currenct = 'USD' and \
price > (select price from stocks where name = 'Tesla')").show()

# 정렬

spark.sql("select name, price from stocks order by price asc").show()
spark.sql("select name, price from stocks order by price desc").show()
spark.sql("select name, price from stocks order by length(name)").show()

# 가격의 합을 출력
spark.sql("select sum(price) from stocks where country = 'Korea'").show()

# 가격의 평균을 출력
spark.sql("select mean(price) from stocks where country = 'Korea'").show()

# 가격의 개수를 출력
spark.sql("select count(price) from stocks where country = 'Korea'").show()

# 한국과 미국 둘 다에서 가격을 얻고 싶을 때 in 사용
spark.sql("select count(price) from stocks where country in ('Korea', 'USA')").show()

earnings = [
    ('Google', 27.99, 'USD'), 
    ('Netflix', 2.56, 'USD'),
    ('Amazon', 6.12, 'USD'),
    ('Tesla', 1.86, 'USD'),
    ('Tencent', 11.01, 'HKD'),
    ('Toyota', 224.82, 'JPY'),
    ('Samsung', 1780., 'KRW'),
    ('Kakao', 705., 'KRW')
]

from pyspark.sql.types import StringType, FloatType, StructType, StructField

earningsSchema = StructType([
    StructField("name", StringType(), True),
    StructField("eps", FloatType(), True),
    StructField("currency", StringType(), True),
])

earningsDF = spark.createDataFrame(data=earnings, schema=earningsSchema)
earningsDF.dtypes
earningsDF.createOrReplaceTempView("earnings")
earningsDF.select("*").show()

• 이번에는 sqarksql schema 내에서 사용할 타입들을 직접 설정해주기 위해 import로 불러옴

# earnings 그룹의 이름과 stocks 그룹의 이름이 같을때 inner join
spark.sql("select * from stocks join earnings on stocks.name = earnings.name").show()

# PER: Price / EPS 
spark.sql("select stocks.name, (stocks.price/earnings.eps) from stocks join earnings on stocks.name = earnings.name ").show()