# LOAD DATA INFILE
`LOAD DATA INFILE`语句能高效地将文件中的数据读取到数据库中的表中。`LOAD DATA INFILE` 与 `SELECT INTO OUTFILE`互补。要将数据从 table导出到文件,请使用[SELECT INTO OUTFILE](../dql/SELECT_INTO_STATEMENT.md)。要将文件数据导入到 table 中,请使用`LOAD DATA INFILE`。
## Syntax
```sql
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`一致。
支持从Hive导入数据,但需要额外的设置,详情见[Hive 支持](#hive-支持)。
下表展示了`LOAD DATA INFILE`语句的配置项。
| 配置项 | 类型 | 默认值 | 描述 |
| ---------- | ------- | ----------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| delimiter | String | , | 列分隔符,默认为`,`。 |
| header | Boolean | true | 是否包含表头, 默认为`true` 。 |
| null_value | String | null | NULL值,默认填充`"null"`。加载时,遇到null_value的字符串将被转换为`"null"`,插入表中。 |
| format | String | csv | 导入文件的格式:
`csv`:不显示指明format时,默认为该值
`parquet`:集群版还支持导入parquet格式文件,单机版不支持。 |
| quote | String | "" | 输入数据的包围字符串。字符串长度<=1。默认为"",表示解析数据,不特别处理包围字符串。配置包围字符后,被包围字符包围的内容将作为一个整体解析。例如,当配置包围字符串为"#"时, `1, 1.0, #This is a string field, even there is a comma#`将为解析为三个filed.第一个是整数1,第二个是浮点1.0,第三个是一个字符串。 |
| mode | String | "error_if_exists" | 导入模式:
`error_if_exists`: 仅离线模式可用,若离线表已有数据则报错。
`overwrite`: 仅离线模式可用,数据将覆盖离线表数据。
`append`:离线在线均可用,若文件已存在,数据将追加到原文件后面。 |
| deep_copy | Boolean | true | `deep_copy=false`仅支持离线load, 可以指定`INFILE` Path为该表的离线存储地址,从而不需要硬拷贝。 |
| load_mode | String | cluster | `load_mode='local'`仅支持从csv本地文件导入在线存储, 它通过本地客户端同步插入数据;
`load_mode='cluster'`仅支持集群版, 通过spark插入数据,支持同步或异步模式 |
| thread | Integer | 1 | 仅在本地文件导入时生效,即`load_mode='local'`或者单机版,表示本地插入数据的线程数。 最大值为`50`。 |
```{note}
在集群版中,`LOAD DATA INFILE`语句会根据当前执行模式(execute_mode)决定将数据导入到在线或离线存储。单机版中没有存储区别,同时也不支持`deep_copy`选项。
在线导入只能使用append模式。
离线软拷贝导入后,OpenMLDB不应修改**软连接中的数据**,因此,如果当前离线数据是软连接,就不再支持`append`方式导入。并且,当前软连接的情况下,使用`overwrite`模式的硬拷贝,也不会删除软连接的数据。
```
```{warning} INFILE Path
:class: warning
在集群版中,如果`load_mode='cluster'`,`INFILE`路径的读取是由batchjob来完成的,如果是相对路径,就需要batchjob可以访问到的相对路径。
在生产环境中,batchjob的执行通常由yarn集群调度,难以确定具体的执行者。在测试环境中,如果也是多机部署,难以确定batchjob的具体执行者。
所以,请尽量使用绝对路径。单机测试中,本地文件用`file://`开头;生产环境中,推荐使用hdfs等文件系统。
```
## SQL语句模版
```sql
LOAD DATA INFILE 'file_name' INTO TABLE 'table_name' OPTIONS (key = value, ...);
```
## Examples:
从`data.csv`文件读取数据到表`t1`在线存储中。并使用`,`作为列分隔符
```sql
set @@execute_mode='online';
LOAD DATA INFILE 'data.csv' INTO TABLE t1 OPTIONS(delimiter = ',' );
```
从`data.csv`文件读取数据到表`t1`中。并使用`,`作为列分隔符, 字符串"NA"将被替换为NULL。
```sql
LOAD DATA INFILE 'data.csv' INTO TABLE t1 OPTIONS(delimiter = ',', null_value='NA');
```
将`data_path`软拷贝到表`t1`中,作为离线数据。
```sql
set @@execute_mode='offline';
LOAD DATA INFILE 'data_path' INTO TABLE t1 OPTIONS(deep_copy=false);
```
## Hive 支持
### 支持Hive数据格式
支持以下几种Hive的数据格式,不支持Binary等其他格式。
| OpenMLDB Data Format | Hive Data Format |
| -------------------- | ---------------- |
| BOOL | BOOLEAN |
| SMALLINT | SMALLINT |
| INT | INT |
| BIGINT | BIGINT |
| FLOAT | FLOAT |
| DOUBLE | DOUBLE |
| DATE | DATE |
| TIMESTAMP | TIMESTAMP |
### 开启 Hive 支持
为了支持读写Hive,我们需要Hive相关的依赖和Hive配置。
#### Spark Hive依赖
[OpenMLDB Spark 发行版](../../../tutorial/openmldbspark_distribution.md) v0.6.7及以上均包含Hive依赖。如果使用其他Spark发行版,我们需要在Spark中编译出Hive依赖,依赖包在`assembly/target/scala-xx/jars`. 将所有依赖加入Spark的class path中。
```
./build/mvn -Pyarn -Phive -Phive-thriftserver -DskipTests clean package
```
#### Hive 配置
目前只支持使用metastore服务来连接Hive。
- spark.conf
你可以在spark conf中配置`spark.hadoop.hive.metastore.uris`。有两种方式:
- taskmanager.properties: 在配置项 `spark.default.conf` 中加入`spark.hadoop.hive.metastore.uris=thrift://...`并重启taskmanager。
- CLI: 在ini conf中加入此配置项,并使用`--spark_conf`启动CLI,参考[客户端Spark配置文件](../../client_config/client_spark_config.md)。
- hive-site.xml:
你可以配置`hive-site.xml`中的`hive.metastore.uris`,并将配置文件放入Spark home的`conf/`。
`hive-site.xml`样例:
```
hive.metastore.uris
thrift://localhost:9083
URI for client to contact metastore server
```
### Hive Debug
确认`LOAD DATA`等任务是否连接到正确的Hive集群,可以通过job log来查看。
- `INFO HiveConf:`提示读取到的是哪个Hive配置文件。如果需要配置加载的细节,可以打开spark debug日志。
- 连接Hive metastore应该有` INFO metastore: Trying to connect to metastore with URI`的日志提示。连接成功会有`INFO metastore: Connected to metastore.`日志。