Go to Qiita Advent Calendar 2024 Top
LoginSignup
3
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

TiDBAdvent Calendar 2024

Day 3
@bohnen

DuckDBを使ってS3とTiDBの間でデータを移動する

Posted at

はじめに

S3を使っていると、parquet形式のファイルをしばしば使います（Aurora Snapshotとか)。このデータをTiDBに入れたり、逆にTiDBのデータをS3にエクスポートしたいというニーズはまぁある（個人の感想です）のですが、そのためにETLジョブ組むのも面倒ですよね？

DuckDBはまさにそういう作業にうってつけです。DuckDBはparquetファイルの扱いにとても優れており、更にs3を簡単に、個人的にはglueやAthenaより遥かに容易に扱えます。
更に、MySQLにも接続でき、それらの間のデータ移動もできます。

本記事ではDuckDBを使ってS3、TiDBそれぞれに接続してみて、更にTiDBからS3、S3からTiDB間のデータの受け渡しを行います。環境はMacOSです。

DuckDBのインストール

いくつか方法はあると思いますが、Homebrewでインストールしました。

> brew install duckdb
> duckdb --version
v1.1.3 19864453f7

DuckDBからS3への接続

DuckDBはS3を標準でサポートしており、認証方式も非常に多彩です。特に、SSO(AWS IAM Identity Center)をサポートしているのが便利です。ここではSSOを使った認証を行っていきます。

  1. はじめにBucketを作って適当なparquetのファイルを置きます。この記事では TimeStored のtitanic.parquetを使いました。

  2. 次にaws cliを使ってssoにログインしておきます。

> aws sso login 
 # [ブラウザでログインして許可しておく]
  1. duckDBを起動して、s3のシークレットを作成します。
CREATE SECRET secret_s3 (
    TYPE S3,
    PROVIDER CREDENTIAL_CHAIN,
    CHAIN 'sso'
);
┌─────────┐
│ Success │
│ boolean │
├─────────┤
│ true    │
└─────────┘
  1. SQLでクエリします。parquetの読み取りはread_parquet関数を使う必要がありますが、ファイルパスの末尾に.parquetがある場合は勝手に使ってくれます。すごく便利です。
select * from 's3://seki-duckdb/titanic.parquet';
┌─────────────┬──────────┬────────┬───────────────────────────┬─────────┬────────┬───────┬───────┬──────────────────┬─────────┬─────────────┬──────────┐
│ PassengerId │ Survived │ Pclass │           Name            │   Sex   │  Age   │ SibSp │ Parch │      Ticket      │  Fare   │    Cabin    │ Embarked │
│    int64    │  int64   │ int64  │          varchar          │ varchar │ double │ int64 │ int64 │     varchar      │ double  │   varchar   │ varchar  │
├─────────────┼──────────┼────────┼───────────────────────────┼─────────┼────────┼───────┼───────┼──────────────────┼─────────┼─────────────┼──────────┤
│           1 │        0 │      3 │ Braund, Mr. Owen Harris   │ male    │   22.0 │     1 │     0 │ A/5 21171        │    7.25 │             │ S        │
│           2 │        1 │      1 │ Cumings, Mrs. John Brad…  │ female  │   38.0 │     1 │     0 │ PC 17599         │ 71.2833 │ C85         │ C        │
│           3 │        1 │      3 │ Heikkinen, Miss. Laina    │ female  │   26.0 │     0 │     0 │ STON/O2. 3101282 │   7.925 │             │ S        │
│           4 │        1 │      1 │ Futrelle, Mrs. Jacques …  │ female  │   35.0 │     1 │     0 │ 113803           │    53.1 │ C123        │ S        │
│           5 │        0 │      3 │ Allen, Mr. William Henry  │ male    │   35.0 │     0 │     0 │ 373450           │    8.05 │             │ S        │
...

これだけです。めちゃくちゃ簡単じゃないですか？

DuckDBからTiDB(MySQL)への接続

TiDB Cloud Serverlessの準備

サーバレスクラスタと適当なデータを用意しましょう。

  1. 適当なServerlessクラスタを作成します
  2. ヘルプ（右下の?マーク) から Guided Tour of SQL Editor を選択して、githubのデータをimportします。数分かかると思います
    スクリーンショット 2024-12-04 10.46.49.png
  3. インポートが完了したら閉じて構いません
  4. 接続情報を取得します。Overview画面の右上から、Connect ボタンを押して接続画面を開きます。この情報をDuckDBから接続する際に利用します
    screenshot-20241204-115640.png

DuckDBからTiDBへの接続

DuckDBのMySQL extentionを使います。といっても拡張機能をINSTALL, LOADで呼び出すだけで、別にインストールが必要なわけではありません。

拡張機能を呼び出し、先程の接続情報を元にシークレットを作成します。

INSTALL mysql;
LOAD mysql;

CREATE SECRET  (
    TYPE MYSQL,
    HOST 'gateway01.ap-northeast-1.prod.aws.tidbcloud.com',
    PORT 4000,
    DATABASE github_sample,
    USER '<Serverlessのユーザー名>',
    PASSWORD '<Serverlessのパスワード>',
    SSL_MODE 'verify_identity',
    SSL_CA '/etc/ssl/cert.pem'
);

