Database
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
Create New Diagram
- Click File > New in the ChartDB editor
- Select ClickHouse from the database options
2
Run Import Query
Execute the provided query using either:
- Your database client (e.g., ClickHouse UI, DBeaver, etc..)
- Direct ClickHouse connection
3
Import Results
- Copy the JSON output from the query
- Paste into ChartDB’s import field
- Click Import to generate your diagram
Import Methods
ClickHouse
Copy
-- 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
Common Issues Guide
Find solutions for frequently encountered import problems and their resolutions
Was this page helpful?
On this page
Assistant
Responses are generated using AI and may contain mistakes.