月: 2024年12月

タイトルはMySQLですが、実際には AWS RDSのAurora (MySQL)を想定しています。

Aurora Serverless v2 への移行も見据えて、MySQLのデータの移行方法を調べました。dumpをとる専用のコマンドがあり、それでバックアップを取得してそれを戻せば良いようです。

ドキュメントはこちらになります。
参考: MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

ドキュメントの一番下の方に例として一番シンプルなコマンドが載っています。

# データベース全体のバックアップを作成
shell> mysqldump db_name > backup-file.sql
# ダンプファイルをサーバーにロード
shell> mysql db_name < backup-file.sql

ただし、これはそのローカルサーバーにDBMSがあってパスワード等もかかってない場合に使うものなので、現実的にはDBのエンドポイント等を指定する必要があります。

# バックアップ
$ mysqldump -h <エンドポイント> -u <ユーザー名> -p --databases <データベース名> > aurora_backup.sql
# リストア
$ mysql -h <エンドポイント> -u <ユーザー名> -p < aurora_backup.sql

ダンプするときにデータベース名はスペース区切りで複数まとめて指定することもできます。

一方で、ダンプファイルにDB名の情報は含まれているので、リストアする時はDB名の指定は不要です。

–all-databases という全てのデータベースを対象とするオプションもありますが、システム情報的なDBまで含まれてしまうのでこれは使わず、自分で必要なDBを指定した方が安全だと思います。バージョンが違うDB間で移行するような時は特に注意が必要です。

最後に、ダンプを取るときに特にオプション等でタイムゾーンに関する設定をしなければ、datatime型のデータはタイムゾーンの情報を持たずにダンプされてしまいます。

移行前後のDB間でタイムゾーンが違うと異なる時間で解釈されるリスク等もあるので、先に設定を揃えておきましょう。

今年から使いはじめた Streamlit in Snowflake についての記事です。

とはいえ、Sreamlit要素はほぼなく、Snowflake上で使うからこそ発生するSQLの結果の取得方法(=pandasのDataFrameとしての取得方法)をまとめておきます。

Streamlit in Snowflake の場合、Snowflkaeにログインして使いますので認証情報として現時点でログインしているセッションの情報が使えます。それを取得する専用の関数として、get_active_session があるので、これを呼び出すのが最初の準備です。

from snowflake.snowpark.context import get_active_session


# Snowflakeセッションの取得
session = get_active_session()

ここから下は 「query」という変数にSQL(SELECT)文が格納されている前提になります。

sessionの sql()というメソッドでSQLを発行し、結果を得ることができます。さらに、その結果は to_pandas() というメソッドを持っており、これを使うことでDataFrame型に変換できます。

結果を表示したい場合は streamlitの dataframeとかtableといったメソッドが使えますね。

# クエリの実行と結果の取得
result = session.sql(query).to_pandas()
st.dataframe(result)
st.table(result)

続いて、 SELECT文ではなく show columns や describe table のケースも紹介します。

こちらは sqlメソッドで発行できるのは同じなのですが、結果が to_padnas()メソッドを持っていません。

ここでは、collect() というメソッドを使います。

result = session.sql(query).collect()
st.dataframe(result)

簡潔ですが以上で Streamlit in SnowflakeでSQLを発行し結果をStreamlit内で使えるようになります。

要するに第1正規化ができてないデータがあるときにそれを複数行に展開する方法です。

a,b,c みたいな文字列がデータにあるときにa, b, c をそれぞれ別行のデータに展開します。

これには lateral flatten という構文を使います。参考になるドキュメントはこちらです。
参考: FLATTEN | Snowflake Documentation

元々のデータが配列型の場合は split が不要になるのでその分を読み替えてください。
最初のwith句でダミーデータを生成して実験しています。

from 句で指定してるテーブルの後、joinで複数テーブルを結合している場合はそれらの一番最後にカンマを打ってから使う点に注意してください。また、 inputとか values とかも決まった単語ですのでそのまま使います。

with
dummy_data as (
    select
        1 as id,
        'a,b,c' as text
    union all select
        2 as id,
        None as text
    union all select
        3 as id,
        'd' as text
)

select
    id,
    value::varchar as text
from
    dummy_data, lateral flatten(input => split(text, ','))

これで、
id, text
1, a
1, b
1, c
3, d
という結果が得られます。

さて、id = 2のレコードについてはtextの値がNullだったので結果に出てきませんでした。
これがNullではなく空白文字列であれば、id=3のレコードと同様に行は作られたのですけどね。

このように展開する値がNullのレコードも残したい場合は、 outerオプションを使います。= でではなく => で trueに指定するSQLでは見慣れない記法ですが、次のような形になります。

with
dummy_data as (
    select
        1 as id,
        'a,b,c' as text
    union all select
        2 as id,
        Null as text
    union all select
        3 as id,
        'd' as text
)

select
    id,
    value::varchar as text
from
    dummy_data, lateral flatten(input => split(text, ','), outer => true)

そもそも正規化がちゃんと行われているデータだけ使えばいいのであれば滅多に使わない記法なのですが、残念ながら自分はよく使う場面が頻繁にあることと、その割になかなか覚えられなくて毎回調べているので今回記事にしました。