ClickHouseVapor

A simple column-oriented ORM for the ClickHouse database in Swift.

Features:

• Fixed datatype ORM
• Connection pool
• Fully asynchronous based on the adapter ClickHouseNio
• Integrated with Vapor 4

Installation:

1. Add ClickHouseVapor as a dependency to your Package.swift

  dependencies: [
    .package(url: "https://github.com/patrick-zippenfenig/ClickHouseVapor.git", from: "1.0.0")
  ],
  targets: [
    .target(name: "MyApp", dependencies: ["ClickHouseVapor"])
  ]

2. Build your project:

$ swift build

Usage

1. Configure the connection credentials with a Vapor 4 application. Usually this is done in config.swift.

Note: maxConnectionsPerEventLoop controls the number of connections per thread. If you have 4 CPU cores and Vapor is using 4 eventLoops, 8 connections will be used. requestTimeout is the timeout to establish a connection. It does not limit query runtime.

import ClickHouseVapor

let app = Application(.testing)
defer { app.shutdown() }

app.clickHouse.configuration = try ClickHousePoolConfiguration(
    hostname: "localhost",
    port: 9000,
    user: "default",
    password: "admin",
    database: "default",
    maxConnectionsPerEventLoop: 2,
    requestTimeout: .seconds(10)
)

1. Define a table with fields and an engine.

public class TestModel : ClickHouseModel {
    @Field(key: "timestamp", isPrimary: true, isOrderBy: true)
    var timestamp: [Int64]
    
    @Field(key: "stationID", isPrimary: true, isOrderBy: true)
    var id: [String]
    
    @Field(key: "fixed", fixedStringLen: 10)
    var fixed: [ String ]
    
    @Field(key: "temperature")
    var temperature: [Float]
    
    required public init() {
        
    }
    
    public static var engine: ClickHouseEngine {
        return ClickHouseEngineReplacingMergeTree(
            table: "test",
            database: nil,
            cluster: nil,
            partitionBy: "toYYYYMM(toDateTime(timestamp))"
        )
    }
}

3. Create a table. For simplicity this example is calling wait(). It is discouraged to use wait() in production.

try TestModel.createTable(on: app.clickHouse).wait()

4. Insert data

let model = TestModel()
model.id = [ "x010", "ax51", "cd22" ]
model.fixed = [ "", "123456", "12345678901234" ]
model.timestamp = [ 100, 200, 300 ]
model.temperature = [ 11.1, 10.4, 8.9 ]

try model.insert(on: app.clickHouse).wait()

5. Query all data again

let result = try TestModel.select(on: app.clickHouse).wait()
print(result.temperature) // [ 11.1, 10.4, 8.9 ]

// Filter data in more detail
let result2 = try! TestModel.select(
    on: app.clickHouse,
    fields: ["timestamp", "stationID"],
    where: "temperature > 10",
    order: "timestamp DESC",
    limit: 10,
    offset: 0
).wait()

print(result2.id) // ["ax51", "x010"]
print(result2.timestamp) //  [200, 100]

// Perform raw queries, but assign the result to TestModel
let sql = "SELECT timestamp, stationID FROM default.test"
let result2 = try! TestModel.select(on: app.clickHouse, sql: sql).wait()

ToDo List

• Query timeouts
• Implement more engines

Contributing

Pull requests are welcome. For major changes, please open an issue first to discuss what you would like to change.

Please make sure to update tests as appropriate.

License

MIT