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.
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
Get help from others!
Recent Questions
Recent Answers
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP