mirror of
https://github.com/datahub-project/datahub.git
synced 2025-07-04 15:50:14 +00:00
145 lines
4.0 KiB
SQL
145 lines
4.0 KiB
SQL
CREATE DATABASE db1;
|
|
|
|
CREATE TABLE db1.test_data_types
|
|
(
|
|
`col_Array` Array(String),
|
|
`col_Bool` Bool COMMENT 'https://github.com/ClickHouse/ClickHouse/pull/31072',
|
|
`col_Date` Date,
|
|
`col_Date32` Date32 COMMENT 'this type was added in ClickHouse v21.9',
|
|
`col_DateTime` DateTime,
|
|
`col_DatetimeTZ` DateTime('Europe/Berlin'),
|
|
`col_DateTime32` DateTime,
|
|
`col_DateTime64` DateTime64(3),
|
|
`col_DateTime64TZ` DateTime64(2, 'Europe/Berlin'),
|
|
`col_Decimal` Decimal(2, 1),
|
|
`col_Decimal128` Decimal(38, 2),
|
|
`col_Decimal256` Decimal(76, 3),
|
|
`col_Decimal32` Decimal(9, 4),
|
|
`col_Decimal64` Decimal(18, 5),
|
|
`col_Enum` Enum8('hello' = 1, 'world' = 2),
|
|
`col_Enum16` Enum16('hello' = 1, 'world' = 2),
|
|
`col_Enum8` Enum8('hello' = 1, 'world' = 2),
|
|
`col_FixedString` FixedString(128),
|
|
`col_Float32` Float32,
|
|
`col_Float64` Float64,
|
|
`col_IPv4` IPv4,
|
|
`col_IPv6` IPv6,
|
|
`col_Int128` Int128,
|
|
`col_Int16` Int16,
|
|
`col_Int256` Int256,
|
|
`col_Int32` Int32,
|
|
`col_Int64` Int64,
|
|
`col_Int8` Int8,
|
|
`col_Map` Map(String, Nullable(UInt64)),
|
|
`col_String` String,
|
|
`col_Tuple` Tuple(UInt8, Array(String)),
|
|
`col_UInt128` UInt128,
|
|
`col_UInt16` UInt16,
|
|
`col_UInt256` UInt256,
|
|
`col_UInt32` UInt32,
|
|
`col_UInt64` UInt64,
|
|
`col_UInt8` UInt8,
|
|
`col_UUID` UUID
|
|
)
|
|
ENGINE = MergeTree
|
|
ORDER BY tuple()
|
|
SETTINGS index_granularity = 8192
|
|
COMMENT 'This table has basic types';
|
|
|
|
|
|
CREATE TABLE db1.test_nested_data_types
|
|
(
|
|
`col_ArrayArrayInt` Array(Array(Int8)) COMMENT 'this is a comment',
|
|
`col_LowCardinality` LowCardinality(String),
|
|
`col_AggregateFunction` AggregateFunction(avg, Float64),
|
|
`col_SimpleAggregateFunction` SimpleAggregateFunction(max, Decimal(38, 7)),
|
|
`col_Nested.c1` Array(UInt32),
|
|
`col_Nested.c2` Array(UInt64),
|
|
`col_Nested.c3.c4` Array(UInt128),
|
|
`col_Nullable` Nullable(Int8),
|
|
`col_Array_Nullable_String` Array(Nullable(String)),
|
|
`col_LowCardinality_Nullable_String` LowCardinality(Nullable(String))
|
|
)
|
|
ENGINE = MergeTree
|
|
ORDER BY tuple()
|
|
SETTINGS index_granularity = 8192
|
|
COMMENT 'This table has nested types';
|
|
|
|
|
|
CREATE DICTIONARY db1.test_dict
|
|
(
|
|
`col_Int64` Int64,
|
|
`col_String` String
|
|
)
|
|
PRIMARY KEY col_Int64
|
|
SOURCE(CLICKHOUSE(DB 'db1' TABLE 'test_data_types'))
|
|
LAYOUT(DIRECT());
|
|
|
|
|
|
CREATE VIEW db1.test_view
|
|
(
|
|
`col_String` String
|
|
) AS
|
|
SELECT dictGetOrDefault('db1.test_dict', 'col_String', toUInt64(123), 'na') AS col_String;
|
|
|
|
|
|
CREATE TABLE db1.mv_target_table
|
|
(
|
|
`col_DateTime` DateTime,
|
|
`col_Int64` Int64,
|
|
`col_Float64` Float64,
|
|
`col_Decimal64` Decimal(18, 5),
|
|
`col_String` Nullable(String)
|
|
)
|
|
ENGINE = MergeTree
|
|
ORDER BY col_Int64
|
|
SETTINGS index_granularity = 8192
|
|
COMMENT 'This is target table for materialized view';
|
|
|
|
|
|
-- https://clickhouse.com/docs/en/sql-reference/table-functions/generate/#generaterandom
|
|
INSERT INTO db1.mv_target_table
|
|
SELECT *
|
|
FROM generateRandom('col_DateTime DateTime, col_Int64 Int64, col_Float64 Float64, col_Decimal64 Decimal(18, 5), col_String Nullable(String)',
|
|
5 -- random_seed
|
|
)
|
|
LIMIT 10;
|
|
|
|
|
|
CREATE MATERIALIZED VIEW db1.mv_with_target_table TO db1.mv_target_table
|
|
(
|
|
`col_DateTime` DateTime,
|
|
`col_Int64` Int64,
|
|
`col_Float64` Float64,
|
|
`col_Decimal64` Decimal(18, 5),
|
|
`col_String` String
|
|
) AS
|
|
SELECT
|
|
col_DateTime,
|
|
col_Int64,
|
|
col_Float64,
|
|
col_Decimal64,
|
|
col_String
|
|
FROM db1.test_data_types;
|
|
|
|
|
|
CREATE MATERIALIZED VIEW db1.mv_without_target_table
|
|
(
|
|
`col_ArrayArrayInt` Array(Array(Int8)),
|
|
`col_LowCardinality` LowCardinality(String),
|
|
`col_Nullable` Nullable(Int8),
|
|
`col_Array_Nullable_String` Array(Nullable(String)),
|
|
`col_LowCardinality_Nullable_String` LowCardinality(Nullable(String))
|
|
)
|
|
ENGINE = MergeTree
|
|
PRIMARY KEY tuple()
|
|
ORDER BY tuple()
|
|
SETTINGS index_granularity = 8192 AS
|
|
SELECT
|
|
col_ArrayArrayInt,
|
|
col_LowCardinality,
|
|
col_Nullable,
|
|
col_Array_Nullable_String,
|
|
col_LowCardinality_Nullable_String
|
|
FROM db1.test_nested_data_types;
|