araddon/qlbridge 方便开发sql 引擎的golang 包

araddon/qlbridge 是一个golang 表达式执行器可以用来方便的构建基于sql 的查询引擎
同时已经内置了几种sql 的实现，同时也有一个dataux/dataux （但是不 维护了）的实现，
类似presto，以下是关于araddon/qlbridge 的简单学习

代码来自官方文档

核心代码

 

package main

​

import (

    "bytes"

    "database/sql"

    "flag"

    "fmt"

    "net/mail"

    "strings"

​

    // Side-Effect Import the qlbridge sql driver

    _ "github.com/araddon/qlbridge/qlbdriver"

    "github.com/araddon/qlbridge/schema"

​

    u "github.com/araddon/gou"

    "github.com/araddon/qlbridge/datasource"

    "github.com/araddon/qlbridge/expr"

    "github.com/araddon/qlbridge/expr/builtins"

    "github.com/araddon/qlbridge/value"

)

​

var (

    sqlText          string

    flagCsvDelimiter = ","

    logging          = "info"

)

​

func init() {

​

    flag.StringVar(&logging, "logging", "info", "logging [ debug,info ]")

    flag.StringVar(&sqlText, "sql", "", "QL ish query multi-node such as [select user_id, yy(reg_date) from stdio];")

    flag.StringVar(&flagCsvDelimiter, "delimiter", ",", "delimiter:   default = comma [t,|]")

    flag.Parse()

​

    u.SetupLogging(logging)

    u.SetColorOutput()

}

​

func main() {

​

    if sqlText == "" {

        u.Errorf("You must provide a valid select query in argument:    --sql=\"select ...\"")

        return

    }

​

    // load all of our built-in functions

    builtins.LoadAllBuiltins()

​

    // Add a custom function to the VM to make available to SQL language

    expr.FuncAdd("email_is_valid", &EmailIsValid{})

​

    // We are registering the "csv" datasource, to show that

    // the backend/sources can be easily created/added.  This csv

    // reader is an example datasource that is very, very simple.

    exit := make(chan bool)

    src, _ := datasource.NewCsvSource("stdin", 0, bytes.NewReader([]byte("##")), exit)

    schema.RegisterSourceAsSchema("example_csv", src)

​

    db, err := sql.Open("qlbridge", "example_csv")

    if err != nil {

        panic(err.Error())

    }

    defer db.Close()

​

    rows, err := db.Query(sqlText)

    if err != nil {

        u.Errorf("could not execute query: %v", err)

        return

    }

    defer rows.Close()

    cols, _ := rows.Columns()

​

    // this is just stupid hijinx for getting pointers for unknown len columns

    readCols := make([]interface{}, len(cols))

    writeCols := make([]string, len(cols))

    for i := range writeCols {

        readCols[i] = &writeCols[i]

    }

    fmt.Printf("\n\nScanning through CSV: (%v)\n\n", strings.Join(cols, ","))

    for rows.Next() {

        rows.Scan(readCols...)

        fmt.Println(strings.Join(writeCols, ", "))

    }

    fmt.Println("")

}

​

// Example of a custom Function, that we are adding into the Expression VM

//

//         select

//              user_id AS theuserid, email, item_count * 2, reg_date

//         FROM stdio

//         WHERE email_is_valid(email)

type EmailIsValid struct{}

​

func (m *EmailIsValid) Validate(n *expr.FuncNode) (expr.EvaluatorFunc, error) {

    if len(n.Args) != 1 {

        return nil, fmt.Errorf("Expected 1 arg for EmailIsValid(arg) but got %s", n)

    }

    return func(ctx expr.EvalContext, args []value.Value) (value.Value, bool) {

        if args[0] == nil || args[0].Err() || args[0].Nil() {

            return value.BoolValueFalse, true

        }

        if _, err := mail.ParseAddress(args[0].ToString()); err == nil {

            return value.BoolValueTrue, true

        }

​

        return value.BoolValueFalse, true

    }, nil

}

func (m *EmailIsValid) Type() value.ValueType { return value.BoolType }

csv 文件

user_id,email,interests,reg_date,item_count,deleted

9Ip1aKbeZe2njCDM,"aaron@email.com","fishing","2012-10-17T17:29:39.738Z",82,false

hT2impsOPUREcVPc,"bob@gmail.com","swimming","2009-12-11T19:53:31.547Z",12,true

hT2impsabc345c,"not_an_email","swimming","2009-12-11T19:53:31.547Z",12,false

运行效果

• 命令

go run main.go -sql '             

    select

        user_id, email, item_count * 2, yy(reg_date) > 10

    FROM stdin' < users.csv

• 效果

 

 

说明

araddon/qlbridge 的设计是比较灵活的，当然如果从选择的角度来说，dolthub/go-mysql-server 是另外一个开箱即用的不错选择

参考资料

https://github.com/araddon/qlbridge
https://github.com/dataux/dataux
https://github.com/dolthub/go-mysql-server