TransWikia.com

clickhouse : information_schema.KEY_COLUMN_USAGE

Stack Overflow Asked by Sravya Jasthi on November 4, 2021

In MySQL we have information_schema.KEY_COLUMN_USAGE. Where we can find same information in click house ?
select * from information_schema.KEY_COLUMN_USAGE in MySQL, by executing this we get the result. I want to know what is the query in clickhouse which gives same result.

One Answer

CH provides several ways to get metadata about a table.

Let's create test-table:

CREATE TABLE test_001
(
    `id` Int32 CODEC(Delta, LZ4), 
    CONSTRAINT id_should_be_positive CHECK id > 0
)
ENGINE = MergeTree()
PARTITION BY tuple()
ORDER BY id

and look at these ways:

SELECT *
FROM system.tables
WHERE name = 'test_001'
FORMAT Vertical
/*
Row 1:
──────
database:                   default
name:                       test_001
uuid:                       00000000-0000-0000-0000-000000000000
engine:                     MergeTree
is_temporary:               0
data_paths:                 ['/var/lib/clickhouse/data/default/test_001/']
metadata_path:              /var/lib/clickhouse/metadata/default/test_001.sql
metadata_modification_time: 2020-07-21 12:42:07
dependencies_database:      []
dependencies_table:         []
create_table_query:         CREATE TABLE default.test_001 (`id` Int32 CODEC(Delta(4), LZ4),  CONSTRAINT id_should_be_positive CHECK id > 0) ENGINE = MergeTree() PARTITION BY tuple() ORDER BY id SETTINGS index_granularity = 8192
engine_full:                MergeTree() PARTITION BY tuple() ORDER BY id SETTINGS index_granularity = 8192
partition_key:              tuple()
sorting_key:                id
primary_key:                id
sampling_key:               
storage_policy:             default
total_rows:                 0
total_bytes:                0
*/
SELECT *
FROM system.columns
WHERE table = 'test_001'
FORMAT Vertical
/*
Row 1:
──────
database:                default
table:                   test_001
name:                    id
type:                    Int32
default_kind:            
default_expression:      
data_compressed_bytes:   0
data_uncompressed_bytes: 0
marks_bytes:             0
comment:                 
is_in_partition_key:     0
is_in_sorting_key:       1
is_in_primary_key:       1
is_in_sampling_key:      0
compression_codec:       CODEC(Delta(4), LZ4)
*/
DESCRIBE TABLE test_001
/*
┌─name─┬─type──┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ id   │ Int32 │              │                    │         │ Delta(4), LZ4    │                │
└──────┴───────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘
*/
SHOW CREATE TABLE test_001
/*
┌─statement──────────────────────────────────────────┐
│ CREATE TABLE default.test_001
(
    `id` Int32 CODEC(Delta(4), LZ4), 
    CONSTRAINT id_should_be_positive CHECK id > 0
)
ENGINE = MergeTree()
PARTITION BY tuple()
ORDER BY id
SETTINGS index_granularity = 8192 │
└─────────────────────────────────────────────────────┘
*/

Answered by vladimir on November 4, 2021

Add your own answers!

Ask a Question

Get help from others!

© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP