---
title: "Working with ClickHouse Databases Using ClickHouseHTTP"
oauthor: "[Patrice Godard](mailto:patrice.godard@gmail.com?Subject=ClickHouseHTTP)"
date: "`r format(Sys.time(), '%B %d %Y')`"
always_allow_html: true
output:
  html_document:
    keep_md: no
    number_sections: yes
    self_contained: yes
    theme: cerulean
    toc: yes
    toc_float: yes
  pdf_document:
    toc: yes
    number_sections: yes
package: ClickHouseHTTP (version `r packageVersion('ClickHouseHTTP')`)
vignette: >
   %\VignetteIndexEntry{Working with ClickHouse Databases Using ClickHouseHTTP}
   %\VignetteEncoding{UTF-8}
   %\VignetteEngine{knitr::rmarkdown}
editor_options:
  chunk_output_type: console
---

```{r setup, include = FALSE}
library(knitr)
library(ClickHouseHTTP)
library(dplyr)
```

# Connection

```{r, eval=FALSE}
library(DBI)
## HTTP connection
con <- dbConnect(
  ClickHouseHTTP::ClickHouseHTTP(),
  host = "localhost",
  port = 8123
)
## HTTPS connection (without ssl peer verification)
con <- dbConnect(
  ClickHouseHTTP::ClickHouseHTTP(),
  host = "localhost",
  port = 8443,
  https = TRUE,
  ssl_verifypeer = FALSE
)
```

# Write a table in the database

```{r, eval=FALSE}
library(dplyr)
data("mtcars")
mtcars <- as_tibble(mtcars, rownames = "car")
dbWriteTable(con, "mtcars", mtcars)
```

# Query the database

```{r, eval=FALSE}
carsFromDB <- dbReadTable(con, "mtcars")
dbGetQuery(con, "SELECT car, mpg, cyl, hp FROM mtcars WHERE hp>=110")
```

By default, ClickHouseHTTP relies on the
[Apache `Arrow`](https://arrow.apache.org/) format provided by ClickHouse.
However, as described in the
[documentation](https://clickhouse.com/docs/en/interfaces/formats/#data-format-arrow),
the following types are not supported in the current implementation of
this format: *TIME32*, *FIXED_SIZE_BINARY*, *JSON*, *UUID*, *ENUM*.
The `format` argument of the `dbGetQuery()` function can be used to
rely on the *TabSeparatedWithNamesAndTypes* format.

```{r, eval=FALSE}
selCars <- dbGetQuery(
  con,
  "SELECT car, mpg, cyl, hp FROM mtcars WHERE hp>=110",
  format = "TabSeparatedWithNamesAndTypes"
)
## Identifying the original ClickHouse data types
attr(selCars, "type")
```

# Using alternative databases stored in ClickHouse

It's only possible when sessions are activated with the `use_session` param.

```{r, eval=FALSE}
library(DBI)
con <- dbConnect(
  ClickHouseHTTP::ClickHouseHTTP(),
  host = "localhost",
  port = 8123,
  use_session = TRUE
)
```

```{r, eval=FALSE}
dbSendQuery(con, "CREATE DATABASE swiss")
dbSendQuery(con, "USE swiss")
```

The chosen database is used until the session expires.
It can also be chosen when connecting using the `dbname` argument of
the `dbConnect()` function.

The example below shows that spaces in column names are supported.
It also shows the support of R `list` using the *Array* ClickHouse type.

```{r, eval=FALSE}
data("swiss")
swiss <- as_tibble(swiss, rownames = "province")
swiss <- mutate(swiss, "pr letters" = strsplit(province, ""))
dbWriteTable(
  conn = con,
  name = "swiss",
  value = swiss,
  engine = "MergeTree() ORDER BY (Fertility, province)"
)
swissFromDB <- dbReadTable(con, "swiss") |>
  as_tibble()
```

A table from another database can also be accessed as following:

```{r, eval=FALSE}
dbReadTable(con, SQL("default.mtcars"))
```