Dune Analytics에서 MakerDao 주요 지표 다운로드 및 활용

이번 포스트에서는 Ethereum, Polygon, Binance Smart Chain(BSC), Gnosis Chain 등과 같은 블록체인의 트랜잭션, 토큰발행량, 수수료 등의 data를 제공하는 Dune Analytics로부터 원하는 data를 다운받고 가공하는 과정을 소개한다.

Dune Analytics에는 블록체인 data가 데이터베이스 형태로 저장되어 있으므로 이로부터 원하는 data를 불러오기 위해서는 SQL(Structured Query Language)을 사용하여야한다. 또한 Dune Analytics 사이트에서는 원하는 data를 그래프나 표 등으로 시각화하는 tool을 제공하지만 data를 엑셀이나 CSV 형태의 파일로 다운로드 받을 수 있는 기능은 제공하지 않으므로 python의 ‘duneanalytics’ 라이브러리를 통해 data를 다운 받아야한다.

여기에서는 예시로서 MakerDao 프로토콜의 stability fee에 대한 과거 자료를 다운받아 본다. 앞으로 수행할 작업의 절차는 다음과 같다.

• Dune Analytics에 로그인하여 SQL query 입력하고 결과 실행(SQL query는 사이트 내에 참고할 만한 예시로 제공하는 query들이 많으니 이를 활용하자)
• python의 ‘duneanalytics’ 라이브러리를 통해 dune analytics에 접속
• 위에서 실행한 sql query의 결과를 python을 통해 불러오고 저장
• 저장된 data를 분석에 편리한 형태로 변형(pandas 라이브러리 활용)

1. Maker 프로토콜의 ETH stability fee data 다운로드

위에서 정리한 작업 절차에 따라 dune analytics에 로그인하여 SQL query를 입력하고 결과를 실행시킨다.

참고로 Maker 프로토콜의 stability fee와 DSR은 1초마다 이자가 누적되는 복리이다. 동 프로토콜에서는 stability fee와 DSR이 초당 이자율에 $10^{27}$을 곱한 형태로 세팅 되어있다. 이를 연이율로 환산하기 위해서는 다음과 같은 과정이 필요하다.

\[R_{annual} = (\frac{r_{second}}{10^{27}})^{31536000} - 1\] \[\cdot \ r_{second}: Maker\; 프로토콜에\; 세팅된\; 초당\; 이자율\\ \cdot \ R_{annual}: 연 환산\;이자율\\ \cdot \ 31536000: 365(일)*24(시간)*60(분)*60(초)\\\]

ETH stability fee의 일자별 data를 불러오는 sql query는 다음과 같다.(이는 cnParadigm의 query를 참고하였다.)

SELECT 
date_trunc('day', call_block_time) as day,
ROUND((Power((data/1e27),31536000)-1),5) as stabilityFee,
CASE ilk /*ilk는 maker 프로토콜에서 담보자산 종류를 의미하는 변수*/
    WHEN '\x4554482d41000000000000000000000000000000000000000000000000000000' THEN 'ETH-A'
    WHEN '\x555344432d410000000000000000000000000000000000000000000000000000' THEN 'USDC-A'
    WHEN '\x574254432d410000000000000000000000000000000000000000000000000000' THEN 'WBTC-A'
    ELSE 'other'
END AS collateralType
FROM makermcd."JUG_call_file" /* stability fee 정보가 들어있는 database */
WHERE call_success
AND ilk IN(  
/* ETH-A */
'\x4554482d41000000000000000000000000000000000000000000000000000000',
/* USDC-A */
'\x555344432d410000000000000000000000000000000000000000000000000000',
/* WBTC-A */
'\x574254432d410000000000000000000000000000000000000000000000000000'
)
ORDER BY day desc

sql query를 보다보면 ‘FROM makermcd.”JUG_call_file”‘에 대한 의문이 생길 수 있다.

stability fee에 대한 정보가 저 database에 들어있는지 어떻게 알 수 있으며, 저 database에 어떠한 변수들이 들어있는지는 어디서 확인가능한가?

이는 Maker protocol의 technical whitepaper와 dune analytics 사이트의 query 입력창 왼편에 있는 database 검색 기능을 통해 사용자가 파악해야 하는 부분이다. MakerDao는 Ethereum 블록체인을 기반으로 하고 있으므로 query 왼편 검색창에 ‘1.Ethereum’으로 조건을 설정하고 아래에 ‘makermcd’를 입력하면 아래의 사진과 같은 MakerDao multi-collateral Dai에 대한 database 목록을 볼 수 있다.

