更新时间:2022-08-12 GMT+08:00

导入数据

功能描述

LOAD DATA可用于导入CSV、Parquet、ORC、JSON、Avro格式的数据,内部将转换成Parquet数据格式进行存储。

语法格式

1
2
LOAD DATA INPATH 'folder_path' INTO TABLE [db_name.]table_name
  OPTIONS(property_name=property_value, ...);

关键字

  • INPATH:数据路径。
  • OPTIONS:属性列表。

参数说明

表1 参数描述

参数

描述

folder_path

原始数据文件夹或者文件的OBS路径。

db_name

数据库名称。若未指定,则使用当前数据库。

table_name

需要导入数据的DLI表的名称。

以下是可以在导入数据时使用的配置选项:

  • DATA_TYPE: 指定导入的数据类型,当前支持CSV、Parquet、ORC、JSON、Avro类型,默认值为“CSV”

    配置项为OPTIONS('DATA_TYPE'='CSV')

    导入CSV和JSON文件时,有三种模式可以选择:

    • PERMISSIVE:选择PERMISSIVE模式时,如果某一列数据类型与目标表列数据类型不匹配,则该行数据将被设置为null。
    • DROPMALFORMED:选择DROPMALFORMED模式时,如果某一列数据类型与目标表列数据类型不匹配,则不导入该行数据。
    • FAILFAST:选择FAILFAST模式时,如果某一列类型不匹配,则会抛出异常,导入失败。

    模式设置可通过在OPTIONS中添加 OPTIONS('MODE'='PERMISSIVE')进行设置。

  • DELIMITER:可以在导入命令中指定分隔符,默认值为“,”

    配置项为OPTIONS('DELIMITER'=',')。

    对于CSV数据,支持如下所述分隔符:

    • 制表符tab,例如:'DELIMITER'='\t'。
    • 任意的二进制字符,例如:'DELIMITER'='\u0001(^A)'。
    • 单引号('),单引号必须在双引号(" ")内。例如:'DELIMITER'= "'"。
    • DLI表还支持\001(^A)和\017(^Q),例如:'DELIMITER'='\001(^A)','DELIMITER'='\017(^Q)'。
  • QUOTECHAR:可以在导入命令中指定引号字符。默认值为"

    配置项为OPTIONS('QUOTECHAR'='"')

  • COMMENTCHAR:可以在导入命令中指定注释字符。在导入操作期间,如果在行的开头遇到注释字符,那么该行将被视为注释,并且不会被导入。默认值为#。

    配置项为OPTIONS('COMMENTCHAR'='#')

  • HEADER:用来表示源文件是否有表头。取值范围为“true”“false”“true”表示有表头,“false”表示无表头。默认值为“false”。如果没有表头,可以在导入命令中指定FILEHEADER参数提供表头。

    配置项为OPTIONS('HEADER'='true')

  • FILEHEADER:如果源文件中没有表头,可在LOAD DATA命令中提供表头。

    OPTIONS('FILEHEADER'='column1,column2')

  • ESCAPECHAR:如果用户想在CSV上对Escape字符进行严格验证,可以提供Escape字符。默认值为“\\”
    配置项为OPTIONS('ESCAPECHAR'='\\')

    如果在CSV数据中输入ESCAPECHAR,该ESCAPECHAR必须在双引号(" ")内。例如:"a\b"。

  • MAXCOLUMNS:该可选参数指定了在一行中,CSV解析器解析的最大列数。

    配置项为OPTIONS('MAXCOLUMNS'='400')

    表2 MAXCOLUMNS

    可选参数名称

    默认值

    最大值

    MAXCOLUMNS

    2000

    20000

    设置MAXCOLUMNS Option的值后,导入数据会对executor的内存有要求,所以导入数据可能会由于executor内存不足而失败。

  • DATEFORMAT:指定列的日期格式。

    OPTIONS('DATEFORMAT'='dateFormat')

    • 默认值为:yyyy-MM-dd。
    • 日期格式由Java的日期模式字符串指定。在Java的日期和时间模式字符串中,未加单引号(')的字符'A' 到'Z' 和'a' 到'z' 被解释为模式字符,用来表示日期或时间字符串元素。若模式字符使用单引号 (') 引起来,则在解析时只进行文本匹配,而不进行解析。Java模式字符定义请参见表3
    表3 日期及时间模式字符定义

    模式字符

    日期或时间元素

    示例

    G

    纪元标识符

    AD

    y

    年份

    1996; 96

    M

    月份

    July; Jul; 07

    w

    年中的周数

    27(该年的第27周)

    W

    月中的周数

    2(该月的第2周)

    D

    年中的天数

    189(该年的第189天)

    d

    月中的天数

    10(该月的第10天)

    u

    星期中的天数

    1 = 星期一, ..., 7 = 星期日

    a

    am/pm 标记

    pm(下午时)

    H

    24小时数(0-23)

    2

    h

    12小时数(1-12)

    12

    m

    分钟数

    30

    s

    秒数

    55

    S

    毫秒数

    978

    z

    时区

    Pacific Standard Time; PST; GMT-08:00

  • TIMESTAMPFORMAT:指定列的时间戳格式。

    OPTIONS('TIMESTAMPFORMAT'='timestampFormat')

  • MODE:指定导入过程错误记录的处理模式,支持三种选项:PERMISSIVE、DROPMALFORMED和FAILFAST。

    OPTIONS('MODE'='permissive')

    • PERMISSIVE(默认):尽可能地解析bad records,如果遇到不能转换的字段,则整行为null
    • DROPMALFORMED:忽略掉无法解析的bad records
    • FAILFAST:遇到无法解析的记录时,抛出异常并使Job失败
  • BADRECORDSPATH:指定导入过程中错误记录的存储目录。

    OPTIONS('BADRECORDSPATH'='obs://bucket/path')

    配置该选项后,MODE不可配,固定为"DROPMALFORMED",即将能够成功转换的记录导入到目标表,而将转换失败的记录存储到指定错误记录存储目录。

注意事项

  • 导入OBS表时,创建OBS表时指定的路径必须是文件夹,若建表路径是文件将导致导入数据失败。
  • 仅支持导入位于OBS路径上的原始数据。
  • 不建议对同一张表并发导入数据,因为有一定概率发生并发冲突,导致导入失败。
  • 导入数据时只能指定一个路径,路径中不能包含逗号。
  • 当OBS桶目录下有文件夹和文件同名时,导入数据会优先指向该路径下的文件而非文件夹。
  • 导入PARQUET、ORC及JSON类型数据时,必须指定DATA_TYPE这一OPTIONS,否则会以默认的“CSV”格式进行解析,从而导致导入的数据格式不正确。
  • 导入CSV及JSON类型数据时,如果包含日期及时间列,需要指定DATEFORMATTIMESTAMPFORMAT选项,否则将以默认的日期及时间戳格式进行解析。

示例

导入数据前已参考创建OBS表或者创建DLI表章节中的示例描述创建对应的表。

  • 可使用下列语句将CSV文件导入到DLI表,“t”为表名。
    1
    2
    LOAD DATA INPATH 'obs://dli/data.csv' INTO TABLE t
      OPTIONS('DELIMITER'=',' , 'QUOTECHAR'='"','COMMENTCHAR'='#','HEADER'='false');
    
  • 可使用下列语句将JSON文件导入到DLI表,“jsontb”为表名。
    1
    2
    LOAD DATA INPATH 'obs://dli/alltype.json' into table jsontb
      OPTIONS('DATA_TYPE'='json','DATEFORMAT'='yyyy/MM/dd','TIMESTAMPFORMAT'='yyyy/MM/dd HH:mm:ss');