Documentation
¶
Overview ¶
Package sql implements Input/Output for SQL. One can use sql to create a new table from a query. This is similar to the ClickHouse CREATE MATERIALIZED VIEW statement but there is no trigger to update the output table if the input changes.
There are three approaches to creating a new ClickHouse table:
Direct ClickHouse insertion. Use sql Reader.Insert to issue an Insert query with Reader.SQL as the source.
Values insertion. Use sql Writer.Insert to issue an Insert query using VALUES. The values are created by sql Writer writing values from a reader. Although the source can be a sql.Reader, more commonly one would expect it to be a file.Reader.
clickhouse-client insert. Use a file Writer.Insert to create a CSV file and then issue a shell command to run the clickhouse-client to insert the file.
Before any of these approaches are used, the TableDef.CreateTable() can be used to create the destination table.
Index ¶
- func Wrtrs(table string, nWrtr int, conn *chutils.Connect) (wrtrs []chutils.Output, err error)
- type Reader
- func (rdr *Reader) Close() error
- func (rdr *Reader) CountLines() (numLines int, err error)
- func (rdr *Reader) Init(key string, engine chutils.EngineType) (err error)
- func (rdr *Reader) Insert() error
- func (rdr *Reader) Materialize(orderBy string) error
- func (rdr *Reader) Read(nTarget int, validate bool) (data []chutils.Row, valid []chutils.Valid, err error)
- func (rdr *Reader) Reset() error
- func (rdr *Reader) Seek(lineNo int) error
- func (rdr *Reader) TableSpec() *chutils.TableDef
- type Writer
Examples ¶
Constants ¶
This section is empty.
Variables ¶
This section is empty.
Functions ¶
Types ¶
type Reader ¶
type Reader struct {
SQL string // SQL is the SELECT string. It does not have an INSERT
RowsRead int // RowsRead is the number of rows read so far
Name string // Name is the name of the output table created by Insert()
// contains filtered or unexported fields
}
Reader implements chutils.Input interface.
func (*Reader) CountLines ¶
CountLines returns the number of rows in the result set.
func (*Reader) Init ¶
func (rdr *Reader) Init(key string, engine chutils.EngineType) (err error)
Init initializes Reader.TableDef by looking at the output of the query. if key is empty, it defaults to the first field.
func (*Reader) Materialize ¶ added in v1.1.28
func (*Reader) Read ¶
func (rdr *Reader) Read(nTarget int, validate bool) (data []chutils.Row, valid []chutils.Valid, err error)
Read reads nTarget rows. If nTarget == 0, the entire result set is returned.
If validation == true:
- The data is validated according to the rules in rdr.TableSpec.
- The results are returned as the slice valid.
- data is returned with the fields appropriately typed.
If validation == false:
- data is returned with the fields appropriately typed.
- The return slice valid is nil
err is io.EOF at the end of the record set
type Writer ¶
type Writer struct {
Table string // Table is the output table
// contains filtered or unexported fields
}
Writer implements chutils.Output
func (*Writer) Insert ¶
Insert executes an Insert query -- the values must have been built using Writer.Write
func (*Writer) Write ¶
Write writes the byte slice to Writer.hold. The byte slice is a single row of the output
Example ¶
This example reads from a file.Reader and writes to ClickHouse using a sql.Writer
/*
/home/test/data/zip_data.csv:
id,zip,value
1A34,90210,20.8
1X88,43210,19.2
1B23,77810,NA
1r99,94043,100.4
1x09,hello,9.9
*/
const inFile = "/home/will/tmp/zip_data.csv" // source data
const table = "testing.values" // ClickHouse destination table
var con *chutils.Connect
con, err := chutils.NewConnect("127.0.0.1", "tester", "testGoNow", clickhouse.Settings{})
if err != nil {
panic(err)
}
defer func() {
if con.Close() != nil {
panic(err)
}
}()
f, err := os.Open(inFile)
if err != nil {
panic(err)
}
rdr := file.NewReader(inFile, ',', '\n', '"', 0, 1, 0, f, 50000)
defer func() {
if rdr.Close() != nil {
panic(err)
}
}()
if e := rdr.Init("zip", chutils.MergeTree); e != nil {
panic(err)
}
if e := rdr.TableSpec().Impute(rdr, 0, .95); e != nil {
panic(e)
}
// Specify zip as FixedString(5) with a missing value of 00000
_, fd, err := rdr.TableSpec().Get("zip")
if err != nil {
panic(err)
}
// zip will impute to int if we don't make this change
fd.ChSpec.Base = chutils.ChFixedString
fd.ChSpec.Length = 5
fd.Missing = "00000"
legal := []string{"90210", "43210", "77810", "94043"}
fd.Legal.Levels = legal
// Specify value as having a range of [0,30] with a missing value of -1.0
_, fd, err = rdr.TableSpec().Get("value")
if err != nil {
panic(err)
}
fd.Legal.HighLimit = 30.0
fd.Legal.LowLimit = 0.0
fd.Missing = -1.0
rdr.TableSpec().Engine = chutils.MergeTree
rdr.TableSpec().Key = "id"
if err = rdr.TableSpec().Create(con, table); err != nil {
panic(err)
}
wrtr := NewWriter(table, con)
if err = chutils.Export(rdr, wrtr, 0, false); err != nil {
panic(err)
}
qry := fmt.Sprintf("SELECT * FROM %s", table)
res, err := con.Query(qry)
if err != nil {
panic(err)
}
defer func() {
if res.Close() != nil {
panic(err)
}
}()
for res.Next() {
var (
id string
zip string
value float64
)
if res.Scan(&id, &zip, &value) != nil {
panic(err)
}
fmt.Println(id, zip, value)
}
Output: 1A34 90210 20.8 1B23 77810 -1 1X88 43210 19.2 1r99 94043 -1 1x09 00000 9.9
Source Files