dmfldr(DM Fast Loader)是达梦数据库提供的快速数据装载命令行工具。用户通过使用dmfldr工具能够把一定格式的文本数据以简单、快速、高效的方式导入到达梦数据库中,或把达梦数据库中的数据按照一定格式导出到文本文件。其功能与Oracle的sqlldr类似。比如在实际业务应用中,假设A系统因为业务场景需要做数据分析,此时需要B系统的用户行为数据,如果实时性要求不高,而B系统数据量又很大时,可以使用dmfldr数据装载方式同步数据,即B系统导出该表部分字段数据给A系统,A系统导入后做数据分析。
dmfldr工具分为服务端和客户端。当进行数据导入时,dmfldr客户端接收用户提交的命令与参数,分析控制文件与数据文件,将数据打包发送给服务器端的dmfldr模块,由服务器完成数据的装载工作,装载完成后,客户端分析服务器返回的消息,根据用户参数生成日志文件与错误数据文件。当进行数据导出时,dmfldr客户端解析命令与参数,分析控制文件,将用户要求转换成相应消息发送给服务器端的dmfldr模块,服务器解析并打包导出需要的数据,发送给dmfldr客户端,客户端将数据写入指定的数据文件,并根据用户参数指定生成日志文件。
本文分别从数据导入和导出两方面介绍dmfldr使用方法。
dmfldr参数简介
dmfldr命令有USERID和控制文件两个必选参数。其中USERID用来指定用户名和密码等以登录数据库服务器;控制文件用来指定导入或导出的表、数据文件、行分隔符、列分隔符等信息。注意USERID必须是命令行中的第一个参数,CONTROL 必须是命令行中的第二个参数。
dmfldr提供了方便的命令行参数帮助命令。进入达梦数据库安装bin目录下,执行dmfldr help就可查看命令行参数说明。dmfldr命令行参数语法格式如下。
dmfldr控制文件
控制文件是启动dmfldr的必选参数,也是数据装载的核心参数文件。其作用用于指定数据装载的表名、数据文件、行分隔符、列分隔符等信息。在数据导入时,dmfldr根据控制文件指定的格式来解析数据文件;导出数据时,dmfldr根据控制文件指定的列名、分隔符等生成数据文件。dmfldr控制文件的语法如下。
(1)OPTIONS可指定除userid,control,help以外的其他所有参数,每个参数及参数值对使用空格或者换行区分。对于OPTIONS中指定的参数,如在dmfldr命令行也指定时,dmfldr将优先选择OPTIONS中对应参数的值执行。
(2)INFILE子句用来指定被装载的数据文件信息。包含数据文件路径、文件名称、行分隔符等信息。INFILE子句语法定义如下:
①<file_option>用于指定单个或多个数据文件。指定多个数据文件时,用逗号分割。可使用LIST选项,当使用LIST时,表明实际的数据文件路径存储在LIST指定的文件中,该文件可以存储多个实际的数据文件路径,使用逗号或者换行区分。
②<directory_option>用来指定整个文件夹。指定此选项后,dmfldr会自动扫描指定文件夹下的所有文件,这些文件数据将被导入到服务器。
③<row_term_option>用于指定数据文件行分隔符。windows系统默认为0x0D0A(\r\n),非windows系统默认为0x0A(\n);用户可根据需要自行调整。若指定的分割符为十六进制的字符串值则需要指明[X]选项,此时<delimiter>值不再需要以0x开头。若没有指明[X]选项,则<delimiter>值为指定的字符串。
(3)BADFILE用于指定错误数据文件。错误数据文件用于记录加载过程产生错误的数据(仅记录数据解析中出现的错误,对于数据库字段约束等原因导致的错误数据不记录),是可选参数,仅对MODE='IN'即导入模式有效,也可在执行dmfldr命令或者OPTIONS参数中指定。当数据类型和数据的编码转换中存在错误数据,而错误数在允许的最大错误数ERRORS参数值范围内时,dmfldr会将出错的数据记录到BADFILE指定的错误数据文件中。文件记录的信息为执行程序、时间、目标表、数据文件中存在格式错误的行数据以及转换出错的行数据。
(4)APPEND|REPLACE|INSERT选项,表示数据装载时采用的加载方式。
MODE='IN'导入数据模式时,INSERT为插入方式,向空表插入新记录(如果不是空表则会报错无效的装载模式);APPEND为追加方式,在表中追加新记录;REPLACE为替代方式,先清空表再插入新记录。
MODE='OUT'导出数据模式时,设置为APPEND时,dmfldr会检查导出数据文件是否存在,若存在,则以追加的方式写入数据文件;若不存在,则新建数据文件;设置为其他值时,将直接创建新数据文件。默认值为APPEND。
(5)<into_table_clause>用于指定INTO TABLE子句,可以指定一个或多个INTO TABLE子句,用于将一批数据同时向一个或多个表进行装载。每个INTO TABLE子句中都可以指定要装载的表、WHEN过滤条件、FIELDS子句和列定义子句等。<into_table_clause>子句语法定义如下:
其中:
①[<schema>.]<tablename>表示模式名和表名,其中模式名可省略。
②EP <ep_option>子句用于指定数据将要发送的目的站点,仅适用于MPP环境。其格式如下:
③WHEN <field_conditions>子句用于指定条件过滤表达式,可以在装载时对数据进行过滤,符合<field_conditions>条件的数据才会被装载。其语法定义如下:
其操作符仅支持=、!=和<>这三个比较操作符;仅支持使用AND连接多个过滤条件;BLANKS和WHITESPACE表示若干个空格。
④FIELDS子句用于指定列分隔符。若分隔符指明[X]选项,则表明此分隔符为十六进制格式的字符串。FIELDS子句语法定义如下:
⑤<enclosed_option>选项用于指定列封闭符。为可选参数,默认不存在封闭符;若封闭符前指定[X]选项,则表明此封闭符为十六进制格式的字符串,分隔符或封闭符字符串的长度均不能超过255个字节。<enclosed_option>语法定义如下:
⑥<coldef_option>是列定义子句,用于指定列名、列属性、列分隔符、列封闭符等。<coldef_option>相关语法定义如下:
其中:
<col_def>中FILLER表示跳过处理数据文件中指定列的值。
<property_option>选项,用于指定数据的相对位置,该参数仅对导入有效。
position(p1:p2):从数据文件中每行数据的第p1个字节到第p2个字节为该列值,包含边界p1,p2;position(p1):从数据文件中每行数据的第p1个字节开始,到下一个列分隔符之间的数据为该列值,包含边界p1;NULL:指定值为NULL,此时忽略数据文件中的值。position选项对大字段数据无效,若对大字段类型指定此选项会报错。
<fmt_option>选项用来指定时间格式。
<term_option>选项用来指定数据文件中指定列的结束标志(列分隔符)。列的结束标志可以是WHITESPACE(空格)或者用户自定义的字符串或十六进制串。
<enclosed_option>选项用于指定列封闭符,前面已有介绍,这里不再累述。列分隔符和封闭符同时可在FIELDS子句中指定;若在FIELDS子句和<coldef_option>子句中均设置了列分隔符或封闭符,则以<coldef_option>中的设置为准。
<constant_option>选项用来指定常量表达式,当指定为常量时,数据装载时该列将以常量值装载。
<fun_option>选项,目前只支持trim()和replace(colname, srcStr, destStr)函数。当使用<fun_option>选项后,数据将使用函数处理后再进行装载。
下面分别从导入和导出两方面介绍控制文件的编写和dmfldr使用方法。
dmfldr数据导出
下面以导出DMHR用户下EMPLOYEE表数据为例,介绍dmfldr的数据导出方法。
第1步 准备测试表和数据
查看EMPLOYEE表结构,表结构定义如下:
SQL> desc employee;
查看EMPLOYEE表数据,这里展示部分数据样例:
查看表结构和数据是为编写控制文件做准备。控制文件中定义了列名称;对于时间类型字段,建议根据实际业务需要指定format格式。
第2步 编写控制文件
根据表结构编写控制文件。其中,导出数据文件指定为D盘test目录下employee.csv文件,导出表名为EMPLOYEE,列分割隔符为“|”,行分隔符保持默认,IDENTITY_CARD字段使用双引号引起来(指定封闭符“"”),HIRE_DATE字段格式指定为“YYYY-MM-DD”,编写完成后保存至控制文件相同目录下,命令为out_employee.ctrl。内容参考如下:
第3步 执行导出命令
使用dmfldr命令导出数据,执行命令参考如下(注意,导出需指定mode='out'):
执行结果显示如下:
第4步 查看导出数据文件
导出完成后,查看控制文件中指定的数据文件D:/test/employee.csv,核对导出数据是否准确,记录是否完整。如下给出导出数据文件的部分样例数据:
dmfldr数据导入
当第三方系统提供给我们数据文件及相关字段定义时,我们需要根据数据文件定义新表或在已有的业务表中做数据导入。这里以模拟同时将多个同类型数据文件分别导入一张表来介绍dmfldr的数据导入方法。
第1步 准备数据文件和测试表
假设我们有多个数据文件,分别为test001.txt、test002.txt和test003.txt等(这里以三个文件为例)。现要求将此三个文本文件导入到test表中。
新建test.txt的文件,将数据文件名和路径都存放在此文件中,内容如下:
test001.txt数据文件内容如下:
test002.txt数据文件内容如下:
test003.txt数据文件内容如下:
test表结构如下:
第2步 编写控制文件
导入要求如下:TEST表有6个字段,分别是F1~F6,数据文件以“|”分割,第一列编码插入F1字段,第二列是时间类型,插入F2字段,F3字段要求插入空值,F4字段插入第4列双引号中的数据(控制文件需指定封闭符),F5字段插入固定值test,F6字段插入第4列剩余部分并去掉数据两头多余空格。
根据数据文件和导入要求编写控制文件test.ctrl。其中OPTIONS选项中定义了SKIP、ROWS、DIRECT和INDEX_OPTION参数;定义了一个LOAD项,LOAD项中指定了数据文件(因为多个数据文件包含在test.txt文件中,所以INFILE中指定了LIST选项)、BADFILE文件、操作的数据库表、行分隔符、列定义以及列分隔符等。控制文件内容如下所示。
第3步 执行导入命令
使用dmfldr命令导入数据,执行命令参考如下:
执行结果显示如下:
第4步 查看导入结果
从上述执行结果中可以看出,10条导入成功,1条数据导入失败。test.bad中记录了导入失败的数据内容,内容展示如下:
可以看出,因为时间数据异常的原因导致该记录没有正常导入。导入完成后的test表数据查询如下:
假如我们重复执行一次导入动作,此时由于TEST表中F1为主键,主键值冲突导致数据导入失败。从日志中可以看出数据0行加载,但此时test.bad中仍然只记录了时间异常的那条数据,其他导入失败的数据未计入。这是因为BADFILE只记录数据解析失败的数据,对于约束等原因导致的数据库插入失败,BADFILE中不记录。