Using Snowflake Cortex — COMPLETE function to generate responses to prompt
Thanks for reading my earlier blogs! This is the next blog in the series about understanding and using Snowflake Cortex functions. The first blog helps you to understand the Snowflake Cortex and its functions offered as part of the Cortex.
Read the first blog here — https://medium.com/snowflake/understanding-snowflake-cortex-functions-93d6eb695ed7 if you missed reading the blog.
This is the second blog that helps you learn more about the COMPLETE function and use it to generate responses to input prompts.
What is the function- COMPLETE?
This is the LLM function available in the preview for selected regions. This function returns responses using selected large language models (LLM).
What are the models available to be used with COMPLETE?
There is a set of models available to be used with this function. You can choose one from below:
'mistral-large''mixtral-8x7b''llama2-70b-chat''mistral-7b''gemma-7b'
You can use one of the models from the above list. These models are subject to different costs and quotas.
What are the available quotas for given LLM Models?
As shared in the first blog as well, these models are subject to limitations and quotas depending on the model, input tokens, and rows processed per minute.
How can I grant access to the users to use this function?
The access is to be granted to a database role, and a role can be granted to the user to use this function. Users who want to use LLM functions must use a role that has been granted to them. The custom role created must have granted Snowflake.cortex_user to the database role.
How can I use this function?
You can use this function similar to any other UDF or system-defined functions in Snowflake. You need to pass the input as prompt, and model as the choice of model to be used. You can also pass a bunch of optional parameters to the function. You can use the below syntax :
SELECT SNOWFLAKE.CORTEX.COMPLETE(‘model_name’, ‘input prompt’);
Additionally, you can use options to set the temperature, max tokens, and top_p as alternatives to the temperature to pass to the model.
Can I use the COMPLETE function for conversational responses?
Snowflake Cortex — COMPLETE function does not maintain the state of the prompts or prompt history on its own. You need to pass the prompt_history in case you need to have conversational experience using COMPLETE. You can pass all the historical or previous prompts as <prompt_of_history> array.
What are the sample use cases of COMPLETE?
There are various scenarios where you can use this function as independent prompts or conversational or run on top of any table attributes to generate responses.
- You can use this function to run the sample input prompts and provide responses to the prompts as part of the conversation for individual users.
- You can also use this to read table data in the form of one or more table attributes and generate responses.
You can also use this to generate the responses to summarize, sentiment generation, etc. however Cortex's set of functions includes these special functions to offer summarization, text generation, and sentiment analysis features to be implemented for specific use cases.
Sample Use Cases:
- SELECT to generate prompt using LLM model
SELECT SNOWFLAKE.CORTEX.COMPLETE('mistral-large', 'What is Snowflake cortex?');Response:
Snowflake Cortex is a feature of the Snowflake cloud data platform that enables customers to process data stored in external cloud storage services, such as Amazon S3, Azure Blob Storage, and Google Cloud Storage, without having to first load the data into Snowflake. Cortex uses serverless computing resources to perform data processing tasks, such as querying and transformation, on the data in its external location. This allows customers to take advantage of the scalability and cost-effectiveness of cloud storage services while still being able to use Snowflake’s powerful data processing capabilities.
2. Run SELECT statement to generate response using different model:
SELECT SNOWFLAKE.CORTEX.COMPLETE('llama2–70b-chat', 'How can I use Snowflake cortex?');
Reponse:
Snowflake Cortex is a feature of Snowflake’s cloud-based data warehousing platform that allows you to run containerized workloads, such as data processing, data transformation, and data science tasks, inside Snowflake. This allows you to perform complex data processing tasks without having to leave the Snowflake environment, and without having to worry about managing the underlying infrastructure. Here are the general steps to use Snowflake Cortex: 1. Create a Snowflake account: If you don’t already have a Snowflake account, sign up for one at snowflake.com. 2. Create a Snowflake Cortex cluster: Once you have a Snowflake account, you can create a Snowflake Cortex cluster by going to the Snowflake web interface, clicking on the “Create” button, and selecting “Cortex Cluster” from the drop-down menu. 3. Install Docker images: Snowflake Cortex uses Docker containers to run workloads. You can install Docker images from Docker Hub or other container registries to use with Snowflake Cortex. 4. Create a Cortex job: Once you have a Cortex cluster and Docker images installed, you can create a Cortex job by going to the Snowflake web interface, clicking on the “Create” button, and selecting “Cortex Job” from the drop-down menu. 5. Define the job: When creating a Cortex job, you’ll need to specify the Docker image to use, the command to run inside the container, and any dependencies or libraries that the job needs. 6. Run the job: Once you’ve defined the job, you can run it by clicking the “Run” button in the Snowflake web interface. Snowflake Cortex will automatically provision and run the container, and you’ll be able to see the output of the job in the Snowflake web interface. 7. Monitor the job: While the job is running, you can monitor its progress in the Snowflake web interface. You can view logs, monitor resource usage, and see the status of the job. 8. Retrieve results: Once the job is complete, you can retrieve the results by clicking on the “Results” tab in the Snowflake web interface. Some common use cases for Snowflake Cortex include: * Data processing: You can use Snowflake Cortex to run data processing workloads, such as data transformation, data cleaning, and data aggregation. * Data science: Snowflake Cortex provides a flexible environment for data scientists to run machine learning models, perform statistical analysis, and visualize data. * Data integration: Snowflake Cortex can be used to integrate data from different sources, such as APIs, databases, and files, and load it into Snowflake for analysis. Overall, Snowflake Cortex provides a powerful and flexible way to run containerized workloads inside Snowflake, allowing you to perform complex data processing and analysis tasks without leaving the Snowflake environment.
3. Running on top of table columns :
Consider a use case where you have movie review table with movie_name, movie_review and movie_rating. You want to get summary of the review and sentiment of a viewer quickly.
SELECT SNOWFLAKE.CORTEX.COMPLETE(
'mistral-large',
CONCAT('Critique this review in 50 words as summary and what is sentiment of viewer:', MOVIE_REVIEW)
) FROM MOVIE_REVIEWS;You can use below table and sample records to run this use case:
/* Create table*/
CREATE TABLE MOVIE_REVIEWS(MOVIE_NAME STRING, MOVIE_REVIEW STRING, MOVIE_RATING float);
/* load sample records */
INSERT INTO MOVIE_REVIEWS VALUES('Arthur The King','Michael Light (Mark Wahlberg) has one last chance to win a championship and prove himself. Finding sponsorship and creating a team of four is challenging, given his washed-out career and a previous disastrous race. However, he assembles a team of an athlete and a social media star, Leo (Simu Liu), free climber Olivia (Nathalie Emmanuel), and Chik (Ali Sulaiman), who has a bad knee. During the race, he has a fifth and an unlikely teammate — an injured indie dog, Arthur.
Director Simon Cellan Jones’s offering is an account of companionship between man and dog. The story talks about loyalty, friendship, sacrifice, and survival. It also offers ample adventure through a jungle trek, rock climbing with bicycles, ziplining across a valley (a sequence that will have you on the edge of your seat), night runs, and more.
Cinematographer Jacques Jouffret splendidly captures the Dominican terrains, forests, mountains, rivers, and valleys. The adventurous race also looks convincing and authentic. Writers Michael Brandt and Mikael Lindnord’s narrative has all the ingredients you expect from this genre—friction amid friendship, obstacles, and the tough decisions the athletes must make. Although the story gets a tad too sappy, the narrative’s even pacing prevents it from weighing you down.
Mark Wahlberg delivers a powerful performance as the out-of-luck captain who wants to make a comeback no matter what. Nathalie Emmanuel, Simu Liu, and Ali Suliman are also worthy additions. Their banter and individual reasons for participating in the race add authenticity to the narrative. The movie’s real star, however, is the dog. It’s inspiring and incredulous to think this is a true story (of Michael Lindnord), and the narrative will tug at your heartstrings.
The movie does not end with the race or whether the Broadrail team wins and goes beyond being an adventure sports drama. Whether you are an animal lover or an adventure sports enthusiast, Arthur the King will thrill you and warm your heart. Be warned: you may tear up at this endearing story of a dog and underdogs!',3.5);
INSERT INTO MOVIE_REVIEWS VALUES('Kung Fu Panda 4','After eight years since Kung Fu Panda 3, Po the Panda is back, facing the dilemma of relinquishing his role as the Dragon Warrior to become the Spiritual Leader of the Valley of Peace. Despite resistance from his mentor, Master Shifu (Dustin Hoffman), Po hesitates to let go of his dream role. A new threat emerges in the form of the Chameleon, a master sorceress and crime lord who seeks to unlock the power of previous Kung Fu masters to take over the world. Po teams up with a skilled thief, Zhen, to combat Chameleon and protect the Staff of Wisdom. Throughout the film, Po grapples with his inner conflicts while facing formidable adversaries, navigating themes of duty, sacrifice, and embracing ones true potential. Kung Fu Panda 4 follows a predictable plot, but co-directors Mike Mitchell and Stephanie Ma Stine deliver fun and fast-paced action, which continues to be one of the franchises strong suits. The fight scenes, especially the vibrant chase sequence throughout the setting of Juniper City are performed with style and keep you engaged. The top-notch animation has vivid colours that enhance the visual experience without overwhelming the senses. The editing, too, has been deftly done, thus ensuring that the movie never drags. The only drawback is that the script, co-written by Jonathan Aibel, Glenn Berger, and Darren Lemke, leans heavily on its moral messages, occasionally feeling like its guiding the audience too overtly through its themes of duty and sacrifice. Jack Black reprises his role as Po and delivers a solid vocal performance, mentoring a protégé throughout the film. Dustin Hoffman shines again as Master Shifu, while other characters like Pos adopted father, Ping (Hong), and his biological panda father, Li Shan (Cranston), join the adventure. Viola Daviss portrayal of the menacing Chameleon adds an intimidating presence to the film, while the animation team brings the character to life with impressive visuals. Although Awkwafinas voice doesnt fit the character, Zhens characterization is fresh and unique. Ian McShanes surprising return as Tai Lung provides closure for the character, with his performance adding depth to the story.
Despite not reaching the heights of its predecessors, Kung Fu Panda 4 still offers an entertaining 94-minute ride, blending humour, action, and heart in a way that will please fans of the franchise.',3);You can use it for any of your use cases where you may want to get details of support issues, tickets, customer sentiments etc. You can use other cortex functions that are specifically designed to be used for sentiments and summarizations.
You can read more about this function on snowflake documentation — https://docs.snowflake.com/en/sql-reference/functions/complete-snowflake-cortex to learn to implement it for your use cases.
Subscribe to my blog series to learn more about next set of Cortex functions. The next blog will share SUMMARIZE function with sample use case and SQL to implement it real quick.
About Me :
I am one of the Snowflake Data Superheroes 2023 & 2024. I am a DWBI and Cloud Architect! I am currently working as Senior Data Architect — GCP, Snowflake. I have been working with various Legacy data warehouses, data implementations, and Cloud platforms/Migrations. I am a SnowPro Core certified Data Architect as well as a Google-certified Google Professional Cloud Architect. You can reach out to me on LinkedIn if you need any further help on certification, Data Solutions, and Implementations!
