2023年日历维度数据
建表语句,如下:
DROP TABLE IF EXISTS dim_date_info;
CREATE EXTERNAL TABLE dim_date_info(
`date_id` STRING COMMENT '日',
`week_id` STRING COMMENT '周ID',
`week_day` STRING COMMENT '周几',
`day` STRING COMMENT '每月的第几天',
`month` STRING COMMENT '第几月',
`quarter` STRING COMMENT '第几季度',
`year` STRING COMMENT '年',
`is_workday` STRING COMMENT '是否是工作日',
`holiday_id` STRING COMMENT '节假日'
) COMMENT '时间维度表'
STORED AS ORC
LOCATION '/warehouse/xxx/dim/dim_date_info/'
TBLPROPERTIES ("orc.compress"="snappy");
日期内容,如下:
DROP TABLE IF EXISTS dim_date_info;
CREATE EXTERNAL TABLE dim_date_info(
`date_id` STRING COMMENT '日',
`week_id` STRING COMMENT '周ID',
`week_day` STRING COMMENT '周几',
`day` STRING COMMENT '每月的第几天',
`month` STRING COMMENT '第几月',
`quarter` STRING COMMENT '第几季度',
`year` STRING COMMENT '年',
`is_workday` STRING COMMENT '是否是工作日',
`holiday_id` STRING COMMENT '节假日'
) COMMENT '时间维度表'
STORED AS ORC
LOCATION '/warehouse/xxx/dim/dim_date_info/'
TBLPROPERTIES ("orc.compress"="snappy");
日期内容,如下:
2023/1/1 53 7 1 1 1 2023 0 元旦 2023/1/2 1 1 2 1 1 2023 0 元旦 2023/1/3 1 2 3 1 1 2023 1 \N 2023/1/4 1 3 4 1 1 2023 1 \N 2023/1/5 1 4 5 1 1 2023 1 \N 2023/1/6 1 5 6 1 1 2023 1 \N 2023/1/7 1 6 7 1 1 2023 0 \N 2023/1/8 1 7 8 1 1 2023 0 \N 2023/1/9 2 1 9 1 1 2023 1 \N 2023/1/10 2 2 10 1 1 2023 1 \N 2023/1/11 2 3 11 1 1 2023 1 \N 2023/1/12 2 4 12 1 1 2023 1 \N 2023/1/13 2 5 13 1 1 2023 1 \N 2023/1/14 2 6 14 1 1 2023 0 \N 2023/1/15 2 7 15 1 1 2023 0 \N 2023/1/16 3 1 16 1 1 2023 1 \N 2023/1/17 3 2 17 1 1 2023 1 \N 2023/1/18 3 3 18 1 1 2023 1 \N 2023/1/19 3 4 19 1 1 2023 1 \N 2023/1/20 3 5 20 1 1 2023 1 \N 2023/1/21 3 6 21 1 1 2023 0 春节 2023/1/22 3 7 22 1 1 2023 0 春节 2023/1/23 4 1 23 1 1 2023 0 春节 2023/1/24 4 2 24 1 1 2023 0 春节 2023/1/25 4 3 25 1 1 2023 0 春节 2023/1/26 4 4 26 1 1 2023 0 春节 2023/1/27 4 5 27 1 1 2023 0 春节 2023/1/28 4 6 28 1 1 2023 1 \N 2023/1/29 4 7 29 1 1 2023 1 \N 2023/1/30 5 1 30 1 1 2023 1 \N 2023/1/31 5 2 31 1 1 2023 1 \N 2023/2/1 5 3 1 2 1 2023 1 \N 2023/2/2 5 4 2 2 1 2023 1 \N 2023/2/3 5 5 3 2 1 2023 1 \N 2023/2/4 5 6 4 2 1 2023 0 \N 2023/2/5 5 7 5 2 1 2023 0 \N 2023/2/6 6 1 6 2 1 2023 1 \N 2023/2/7 6 2 7 2 1 2023 1 \N 2023/2/8 6 3 8 2 1 2023 1 \N 2023/2/9 6 4 9 2 1 2023 1 \N 2023/2/10 6 5 10 2 1 2023 1 \N 2023/2/11 6 6 11 2 1 2023 0 \N 2023/2/12 6 7 12 2 1 2023 0 \N 2023/2/13 7 1 13 2 1 2023 1 \N 2023/2/14 7 2 14 2 1 2023 1 \N 2023/2/15 7 3 15 2 1 2023 1 \N 2023/2/16 7 4 16 2 1 2023 1 \N 2023/2/17 7 5 17 2 1 2023 1 \N 2023/2/18 7 6 18 2 1 2023 0 \N 2023/2/19 7 7 19 2 1 2023 0 \N 2023/2/20 8 1 20 2 1 2023 1 \N 2023/2/21 8 2 21 2 1 2023 1 \N 2023/2/22 8 3 22 2 1 2023 1 \N 2023/2/23 8 4 23 2 1 2023 1 \N 2023/2/24 8 5 24 2 1 2023 1 \N 2023/2/25 8 6 25 2 1 2023 0 \N 2023/2/26 8 7 26 2 1 2023 0 \N 2023/2/27 9 1 27 2 1 2023 1 \N 2023/2/28 9 2 28 2 1 2023 1 \N 2023/3/1 9 3 1 3 1 2023 1 \N 2023/3/2 9 4 2 3 1 2023 1 \N 2023/3/3 9 5 3 3 1 2023 1 \N 2023/3/4 9 6 4 3 1 2023 0 \N 2023/3/5 9 7 5 3 1 2023 0 \N 2023/3/6 10 1 6 3 1 2023 1 \N 2023/3/7 10 2 7 3 1 2023 1 \N 2023/3/8 10 3 8 3 1 2023 1 \N 2023/3/9 10 4 9 3 1 2023 1 \N 2023/3/10 10 5 10 3 1 2023 1 \N 2023/3/11 10 6 11 3 1 2023 0 \N 2023/3/12 10 7 12 3 1 2023 0 \N 2023/3/13 11 1 13 3 1 2023 1 \N 2023/3/14 11 2 14 3 1 2023 1 \N 2023/3/15 11 3 15 3 1 2023 1 \N 2023/3/16 11 4 16 3 1 2023 1 \N 2023/3/17 11 5 17 3 1 2023 1 \N 2023/3/18 11 6 18 3 1 2023 0 \N 2023/3/19 11 7 19 3 1 2023 0 \N 2023/3/20 12 1 20 3 1 2023 1 \N 2023/3/21 12 2 21 3 1 2023 1 \N 2023/3/22 12 3 22 3 1 2023 1 \N 2023/3/23 12 4 23 3 1 2023 1 \N 2023/3/24 12 5 24 3 1 2023 1 \N 2023/3/25 12 6 25 3 1 2023 0 \N 2023/3/26 12 7 26 3 1 2023 0 \N 2023/3/27 13 1 27 3 1 2023 1 \N 2023/3/28 13 2 28 3 1 2023 1 \N 2023/3/29 13 3 29 3 1 2023 1 \N 2023/3/30 13 4 30 3 1 2023 1 \N 2023/3/31 13 5 31 3 1 2023 1 \N 2023/4/1 13 6 1 4 2 2023 0 \N 2023/4/2 13 7 2 4 2 2023 0 \N 2023/4/3 14 1 3 4 2 2023 1 \N 2023/4/4 14 2 4 4 2 2023 1 \N 2023/4/5 14 3 5 4 2 2023 0 清明节 2023/4/6 14 4 6 4 2 2023 1 \N 2023/4/7 14 5 7 4 2 2023 1 \N 2023/4/8 14 6 8 4 2 2023 0 \N 2023/4/9 14 7 9 4 2 2023 0 \N 2023/4/10 15 1 10 4 2 2023 1 \N 2023/4/11 15 2 11 4 2 2023 1 \N 2023/4/12 15 3 12 4 2 2023 1 \N 2023/4/13 15 4 13 4 2 2023 1 \N 2023/4/14 15 5 14 4 2 2023 1 \N 2023/4/15 15 6 15 4 2 2023 0 \N 2023/4/16 15 7 16 4 2 2023 0 \N 2023/4/17 16 1 17 4 2 2023 1 \N 2023/4/18 16 2 18 4 2 2023 1 \N 2023/4/19 16 3 19 4 2 2023 1 \N 2023/4/20 16 4 20 4 2 2023 1 \N 2023/4/21 16 5 21 4 2 2023 1 \N 2023/4/22 16 6 22 4 2 2023 0 \N 2023/4/23 16 7 23 4 2 2023 1 \N 2023/4/24 17 1 24 4 2 2023 1 \N 2023/4/25 17 2 25 4 2 2023 1 \N 2023/4/26 17 3 26 4 2 2023 1 \N 2023/4/27 17 4 27 4 2 2023 1 \N 2023/4/28 17 5 28 4 2 2023 1 \N 2023/4/29 17 6 29 4 2 2023 0 劳动节 2023/4/30 17 7 30 4 2 2023 0 劳动节 2023/5/1 18 1 1 5 2 2023 0 劳动节 2023/5/2 18 2 2 5 2 2023 0 劳动节 2023/5/3 18 3 3 5 2 2023 0 劳动节 2023/5/4 18 4 4 5 2 2023 1 \N 2023/5/5 18 5 5 5 2 2023 1 \N 2023/5/6 18 6 6 5 2 2023 1 \N 2023/5/7 18 7 7 5 2 2023 0 \N 2023/5/8 19 1 8 5 2 2023 1 \N 2023/5/9 19 2 9 5 2 2023 1 \N 2023/5/10 19 3 10 5 2 2023 1 \N 2023/5/11 19 4 11 5 2 2023 1 \N 2023/5/12 19 5 12 5 2 2023 1 \N 2023/5/13 19 6 13 5 2 2023 0 \N 2023/5/14 19 7 14 5 2 2023 0 \N 2023/5/15 20 1 15 5 2 2023 1 \N 2023/5/16 20 2 16 5 2 2023 1 \N 2023/5/17 20 3 17 5 2 2023 1 \N 2023/5/18 20 4 18 5 2 2023 1 \N 2023/5/19 20 5 19 5 2 2023 1 \N 2023/5/20 20 6 20 5 2 2023 0 \N 2023/5/21 20 7 21 5 2 2023 0 \N 2023/5/22 21 1 22 5 2 2023 1 \N 2023/5/23 21 2 23 5 2 2023 1 \N 2023/5/24 21 3 24 5 2 2023 1 \N 2023/5/25 21 4 25 5 2 2023 1 \N 2023/5/26 21 5 26 5 2 2023 1 \N 2023/5/27 21 6 27 5 2 2023 0 \N 2023/5/28 21 7 28 5 2 2023 0 \N 2023/5/29 22 1 29 5 2 2023 1 \N 2023/5/30 22 2 30 5 2 2023 1 \N 2023/5/31 22 3 31 5 2 2023 1 \N 2023/6/1 22 4 1 6 2 2023 1 \N 2023/6/2 22 5 2 6 2 2023 1 \N 2023/6/3 22 6 3 6 2 2023 0 \N 2023/6/4 22 7 4 6 2 2023 0 \N 2023/6/5 23 1 5 6 2 2023 1 \N 2023/6/6 23 2 6 6 2 2023 1 \N 2023/6/7 23 3 7 6 2 2023 1 \N 2023/6/8 23 4 8 6 2 2023 1 \N 2023/6/9 23 5 9 6 2 2023 1 \N 2023/6/10 23 6 10 6 2 2023 0 \N 2023/6/11 23 7 11 6 2 2023 0 \N 2023/6/12 24 1 12 6 2 2023 1 \N 2023/6/13 24 2 13 6 2 2023 1 \N 2023/6/14 24 3 14 6 2 2023 1 \N 2023/6/15 24 4 15 6 2 2023 1 \N 2023/6/16 24 5 16 6 2 2023 1 \N 2023/6/17 24 6 17 6 2 2023 0 \N 2023/6/18 24 7 18 6 2 2023 0 \N 2023/6/19 25 1 19 6 2 2023 1 \N 2023/6/20 25 2 20 6 2 2023 1 \N 2023/6/21 25 3 21 6 2 2023 1 \N 2023/6/22 25 4 22 6 2 2023 0 端午节 2023/6/23 25 5 23 6 2 2023 0 端午节 2023/6/24 25 6 24 6 2 2023 0 端午节 2023/6/25 25 7 25 6 2 2023 1 \N 2023/6/26 26 1 26 6 2 2023 1 \N 2023/6/27 26 2 27 6 2 2023 1 \N 2023/6/28 26 3 28 6 2 2023 1 \N 2023/6/29 26 4 29 6 2 2023 1 \N 2023/6/30 26 5 30 6 2 2023 1 \N 2023/7/1 26 6 1 7 3 2023 0 \N 2023/7/2 26 7 2 7 3 2023 0 \N 2023/7/3 27 1 3 7 3 2023 1 \N 2023/7/4 27 2 4 7 3 2023 1 \N 2023/7/5 27 3 5 7 3 2023 1 \N 2023/7/6 27 4 6 7 3 2023 1 \N 2023/7/7 27 5 7 7 3 2023 1 \N 2023/7/8 27 6 8 7 3 2023 0 \N 2023/7/9 27 7 9 7 3 2023 0 \N 2023/7/10 28 1 10 7 3 2023 1 \N 2023/7/11 28 2 11 7 3 2023 1 \N 2023/7/12 28 3 12 7 3 2023 1 \N 2023/7/13 28 4 13 7 3 2023 1 \N 2023/7/14 28 5 14 7 3 2023 1 \N 2023/7/15 28 6 15 7 3 2023 0 \N 2023/7/16 28 7 16 7 3 2023 0 \N 2023/7/17 29 1 17 7 3 2023 1 \N 2023/7/18 29 2 18 7 3 2023 1 \N 2023/7/19 29 3 19 7 3 2023 1 \N 2023/7/20 29 4 20 7 3 2023 1 \N 2023/7/21 29 5 21 7 3 2023 1 \N 2023/7/22 29 6 22 7 3 2023 0 \N 2023/7/23 29 7 23 7 3 2023 0 \N 2023/7/24 30 1 24 7 3 2023 1 \N 2023/7/25 30 2 25 7 3 2023 1 \N 2023/7/26 30 3 26 7 3 2023 1 \N 2023/7/27 30 4 27 7 3 2023 1 \N 2023/7/28 30 5 28 7 3 2023 1 \N 2023/7/29 30 6 29 7 3 2023 0 \N 2023/7/30 30 7 30 7 3 2023 0 \N 2023/7/31 31 1 31 7 3 2023 1 \N 2023/8/1 31 2 1 8 3 2023 1 \N 2023/8/2 31 3 2 8 3 2023 1 \N 2023/8/3 31 4 3 8 3 2023 1 \N 2023/8/4 31 5 4 8 3 2023 1 \N 2023/8/5 31 6 5 8 3 2023 0 \N 2023/8/6 31 7 6 8 3 2023 0 \N 2023/8/7 32 1 7 8 3 2023 1 \N 2023/8/8 32 2 8 8 3 2023 1 \N 2023/8/9 32 3 9 8 3 2023 1 \N 2023/8/10 32 4 10 8 3 2023 1 \N 2023/8/11 32 5 11 8 3 2023 1 \N 2023/8/12 32 6 12 8 3 2023 0 \N 2023/8/13 32 7 13 8 3 2023 0 \N 2023/8/14 33 1 14 8 3 2023 1 \N 2023/8/15 33 2 15 8 3 2023 1 \N 2023/8/16 33 3 16 8 3 2023 1 \N 2023/8/17 33 4 17 8 3 2023 1 \N 2023/8/18 33 5 18 8 3 2023 1 \N 2023/8/19 33 6 19 8 3 2023 0 \N 2023/8/20 33 7 20 8 3 2023 0 \N 2023/8/21 34 1 21 8 3 2023 1 \N 2023/8/22 34 2 22 8 3 2023 1 \N 2023/8/23 34 3 23 8 3 2023 1 \N 2023/8/24 34 4 24 8 3 2023 1 \N 2023/8/25 34 5 25 8 3 2023 1 \N 2023/8/26 34 6 26 8 3 2023 0 \N 2023/8/27 34 7 27 8 3 2023 0 \N 2023/8/28 35 1 28 8 3 2023 1 \N 2023/8/29 35 2 29 8 3 2023 1 \N 2023/8/30 35 3 30 8 3 2023 1 \N 2023/8/31 35 4 31 8 3 2023 1 \N 2023/9/1 35 5 1 9 3 2023 1 \N 2023/9/2 35 6 2 9 3 2023 0 \N 2023/9/3 35 7 3 9 3 2023 0 \N 2023/9/4 36 1 4 9 3 2023 1 \N 2023/9/5 36 2 5 9 3 2023 1 \N 2023/9/6 36 3 6 9 3 2023 1 \N 2023/9/7 36 4 7 9 3 2023 1 \N 2023/9/8 36 5 8 9 3 2023 1 \N 2023/9/9 36 6 9 9 3 2023 0 \N 2023/9/10 36 7 10 9 3 2023 0 \N 2023/9/11 37 1 11 9 3 2023 1 \N 2023/9/12 37 2 12 9 3 2023 1 \N 2023/9/13 37 3 13 9 3 2023 1 \N 2023/9/14 37 4 14 9 3 2023 1 \N 2023/9/15 37 5 15 9 3 2023 1 \N 2023/9/16 37 6 16 9 3 2023 0 \N 2023/9/17 37 7 17 9 3 2023 0 \N 2023/9/18 38 1 18 9 3 2023 1 \N 2023/9/19 38 2 19 9 3 2023 1 \N 2023/9/20 38 3 20 9 3 2023 1 \N 2023/9/21 38 4 21 9 3 2023 1 \N 2023/9/22 38 5 22 9 3 2023 1 \N 2023/9/23 38 6 23 9 3 2023 0 \N 2023/9/24 38 7 24 9 3 2023 0 \N 2023/9/25 39 1 25 9 3 2023 1 \N 2023/9/26 39 2 26 9 3 2023 1 \N 2023/9/27 39 3 27 9 3 2023 1 \N 2023/9/28 39 4 28 9 3 2023 1 \N 2023/9/29 39 5 29 9 3 2023 0 中秋节 2023/9/30 39 6 30 9 3 2023 0 中秋节 2023/10/1 39 7 1 10 4 2023 0 国庆节 2023/10/2 40 1 2 10 4 2023 0 国庆节 2023/10/3 40 2 3 10 4 2023 0 国庆节 2023/10/4 40 3 4 10 4 2023 0 国庆节 2023/10/5 40 4 5 10 4 2023 0 国庆节 2023/10/6 40 5 6 10 4 2023 0 国庆节 2023/10/7 40 6 7 10 4 2023 1 \N 2023/10/8 40 7 8 10 4 2023 1 \N 2023/10/9 41 1 9 10 4 2023 1 \N 2023/10/10 41 2 10 10 4 2023 1 \N 2023/10/11 41 3 11 10 4 2023 1 \N 2023/10/12 41 4 12 10 4 2023 1 \N 2023/10/13 41 5 13 10 4 2023 1 \N 2023/10/14 41 6 14 10 4 2023 0 \N 2023/10/15 41 7 15 10 4 2023 0 \N 2023/10/16 42 1 16 10 4 2023 1 \N 2023/10/17 42 2 17 10 4 2023 1 \N 2023/10/18 42 3 18 10 4 2023 1 \N 2023/10/19 42 4 19 10 4 2023 1 \N 2023/10/20 42 5 20 10 4 2023 1 \N 2023/10/21 42 6 21 10 4 2023 0 \N 2023/10/22 42 7 22 10 4 2023 0 \N 2023/10/23 43 1 23 10 4 2023 1 \N 2023/10/24 43 2 24 10 4 2023 1 \N 2023/10/25 43 3 25 10 4 2023 1 \N 2023/10/26 43 4 26 10 4 2023 1 \N 2023/10/27 43 5 27 10 4 2023 1 \N 2023/10/28 43 6 28 10 4 2023 0 \N 2023/10/29 43 7 29 10 4 2023 0 \N 2023/10/30 44 1 30 10 4 2023 1 \N 2023/10/31 44 2 31 10 4 2023 1 \N 2023/11/1 44 3 1 11 4 2023 1 \N 2023/11/2 44 4 2 11 4 2023 1 \N 2023/11/3 44 5 3 11 4 2023 1 \N 2023/11/4 44 6 4 11 4 2023 0 \N 2023/11/5 44 7 5 11 4 2023 0 \N 2023/11/6 45 1 6 11 4 2023 1 \N 2023/11/7 45 2 7 11 4 2023 1 \N 2023/11/8 45 3 8 11 4 2023 1 \N 2023/11/9 45 4 9 11 4 2023 1 \N 2023/11/10 45 5 10 11 4 2023 1 \N 2023/11/11 45 6 11 11 4 2023 0 \N 2023/11/12 45 7 12 11 4 2023 0 \N 2023/11/13 46 1 13 11 4 2023 1 \N 2023/11/14 46 2 14 11 4 2023 1 \N 2023/11/15 46 3 15 11 4 2023 1 \N 2023/11/16 46 4 16 11 4 2023 1 \N 2023/11/17 46 5 17 11 4 2023 1 \N 2023/11/18 46 6 18 11 4 2023 0 \N 2023/11/19 46 7 19 11 4 2023 0 \N 2023/11/20 47 1 20 11 4 2023 1 \N 2023/11/21 47 2 21 11 4 2023 1 \N 2023/11/22 47 3 22 11 4 2023 1 \N 2023/11/23 47 4 23 11 4 2023 1 \N 2023/11/24 47 5 24 11 4 2023 1 \N 2023/11/25 47 6 25 11 4 2023 0 \N 2023/11/26 47 7 26 11 4 2023 0 \N 2023/11/27 48 1 27 11 4 2023 1 \N 2023/11/28 48 2 28 11 4 2023 1 \N 2023/11/29 48 3 29 11 4 2023 1 \N 2023/11/30 48 4 30 11 4 2023 1 \N 2023/12/1 48 5 1 12 4 2023 1 \N 2023/12/2 48 6 2 12 4 2023 0 \N 2023/12/3 48 7 3 12 4 2023 0 \N 2023/12/4 49 1 4 12 4 2023 1 \N 2023/12/5 49 2 5 12 4 2023 1 \N 2023/12/6 49 3 6 12 4 2023 1 \N 2023/12/7 49 4 7 12 4 2023 1 \N 2023/12/8 49 5 8 12 4 2023 1 \N 2023/12/9 49 6 9 12 4 2023 0 \N 2023/12/10 49 7 10 12 4 2023 0 \N 2023/12/11 50 1 11 12 4 2023 1 \N 2023/12/12 50 2 12 12 4 2023 1 \N 2023/12/13 50 3 13 12 4 2023 1 \N 2023/12/14 50 4 14 12 4 2023 1 \N 2023/12/15 50 5 15 12 4 2023 1 \N 2023/12/16 50 6 16 12 4 2023 0 \N 2023/12/17 50 7 17 12 4 2023 0 \N 2023/12/18 51 1 18 12 4 2023 1 \N 2023/12/19 51 2 19 12 4 2023 1 \N 2023/12/20 51 3 20 12 4 2023 1 \N 2023/12/21 51 4 21 12 4 2023 1 \N 2023/12/22 51 5 22 12 4 2023 1 \N 2023/12/23 51 6 23 12 4 2023 0 \N 2023/12/24 51 7 24 12 4 2023 0 \N 2023/12/25 52 1 25 12 4 2023 1 \N 2023/12/26 52 2 26 12 4 2023 1 \N 2023/12/27 52 3 27 12 4 2023 1 \N 2023/12/28 52 4 28 12 4 2023 1 \N 2023/12/29 52 5 29 12 4 2023 1 \N 2023/12/30 52 6 30 12 4 2023 0 \N 2023/12/31 52 7 31 12 4 2023 0 \N