add clickhouse fdw docs

rohan-flutterint · Apr 6, 2023 · 4cfb7c3 · 4cfb7c3
1 parent e14a764
commit 4cfb7c3
Show file tree

Hide file tree

Showing 5 changed files with 140 additions and 110 deletions.
diff --git a/docs/bigquery.md b/docs/bigquery.md
@@ -77,7 +77,7 @@ end $$;
 
 #### Auth (Non-Supabase)
 
-If the platform you are using does not support `pgsodium` and `Vault`, you can create a server by storing yourt GCP credentials directly.
+If the platform you are using does not support `pgsodium` and `Vault`, you can create a server by storing your GCP credentials directly.
 
 
 !!! important

diff --git a/docs/clickhouse.md b/docs/clickhouse.md
@@ -0,0 +1,134 @@
+[ClickHouse](https://clickhouse.com/) is a fast open-source column-oriented database management system that allows generating analytical data reports in real-time using SQL queries.
+
+ClickHouse FDW supports both data read and modify.
+
+### Supported Data Types
+
+| Postgres Type      | ClickHouse Type   |
+| ------------------ | ----------------- |
+| boolean            | UInt8             |
+| smallint           | Int16             |
+| integer            | Int32             |
+| bigint             | UInt32            |
+| bigint             | Int64             |
+| bigint             | UInt64            |
+| real               | Float32           |
+| double precision   | Float64           |
+| text               | String            |
+| timestamp          | DateTime          |
+
+### Wrapper 
+To get started with the ClickHouse wrapper, create a foreign data wrapper specifying `handler` and `validator` as below.
+
+```sql
+create extension if not exists wrappers;
+
+create foreign data wrapper clickhouse_wrapper
+  handler click_house_fdw_handler
+  validator click_house_fdw_validator;
+```
+
+### Server 
+
+Next, we need to create a server for the FDW to hold options and credentials.
+
+#### Auth (Supabase)
+
+If you are using the Supabase platform, this is the recommended approach for securing your ClickHouse credentials.
+
+For example, to store ClickHouse connection string in Vault and retrieve the `key_id`,
+
+```sql
+-- save ClickHouse connection string in Vault and get its key id
+select pgsodium.create_key(name := 'clickhouse');
+insert into vault.secrets (secret, key_id) values (
+  'tcp://default:@localhost:9000/default',
+  (select id from pgsodium.valid_key where name = 'clickhouse')
+) returning key_id;
+```
+
+Then create the foreign server,
+
+```sql
+do $$
+declare
+  key_id text;
+begin
+  select id into key_id from pgsodium.valid_key where name = 'clickhouse' limit 1;
+
+  execute format(
+    E'create server clickhouse_server \n'
+    '   foreign data wrapper clickhouse_server \n'
+    '   options (conn_string_id ''%s'');',
+    key_id
+  );
+end $$;
+```
+
+#### Auth (Non-Supabase)
+
+If the platform you are using does not support `pgsodium` and `Vault`, you can create a server by storing your ClickHouse credentials directly.
+
+
+!!! important
+
+    Credentials stored using this method can be viewed as plain text by anyone with access to `pg_catalog.pg_foreign_server`
+
+
+```sql
+create server clickhouse_server
+  foreign data wrapper clickhouse_wrapper
+  options (
+    conn_string 'tcp://default:@localhost:9000/default'
+  );
+```
+
+
+### Tables
+
+ClickHouse wrapper is implemented with [ELT](https://hevodata.com/learn/etl-vs-elt/) approach, so the data transformation is encouraged to be performed locally after data is extracted from remote data source.
+
+
+#### Foreign Table Options
+
+The full list of foreign table options are below:
+
+- `table` - Source table name in ClickHouse, required.
+- `rowid_column` - Primary key column name, optional for data scan, required for data modify
+
+#### Examples
+
+Create a source table on ClickHouse and insert some data,
+
+```sql
+drop table if exists people;
+create table people (
+    id Int64, 
+    name String
+)
+engine=MergeTree()
+order by id;
+
+insert into people values (1, 'Luke Skywalker'), (2, 'Leia Organa'), (3, 'Han Solo');
+```
+
+Create foreign table and run a query on Postgres to read ClickHouse table,
+
+```sql
+create foreign table people (
+  id bigint,
+  name text
+)
+  server clickhouse_server
+  options (
+    table 'people'
+  );
+
+-- data scan
+select * from people;
+
+-- data modify
+insert into people values (4, 'Yoda');
+update people set name = 'Princess Leia' where id = 2;
+delete from people where id = 3;
+```
diff --git a/docs/index.md b/docs/index.md
@@ -40,6 +40,7 @@ Currently `supabase/wrappers` supports:
 | Integration | Select            | Insert            | Update            | Delete            | Truncate          |
 | ----------- | :----:            | :----:            | :----:            | :----:            | :----:            |
 | BigQuery    | :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :x:               |
+| ClickHouse  | :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :x:               |
 | Firebase    | :white_check_mark:| :x:               | :x:               | :x:               | :x:               |
 | S3          | :white_check_mark:| :x:               | :x:               | :x:               | :x:               |
 | Stripe      | :white_check_mark:| :white_check_mark:| :white_check_mark:| :white_check_mark:| :x:               |
diff --git a/mkdocs.yaml b/mkdocs.yaml
@@ -10,6 +10,7 @@ nav:
     - Installation: 'installation.md'
     - Wrappers:
       - BigQuery: 'bigquery.md'
+      - ClickHouse: 'clickhouse.md'
       - Firebase: 'firebase.md'
       - S3: 's3.md'
       - Stripe: 'stripe.md'

diff --git a/wrappers/src/fdw/clickhouse_fdw/README.md b/wrappers/src/fdw/clickhouse_fdw/README.md
@@ -1,117 +1,11 @@
 # ClickHouse Foreign Data Wrapper
 
-This is a foreign data wrapper for [Clickhouse](https://clickhouse.com/). It is developed using [Wrappers](https://github.com/supabase/wrappers) and supports both data scan and modify. 
+This is a foreign data wrapper for [ClickHouse](https://clickhouse.com/). It is developed using [Wrappers](https://github.com/supabase/wrappers) and supports both data scan and modify.
 
-## Basic usage
+## Documentation
 
-These steps outline how to use the Clickhouse FDW:
+[https://supabase.github.io/wrappers/clickhouse/](https://supabase.github.io/wrappers/clickhouse/)
 
-1. Clone this repo
-
-```bash
-git clone https://github.com/supabase/wrappers.git
-```
-
-2. Run it using pgx with feature:
-
-```bash
-cd wrappers/wrappers
-cargo pgx run --features clickhouse_fdw
-```
-
-3. Create the extension, foreign data wrapper and related objects:
-
-```sql
--- create extension
-create extension wrappers;
-
--- create foreign data wrapper and enable 'ClickHouseFdw'
-create foreign data wrapper clickhouse_wrapper
-  handler click_house_fdw_handler
-  validator click_house_fdw_validator;
-
--- create and save ClickHouse connection string in Vault
-select pgsodium.create_key(name := 'clickhouse');
-insert into vault.secrets (secret, key_id) values (
-  'tcp://default:@localhost:9000/default',
-  (select id from pgsodium.valid_key where name = 'clickhouse')
-) returning key_id;
-
--- create a wrappers ClickHouse server with connection string id option
--- Here we're using the connection string stored in Vault, if you don't want
--- to use Vault, you can directly specify the connection string using `conn_string`
--- option. For example,
---
--- create server my_clickhouse_server
---   foreign data wrapper clickhouse_wrapper
---   options (
---     conn_string 'tcp://default:@localhost:9000/default'
---   );
---
-do $$
-declare
-  key_id text;
-begin
-  select id into key_id from pgsodium.valid_key where name = 'clickhouse' limit 1;
-
-  execute format(
-    E'create server my_clickhouse_server \n'
-    '   foreign data wrapper clickhouse_wrapper \n'
-    '   options (conn_string_id ''%s'');',
-    key_id
-  );
-end $$;
-
--- create an example foreign table
-create foreign table people (
-  id bigint,
-  name text
-)
-  server my_clickhouse_server
-  options (
-    table 'people',
-    rowid_column 'id',
-    startup_cost '42'
-  );
-```
-
-4. Open another shell and start a Clickhouse server with some data populated
-
-```
-cd src/fdw/clickhouse_fdw/test_server
-docker-compose -f clickhouse.yaml up
-```
-
-5. Run some queries to check if it is working:
-
-On Postgres:
-
-```sql
--- data scan
-select * from people;
-
--- data modify
-insert into people values (4, 'Yoda');
-update people set name = 'Princess Leia' where id = 2;
-delete from people where id = 3;
-```
-
-6. Open another shell and check the changes on Clickhouse:
-
-```bash
-cd src/fdw/clickhouse_fdw/test_server
-docker-compose -f clickhouse.yaml exec server clickhouse-client
-```
-
-Run the following SQL on ClickHouse
-
-```sql
--- data scan
-select * from people;
-
--- data modification in ClickHouse will appear in Postgres:
-insert into people values (3, 'Han Solo');
-```
 
 ## Changelog