注册
 登录
  1. 当前位置：
  2. 首页
  3. 论坛
  4. GBASE数据库问答专区
  5. GBase 8a问题解决宝典
  6. GBase8a集群的日期时间类型格式加载

提问者

iLeLe
发布26
回答141
关注

相关问题

  • 加载千余个几兆到几十兆之间的行数少列数多的小文件...
  • sftp加载大文件报错
  • 数据文件中空格加载入not null的表列中
  • tmp目录权限改变导致加载失败
  • GBase 8a86版本大规模集群加载服务核心功能
  • 86集群版本使用.net模拟http服务器进行加载报错
  • windows平台的csv文件在linux系统加载出现一条错误数据不...
  • GBase加载数据时的数据落地问题
  • 加载过程中dispserver服务消失问题
  • 加载时数据文件的“null”如何替换成"\n"？

    • 相关资料

    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的使用
    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


    关注问题 0
    回答0
    评论

    回答 (0)
    按赞同排序
    按时间排序

    撰写回答

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