Source: https://dev-docs.nebula-graph.io/v5.3.2/utilities/exchange/config/sources/preprocess/
Documentation version: v5.3.2

# Preprocessing configurations

This topic describes the optional preprocessing configurations when importing data into NebulaGraph Database Developer Edition.

## Sample configuration

````
sources: [
  {
//    type:"hdfs"
//    format:"csv"
//    header:false
//    separator:","
//    path: "hdfs://127.0.0.1:9000/import_movie/sub_movie.csv"
    pre_processes: {
        concat: [
          {
            oldFields: [a, b]
            newField: "ab"
            splicer: "_"
          }
        ]
        separate: [
          {
            oldField: "aaa"
            newFields: [a, b]
            sep: "&"
          }
        ]
        filter: ["age>=10 AND firstName=='Tom' OR lastName!='Li'", "aa>='ss'"]
        concatConstant: [
          {
            field: "name"
            prefixValue: "test_"
            suffixValue: "_test"
          }
        ]
        newConstantColumn: [
          {
            field: "newColumn"
            value: "AAA"
          }
        ]        
        nonValue: ""
        udfSql: "select * from $exchange"
      }
//    nodetypes: []
//    edgetypes: []
  }
]

````

## Configuration options

 | Option | Description | Type | Required | Default value
 | `pre_processes.concat` | Concatenates multiple fields into a new field. | List | No | N/A
 | `pre_processes.concat.oldFields` | The list of old fields to be concatenated. | List | No | N/A
 | `pre_processes.concat.newField` | The name of the new field that is created after concatenation. | String | No | N/A
 | `pre_processes.concat.splicer` | The splicer used to join the old fields. | String | No | N/A
 | `pre_processes.separate` | Separates a field into multiple fields. | List | No | N/A
 | `pre_processes.separate.oldField` | The name of the old field to be separated. | String | No | N/A
 | `pre_processes.separate.newFields` | The list of new fields that are created after separation. | List | No | N/A
 | `pre_processes.separate.sep` | The separator used to split the old field. | String | No | N/A
 | `pre_processes.filter` | Filter conditions that are applied to the data. String values in the filter conditions must be enclosed with single quotes. | List | No | N/A
 | `pre_processes.concatConstant` | Concatenates a field with a constant value. | List | No | N/A
 | `pre_processes.concatConstant.field` | The name of the field to be concatenated. | String | No | N/A
 | `pre_processes.concatConstant.prefixValue` | The constant value that is added as a prefix to each field value. | String | No | N/A
 | `pre_processes.concatConstant.suffixValue` | The constant value that is added as a suffix to each field value. | String | No | N/A
 | `pre_processes.newConstantColumn` | Adds a new field that contains a constant value. | List | No | N/A
 | `pre_processes.newConstantColumn.field` | The name of the new field. | String | No | N/A
 | `pre_processes.newConstantColumn.value` | The constant value of the new field. | String | No | N/A
 | `pre_processes.nonValue` | The value that is used to replace null values. | String | No | N/A
 | `pre_processes.udfSql` | The Spark SQL statement that is used to preprocess the data. This statement must be in the format of `select <custom_statement> from $exchange <custom_statement>`, where `<custom_statement>` represents the custom SQL statement that you want to use. For example, `select LENGTH(ab) as ab, REVERSE(newColumn) as newColumn from $exchange`.
