LOAD DATA INFILE
Contents
LOAD DATA INFILE#
LOAD DATA INFILE语句能高效地将文件中的数据读取到数据库中的表中。LOAD DATA INFILE 与 SELECT INTO OUTFILE互补。要将数据从 table导出到文件，请使用SELECT INTO OUTFILE。要将文件数据导入到 table 中，请使用LOAD DATA INFILE。注意，导入的文件schema顺序应与表的schema顺序一致。
Note
无论何种load_mode，INFILE 的 filePath既可以是单个文件名，也可以是目录，也可以使用*通配符。
load_mode=cluster的具体格式等价于
DataFrameReader.read.load(String)，可以使用spark shell来read你想要的文件路径，确认能否读入成功。如果目录中存在多格式的文件，只会选择 LoadDataInfileOptionsList 中指定的FORMAT格式文件。load_mode=local则使用glob选择出符合的所有文件，不会检查单个文件的格式，所以，请保证满足条件的文件都是csv格式，建议使用
*.csv限制文件格式。
Syntax#
LoadDataInfileStmt
::= 'LOAD' 'DATA' 'INFILE' filePath 'INTO' 'TABLE' tableName LoadDataInfileOptionsList
filePath
::= URI
tableName
::= string_literal
LoadDataInfileOptionsList
::= 'OPTIONS' '(' LoadDataInfileOptionItem (',' LoadDataInfileOptionItem)* ')'
LoadDataInfileOptionItem
::= 'DELIMITER' '=' string_literal
|'HEADER' '=' bool_literal
|'NULL_VALUE' '=' string_literal
|'FORMAT' '=' string_literal
|'QUOTE' '=' string_literal
|'MODE' '=' string_literal
|'DEEP_COPY' '=' bool_literal
|'LOAD_MODE' '=' string_literal
|'THREAD' '=' int_literal
URI
::= 'file://FilePathPattern'
|'hdfs://FilePathPattern'
|'hive://[db.]table'
|'FilePathPattern'
FilePathPattern
::= string_literal
其中FilePathPattern支持通配符*，比如可以设成/test/*.csv，匹配规则和ls FilePathPattern一致。
下表展示了LOAD DATA INFILE语句的配置项。
配置项 |
类型 |
默认值 |
描述 |
|---|---|---|---|
delimiter |
String |
, |
列分隔符，默认为 |
header |
Boolean |
true |
是否包含表头, 默认为 |
null_value |
String |
null |
NULL值，默认填充 |
format |
String |
csv |
导入文件的格式: |
quote |
String |
“ |
输入数据的包围字符串。字符串长度<=1。 |
mode |
String |
“error_if_exists” |
导入模式: |
deep_copy |
Boolean |
true |
|
load_mode |
String |
cluster |
|
thread |
Integer |
1 |
仅在本地文件导入时生效，即 |
writer_type |
String |
single |
集群版在线导入中插入数据的writer类型。可选值为 |
put_if_absent |
Boolean |
false |
在源数据无重复行也不与表中已有数据重复时，可以使用此选项避免插入重复数据，特别是job失败后可以重试。等价于使用 |
Note
在集群版中，LOAD DATA INFILE语句会根据当前执行模式（execute_mode）决定将数据导入到在线或离线存储。单机版中没有存储区别，只会导入到在线存储中，同时也不支持deep_copy选项。
具体的规则见下文。
Warning
INFILE Path
在集群版中，如果load_mode='cluster'，INFILE路径的读取是由batchjob来完成的，如果是相对路径，就需要batchjob可以访问到的相对路径。
在生产环境中，batchjob的执行通常由yarn集群调度，难以确定具体的执行者。在测试环境中，如果也是多机部署，难以确定batchjob的具体执行者。
所以，请尽量使用绝对路径。单机测试中，本地文件用file://开头；生产环境中，推荐使用hdfs等文件系统。
SQL语句模版#
LOAD DATA INFILE 'file_path' INTO TABLE 'table_name' OPTIONS (key = value, ...);
Hive 支持#
OpenMLDB 支持从 Hive 导入数据，但需要额外的设置和功能限制，详情见 Hive 支持。
Examples:#
从data.csv文件读取数据到表t1在线存储中。并使用,作为列分隔符
set @@execute_mode='online';
LOAD DATA INFILE 'data.csv' INTO TABLE t1 OPTIONS(delimiter = ',' );
从data.csv文件读取数据到表t1中。并使用,作为列分隔符， 字符串”NA”将被替换为NULL。
LOAD DATA INFILE 'data.csv' INTO TABLE t1 OPTIONS(delimiter = ',', null_value='NA');
将data_path软拷贝到表t1中，作为离线数据。
set @@execute_mode='offline';
LOAD DATA INFILE 'data_path' INTO TABLE t1 OPTIONS(deep_copy=false);
在离线模式下导入 Hive 数据仓库的表格：
set @@execute_mode='offline';
LOAD DATA INFILE 'hive://db1.t1' INTO TABLE t1;
在线导入规则#
在线导入只允许mode='append'，无法overwrite或error_if_exists。
如果设置了 insert_memory_usage_limit session变量，服务端内存使用率超过设定的值就会返回失败。
离线导入规则#
表的离线信息可通过desc <table>查看。我们将数据地址分为两类，Data path与Symbolic path，离线地址Data path是OpenMLDB的内部存储路径，硬拷贝将写入此地址，仅一个；软链接地址Symbolic path，则是软链接导入的地址列表，可以是多个。
--- ------- ----------- ------ ---------
# Field Type Null Default
--- ------- ----------- ------ ---------
1 c1 Varchar YES
2 c2 Int YES
3 c3 BigInt YES
4 c4 Float YES
5 c5 Double YES
6 c6 Timestamp YES
7 c7 Date YES
--- ------- ----------- ------ ---------
--- -------------------- ------ ---- ------ ---------------
# name keys ts ttl ttl_type
--- -------------------- ------ ---- ------ ---------------
1 INDEX_0_1705743486 c1 - 0min kAbsoluteTime
--- -------------------- ------ ---- ------ ---------------
---------------------------------------------------------- ------------------------------------------ --------- ---------
Data path Symbolic paths Format Options
---------------------------------------------------------- ------------------------------------------ --------- ---------
file:///tmp/openmldb_offline_storage/demo_db/demo_table1 file:///work/taxi-trip/data/data.parquet parquet
---------------------------------------------------------- ------------------------------------------ --------- ---------
--------------- --------------
compress_type storage_mode
--------------- --------------
NoCompress Memory
--------------- --------------
根据模式的不同，对离线信息的修改也不同。
overwrite模式，将会覆盖原有的所有字段，包括离线地址、软链接地址、格式、读取选项，仅保留当前overwrite进入的信息。
overwrite 硬拷贝，离线地址如果存在数据将被覆盖，软链接全部清空，格式更改为内部默认格式parquet，读取选项全部清空。
overwrite 软拷贝，离线地址直接删除（并不删除数据），软链接覆盖为输入的链接、格式、读取选项。
append模式，append 硬拷贝将数据写入当前离线地址，append 软拷贝需要考虑当前的格式和读取选项，如果不同，将无法append。
append同样的路径将被忽略，但路径需要是字符串相等的，如果不同，会作为两个软链接地址。
errorifexists，如果当前已有离线信息，将报错。这里的离线信息包括离线地址和软链接地址，比如，当前存在离线地址，无软链接，现在
LOAD DATA软链接，也将报错。
Tip
如果当前离线信息存在问题，无法通过LOAD DATA修改，可以手动删除离线地址的数据，并用nameserver http请求清空表的离线信息。
清空离线信息步骤：
curl http://<ns_endpoint>/NameServer/ShowTable -d'{"db":"<db_name>","name":"<table_name>"}' # 获得其中的表tid
curl http://<ns_endpoint>/NameServer/UpdateOfflineTableInfo -d '{"db":"<db_name>","name":"<table_name>","tid":<tid>}'
由于硬拷贝的写入格式无法修改，是parquet格式，所以如果想要硬拷贝和软链接同时存在，需要保证软链接的数据格式也是parquet。
CSV源数据格式说明#
导入支持csv和parquet两种数据格式，csv的格式需要特别注意，下面举例说明。
csv的列分隔符默认为
,，不允许出现空格，否则，”a, b”将被解析为两列，第一列为a，第二列为b（有一个空格）。local模式会trim掉列分隔符两边的空格，所以
a, b会被解析为两列，第一列为a，第二列为b。但从规范上来说，csv的列分隔符左右不应该有空格，请不要依赖这个特性。
cluster和local模式对于空值的处理不同，具体为：
c1, c2 , "","" ab,cd "ef","gh" null,null
这个csv源数据中，第一行两个空值（blank value）。
cluster模式空值会被当作
null（无论null_value是什么）。local模式空值会被当作空字符串，具体见issue3015。
第二行两列都是两个双引号。
cluster模式默认quote为
"，所以这一行是两个空字符串。local模式默认quote为
\0，所以这一行两列都是两个双引号。local模式quote可以配置为"，但escape规则是""为单个"，和Spark不一致，具体见issue3015。
cluster的csv格式支持两种格式的timestamp，但同一次load只会选择一种格式，不会混合使用。如果csv中存在两种格式的timestamp，会导致解析失败。选择哪种格式由第一行数据决定，如果第一行数据是
2020-01-01 00:00:00，则后续所有timestamp都会按照yyyy-MM-dd HH:mm:ss格式解析；如果第一行数据是整型1577808000000，则后续所有timestamp都会按照整型格式解析。timestamp可以为字符串格式，比如
"2020-01-01 00:00:00"。date可以是年月日（
yyyy-MM-dd）或者年月日时分秒（yyyy-MM-dd HH:mm:ss）。
local的csv格式只支持整型timestamp，date类型为年月日，例如
2022-2-2。timestamp和date均不可以为字符串格式，比如
"2020-01-01"将解析失败。date不可以是年月日时分秒，例如
2022-2-2 00:00:00将解析失败。
local的字符串不支持quote转义，所以如果你的字符串中存在quote字符，请使用cluster模式。
cluster如果读取csv时解析失败，将会把失败的列值设为NULL，继续导入流程，但local模式会直接报错，不会继续导入。
PutIfAbsent说明#
PutIfAbsent是一个特殊的选项，它可以避免插入重复数据，仅需一个配置，操作简单，特别适合load datajob失败后重试，等价于使用INSERT OR IGNORE。如果你想要导入的数据中存在重复，那么通过PutIfAbsent导入，会导致部分数据丢失。如果你需要保留重复数据，不应使用此选项，建议通过其他方式去重后再导入。local模式暂不支持此选项。
PutIfAbsent需要去重这一额外开销，所以，它的性能与去重的复杂度有关：
表中只存在ts索引，且同一key+ts的数据量少于10k时（为了精确去重，在同一个key+ts下会逐行对比整行数据），PutIfAbsent的性能表现不会很差，通常导入时间在普通导入时间的2倍以内。
表中如果存在time索引（ts列为空），或者ts索引同一key+ts的数据量大于100k时，PutIfAbsent的性能会很差，导入时间可能超过普通导入时间的10倍，无法正常使用。这样的数据条件下，更建议进行去重后再导入。
local导入模式说明#
load_mode可使用local模式，但与cluster模式有一些不同，如果你部署了TaskManager，我们建议使用cluster模式。不同之处如下：
local模式仅支持在线，不支持离线。也只支持csv格式，不支持parquet格式。
csv的读取支持有限，（SplitLineWithDelimiterForStrings）