{"id":6012,"date":"2019-01-12T20:18:31","date_gmt":"2019-01-12T20:18:31","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=6012"},"modified":"2024-08-05T17:37:04","modified_gmt":"2024-08-05T17:37:04","slug":"read-write-rest-api-data-in-talend-json-xml-soap","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/read-write-rest-api-data-in-talend-json-xml-soap\/","title":{"rendered":"Read \/ Write REST API data in Talend (JSON \/ XML \/ SOAP)"},"content":{"rendered":"

Introduction<\/h2>\n
UPDATE:<\/strong> Please visit Talend Studio Connectors<\/a>\u00a0page in API Integration Hub<\/a> to find updated articles on various Talend Studio integrations, including integrations with REST API<\/a>, SOAP API<\/a>, JSON file<\/a>, and XML file<\/a>.<\/div><\/div>\n

\"\"In this post we will learn how to read \/ write REST API data in Talend Open Studio. We will create a simple Talend Job using ZappySys JSON\u00a0 Driver<\/a>\u00a0to read from REST API \/ JSON Files and load into Target (e.g. File \/ DB). Techniques listed in this article can be also used to read from SOAP API \/ XML Files or CSV Files \/ API using XML Driver<\/a> \/ CSV Driver<\/a>.<\/p>\n

These drivers support familiar SQL query language. Using SQL you can query virtually any API services just like relational database table. It can flatten nested hierarchy and provide output in rows \/ columns. Many complex REST API \/ SOAP API complexity is taken care automatically (e.g. Authentication, Pagination, Security, Error Handling).<\/p>\n

So let’s get started.<\/p>\n

 <\/p>\n

Requirements<\/h2>\n
    \n
  1. Download and install Talend Open Studio (FREE) from here<\/a>. Skip this step if you already installed.<\/li>\n
  2. Download ZappySys ODBC PowerPack<\/a> (JSON \/ XML Drivers)<\/li>\n
  3. Get Microsoft JDBC driver<\/strong> for SQL Server<\/strong> from here<\/a>\u00a0(Download\u00a0sqljdbc_6.0.8112.200_enu.exe<\/strong> which is self extracting file you can run and extract to some folder)
    \nAfter you extract jdbc files, go to\u00a0sqljdbc_6.0\\enu\\jre8\\ folder rename\u00a0sqljdbc42.jar<\/strong> to\u00a0\u00a0mssql-jdbc.jar<\/strong> (name must be this). We will load this file in Talend later in this article.<\/li>\n
  4. Basic knowledge about REST API and JSON \/ XML format.<\/li>\n<\/ol>\n

    Configure Data Gateway<\/h2>\n
    Now let's look at how to configure\u00a0ZappySys Data Gateway<\/a>. This feature acts as a bridge between Client App and ZappySys Drivers. Using data gateway you can use ZappySys Drivers inside applications \/ operating systems where ZappySys drivers may not be available directly for some reason (e.g. You don't have access to Server for Installation or System does not support ODBC drivers like JAVA programs). Click here to read more<\/a> on various use cases of Data Gateway.\r\n

    Configure Data Gateway User \/ Port<\/span><\/h4>\r\nNow let's look at steps to configure Data Gateway after installation. We will also create a sample data source for ODATA API (i.e. JSON based REST API Service).\r\n
      \r\n \t
    1. Assuming you have installed\u00a0ZappySys ODBC PowerPack<\/a>\u00a0using default options (Which also enables Data Gateway Service)<\/li>\r\n \t
    2. Search \"Gateway\" in your start menu and click ZappySys Data Gateway\r\n
      \r\n\"Open<\/a>\r\n

      Open ZappySys Data Gateway<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    3. First make sure Gateway Service is running (Verify Start icon is disabled)<\/li>\r\n \t
    4. Also verify Port on General Tab\r\n
      \r\n\"Port<\/a>\r\n

      Port Number setting on ZappySys Data Gateway<\/p>\r\n\r\n<\/div><\/li>\r\n \t

    5. Now go to Users tab. Click Add<\/strong> icon to add a new user. Check Is admin to give access to all data sources you add in future. If you don't check admin then you have to manually configure user permission for each data source.\r\n
      \r\n\"Add<\/a>\r\n

      Add Data Gateway User<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\n \r\n

      Configure Data Source<\/span><\/h4>\r\n
        \r\n \t
      1. After user is added, go to Data Sources tab. Click Add<\/strong>\u00a0icon to create new data source. Select appropriate driver based on your API \/ File format. You can choose Generic ODBC option to read data from ODBC DSN or use Native Driver option.\r\n
        NOTE:<\/strong> Whenever possible use native driver option for better performance \/ security and ease of use.<\/pre>\r\n
        \r\n\"Add<\/a>\r\n
        Add Gateway Data Source (Native JSON Driver)<\/p>\r\n\r\n<\/div><\/li>\r\n \t
      2. Click on \"Edit\" under Data source and configure as per your need (e.g. Url, Connection, Request Method, Content Type, Body, Pagination etc.). For this demo we are going to pick simple JSON REST API which doesn't need any authentication.\u00a0 Enter following URL.\r\n
        https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Invoices?$format=json<\/pre>\r\n<\/li>\r\n \t
      3. You can also view response structure and select default hierarchy (i.e. Filter) like below (Select Array Icon) for data extraction.\r\n
        \r\n\"Configure<\/a>\r\n
        Configure JSON API Data source<\/p>\r\n\r\n<\/div><\/li>\r\n<\/ol>\r\n
        Test SQL Query \/ Preview Data<\/span><\/h4>\r\n
          \r\n \t
        1. Now go to Preview Tab. You can click Preview button to execute default query\r\nOR\r\nSelect Table name from dropdown to generate SQL with column names.\r\n
          \r\n\"JSON<\/a>\r\n
          JSON \/ REST API Driver Query Preview \/ Query Examples (Read REST API or JSON Files)<\/p>\r\n\r\n<\/div><\/li>\r\n \t
        2. You can also click Query Builder to generate SQL using different options in WITH clause. ANy setting you specify in WITH clause will override UI settings we applied in previous steps.\r\n
          \r\n\"Using<\/a>\r\n
          Using SQL Query Builder (For Files or REST \/ SOAP API - JSON \/ XML \/ CSV Format)<\/p>\r\n\r\n<\/div><\/li>\r\n \t
        3. There is another useful option for code generation. Select your Language and quickly copy code snippet. See below Example of XML Driver Query to call SOAP API.\r\n
          \r\n\"Generate<\/a>\r\n
          Generate Example Code for ZappySys Driver<\/p>\r\n\r\n<\/div><\/li>\r\n \t
        4. Click OK<\/strong> to Close Data Source UI<\/li>\r\n \t
        5. Once data source is tested and configured you can click Save <\/strong>button in the Gateway UI toolbar and click Yes<\/strong> for Restart Service<\/strong>.<\/li>\r\n<\/ol>\r\n <\/div>\n
          Register MS SQL JDBC driver in Talend<\/h2>\n
          Now lets register Microsoft JDBC Driver in Talend. This is very important step because MSSQL JDBC driver is used to communicate with ZappySys Data Gateway we configured in previous step.<\/p>\n
          If you missed steps mentioned in the Requirements section then make sure you first download JDBC driver using below steps.<\/p>\n
          Get Microsoft JDBC driver<\/strong> for SQL Server<\/strong> from here<\/a>\u00a0(Download\u00a0sqljdbc_6.0.8112.200_enu.exe<\/strong> which is self extracting file you can run and extract to some folder).\u00a0After you extract jdbc files, go to\u00a0sqljdbc_6.0\\enu\\jre8\\ folder rename\u00a0sqljdbc42.jar<\/strong> to\u00a0\u00a0mssql-jdbc.jar<\/strong> (name must be this).<\/p>\n
          Now lets go through the steps to register MSSQL jdbc driver in Talend.<\/p>\n
            \n
          1. Open Talend Open Studio<\/li>\n
          2. Go to Windows<\/strong> > Click Show View<\/strong><\/strong>\n
          3. When you see Popup selection under Talend<\/strong> > Select Modules<\/strong><\/strong><\/strong>\n
          4. When Module window is visible click on Little Jar Icon (Bottle icon) in the toolbar.<\/li>\n
          5. Select\u00a0mssql-jdbc.jar<\/strong>\u00a0file we renamed earlier and load this file.\n
          6. That’s it. Now we ready to make API calls \/ read from JSON \/ XML in the next section.<\/li>\n<\/ol>\n
            Setup Talend REST API Connection (JSON \/ XML \/ CSV)<\/h2>\n
            Now let’s configure REST API Connection in Talend. To read from JSON \/ XML Files you can use same steps too. We will use MSSQL JDBC Driver to connect to ZappySys Data Gateway.<\/p>\n
              \n
            1. In Talend Go to Metadata > Db Connections (Right click) > Create Connection\n
            2. On the connection Wizard specify following attributes.
              \nDB Version :<\/strong> Microsoft
              \nLogin :<\/strong>\u00a0 username you setup in zappysys data gateway
              \nPassword :<\/strong>\u00a0 password of data gateway user
              \nServer :<\/strong> machine name or IP where zappysys data gateway is running
              \nPort :<\/strong> <default is 5000> Port on which zappysys data gateway is listening
              \nDataBase :<\/strong>\u00a0 Data source name you setup in\u00a0zappysys data gateway (case-sensitive)<\/p>\n
            3. That’s all we need to do to setup a connection which can be used to read \/ write REST API data in Talend. In the next section we will see how to create a job to read data from REST API Service using this connection.<\/li>\n<\/ol>\n
               <\/p>\n
              Read from REST API in Talend<\/h2>\n
              Now let’s look at how to read data from REST API source or JSON \/ XML File using the connection we configured in the previous section.<\/p>\n
              Configure REST API Source<\/h3>\n
                \n
              1. Create a Talend JOB and double click to open designer<\/li>\n
              2. Now drag and drop MSSQL Connection we created for ZappySys Data gateway, drop it on the designer surface. It will popup UI like below.<\/li>\n
              3. Select tDBInput<\/strong> (Microsoft SQL Server). Remember that we are using MSSQL JDBC Driver to connect to ZappySys Data Gateway for REST API Call. This gateway uses Microsoft TDS Protocol so MSSQL JDBC driver is used to communicate.<\/li>\n
              4. Now rename Source to something meaningful (e.g. Read from JSON REST API)<\/li>\n
              5. Double click REST Source to configure.<\/li>\n
              6. Enter Query<\/strong> like below (Make sure to enter between double quotes<\/strong>). See below examples to read from URL or File. If you have double quote in SQL then escape using \\” character (e.g. select \\”my col\\” from $ )Read From REST API Url<\/strong>\n
                \"SELECT * FROM value \r\nWITH (SRC='https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Customers?$format=json')\"<\/pre>\n
                Read from Local File (Wildcard Pattern Search allowed)<\/strong><\/p>\n
                \"SELECT * FROM value \r\nWITH (SRC='c:\\data\\Customers_*.json')\"<\/pre>\n<\/li>\n
              7. Click on Guess Schema<\/strong> button and Click OK to accept detected schema.\n
              8. Now we will configure target in the next section.<\/li>\n<\/ol>\n
                Configure Target (Delimited File)<\/h3>\n
                  \n
                1. Now search for “FileOut” in the toolbox (Hit Enter). You will see tFileOutputDelimited<\/strong> so just select that for now and drag on the surface.\n
                2. Double click it to configure.<\/li>\n
                3. Enter correct file path\u00a0(e.g.\u00a0“C:\/Talend\/workspace\/rest-api-output.csv” )<\/li>\n
                4. On Advanced Tab you can configure some additional settings (e.g. Throw an error if file already exists)<\/li>\n<\/ol>\n
                  Connect and Run<\/h3>\n
                    \n
                  1. Once you have configured Source and Target its time to connect them<\/li>\n
                  2. Drag Source Port to Target to connect like below.\n
                  3. Run the job\n
                  4. That’s it. So in few clicks you loaded data from REST API to File in Talend Open Studio.<\/li>\n<\/ol>\n
                    Write \/ Send data to REST API (POST Example)<\/h2>\n
                    There will be a time when you want to POST data to REST API service. Let’s check how to write POST query to submit data to REST API.<\/p>\n
                    Just like how we did Read query in previous example, we can set POST Body in the SQL Query to send data. Use query like below and click Guess Schema button. If Blank Filter gives you no data error then make sure you remove Filter on Data Gateway Data source. (Notice we used \\” to escape double quote inside query )<\/p>\n
                    \"SELECT * FROM _root_\r\nWITH (\r\n METHOD='POST'\r\n,HEADER='Content-Type: text\/plain || first-header: AAA || second-header: BBB'\r\n,SRC='http:\/\/httpbin.org\/post'\r\n,BODY='{id:1,notes:\\\"Some notes\\\"}'\r\n,FILTER=''\r\n)\r\n\"<\/pre>\n
                    \"Talend<\/a>
                    Talend – POST data to REST API URL<\/p><\/div>\n
                    SQL Query Examples<\/h2>\n
                    Click on below link to learn more writing SQL Query using ZappySys Drivers.<\/p>\n
                    JSON \/ REST Driver – SQL Query Examples<\/a><\/p>\n
                    XML \/ SOAP Driver – SQL Query Examples<\/a><\/p>\n
                    CSV \/ REST Driver – SQL Query Examples<\/a><\/p>\n
                    REST API \/ XML SOAP Pagination Settings for Talend<\/h2>\n
                    Even we set up ODBC Data Source to get the data, it may not be enough. Usually, if you are getting a huge data set from API provider, it won't give it to you in one HTTP response. Instead, it gives back only a subset of data and provides a mechanism for data pagination. The good news is that\u00a0ZappySys ODBC Driver<\/em> includes many options to cover virtually any pagination method.<\/div>\r\n
                    Below you will find a few examples of API pagination. If you need something more sophisticated check the below link (the article was written for SSIS PowerPack but UI options and concepts apply to ODBC Driver too):<\/span><\/div>\r\n
                    https:\/\/zappysys.com\/blog\/ssis-rest-api-looping-until-no-more-pages-found\/<\/a><\/div>\r\n
                    Paginate by Response Attribute<\/h3>\r\nThis example shows how to paginate API calls where you need to paginate until the last page detected. In this example, next page is indicated by some attribute called nextlink (found in response). If this attribute is missing or null then it stops fetching the next page.\r\n
                    SELECT * FROM $\r\nWITH(\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/pagination_nextlink_inarray_1.json'\r\n,NextUrlAttributeOrExpr = '$.nextlink'  --keep reading until this attribute is missing. If attribute name contains dot then use brackets like this $.['my.attr.name']\r\n)<\/pre>\r\n
                    Paginate by URL Parameter (Loop until certain StatusCode)<\/h3>\r\nThis example shows how to paginate API calls where you need to pass page number via URL. The driver keeps incrementing page number and calls next URL until the last page detected (401 error). There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).\r\n
                    SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/page-xml.aspx?page=1&mode=DetectBasedOnResponseStatusCode'\r\n,PagingMode='ByUrlParameter'\r\n,PagingByUrlAttributeName='page'\r\n,PagingByUrlEndStrategy='DetectBasedOnResponseStatusCode'\r\n,PagingByUrlCheckResponseStatusCode=401\r\n,IncrementBy=1\r\n)<\/pre>\r\n
                    Paginate by URL Path (Loop until no record)<\/h3>\r\nThis example shows how to paginate API calls where you need to pass page number via URL Path. The driver keeps incrementing page number and calls next URL until the last page is detected. There are few ways to indicate the last page (e.g. By status code, By row count, By response size). If you don't specify end detection then it will use the default (i.e. No records found).\r\n
                    SELECT * FROM $\r\nWITH (\r\nSRC=@'https:\/\/zappysys.com\/downloads\/files\/test\/cust-<%page%>.xml'\r\n,PagingMode='ByUrlPath'\r\n,PagingByUrlAttributeName='<%page%>'\r\n,PagingByUrlEndStrategy='DetectBasedOnRecordCount'\r\n,IncrementBy=1\r\n)<\/pre>\r\n
                    Paginate by Header Link (RFC 5988)<\/h3>\r\nAPI like GitHub \/ Wordpress use Next link in Headers (RFC 5988<\/a>)\r\n
                    SELECT * FROM $\r\nLIMIT 25\r\nWITH(\r\n\t Src='https:\/\/wordpress.org\/news\/wp-json\/wp\/v2\/categories?per_page=10'\r\n\t,PagingMode='ByResponseHeaderRfc5988'\r\n\t,WaitTimeMs='200' --\/\/wait 200 ms after each request\r\n)<\/pre>\r\n <\/div>\n
                    REST API \/ SOAP Web Service Connection Settings for Talend<\/h2>\n
                    If you need to authenticate or authorize your user to access a web resource, you will need to use one of\u00a0the Connections:<\/em><\/div>\r\n