MySQLのデータベースをアタッチして、DuckDBのデータベースのように利用できます。

ATTACH '' AS github_sample (TYPE MYSQL);
select * from github_sample.github_repos limit 1;
┌─────────┬──────────────────────┬──────────────────┬───┬──────────────────────┬──────────────────────┬─────────────────────┬─────────────────────┐
│ repo_id │      repo_name       │ primary_language │ … │     description      │      embedding       │     created_at      │     updated_at      │
│  int32  │       varchar        │     varchar      │   │       varchar        │       varchar        │      timestamp      │      timestamp      │
├─────────┼──────────────────────┼──────────────────┼───┼──────────────────────┼──────────────────────┼─────────────────────┼─────────────────────┤
│    1227 │ mislav/will_paginate │ Ruby             │ … │ Pagination library…  │ [-0.022141477,0.01…  │ 2008-02-25 20:21:40 │ 2024-06-10 09:07:56 │
├─────────┴──────────────────────┴──────────────────┴───┴──────────────────────┴──────────────────────┴─────────────────────┴─────────────────────┤
│ 1 rows                                                                                                                     11 columns (7 shown) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

S3(Parquet) ⇔ TiDB

さて以上でDuckDBでS3、TiDBそれぞれを扱えるようになりました。DuckDBがすごいのはここからで、これらのデータベース間でデータのやり取りができます。

たとえば、TiDBから読み出したデータをparquet形式でS3に保存するにはCOPYを使います。

-- TiDBからS3に保存
COPY github_sample.github_repos TO 's3://seki-duckdb/github_repos.parquet';
100% ▕████████████████████████████████████████████████████████████▏

-- 保存されたファイルをクエリ
select * from read_parquet('s3://seki-duckdb/github_repos.parquet') limit 1;

┌─────────┬──────────────────────┬──────────────────┬───┬──────────────────────┬──────────────────────┬─────────────────────┬─────────────────────┐
│ repo_id │      repo_name       │ primary_language │ … │     description      │      embedding       │     created_at      │     updated_at      │
│  int32  │       varchar        │     varchar      │   │       varchar        │       varchar        │      timestamp      │      timestamp      │
├─────────┼──────────────────────┼──────────────────┼───┼──────────────────────┼──────────────────────┼─────────────────────┼─────────────────────┤
│    1227 │ mislav/will_paginate │ Ruby             │ … │ Pagination library…  │ [-0.022141477,0.01…  │ 2008-02-25 20:21:40 │ 2024-06-10 09:07:56 │
├─────────┴──────────────────────┴──────────────────┴───┴──────────────────────┴──────────────────────┴─────────────────────┴─────────────────────┤
│ 1 rows                                                                                                                     11 columns (7 shown) │
└─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

parquetファイルにはワイルドカードが使えるので、日次でエクスポートしてまとめて分析みたいなことも可能です。


select count(*) from read_parquet('s3://seki-duckdb/github_repos.parquet') ;
┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│         5607 │
└──────────────┘

-- 例として、同じデータを別名で保存してみる
COPY github_sample.github_repos TO 's3://seki-duckdb/github_repos2.parquet';
100% ▕████████████████████████████████████████████████████████████▏

-- ワイルドカードを使って複数のparquetファイルをまとめて分析する
select count(*) from read_parquet('s3://seki-duckdb/github_repos*.parquet');

┌──────────────┐
│ count_star() │
│    int64     │
├──────────────┤
│        11214 │
└──────────────┘

逆にparquetのデータをTiDBに保存してみましょう。parquetはテーブル定義を持っているので、CREATE TABLE ... AS SELECT ... を使って、テーブルを定義することなしにTiDB側にImportできるのです。

-- s3からTiDBにテーブルを作成する
CREATE TABLE github_sample.titanic as
    select * from read_parquet('s3://seki-duckdb/titanic.parquet');

-- 作成されたテーブルをクエリ
select * from github_sample.titanic limit 1;
┌─────────────┬──────────┬────────┬─────────────────────────┬─────────┬────────┬───────┬───────┬───────────┬────────┬─────────┬──────────┐
│ PassengerId │ Survived │ Pclass │          Name           │   Sex   │  Age   │ SibSp │ Parch │  Ticket   │  Fare  │  Cabin  │ Embarked │
│    int64    │  int64   │ int64  │         varchar         │ varchar │ double │ int64 │ int64 │  varchar  │ double │ varchar │ varchar  │
├─────────────┼──────────┼────────┼─────────────────────────┼─────────┼────────┼───────┼───────┼───────────┼────────┼─────────┼──────────┤
│           1 │        0 │      3 │ Braund, Mr. Owen Harris │ male    │   22.0 │     1 │     0 │ A/5 21171 │   7.25 │         │ S        │
└─────────────┴──────────┴────────┴─────────────────────────┴─────────┴────────┴───────┴───────┴───────────┴────────┴─────────┴──────────┘

さいごに

DuckDBはparquetの他にもCSVやJSONにも対応し、またPostgreSQLやsqliteなど多くのDBにも対応しています。それらのデータベース間で今回の記事のようにデータを移動したり加工したりすることができます。非常に軽量ですが便利なデータベースなので活用してみてください！

3
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
3
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?