# ClickHouse [ClickHouse](https://clickhouse.com/) is a fast and resource-efficient real-time data warehouse and open-source database. This document walks through setting up ClickHouse to be used as a data store in DeltaStream. Step through the following demo to get an overview. Detailed instructions are below the demo. {% @storylane/embed subdomain="deltastream" url="" linkValue="qhiujgcjlwmq" %} ## Setting up a ClickHouse Account To set up your ClickHouse Cloud account, please review the [ClickHouse Cloud Quick Start](https://clickhouse.com/docs/cloud/get-started/cloud-quick-start) guide. ## Adding ClickHouse as a DeltaStream Data Store 1. Open DeltaStream. In the lefthand navigation, click **Resources (** ![](https://1288764042-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fdbd9e6ZJodkgF1H6AVay%2Fuploads%2F9gG1xxfNSfFRjO6aS5Ou%2F0.png?alt=media) ) and towards the top right click + **Add Data Store**. 2. When the **Choose a Data Store** window opens, click **ClickHouse** and then click **Next**.
3\. The **Add Data Store** window displays.
4. Enter the authentication and connection parameters. These include: * **Name** – A name that identifies your ClickHouse store. * **URL** – JDBC URL from the ClickHouse Cloud console. Be sure to preface this URL with a `clickhouse://` scheme:
* **Username** – User login name for the ClickHouse account. * **Password** – Password for the login name of the ClickHouse account. 4\. Click **Add** to save and create the store. *For instructions on creating the store using DSQL, see* [create-store](https://docs.deltastream.io/reference/sql-syntax/ddl/create-store "mention"). ## Process Streaming Data and Sink it to ClickHouse {% hint style="warning" %} **Important** Before you proceed, you must already have: * A [stream](https://docs.deltastream.io/overview/core-concepts/databases#stream) defined in DeltaStream labeled `pageviews,` which is backed by a topic in an Apache Kafka Store. * A ClickHouse store labeled `clickhouse_store` (see [#adding-clickhouse-as-a-deltastream-store](#adding-clickhouse-as-a-deltastream-store "mention")). With these two items in place you can perform a simple filter on the `pageviews` stream and then sink the results into ClickHouse. {% endhint %} ### Inspect the ClickHouse Store 1. In the lefthand navigation, click **Resources (** ![](https://1288764042-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fdbd9e6ZJodkgF1H6AVay%2Fuploads%2F9gG1xxfNSfFRjO6aS5Ou%2F0.png?alt=media) ). This displays a list of the existing data stores.
2. Click `clickhouse_store.` The store page opens, displaying a list of the existing databases in your ClickHouse account:
3. (Optional) Create a new database. To do this: * Click **+ Add Database**. When prompted, enter a name for the new database and click **Add**. The new database displays in the list. 4. To see the tables that exist under a particular database, click the database name. ### Write a CTAS (CREATE TABLE AS SELECT) Query to Sink Data into ClickHouse 1. In the lefthand navigation, click **Workspace** ( ![](https://1288764042-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fdbd9e6ZJodkgF1H6AVay%2Fuploads%2F5EudXKVDSWZLByUuGbyy%2F5.png?alt=media) ). 2. In the SQL pane of your workspace, write the [create-table-as](https://docs.deltastream.io/reference/sql-syntax/query/create-table-as "mention") (CTAS) query to ingest from **pageviews** and output to a new table titled **ch\_pv\_table**.
```sql CREATE TABLE ch_pv_table WITH ( 'store' = 'clickhouse_store', 'clickhouse.db.name' = 'abc' ) AS SELECT viewtime, pageid, userid FROM pageviews WHERE pageid != 'Page_3'; ``` 3. Click **Run**. 4. View the existing queries, including the query from the step immediately prior. To do this, in the lefthand navigation click **Queries** ( ![](https://1288764042-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fdbd9e6ZJodkgF1H6AVay%2Fuploads%2FoeV9owwjCxALYll5NfSJ%2F6.png?alt=media) ). It may take a few moments for the query to transition into the **Running** state. 5. To see more details about the status of the query, click the query row :
### View the results 1. In the lefthand navigation, click **Resources (** ![](https://1288764042-files.gitbook.io/~/files/v0/b/gitbook-x-prod.appspot.com/o/spaces%2Fdbd9e6ZJodkgF1H6AVay%2Fuploads%2F9gG1xxfNSfFRjO6aS5Ou%2F0.png?alt=media) ). This displays a list of the existing data stores. 2. To view the new table created by the above CTAS, navigate to `clickhouse_store` → `Databases` → `abc` → `ch_pv_table`. 3. To view a sample of the data in your ClickHouse table, click **Print**.
### Data Type Handling At DeltaStream, all data types are nullable by default. But in ClickHouse, data types are not-null by default. For a smooth conversion between SQL and ClickHouse data types, DeltaStream provides a reasonable default for nullable columns to prevent `Nullable()` ClickHouse data types. Data type defaults are as followed:
SQL Data TypeClickHouse Data TypeNullable Column Default
BOOLEANBooleanfalse
CHARString''
VARCHARString''
VARBINARYString''
TINYINTInt80
SMALLINTInt160
INTEGERInt320
BIGINTInt640
DECIMALDecimal(P, S)0.00x
FLOATFloat320.0
DOUBLEFloat640.0
DATEDate32toDate('1970-01-01')
TIMESTAMPDateTime64(P)toDateTime64('1970-01-01 00:00:00', P)
TIMESTAMP_WITH_LOCAL_TIME_ZONEDateTime64(P)toDateTime64('1970-01-01 00:00:00', P)
ARRAYArray(T)[]
MAPMap(K, V)map()
STRUCTTuple(T1, T2, ...)[]
If you must use a specific default that is different than what's provided in the table above, you can use a `CASE` function to alter the value at query execution: ```sql SELECT viewtime, userid, CASE WHEN pageid IS NULL THEN 'Page_0' END AS pageid_my_default FROM pageviews; ``` In the above query, whenever the original `pageviews` column `pageid` is `null`, the value `Page_0` is projected to the results. This effectively eliminates all `null` values for the column and also allows you to define the resulting data type for the column as `pageid VARCHAR NOT NULL`.