Reference Guide

Attention

🚧🛠️ Pardon Our Dust! 🛠️🚧

We’re in the process of enhancing our website to provide you with a better experience. While we work diligently, you might notice a few things out of place. Please excuse the temporary mess as we strive to improve our site.

Please contact us at MelissaDocs@Melissa.com if you have any questions.

2-Step Configuration

Follow the instruction to get the app and details about installation in Native App Installation.

Events and Logs

Note

An ACCOUNTADMIN or equivalent role is required to enable Events and Logs.

To observe and troubleshoot app behavior, you can enable Logging and Event Tracing for your account and share the app logs with us. For more information, please check Snowflake Documentations below:

• Set up event tracing for an app as a consumer

• Enable event sharing for an app

Stored Procedures

CHECK_SINGLE_IP

The CHECK_SINGLE_IP procedure validates and standardizes individual IP components using the Global IP API. It returns the verification results in JSON format or optional output table, supporting optional fields for flexible and accurate IP validation.

To get started, please contact a sales representative at Snowflakemarketplace@Melissa.com for your License Key.

There are 2 ways to check an IP in Native App Global IP for Snowflake:

1. Fill in the Checking Form on the Native App interface.

2. Manually run a Snowflake SQL script to call the stored procedure.

Checking Form

In your Snowflake account, select Data Products > Apps > GLOBAL_IP > Menu > Check Single IP > Try It Now > Checking Form.

Input Example

1. Enter the License Key and IP address you want to check.

   

2. Select Check.

3. Result will be displayed on the left sidebar.

Output Examples

Some common output examples are shown below:

Check a single IP address

Result will be displayed in JSON format.

Check a single IP and insert the result to an output table

You can choose to insert the result to an output table for later use with our Default Output Fields

See also

To query the result output, please check SQL Script Query Output Table

Check a single IP with selected output table fields

You can choose which fields to be included in the output table.

Note

To query the result output, please check SQL Script Query Output Table

Check a single IP with ‘ALL’ output table fields

You can choose to have All fields to be included in the output table.

Note

To query the result output, please check SQL Script Query Output Table

SQL Script

Manually call our procedures using a Snowflake SQL script.

Setup and Sample Script

1. Select Projects > Worksheets on the left panel in your Snowflake account.

   You can find the installed application database in Projects > Worksheets > Databases.

   

2. Select + to create a new SQL worksheet.

   

3. Copy and paste the usage example below onto your worksheet to call the stored procedure, replace with your values.

   Check Input Parameters for more information.

   /******************************************************************
   Global IP - Check Single IP Address Usage Example
   ******************************************************************/
   USE GLOBAL_IP.CORE;
   
   /* Check a single IP address, replace with your values*/
   CALL CHECK_SINGLE_IP(
       LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
      ,IPADDRESS              => '216.231.3.166'
      ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
      ,OUTPUT_TABLE_FIELDS    => ''
   );
   
   /* Result view, if Output Table is provided */
   SELECT *
   FROM <OUTPUT_TABLE_NAME>
   

   Example of a stored procedure call from a SQL worksheet. Highlight the SQL statement then select Run button on the right top corner.

   

Input Parameters

Parameter	Data Type	Description	Example
LICENSE	VARCHAR	Required. The License Key Using Credits issued by Melissa. Get it here.	REPLACE_WITH_YOUR_LICENSE_KEY
IPADDRESS	VARCHAR	Required. IPv4 or IPv6 address.	216.231.3.166
OUTPUT_TABLE_NAME	VARCHAR	Required. Empty string is accepted. The output table name. A new table will be created in the same application database if it doesn’t exist. If the table exists, its structure must remain unchanged.	OUTPUT_TABLE_NAME
OUTPUT_TABLE_FIELDS	VARCHAR	Required. Empty string is accepted. Only valid when OUTPUT_TABLE_NAME provided. Fields in the output table; fields separated by comma (,) if not ‘All’. Field name must match one or more from the example list. Empty string will return Default Output Fields.	RecordID, INIPAddress, Result, City, ConnectionSpeed, ConnectionType, Continent, CountryAbbreviation, CountryName, DomainName, DST, IPAddress, ISPName, Latitude, Longitude, PostalCode, ProxyDescription, ProxyType, Region, TimeZoneCode, TimeZoneName, UTC, Response, Timestamp

Output Examples

Some common output examples are shown below:

• Check a single IP

• Check a single IP and insert the result into an output table

• Check a single IP with selected output fields

• Check a single IP with ‘ALL’ output table fields

Check a single IP

/* Check a single IP address, replace with your values*/
CALL CHECK_SINGLE_IP(
    LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
   ,IPADDRESS              => '216.231.3.166'
   ,OUTPUT_TABLE_NAME      => ''
   ,OUTPUT_TABLE_FIELDS    => ''
);