Dune analytics에서 위 query를 실행하면 아래와 같은 table이 출력된다.

그러나 앞서 밝힌대로 이 data를 웹사이트 내에서 엑셀 파일 형태로 다운받을 수 없기 때문에 이를 활용한 다른 분석에 어려운 점이 있다.

아래에서는 query 실행 결과를 python ‘duneanalytics’ 라이브러리를 이용해 가공하고 CSV 파일로 저장한다. (duneanalytics 라이브러리에 대한 자세한 사용 방법 등은 다음의 링크를 참조)

# Library import
from duneanalytics import DuneAnalytics

# Login information(동 라이브러리 사용을 위해서는 dune analytics에 sign up을 해야함)
dune = DuneAnalytics('사용자id', '사용자 비밀번호')

# id, password를 통해 dune analytics에 로그인
dune.login()
dune.fetch_auth_token()

# query의 결과를 defi_data 변수에 저장
# (query_id는 dune analytics에서 SQL query를 입력하고 얻은 결과창의 url을 통해 확인가능) 
result_id = dune.query_result_id(query_id = 405665)
defi_data = dune.query_result(result_id)

# data가 dicitonary 형태이므로 key, value 각각의 속성을 파악
type(defi_data)

print(defi_data.keys())
>>> dict_keys(['data'])

# defi_data는 key로 'data'를 갖는 다중 dictionary 구조임을 알수 있음
type(defi_data['data'])

# defi_data의 'data'는 어떤 key를 갖고 있는지?
print(defi_data['data'].keys())
>>> dict_keys(['query_results', 'get_result_by_result_id'])

# 각각의 key 'query_results', 'get_result_by_result_id'를 확인
# 이중 'columns'를 key로 갖는 value list가 우리가 원하는 자료
print(defi_data['data']['query_results'])
>>> [{'id': '0f2cfbdc-ca7a-4b28-b9b5-b4f648823e5a', 'job_id': 'f9ced8fe-75de-4174-a928-d9a7c42a87b1', 'error': None, 'runtime': 0, 'generated_at': '2022-03-09T01:03:08.128138+00:00', 'columns': ['day', 'stabilityfee', 'collateraltype'], '__typename': 'query_results'}]


