¶ SQL in FlexVertex: Technical Brief
Given its widespread popularity and familiarity among software developers, SQL serves as a foundational element for FlexVertex. It's integral to our mission to democratize access to information. This technical brief outlines our tailored approach to SQL, highlighting its architecture, appropriate use cases, and providing examples of optimal utilization.
¶ Overview of SQL Access and Capabilities
FlexVertex enables SQL access both programmatically via all FlexVertex APIs and interactively through two primary user interfaces: the FlexVertex Commander graphical interface and the FlexVertex Periscope command line interface. This dual approach ensures flexibility and accessibility for users with different preferences and technical expertise.
SQL in FlexVertex is not just a query language but a robust toolset equipped with object-oriented capabilities such as inheritance and polymorphism. Users can perform a variety of operations, including but not limited to:
- Creation, modification, deletion, and searching of data: SQL can handle all these tasks efficiently.
- Advanced object searching: This includes searching properties on data objects and connections, and even querying embedded objects within other objects.
- Enhanced query features: FlexVertex SQL includes filtering, sorting, result limits, a comprehensive function library, and the use of named parameters to enhance query clarity and performance.
¶ When Not to Use SQL
While SQL in FlexVertex is powerful, it has its limitations and is not suitable for certain tasks:
- Data Definition (DDL) tasks: Defining new classes should be handled by Voyager or other FlexVertex APIs.
- Data Manipulation (DML) tasks: Establishing connections between objects should also be conducted outside of SQL.
- Complex relational operations: Including identifying intricate relationships through joins, are typically managed using Voyager, which is better suited for these tasks due to its specialized capabilities.
¶ Ideal Use Cases for SQL in FlexVertex
SQL is particularly useful for:
- Interactive queries and modifications: It provides a user-friendly way to interact with data.
- Navigating the FlexVertex Data Multiverse: It serves as a starting point for data exploration, enabling users to traverse and discover relationships and dependencies across data sets.
¶ Examples of SQL in Action
To illustrate, consider the following request:
“Find all friends – up to three degrees away – of the person with the highest influencer rating. Of these people, who earns more than $100,000 and has taken a special offer package trip to a tropical destination between March 1 and July 31, 2024?”
This query aims to identify a complex set of relationships and data points across the FlexVertex Data Multiverse, and would be challenging to express in standard SQL. However, the combination of FlexVertex SQL and Voyager simplifies and expedites data traversal and visualization.
Voyage {
title "Highest influencer friends with high incomes and package trips"
author "FlexVertex"
comment "Objective: Find all direct and 3 degree of separation friends of the person with the highest influencer rating. Only bring back people with an income > 100K who’ve taken a special offer package trip to a tropical destination in between March 1 and July 31 2024 "
tab "High income package trips"
// Create the initial Journey Builder
FlexJourneyBuilder jb = journey("select * from Person where InfluencerRating = 98")
.connection() // Search the initial connection to find relevant friends
.name("Friend") // For connections named "Friend"
.target()
.where("Income > 100000") // Filter based on income greater than $100,000
.both() // Consider bidirectional connections between
.evaluate {
// Evaluation block for the initial journey
// Create a nested journey based on the results of the initial journey
FlexJourney j = journey(getTarget())
.connection("Visited") // For connections named "Visited"
.where("VisitDate >= date('2024-03-01') and VisitDate <= date('2024-07-31') and Promotion = true") // Filter based on visit date and promotion
.target()
.where("Type = 'Tropical'") // Filter based on the type being "Tropical"
.explore() // Explore the target
// Check if results exist for the nest journey
if (j.exists()) {
// If it exists, add segments and return true
addSegments(j)
return true
}
// If results for the sub journey does not exist, return false
return false
}
.build() // Build the initial journey
// Perform a breadth-first search on the initial journey
FlexJourney friendsOfInfluencer = breadthFirstSearch(jb, 0, 3, -1)
}
For instance, after executing such a query, the results might be displayed graphically, allowing users to interactively explore data by clicking through connected objects. This method is a common pattern in FlexVertex and exemplifies how our SQL implementation supports intuitive data exploration.
Another example demonstrates how results are returned and presented in a clear, tabular format, facilitating straightforward analysis and interpretation.
"Who hasn't provided any travel documentation?"
Voyage {
title "Who hasn't provided any travel documentation?"
author "FlexVertex"
comment "Find people who *have* provided travel documentation, then use that list as an exclusion"
tab "No travel documentation"
FlexJourney personsWithID = journey("Identification") // Search on objects belonging to the 'Identification' class
.connection("HasID") // Search for connections named "HasID" to find individuals with ID information
.from() // Evaluate connections coming from other objects to Identification objects
.target() // Examine the target objects (individuals with ID information)
.distinct() // Ensure distinct individuals are considered
.explore() // Execute the journey and return relevant data
FlexNamedParameters np = new FlexNamedParameters() // Create named parameters for the exclusion query
np.addParameter("list", personsWithID.getTargetKeys()) // Get the object keys of persons on this list
return query("select * from Exclude('Person', @list)", np) // Execute an exclusion query to find individuals without travel documentation
}
¶ Conclusion
This brief underscores the unique aspects of SQL implementation within FlexVertex, designed to enhance and streamline the user experience in handling complex data operations. For a deeper dive into the specifics of SQL syntax and advanced functionalities, users are encouraged to consult our comprehensive SQL guide.
By integrating standard SQL capabilities with our specialized enhancements, FlexVertex continues to empower users to manage and navigate data in increasingly sophisticated ways, aligning with our core mission of information democratization.