Run Inference with SQL Commands

This article applies to these versions of LandingLens:

After deploying a model with Cloud Deployment, you can run inference using this function, where APP_NAME is the name of your LandingLens instance:

APP_NAME.core.run_inference(file_path varchar, endpoint_id varchar)

Here is how you select the function:

SELECT APP_NAME.core.run_inference(file_path varchar, endpoint_id varchar) as inference;

​

Prerequisites

• You must be granted the LLENS_PUBLIC application role to run this function.
• The inference function requires the name of your LandingLens instance (APP_NAME). To locate the name, go to Name of LandingLens Instance.
• If running inference on images in Snowflake stages, you must first grant LandingLens access to the stage. To do this, go to Grant LandingLens Access to Stages.

​

Arguments

file_path

A VARCHAR string that defines the location of an image or set of images to run inference on. This can be:

• images in Snowflake, including images in tables
• publicly-available HTTP/HTTPS URLs, including publicly-available Amazon S3 URLs

endpoint_id

A VARCHAR string that defines the endpoint of a model deployed with Cloud Deployment. The endpoint_id displays on the Deploy page in LandingLens.

​

Run Inference on Multiple Images

To use the run_inference function to run inference on multiple images, the image filepaths must be in a table or table-like format. For example, you can use the LIST command to list all files in a stage in a table-like format. You can then incorporate the LIST command results in the run_inference function.

For more information, see this example.

​

Inference Function Results

Running inference with the function returns prediction results in a JSON format. This is the same format that is returned when running Cloud Deployment using any other method.

The image and the prediction also display on the Deploy page in LandingLens (refresh the page in LandingLens to see the results).

​

Locate the Function

LandingLens automatically generates the inference function that is populated with the name of your instance of LandingLens. You can get the function for your instance of LandingLens in Snowsight:

1. Open Snowsight.
2. Go to Data Products > Apps > LandingLens Visual AI Platform.
3. Click Launch App.
4. Click APP_WIZARD.
5. Click Settings.
6. Expand the Run inferences from SQL statement section. The custom inference function for your instance of LandingLens displays.

​

Example: Run Inference on an Image Online

The function can run inference on publicly-available HTTP/HTTPS URLs. For example:

SELECT LLENS_SNW_STAGING.core.run_inference('https://upload.wikimedia.org/wikipedia/commons/a/a1/Normal_posteroanterior_%28PA%29_chest_radiograph_%28X-ray%29.jpg', '2c77a3b4-e242-4509-985b-a6fea48a7c47') as inference;

​

Example: Run Inference on an Image in Snowflake

Let’s say you’ve installed the Sample Dataset for LandingLens: LifeSciences Pneumonia dataset from LandingAI. You can then run inference on images from that dataset. For example:

SELECT LLENS_SNW_STAGING.core.run_inference('@llens_sample_ds_lifesciences.pneumonia.dataset/data/normal/IM-0001-0001.jpeg', 'e7fa760c-3472-42e0-b11c-ca219355cb19') as inference;

​

Example: Run Inference on Images in a Snowflake Table

You can run inference on images in a Snowflake table. For example:

SELECT
    LLENS_SNW_STAGING.core.run_inference(file_url_column, 'ac104c43-c6eb-4d1a-8a94-cfaf3dae8f70') as inference
FROM table_with_image_files
WHERE
    some_condition = true;

​

Example: Add Images from a Stage to a Table and Run Inference

Let’s say you want to run inference on multiple images in a Snowflake stage and save the results of the model’s predictions. You can do this by running a series of SQL commands that:

1. create a table,
2. add the image file names to that table,
3. run inference, and
4. save the prediction results to that table.

To make this easier to understand, we’ve created a SQL script below that shows how to do this based on the Sample Dataset for LandingLens: LifeSciences Pneumonia dataset from LandingAI. The script explains how to run inference on the first 20 images in that dataset. Modify the script as needed for your use case.

​

Prerequisites

Here are a set of prerequisites to consider when you’re running the script on the sample dataset and when you’re running it on your own dataset.

​

Sample SQL Script

You can copy and paste the following SQL script into a Worksheet in Snowsight. Update the placeholders that are called out in the commented parts of the code.

-- Create a table. You will later use this to store the image filenames and inference results. 
-- This creates a table named file_inferences with two columns: filename and inference.
 CREATE TABLE file_inferences (
   filename varchar,
   inference variant
 );

-- Run the LIST command to return a list of all JPEG files in the sample dataset that are on the Snowflake stage.
LIST @LLENS_SAMPLE_DS_LIFESCIENCES.PNEUMONIA.DATASET PATTERN = '.*jpeg';

-- Run the following nested commands. 
-- These run inference on the images returned by the LIST command, add the image filenames to the filename column, and add the inference results to the inference column.
-- Replace "LANDINGLENS_SNOWFLAKE" with the name of your instance of LandingLens.
-- Replace "dda5e0f8-30ca-4ed5-abac-fb068af39379" with the endpoint ID of the model you deployed.
-- In the following code, "name" is the column from the LIST results that includes the image filenames.
-- The run_inference function requires a complete filepath, so you must concatenate the database and schema with the filename ('@LLENS_SAMPLE_DS_LIFESCIENCES.PNEUMONIA.' || "name"). 
-- Be sure to include the period at the end of the schema (PNEUMONIA.)
INSERT INTO file_inferences(filename, inference)
SELECT
   "name", LANDINGLENS_SNOWFLAKE.core.run_inference('@LLENS_SAMPLE_DS_LIFESCIENCES.PNEUMONIA.' || "name", 'dda5e0f8-30ca-4ed5-abac-fb068af39379') as inference
   FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))
   LIMIT 20;