> ## Documentation Index
> Fetch the complete documentation index at: https://docs.chartdb.io/llms.txt
> Use this file to discover all available pages before exploring further.
# Import ClickHouse
> Import ClickHouse database schema into ChartDB
ChartDB never stores or accesses your data - the import query only retrieves schema metadata.
## Quick Start
1. Click **File** > **New** in the ChartDB editor
2. Select **ClickHouse** from the database options
Execute the provided query using either:
* Your database client (e.g., ClickHouse UI, DBeaver, etc..)
* Direct ClickHouse connection
1. Copy the JSON output from the query
2. Paste into ChartDB's import field
3. Click **Import** to generate your diagram
## Import Methods
```sql ClickHouse theme={null}
-- Regular ClickHouse Database Import Query
WITH
cols AS (
SELECT arrayStringConcat(arrayMap(col_tuple ->
concat('{"schema":"', col_tuple.1, '"',
',"table":"', col_tuple.2, '"',
',"name":"', col_tuple.3, '"',
',"ordinal_position":"', toString(col_tuple.4), '"',
',"type":"', col_tuple.5, '"',
',"nullable":"', if(col_tuple.6 = 'NULLABLE', 'true', 'false'), '"',
',"default":"', if(col_tuple.7 = '', 'null', col_tuple.7), '"',
',"comment":', if(col_tuple.8 = '', '""', toString(toJSONString(col_tuple.8))), '}'),
groupArray((
col.database,
col.table,
col.name,
col.position,
col.type,
col.default_kind,
col.default_expression,
col.comment
))
), ',') AS cols_metadata
FROM system.columns AS col
JOIN system.tables AS tbl
ON col.database = tbl.database AND col.table = tbl.name
WHERE lower(col.database) NOT IN ('system', 'information_schema')
AND lower(col.table) NOT LIKE '.inner_id.%'
AND tbl.is_temporary = 0 -- Exclude temporary tables if desired
),
tbl_sizes AS (
SELECT database, table, sum(bytes_on_disk) AS size
FROM system.parts
GROUP BY database, table
),
tbls AS (
SELECT arrayStringConcat(arrayMap(tbl_tuple ->
concat('{"schema":"', tbl_tuple.1, '"',
',"table":"', tbl_tuple.2, '"',
',"rows":', toString(tbl_tuple.3),
',"type":"', tbl_tuple.4, '"',
',"engine":"', tbl_tuple.5, '"',
',"collation":"",',
'"size":', toString(tbl_tuple.6), ',',
'"comment":', if(tbl_tuple.7 = '', '""', toString(toJSONString(tbl_tuple.7))), '}'),
groupArray((
tbl.database, -- tbl_tuple.1
tbl.name, -- tbl_tuple.2
tbl.total_rows, -- tbl_tuple.3
tbl.type, -- tbl_tuple.4
tbl.engine, -- tbl_tuple.5
coalesce(ts.size, 0), -- tbl_tuple.6
tbl.comment -- tbl_tuple.7
))
), ',') AS tbls_metadata
FROM (
SELECT
tbl.database,
tbl.name,
coalesce(tbl.total_rows, 0) as total_rows,
-- Determine the type based on the engine
if(tbl.engine = 'View', 'VIEW',
if(tbl.engine = 'MaterializedView', 'MATERIALIZED VIEW', 'TABLE')) AS type,
tbl.engine,
tbl.comment
FROM system.tables AS tbl
WHERE lower(tbl.database) NOT IN ('system', 'information_schema')
AND lower(tbl.name) NOT LIKE '.inner_id.%'
AND tbl.is_temporary = 0
) AS tbl
LEFT JOIN tbl_sizes AS ts
ON tbl.database = ts.database AND tbl.name = ts.table
-- GROUP BY tbl.database, tbl.name, tbl.total_rows, tbl.type, tbl.engine, tbl.comment, ts.size
),
indexes AS (
SELECT arrayStringConcat(arrayMap((db, tbl, name) ->
concat('{"schema":"', db, '"',
',"table":"', tbl, '"',
',"name":"', name, '"',
',"index_type":"",',
'"cardinality":"",',
'"size":"",',
'"unique":"false"}'),
groupArray((idx.database, idx.table, idx.name))
), ',') AS indexes_metadata
FROM system.data_skipping_indices AS idx
WHERE lower(idx.database) NOT IN ('system', 'information_schema')
AND lower(idx.table) NOT LIKE '.inner_id.%'
),
views AS (
SELECT arrayStringConcat(arrayMap((db, name, definition) ->
concat('{"schema":"', db, '"',
',"view_name":"', name, '"',
',"view_definition":"',
base64Encode(replaceAll(replaceAll(definition, '\\', '\\\\'), '"', '\\"')), '"}'),
groupArray((vw.database, vw.name, vw.create_table_query))
), ',') AS views_metadata
FROM system.tables AS vw
WHERE vw.engine in ('View', 'MaterializedView')
AND lower(vw.database) NOT IN ('system', 'information_schema')
),
pks AS (
SELECT
col.database AS schema_name,
col.table AS table_name,
groupArray(col.name) AS pk_columns,
concat('PRIMARY KEY(', arrayStringConcat(groupArray(col.name), ', '), ')') AS pk_def
FROM system.columns AS col
WHERE col.is_in_primary_key = 1
AND lower(col.database) NOT IN ('system', 'information_schema')
AND lower(col.table) NOT LIKE '.inner_id.%'
GROUP BY col.database, col.table
),
pks_metadata AS (
SELECT arrayStringConcat(arrayMap(pk_tuple ->
concat('{"schema":"', pk_tuple.1, '"',
',"table":"', pk_tuple.2, '"',
',"column":"', pk_tuple.3, '"',
',"pk_def":"', pk_tuple.4, '"}'),
groupArray((
pks.schema_name,
pks.table_name,
arrayJoin(pks.pk_columns),
pks.pk_def
))
), ',') AS pk_metadata
FROM pks
)
SELECT
concat('{
"fk_info": [],',
'"pk_info": [', COALESCE((SELECT pk_metadata FROM pks_metadata), ''), '],',
'"columns": [', COALESCE((SELECT cols_metadata FROM cols), ''),
'], "indexes": [', COALESCE((SELECT indexes_metadata FROM indexes), ''),
'], "tables":[', COALESCE((SELECT tbls_metadata FROM tbls), ''),
'], "views":[', COALESCE((SELECT views_metadata FROM views), ''),
'], "database_name": "', currentDatabase(), '", "version": "', version(), '"}'
) AS metadata_json_to_import;
```
## Troubleshooting
Find solutions for frequently encountered import problems and their resolutions