Over a million developers have joined DZone.
{{announcement.body}}
{{announcement.title}}

How Our CEQL Compares to SQL Queries

DZone's Guide to

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.

· Integration Zone ·
Free Resource

The Future of Enterprise Integration: Learn how organizations are re-architecting their integration strategy with data-driven app integration for true digital transformation.

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:

  1. Contacts that have been modified since a specific date.
  2. Specific contact by email? Determines POST vs. PATCH.
  3. 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.

CEQL Examples

  1. where=Name LIKE '%Foo%'
  2. where=Name='Foo Account' AND Industry='Biotechnology'
  3. 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?

Some Examples

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.

CEQL

Translated:

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:

CEQL

Translated:

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.

For additional information about CEQL, specific to a given Element, check out our API Documentation on the endpoint you care about, like Salesforce, for example.

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:

  1. When building a platform strategy - how can you drive new revenue opportunities or deliver value through API integration.
  2. By understanding drivers of adoption, you can change how developers and end-users consume APIs and integration services.
  3. The strategic shift of integration has widely been driven by enterprise applications, like Enterprise Resource Planning (ERP).
  4. 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.

Make your mark on the industry’s leading annual report. Fill out the State of API Integration 2019 Survey and receive $25 to the Cloud Elements store.

Topics:
api ,integration ,api integration ,query language

Published at DZone with permission of

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}