Output will be displayed in JSON format.

Check a single IP and insert the result into an output table

If OUTPUT_TABLE_NAME is provided, a new table will be created if not already existed in the same application database with Default Output Fields.

• Call stored procedure CHECK_SINGLE_IP.

   /* Check a single IP address, replace with your values*/
   CALL CHECK_SINGLE_IP(
       LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
      ,IPADDRESS              => '216.231.3.166'
      ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
      ,OUTPUT_TABLE_FIELDS    => ''
   );
  

  

• Query output table.

  SELECT *
  FROM <OUTPUT_TABLE_NAME>;
  

Check a single IP with selected output fields

Note

OUTPUT_TABLE_FIELDS is only valid when OUTPUT_TABLE_NAME is provided.

• Call stored procedure CHECK_SINGLE_IP.

   CALL CHECK_SINGLE_IP(
       LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
      ,IPADDRESS              => '216.231.3.166'
      ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
      ,OUTPUT_TABLE_FIELDS    => '<Field_1,Field_2>'
   );
  

  

• Query output table.

  SELECT *
  FROM <OUTPUT_TABLE_NAME>;
  

Check a single IP with ‘ALL’ output table fields

• Call stored procedure CHECK_SINGLE_IP.

   CALL CHECK_SINGLE_IP(
      LICENSE                => '<REPLACE_WITH_YOUR_LICENSE_KEY>'
      ,IPADDRESS              => '216.231.3.166'
      ,OUTPUT_TABLE_NAME      => '<OUTPUT_TABLE_NAME>'
      ,OUTPUT_TABLE_FIELDS    => 'All'
   );
  

  

• Query output table.

  SELECT *
  FROM <OUTPUT_TABLE_NAME>;
  

CHECK_MULTIPLE_IPS

Note

To get started, please contact a sales representative at Snowflakemarketplace@Melissa.com for your License Key.

The CHECK_MULTIPLE_IPS procedure processes IP records in batches, validating and standardizing key components using the Global IP API.

It can handle tables of any size, returning the results in a specified output table and supports optional fields for flexible, accurate IP validation.

Requirements

To use this feature, you need to prepare an input table in Snowflake beforehand referencing our default input fields below.

Attention

Input table must have all the Required columns. Otherwise, you will get a “NoneType” error.

Default Input Fields

Column Name	Data Type	Description
RECORDID	NUMBER	Required. Record identifier, primary key.
IPADDRESS	VARCHAR	Required. The IP address to be verified.

SQL Script

You can find the installed application database in Projects > Worksheets > Databases.

Setup and Sample Script

The step-by-step example below shows how to use our stored procedure CHECK_MULTIPLE_IPS with the default values. Replace with your values.

• Step 0 - Prepare an input table

  Assume that the input table has the signature below:

  CREATE TABLE IF NOT EXISTS <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> (
    RECID NUMBER(38,0) autoincrement start 1 increment 1 noorder
    ,IPADDRESS VARCHAR DEFAULT ('')
  ) CLUSTER BY (RECID);
  

  This input table has different column names from the Default Input Fields. Mapping column names in Step 2 is necessary for the program to get the correct parameters.

• Step 1 - Grant Required Privileges

  Ensure the application has the necessary access to the input table. Replace placeholders with your actual values.

  /**********************************************************************
  Global IP - Check Multiple Ips Usage Example
  **********************************************************************/
  /* Grant usage on input table to the application, replace with your values.*/
  GRANT USAGE ON DATABASE <INPUT_DATABASE> TO APPLICATION GLOBAL_IP;
  GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION GLOBAL_IP;
  GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION GLOBAL_IP;
  

Note

Default options are set with empty string

• Step 2 - Map Input Columns with the Default Input Fields

  Set the required parameters and map your input columns to our Default Input Fields.

  You can skip the mapping step if your input table matches our Default Input Fields exactly.

  USE GLOBAL_IP.CORE;
  
  /* Set input parameters, replace with your values */
  SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
  SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
  SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
  SET OUTPUT_TABLE_FIELDS = '<Field_1,Field_2>';
  
  /* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
  CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
  SELECT
     RECID                       AS RECORDID
    ,COALESCE(IPADDRESS, '')     AS IPADDRESS
  FROM IDENTIFIER($INPUT_TABLE_NAME);
  

• Step 3 - Call stored procedure CHECK_MULTIPLE_IPS to check your data

  Check Input Parameter for more information.

  /* Call the stored procedure */
  CALL CHECK_MULTIPLE_IPS(
     LICENSE               => $LICENSE
    ,INPUT_TABLE_NAME      => TABLE(INPUT_RECORDS)
    ,OUTPUT_TABLE_NAME     => $OUTPUT_TABLE_NAME
    ,OUTPUT_TABLE_FIELDS   => $OUTPUT_TABLE_FIELDS
    -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
    -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
  );
  
  /* Output table view */
  SELECT TOP 100 *
  FROM IDENTIFIER($OUTPUT_TABLE_NAME)
  ORDER BY (RECORDID);
  

