GoとSQL Server

この記事は、[一休.comアドベントカレンダー2017]の7日目です。

qiita.com

こんにちは、データサイエンス部・大西 id:ohke です。
ユーザの行動収集基盤や、マーケティング施策の実行を支援するシステムの開発・メンテナンスを担当しています。

7日目の本投稿では、GoでSQL Serverを使う方法について、紹介したいと思います。

なぜGoとSQL Serverなのか

メジャーじゃない組み合わせだと思いますので、なぜGoからSQL Serverを使うことになったのか、背景を補足します。

今年に入り、一休ではデータウェアハウス基盤をクラウド環境に構築しました。 この基盤では、リアルタイムな行動ログを含む、マーケティングに必要なデータを全てSQL Sever(Amazon RDS)に集約しています。

この基盤を使った施策の一貫として、ユーザのリアルタイムな行動を分析し、今一休に訪れているユーザへ1 to 1マーケティングを検討しています。 具体的には、サイトやアプリでのメッセージの通知などです。

ブラウザ、フロントエンドサーバ、アプリなど、様々なアプリケーションからアクセスされるため、Web APIで共通したインタフェースを提供するのがセオリーですが、今いるユーザを対象とした施策においてはPV数に比例したアクセスが予想されます。 こうした高負荷に耐えるための環境として、行動ログの収集でも実績があるGoをAPIサーバとして構えることになりました。
行動ログ収集の取り組みについて興味のある方は、こちらもご覧ください(ちなみに、当時はSQL Serverではなく、Azure SQL Data Warehouseをデータウェアハウスとして採用してました。変更された経緯については、 12/20投稿予定の「データ分析基盤、その後 id:sisijumi 」で触れます)。

2017-08-17-_DataAnalyticsPlatform.pdf // Speaker Deck

また、可能な限りリアルタイムな行動ログを使う(目標タイムラグは1分以内)ため、データマートなどを介在させずに、Goで動くAPIサーバからSQL Serverに直接アクセスする必要があり、今回の調査に至りました。

生のSQLを実行する

Goでは、database/sqlという標準ライブラリがSQL(-like)なインタフェースを提供しています。 DB製品ごとのドライバと組み合わせることで、DBへ直接SQLを実行できるようになります。

SQL Serverのドライバとして、go-mssqldbとgofreetdsの2つがあります。

• go-mssqldbはGo単体で実装されていますが、gofreetdsはcgo(GoからCのコードをコンパイルしたり、CのライブラリをリンクできるようになるGoのビルド機能)を使ってます
• メジャーなのは、go-mssqldbのようです(GitHubスター比較)

今回はgo-mssqldbについて解説していきます。

https://github.com/denisenkom/go-mssqldb

go-mssqldb

パッケージをダウンロードしておきます。

> go get github.com/denisenkom/go-mssqldb

まずは接続です。ポイントは3点です。

• go-mssqldbをブランクインポート(_)して、SQL Serverのドライバで初期化します
  • これでdatabase/sqlのインタフェースでSQL Serverにアクセスできるようになります
• 接続文字列は3パターンで記述できますが、パスワードに;(セミコロン)を含む場合は2番目か3番目を選択する必要があります
  • server=testdb.ikyu.jp;user id=ohke;password=p@ssw0rd;database=TestDB
  • odbc:server=testdb.ikyu.jp;user id=ohke;password=p@ssw0rd;database=TestDB
    • パスワードに;を含む場合は、password={p@ss;word}のように{}で括る
  • sqlserver://ohke:p@ssw0rd@testdb.ikyu.jp?database=TestDB
    • パスワードに;を含む場合は、p@ss%3Bw0rdのようにURLエンコードする
• sql.Open()の第1引数に"sqlserver"または"mssql"を指定して接続します
  • 2つは基本的に同じですが、クエリパラメータの渡し方に違いがあります(後述)

package main

import (
    "database/sql"
    
    _ "github.com/denisenkom/go-mssqldb"
)

func main() {
    connectionString := "sqlserver://ohke:p@ssw0rd@testdb.ikyu.jp?database=TestDB"

    // 接続
    // "sqlserver"の代わりに"mssql"でもOK
    connection, err := sql.Open("sqlserver", connectionString)
    if err != nil {
        return nil, err
    }
    
    // 切断
    defer connection.Close()

    // CRUD処理を記述
    // ...
}

接続すれば、database/sqlのインタフェースに則って、SQLを実行できます。

まずはselectです。

• 1行のselectならQueryRow、複数行のselectならQueryと使い分けます
• ドライバに"sqlserver"を指定した場合、@で始まるパラメータ名をSQLに埋め込み、sql.NamedArg構造体でパラメータに渡す値を設定します
  • "mssql"で指定した場合、?nをSQLに埋め込み、2つ目以降の引数で?nに渡す値を設定します(?1ならば2番目の引数、?2なら3番目の引数の値が渡されます)

   // select(1行)
    row := connection.QueryRow(`
      select name, registered_at, valid from members
          where member_id = @member_id`,
        sql.NamedArg{Name: "member_id", Value: 1})
    // ドライバに"mssql"を指定した場合
    // row := connection.QueryRow(`select name, registered_at, valid from members where member_id = ?1`, 1)

    var name string
    var registeredAt time.Time
    var valid bool
    if err := row.Scan(&name, &registeredAt, &valid); err != nil {
        return
    }
    fmt.Println(name, registeredAt, valid)

    // select(複数行)
    rows, err := connection.Query(`select name from members`)
    if err != nil {
        return
    }

    defer rows.Close()
    for rows.Next() {
        if err := rows.Scan(&name); err != nil {
            return
        }
        fmt.Println(name)
    }

