相关问题
相关资料
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的使用
Timestamp在GBase8a集群中是时间戳类型,必须指定默认值,且每个表中只允许一个timestamp类型指定默认值为当前日期时间(CURRENT_TIMESTAMP);
在一些oracle等数据库迁移中,如不熟悉GBase8a集群的日期时间类型情况,会直接将Oracle的timestamp迁移为GBase 8a的timestamp类型,一是会造成时间精度的丢失,二是GBase8a的timestamp默认会直接设置第一个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小时制,个位数字前加0(01...12) |
%i | 分钟,数字形式(00...59) |
%j | 一年中的天数(001...366) |
%k | 小时,24小时制(0...23) |
%l | 小时,12小时制(1...12) |
%M | 月份,英文形式全拼(January...December) |
%m | 月份,数字形式(00...12) |
%p | AM或PM |
%r | 时间,12小时制(HH:MI:SS后面紧跟AM或PM) |
%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