GraphQL is a data query language, and a runtime for executing those queries against your data.
Denodo GraphQL Service enables the execution of GraphQL queries against the Denodo virtual data model, allowing graphQL-like queries on top of any data source.
Denodo GraphQL Service follows a bottom-up approach where the API specifications are automatically generated from the metadata of the Denodo views and stored procedures.
The GraphQL Service creates a query type for each view and stored procedure of a Denodo database. The view fields appear as children of the view object, and the same occurs for the parameters, in the case of stored procedure objects. Associations in Denodo are mapped to nested relationships between the different objects, so the GraphQL queries will fetch related objects and their fields in one request.
type Query { order(order_id: Int, cust_id: Int, total_price: BigInteger, order_desc: String, order_date: Timestamp, last_updated_time: Timestamp, _first: Int, _offset: Int): [order]! } type order { order_id: Int cust_id: Int total_price: BigInteger order_desc: String order_date: Timestamp last_updated_time: Timestamp order_detail: [order_detail]! # Denodo Association
} # Custom Scalar for Timestamp. # Its format is:yyyy-MM-dd hh:mm:ss[.sss] scalar Timestamp |
GraphQL schema for the Denodo view 'order'
Denodo GraphQL Service responses are given in JSON format.
The privilege system of Denodo is enforced, so users see only the views and stored procedures they are authorized to access.
The Denodo GraphQL Service provides:
If you are using Denodo 7, the Denodo GraphQL Service is distributed as a DenodoConnect that can be downloaded on the Support site.
This Denodo GraphQL Service distribution consists of:
If you need to use a Denodo driver version different to the one that is distributed, you only need to replace this jar with the Denodo driver of the proper version.
For installing it just download the .zip file and extract the service into the desired folder.
To run it, you need Java 8 and the environment variables JAVA_HOME and PATH correctly configured.
After running the script in the /bin folder, you can use the Denodo GraphQL Service from a GraphQL client, using HTTP Basic Authentication, SPNEGO or OAuth 2.0 with Denodo-valid credentials.
Denodo GraphQL Service runs at: http://localhost:8085/graphql/<DBNAME>
The Denodo 8 version of the Denodo GraphQL Service comes out of the box with the Denodo installation itself so, by default, you will be able to access directly from the embedded web container at http://localhost:9090/denodo-graphql-service/graphql/<DBNAME>.
The Denodo GraphQL Service has the following properties in the config/application.properties:
cors.allowed-origins=* graphql.enable.filtering=true graphql.max.query.complexity=200 graphql.max.query.depth=5 graphql.endpoint=/graphql query.default-page-size=1000 server.port=8085 #Only for Denodo 7 vdp.datasource.driverClassName=com.denodo.vdp.jdbc.Driver vdp.datasource.jdbcUrl=jdbc:vdb://localhost:9999/?noAuth=true vdp.admin.user.enabled=false |
You must configure the property to include the list of allowed URLs (separate each URL by a comma), from which GraphQL requests are allowed. For example, http://foo.com, https://foo.bar.com.
Configuring the Denodo GraphQL Service with HTTPs requires two steps:
In this section we focus on the second step, as it is the one that affects the GraphQL Service configuration.
For enabling HTTPS in the Denodo GraphQL Service 7.0 you have to add Spring Boot server.ssl.* properties to the config/application.properties file. Here is an example:
# custom port instead of the default 8085 server.port=8443 # path to the key store that holds the SSL certificate server.ssl.key-store=path_to_keystore.jks # password used to generate the certificate server.ssl.key-store-password=secret # password to access the key in the key store server.ssl.key-password=another-secret |
CORS is a W3C specification implemented by browsers that allows you to specify what kind of cross domain requests are authorized.
The Denodo GraphQL Service default value of the property cors.allowed-origins is *. But this value is not allowed as it is against the CORS specification and may cause security problems.
You must configure the property to include the list of allowed URLs (separate each URL by a comma), from which GraphQL requests are allowed. For example, http://foo.com, https://foo.bar.com.
CORS requests from any other origin will be denied with the HTTP code 403 (Forbidden).
In the GraphQL website is recommended that any production GraphQL service enable GZip compression and encourage their clients to send the header:
Accept-Encoding: gzip |
Depending on your scenario, compressing the JSON response can increase the performance or not. If it turns out that it is an effective strategy, you can enable GZip in the GraphQL Service adding Spring Boot server.compression.* properties to the config/application.properties file. Here is an example:
# enable response compression server.compression.enabled=true # comma-separated list of mime types that should be compressed server.compression.mime-types=application/json, application/graphql |
Users of Denodo GraphQL Service should have the following Denodo privileges assigned:
If a view with Execute privileges has protected columns, that is, certain columns a user cannot access, the Denodo GraphQL Service will not allow these columns to be queried.
But, the Denodo GraphQL Service does not propagate protected columns to all the views derived from the ones over which you assign these restrictions. Because of this behavior, if you want to protect columns from users/roles in the Denodo GraphQL Service, you have to do it over each view individually in the Denodo server.
Denodo GraphQL Service automatically creates the GraphQL schema from the metadata of the views and stored procedures of a Denodo database.
Denodo 'hibernate' database model
The GraphQL Service generates a query type for each view and each stored procedure. The view fields appear as children of the view object, and the same occurs for the parameters, in the case of stored procedure objects. Associations in Denodo are mapped to nested relationships between the different objects, so the GraphQL queries will fetch related objects and their fields in one request.
Besides being able to use the standard GraphQL introspection mechanisms, you can easily obtain the schema of a Denodo database sending a GET request to the GraphQL Service to: http://localhost:8085/graphql/<DBNAME>/schema.json
Schema request for Denodo 'hibernate' database
The schema describes the set of possible data you can query for that Denodo database.
For example, for the Denodo 'hibernate' database model above, the Denodo GraphQL Service generates this schema:
type Query { order(cust_id: Int, order_id: Int, total_price: BigInteger, order_desc: String, order_date: Timestamp, last_updated_time: Timestamp, _first: Int, _offset: Int): [order]! order_detail(order_id: Int, prod_id: Int, price: BigInteger, last_updated_time: Timestamp, _first: Int, _offset: Int): [order_detail]! product(prod_id: Int, prod_name: String, prod_desc: String, regular_price: BigInteger, last_updated_time: Timestamp, _first: Int, _offset: Int): [product]! } type order { cust_id: Int order_id: Int total_price: BigInteger order_desc: String order_date: Timestamp last_updated_time: Timestamp order_detail: [order_detail]! # Denodo association } type order_detail { order_id: Int prod_id: Int price: BigInteger last_updated_time: Timestamp orders: order # Denodo association product: product } type product { prod_id: Int prod_name: String prod_desc: String regular_price: BigInteger last_updated_time: Timestamp order_detail: [order_detail]! # Denodo association } #Custom Scalar for Timestamp. This type uses ISO8601 scalar Timestamp |
Schema for Denodo 'hibernate' database
The Denodo GraphQL Service includes custom scalar types for handling Denodo types not defined in the GraphQL specification:
"array_type": "[{value=666-987-8549}, {value=555-125-7841}]" |
Array format
"date_type": "2018-02-04" |
LocalDate format
"intervalds_type": "1 21:01:02.345" |
IntervalDaySecond format
"intervalym_type": "-2-3" |
IntervalYearMonth format
"struct_type": "{SALARY=32330.04, NAME=Diane O'Donnell}" |
Struct format
"time_type": "16:06:11" |
Time format
"timestamp_type": "2018-02-04 17:07:06" |
Timestamp format
"timestamptz_type": "2018-02-04 16:07:06Z" |
Timestamptz format
An alternative way to discover the queries and types available at the Denodo GraphQL Service is the GraphQL introspection system.
For example, if you want to know which fields are available for the GraphQL type order, you can send a POST request to the introspection system:
Introspection request for 'order' type
{ "data": { "__type": { "name": "order", "kind": "OBJECT", "description": null, "fields": [ { "name": "order_id", "type": { "name": "Int", "kind": "SCALAR" } }, { "name": "cust_id", "type": { "name": "Int", "kind": "SCALAR" } }, { "name": "total_price", "type": { "name": "BigInteger", "kind": "SCALAR" } }, { "name": "order_desc", "type": { "name": "String", "kind": "SCALAR" } }, { "name": "order_date", "type": { "name": "Timestamp", "kind": "SCALAR" } }, { "name": "last_updated_time", "type": { "name": "Timestamp", "kind": "SCALAR" } }, { "name": "order_detail", "type": { "name": null, "kind": "NON_NULL" } } ] } } } |
Introspection response for 'order' type
This section shows how to query the Denodo GraphQL Service.
GraphQL is about asking for specific fields on objects. Queries can also traverse related objects and their fields, letting users fetch related data in one request.
In the query of the example below, we are requesting the fields order_id, order_desc and total_price of the object order. Also, for each order, we are retrieving the related object order_detail, with its fields prod_id and price.
Query for 'order' type fields
{ "data": { "order": [ { "order_id": 111, "order_desc": "Internet and phone", "order_detail": [ { "prod_id": 2000, "price": 60 }, { "prod_id": 2020, "price": 40 } ], "total_price": 100 }, { "order_id": 222, "order_desc": "Cable at discounted price", "order_detail": [ { "prod_id": 2010, "price": 15 } ], "total_price": 15 } ] } } |
Response for 'order' type fields
GraphQL allows you to pass arguments to fields to narrow results.
In the query of the example below, we are requesting the fields order_desc and total_price of the object order that has 222 as order_id.
Query with arguments
{ "data": { "order": [ { "order_desc": "Cable at discounted price", "total_price": 15 } ] } } |
Response for query with arguments
Notice that the following custom scalar types are not supported as an argument in GraphQL queries:
If you want to query for the same fields with different arguments you will need to use aliases to avoid name conflicts.
In the query of the example below, we use first_order and second_order as aliases of the two queries.
Query with aliases
{ "data": { "first_order": [ { "order_desc": "Internet and phone", "total_price": 100 } ], "second_order": [ { "order_desc": "Cable at discounted price", "total_price": 15 } ] } } |
Response for query with aliases
Fragments allow you to define a set of fields once and reuse them in all the queries you need, avoiding field repetition.
In the query of the example below, we define the orderFields fragment and use it in both queries first_order and second_order. This is a sample, but in complex scenarios fragments are really useful.
Query with fragment
{ "data": { "first_order": [ { "order_desc": "Internet and phone", "total_price": 100 } ], "second_order": [ { "order_desc": "Cable at discounted price", "total_price": 15 } ] } } |
Response for query with fragment
Variables are used when the arguments of a query are going to be dynamic. This way, when we want to use the same query with different argument values, we only have to pass a different variable rather than writing a new GraphQL query.
In the query of the example below, $price is a GraphQL variable.
Query with variables
{ "data": { "order_detail": [ { "order_id": 333, "prod_id": 2000, "last_updated_time": "2018-06-13 17:08:02", "price": 33 }, { "order_id": 333, "prod_id": 2010, "last_updated_time": "2018-06-13 17:08:02", "price": 33 } ] } } |
Response for query with variables
The Denodo GraphQL Service includes the two directives defined as mandatory in the specification:
Query with directive @include
{ "data": { "order": [ { "order_id": 111, "order_desc": "Internet and phone" }, { "order_id": 222, "order_desc": "Cable at discounted price" }, { "order_id": 333, "order_desc": "3 in one offer" } ] } } |
Response for query with directive @include
Denodo GraphQL Service implements offset-based pagination.
The query order_detail(_offset:1, _first:2) asks for the next two order_details in the list, skipping the first one.
The pagination arguments are:
Query with pagination
{ "data": { "order_detail": [ { "prod_id": 2010, "price": 33 }, { "prod_id": 2020, "price": 33 } ] } } |
Response for query with pagination
Names in GraphQL specification are limited to this ASCII subset of characters:
Denodo GraphQL service escapes disallowed characters with the Unicode escape sequence: \u4_hex_digits but replacing \ with _, as \ is not valid.
The identifiers 29client and na-me of the Denodo table below, are not allowed by the GraphQL specification.
When the Denodo GraphQL Service generates the GraphQL schema it escapes those identifiers of the 29client view:
type Query { _u00329client(client_id: String, na_u002Dme: String, surname: String, client_type: String, company_code: String, ssn: String, _first: Int, _offset: Int): [_u00329client]! } type _u00329client { client_id: String na_u002Dme: String surname: String client_type: String company_code: String ssn: String } |
GraphQL schema with non ASCII characters
Query with non ASCII characters
{ "data": { "_u00329client": [ { "na_u002Dme": "John", "client_id": "C001", "client_type": "01", "company_code": "COM01", "ssn": "592-76-8867", "surname": "Smith" }, ... ] } } |
Response for query with non ASCII characters
Beyond the GraphQL standard features, Denodo GraphQL Service allows to specify complex filters, sort data, group results and calculate aggregation functions when the property graphql.enable.filtering is true.
When graphql.enable.filtering is true, a new optional argument _filter is added to each query definition in the schema generated by Denodo GraphQL Service.
This way, the user can query the GraphQL Service using conditions other than equality and combine them using the and, or and not operators.
In the query of the example below, we select the films that have a rental duration equal to 3 days and a rental rate greater than 3:
Query with complex filtering
{ "data": { "film": [ { "film_id": 2, "title": "ACE GOLDFINGER", "description": "An Astounding Epistle of a Database Administrator And an Explorer who must Find a Car in Ancient China", "length": 48, "rental_duration": 3, "rental_rate": 4.99 }, ... ] } } |
Response for query with complex filtering
Besides the _filter argument the required input types are added to the schema:
type Query { order(cust_id: Int, order_id: Int, total_price: BigInteger, order_desc: String, order_date: Timestamp, last_updated_time: Timestamp, _first: Int, _offset: Int, _filter: FilterInput, _orderBy: [SortInput!], _groupBy: [String!]): [order]! } # Only one is allowed: eq | neq | gt | gte | lt | lte | in | isnull | like | containsand | containsor | iscontained | regexp_like | regexp_ilike input FilterExpressionInput { containsand: String containsor: String eq: String field: String! gt: String gte: String in: String iscontained: String isnull: String like: String lt: String lte: String neq: String regexp_ilike: String regexp_like: String } # Only one is allowed: and | expression | not | or input FilterInput { and: [FilterInput!] expression: FilterExpressionInput not: FilterInput or: [FilterInput!] } |
GraphQL schema for complex filtering
The comparison operators that can be used in complex filtering are the ones defined in the input type FilterExpressionInput and are explained in the Comparison Operators section in the Virtual DataPort Administration Guide.
Note that the operators containsand, containsor and iscontained are deprecated in Denodo 7 and no longer implemented in Denodo 8 and above.
The following custom scalar types are not supported as an input field in this kind of filtering:
Also, fields cannot be specified at the same time in the _filter argument and in the field selection set:
Invalid query with complex filtering
When graphql.enable.filtering is true, a new argument _orderby is added to each query in the schema generated by Denodo GraphQL Service.
This way, the user can sort data when querying the GraphQL Service.
In the query of the example below, we sort films by their length in a descending order and by their rental rate in ascending order:
Query with sorting
{ "data": { "film": [ … { "film_id": 991, "title": "WORST BANGER", "description": "A Thrilling Drama of a Madman And a Dentist who must Conquer a Boy in The Outback", "length": 185, "release_year": "2006-01-01", "rental_rate": 2.99 }, { "film_id": 141, "title": "CHICAGO NORTH", "description": "A Fateful Yarn of a Mad Cow And a Waitress who must Battle a Student in California", "length": 185, "release_year": "2006-01-01", "rental_rate": 4.99 }, ... ] } } |
Response for query with sorting
Besides the _orderby argument the required input type is added to the schema:
type Query { order(cust_id: Int, order_id: Int, total_price: BigInteger, order_desc: String, order_date: Timestamp, last_updated_time: Timestamp, _first: Int, _offset: Int, _filter: FilterInput, _orderBy: [SortInput!], _groupBy: [String!]): [order]! } #Only one is allowed: asc | desc input SortInput { asc: String desc: String } |
GraphQL schema for sorting
In the schema generated by Denodo GraphQL Service, when graphql.enable.filtering is true, a new argument _groupBy is added to each query and an _aggregation field is added to each view type.
This way, users can group the results and calculate aggregate values (average, sum, max, …) of their data.
In the query of the example below, we retrieve the average rental rate according to the length of films:
Query with group by and aggregation functions
{ "data": { "film": [ { "_aggregation": [ { "avg": { "field": "rental_rate", "value": 2.59 } } "length": 46 }, ... ] } } |
Response for query with group by and aggregation functions
Besides the _groupBy argument and the _aggregation field, the required types are added to the schema:
type Query { order(cust_id: Int, order_id: Int, total_price: BigInteger, order_desc: String, order_date: Timestamp, last_updated_time: Timestamp, _first: Int, _offset: Int, _filter: FilterInput, _orderBy: [SortInput!], _groupBy: [String!]): [order]! } type order { cust_id: Int order_id: Int total_price: BigInteger order_desc: String last_updated_time: Timestamp order_date: Timestamp order_detail: [order_detail]! # Denodo association _aggregation: AggregationFunction! } type Aggregated { field: String! value: String! } type AggregationFunction { avg(field: String): Aggregated count: BigInteger first(field: String): Aggregated last(field: String): Aggregated max(field: String): Aggregated median(field: String): Aggregated min(field: String): Aggregated stdev(field: String): Aggregated stdevp(field: String): Aggregated sum(field: String): Aggregated var(field: String): Aggregated varp(field: String): Aggregated } |
GraphQL schema for group by and aggregation functions
The aggregation functions that can be used are the ones defined in the type AggregationFunction and are explained in the Aggregation Functions section in the Virtual DataPort Administration Guide.
As functions are fields in the AggregationFunction type, we are not able to use the same aggregation function more than once unless we use the alias mechanism provided by GraphQL. Naming each function call with a unique name inside the _aggregation clause will let you repeat any function.
Query with a repeated aggregation function (max)
{ "data": { "film": [ { "_aggregation": { "count": 1000, "max1": { "field": "rental_rate", "value": "4.99" }, "max2": { "field": "rental_duration", "value": "7" }, "max3": { "field": "length", "value": "185" } } } ] } } |
Any client using the Denodo GraphQL Service needs to authenticate itself with Denodo-valid credentials.
The authentication methods available are described below.
In HTTP Basic the Denodo user name and password are passed as unencrypted base64 encoded text with an Authorization HTTP request header.
<username>:<password> -> Authorization: Basic YWRtaW46YWRtaW4=
The HTTP SPNEGO authentication method can only be used when Kerberos authentication is enabled on the Denodo Server. The section Kerberos Authentication of the Virtual DataPort Administration Guide explains how to enable this.
Also, the client has to be configured to use SPNEGO and Kerberos. This article, (section Client Configuration), explains how to configure Internet Explorer and Mozilla Firefox to use the Kerberos-SPNEGO authentication. No special configuration is needed for Google Chrome.
When you connect from a browser to the Denodo GraphQL Service using SPNEGO and Kerberos, instead of having to enter your credentials, the browser will obtain a Kerberos ticket from the system and send it with the request. If the browser requests your credentials, it means that the browser is not correctly configured to use Kerberos authentication or that there was an authentication error.
Notice that for accessing the Denodo GraphQL Service, you have to use the Fully Qualified Domain Name of the Server Principal Name you configured in the Denodo Server. If the server hostname differs, SPNEGO authentication will fail. For example, if the server principal is HTTP/denodo-prod.subnet1.contoso.com@CONTOSO.COM, the client should use http://denodo-prod.subnet1.contoso.com:8085/graphql/<DBNAME>.
To use OAuth2 as an authentication method, you need to enable OAuth authentication on the Denodo server. The section OAuth Authentication of the Virtual DataPort Administration Guide explains how to do this.
Then, you need to obtain an access token. You can use the OAuth 2.0 credentials wizard in VDP Admin Tool to obtain it. The wizard is available at Tools → OAuth credentials wizards → OAuth 2.0 Wizard.
OAuth 2.0 Credentials Wizard
Once you have the access token you have to pass it to the Denodo GraphQL Service with an Authorization request header:
oauth_token -> Authorization: Bearer oauth_token
Theoretically, GraphQL could allow users to submit very expensive nested queries that could be damaging for the performance of the server.
For this reason, the Denodo GraphQL Service prevents the execution of queries if any of these conditions are met:
The complexity is based on the number of fields requested and it is calculated using the formula: 1 + childComplexity.
Mutations and subscriptions are not supported.
The _groupBy clause is only supported for fields of the main entity of the query. For example:
Grouping by fields of related entities is not supported by the Denodo GraphQL Service.
{ "errors": [ { "message": "Error in _groupBy clause: Grouping by navigational fields is not supported.", "extensions": { "orderByField": "orders.total_price", "invalidClause": "_groupBy", "classification": "ValidationError" } } ], "data": { "order_detail": [] } } |
Due to the output validation and serialization requirements imposed by the GraphQL specification, it’s recommended to use large page sizes if the result sets are potentially very large. This way you will reduce the number of pages needed to iterate the result and overall data retrieval performance will improve. However, note that the page size could affect the amount of server memory used by the Denodo GraphQL Service.