olapsql

package module
v1.0.0 Latest Latest
Warning

This package is not in the latest version of its module.

 Go to latest Published: Nov 3, 2022 License: MIT Imports: 15 Imported by: 0

README

olap-sql

Go

Introduction

olap-sql is golang library for generating adapted sql by olap query with metrics, dimension and filter. Then get formatted sql result by queried metrics and dimension.

Example

There is unprocessed olap data with table named wikistat.

date time hits
2021-05-07 2021-05-07 09:28:27 4783
2021-05-07 2021-05-07 09:33:59 1842
2021-05-07 2021-05-07 10:34:12 0
2021-05-06 2021-05-06 20:32:41 5
2021-05-06 2021-05-06 21:16:39 139

It wants a sql to query the data with metrics: sum(hits) / count(*) and dimension: date.

SELECT wikistat.date AS date, ( ( 1.0 * SUM(wikistat.hits) ) /  NULLIF(( COUNT(*) ), 0) ) AS hits_avg FROM wikistat AS wikistat GROUP BY wikistat.date

It wants a sql to query the data with metrics: sum(hits) and filter: date <= '2021-05-06'.

SELECT SUM(wikistat.hits) AS hits FROM wikistat AS wikistat WHERE wikistat.date <= '2021-05-06'

Documentation

  1. Configuration to configure olap-sql instance and OLAP dictionary.
  2. Query to define olap query.
  3. Result to parse olap result.

Getting Started

Define the OLAP dictionary configuration file

Create a new file for example named olap-sql.toml to define sets, sources, metrics, dimensions.

sets = [
  {name = "wikistat", type = "clickhouse", data_source = "wikistat"},
]

sources = [
  {database = "", name = "wikistat", type = "fact"},
]

metrics = [
  {data_source = "wikistat", type = "METRIC_SUM", name = "hits", field_name = "hits", value_type = "VALUE_INTEGER"},
  {data_source = "wikistat", type = "METRIC_COUNT", name = "count", field_name = "*", value_type = "VALUE_INTEGER"},
  {data_source = "wikistat", type = "METRIC_DIVIDE", name = "hits_avg", value_type = "VALUE_FLOAT", dependency = ["wikistat.hits", "wikistat.count"]},
]

dimensions = [
  {data_source = "wikistat", type = "DIMENSION_SINGLE", name = "date", field_name = "date", value_type = "VALUE_STRING"},
]
To make use of olap-sql in golang

Create a new manager instance.

import "github.com/awatercolorpen/olap-sql"

// set clients option
clientsOption := map[string]*olapsql.DBOption{
	"clickhouse": &olapsql.DBOption{
		DSN:  "clickhouse://localhost:9000/default", 
		Type: "clickhouse"
	}
},

// set dictionary option
dictionaryOption := olapsql.AdapterOption{
	Dsn: "olap_sql.toml",
}

// build manager configuration
configuration := &olapsql.Configuration{
	ClientsOption:    clientsOption, 
	DictionaryOption: dictionaryOption,
}

// create a new manager instance
manager, err := olapsql.NewManager(configuration)

Build olap-sql query.

import "github.com/awatercolorpen/olap-sql/api/types"

queryJson := `
{
  "data_set_name": "wikistat",
  "time_interval": {
    "name": "date",
    "start": "2021-05-06",
    "end": "2021-05-08"
  },
  "metrics": [
    "hits",
    "hits_avg"
  ],
  "dimensions": [
    "date"
  ]
}`

query := &types.Query{}
err := json.Unmarshal([]byte(queryJson), query)

Run query to get result from manager.

// run query with parallel chan
result, err := manager.RunChan(query)

// run query with sync
result, err := manager.RunSync(query)
Generate SQL then format result

Firstly, auto generate sql. For detail.

Then, get result json with dimensions property and source property.

{
  "dimensions": [
    "date",
    "hits",
    "hits_avg"
  ],
  "source": [
    {
      "date": "2021-05-06T00:00:00Z",
      "hits": 147,
      "hits_avg": 49
    },
    {
      "date": "2021-05-07T00:00:00Z",
      "hits": 7178,
      "hits_avg": 897.25
    }
  ]
}

