如何用WSO2 ESB 4.8.1实现API调用获取SFTP中CSV并转JSON

Alright, let's break down exactly how to implement this scenario with WSO2 ESB 4.8.1 — I’ve built similar integrations before, so this is a practical, step-by-step guide:

The core workflow we need to build is:

• Client sends an API request to WSO2 ESB
• ESB connects to the SFTP server, fetches the target CSV file
• ESB transforms the CSV content into JSON format
• ESB returns the JSON response to the client

1. Configure SFTP Connectivity in ESB #

WSO2 ESB 4.8.1 comes with built-in VFS (Virtual File System) support for SFTP, so first we need to ensure the transport is enabled and configured correctly:

• Navigate to <ESB_HOME>/repository/conf/axis2/axis2.xml
• Verify that the VFSListener and VFSTransportSender entries are not commented out (they’re enabled by default, but double-check)
• For SFTP authentication, avoid hardcoding credentials directly in configurations later — we’ll use the Carbon Secure Vault for encryption (more on that in the security section)

2. Build the API Proxy Service #

Create an API to expose the CSV-to-JSON functionality to clients. Here’s a sample Synapse configuration you can use as a starting point:

<api xmlns="http://ws.apache.org/ns/synapse" name="CSVtoJSONAPI" context="/csv-to-json">
   <resource methods="GET">
      <inSequence>
         <!-- Trigger SFTP file fetch -->
         <send>
            <endpoint>
               <address uri="vfs:sftp://{SFTP_USER}:{SFTP_PASS}@{SFTP_HOST}:{SFTP_PORT}/path/to/target-file.csv?transport.vfs.Action=GET&amp;transport.vfs.ContentType=text/csv"/>
            </endpoint>
         </send>
      </inSequence>
      <outSequence>
         <!-- Convert CSV to JSON (use this for fixed-column CSV) -->
         <payloadFactory media-type="json">
            <format>
               {
                  "data_records": [
                     $1
                  ]
               }
            </format>
            <args>
               <arg evaluator="xml" expression="fn:concat('{&quot;user_id&quot;:',//record/user_id,',&quot;user_name&quot;:&quot;',//record/user_name,'&quot;},')"/>
            </args>
         </payloadFactory>
         <!-- Fix trailing comma issue with JS script -->
         <script language="js">
            var payload = mc.getPayloadJSON();
            var recordsStr = payload.data_records.join('').replace(/,$/, '');
            payload.data_records = JSON.parse('[' + recordsStr + ']');
            mc.setPayloadJSON(payload);
         </script>
         <!-- Set response content type to JSON -->
         <property name="messageType" value="application/json" scope="axis2"/>
         <send/>
      </outSequence>
      <faultSequence>
         <log level="full" category="ERROR"/>
         <respond/>
      </faultSequence>
   </resource>
</api>

3. Flexible CSV-to-JSON Conversion (Dynamic Columns) #

If your CSV has variable columns, the payload factory approach won’t work. Instead, use a Groovy script for more robust parsing:

1. Download opencsv.jar and json-lib.jar (and their dependencies)
2. Place them in <ESB_HOME>/repository/components/lib/ and restart ESB
3. Replace the outSequence’s payload factory and JS script with this Groovy script:

<script language="groovy">
   import com.opencsv.CSVReader;
   import java.io.StringReader;
   import net.sf.json.JSONArray;
   import net.sf.json.JSONObject;

   // Fetch raw CSV content from the message
   String csvContent = mc.getPayloadXML().getText();
   CSVReader reader = new CSVReader(new StringReader(csvContent));
   String[] headers = reader.readNext();
   String[] row;
   JSONArray jsonOutput = new JSONArray();

   // Map each CSV row to a JSON object
   while ((row = reader.readNext()) != null) {
      JSONObject rowObj = new JSONObject();
      for (int i = 0; i < headers.length; i++) {
         rowObj.put(headers[i].trim(), row[i].trim());
      }
      jsonOutput.add(rowObj);
   }

   // Set the final JSON payload
   mc.setPayloadJSON(jsonOutput.toString());
</script>

4. Security Best Practices #

• Encrypt SFTP Credentials: Use WSO2’s Carbon Secure Vault to store SFTP username/password, then reference them in your config with $secret{your_credential_alias} instead of plaintext
• Restrict API Access: Add OAuth2 authentication or IP whitelisting to the API to prevent unauthorized requests
• Validate Input/Output: Add schema validation to ensure the CSV content is as expected, and sanitize the JSON response to avoid injection risks

5. Testing & Debugging #

• Use tools like Postman or curl to test the API: GET http://your-esb-host:8280/csv-to-json
• Check ESB logs at <ESB_HOME>/repository/logs/wso2carbon.log to troubleshoot SFTP connection issues, parsing errors, or API failures

• Scheduled Fetch + Caching: Use the ESB Task Mediator to periodically pull the CSV from SFTP, store it in the ESB Registry, and have the API fetch from the registry (faster response times)
• Response Caching: Enable API-level caching to avoid re-fetching and re-parsing the CSV on every request (set an appropriate TTL based on how often the CSV updates)

内容的提问来源于stack exchange，提问作者Gavinli.sh