golang+presto查询在数据平台中ad hoc查询

先贴一下GitHub地址:https://github.com/derekennui/presto-go-sql

presto作为分布式计算引擎,因为其提供了跨数据源查询功能,以及内存计算优秀的性能,在我司应用到了很多场景。由于我司以golang为主,所以实现了一个简陋的presto sql查询工具。
通常情况下golang查询需要预先定好struct,再传入指针,对于ad hoc查询显然是不满足的,所以只能将其转化为json格式。
比如我们数据库中数据如下:

PersonID LastName FirstName Address City
1 White Clover 305 - 14th Ave. S. Suite 3B Seattle
2 Wilman Kala Keskuskatu 45 Helsinki

传入sql:

select * from mysql.test.Persons 

得到的返回结果:

[{"address":"305 - 14th Ave. S. Suite 3B","city":"Seattle","firstname":"Clover","lastname":"White","personid":1}, {"address":"Keskuskatu 45","city":"Helsinki","firstname":"Kala","lastname":"Wilman","personid":2}]

代码如下:

package main
import (
 "database/sql"
 "encoding/json"
 "fmt"
 "reflect"
 "github.com/go-sql-driver/mysql"
 _ "github.com/prestodb/presto-go-client/presto"
)
func main() {
 prestoUrl := "http://user@localhost:8080?catalog=mysql&schema=test"
 sqlExe := "select * from mysql.test.Persons"
 content, _ := ExePrestoSqlQuery(prestoUrl, sqlExe)
 fmt.Println("query result : ", string(content))
}
type jsonNullInt64 struct {
 sql.NullInt64
}
func (v jsonNullInt64) MarshalJSON() ([]byte, error) {
 if !v.Valid {
 return json.Marshal(nil)
 }
 return json.Marshal(v.Int64)
}
type jsonNullFloat64 struct {
 sql.NullFloat64
}
func (v jsonNullFloat64) MarshalJSON() ([]byte, error) {
 if !v.Valid {
 return json.Marshal(nil)
 }
 return json.Marshal(v.Float64)
}
type jsonNullTime struct {
 mysql.NullTime
}
func (v jsonNullTime) MarshalJSON() ([]byte, error) {
 if !v.Valid {
 return json.Marshal(nil)
 }
 return json.Marshal(v.Time)
}
var jsonNullInt64Type = reflect.TypeOf(jsonNullInt64{})
var jsonNullFloat64Type = reflect.TypeOf(jsonNullFloat64{})
var jsonNullTimeType = reflect.TypeOf(jsonNullTime{})
var nullInt64Type = reflect.TypeOf(sql.NullInt64{})
var nullFloat64Type = reflect.TypeOf(sql.NullFloat64{})
var nullTimeType = reflect.TypeOf(mysql.NullTime{})
func ExePrestoSqlQuery(prestoUrl string, sqlExe string) ([]byte, error) {
 db, err := sql.Open("presto", prestoUrl)
 if err != nil {
 return nil, fmt.Errorf("can't connect to presto error: %v", err)
 }
 rows, err := db.Query(sqlExe)
 defer rows.Close()
 columns, err := rows.Columns()
 if err != nil {
 return nil, fmt.Errorf("column error: %v", err)
 }
 ct, err := rows.ColumnTypes()
 if err != nil {
 return nil, fmt.Errorf("column type error: %v", err)
 }
 types := make([]reflect.Type, len(ct))
 for i, tp := range ct {
 st := tp.ScanType()
 if st == nil {
 return nil, fmt.Errorf("scantype is null for column: %v", err)
 }
 switch st {
 case nullInt64Type:
 types[i] = jsonNullInt64Type
 case nullFloat64Type:
 types[i] = jsonNullFloat64Type
 case nullTimeType:
 types[i] = jsonNullTimeType
 default:
 types[i] = st
 }
 }
 values := make([]interface{}, len(ct))
 var slice []map[string]interface{}
 for rows.Next() {
 for i := range values {
 values[i] = reflect.New(types[i]).Interface()
 }
 err = rows.Scan(values...)
 if err != nil {
 return nil, fmt.Errorf("failed to scan values: %v", err)
 }
 data := make(map[string]interface{})
 for i, v := range values {
 data[columns[i]] = v
 }
 slice = append(slice, data)
 }
 return json.Marshal(slice)
}

本文来自:简书

感谢作者:aside section._1OhGeD

查看原文:golang+presto查询在数据平台中ad hoc查询

• 编辑
• 预览