Hi @kapa.ai, I have deployed Airbyte locally. My Source is Clickhouse Datawarehouse - 1 My destination is Clickhouse Datawarehouse - 2 [different from source] In the destination side I see the data as a JSON on

airbyte_internal

database But I would like to see the data as a table in a schema called

test_schema

. How can I achieve this?

To achieve your goal of seeing the data as a table in a schema called

test_schema

in your ClickHouse destination, you will need to perform some additional steps since Airbyte currently writes data into the

airbyte_internal

database in JSON format. Here are the steps you can follow: 1. Grant Necessary Permissions: Ensure that the

airbyte_user

has the necessary permissions to create tables and databases in ClickHouse. You can use the following SQL statements to grant the required permissions:

sql     GRANT CREATE ON * TO airbyte_user;       GRANT CREATE ON default * TO airbyte_user;       GRANT DROP ON * TO airbyte_user;       GRANT TRUNCATE ON * TO airbyte_user;       GRANT INSERT ON * TO airbyte_user;       GRANT SELECT ON * TO airbyte_user;       GRANT CREATE DATABASE ON airbyte_internal.* TO airbyte_user;       GRANT CREATE TABLE ON airbyte_internal.* TO airbyte_user;       GRANT DROP ON airbyte_internal.* TO airbyte_user;       GRANT TRUNCATE ON airbyte_internal.* TO airbyte_user;       GRANT INSERT ON airbyte_internal.* TO airbyte_user;       GRANT SELECT ON airbyte_internal.* TO airbyte_user;

[Source: ClickHouse Permissions](https://docs.airbyte.com/integrations/destinations/clickhouse#permissions) 2. Extract Data from JSON: Use ClickHouse's JSONExtract functions to parse the data from the

_airbyte_data

column. You can create a new table in the

test_schema

schema and insert the parsed data into this table. Here is an example of how you can do this:

sql     CREATE DATABASE IF NOT EXISTS test_schema;      CREATE TABLE test_schema.cars AS     SELECT         JSONExtractString(_airbyte_data, 'make') AS make,         JSONExtractString(_airbyte_data, 'model') AS model,         JSONExtractInt(_airbyte_data, 'horsepower') AS horsepower,         _airbyte_ab_id,         _airbyte_emitted_at     FROM airbyte_internal._airbyte_raw_cars;

This example assumes that your raw data table is

_airbyte_raw_cars

and that you want to extract the

make

,

model

, and

horsepower

fields from the JSON data. [Source: GitHub Issue on ClickHouse JSONExtract](https://github.com/airbytehq/airbyte/issues/36199) 3. Automate the Process: If you need to automate this process, consider using a tool like dbt (data build tool) to manage the transformations. dbt can help you create and manage the SQL transformations needed to convert the raw JSON data into structured tables. [Source: GitHub Discussion on Typing and Deduping](https://github.com/airbytehq/airbyte/discussions/35339) By following these steps, you should be able to see your data as a table in the

test_schema

schema in your ClickHouse destination. If you need further customization or automation, integrating dbt with your Airbyte setup can provide additional flexibility and control over your data transformations.