{"id":4545,"date":"2018-08-04T20:44:39","date_gmt":"2018-08-04T20:44:39","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=4545"},"modified":"2018-09-26T03:34:25","modified_gmt":"2018-09-26T03:34:25","slug":"pentaho-read-rest-api-in-pentaho","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/pentaho-read-rest-api-in-pentaho\/","title":{"rendered":"How to read REST API in Pentaho Kettle"},"content":{"rendered":"

Introduction<\/h2>\n

\"Pentaho<\/a>In this article, we will learn how to read REST API in Pentaho Kettle. Pentaho is a very\u00a0popular tool in Business Intelligence to integrate data for OLAP services and to\u00a0create reports and dashboards. In this article, we will use our\u00a0ZappySys ODBC\u00a0PowerPack<\/a> to connect to REST API in Pentaho Kettle.<\/p>\n

Our software requires the JDBC\u00a0connector for SQL Server and then Uses our ZappySys Gateway to connect.<\/p>\n

<\/h2>\n

Requirements<\/h2>\n
    \n
  1. First of all, we will require the\u00a0ZappySys ODBC PowerPack.<\/a><\/li>\n
  2. Secondly, we will use\u00a0Pentaho Kettle (Our example in this post uses v8.1)<\/a><\/li>\n
  3. Finally, we will require the\u00a0JDBC driver for SQL Server<\/a>.<\/li>\n<\/ol>\n

    Getting started<\/h2>\n

    First of all, we will need to create an ODBC connection to REST API using\u00a0the Windows ODBC Data Source Administrator. Next, we will create a gateway and\u00a0finally, we will invoke the data in Pentaho.<\/p>\n

    Create an ODBC connection to read REST API in Pentaho Kettle<\/h3>\n

    In this example, we will first create an ODBC connection to an URL.<\/p>\n

      \n
    1. First of all, open the ODBC Data Source administrator in Windows in the\u00a0System DSN press Add:\n
    2. Secondly, select the ZappySys JSON Driver:\n
    3. Finally, add a data source name and specify a link to a URL. In this\u00a0example, we will connect to the following url:\n
      URL: https:\/\/services.odata.org\/V3\/Northwind\/Northwind.svc\/Invoices?$format=json<\/pre>\n
      \"URL<\/a>
      REST API ODBC URL<\/p><\/div><\/li>\n<\/ol>\n
      Create a ZappySys gateway to JDBC to read REST API in Pentaho<\/h3>\n
      In order to connect to REST API, ZappySys ODBC PowerPack uses the JDBC for\u00a0SQL Server and a gateway. Let’s take a look at it:<\/p>\n
        \n
      1. First of all, open the ZappySys Gateway that is installed with the ODBC\u00a0PowerPack and add a new data source:\n
      2. Secondly, add the data source name:\n
      3. In addition, in the user’s tab, add the user test and specify a password:\n
      4. Also, in settings, press edit and add the ODBC Data Source created in the\u00a0previous section and add the user created before:\n
      5. Finally, in the General tab, you can specify the port. By default, it is 5000.
        \nMake sure to save the changes and check the gateway is started with the\u00a0start button:<\/p>\n
        \"Port<\/a>
        Default gateway with port 5000<\/p><\/div><\/li>\n<\/ol>\n
        How to read REST API in Pentaho<\/h3>\n
          \n
        1. First of all, in Pentaho, we will create a project like the following:\n
        2. Secondly, we will create a query like the following:\n
        3. In addition, we will add the following connection. We will connect to\u00a0the data source named customers created in the ZappySys Gateway with port\u00a05000 and the user created in the Gateway:\n
        4. If everything is OK, you will be able to see the CSV file generated:\n
        5. Also, you can use SQL statements. The following example shows how to do\u00a0a where filter:\n
          SELECT Shipname \r\nFROM value\r\nwhere Shipname='Consolidated Holdings'<\/pre>\n<\/li>\n
        6. Finally, we will show the first 10 rows using a group by:\n
          SELECT Shipname \r\nFROM value\r\ngroup by shipname\r\nlimit 10<\/pre>\n<\/li>\n<\/ol>\n
          How to read REST API in Pentaho (Gmail)<\/h3>\n
          The following example will show how to get Gmail information from Pentaho.<\/p>\n
            \n
          1. First of all, we will need to create a connection to Gmail in ODBC using\u00a0the ZappySys ODBC Driver (Check the Create an ODBC connection for a Pentaho REST Client\u00a0section). In the ODBC Administrator, we will add a ZappySys JSON Driver\u00a0connection.<\/li>\n
          2. Secondly, in the ZappySys ODBC driver, specify the URL and select the\u00a0OAuth connection:\n
          3. Also, in the OAuth properties specify the Google OAuth provider and the https:\/\/mail.google.com scope. Press the generate token button and specify your credentials and accept the access permission request:\n
          4. In addition, using the ZappysSys Data Gateway, add the new Data Source\u00a0and associate with the ODBC connection just created:\n
          5. Additionally, add the test user to the data source:\n
          6. In Pentaho, change the connection in the table input to connect to the Gateway:\n
          7. Finally, in the table input add the following query:\n
            \"Query<\/a>
            Connect Pentaho to Gmail<\/p><\/div><\/li>\n<\/ol>\n
            Conclusions<\/h3>\n
            To conclude, we can say that it is simple to connect to the REST API using\u00a0Pentaho with the help of ZappySys ODBC PowerPack. This tool allows to easily\u00a0query REST API data like a simple SQL Query. If you like this tool you can
            \ndownload the             ODBC PowerPack here<\/a>.<\/p>\n
            References<\/h3>\n
            For more information, refer to the following links:<\/p>\n