Overview
Surface Command has a feature called “Reference Lists”:
A reference list in Surface Command is a user-serviceable lookup table:
- Upload your data from a CSV or an Excel file.
- Meant for slowly changing data of modest volume, fewer than 1000 records, but handle up to about 50,000 records.
- The table structure is automatically defined from the data, this typically means that the fields in the CSV/Excel end up as “properties” in the resulting reference list.
What a reference list is not good for:
- High volume data.
- Data that changes frequently.
- A reference list does not create an “edge” with any other data (more on this later).
- A reference list does not index any columns, so there will be performance implications for large reference lists.
Using a Reference List
There are two ways to use a reference list:
- Query against it like it’s any other “type” in the Surface Command system.
- Use one of the 4 reference table lookup functions.
Querying a Reference List
Querying a reference list can be as simple as:
MATCH (r:ref_risky_services)
WHERE r.service = "compressnet"
RETURN r.description
To “join” against a reference list, because a reference list doesn’t have any “edges”, assuming that the example reference list has a column named name:
MATCH (a:Asset), (r:my_ref_list)
WHERE a.name = r.name
RETURN a, r
The above “join” works reasonably well if my_ref_list is relatively small (<100-1000 records), but performance will suffer, because each lookup in the join will require a traditional table scan.
Using Reference List Lookup Functions
An alternative to querying a reference list is to use one of the 4 lookup functions. These generally tend to work better for larger reference lists, because it utilizes hash values matches. The downside to using these functions.
Exact match functions:
rl_lookup_first(<ref list>, <match field>, <match value>, <return field>)rl_lookup_multi(<ref list>, <match field>, <match value>, <return field>)
With these functions, the match value is compared against the value of the match field
Pattern match functions:
rl_regex_first(<ref list>, <pattern field>, <match value>, <return field>)rl_regex_multi(<ref list>, <pattern field>, <match value>, <return field>)
With these functions, the match value is compared against the value of the regex expression in patten field
Also, the “first” versions return the respective first matches, while the “multi” versions return all respective matches as a list of matches.
Examples:
rl_lookup_first("ref_risky_services", "service", "compressnet", "description")
rl_regex_first("ref_ref_list_regex", "asset_name_pattern", "acme_001", "business_owner")
NOTE: field names for references list are downcased from the column heading in the CSV/Excel.