# Clickhouse

## Setup

The following credentials keys are accepted:

* `host` **(required)** -> The hostname / ip of the instance
* `database` **(required)** -> The database name of the instance
* `user` (optional) -> The username to access the instance
* `schema` (optional) -> The default schema to use
* `password` (optional) -> The password to access the instance
* `port` (optional) -> The port of the instance. Default is `9000`.
* `secure` (optional) -> Whether to use TLS for connecting. Default is `false`. **Note: This is required (`secure=true`) when connecting to ClickHouse Cloud.**
* `export_stream_format` (optional) -> Whether to specify the `FORMAT` when exporting (e.g. [`CSVWithNames`](https://clickhouse.com/docs/interfaces/formats/CSVWithNames)). Can help achieve low-memory exports.
* `skip_verify` (optional) -> Whether to skip verification for TLS. Default is `false`.
* `http_url` (optional) -> The HTTP url to override the connection string (see docs at [github.com/ClickHouse/clickhouse-go](https://github.com/ClickHouse/clickhouse-go?tab=readme-ov-file#http-support-experimental)). When specifying `http_url`, sling will use the HTTP clickhouse interface instead of the native interface. Native is recommended for optimal performance. **HTTP still has some limitations to be aware of for things like batch flushing and session context, so be cautious when switching over code to this protocol.**
* `ssh_tunnel` (optional) -> The URL of the SSH server you would like to use as a tunnel (example `ssh://user:password@db.host:22`)
* `ssh_private_key` (optional) -> The private key to use to access a SSH server (raw string or path to file).
* `ssh_passphrase` (optional) -> The passphrase to use to access a SSH server.
* `tls` -> TLS configuration name (`true`, `false`, `skip-verify`, or `custom` when providing `cert_*` keys below)

Custom TLS Certificates (v1.4.18+):

* `cert_file` (optional) -> the client certificate to use to access the instance via TLS (file path or raw)
* `cert_key_file` (optional) -> the client key to use to access the instance via TLS (file path or raw)
* `cert_ca_file` (optional) -> the client CA certificate to use to access the instance via TLS (file path or raw)

### Using `sling conns`

Here are examples of setting a connection named `CLICKHOUSE`. We must provide the `type=clickhouse` property:

{% code overflow="wrap" %}

```bash
$ sling conns set CLICKHOUSE type=clickhouse host=<host> user=<user> database=<database> password=<password> port=<port> 

# OR use url
$ sling conns set CLICKHOUSE url="clickhouse://myuser:mypass@host.ip:9000/mydatabase"

# connecting via http
$ sling conns set CLICKHOUSE type=clickhouse http_url="http://myuser:mypass@host.ip:8123/default"

# connecting to ClickHouse Cloud (secure=true is required)
$ sling conns set CLICKHOUSE_CLOUD url="clickhouse://myuser:mypass@host.clickhouse.cloud:9440/mydatabase?secure=true"
```

{% endcode %}

### Environment Variable

See [here](https://docs.slingdata.io/sling-cli/environment#dot-env-file-.env.sling) to learn more about the `.env.sling` file.

{% code overflow="wrap" %}

```bash
export CLICKHOUSE='clickhouse://myuser:mypass@host.ip:9000/mydatabase'
export CLICKHOUSE='{ type: clickhouse, user: "myuser", password: "mypass", host: "host.ip", port: 9000, database: "mydatabase", export_stream_format: "CSVWithNames" }'

# ClickHouse Cloud (secure=true is required)
export CLICKHOUSE_CLOUD='clickhouse://myuser:mypass@host.clickhouse.cloud:9440/mydatabase?secure=true'
```

{% endcode %}

### Sling Env File YAML

See [here](https://docs.slingdata.io/sling-cli/environment#sling-env-file-env.yaml) to learn more about the sling `env.yaml` file.

```yaml
connections:
  CLICKHOUSE:
    type: clickhouse
    host: <host>
    user: <user>
    port: <port>
    database: <database>
    schema: <schema>
    password: <password>
    export_stream_format: CSVWithNames

  # connecting via http
  CLICKHOUSE_HTTP:
    type: clickhouse
    http_url: http://myuser:mypass@host.ip:8123/default

  # connecting via https
  CLICKHOUSE_HTTP:
    type: clickhouse
    http_url: https://myuser:mypass@host.ip:8123/default?secure=true

  # ClickHouse Cloud (secure=true is required)
  CLICKHOUSE_CLOUD:
    type: clickhouse
    host: host.clickhouse.cloud
    user: <user>
    port: 9440
    database: <database>
    password: <password>
    secure: true
    export_stream_format: CSVWithNames

  CLICKHOUSE_URL:
    url: "clickhouse://myuser:mypass@host.ip:9000/mydatabase"
```

If you are facing issues connecting, please reach out to us at <support@slingdata.io>, on [discord](https://discord.gg/q5xtaSNDvp) or open a Github Issue [here](https://github.com/slingdata-io/sling-cli/issues).