Full SQL Script

/**********************************************************************
Global IP - Check Multiple IP Addresses Usage Example
**********************************************************************/
/* Grant usage on input table to the application, replace with your values.*/
GRANT USAGE ON DATABASE <INPUT_DATABASE> TO APPLICATION GLOBAL_IP;
GRANT USAGE ON SCHEMA <INPUT_DATABASE>.<INPUT_SCHEMA> TO APPLICATION GLOBAL_IP;
GRANT SELECT ON TABLE <INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE> TO APPLICATION GLOBAL_IP;

USE GLOBAL_IP.CORE;

/* Set input parameters, replace with your values */
SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
SET OUTPUT_TABLE_FIELDS = '<Field_1,Field_2>';

/* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
SELECT
   RECID                        AS RECORDID
   ,COALESCE(IPADDRESS, '')     AS IPADDRESS
FROM IDENTIFIER($INPUT_TABLE_NAME);

/* Call the stored procedure */
CALL CHECK_MULTIPLE_IPS(
   LICENSE               => $LICENSE
  ,INPUT_TABLE_NAME      => TABLE(INPUT_RECORDS)
  ,OUTPUT_TABLE_NAME     => $OUTPUT_TABLE_NAME
  ,OUTPUT_TABLE_FIELDS   => $OUTPUT_TABLE_FIELDS
  -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
  -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
);

/* Output table view */
SELECT TOP 100 *
FROM IDENTIFIER($OUTPUT_TABLE_NAME)
ORDER BY (RECORDID);

Input Parameter

Input parameters when calling stored procedure CHECK_MULTIPLE_IPS.

Parameter	Data Type	Description	Example
LICENSE	VARCHAR	Required. The License Key Using Credits issued by Melissa. Get it here.	REPLACE_WITH_YOUR_LICENSE_KEY
INPUT_TABLE_NAME	VARCHAR	Required. Name of the input table.	IN_DB.IN_SCHEMA.INPUT_TABLE_NAME
OUTPUT_TABLE_NAME	VARCHAR	Required. The output table name. A new table will be created in the same application database if it doesn’t exist. If the table exists, its structure must remain unchanged.	OUTPUT_TABLE_NAME
OUTPUT_TABLE_FIELDS	VARCHAR	Required. Empty string is accepted. Fields in the output table; fields separated by comma (,) if not ‘All’. Field name must match in one or all of the example list. If omitted, see Default Output Fields.	RecordID, INIPAddress, Result, City, ConnectionSpeed, ConnectionType, Continent, CountryAbbreviation, CountryName, DomainName, DST, IPAddress, ISPName, Latitude, Longitude, PostalCode, ProxyDescription, ProxyType, Region, TimeZoneCode, TimeZoneName, UTC, Response, Timestamp

Output Examples

Some common output examples are shown below:

• Check multiple IP addresses with the default output fields

• Check multiple IP addresses with selected output fields

• Check multiple IP addresses with ‘ALL’ output fields

Check multiple IP addresses with the default output fields

• Call the stored procedure to check multiple IPs with Default Output Fields.

   USE GLOBAL_IP.CORE;
  
   /* Set input parameters, replace with your values */
   SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
   SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
   SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
   SET OUTPUT_TABLE_FIELDS = '';
  
   /* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
   CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
   SELECT
       RECID                       AS RECORDID
      ,COALESCE(IPADDRESS, '')     AS IPADDRESS
   FROM IDENTIFIER($INPUT_TABLE_NAME);
  
   /* Call the stored procedure */
   CALL CHECK_MULTIPLE_IPS(
      LICENSE               => $LICENSE
     ,INPUT_TABLE_NAME      => TABLE(INPUT_RECORDS)
     ,OUTPUT_TABLE_NAME     => $OUTPUT_TABLE_NAME
     ,OUTPUT_TABLE_FIELDS   => $OUTPUT_TABLE_FIELDS
   );
  

  

• Query output table.

   /* Output table view */
   SELECT TOP 100 *
   FROM IDENTIFIER($OUTPUT_TABLE_NAME)
   ORDER BY (RECORDID);
  

  

Check multiple IP addresses with selected output fields

