datetime 日期格式 加载 发表于 2020-07-18 阅读 816回复 0

GBase8a集群的日期时间类型格式加载

GBase8a集群的日期时间类型格式加载
GBase 8a MPP Cluster是基于GBase 8a列存储数据库的一款Shared Nothing 架构的分布式并行数据库集群,主要的应用场景是离线大数据分析,如各类数据仓库系统、BI系统和决策支持系统。
GBase8a集群的目标场景中的数据都是离线历史数据,数据类型主要以数值类型、varchar类型、日期时间类型为主,这里单独来说下日期时间类型,如问题最多的,应该选择datetime还是选择timestamp?各中日期格式的数据如何加载入库?
l GBase8a集群支持的日期时间类型有:
类型名称
最小值
最大值
格式
DATE
0001-01-01
9999-12-31
YYYY-MM-dd
DATETIME
[size=10.5000pt]000[size=10.5000pt]1-01-01 00:00:00.000000
9999-12-31 23:59:59.999999
YYYY-MM-dd HH:M[size=10.5000pt]I:SS.ffffff
TIME
-838:59:59
838:59:59
HHH:M[size=10.5000pt]I:SS
TIMESTAMP
1970-01-01 0[size=10.5000pt]8:00:0[size=10.5000pt]1
2038-01-01 00:59:59
YYYY-MM[size=10.5000pt]-DD HH:M[size=10.5000pt]I:SS
l 日期时间的精度
Datetime类型支持六位的时间精度,可以到纳秒;
Timestamp类型只能支持到秒;
l Timestamp的使用
TimestampGBase8a集群中是时间戳类型,必须指定默认值,且每个表中只允许一个timestamp类型指定默认值为当前日期时间(CURRENT_TIMESTAMP);
在一些oracle等数据库迁移中,如不熟悉GBase8a集群的日期时间类型情况,会直接将Oracletimestamp迁移为GBase 8atimestamp类型,一是会造成时间精度的丢失,二是GBase8atimestamp默认会直接设置第一个timestamp字段的默认值为CURRENT_TIMESTAMP,如该表有第二个字段真实需要时间戳属性时,就会造成困扰。
l 日期、时间类型字段的格式
默认的日期、时间字段数据的格式见上表。
在查询展示时,可以通过date_format()to_date()to_char()等函数进行日期、时间数据的格式化。
在数据入库时,insert values日期格式较为宽泛,可以是年月日时分秒的数值数据,可以是各类分隔符,如下面的三个数据都可以通过insert values插入到定义为datetime类型的字段中
20200111120002
'2020-01-11 12/00/03'
'2020/11/11 12/00/03'
l 日期数据的加载
进入主题,对于日期、时间类型数据,其具有多种数据格式,在加载时可以使用DATETIME|TIMESTAMP|DATE|TIME FORMAT format进行日期\时间格式的匹配与定义,不同类型的字段使用不同的FORMAT,如datetime类型字段使用TIMESTAMP FORMAT会没有效果。
格  式
   
