How to write a SQL query in ADF lookup activity for cosmos gremlin DB(json data)?

Question

I have the gremlin cosmos db and i loaded the data as vertices and edges. when i am trying to read the data using adf look up activity(select * from grem_db_test), I am getting data in the json format which is fine. But why it's not supporting when i run nested select or join or union statements to run complex queries on json data?

Answer

Gremlin is a graph traversal language used to query graph databases and let's agree that it is powerful for traversing and querying graph data. Still, it doesn't directly support SQL-style queries with nested selects, joins, or unions.

In you case, you need to use Gremlin steps and traversals rather than SQL syntax :

To fetch all vertices from a graph:


g.V()

To filter vertices based on a property:


g.V().has('propertyKey', 'propertyValue')

To traverse edges and get connected vertices:


g.V().has('propertyKey', 'propertyValue').out('edgeLabel')

Then you can use ADF Lookup activity can execute a Gremlin query and return results in JSON format.


{

  "query": "g.V().hasLabel('person').has('age', gt(30))"

}

You may need to break down your queries into multiple Gremlin traversals for complex operations involving multiple steps or conditions. Here's an example of a more complex traversal:

Example: Find friends of people over 30


g.V().hasLabel('person').has('age', gt(30)).out('knows').dedup()

This query finds all vertices with the label 'person' who is over 30 years old and then traverses the 'knows' edges to find their friends, removing duplicates.

If you need to combine results from different queries (like a UNION in SQL), you will have to handle this programmatically within your application or data pipeline after retrieving the results from Cosmos DB.

Share via

How to write a SQL query in ADF lookup activity for cosmos gremlin DB(json data)?

1 answer