How Our CEQL Compares to SQL Queries
How Our CEQL Compares to SQL Queries
A look at how CEQL, a query language invited by the team at Cloud Elements, compares to SQL when working with APIs in your app.
Join the DZone community and get the full member experience.Join For Free
Our Elements are the root of what we do but don't call them Connectors, it doesn't do them justice. Their job is to provide a standard way to connect to endpoints, regardless of the nuances of each given endpoint, and frankly, is not as easy as it sounds.
One of these subtleties is with the query language required to filter data when retrieving it from an endpoint using API resources - it ranges from slight variations of SQL queries to OData or XML RPC filter queries. Some endpoints have their own, modified version of SQL queries (e.g. Bullhorn) in order to meet their specific search use cases, others use specialized query languages like SPARQL (e.g. Docustore).
Why is this important in your integrations? Because querying for specific data is required to meet so many use cases, for example, search for:
- Contacts that have been modified since a specific date.
- Specific contact by email? Determines POST vs. PATCH.
- Contacts added recently, ordered by lastName.
As a developer that is in the throes of adding multiple integrations to a product, you are faced with discovering, researching/learning, developing, and testing each endpoints specific query language capabilities. You need to know how to apply them to your specific product use cases. Essentially, you have to become an expert in each and will likely have to build an entire framework just for this piece alone.
As a product manager, it is doubtful that you have the resources to support your developers in their quest to hook up the variety of endpoint query languages in your product. That money is better spent on adding capabilities and features to your product to keep you competitive in the marketplace.
So, What Does Cloud Elements Do About This?
When we build an Element and add it to our catalog, we normalize the query language to our standard, called Cloud Elements Query Language, or CEQL. This is very similar to SQL queries and thus has a very small learning curve — just a simple where clause and you are good to go.
- where=Name LIKE '%Foo%'
- where=Name='Foo Account' AND Industry='Biotechnology'
- Name='GenePoint' OR (Industry='Biotechnology' AND LastModifiedDate>'2015-01-01T00:00:00.000Z')
What about when the endpoint's native query capabilities are not variations of SQL? How does Cloud Elements translate CEQL to the native query language?
SOAP, e.g. Netsuite, Salesforce Marketing Cloud (ExactTarget), and Autotask:
where = firstName = 'Dave' <query> <field> firstName <expression op="equals">Dave</expression> </field> </query> where = firstName = 'Dave' AND lastName = 'Honan' <query> <condition operator="AND"> <field> firstName <expression op="equals">Dave</expression> </field> </condition> <condition operator="AND"> <field> lastName <expression op="equals">Honan</expression> </field> </condition> </query>
OData, e.g. Dynamics, SAP C4C, GoodData, Microsoft Graph, etc.
where = firstName = 'Dave' $filter = firstName eq 'Dave' where = firstName = 'Dave' AND lastName = 'Honan' $filter = firstName eq 'Dave' and lastName eq 'Honan' where = firstName = 'Dave' AND lastName like 'Hon*' $filter = firstName eq 'Dave' and contains(lastName,'Hon*')
XML RPC, e.g. Intacct:
where = firstName = 'Dave' <filter> <expression> <field>firstName</field> <operator>=</operator> <value>Dave</value> </expression> </filter> where = firstName = 'Dave' AND lastName = 'Honan' <filter> <logical logical_operator="and"> <expression> <field>firstName</field> <operator>=</operator> <value>Dave</value> </expression> <expression> <field>lastName</field> <operator>=</operator> <value>Honan</value> </expression> </logical> </filter>
As you can see, the Element is more than a connector - it can speak query across many different languages so you don't have to.
And, just when you thought CEQL was exciting enough as is, guess what else an Element uses CEQL for? You got it, for our Eventing Framework to poll the endpoint for changes and also for Bulk APIs to query for and retrieve data in quantity. Elements rock.
The 2018 State of API Integration Report
Last year, the State of API Integration Report was our first inaugural research report that sought to sort through the proliferation of APIs setting the benchmark for our research on API integration. This year’s report builds on observations from 2017, with the help of over 400 API enthusiasts who took the State of API Integration Survey at the end of last year.
With over 60% of the respondents agreeing that API Integration is critical to their business strategy, our report focused in on four key trends:
- When building a platform strategy - how can you drive new revenue opportunities or deliver value through API integration.
- By understanding drivers of adoption, you can change how developers and end-users consume APIs and integration services.
- The strategic shift of integration has widely been driven by enterprise applications, like Enterprise Resource Planning (ERP).
- An evolution of B2B integration has organizations evolving the way they share and synchronize data with partners.
With expert contributions and insights from:
- Ross Garrett, VP of Marketing at Cloud Elements
- Kin Lane, The API Evangelist
- Isabelle Mauny, co-founder and CTO of 42Crunch
- Mark Boyd, API Industry Analyst and Founder of Platformable
The comprehensive research covers sections including a breakdown of the data, trends, common API business models, and event-driven integration. Download it here to receive the report directly to your inbox.
Published at DZone with permission of David Honan , DZone MVB. See the original article here.
Opinions expressed by DZone contributors are their own.