GCP VM Connect to BigQuery using Streamlit (ver. 2025, 06)

ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABAQDKq75NoFSBvAEp9kKMbBowW9cLi0e2cSiF8CmaYYa8Xxirm/x+ ... your_gmail@gmail.com

~/OneDrive/Desktop
$ cp -r gce2windows ~/.ssh/

~/OneDrive/Desktop
$ cd ~/.ssh

~/OneDrive/Desktop
$ ls
Q-tutor.pem  gce2windows  id_rsa.pub   known_hosts.old
config       id_rsa.ppk   known_hosts  openssh

Host gce2windows
    HostName 34.22.86.213 # 외부 IP 각자 다름
    Port 22
    User yourname
    IdentityFile ~/.ssh/gce2windows

curl -LsSf https://astral.sh/uv/install.sh | sh
source $HOME/.local/bin/env 

streamlit
jupyterlab

uv pip install -r requirements.txt

import streamlit as st

st.title("Hello GCP-Streamlit")

jupyter lab --ip=0.0.0.0 --port=8888 --allow-root

http://127.0.0.1:8888/lab?token=297d002ad3c3a76b55db4ed10856609201bd12631ef5efbc

SELECT
  name,
  gender,
  SUM(number) as total
FROM
  `bigquery-public-data.usa_names.usa_1910_2013`
WHERE
  state = 'TX'
GROUP BY
  name, gender
ORDER BY
  total DESC
LIMIT 10;

SELECT * FROM `lgu6th.sample_data.boston`;

streamlit
jupyterlab
db-dtypes
google-cloud-bigquery
pandas
matplotlib

import streamlit as st
import pandas as pd
import numpy as np
from google.cloud import bigquery
import matplotlib.pyplot as plt

client = bigquery.Client()
source_table = "lgu6th.sample_data.boston"
transformed_table = "lgu6th.sample_data.transformed_boston"

st.set_page_config(layout="wide")
st.title("Boston Housing Data - BigQuery 연동 및 표준화 처리")

tab1, tab2, tab3 = st.tabs(["데이터 불러오기", "데이터 변환 및 업로드", "📊 시각화"])

# 데이터 불러오기 탭
with tab1:
    st.subheader("1. 원본 데이터 불러오기")
    if st.button("데이터 불러오기 (BigQuery)"):
        query = f"SELECT * FROM `{source_table}` LIMIT 1000"
        df = client.query(query).to_dataframe()
        st.session_state["original_df"] = df
        st.success("데이터 불러오기 완료")
    
    if "original_df" in st.session_state:
        st.dataframe(st.session_state["original_df"])

# 데이터 변환 탭
with tab2:
    st.subheader("2. 데이터 변환 및 BigQuery 업로드")

    if "original_df" not in st.session_state:
        st.warning("먼저 [데이터 불러오기] 탭에서 데이터를 불러오세요.")
    else:
        if st.button("데이터 표준화 + 로그변환 후 업로드"):
            df = st.session_state["original_df"].copy()
            
            # 로그 변환
            df["LOG_CRIM"] = df["CRIM"].apply(lambda x: np.nan if x <= 0 else round(np.log(x), 4))
            
            # 표준화 (평균 0, 표준편차 1)
            numeric_cols = df.select_dtypes(include=[np.number]).columns.tolist()
            for col in numeric_cols:
                mean = df[col].mean()
                std = df[col].std()
                if std > 0:
                    df[f"{col}_z"] = (df[col] - mean) / std

            st.session_state["transformed_df"] = df  # 변환된 데이터 저장

            # 업로드
            job_config = bigquery.LoadJobConfig(
                write_disposition="WRITE_TRUNCATE",
                autodetect=True,
            )
            job = client.load_table_from_dataframe(df, transformed_table, job_config=job_config)
            job.result()
            st.success("변환 및 업로드 완료")
            st.dataframe(df)

        elif "transformed_df" in st.session_state:
            st.info("이전 변환 결과:")
            st.dataframe(st.session_state["transformed_df"])

# 시각화 탭
with tab3:
    st.subheader("3. 변환된 데이터 시각화 (BigQuery에서 불러오기)")
    
    if st.button("변환된 데이터 시각화"):
        try:
            query = f"SELECT * FROM `{transformed_table}`"
            df_trans = client.query(query).to_dataframe()
            st.session_state["visual_df"] = df_trans  # 시각화용 저장
            st.success("BigQuery에서 불러오기 완료")
        except Exception as e:
            st.error(f"불러오기 실패: {e}")

    if "visual_df" in st.session_state:
        df_trans = st.session_state["visual_df"]
        st.write("데이터 미리보기:")
        st.dataframe(df_trans.head())

        # 시각화
        if "CRIM" in df_trans.columns and "LOG_CRIM" in df_trans.columns:
            fig, ax = plt.subplots()
            ax.hist(df_trans["CRIM"].dropna(), bins=30, alpha=0.5, label="CRIM")
            ax.hist(df_trans["LOG_CRIM"].dropna(), bins=30, alpha=0.5, label="LOG_CRIM")
            ax.set_title("CRIM vs LOG_CRIM Histogram")
            ax.legend()
            st.pyplot(fig)