Hologres:IP address resolution functions

Prerequisites

A Hologres instance is purchased. A development tool is connected to the instance. For more information, see Connect to HoloWeb and perform queries.

Overview

Limits

Take note of the following rules when you use the IP address resolution functions in Hologres:

• The IP address resolution functions in Hologres support only IPv4 addresses.

• The IP address resolution functions work based on extension functions. Before you use them, perform the following operations:

  1. Submit a ticket to contact Hologres technical support engineers to enable function configuration.

  2. Contact the superuser of your Hologres instance to execute the following statement in the instance:

     create extension ipdb;

     Note

     This statement takes effect at the database level and needs to be executed for each database only once. If you switch to another database, the superuser must execute the statement again.

ip2region

• Description

  This function is used to query the information about an IP address, including the country, province, city, and ISP. The returned data can be displayed in different languages.

• Syntax

  ip2region(ip, level, lang)

• Parameters

  Parameter	Description
  ip	The IP address whose information is to be queried. You can specify only IPv4 addresses. The value is of the TEXT type.
  level	The category of the information to be queried. The value is of the TEXT type. Valid values: Country: the country to which the IP address belongs. Province: the province to which the IP address belongs. City: the city to which the IP address belongs. isp: the ISP that manages the IP address.
  lang	The language of the data to be returned. The value is of the TEXT type. Valid values: CN and EN.

• The returned data is of the TEXT type.

• Examples

  select ip2region('31.13.XX.X', 'country', 'CN');
  select ip2region('31.13.XX.X', 'country', 'EN');
  select ip2region('31.13.XX.X', 'province', 'EN');
  select ip2region('31.13.XX.X', 'city', 'EN');
  select ip2region('31.13.XX.X', 'isp', 'EN');

ip_country, ip_province, ip_city, and ip_isp

• Description

  You can separately use the ip_country, ip_province, ip_city, or ip_isp function to query the country, province, city, or ISP that corresponds to an IP address. These four functions are simple versions of the ip2region function.

• Syntax

  ip_country/ip_province/ip_city/ip_isp (ip, [lang])

  The ip_country, ip_province, ip_city, and ip_isp functions are used to query the following information about an IP address: the country, province, and city to which the IP address belongs, and the ISP that manages the IP address.

• Parameters

  Parameter	Description
  ip	The IP address whose information is to be queried. You can specify only IPv4 addresses. The value is of the TEXT type.
  lang	The language of the data to be returned. The value is of the TEXT type. Valid values: CN and EN. If you leave this parameter unset, the returned data is displayed in English.

• The returned data is of the TEXT type.

• Examples

  select ip_country('31.13.XX.X'); // The default language of the data to be returned is English.
  select ip_country('31.13.XX.X','EN');
  select ip_province('31.13.XX.X');
  select ip_province('31.13.XX.X','EN');
  select ip_city('31.13.XX.X');
  select ip_city('31.13.XX.X','EN');
  select ip_isp('115.239.XXX.XX');
  select ip_isp('115.239.XXX.XX','EN');

ip2long

• Description

  This function is used to convert an IPv4 address to a number.

• Syntax

  ip2long(ip)

• Parameters

  Parameter	Description
  ip	The IP address to be converted. You can specify only IPv4 addresses. The value is of the TEXT type.

• The returned data is of the BIGINT type.

• Examples

  select ip2long('31.13.XX.X');

long2ip

• Description

  This function is used to convert a number to an IPv4 address.

• Syntax

  long2ip(longVal)

• Parameters

  Parameter	Description
  longVal	The number to be converted. The value is of the TEXT type.

• The returned data is of the TEXT type.

• Examples

  select long2ip(ip2long('31.13.XX.X'));