Creating a Loading Job

Among its several duties, the RESTPP component manages loading jobs. There can be multiple RESTPP-LOADER subcomponents, each of which can handle a loading job independently. The maximum number of concurrent loading jobs is set by the configuration parameter FileLoader.ReplicaNumber.

Furthermore, if the TigerGraph graph is distributed (partitioned) across multiple machine nodes, each machine’s RESTPP-LOADER(s) can be put into action. Each RESTPP-LOADER only reads local input data files, but the resulting graph data can be stored on any machine in the cluster.

A concurrent-capable loading job can logically be separated into parts according to each file variable. When a concurrent-capable loading job is compiled, a RESTPP endpoint is generated for the loading job, which you can call to load data into your graph as an alternative to RUN LOADING JOB.

The DEFINE FILENAME statement defines a filename variable. The variable can then be used later in the JOB block by a LOAD statement to identify its data source. Every concurrent loading job must have at least one DEFINE FILENAME statement.

The filevar _is optionally followed by a filepath_string, which tells the job where to find input data. As the name suggests, _filepath_string is a string value. Therefore, it should start and end with double quotes.

filepath_string

There are four options for filepath_string :

An absolute path may begin with the session variable $sys.data_root.

Then, when running this loading job, first set a value for the parameter, and then run the job:

As the name implies, session parameters only retain their value for the duration of the current GSQL session. If the user exits GSQL, the settings are lost.

The DEFINE HEADER statement defines a sequence of column names for an input data file. The first column name maps to the first column, the second column name maps to the second column, etc.

The DEFINE INPUT_LINE_FILTER statement defines a named Boolean expression whose value depends on column attributes from a row of input data. When combined with a USING reject_line_rule clause in a LOAD statement, the filter determines whether an input line is ignored or not.

filevar __must have been previously defined in a DEFINE FILENAME statement.

filepath_string __must satisfy the same rules given above in the DEFINE FILENAME section.

A Destination clause describes how the tokens from a data source should be used to construct one of three types of data objects: a vertex, an edge, or a row in a temporary table (TEMP_TABLE). The destination clause formats for the three types are very similar, but we show them separately for clarity:

For the TO VERTEX and TO EDGE destination clauses, the following rules for its parameters apply:

The TO TEMP_TABLE clause defines a new, temporary data structure. Its unique characteristics will be described in a separate subsection. For now, we focus on TO VERTEX and TO EDGE.

Suppose we have the following vertex and edge types:

A Visit edge can connect two Person vertices or a Person to a Company. A Person has a string ID, while a Company has an INT ID. Then suppose the Visit edge source data comes from a single CSV file, containing both variants of edges. Note that the 2nd column ($1) contains either Person or Company, and that the 3rd column ($2) contains either a string or an integer.

Using the optional `target_type_expr` field, we can load both variants of the Visit edge with a single clause.

A basic principle in the GSQL Loader is cumulative loading. Cumulative loading means that a particular data object might be written to (i.e., loaded) multiple times, and the result of the multiple loads may depend on the full sequence of writes. This usually means that If a data line provides a valid data object, and the WHERE clause and OPTION clause are satisfied, then the data object is loaded.

means to skip the next-to-last attribute. This technique is used when it is known that the input data file does not contain data for every attribute.

An attribute expression may use column tokens (e.g., $0), literals (constant numeric or string values), any of the built-in loader token functions, or a user-defined token function. Attribute expressions may not contain mathematical or boolean operators (such as +, *, AND). The rules for attribute expressions are the same as those for id expressions, but an attribute expression can additionally use a reducer function:

Note that token functions can be nested, that is, a token function can be used as an input parameter for another token function. The built-in loader token/reducer functions and user-defined token functions are described in the section "Built-In Loader Token Functions".

The subsections below describe details about loading particular data types.

A floating point value has the basic format

In the first case, the decimal point and following digits are required. In the second case, some digits are required (looking like an integer), and the following decimal point and digits are optional.

In both cases, the leading sign ( "+" or "-") is optional. The exponent, using "e" or "E", is optional. Commas and extra spaces are not allowed.

When loading data into a DATETIME attribute, the GSQL loader will automatically read a string representation of DateTime information and convert it to internal DateTime representation. The loader accepts any of the following string formats:

Format notation:

%Y is a 4-digit year. A 2-digit year is not a valid value.

%m and %s are a month (1 to 12) and a day (1 to 31), respectively. Leading zeroes are optional.

