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.	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 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.