%a
星期名的英文缩写形式(Sun...Sat
%b
月份的英文缩写形式(Jan...DEC
%c
月份的数字形式(0...12
%D
有英文后缀的某月的第几天(0th, 1st, 2nd, 3rd...
%d
月份中的天数,数字形式(00...31
%e
月份中的天数,数字形式(0...31
%f
微秒(000000...999999
%H
小时,24小时制(00...23
%h
小时,12小时制(0,1...12
%I
小时,12小时制,个位数字前加001...12
%i
分钟,数字形式(00...59
%j
一年中的天数(001...366
%k
小时,24小时制(0...23
%l
小时,12小时制(1...12
%M
月份,英文形式全拼(January...December
%m
月份,数字形式(00...12
%p
AMPM
%r
时间,12小时制(HH:MI:SS后面紧跟AMPM
%S
秒(00...59
%s
秒(00...59
%T
时间,24小时(HH:MI:SS
%U
星期(00...53),星期日是一个星期的第一天
%u
星期(00...53),星期一是一个星期的第一天
%V
星期(01...53),星期日是一个星期的第一天。
与“%X”一起使用
%v
星期(01...53),星期一是一个星期的第一天。
与“%x”一起使用
%W
星期名的英文全拼形式(Sunday...Saturday
%w
一星期中的哪一天(0=Sunday...6=Saturday
%X
4位数字形式反映周所在的年份,星期日周的第一天
%x
4位数字形式反映周所在的年份,星期日周的第一天
%Y
4位数字形式表达的年份
%y
2位数字形式表达的年份
%%
一个字母“%
%.
除字母、数字和空格外的一个或多个字符
%@
一个或多个字母
%#
一个或多个数字
下面以举例的方式介绍日期时间格式的加载,所使用的的数据样例为dt123.txt文件,内容如下:
[gbase@pst_w160 test]$ cat dt123.txt
2020-05-31 21:10:01.654321|1
2020-06-12 10:00:01|2
|3
|4
null|5
2020-07-01 01/22/10|6
2019.06.18 21:01:50.123456|7
测试表结构如下:
gbase> create table dt1(d1 datetime,id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.05)
gbase> create table dt2(ts1 timestamp DEFAULT CURRENT_TIMESTAMP, id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.04)
gbase> CREATE TABLE dt3(d3 datetime default '2020-01-01 12:00:00',id int);
Query OK, 0 rows affected (Elapsed: 00:00:00.03)
每次数据加载前,先删除表中的数据。
Ø 日期时间的默认加载和指定格式加载
指定及不指定日期时间数据格式
gbase> load data infile 'sftp://gbase:gbase@172.16.3.160/home/gbase/test/dt123.txt' into table test.dt1 FIELDS TERMINATED BY '|' ;
Query OK, 3 rows affected (Elapsed: 00:00:00.70)
Task 486416 finished, Loaded 3 records, Skipped 4 records
gbase> select * from dt1;
+---------------------+------+
| d1                  | id   |
+---------------------+------+
| NULL                |    3 |
| 2020-06-12 10:00:01 |    2 |
| NULL                |    4 |
+---------------------+------+
3 rows in set (Elapsed: 00:00:00.02)
-- 入库数据包含空值数据,默认情况下空串被认为是null值,也可以在加载时通过null_values指定NULL值。
gbase> load data infile 'sftp://gbase:gbase@172.16.3.160/home/gbase/test/dt123.txt' into table test.dt1 FIELDS TERMINATED BY '|' DATETIME FORMAT '%Y-%m-%d %H:%i:%s.%f' ;
Query OK, 4 rows affected (Elapsed: 00:00:00.68)
Task 486419 finished, Loaded 4 records, Skipped 3 records
gbase> select * from dt1;
+----------------------------+------+
| d1                         | id   |
+----------------------------+------+
| 2020-06-12 10:00:01        |    2 |
| NULL                       |    4 |
| 2020-05-31 21:10:01.654321 |    1 |
| NULL                       |    3 |
+----------------------------+------+
4 rows in set (Elapsed: 00:00:00.01)
gbase> load data infile 'sftp://gbase:gbase@172.16.3.160/home/gbase/test/dt123.txt' into table test.dt2 FIELDS TERMINATED BY '|' TIMESTAMP FORMAT '%Y-%m-%d %H/%i/%s' ;
Query OK, 3 rows affected (Elapsed: 00:00:00.78)
Task 486423 finished, Loaded 3 records, Skipped 4 records
gbase> select * from dt2;
+---------------------+------+
| ts1                 | id   |
+---------------------+------+
| 2021-10-11 05:10:15 |    3 |
| 2020-07-01 01:22:10 |    6 |
| 2021-10-11 05:10:15 |    4 |
+---------------------+------+
3 rows in set (Elapsed: 00:00:00.01)
-- id=6的数据2020-07-01 01/22/10入库,如使用DATETIME FORMAT而不是TIMESTAMP FORMAT,则入库数据为默认格式的日期数据和空值数据。
gbase> load data infile 'sftp://gbase:gbase@172.16.3.160/home/gbase/test/dt123.txt' into table test.dt3 FIELDS TERMINATED BY '|' DATETIME FORMAT '%Y-%m-%d %H:%i:%s.%f' ;
Query OK, 4 rows affected (Elapsed: 00:00:00.70)
Task 486432 finished, Loaded 4 records, Skipped 3 records
gbase> select * from dt3;
+----------------------------+------+
| d3                         | id   |
+----------------------------+------+
| 2020-05-31 21:10:01.654321 |    1 |
| 2020-01-01 12:00:00        |    3 |
| 2020-06-12 10:00:01        |    2 |
| 2020-01-01 12:00:00        |    4 |
+----------------------------+------+
4 rows in set (Elapsed: 00:00:00.02)
-- 当日期数据有默认值时,会将null值替换为默认值数据
Ø 按列指定日期时间数据格式的加载
加载时,可以通过TABLE_FIELDS指定日期时间列的格式,适用于一个表中有多个datetime列,且数据格式不统一
gbase> load data infile 'sftp://gbase:gbase@172.16.3.160/home/gbase/test/dt123.txt' into table test.dt1 FIELDS TERMINATED BY '|' TABLE_FIELDS 'd1 date "%Y-%m-%d %H/%i/%s",id';
Query OK, 3 rows affected (Elapsed: 00:00:00.65)
Task 486427 finished, Loaded 3 records, Skipped 4 records
gbase> select * from dt1;
+---------------------+------+
| d1                  | id   |
+---------------------+------+
| NULL                |    4 |
| NULL                |    3 |
| 2020-07-01 01:22:10 |    6 |
+---------------------+------+
3 rows in set (Elapsed: 00:00:00.02)
Ø 日期格式只有分隔符不同时的加载
样例数据中,2020-06-12 10:00:01、2020-07-01 01/22/10、2019.06.18 21:01:50.123456三个数据格式不一致但只有分隔符不同,且都是一个字段的数据,按照前面举例,因加载时指定的日期格式为"%Y-%m-%d %H:%i:%s",导致数据加载时不符合该格式的数据作为错误数据被skipped。
GBase 8a集群提供了%.和%@来处理这种场景。
gbase> load data infile 'sftp://gbase:gbase@172.16.3.160/home/gbase/test/dt123.txt' into table test.dt1 FIELDS TERMINATED BY '|' DATETIME FORMAT '%Y%.%m%.%d %H%.%i%.%s.%f' ;
Query OK, 6 rows affected (Elapsed: 00:00:00.82)
Task 486439 finished, Loaded 6 records, Skipped 1 records
gbase> select * from dt1;
+----------------------------+------+
| d1                         | id   |
+----------------------------+------+
| 2020-06-12 10:00:01        |    2 |
| NULL                       |    4 |
| 2019-06-18 21:01:50.123456 |    7 |
| 2020-05-31 21:10:01.654321 |    1 |
| NULL                       |    3 |
| 2020-07-01 01:22:10        |    6 |
+----------------------------+------+
6 rows in set (Elapsed: 00:00:00.02)
如果数据中包含有字母,则需要使用%@来处理,如下用例
gbase> load data infile 'sftp://gbase:gbase@172.16.3.160/home/gbase/test/dt124.txt' into table test.dt1 FIELDS TERMINATED BY '|' DATETIME FORMAT '%Y%@%m%@%d%@ %H%@%i%@%s%@';
Query OK, 1 row affected (Elapsed: 00:00:02.57)
Task 486451 finished, Loaded 1 records, Skipped 0 records
gbase> select * from dt1;
+---------------------+------+
| d1                  | id   |
+---------------------+------+
| 2015-05-31 21:01:50 |    8 |
+---------------------+------+
1 row in set (Elapsed: 00:00:00.02)
gbase> system cat ~/test/dt124.txt
2015year05M31d 21h01m50sec|8


回答 (0)

撰写回答

您需要登录后才可以回帖 登录 | 立即注册