%H, %M, %S are hours (0 to 23), minutes (0 to 59) and seconds (0 to 59), respectively. Leading zeroes are optional.

When loading data, the loader checks whether the values of year, month, day, hour, minute, second are out of the valid range. If any invalid value is present, e.g. '2010-13-05' or '2004-04-31 00:00:00', the attribute is invalid and the object (vertex or edge) is not created.

When loading data from CSV files the following values are accepted for BOOL attributes :

When loading data from JSON documents, the valid BOOL values are true and false.

To load a UDT attribute, state the name of the UDT type, followed by the list of attribute expressions for the UDT’s fields, in parentheses. See the example below.

There are three methods to load a LIST or a SET.

The first method is to load multiple rows of data that share the same ID values and append the individual attribute values to form a collection of values. The collections are formed incrementally by reading one value from each eligible data line and appending the new value into the collection. When the loading job processes a line, it checks to see whether a vertex or edge with that id value(s) already exists or not. If the id value(s) is new, then a new vertex or edge is created with a new list/set containing the single value. If the id(s) has been used before, then the value from the new line is appended to the existing list/set. Below shows an example:

The job load_set_list will load two test_vertex vertices because there are two unique id values in the data file. Vertex 1 has attribute values with iset = [10,20] and ilist = [10,20,20]. Vertex 3 has values iset = [30,40] and ilist = [30, 30, 40]. Note that a set doesn’t contain duplicate values, while a list can contain duplicate values.

If the input file contains multiple columns which should be all added to the LIST or SET, then a second method is available. Use the LIST() or SET() function as in the example below:

The third method is to use the SPLIT() function to read a compound token and split it into a collection of elements, to form a LIST or SET collection. The SPLIT() function takes two arguments: the column index and the element separator. The element separator should be distinct from the separator throughout the whole file. Below shows an example:

There are three methods to load a MAP.

The first method is to load multiple rows of data that share the same id values. The maps are formed incrementally by reading one key-value pair from each eligible data line. When the loading job processes a line, it checks to see whether a vertex or edge with that id value(s) already exists or not. If the id value(s) is new, then a new vertex or edge is created with a new map containing the single key-value pair. If the id(s) has been used before, then the loading job checks whether the key exists in the map or not. If the key doesn’t exist in the map, the new key-value pair is inserted. Otherwise, the value will be replaced by the new value.

Method 1: Below is the syntax to load a MAP by the first method: Use an arrow (→) to separate the map’s key and value.

Method 2: The second method is to use the MAP() function. If there are multiple key-value pairs among multiple columns, MAP() can load them together. Below is an example:

Method 3: The third method is to use the SPLIT() function. Similar to the SPLIT() in loading LIST or SET, the SPLIT() function can be used when the key-value pair is in one column and separated by a key-value separator, or multiple key-value pairs are in one column and separated by element separators and key-value separators. SPLIT() here has three parameters: The first is the column index, the second is the key-value separator, and the third is the element separator. The third parameter is optional. If one row of raw data only has one key-value pair, the third parameter can be skipped. Below are the examples without and with the given element separator.

Loading a Composite Key for a vertex works no differently than normal loading. Simply load all the attributes as you would for a vertex with a single-attribute primary key. The primary key will automatically be constructed from the appropriate attributes.

When loading to an edge where either TO_VERTEX or FROM_VERTEX contains a composite key, the composite set of attributes must be enclosed in parentheses. See the example below.

If an edge has been defined using a wildcard vertex type, a vertex type name must be specified, following the vertex id, in a LOAD statement for the edge. An example is shown below:

The following token functions can be used in an id or attribute expression

Users can write their own token functions in C++ and install them in the GSQL system. To learn how to add a user-defined token function, see Add a User-defined Token Function.

A reducer function aggregates multiple values of a non-id attribute into one attribute value of a single vertex or edge. Reducer functions are computed incrementally; that is, each time a new input token is applied, a new resulting value is computed.

To reduce and load aggregate data to an attribute, the attribute expression has the form

where reducer_function is one of the functions in the table below. input_expr can include non-reducer functions, but reducer functions cannot be nested.

Each reducer function is overloaded so that one function can be used for several different data types. For primitive data types, the output type is the same as the input_expr type. For LIST, SET, and MAP containers, the input_expr type is one of the allowed element types for these containers (see "Complex Types" in the Attribute Data Types section). The output is the entire container.

The GSQL Loader language supports most of the standard arithmetic, relational, and boolean operators found in C++. Standard operator precedence applies, and parentheses provide the usual override of precedence.