License

See the License File.

Documentation

Index

Constants

This section is empty.

Variables

This section is empty.

Functions

func BuildResultChan 

func BuildResultChan(query *types.Query, in chan map[string]any) (*types.Result, error)

func BuildResultSync 

func BuildResultSync(query *types.Query, in []map[string]any) (*types.Result, error)

func GetDependencyTree 

func GetDependencyTree(adapter IAdapter, current string) (models.Graph, error)

func IsValidJoin 

func IsValidJoin(adapter IAdapter, join *models.Join) error

func RunChan 

func RunChan(db *gorm.DB) (chan map[string]any, error)

func RunSync 

func RunSync(db *gorm.DB) ([]map[string]any, error)

Types

type AdapterOption 

type AdapterOption struct {
	Type AdapterType `json:"type"`
	Dsn  string      `json:"dsn"`
}

AdapterOption Adapter配置

type AdapterType 

type AdapterType string
const (
	FILEAdapter AdapterType = "FILE"
)

type Clients 

type Clients map[string]*gorm.DB

func NewClients 

func NewClients(option ClientsOption) (Clients, error)

func (Clients) BuildDB 

func (c Clients) BuildDB(clause types.Clause) (*gorm.DB, error)

func (Clients) BuildSQL 

func (c Clients) BuildSQL(clause types.Clause) (string, error)

func (Clients) Get 

func (c Clients) Get(dbType types.DBType, dataset string) (*gorm.DB, error)

func (Clients) RegisterByKV 

func (c Clients) RegisterByKV(dbType types.DBType, dataset string, db *gorm.DB)

func (Clients) RegisterByOption 

func (c Clients) RegisterByOption(option ClientsOption) error

func (Clients) SetLogger 

func (c Clients) SetLogger(log logger.Interface)

type ClientsOption 

type ClientsOption = map[string]*DBOption

type Configuration 

type Configuration struct {
	// configurations for clients, data_dictionary
	ClientsOption    ClientsOption `json:"clients_option"`
	DictionaryOption *Option       `json:"dictionary_option"`
}

type DBOption 

type DBOption struct {
	Debug bool         `json:"debug"`
	DSN   string       `json:"dsn"`
	Type  types.DBType `json:"type"`
}

func (*DBOption) NewDB 

func (o *DBOption) NewDB() (*gorm.DB, error)

type DependencyGraph 

type DependencyGraph interface {
	Get(key string) (any, error)
	GetMetric(key string) (*types.Metric, error)
	GetDimension(key string) (*types.Dimension, error)
}

type DependencyGraphBuilder 

type DependencyGraphBuilder struct {
	// contains filtered or unexported fields
}

func (*DependencyGraphBuilder) Build 

func (g *DependencyGraphBuilder) Build() (DependencyGraph, error)

type Dictionary 

type Dictionary struct {
	Adapter IAdapter
}

func NewDictionary 

func NewDictionary(option *Option) (*Dictionary, error)

func (*Dictionary) Translate 

func (d *Dictionary) Translate(query *types.Query) (types.Clause, error)

func (*Dictionary) Translator 

func (d *Dictionary) Translator(query *types.Query) (Translator, error)

type FileAdapter 

type FileAdapter struct {
	Sets       []*models.DataSet
	Sources    []*models.DataSource
	Metrics    []*models.Metric
	Dimensions []*models.Dimension
}

FileAdapter 文件适配器

func (*FileAdapter) BuildDataSourceAdapter 

func (f *FileAdapter) BuildDataSourceAdapter(key string) (IAdapter, error)

func (*FileAdapter) GetDataSetByKey 

func (f *FileAdapter) GetDataSetByKey(key string) (*models.DataSet, error)

func (*FileAdapter) GetDimension 

func (f *FileAdapter) GetDimension() []*models.Dimension

func (*FileAdapter) GetDimensionByKey 

func (f *FileAdapter) GetDimensionByKey(key string) (*models.Dimension, error)

func (*FileAdapter) GetDimensionsBySource 

func (f *FileAdapter) GetDimensionsBySource(key string) []*models.Dimension

func (*FileAdapter) GetMetric 

