All Products
Search
Document Center

IP address resolution functions

Last Updated: Mar 22, 2021

This topic describes IP address resolution functions in Data Lake Analytics (DLA).

ip2region(ip, level, lang)

  • Description: This function obtains information about the country, province, or city to which an IP address belongs, and supports language settings.

  • Return value type: VARCHAR.

  • Parameters:

    • ip: The IP address that you want to query.

    • level: The level at which information is queried. Valid values: country, province, city, and isp.

    • lang: The language in which results are returned. Valid values: CN and EN.

  • Example:

       select ip2region('31.13.79.1', 'country', 'CN')
       +---------------------------------------------+
       | 印度                                         |
       select ip2region('31.13.79.1', 'country', 'EN')
       +---------------------------------------------+
       | India                                        |

ip_country/ip_province/ip_city/ip_isp

  • Description: These functions are simple application of the ip2region function.

    • ip_country: obtains the country to which an IP address belongs.

    • ip_province: obtains the province to which an IP address belongs.

    • ip_city: obtains the city to which an IP address belongs.

    • ip_isp: obtains the Internet service provider that provides an IP address.

  • Example:

      select ip_country('31.13.79.1')
       +---------------------------+
       |印度                        |
       select ip_country('31.13.79.1','EN')
       +-----------------------------+
       |India                        |
       select ip_province('31.13.79.1')
       +-----------------------------+
       |马哈拉施特拉邦                 |
       select ip_province('31.13.79.1','EN')
       +------------------------------+
       |Maharashtr                    |
       select ip_city('31.13.79.1')
       +------------------------------+
       |孟买                          |
       select ip_city('31.13.79.1','EN')
       +------------------------------+
       |Mumbai                        |
       select ip_isp('115.239.210.27')
       +------------------------------+
       |电信                           |
       select ip_isp('115.239.210.27','EN')
       +-------------------------------+
       |ChinaTelecom                   |

ip2long

ip2long(ip)
  • Description: This function converts an IP address into a string of digits.

  • Return value type: BIGINT.

  • Example:

      select ip2long('115.239.210.27')
       +----------------------------+
       |1945096731                  |

long2ip

long2ip(longVal)
  • Description: This function converts a string of digits into an IP address.

  • Return value type: VARCHAR.

  • Example:

      select long2ip(1945096731)
       +--------------------+
       | 115.239.210.27     |

ip_city_geo

ip_city_geo(ip)
  • Description: This function queries the latitude and longitude of the city to which an IP address belongs.

  • Example:

      SELECT ip_city_geo('42.120.74.91');
      +----------------------------+
      |   30.252501,120.165024     |

ip_timezone

ip_timezone(ip)
  • Description: This function queries the time zone to which an IP address belongs.

  • Example:

      SELECT ip_timezone('42.120.74.91');
      +----------------------------+
      |       Asia/Shanghai        |

ip_utc_offset

ip_utc_offset(ip)
  • Description: This function queries the UTC offset of the time zone to which an IP address belongs.

  • Example:

      SELECT ip_utc_offset('42.120.74.91');
      +----------------------------+
      |           UTC+8            |

ip_china_admin_code

ip_china_admin_code(ip)
  • Description: This function queries the administrative code for an IP address in mainland China.

  • Example:

      SELECT ip_china_admin_code('42.120.74.91');
      +----------------------------------------+
      |                 330100                 |

ip_idd_code

ip_idd_code(ip)
  • Description: This function queries the code of the country to which an IP address belongs. Example: 86 for mainland China.

  • Example:

      SELECT ip_idd_code('42.120.74.91');
      +----------------------------+
      |             86             |

ip_country_code

ip_country_code(ip)
  • Description: This function queries the internationalized domain name of the country to which an IP address belongs. Example: CN for mainland China.

  • Example:

      SELECT ip_country_code('42.120.74.91');
      +----------------------------+
      |             CN             |

ip_continent_code

ip_continent_code(ip)
  • Description: This function queries the continent code for an IP address. Example: AP for the Asia Pacific region.

  • Example:

      SELECT ip_continent_code('42.120.74.91');
      +--------------------------------------+
      |                   AP                 |

ip_country_code_3

ip_country_code_3(ip)
  • Description: This function queries the 3-letter code of the country to which an IP address belongs. Example: CHN for mainland China.

  • Example:

      SELECT ip_country_code_3('42.120.74.91');
      +--------------------------------------+
      |                   CHN                |

ip_currency_code

ip_currency_code(ip)
  • Description: This function queries the currency code of the country to which an IP address belongs. Example: CNY for mainland China.

  • Example:

      SELECT ip_currency_code('42.120.74.91');
      +--------------------------------------+
      |                   CNY                |

ip_currency_name

ip_currency_name(ip)
  • Description: This function queries the currency name of the country to which an IP address belongs. Example: Yuan Renminbi for mainland China.

  • Example:

      SELECT ip_currency_name('42.120.74.91');
      +--------------------------------------+
      |             Yuan Renminbi            |