The GSQL loading language provides several built-in functions for the WHERE clause.

There are no supported options for the OPTION clause at this time.

The TAGS clause specifies the tags to be applied to the vertices loaded by the LOAD statement.

If a LOAD statement has a TAGS clause, it will tag the vertices with the tags specified in the TAGS clause. Before vertices can be loaded and tagged with a LOAD statement, the vertex type must first be marked as taggable, and the tags must be defined.

Users have two options when it comes to how to merge tags if the target vertices exist in the graph:

A USING clause contains one or more optional parameter value pairs:

The parser will not treat separator characters found within a pair of quotation marks as a separator. For example, if the parsing conditions are QUOTE="double", SEPARATOR=",", the comma in "Leonard,Euler" will not separate Leonard and Euler into separate tokens.
**

When the USING option JSON_FILE="true" is used, the loader loads JSON objects instead of tabular data. A JSON object is an unordered set of key/value pairs, where each value may itself be an array or object, leading to nested structures. A colon separates each key from its value, and a comma separates items in a collection. A more complete description of JSON format is available at www.json.org. The JSON loader requires that each input line has exactly one JSON object. Instead of using column values as tokens, the JSON loader uses JSON values as tokens, that is, the second part of each JSON key/value pair. In a GSQL loading job, a JSON field is identified by a dollar sign $ followed by the colon-separated sequence of nested key names to reach the value from the top level. For example, given the JSON object {"abc":{"def": "this_value"}}, the identifier $"abc":"def" is used to access "this_value". The double quotes are mandatory.

An example is shown below:

To specify an end-of-line character other than the standard one, use the EOL option, as shown below.

In the above data encoding.json, the order of fields are not fixed and some fields are missing. The JSON loader ignores the order and accesses the fields by the nested key names. The missing fields are loaded with default values. The result vertices are:

TigerGraph can load data from Parquet files if they are stored in AWS S3 buckets. For more details on how to set up S3 data sources and loading jobs, read the AWS S3 Loader User Guide. In the background TigerGraph uses the JSON loading functionality to read data from Parquet files, so the JSON specific information in the previous section applies.

In order to load Parquet data, you need to:

You will probably want to add USING EOF="true" to your RUN LOADING JOB statement to explicitly indicate to the loading job to stop after consuming all data from the Parquet source, not to expect further entries.

An example of a Parquet loading setup is shown below:

Normally, if vertices do not exist when loading data to edges, a vertex will be created for the connecting edge, using default values for all attributes. Using the VERTEX_MUST_EXIST="true" option will load data only if the vertices on both sides of an edge already exist, therefore no longer creating extra vertices.

flatten( column_to_be_split, separator, 1 ) is used to parse a one-level group into individual elements. An example is shown below:

The following loading job contains two LOAD statements. The first one loads input data to Book vertices and to a TEMP_TABLE. The second one loads the TEMP_TABLE data to Genre vertices and book_genre edges.

Line 5 says that the third column ($2) of each input line should be split into separate tokens, with comma (,) as the separator. Each token will have its own row in table t1. The first column is labeled bookcode with value $0 and the second column is genre with one of the $2 tokens. The contents of TEMP_TABLE t1 are shown below:

Then, lines 8 to 10 say to read TEMP_TABLE t1 and to do the following for each row:

The final graph will contain two Book vertices (101 and 102), five Genre vertices, and six book_genre edges.

flatten( column_to_be_split, group_separator, sub_field_separator, number_of_sub_fields_in_one_group ) is used for parse a two-level group into individual elements. Each token in the main group may itself be a group, so there are two separators: one for the top level and one for the second level. An example is shown below.

The flatten function now has four parameters instead of three. The additional parameter is used to record the genre_name in the Genre vertices.

In this example, in the genres column ($2), there are multiple groups, and each group has two sub-fields, genre_id and genre_name. After running the loading job, the file book2.dat will be loaded into the TEMP_TABLE t2 as shown below.

flatten_json_array($" array_name ") parses a JSON array of primitive (string, numberic, or bool) values, where "array_name" is the name of the array. Each value in the array creates a record. Below is an example:

The above data and loading job creates the following temporary table:

flatten_json_array() parses a JSON array of JSON objects. The function takes the name of the array in the first argument, and the field key names of each object in the following arguments.

flatten_json_array() can also be used to split a column of a tabular file, where the column contains JSON arrays. An example is given below:

The second column in the csv file is a JSON array which we want to split. flatten_json_array() can be used in this case without the USING JSON_FILE="true" clause: