バイセル Tech Blog

バイセル Tech Blogは株式会社BuySell Technologiesのエンジニア達が知見・発見を共有する技術ブログです。

バイセル Tech Blog

Hasura CLIで挿入するマスタデータをスプレッドシート管理に移行させたので振り返ってみた

はじめに

テクノロジー戦略本部の尾沼です。

私が現在携わっているプロジェクトでは、HasuraとGo言語を使ってバックエンド部分を開発しています。マイグレーションやデータのCRUDは全てHasura経由でPostgreSQLに対して実行しており、マスタデータのUPSERTもHasuraを経由して実施しています。

この度、マスタデータの管理方法をスプレッドシートに移行させたのでその際に行ったことを紹介したいと思います。

課題

従来のマスタデータ管理方法では以下のような問題がありました。

  • カラム数が多いレコードを、手動でデータ編集をするのに非常に手間がかかるようになっていた。

  • エンジニアしかマスタデータを読んだり更新したりできない状況であった。

  • レコード数やデータ量が多い場合、それらを追加するSQL上で値がどのカラムに属するかを判別することが難しかった。

スプレッドシートで管理する方式にすることで以下のようなメリットを享受し、上記を解決できると考え、改修を行いました。

  • スプレッドシート関数を利用してデータの追加/加工が行える。

  • 非エンジニアメンバー含め、複数人で同時に作業ができる。

  • レコード数やデータ量が多くなっても値がどのカラムに属するかを容易に判別できる。

改修概要

前提として、弊プロジェクトではhasura cliを用いてSQLファイルをHasura経由でPostgreSQLに対して実行しています。

今回の改修によってマスタデータを追加/更新する際の方法が以下のように変わりました。

Before

  • 開発者が直接SQLファイルを作成/更新する

    • バージョン管理はSQLをGit管理することで実現する

before

After

  • スプレッドシートで追加更新を行い、アプリケーション側に用意したコマンドで取り込んで反映するだけにする。

    • 同時編集が可能

    • エンジニアと非エンジニアが編集可能

    • バージョン管理はCSVをGit管理することで実現する

      • Github上でCSVのほうがSQLよりも見やすいため

after

工夫した点

今回特に工夫した点を紹介したいと思います。

  • スプレッドシートで細かい設定をできるようにした

  • ユーザーアカウントでスプレッドシートへのアクセスを認証するようにした

  • シートの情報からCSVファイルを生成するようにした

  • CSVファイルからUpsertのSQLファイルを出力するようにした

以下のような構造のmaster_usersテーブルにデータを投入する例を通じて紹介します。 master_users

本記事では説明の都合上、コード中省略している箇所がございます。動作するサンプルコードは以下にアップロードしています。

github.com

スプレッドシートで細かい設定をできるようにした

スプレッドシートを用意します。工夫した点は以下です。

  • シート名をテーブル名にすることで、どのシートがどのテーブルに対するものかを判別できるようにしました。

  • 2行目に各カラムの説明を入れられるようにしました。

  • 最終的にSQLに変換するにあたって、型を自由に設定できるようにしたいため3行目に各カラムの型を設定できるようにしました。

    • 今回の例ではinteger, boolean, stringに対応
  • UpsertのSQLにてON CONFLICT句の条件にプライマリキーを指定しています。そのため、どのカラムがプライマリキーか判るように4行目に設定できるようにしました。

  • SQLに含ませたいデータを指定できるようにしました。

    • 含ませたいデータ → 1列目をTrue

    • 含ませたくないデータ → 1列目をFalse

sheet

最終的には上のスプレッドシートから以下のようなSQLが出力されます。

sql

ユーザーアカウントでスプレッドシートへのアクセスを認証するようにした

サービスアカウントではなくユーザーアカウントで認証することで、権限の管理をユーザー単位で行えるようにしました。

まず、Goの処理の中でスプレッドシートにアクセスしてデータを取得するために、GoのSheets APIクライアントを利用します。

