The GeoIPFunctions UDX lets you determine the country, region, city, latitude, and longitude associated with IP addresses worldwide, along with the postal code and metro code for US IP addresses. It does so using the MaxMind GeoIp City database. s-Server ships with a free version of this database. This topic describes how the plugin finds the MaxMind data file and how one should use the licensed version for production. You can use this free default database for testing, but for production use, you should use at least an up-to-date GeoLiteCity.dat*, or probably a licensed data file.
See https://www.maxmind.com/en/geoip2-city for more details on the MaxMind GeoIp City database.
This page includes information on the following subtopics:
In order to use the GeoIPFunctions UDX, you need the following:
Creating a function to call GeoIPFunctions The following code creates and sets a test schema and loads the GeoIP Plugin Jar.
--- create a test schema
CREATE OR REPLACE SCHEMA "test";
SET SCHEMA '"test"';
-- load the GeoIp Plugin Jar
CREATE OR REPLACE JAR "GeoIp" LIBRARY 'file:plugin/geoip/geoip.jar'
OPTIONS(0);
Once you have converted the IP address to a BIGINT, you can use functions to return individual columns from the GeoIP database. In the following functions, you can use the IP address directly, as in 18.9.22.169.
CREATE OR REPLACE FUNCTION "getCityFromAddress" (address VARCHAR(30))
RETURNS VARCHAR(32)
LANGUAGE JAVA
PARAMETER STYLE SYSTEM DEFINED JAVA
NO SQL
EXTERNAL NAME '"GeoIp":com.sqlstream.plugin.geoip.GeoIp.getCityFromAddress';
CREATE OR REPLACE FUNCTION "getCountryFromAddress" (address VARCHAR(30))
RETURNS VARCHAR(34)
LANGUAGE JAVA
PARAMETER STYLE SYSTEM DEFINED JAVA
NO SQL
EXTERNAL NAME '"GeoIp":com.sqlstream.plugin.geoip.GeoIp.getCountryFromAddress';
CREATE OR REPLACE FUNCTION "getPostalCodeFromAddress" (address VARCHAR(30))
RETURNS VARCHAR(12)
LANGUAGE JAVA
PARAMETER STYLE SYSTEM DEFINED JAVA
NO SQL
EXTERNAL NAME
'"GeoIp":com.sqlstream.plugin.geoip.GeoIp.getPostalCodeFromAddress';
values("test"."getCityFromAddress"('18.9.22.169'));
values("test"."getCountryFromAddress"('18.9.22.169'));
values("test"."getPostalCodeFromAddress"('18.9.22.169'));
Some of the functions require you to convert the IP address into a form usable by the plugin. The following routine takes the 4 numeric portions of an IP address in dotted notation and produces a single BIGINT value.
CREATE OR REPLACE FUNCTION "ip4num"(ip1 VARCHAR(3), ip2 VARCHAR(3), ip3 VARCHAR(3), ip4 VARCHAR(3))
RETURNS BIGINT
CONTAINS SQL
DETERMINISTIC
RETURN
(CAST(ip1 AS BIGINT) * 256 * 256 * 256)
+ (CAST(ip2 AS INTEGER) * 256 * 256)
+ (CAST(ip3 AS INTEGER) * 256)
+ CAST(ip4 AS INTEGER);
CREATE OR REPLACE FUNCTION "getCityFromIP" (ipNum BIGINT)
RETURNS VARCHAR(132)
LANGUAGE JAVA
PARAMETER STYLE SYSTEM DEFINED JAVA
NO SQL
EXTERNAL NAME '"GeoIp":com.sqlstream.plugin.geoip.GeoIp.getCityFromIP';
CREATE OR REPLACE FUNCTION "getCountryFromIP" (ipNum BIGINT)
RETURNS VARCHAR(132)
LANGUAGE JAVA
PARAMETER STYLE SYSTEM DEFINED JAVA
NO SQL
EXTERNAL NAME '"GeoIp":com.sqlstream.plugin.geoip.GeoIp.getCountryFromIP';
CREATE OR REPLACE FUNCTION "getPostalCodeFromIP" (ipNum BIGINT)
RETURNS VARCHAR(132)
LANGUAGE JAVA
PARAMETER STYLE SYSTEM DEFINED JAVA
NO SQL
EXTERNAL NAME '"GeoIp":com.sqlstream.plugin.geoip.GeoIp.getPostalCodeFromIP';
values("test"."ip4num"('184','105','74','90'));
values("test"."getCityFromIP"(3093908058));
values("test"."getCountryFromIP"(3093908058));
values("test"."getPostalCodeFromIP"(3093908058));
The following code defines a function for the GeoIp UDX to take a stream and a named column as input and returns all stream columns plus 6 additional columns which correspond to the CountryCode, Country Name, City, Region, Latitude, and Longitude for the IP address.
CREATE OR REPLACE FUNCTION "addIpLocation" (c CURSOR, ipColName VARCHAR(25))
RETURNS TABLE(c.*,
"countryCode" CHAR(2),
"countryName" VARCHAR(34),
"city" VARCHAR(32),
"region" CHAR(2),
"latitude" float,
"longitude" float)
LANGUAGE JAVA
PARAMETER STYLE SYSTEM DEFINED JAVA
NO SQL
EXTERNAL NAME '"GeoIp":com.sqlstream.plugin.geoip.GeoIp.addLocation';
Next, you define a simple stream used in the examples below
CREATE OR REPLACE STREAM "in"
("ipAsText" VARCHAR(32),
"ipAsNum" BIGINT
)
DESCRIPTION 'example input stream to GeoIP Plugin'
;
The following code invokes the GeoIp UDX and uses the* ipAsNum* column as input:
CREATE OR REPLACE VIEW "Example1"
DESCRIPTION 'example invocation of GeoIP Plugin' AS
SELECT STREAM *
FROM STREAM("test"."addIpLocation"(CURSOR(SELECT STREAM * FROM "test"."in"),'ipAsNum'))
;
The following code parses a dotted notation IP address into the component parts so it can be used in the second example invocation for the GeoIp UDX.
CREATE OR REPLACE VIEW "Parse"
DESCRIPTION 'example parsing of IP addresses' AS
SELECT STREAM "ipAsText",
"test"."ip4num"(VCLP.R.ip1, VCLP.R.ip2, VCLP.R.ip3, VCLP.R.ip4) AS "ipParsed"
FROM (
SELECT STREAM *,
VARIABLE_COLUMN_LOG_PARSE("ipAsText", 'ip1, ip2, ip3, ip4' , '.') AS R
FROM "in"
) AS VCLP
;
The following code invokes the GeoIp UDX and uses the ipAsText column which is parsed using Variable Column Log Parser and then passed to the ip4num function to produce the BIGINT required by the GeoIp UDX.
CREATE OR REPLACE VIEW "Example2"
DESCRIPTION 'example invocation of GeoIP Plugin' AS
  SELECT STREAM *
  FROM STREAM("test"."addIpLocation"(CURSOR(SELECT STREAM * FROM "Parse"),'ipParsed'))
;
Next, write a stream to invoke the code above
!set incremental true
!set maxwidth 512
SELECT STREAM * from "Example2";
Next, in another SQLline session, enter the following:
-- insert into "test"."in" values ('184.105.74.90',3093908058);
-- insert into "test"."in" values ('124.105.74.90',3093908058);
In the original terminal, the following result will return:
| ipAsText | ipParsed | countryCode | countryName | city | region | latitude | longitude |
+----------+----------+-------------+-------------+-------+-------+----------+-----------+
| 184.105.74.90 | 3093908058 | US | United States | Fremont | CA | 37.5155029296875 | -121.89619445800781 |
| 124.105.74.90 | 2087275098 | PH | Philippines | Cavite | B6 | 14.477798461914062 | 120.8931884765625 |