When tasked with enriching a dataset of IP addresses with geographic metadata—such as country, province, and city—the priority is rapid, reliable execution. This scenario involved processing several hundred IP records stored in a Excel file, converting them to CSV for simplicity, then calling an external geolocation API to append loccation details.

Data Preparation

The source file was an .xls spreadsheet containing mixed columns, with the target IP column labeled generically (e.g., ip). To steramline parsing, the file was exported as UTF-8–encoded CSV. A header row (id,ip) was added manually to ensure structured deserialization.

Domain Model

A Java class modeled the expected input and enriched output:

@Data
public class IpRecord {
    private String id;
    private String ip;
    private String region;   // replaces "province"
    private String locality; // replaces "city"
    private String nation;   // replaces "country"
}

The API response was mapped using two supporting DTOs:

@Data
public class GeoApiResponse {
    private boolean ok;
    private String queriedIp;
    private GeoLocation data;
}

@Data
public class GeoLocation {
    private String country;
    private String province;
    private String city;
    private String provider;
}

API Integration

An OpenFeign client abstracted the HTTP interaction:

@FeignClient(name = "geoApiClient", url = "https://api.vvhan.com/api")
public interface GeoLocationClient {
    @GetMapping("/ipInfo")
    GeoApiResponse fetchLocation(@RequestParam("ip") String ip);
}

Processing Pipeline

A Spring Boot test orchestrated the full flow: read CSV → invoke API per record → merge results → write enriched CSV:

@SpringBootTest
@Slf4j
class IpGeolocationProcessor {

    @Autowired
    private GeoLocationClient geoClient;

    @Test
    void enrichAndExport() {
        List<IpRecord> records = CsvUtil.read(
            ResourceUtil.getUtf8Reader("input.csv"),
            IpRecord.class
        );

        records.parallelStream().forEach(record -> {
            try {
                GeoApiResponse resp = geoClient.fetchLocation(record.getIp());
                if (Boolean.TRUE.equals(resp.isOk()) && resp.getData() != null) {
                    GeoLocation loc = resp.getData();
                    record.setNation(loc.getCountry())
                          .setRegion(loc.getProvince())
                          .setLocality(loc.getCity());
                }
            } catch (Exception e) {
                log.warn("Failed to resolve IP {}: {}", record.getIp(), e.getMessage());
            }
        });

        CsvUtil.getWriter("/tmp/enriched-output.csv", CharsetUtil.CHARSET_UTF_8)
               .writeBeans(records);
    }
}

Note the use of parallelStream() to improve throughput—though rate limiting or retries would be advisable in production. The final CSV retained original columns and appended nation, region, and locality as new fields.

Observations

• Total runtime was ~43 seconds for 270 IPs — dominated by network latency and sequential API calls.
• No batch endpoint was available, so individual requests were unavoidable.
• Missing or ambiguous responses (e.g., private IPs like 127.0.0.1) resulted in empty fields — acceptable for this scope.