事前準備として、GCPのプロジェクトを作成してGoogle Sheets APIを有効にしておきます。 sheet_api

今回はOAuth2.0クライアント認証でGCPにアクセスします。クライアントIDを作成し、JSONのキーをダウンロードします。

oauth_download

以下のようなJSONキーがダウンロードできると思うので、Goプロジェクト内に配置しておきます。

{
    "installed":
        {
            "client_id":"xxxxxxxxxxxxxxxxxx",
            "project_id":"xxxxxxxxxxxxxxxxxxx",
            "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_secret":"xxxxxxxxxxxxx",
            "redirect_uris":["urn:ietf:wg:oauth:2.0:oob","http://localhost"]
        }
}

続いて、スプレッドシートへアクセスするために、ユーザー権限で認証を行うhttp clientを作成します。

まず、以下のようにhttp clientのコンストラクタを作成します。OAuth2.0クライアントの認証キーから接続先GCPプロジェクトの情報を読み込み、スプレッドシートのRead権限の利用が許可されるように設定します。

pkg.go.dev

import (
    "context"
    "net/http"
    "os"
    
    "golang.org/x/oauth2/google"
)

func NewClient(ctx context.Context) (*http.Client, error) {
    // gcpからダウンロードしたJSON keyのパスを指定する
    const gcsAuthJSONFilePath = "/go/src/google/gcpClientSecret.json"
    b, err := os.ReadFile(gcsAuthJSONFilePath)
    if err != nil {
        return nil, err
    }
    config, err := google.ConfigFromJSON(b, "https://www.googleapis.com/auth/spreadsheets.readonly")
    if err != nil {
        return nil, err
    }

    token, err := getTokenFromWeb(config) // 後で定義します
    if err != nil {
        return nil, err
    }

    client := config.Client(context.Background(), token)
    if err != nil {
        return nil, err
    }
    return client, nil
}

続いて、認証コードを標準入力で受け取ってトークンに変換して返す getTokenFromWeb 関数を定義します。 以下記事を参考にしました。

developers.google.com

pkg.go.dev

import (
    // 省略
    "golang.org/x/oauth2" // 追加
)

// 省略

func getTokenFromWeb(config *oauth2.Config) (*oauth2.Token, error) {
    authURL := config.AuthCodeURL("state-token", oauth2.AccessTypeOffline)
    fmt.Printf("\nブラウザで以下のURLを開いてauthentication codeを取得してください: \n\n%v\n\n", authURL)
    var authCode string
    fmt.Print("authorization code: ")
    if _, err := fmt.Scan(&authCode); err != nil {
        return nil, err
    }

    token, err := config.Exchange(context.TODO(), authCode)
    if err != nil {
        return nil, err
    }
    return token, nil
}

シートの情報からCSVファイルを生成するようにした

シートの情報をCSVとしてアウトプットします。

シートのデータの中にあるメタデータを元に、CSVに出力する情報の制御や変換を行うカスタムロジックを挟みます。

アウトプットするCSVは、サンプルでは以下のような形になる想定です。

  • 1列目はカラム名

  • 2列目はプライマリキーであるかどうか

  • 3列目以降は各レコード(値はスプレッドシートで指定したカラム型に合わせて変換されている)

csv

まず、CSVの値を出力するgenerateCSVValue関数を定義します。スプレッドシートの3行目に定義したカラムタイプで条件分岐をし、初期値などを設定できるようにしました。値が入っていた場合は初期値ではなくその値を返すようにしました。

import (
    // 省略
    "strconv" // 追加
)

