Home Services Partners Company
Integrated UI with DataDirect Cloud REST API

Integrated UI with DataDirect Cloud REST API

December 07, 2015 0 Comments

If you work with data regularly, DataDirect Cloud's REST API can make your life easier. Use it to easily manage your resources and automate underlying operations based on user inputs.

Getting Started

Progress® DataDirect Cloud® is already a powerful solution for data connectivity, even before we get to the REST API. If you need to connect to data that’s sitting in cloud, or data sources installed on premise behind a firewall, DataDirect Cloud has you covered. Our service supports 23 different data sources, including cloud sources (like Salesforce.com, Google Analytics), relational databases (like SQL Server, IBM DB2) and Big Data sources (like Amazon Redshift, Apache Hadoop Hive).

Apart from that it also offers a REST API service. This manages DataDirect Cloud service resources in addition to the existing website, enabling you to manage your resources programmatically. To demonstrate the use of the REST API, the following article shows how we can leverage DataDirect Cloud’s REST API to develop an SAP Lumira Data Access extension that can fetch data from 23 different data sources.

  • To start with, if you are new to Progress DataDirect Cloud, please register for a DataDirect Cloud account trial.
  • Login to DataDirect Cloud once you register. On the left side, you will find a link to Downloads, where you can download DataDirect Cloud ODBC Drivers for Windows/Linux. Download and install the DataDirect Cloud ODBC drivers file according to your OS.
  • Also download and install DataDirect Cloud On-Premise Connector if you plan to connect to a data source behind a firewall.
  • On the Dashboard go to DataDirect Cloud and click on Connect Data.

Click on 'Connect Data'

 

  • You will be redirected to the DataDirect Cloud Dashboard. The first step here would be to create a Data Source definition. On the Dashboard, you can click on the Data Sources tab to the left of Dashboard, and then you would have to click on Data Stores on the next screen.

     Connect to Your Data Store

  • You will be presented with list of cloud data sources that are currently supported by DataDirect Cloud. For this tutorial I chose Oracle Eloqua as my cloud data source.
  • Once you select your cloud data source, you will have to fill in details like Data Source Name (which can be any name you want), User ID and password for your Eloqua account and company name. If your datasource is behind a firewall, open the DataDirect On-Premise connector, copy the connector ID and paste it in your datasource General options for the field Connector ID.
  • Once you have filled in those details, you can click on Test Connection. If it is successful, you will be notified and you can save the data source.
  • Create a new User data source in ODBC Administrator. Open ODBC Administrator -> Under User data sources, Add -> Select DataDirect Cloud 2.0 as your driver and give a datasource name and save it.
  • To start developing your SAP Lumira data extension using DataDirect Cloud, you need to know which Data store [type of database supported by DataDirect Cloud, like SQL Server, Oracle, Salesforce etc.,] and Data source name that you have configured for that particular Data store in the DataDirect Cloud dashboard to connect, query and fetch the data from that data store.
  • You can get the Data Stores and Data Source Names in DataDirect Cloud using its Management REST APIs. The base URL to interact with the DataDirect Management API is: https://service.datadirectcloud.com/api/mgmt.
  • The API uses HTTP Basic Authentication to authenticate the user. All requests to the API use HTTPS. User ID and password are always transferred encrypted. The DataDirect Cloud User ID and password are encoded in the Authorization header.
  • To retrieve the list of all the data stores and their configuration options you need to send a GET Request using the URL: https://service.datadirectcloud.com/api/mgmt/datastores. This will get all the data stores that are supported at the moment in JSON format.
  • Similarly, to retrieve the list of the user defined data sources in DataDirect Cloud for various data stores, you need to send a GET request to https://service.datadirectcloud.com/api/mgmt/datasources. This will fetch all the user defined data sources in DataDirect Cloud in JSON format. In this case it will fetch the data source that you have configured on the DataDirect Cloud dashboard. Please find the sample JSON response below:

    {
      "dataSources": [
        {
          "id": "72484",
          "name": "DB2_Test",
          "dataStore": 41,
          "isGroup": false,
          "description": ""
        }
      ]
    }

  • If there are no User defined data sources, you can always create a new Data source programmatically using REST API. To do so you need to send a POST request to https://service.datadirectcloud.com/api/mgmt/datasources. Please find the sample request JSON payload to create a Salesforce Data source below:

{
  "name":             "SF2",
  "dataStore":     "1",
  "connectionType":        "Cloud",
  "description":"Salesforce connection",
  "options":         {
    "Database":    "Sales",
    "User":             "mySForceUserId",
    "Password":   "mySForcePassword",
    "SecurityToken":          "mySecurityToken",
    "StmtCallLimit":            "60"
  }
}

 

  • You are not limited to the above actions for Data Sources API. You can also update configuration options and delete existing user defined data sources in DataDirect Cloud. Additionally, you can create, view, update and delete data source groups. To learn more about Data Sources and Data Store API, see our documentation.
  • Now that you have got the Data store name and User defined Data source name, you can create queries based on syntax. This can be used for querying the Data store and query it using the ODBC Connection that you have defined in ODBC administrator.
  • In order to establish a proper connection you need to use your DataDirect Cloud credentials as UID and password for ODBC Connection and DB name as the User defined Datasource name that you have configured in DataDirect Cloud. Following is the sample code in C# that we used to connect to DataDirect Cloud using the ODBC driver.

OdbcConnection connect =  new OdbcConnection("DSN=" + <DSN Name in ODBC Administrator> + ";Uid=" + <DataDirect Cloud UserName> + ";Pwd=" + <DataDirect Cloud Password> + ";DB=" + <User defined Data source name in DataDirect Cloud that you intend to connect to> + "");

  • Once the connection is established, you can execute SQL queries to fetch the data from that data source and print the data to the console so that SAP Lumira will read the data and make it available for your visualizations.
  • You need to follow a protocol while printing the data to console so that SAP Lumira reads it correctly. Before printing the actual data you need to notify the SAP Lumira about the data that you will be printing. All the configuration info has to be printed between “beginDSInfo and “endDSInfo.” Please find below the sample code in C#:

    Console.WriteLine("beginDSInfo");

    //Configuring such that first line of data that you print has column names

    Console.WriteLine("csv_first_row_has_column_names;true;true;");

    Console.WriteLine("endDSInfo");

  • Once you have printed those above Configuration info, you can now print data to the console. Note that first row that you print has to be column names separated by commas. Then you can start printing each row onto the console where columns are separated by comma. All of this has to be printed between beginData and endData. Please find below sample code:

    Console.WriteLine("beginData");

    //print Column Names separated by comma

    //print each row where columns are separated by comma

    Console.WriteLine("endData");

  • Please find below screenshots of Data Access extension plug-in for SAP Lumira that we have developed. The following screenshots demonstrate fetching data from an IBM DB2 Server which is installed on premise behind the firewall.

IBM DB2 Server

 

Add new data set

Add new data set

 

Add new data set

 

  • In Addition to Data Source API and Data Store API, DataDirect Cloud also offers two other management APIs—the Connector and User Provisioning API. To learn more about those please visit here and here respectively.
  • To view the Source code on GitHub, please visit this link.

Get Connected with DataDirect Cloud

Now that you've seen how the DataDirect Cloud REST API can make your life easier, what are you waiting for? Take advantage of this API to manage your resources and automate underlying operations based on user inputs. Of course, this is on top of the already amazing DataDirect Cloud connectivity services. Try out Progress DataDirect Cloud today and if you have any issues while working with it, please leave a comment below and we'll be happy to help you, or feel free to reach out to sales support.

Keep on the lookout for more posts by Saikrishna and myself, as we plan on sharing more exciting tricks and tutorials in the future.

Sumit Sakar

Sumit Sarkar

Sumit Sarkar is a Chief Data Evangelist at Progress, with over 10 years experience working in the data connectivity field. The world's leading consultant on open data standards connectivity with cloud data, Sumit's interests include performance tuning of the data access layer for which he has developed a patent pending technology for its analysis; business intelligence and data warehousing for SaaS platforms; and data connectivity for aPaaS environments, with a focus on standards such as ODBC, JDBC, ADO.NET and ODATA. He is an IBM Certified Consultant for IBM Cognos Business Intelligence and TDWI member. He has presented sessions on data connectivity at various conferences including Dreamforce, Oracle OpenWorld, Strata Hadoop, MongoDB World and SAP Analytics and Business Objects Conference, among many others. 

Read next What Is Data Democratization and Why Do You Need It?
Comments
Comments are disabled in preview mode.