Hologres:IP address resolution functions

Prerequisites

Before you use the IP address resolution functions, you must activate a Hologres instance and connect to a development tool. For more information, see Connect to HoloWeb and execute a query.

Function overview

Limits

The following limits apply when you use IP address resolution functions in Hologres:

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

• The IP address resolution functions are implemented as an extension. To use these functions, perform the following steps:

  1. Submit a ticket to have Hologres technical support enable the function configuration for you.

  2. The administrator (Superuser) of the instance must execute the following extension command.

     create extension ipdb;

     Note

     This is a DB-level command. You only need to execute this command once for each DB. If you switch to a new DB, the administrator (Superuser) must execute the command again.

ip2region

• Description

  This function retrieves the country, province, city, and Internet Service Provider (ISP) information for an IP address. It also supports multiple languages.

• Syntax

  ip2region(ip, level, lang)

• Parameters

  Parameter	Description
  ip	The IP address to query. This parameter is of the text type. Only IPv4 is supported.
  level	The text type includes the following four options: Country Province City ISP
  lang	The language of the returned result. This parameter is of the text type. Valid values: CN and EN.

• Return value type: text.

• Examples

  select ip2region('31.13.79.1', 'country', 'CN');
  select ip2region('31.13.79.1', 'country', 'EN');
  select ip2region('31.13.79.1', 'province', 'EN');
  select ip2region('31.13.79.1', 'city', 'EN');
  select ip2region('31.13.79.1', 'isp', 'EN');

ip_country/ip_province/ip_city/ip_isp

• Description

  The ip_country, ip_province, ip_city, and ip_isp functions are simplified versions of the ip2region function. They retrieve the country, province, city, and ISP information for an IP address.

• Syntax

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

  ip_country retrieves the country, ip_province retrieves the province, ip_city retrieves the city, and ip_isp retrieves the ISP.

• Parameters

  Parameter	Description
  ip	The IP address to query. This parameter is of the text type. Only IPv4 is supported. If you do not specify a language, English is used by default.
  lang	The language of the returned result. This parameter is of the text type. Valid values: CN and EN.

• Return value type: text.

• Examples

  select ip_country('31.13.79.1'); // Returns the result in English by default.
  select ip_country('31.13.79.1','EN');
  select ip_province('31.13.79.1');
  select ip_province('31.13.79.1','EN');
  select ip_city('31.13.79.1');
  select ip_city('31.13.79.1','EN');
  select ip_isp('115.239.210.27');
  select ip_isp('115.239.210.27','EN');

ip2long

• Description

  This function converts an IPv4 address to an integer.

• Syntax

  ip2long(ip)

• Parameters

  Parameter	Description
  ip	The IP address to query. This parameter is of the text type. Only IPv4 is supported.

• Return value type: bigint.

• Examples

  select ip2long('31.13.79.1');

long2ip

• Description

  This function converts an integer to an IPv4 address format.

• Syntax

  long2ip(longVal)

• Parameters

  Parameter	Description
  longVal	The number to convert. This parameter is of the text type.

• Return value type: text.

• Examples

  select long2ip(ip2long('31.13.79.1'));