Run Inference with SQL Commands
  • 06 Nov 2024
  • 4 Minutes to read
  • Dark
    Light
  • PDF

Run Inference with SQL Commands

  • Dark
    Light
  • PDF

Article summary

This article applies to these versions of LandingLens:

LandingLensLandingLens on Snowflake

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

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.

Endpoint ID

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.

Inference Results in Snowsight

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

Inference Results in LandingLens

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.
    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.
    Inference Function Information

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. creates a table,
  2. adds the image file names to that table,
  3. runs inference, and
  4. saves 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.

ActionWhen Using LandingLens Sample DatasetWhen Using Your Own Dataset
Grant the LLENS_PUBLIC roleGrant yourself the LLENS_PUBLIC application role.Grant yourself the LLENS_PUBLIC application role.
Get LandingLens instance nameGet the name of your instance of LandingLens. You will add this to the script.Get the name of your instance of LandingLens. You will add this to the script.
Install datasetInstall the Sample Dataset for LandingLens: LifeSciences Pneumonia dataset.

N/A
Grant LandingLens access to the stageGrant LandingLens access to the stage with the LandingLens dataset.Grant LandingLens access to the stage with your dataset.
Train a modelTrain a model in LandingLens with the dataset.Train a model in LandingLens with the dataset.
Deploy model and get endpoint IDDeploy the model with Cloud Deployment. Get the endpoint ID for the deployed model. You will add this to the script.Deploy the model with Cloud Deployment. Get the endpoint ID for the deployed model. You will add this to the script.

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;

Was this article helpful?