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

Siddartha Reddy Jammula 20 Reputation points
2024-06-13T01:31:15.3166667+00:00

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?

Azure Cosmos DB
Azure Cosmos DB
An Azure NoSQL database service for app development.
1,537 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
10,155 questions
{count} votes

1 answer

Sort by: Most helpful
  1. Amira Bedhiafi 19,781 Reputation points
    2024-06-13T10:15:11.01+00:00

    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.

    0 comments No comments