image
CREATE TABLE local.dim_pub_date
on cluster cluster_3shards_1replicas
(
`dt` Date,
`dt_str` String,
`yea` UInt16,
`quar` UInt8,
`mon` UInt8,
`daynumber_of_year` UInt16,
`daynumber_of_Week` UInt8,
`tmstamp` UInt32
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{layer}-{shard}/dim_pub_date', '{replica}')
PARTITION BY yea
ORDER BY dt
SETTINGS index_granularity = 8192
--根据现有本地表创建分布式表
CREATE TABLE dw.dim_pub_date
on cluster cluster_3shards_1replicas
as local.dim_pub_date
ENGINE = Distributed('cluster_3shards_1replicas', 'local', dim_pub_date, rand());
insert into dw.dim_pub_date
WITH arrayMap(i -> (toDate('2010-01-01') + i), range(29220)) AS Calendar
SELECT
arrayJoin(Calendar) as dt
,cast(dt as String) as dt_str
,toYear(dt) yea
,toQuarter(dt) quar
,toMonth(dt) mon
,toDayOfYear(dt) daynumber_of_year
,toDayOfWeek(dt) daynumber_of_Week
,toUnixTimestamp(toDateTime(dt)) tmstamp
order by dt desc;