For a list of commonly used SQL operations, see [SQL reference](https://dev-docs.nebula-graph.io/v5.3.2/utilities/exchange/config/sources/preprocess/#sql_reference). | String | No | N/A

## Preprocessing order

If all the configuration options are specified, they are executed in the following order: `concat`->`separate`->`filter`->`concatConstant`->`newConstantColumn`->`nonValue`->`udfSql`.

## SQL reference

NebulaGraph Exchange supports [Spark SQL](https://spark.apache.org/docs/latest/sql-programming-guide.html) for data processing during import. This topic provides a list of commonly used SQL operations for your reference.

Note

All SQL statements must be in the format of `select <custom_statement> from $exchange <custom_statement>`, where `<custom_statement>` represents the custom SQL statement that you want to use.

 | Operator/function | Example | Description
 | `current_timestamp` | `select *, current_timestamp as current_time from $exchange` | Adds a new field named `current_time` and sets its values to the current timestamp.
 | `current_date` | `select *, current_date as current_date from $exchange` | Adds a new field named `current_date` and sets its values to the current date.
 | `least` | `select *, least(col1,col2) as new_col from $exchange` | Adds a new field named `new_col` and sets its values to the smaller of the values in `col1` and `col2`.
 | `greatest` | `select *, greatest(col1,col2) as new_col from $exchange` | Adds a new field named `new_col` and sets its values to the larger of the values in `col1` and `col2`.
 | `=`, `!=`, `>`, `<`, `>=`, `<=`, `<>` | `select * from $exchange where col1='abc'` | Filters rows where the value of `col1` is `abc`.
 | `IS NULL`, `IS NOT NULL` | `select * from $exchange where col1 IS NULL` | Filters rows where the value of `col1` is `NULL`.
 | `LIKE`, `NOT LIKE` | `select * from $exchange where col1 LIKE '%abc%'` | Filters rows where the value of `col1` contains `abc`.
 | `LIKE ALL`, `NOT LIKE ALL` | `select * from $exchange where col1 LIKE ALL('%abc%','%ABC%')` | Filters rows where the value of `col1` contains both `abc` and `ABC`.
 | `LIKE ANY`, `NOT LIKE ANY` | `select * from $exchange where col1 LIKE ANY('%abc%','ABC%')` | Filters rows where the value of `col1` contains either `abc` or starts with `ABC`.
 | `AND`, `OR` | `select * from $exchange where col1<10 AND col2>20` | Filters rows where the value of `col1` is less than 10 and the value of `col2` is greater than 20.
 | `NVL` | `select *, NVL(col1, 10) as new_col from $exchange` | Adds a new field named `new_col` and sets its values to those of `col1` if they are not `NULL`; otherwise, sets its values to 10.
 | `COALESCE` | `select *, COALESCE(col1,col2) as new_col from $exchange` | Adds a new field named `new_col` and sets its values to those of `col1` if they are not `NULL`; otherwise, sets its values to those of `col2`.
 | `LENGTH` | `select *, LENGTH(col1) as new_col from $exchange` | Adds a new field named `new_col` and sets its values to the lengths of the values in `col1`.
 | `CONCAT` | `select *, CONCAT(col1,'abc') as new_col from $exchange` | Adds a new field named `new_col` and sets its values to the concatenation of the values in `col1` and `abc`.
 | `REVERSE` | `select *, REVERSE(col1) as new_col from $exchange` | Adds a new field named `new_col` and sets its values to the reverse of the values in `col1`.
 | `LEFT`, `RIGHT` | `select *, LEFT(col1,2) as new_col from $exchange` | Adds a new field named `new_col` and sets its values to the first two characters of the values in `col1`.
 | `SUBSTR` | `select *, SUBSTR(col1,2,3) as new_col from $exchange` | Adds a new field named `new_col` and sets its values to the substrings of the values in `col1` starting from the second character and with a length of 3.
 | `UPPER`, `LOWER` | `select *, UPPER(col1) as new_col from $exchange` | Adds a new field named `new_col` and sets its values to the uppercase of the values in `col1`.
 | `REGEXP_REPLACE` | `select *, REGEXP_REPLACE(col1,'a','b') as new_col from $exchange` | Adds a new field named `new_col` and sets its values to the values in `col1` with all occurrences of `a` replaced by `b`.

For more information about Spark SQL, see the [Spark SQL documentation](https://spark.apache.org/docs/latest/sql-ref.html).