func generateCSVValue(dataType string, v string) string {
    var result string
    switch dataType {
    case "integer":
        num, err := strconv.Atoi(v)
        if err != nil || v == "" {
            // integerにできない値がvalueに入っていたら0で返す
            result = "{{ 0 }}"
        } else {
            result = fmt.Sprintf("{{ %v }}", num)
        }

    case "string":
        if v == "" {
            result = "{{ '' }}"
        } else {
            result = fmt.Sprintf("{{ '%s' }}", v)
        }

    case "boolean":
        val, err := strconv.ParseBool(v)
        if err != nil || v == "" {
            // 変換できなかった時はfalseにする
            result = "{{ false }}"
        } else {
            result = fmt.Sprintf("{{ %v }}", val)
        }
    }
    return result
}

続いて、Goのsheetsパッケージが提供するSheet 構造体を受け取ってCSVを書き出すSheetToCSV関数を定義します。先ほど定義したgenerateCSVValueを利用します。

pkg.go.dev

この処理の中で、出力する行の取捨やCSVに書き込む値の生成なども行っています。

import (
    // 省略
    "errors" // 追加
)

func SheetToCSV(s *gsheets.Sheet, csvOutputDir string) error {
    fileName := s.Properties.Title // シート名 = ファイル名

    // headerの名前を取得
    var columnNames []string
    const headerRowIndex = 0
    for i, value := range s.Data[0].RowData[headerRowIndex].Values {
        if i == 0 {
            continue
        }
        columnNames = append(columnNames, value.FormattedValue)
    }
    records = append(records, columnNames)

    // column型を取得
    var columnTypes []string
    const columnTypeRowIndex = 2
    for i, columnType := range s.Data[0].RowData[columnTypeRowIndex].Values {
        if i == 0 {
            continue
        }
        columnTypes = append(columnTypes, columnType.FormattedValue)
    }
    if len(columnNames) != len(columnTypes) {
        return errors.New("ヘッダーかカラムタイプの長さが一致しません")
    }

    // pkの設定
    var pks []string
    const pkRowIndex = 3
    for i, columnType := range s.Data[0].RowData[pkRowIndex].Values {
        if i == 0 {
            continue
        }
        pks = append(pks, columnType.FormattedValue)
    }
    records = append(records, pks)

    // レコードの差し替え

    // レコードが入っているのは4行目以降
    const dataBeginRowIndex = 4
    for _, row := range s.Data[0].RowData[dataBeginRowIndex:] {
        if len(row.Values) == 0 {
            continue
        }

        const dataBeginColumnIndex = 1
        // 先頭がfalseの行はCSV出力しない
        if len(row.Values) > 0 && row.Values[dataBeginColumnIndex-1].FormattedValue != "TRUE" {
            continue
        }
        var record []string

        values := row.Values[dataBeginColumnIndex:]

        for i, value := range values {
            // 改行あるとCSVが壊れるので余計な改行コードを削除する(任意に文字列データに改行入れたいときはエスケープ文字構文に対応させる)
            rowValue := strings.Replace(value.FormattedValue, "\n", "", -1)
            v := generateCSVValue(columnTypes[i], rowValue)
            // {{ hoge }} のマスタッシュ部分だけ削除
            v = strings.TrimPrefix(v, "{{ ")
            v = strings.TrimSuffix(v, " }}")
            record = append(record, v)
        }
        records = append(records, record)
    }

    fileFullPath := fmt.Sprintf("%s%s", csvOutputDir, fileName)
    if err := writeCSV(fileFullPath, records); err != nil {
        return err
    }
    return nil
}

CSVファイルからUpsertのSQLファイルを出力するようにした

UpsertのSQLの生成はGo標準テンプレートエンジンのtext/template を用いて行うようにしました。

text/templateは文字列の雛形に餌となるデータを渡して新しい文字列を生成することができます。

pkg.go.dev

これを利用してCSVからUpsertのSQLファイルを作成する実装をします。

まずUPSERTのSQLの雛形を用意します。TableNameやColumnNamesは構造体のフィールド名になります。

INSERT INTO "{{ .TableName }}" ({{ .ColumnNames }})
VALUES
{{ .Values }}
ON CONFLICT ({{ .Pk }})
DO UPDATE SET
{{ .UpdateSetting }}
;