続いて更新処理です。

• 更新(insert、update、delete)はExecメソッドで実行します
• Resultオブジェクトが返されます
  • RowsAffected()で、処理された行数を取得できます
  • LastInsertId()で、挿入時のidentityの主キー値が取得できます
    • 設定されていない場合は-1

   // insert
    if result, err := connection.Exec(`
      insert into members (member_id, name, registered_at, valid)
          values (@member_id, @name, @registered_at, @valid)`,
        sql.NamedArg{Name: "member_id", Value: 1},
        sql.NamedArg{Name: "name", Value: "onishik"},
        sql.NamedArg{Name: "registered_at", Value: time.Now()},
        sql.NamedArg{Name: "valid", Value: true}); err == nil {
        insertedNumber, _ := result.RowsAffected()
        insertedId, _ := result.LastInsertId()
        fmt.Println(insertedNumber, insertedId)
    }
 
    // update
    connection.Exec(`
      update members set valid = @valid
          where member_id = @member_id`,
        sql.NamedArg{Name: "valid", Value: false},
        sql.NamedArg{Name: "member_id", Value: 1})

    // delete
    connection.Exec(`
      delete members where member_id = @member_id`,
        sql.NamedArg{Name: "member_id", Value: 1})

ORMでアクセスする

ORMを使う方法もあります。

Goでは、gorm、xorm 、gorpなど幾つか選択肢がありますが、一番メジャー(GitHubスター比較)で、かつ、SQL Serverにも対応しているgormに触れていきます。

https://github.com/jinzhu/gorm

gorm

パッケージをダウンロードしておきます。

> go get github.com/jinzhu/gorm

まずは接続です。

• gormに加えて、gorm/dialects/mssqlをブランクインポートします
  • gorm/dialects/mssqlでSQL Server独自の型(bit型など)や処理(SET IDENTITY_INSERTなど)が吸収しています
  • 内部的にはgo-mssqldbをドライバとして使っています
• 構造体とテーブルレコードがマッピングされます
  • デフォルトではActiveRecordやEntityFrameworkと類似した名前のマッピングが行われます(もちろん変更できます)
    • "構造体名+s"がテーブル名にマッピングされます(Member→members)
    • キャメルケースはスネークケースに変換してマッピングされます(MemberID→member_id)
• gorm.Open()で接続しますが、第1引数は"mssql"とします
  • ちなみに"sqlserver"は不可です

package main

import (
    "fmt"
    "time"

    "github.com/jinzhu/gorm"

    _ "github.com/jinzhu/gorm/dialects/mssql"
)

// レコードの定義
type Member struct {
    MemberID     int       `gorm:"primary_key"`
    Name         string    `gorm:"type:nvarchar(256);name:name"`
    RegisteredAt time.Time `gorm:"type:datetime2;name:registered_at"`
    Valid        bool      `gorm:"type:bit;name:valid"`
}

func main() {
    connectionString := "sqlserver://ohke:p@ssw0rd@testdb.ikyu.jp?database=TestDB"

    // 接続
    db, err := gorm.Open("mssql", connectionString)
    if err != nil {
        panic(err.Error())
    }

    // 切断
    defer db.Close()

    // CRUD処理を記述
    // ...
}

CRUDも概観してみましょう。 いずれも上で取得したDBオブジェクトを使います。

• 1行のselectであればFirst()、複数行のselectではFind()を使います
• DBオブジェクトを返すので、FindしてDelete、といった処理も書きやすいです

   // select(1件)
    var member Member
    db.First(&member, 1)

    fmt.Println(member.Name, member.RegisteredAt, member.Valid)

    // select(複数行)
    members := []Member{}
    db.Find(&members, "valid=?", true)

    for _, m := range members {
        fmt.Println(m.Name, m.RegisteredAt, m.Valid)
    }

    // insert
    insertedMember := Member{MemberID: 2, Name: "akasakas", RegisteredAt: time.Now(), Valid: true}
    db.Create(&insertedMember)

    // update
    member.Valid = false
    db.Save(&member)

    // delete
    db.Delete(&member)

    // DBオブジェクトを返すのでメソッドチェーンで繋げることもできます
    db.Find(&members, "valid=?", true).Delete(&members)

ここでは紹介しませんでしたが、リレーション定義やマイグレーション等の一般的な機能も提供されています。

おわりに

本投稿では、GoからSQL Serverにアクセスする方法として、生のSQLを実行する方法(go-mssqldb)とORMを使う方法(gorm)を紹介しました。

明日は id:shiba-yan さんによる「一休.com で 1 年半の間に取り組んできた改善内容について」です。