print(defi_data['data']['get_result_by_result_id'])
>>> [{'data': {'collateraltype': 'WBTC-A', 'day': '2022-02-07T00:00:00+00:00', 'stabilityfee': 0.0375}, '__typename': 'get_result_template'}, {'data': {'collateraltype': 'ETH-A', 'day': '2022-02-07T00:00:00+00:00', 'stabilityfee': 0.0225}, '__typename': 'get_result_template'}

## 앞에서 살펴본 query_results의 'columns'의 원소를 key로 갖고 우리가 원하는 값들은 이들 key에 대응하는
## value로 저장되어 있다. 이를 활용하여 'day', 'collateraltype', 'stabilityfee' 값만 추려서 dataframe을 구성하면된다.

# 우리가 필요한 정보는 defi_data['data']의 'get_result_by_result_id' key 값의 values에 저장되어 있으므로 이를 a라는 list로 저장
a = defi_data['data']['get_result_by_result_id']

# for loop를 이용해 'day', 'collateraltype', 'stabilityfee' 값을 table_contents라는 빈 list에 저장
table_contents = []

for i in range(len(a)):
  content = list(a[i]['data'].values())
  table_contents.append(content)

# table_conents에 원하는 값들이 잘 저장되어있는지 확인할 수 있다.
print(table_contents[: 2])
>>> [['WBTC-A', '2022-02-07T00:00:00+00:00', 0.0375], ['ETH-A', '2022-02-07T00:00:00+00:00', 0.0225]]

이제 table_contents list에 담긴 정보를 pandas dataframe 형태로 변환하고 csc 파일로 저장해보자.

import pandas as pd

# dataframe의 열 이름으로 지정할 값들을 저장
columns = list(a[0]['data'].keys())
# table이라는 dataframe을 생성, 이때 column의 이름은 위에서 저장해둔 columns를 이용
table = pd.DataFrame(tabel_contents, columns = columns)

print(table.head())
  collateraltype                        day  stabilityfee
0         WBTC-A  2022-02-07T00:00:00+00:00        0.0375
1          ETH-A  2022-02-07T00:00:00+00:00        0.0225
2          ETH-A  2022-01-24T00:00:00+00:00        0.0250
3          ETH-A  2021-12-09T00:00:00+00:00        0.0275
4         WBTC-A  2021-11-22T00:00:00+00:00        0.0400

# day, collateraltype, stability 순서로 column을 재배치
table = table[['day', 'collateraltype', 'stabilityfee']]

# day column에 저장된 날짜 값들을 'yyyy-mm-dd'형태로 변환
table['day'] = table['day'].apply(lambda x: x[: -15])

# csv 파일로 저장
table.to_csv('dune_stb_fee.csv', index = False)

2. Maker 프로토콜의 청산 data 다운로드

이번에는 Maker 프로토콜에서 담보자산 가치 하락에 의해 청산된 vault에서 회수된 Dai data를 다운받아본다. 이를 통해 2020.3.12일 ETH 가격 급락 이벤트 때 실제로 많은 담보자산의 청산이 이뤄났는지 등을 확인해볼 수 있다.

전반적인 절차는 위에서 설명한 stability fee 다운로드 방식과 동일하고 SQL query 부분만 달라진다.

아래의 query는 일자별 담보자산 청산에 따른 Dai 대출 회수량을 담보자산별로 집계하여 보여준다.( @woojin9210 / MakerDAO Liquidations Per Day의 query를 바탕으로 변형하였다.)

SELECT
date_trunc('day', block_time) as day,
sum(art/1e18) as liq,
CASE ilk 
    WHEN '\x4554482d41000000000000000000000000000000000000000000000000000000' THEN 'ETH-A'
    WHEN '\x4554482d42000000000000000000000000000000000000000000000000000000' THEN 'ETH-B'
    WHEN '\x4554482d43000000000000000000000000000000000000000000000000000000' THEN 'ETH-C'
    WHEN '\x555344432d410000000000000000000000000000000000000000000000000000' THEN 'USDC-A'
    WHEN '\x574254432d410000000000000000000000000000000000000000000000000000' THEN 'WBTC-A'
    WHEN '\x554e492d41000000000000000000000000000000000000000000000000000000' THEN 'UNI-A'
    WHEN '\x554e495632554e494554482d4100000000000000000000000000000000000000' THEN 'UNIV2UNIETH-A'
    ELSE 'other'
END AS collateralType
FROM makermcd."CAT_evt_Bite"
LEFT JOIN ethereum."transactions" tx ON evt_tx_hash = tx.hash
GROUP BY 1, 3
ORDER BY 1 asc

마찬가지로 query 실행 결과를 python ‘duneanalytics’ 라이브러리를 이용해 가공하고 CSV 파일로 저장한다.

# query의 결과를 defi_data 변수에 저장
# (query_id는 dune analytics에서 SQL query를 입력하고 얻은 결과창의 url을 통해 확인가능) 
result_id = dune.query_result_id(query_id = 501557)
data = dune.query_result(result_id)
liq_data = data

# 우리가 필요한 정보는 liq_data['data']의 'get_result_by_result_id' 에 저장되어 있으므로 이를 b라는 list로 저장
b = liq_data['data']['get_result_by_result_id']

# for loop를 이용해 'day', 'collateraltype', 'liq' 값을 빈 list인 table_contents_liq에 저장
table_contents_liq = []

for i in range(len(b)):
    content = list(b[i]['data'].values())
    table_contents_liq.append(content)

# columns_liq에 dataframe의 column으로 들어갈 값들을 저장
columns_liq = list(b[0]['data'].keys())

# dataframe 형태로 list를 저장
import pandas as pd

table_liq = pd.DataFrame(table_contents_liq, columns = columns_liq)

# day column에 저장된 날짜 값들을 'yyyy-mm-dd'형태로 변환
table_liq['day'] = table_liq['day'].apply(lambda x: x[:-15])

# day, collateraltype, liq 순서로 column을 재배치
table_liq = table_liq[['day', 'collateraltype', 'liq']]

# csv로 저장
table_liq.to_csv('dune_liq.csv', index = False)