- 06 Nov 2024
- 4 Minutes to read
- Print
- DarkLight
- PDF
Run Inference with SQL Commands
- Updated on 06 Nov 2024
- 4 Minutes to read
- Print
- DarkLight
- PDF
This article applies to these versions of LandingLens:
LandingLens | LandingLens 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
- 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:
- Open Snowsight.
- Go to Data Products > Apps > LandingLens Visual AI Platform.
- Click Launch App.
- Click App_Wizard.
- Click Settings.
- 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:
- creates a table,
- adds the image file names to that table,
- runs inference, and
- 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.
Action | When Using LandingLens Sample Dataset | When Using Your Own Dataset |
---|---|---|
Grant the LLENS_PUBLIC role | Grant yourself the LLENS_PUBLIC application role. | Grant yourself the LLENS_PUBLIC application role. |
Get LandingLens instance name | Get 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 dataset | Install the Sample Dataset for LandingLens: LifeSciences Pneumonia dataset. | N/A |
Grant LandingLens access to the stage | Grant LandingLens access to the stage with the LandingLens dataset. | Grant LandingLens access to the stage with your dataset. |
Train a model | Train a model in LandingLens with the dataset. | Train a model in LandingLens with the dataset. |
Deploy model and get endpoint ID | Deploy 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;