• Call stored procedure to check multiple IPs from an input table at once.

   USE GLOBAL_IP.CORE;
  
   /* Set input parameters, replace with your values */
   SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
   SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
   SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
   SET OUTPUT_TABLE_FIELDS = '<Field_1,Field_2>';
  
   /* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
   CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
   SELECT
       RECID                       AS RECORDID
      ,COALESCE(IPADDRESS, '')     AS IPADDRESS
   FROM IDENTIFIER($INPUT_TABLE_NAME);
  
   /* Call the stored procedure */
   CALL CHECK_MULTIPLE_IPS(
      LICENSE               => $LICENSE
     ,INPUT_TABLE_NAME      => TABLE(INPUT_RECORDS)
     ,OUTPUT_TABLE_NAME     => $OUTPUT_TABLE_NAME
     ,OUTPUT_TABLE_FIELDS   => $OUTPUT_TABLE_FIELDS
     -- Size limit for variables is 256. If your input string exceeds the limit, parse the string directly in the call
     -- e.g. OUTPUT_TABLE_FIELDS    => 'Field_1,Field_2'
   );
  
   /* Output table view */
   SELECT TOP 100 *
   FROM IDENTIFIER($OUTPUT_TABLE_NAME)
   ORDER BY (RECORDID);
  

  

• Query output table.

  SELECT TOP 100 *
  FROM IDENTIFIER($OUTPUT_TABLE_NAME)
  ORDER BY (RECORDID);
  

  

Check multiple IP addresses with ‘ALL’ output fields

• Call stored procedure to check multiple IP from an input table at once.

   USE GLOBAL_IP.CORE;
  
   /* Set input parameters, replace with your values */
   SET LICENSE             = '<REPLACE_WITH_YOUR_LICENSE_KEY>';
   SET INPUT_TABLE_NAME    = '<INPUT_DATABASE>.<INPUT_SCHEMA>.<INPUT_TABLE_NAME>';
   SET OUTPUT_TABLE_NAME   = '<OUTPUT_TABLE_NAME>';
   SET OUTPUT_TABLE_FIELDS = 'All';
  
   /* Map your input columns with our default input fields, replace with your actual column names if they differ from our default values */
   CREATE OR REPLACE TEMPORARY VIEW INPUT_RECORDS AS
   SELECT
      RECID                       AS RECORDID
      ,COALESCE(IPADDRESS, '')     AS IPADDRESS
   FROM IDENTIFIER($INPUT_TABLE_NAME);
  
   /* Call the stored procedure */
   CALL CHECK_MULTIPLE_IPS(
      $LICENSE
      ,TABLE(INPUT_RECORDS)
      ,$OUTPUT_TABLE_NAME
      ,$OUTPUT_TABLE_FIELDS
   );
  

• Query output table.

  SELECT TOP 100 *
  FROM IDENTIFIER($OUTPUT_TABLE_NAME)
  ORDER BY (RECORDID);
  

DROP_OUTPUT_TABLE

After the creation, output table is only allowed for:

• Select.

  SELECT TOP 100 *
  FROM <OUTPUT_TABLE_NAME>
  ORDER BY (RECORDID);
  

• Insert.

  By calling the same stored procedure without changing the input structure, new records will be inserted to the same table.

• Drop.

  Use stored procedure DROP_OUTPUT_TABLE if you wish to remove the table from the application database. Only tables created by the procedure call can be dropped.

  CALL GLOBAL_IP.CORE.DROP_OUTPUT_TABLE('<OUTPUT_TABLE_NAME>');
  

Default Output Fields

If OUTPUT_TABLE_FIELDS is not specified, the default output fields will be:

Column Name	Data Type
RECORDID	NUMBER
INIPADDRESS	VARCHAR
RESULT	VARCHAR
CITY	VARCHAR
POSTALCODE	VARCHAR
REGION	VARCHAR
CONTINENT	VARCHAR
COUNTRYNAME	VARCHAR
DOMAINNAME	VARCHAR
IPADDRESS	VARCHAR
ISPNAME	VARCHAR
LATITUDE	VARCHAR
LONGITUDE	VARCHAR
DST	VARCHAR
TIMEZONECODE	VARCHAR
UTC	VARCHAR
TIMESTAMP	TIMESTAMP_NTZ

Versions and Updates

Check for current version

• Select Data Products > Apps, the current version of the app will be on display.

  

• Or, run this query in Projects > Worksheets

  SHOW APPLICATIONS;
  

Updates

When Melissa releases a new version of the Native App, your installed application will get updated automatically.

However, you will need to reinstall the app’s functionality after the upgrade is complete, as described in Application Configuration.

Result Codes

For the full list of result codes returned by Native App Global IP: Snowflake, please visit here.