func (f *FileAdapter) GetMetric() []*models.Metric

func (*FileAdapter) GetMetricByKey 

func (f *FileAdapter) GetMetricByKey(key string) (*models.Metric, error)

func (*FileAdapter) GetMetricsBySource 

func (f *FileAdapter) GetMetricsBySource(key string) []*models.Metric

func (*FileAdapter) GetSourceByKey 

func (f *FileAdapter) GetSourceByKey(key string) (*models.DataSource, error)

type IAdapter 

type IAdapter interface {
	BuildDataSourceAdapter(string) (IAdapter, error)

	GetMetric() []*models.Metric
	GetDimension() []*models.Dimension

	GetDataSetByKey(string) (*models.DataSet, error)
	GetSourceByKey(string) (*models.DataSource, error)
	GetMetricByKey(string) (*models.Metric, error)
	GetDimensionByKey(string) (*models.Dimension, error)

	GetMetricsBySource(string) []*models.Metric
	GetDimensionsBySource(string) []*models.Dimension
}

IAdapter Adapter适配器

func NewAdapter 

func NewAdapter(option *AdapterOption) (IAdapter, error)

type Manager 

type Manager struct {
	// contains filtered or unexported fields
}

func NewManager 

func NewManager(configuration *Configuration) (*Manager, error)

func (*Manager) BuildSQL 

func (m *Manager) BuildSQL(query *types.Query) (string, error)

func (*Manager) BuildTransaction 

func (m *Manager) BuildTransaction(query *types.Query) (*gorm.DB, error)

func (*Manager) GetClients 

func (m *Manager) GetClients() (Clients, error)

func (*Manager) GetDictionary 

func (m *Manager) GetDictionary() (*Dictionary, error)

func (*Manager) RunChan 

func (m *Manager) RunChan(query *types.Query) (*types.Result, error)

func (*Manager) RunSync 

func (m *Manager) RunSync(query *types.Query) (*types.Result, error)

func (*Manager) SetLogger 

func (m *Manager) SetLogger(log logger.Interface)

type NormalClauseSplitter 

type NormalClauseSplitter struct {
	Translator Translator

	CandidateList []*types.DataSource
	Candidate     map[string]*types.DataSource
	SplitQuery    map[string]*types.Query

	Clause *types.NormalClause
	Query  *types.Query
	DBType types.DBType
}

func NewNormalClauseSplitter 

func NewNormalClauseSplitter(translator Translator, clause *types.NormalClause, query *types.Query, dbType types.DBType) (*NormalClauseSplitter, error)

func (*NormalClauseSplitter) GetOtherCandidate 

func (n *NormalClauseSplitter) GetOtherCandidate() []*types.DataSource

func (*NormalClauseSplitter) GetOtherSplitQuery 

func (n *NormalClauseSplitter) GetOtherSplitQuery() map[string]*types.Query

func (*NormalClauseSplitter) GetSelfCandidate 

func (n *NormalClauseSplitter) GetSelfCandidate() *types.DataSource

func (*NormalClauseSplitter) GetSelfSplitQuery 

func (n *NormalClauseSplitter) GetSelfSplitQuery() *types.Query

func (*NormalClauseSplitter) Polish 

func (n *NormalClauseSplitter) Polish() map[string]*types.Query

func (*NormalClauseSplitter) Run 

func (n *NormalClauseSplitter) Run() error

type Option 

type Option struct {
	AdapterOption
}

type Translator 

type Translator interface {
	GetCurrent() string
	GetAdapter() IAdapter
	GetDependencyGraph() DependencyGraph
	Translate(*types.Query) (types.Clause, error)
}

func NewTranslator 

func NewTranslator(option *TranslatorOption) (Translator, error)

type TranslatorOption 

type TranslatorOption struct {
	Adapter IAdapter
	Query   *types.Query
	DBType  types.DBType
	Current string
}

Source Files

View all Source files

Directories

Path Synopsis
api
models
types

Jump to

Keyboard shortcuts

? : This menu
/ : Search site
f or F : Jump to
y or Y : Canonical URL
go.dev uses cookies from Google to deliver and enhance the quality of its services and to analyze traffic. Learn more.