If I understand this correctly, every query that is sent when using the Apache AGE extension is being parsed, analyzed, rewritten, and it eventually becomes an SQL statement that is being run in the backend process for postgres to actually execute the command.
When we use a simple match query like
SELECT * FROM cypher('graph', $$ MATCH (u) return u $$) as (u agtype);
what is the SQL statement that postgres runs to actually fetch the correct vertices?
Yes, you're right about the first part and understood it exactly as well.
However, for the second part, I think the statement for the simple query "Match (u) return u" would look something like this:
SELECT * FROM vertice_table;
Here, the vertice_table is the name of the table in the database of PostgreSQL that contains the vertices of the graph. And Select * will be used to fetch all the columns.
Cypher queries written in the Apache AGE extension are rewritten to a SQL statement of the same kind for it to be executed in PostgreSQL.
It is dependent on the exact schema and model used in Apache AGE to know the exact SQL statement that will be generated. Thus, it is not possible to predict the exact SQL query that would be executed without knowing specific details about the data model and the graph schema.
E.g. let's assume the data stored in the graph table named as "nodes" and a column named "u" that represents the vertices, the SQL statement that will be generated will be as follows:
SELECT * FROM nodes WHERE <condition to match vertices>;
The sql statement that would be generated depends on how the graph is represented in postgres.
It can be something like this:
SELECT * FROM graph_vertices WHERE label = 'u';
This is only a simplified example and the actual statement can of course be quite complex.
Yes you are right actually when we run a query using Apache AGE extension , it parsed the query and analysed it and then rewrite it into a SQL statement.
The extension translate that cypher query and result
SELECT graph_properties FROM 'graph_name'
So when we run this it is actually running SQL query at backend and return properties of graph.
The PostgreSQL would look up to the vertex table and then collects (using joins in this case) and return the vertices according to the criteria and query you wanted. For example, for your query, the SQL statement would be similar to something like :
SELECT * FROM graph._ag_label_vertex AS u
JOIN graph._ag_label_edges AS e
ON u.id = e.start
The translated SQL statement would look something like this:
SELECT data FROM vertices
Lets assume the graph is stored in a table called vertices with a column called data that contains the vertex properties.
The SQL statement would retrieve all rows from the vertices table and return the data column, which contains the vertex properties which matches the output of the cypher query.
Yes, this is exactly how the Apache Age extension works. There is equivalent SQL query for each query you write in Apache AGE. As, for as the question related to Above Apache AGE query, following SQL queries will be equivalent,
SELECT * FROM graph_table WHERE label = 'u';
Now, when you look into above query, graph_table represents the table where the graph data is stored, and the label information of the nodes is represented by a column label. The WHERE clause filters the rows based on the label value 'u', retrieving the vertices that match the specified label and based on nature of query asked in Apache AGE, it can produce way more complex equivalent SQL queries.
If you want to retrieve the age of a person using a WHERE clause with some criteria, you could write a query like this:
SELECT * FROM graph_vertices WHERE label = 'u' AND properties->>'age' > '15';
Your assumption is spot on, whenever you make use of the AGE extension for querying a graph database, the first step is parsing while the second step is the analysis of the cypher query. The task of the parser is to make sure that the syntax for the query is valid or not. In the next step, the analyzer will generate an Abstract Syntax Tree which is just the tree representation of the text.
In the query that you just provided, the corresponding SQL statement that would be executed is as follows:
SELECT *
FROM graph_vertices
WHERE graph_vertices._label = 'u';
Explanation: The above SQL statement would select only those vertices from the graph that has the label 'u'. ApacheAge adds to the graph vertices a special column named '_label' which stores the label of the vertex.
Yes, in Apache AGE every cypher query is internally translated into appropriate SQL statement that can be executed by postgreSQL. The Query that you have provided, it will look something like below in SQL query.
SELECT * FROM vertices_table WHERE vertex_label = 'u';
Here assume that all the nodes in graph are stored in a table named vertices_table, the query will retreive all columns of the table and then where clause will search for the the row having value for vertex_label column as u.
Basically uses the normal SQL statements too…
For example
MATCH (u:persons) RETURN u
Is equivalent to
SELECT * FROM persons
And also where the WHERE clause is used, same idea is done in the SQL statements.. and of course it can get more complicated depending on the relationships..
Yes, that's correct, the query moves through these stages till it becomes a sql statement in the end, In this particular example you showed the cypher query is meant to fetch all vertices, which would be translated to a sql statement looks something like this one: SELECT * FROM vertices_table, this is the sql statement used by postgres to fetch all vertices. I hope this answers you question.
SELECT * FROM cypher('graph_name', $$
MATCH (:Person {name: 'Mark Smith'})-[]-(movie:Movie)
RETURN movie.title
$$) as (title agtype);
The above code returns any vertices connected with the person named 'Mark Smith' that are labeled Movie