続いてUPSERTのSQLの置換に使うUpsertContentDataという構造体とコンストラクタを定義します。

type UpsertContentData struct {
    TableName        string
    ColumnNames      string
    Values           string
    Pk               string
    UpdateSetting    string
    contentsTemplate string
}

func NewUpsertContentData() *UpsertContentData {
    // upsert句のテンプレ
    contentsTemplate := `INSERT INTO "{{ .TableName }}" ({{ .ColumnNames }})
VALUES
{{ .Values }}
ON CONFLICT ({{ .Pk }})
DO UPDATE SET
{{ .UpdateSetting }}
;
`
    return &UpsertContentData{
        contentsTemplate: contentsTemplate,
    }
}

UpsertContentData構造体の各フィールドにCSVから得たデータを入れていく処理をRegisterContentMapメソッドとして書きます。

import (
    // 省略
    "fmt" // 追加
)

func (c *UpsertContentData) RegisterContentMap(tableName string, records [][]string) {
    // ヘッダーの置き換え  1行目はヘッダーのはず
    columnNames := strings.Join(records[0], ",")
    var values []string
    for i, record := range records {
        // ヘッダーはスキップ
        if i < 2 {
            continue
        }
        var texts []string
        for _, v := range record {
            if v == "" {
                texts = append(texts, "null")
            } else {
                texts = append(texts, v)
            }

        }
        var splitToken string
        if i == len(records)-1 {
            splitToken = ""
        } else {
            splitToken = ","
        }
        values = append(values, fmt.Sprintf("  (%s)%s", strings.Join(texts, ","), splitToken))
    }

    var updateSetting []string
    for i, v := range records[0] {
        var splitToken string
        if i == len(records[0])-1 {
            splitToken = ""
        } else {
            splitToken = ","
        }
        updateSetting = append(updateSetting, fmt.Sprintf("  %q = EXCLUDED.%s%s", v, v, splitToken))
    }

    var pkColumns []string

    for i, columnName := range records[0] {
        if records[2][i] == "TRUE" && columnName != "" {
            pkColumns = append(pkColumns, columnName)
        }
    }
    c.TableName = tableName
    c.ColumnNames = columnNames
    c.Values = strings.Join(values, "\n")
    c.Pk = strings.Join(pkColumns, ",")
    c.UpdateSetting = strings.Join(updateSetting, "\n")
}

以下のように、CSVから読み取ってきたデータを置換してファイルに書き込むことで、SQLファイルを生成することができます。

import (
     // 省略
    "bytes"
    "text/template"
)

func CSVToUpsertSQL(csvPath string, outputDir string) error {
    records, err := readCSV(csvPath)
    if err != nil {
            return err
    }
    
    // 省略
    
    data.RegisterContentMap(tableName, records)
    
    tmpl, err := template.New("tmpl").Parse(data.contentsTemplate)
    if err != nil {
            return err
    }
    
    var doc bytes.Buffer
    if err = tmpl.Execute(&doc, data); err != nil {  // 置換を実行
            return err
    }
    
    str := doc.String()
    if _, err := fmt.Fprintf(f, "%v", str); err != nil { 
            return err
    }
    return nil
}

まとめ

弊プロジェクトの全てのマスタデータ管理をスプレッドシートに移行させたことで、当初の想定メリットであった、

  • スプレッドシート関数を利用してデータの追加/加工が行える。

  • 非エンジニアメンバー含め、複数人で同時に作業ができる。

  • レコード数やデータ量が多くなっても値がどのカラムに属するかを容易に判別できる。

これらに関してかなり恩恵を享受できるようになりました。

どうしてもカラム数やレコード数が増えると、SQLを目視で追ったり改修することにミスが生じやすくなってしまうので、見やすいスプレッドシートだけを編集してあとはコマンドを叩くだけにした今回の改修は実施して良かったなと感じています。

最後に、Buysell Technologiesではエンジニアを募集しています。興味がある方はぜひご応募ください!

herp.careers