1.1 - 8.10 - IPGeo Example: IP Database Stored as ML Engine File - Teradata Vantage

Teradata Vantage™ - Machine Learning Engine Analytic Function Reference

Product
Teradata Vantage
Release Number
1.1
8.10
Release Date
October 2019
Content Type
Programming Reference
Publication ID
B700-4003-079K
Language
English (United States)

This example requires that the files MaxMindLite.jar, GeoLiteCity.dat, and GeoLiteCityv6.dat are installed on ML Engine.

Input

ipgeo_1
id ip
1 159.41.1.23
2 153.65.16.10
3 75.36.209.106
4 202.106.0.20
5 69.236.77.51
6 168.187.7.114

SQL Call

SELECT * FROM IPGeo (
  ON ipgeo_1
  USING
  IpAddressColumn ('ip')
  Converter ('MaxMindLite.jar',
            'com.asterdata.sqlmr.analytics.location.ipgeo.MaxMindLite')
  Accumulate ('id', 'ip')
) AS dt ;

Output

 id ip            country_code country_name  state      city          postal_code latitude longitude isp  organization organization_type area_code metro_code dma_code 
 -- ------------- ------------ ------------- ---------- ------------- ----------- -------- --------- ---- ------------ ----------------- --------- ---------- -------- 
  4 202.106.0.20  CN           China         Beijing    Beijing       NULL         39.9289  116.3883 NULL NULL         NULL                      0          0        0
  6 168.187.7.114 KW           Kuwait        Al Kuwayt  Kuwait        NULL         29.3697   47.9783 NULL NULL         NULL                      0          0        0
  5 69.236.77.51  US           United States California San Francisco NULL         37.7749 -122.4194 NULL NULL         NULL                    415        807      807
  3 75.36.209.106 US           United States California San Francisco NULL         37.7749 -122.4194 NULL NULL         NULL                    415        807      807
  1  159.41.1.23  US           United States Michigan   Saint Joseph  49085        42.0569  -86.4563 NULL NULL         NULL                    269        588      588
  2 153.65.16.10  US           United States Ohio       Miamisburg    45342        39.6182  -84.2488 NULL NULL         NULL                    937        542      542

Download a zip file of all examples and a SQL script file that creates their input tables from the attachment in the left sidebar.