Map a Table to a Graph with the Mapping Editor With GraphXR’s Mapping Editor you can map data in a single CSV file or table of SQL query results to a property graph. You need not import the entire table. You just build a graph from the information you want. The Mapping Editor expects a table with column headings, either for a CSV file or SQL query. If an SQL query returns a table without column headings, or returns data in some other form, it cannot be mapped. A mapping queries the table file and converts it to a property graph with the categories, key values, properties, and connecting relationships you define. Saved mappings persist in the project until you delete them. Once a mapping is created, you can apply, edit, export, or delete it. Mapping Editor options let you: Map a table column as a static category label and key value, select additional columns as properties and define connecting relationships. Map the values within a table column as dynamic category or relationship labels. Unique values within a column are mapped to separate categories (or relationships), and nodes and edges generated accordingly. Dynamic mapping provides a graph that has greater immediate visual detail. This can be useful for working with a table that is updated periodically. Create a Static Mapping To demonstrate, we use a CSV file that contains a table of photo images and their metadata, including column headers for photographer, image ID, size, date created, keywords and ratings, and location (place names and latitude-longitude coordinates). We create a static mapping to generate a property graph that visually links photos, authors, and locations. We’ll define the following: Categories and their properties: Photos properties: pkey_photo (key), name, url, image size_, date created, provider, copyright, keywords, rating, and place name. Location properties: pkey_region (key), latitude and longitude, place name, state, country, and altitude Author properties: provider(key) and copyright Relationships: TAKEN_AT : to connect Photos and Locations TAKEN_BY: To connect Author and Photos Properties can also be assigned to relationships; this static mapping doesn’t need any. To create and apply a static mapping: Open the Query > CSV subpanel, click Load CSV, navigate to the CSV on your system and click Open. Notice that the Mapping Editor is available only after you open the CSV file. The file name and number of rows and columns are displayed under the Load CSV button. The contents of the file including its column headings are displayed in the data table below, 10 rows at a time. Below the table are controls to create, update, edit, and apply a mapping. Scroll down to the mapping controls and click New to display the Mapping Editor. The Mapping Editor window is divided into left and right functional areas: On the left, you add icons representing categories and relationships, and save or export the mapping. The first category icon, labeled Category1, is created automatically and selected. On the right, you enter the Category Name (or Relationship Name), view Sample Data from the CSV, and specify the Properties to be mapped from the CSV ColumnName to a PropertyName for the current category or relationship. First we create our three linked categories: Roll over the Category1 icon and option-click drag in the purple highlighted ring to create a second category and a line icon representing the connecting relationship. Repeat to create the third category icon and its relationship. For the Photos category, click the Category1 icon to select and edit its name, add properties, and specify the key: Change the CategoryName to Photos. Add properties using the Add Property dropdown menu, or by clicking the + icon next to a column in the Sample Data table. Use the horizontal scroll bar to see all the columns in the sample data. Each column name appears in the Properties list below the sample data as you add it. The default Property Name is the same as the column name, but you can edit it. If you add a property by mistake, click its trash can icon in the Remove column. Set the pkeyPhoto property as the key by clicking its AsKey checkbox. Although you can set AsKey on more than one property, many tables include a unique identifier in each row, so that you only need to set one AsKey property. For the Author category, click to edit the second category icon, change the name to Author, add the Provider property column, change the property name to authorName, and click its AsKey checkbox. For the Location category, click to edit the third category icon, change the name to Location, and add properties. At a minimum, we need a key value that uniquely identifies the location (pkey_region), and the latitude and longitude properties so that we can display the Location nodes on a world map. We add other properties relating to locations, such as PlaceName, Altitude, City, and Country. Next, edit the relationship names. Click the line icon between Photos and Author (labeled Category1_Category2), and change the Relationship Name to TAKEN_BY. Click the line icon between Photos and Location (labeled Category1_Category3), and change the Relationship Name to TAKEN_AT. If desired, you can click the Direction Setting checkbox for options to change the direction of a relationship. A standard naming convention for categories and relationships makes mappings and the resulting graph data more readable. A generally used (but optional) convention is capitalized category names (Photos), upper case with words separated by underscore characters for relationship names (TAKEN_AT), and camel-case property names (e.g. pkeyPhoto). Click the Save As icon in the upper left to save the completed mapping with a descriptive name and return to the CSV query panel. The name now appears on the Mapping menu. Click *Apply to apply it to the loaded CSV. A property graph is generated from the CSV table, with Location nodes dropped on a map. We arranged the connected Photo and Author nodes (which do not have lat-long property values) above the world map using additional geometric Line and Ring layouts. Create a Dynamic Mapping A dynamic mapping creates a new category or relationship for each unique value of the key property (i.e. set AsKey). To specify a dynamic category or relationship, you enter the CSV (or SQL table) column name in curly braces { } in the Mapping Editor’s Category Name or Relationship Name field. For more readable labels, additional text can be included both before and after the column name (e.g. Text{Column Name}_MoreText_). As an example, we’ll create a dynamic mapping of our CSV table of photo assets that generates the graph shown below. This mapping is designed to highlight authorship and ratings of photos by location, and to extract new location categories and author relationships when we update the table and re-apply the mapping. The mapping specifies: Static Photos and Author categories. Dynamic {PlaceName} categories for the unique values found in the PlaceName column. If a new PlaceName is added to the table, it will appear in the updated graph. Dynamic TAKEN_BY{Provider} relationships for the unique values in the table’s original Provider column. Each author’s connection to Photo nodes is highlighted. A new author added to the table will appear in the updated graph. Dynamic RATED_AT{Rating}_ relationships which can be colored separately, and which connect Photo nodes to their dynamic PlaceName category. To create a mapping with dynamic entities: In the Query panel, open the CSV tab, click Load CSV, navigate to the CSV file, and click Open to load it as a table. Scroll to the bottom of the panel, and click New to display the Mapping Editor. The Mapping Editor window is divided into left and right functional areas: On the left, you add categories and relationships, and save or export your mapping. On the right, you enter a Category Name (or Relationship Name) and the properties to be included. For a dynamic category or relationship you must enter a column heading from the original table in curly braces (e.g. {PlaceName}). For a selected property, you can edit the PropertyName. It does not need to match the CSV ColumnName. First we create our three linked categories: Roll over the Category1 icon and _option-click drag _ in the purple highlighted ring to create a second category and a line icon representing the connecting relationship. Repeat to create the third category icon and its relationship. For the Photos category, click the Category1 icon to select and edit its name, add properties, and specify the key: Change the CategoryName to Photos. Add properties using the Add Property dropdown menu, or by clicking the + icon next to a column in the Sample Data table. Use the horizontal scroll bar to see all the columns in the sample data. Each column name appears in the Properties list below the sample data as you add it. The default Property Name is the same as the column name, but you can edit it. If you add a property by mistake, click its trash can icon in the Remove column. Set the pkeyPhoto property as the key by clicking its AsKey checkbox. For the Author category, click to edit the second category icon, change the name to Author, add the Provider property column, change the property name to authorName, and click its AsKey checkbox. For the dynamic {PlaceName} category, click to edit the third category icon: Change the name to {PlaceName}, to extract separate categories for each value in the CSV’s PlaceName column. Since these values are descriptive text, they will work well as category labels for the locations in the table. The name you enter in the curly braces must exactly match the column header name in the original table file. If the mapping can’t match the name (for example, because it is misspelled), a relationship will be created but will be labeled "undefined". Select properties. At a minimum, we need a property set AsKey that uniquely identifies the location (e.g. PlaceName), and the latitude and longitude properties so that we can display the nodes on a world map. Add other properties relating to locations such as Altitude, City, and Country. Next, edit the relationship names to create dynamic relationships. Click the line icon between Photos and Author (labeled Category1_Category3), and change the Relationship Name to TAKEN_BY{Provider}. This creates separate for edges for each author between Photo and Author nodes that can be colored by the value of the Provider name. Click the line icon between Photos and {PlaceName}), and change the Relationship Name to _RATED_AS{Provider}. This creates edges between Photo and dynamic location nodes that can be colored by the value of the rating. Click the Save As icon in the upper left to save the completed mapping (with a descriptive name) and return to the CSV query panel. The name now appears on the Mapping menu. Click Apply to apply it to the CSV table. A property graph is generated, with: Static Photo and Author categories and nodes. Separate categories for each location, mapped from the unique values in the original table’s PlaceName column. Separate TAKEN_BY edges between Author and Photo nodes, mapped from unique values in the CSV table’s Provider column Separate RATED_AS edges between Photo and {PlaceName} nodes, mapped from unique values in the CSV table’s PlaceName column. Apply a saved mapping You can apply a saved mapping to any CSV or SQL table that has the same column headings and properties specified in the mapping. To apply a saved mapping: Open the Query > CSV subpanel, click Load CSV, navigate to a CSV on your system and click Open. Select the mapping from the Mapping dropdown menu and click Apply. If you apply a mapping to a file or table that does not have the required column headings or properties, the following message displays: "Mapping not compatible with the data." Edit a mapping You can edit a mapping at any time and either Save it, or use Save As to save it as a new mapping. Since a CSV will still be loaded, you can easily edit a mapping and then re-apply it. To edit a mapping: Open the Query > CSV subpanel, click Load CSV, navigate to the CSV on your system and click Open. Select the mapping you want to edit from the Mapping dropdown menu and click Edit. Edit, add or delete categories, relationships, and their properties. Click either the Save As icon to save your work as a new mapping, or the Save and Exit icon to save to the current mapping. To exit the editor without saving changes, click the X at the upper right of the Mapping Editor window. Export or Import a mapping You can export a mapping as a JSON file, and import it to a different (or the same) project at any time. Mappings persist in a project until you delete them, but it is good practice to export mappings you’ve created before you exit a project. To export a mapping as a JSON file: Open the Query > CSV subpanel, click Load CSV, navigate to the CSV on your system and click Open. Select the mapping in the dropdown menu, and click Edit to open the Mapping Editor. Click the Export Schema icon at the top left. A JSON file for the mapping is downloaded to your local machine. To import a mapping saved as a JSON file: Open the Query > CSV subpanel, click Load CSV, navigate to the CSV on your system and click Open. Click Import, navigate to the JSON file, and click Open. The mapping appears in the Mapping menu. Select the mapping and click Apply to map the loaded CSV data. If you choose a mapping that was not defined on the CSV file you loaded, the following message displays: "Mapping not compatible with the data." Delete a mapping You can delete any mapping at any time. You need not load a CSV or SQL table first. To delete a mapping: Open the Query > CSV subpanel. Select the mapping you want to delete from the Mapping menu and click Delete.