Streamlit와 BigQuery 활용한 배포 (API)

# .streamlit/secrets.toml
[public_data_api]
seoul_api_key = 'your_api_key'

[gcp_service_account]
type = "service_account"
project_id = "your_project_id"
private_key_id = "your_private_key_id"
private_key = "your_private_key"
client_email = "your_client_email"
client_id = "your_client_id"
auth_uri = "https://accounts.google.com/o/oauth2/auth"
token_uri = "https://oauth2.googleapis.com/token"
auth_provider_x509_cert_url = "https://www.googleapis.com/oauth2/v1/certs"
client_x509_cert_url = "your_client_x509_cert_url"

import streamlit as st
from google.oauth2 import service_account

SERVICE_KEY = st.secrets.public_data_api.seoul_api_key

# Create API client.
credentials = service_account.Credentials.from_service_account_info(
    # Very Important Point
    st.secrets["gcp_service_account"]
)

# -*- coding:utf-8 -*-
import requests
import pandas as pd

# Google Cloud
from google.cloud import bigquery
import pandas_gbq

# API Key Settings
from utils import credentials, SERVICE_KEY
client = bigquery.Client(credentials=credentials)

def aptCrawling(SERVICE_KEY):
    data = None
    for j in range(1,2):
        url = f'http://openapi.seoul.go.kr:8088/{SERVICE_KEY}/json/tbLnOpendataRtmsV/{1+((j-1)*1000)}/{j*1000}'
        print(url)
        req = requests.get(url)
        content = req.json()
        con = content['tbLnOpendataRtmsV']['row']
        result = pd.DataFrame(con)
        data = pd.concat([data, result])
    data = data.reset_index(drop=True)
    data['DEAL_YMD'] = pd.to_datetime(data['DEAL_YMD'], format=("%Y%m%d"))

    return data

def save2BQ(data):
    table_name = "seoul.realestate"
    project_id = "streamlit-dashboard-369600"

    # Save the DataFrame to BigQuery
    pandas_gbq.to_gbq(data,
                      table_name,
                      project_id=project_id, if_exists='replace')

if __name__ == "__main__":
    data = aptCrawling(SERVICE_KEY)
    save2BQ(data)

$ python aptCrawling.py
http://openapi.seoul.go.kr:8088/your_api_key/json/tbLnOpendataRtmsV/1/1000
100%|████████████████████████████████████████████████████████████| 1/1 [00:00<?, ?it/s]

# streamlit_app.py

import streamlit as st
from google.cloud import bigquery
import seaborn as sns
import pandas as pd
import pandas_gbq

from utils import credentials, SERVICE_KEY
client = bigquery.Client(credentials=credentials)

# Perform query.
# Uses st.experimental_memo to only rerun when the query changes or after 10 min.
@st.cache_data(ttl=600)
def run_query(cols, name):
    st.write("Load DataFrame")
    sql = f"SELECT {cols} FROM streamlit-dashboard-369600.seoul.{name}"
    df = client.query(sql).to_dataframe()

    st.dataframe(df)

def main():
    tableNames = st.selectbox("테이블 선택", ("realestate", "iris"))
    if tableNames == "iris":
        run_query(cols="*", name="iris")
    else:
        sql = """
        SELECT STRING_AGG(column_name)
        FROM `streamlit-dashboard-369600.seoul.INFORMATION_SCHEMA.COLUMNS`
        where table_name = 'realestate'
        group by table_name
        """

        df = client.query(sql).to_dataframe()
        all_cols = df.values[0][0].split(",")
        columns = st.multiselect("컬럼명 선택", all_cols, default=all_cols)
        temp_Strings = ", ".join(columns)
        run_query(temp_Strings, tableNames)

if __name__ == "__main__":
    main()