Skip to content

Instantly share code, notes, and snippets.

@tae0y

tae0y/relational_data_query_demo_on_colab_ver1-00.ipynb

Created May 31, 2024 11:04
Show Gist options
  • Save tae0y/bb07c7ea391a4be0714752a43219d602 to your computer and use it in GitHub Desktop.
Save tae0y/bb07c7ea391a4be0714752a43219d602 to your computer and use it in GitHub Desktop.
Download ZIP
relational_data_query_demo_on_colab_ver1.00.ipynb
Display the source blob
Display the rendered blob
Raw
relational_data_query_demo_on_colab_ver1-00.ipynb
{
"nbformat": 4,
"nbformat_minor": 0,
"metadata": {
"colab": {
"provenance": [],
"gpuType": "T4",
"authorship_tag": "ABX9TyNrsVWQgPOI79NV0EsTe5fb",
"include_colab_link": true
},
"kernelspec": {
"name": "python3",
"display_name": "Python 3"
},
"language_info": {
"name": "python"
},
"accelerator": "GPU"
},
"cells": [
{
"cell_type": "markdown",
"metadata": {
"id": "view-in-github",
"colab_type": "text"
},
"source": [
"<a href=\"https://colab.research.google.com/gist/tae0y/bb07c7ea391a4be0714752a43219d602/relational_data_query_demo_on_colab_ver1-00.ipynb\" target=\"_parent\"><img src=\"https://colab.research.google.com/assets/colab-badge.svg\" alt=\"Open In Colab\"/></a>"
]
},
{
"cell_type": "code",
"execution_count": null,
"metadata": {
"id": "fzLSCfccZWDt"
},
"outputs": [],
"source": [
"%%capture\n",
"!apt-get update\n",
"!apt-get install pciutils"
]
},
{
"cell_type": "code",
"source": [
"!curl -fsSL https://ollama.com/install.sh | sh"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "epUfyvpueByY",
"outputId": "d79bb2d7-5d8f-458b-904c-f19e28f35096"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
">>> Downloading ollama...\n",
"############################################################################################# 100.0%\n",
">>> Installing ollama to /usr/local/bin...\n",
">>> Creating ollama user...\n",
">>> Adding ollama user to video group...\n",
">>> Adding current user to ollama group...\n",
">>> Creating ollama systemd service...\n",
">>> NVIDIA GPU installed.\n",
">>> The Ollama API is now available at 127.0.0.1:11434.\n",
">>> Install complete. Run \"ollama\" from the command line.\n"
]
}
]
},
{
"cell_type": "code",
"source": [
"# 가장 간단한 방법\n",
"import subprocess\n",
"subprocess.Popen(\"ollama serve\", shell=True)"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "4EHyJfp-eEi7",
"outputId": "6e5b6512-53b3-4d99-c010-a1485a2aae97"
},
"execution_count": null,
"outputs": [
{
"output_type": "execute_result",
"data": {
"text/plain": [
"<Popen: returncode: None args: 'ollama serve'>"
]
},
"metadata": {},
"execution_count": 32
}
]
},
{
"cell_type": "code",
"source": [
"!ollama run sqlcoder:15b"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "u3gEXYDWeE4C",
"outputId": "7cff2f47-56fe-46b7-82b0-8ead89022185"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"\u001b[?25l\u001b[2K\u001b[1G⠋ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠙ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠹ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠸ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠼ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠴ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠦ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠧ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠇ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠏ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠋ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠙ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠹ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠸ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠼ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠴ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠦ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠧ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠇ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠏ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠋ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠙ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠹ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠸ \u001b[?25h\u001b[?25l\u001b[2K\u001b[1G⠼ \u001b[?25h^C\n"
]
}
]
},
{
"cell_type": "code",
"source": [
"!pip install ollama"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "SOz6u0opeoqG",
"outputId": "4bdae605-96df-4f54-b513-ea77d32d5c61"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"Collecting ollama\n",
" Downloading ollama-0.1.7-py3-none-any.whl (9.4 kB)\n",
"Collecting httpx<0.26.0,>=0.25.2 (from ollama)\n",
" Downloading httpx-0.25.2-py3-none-any.whl (74 kB)\n",
"\u001b[2K \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m75.0/75.0 kB\u001b[0m \u001b[31m3.2 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
"\u001b[?25hRequirement already satisfied: anyio in /usr/local/lib/python3.10/dist-packages (from httpx<0.26.0,>=0.25.2->ollama) (3.7.1)\n",
"Requirement already satisfied: certifi in /usr/local/lib/python3.10/dist-packages (from httpx<0.26.0,>=0.25.2->ollama) (2024.2.2)\n",
"Collecting httpcore==1.* (from httpx<0.26.0,>=0.25.2->ollama)\n",
" Downloading httpcore-1.0.4-py3-none-any.whl (77 kB)\n",
"\u001b[2K \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m77.8/77.8 kB\u001b[0m \u001b[31m9.1 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
"\u001b[?25hRequirement already satisfied: idna in /usr/local/lib/python3.10/dist-packages (from httpx<0.26.0,>=0.25.2->ollama) (3.6)\n",
"Requirement already satisfied: sniffio in /usr/local/lib/python3.10/dist-packages (from httpx<0.26.0,>=0.25.2->ollama) (1.3.1)\n",
"Collecting h11<0.15,>=0.13 (from httpcore==1.*->httpx<0.26.0,>=0.25.2->ollama)\n",
" Downloading h11-0.14.0-py3-none-any.whl (58 kB)\n",
"\u001b[2K \u001b[90m━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━\u001b[0m \u001b[32m58.3/58.3 kB\u001b[0m \u001b[31m7.4 MB/s\u001b[0m eta \u001b[36m0:00:00\u001b[0m\n",
"\u001b[?25hRequirement already satisfied: exceptiongroup in /usr/local/lib/python3.10/dist-packages (from anyio->httpx<0.26.0,>=0.25.2->ollama) (1.2.0)\n",
"Installing collected packages: h11, httpcore, httpx, ollama\n",
"Successfully installed h11-0.14.0 httpcore-1.0.4 httpx-0.25.2 ollama-0.1.7\n"
]
}
]
},
{
"cell_type": "code",
"source": [
"response = ollama.chat(model='sqlcoder', messages=[\n",
" {\n",
" 'role':'user',\n",
" 'content':'why is the sky blue?',\n",
" },\n",
"])\n",
"\n",
"print(response['message']['content'])"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "bvNw3HnziHby",
"outputId": "d1499a02-511f-4cea-e818-18b365cad757"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
"\n",
"\n",
"###### The Science of Color: Why are there so many shades of gray?\n",
"\n",
"Why does color exist in nature and not just black and white?\n"
]
}
]
},
{
"cell_type": "code",
"source": [
"import ollama\n",
"response = ollama.chat(model='sqlcoder', messages=[\n",
" {\n",
" 'role':'user',\n",
" 'content':'''\n",
"### Instructions:\n",
"Your task is to convert a question into a SQL query, given a Postgres database schema.\n",
"Adhere to these rules:\n",
"- **Deliberately go through the question and database schema word by word** to appropriately answer the question\n",
"- **Use Table Aliases** to prevent ambiguity. For example, `SELECT table1.col1, table2.col1 FROM table1 JOIN table2 ON table1.id = table2.id`.\n",
"- When creating a ratio, always cast the numerator as float\n",
"\n",
"### Input:\n",
"Generate a SQL query that answers the question `{question}`.\n",
"This query will run on a database whose schema is represented in this string:\n",
"CREATE TABLE products (\n",
" product_id INTEGER PRIMARY KEY, -- Unique ID for each product\n",
" name VARCHAR(50), -- Name of the product\n",
" price DECIMAL(10,2), -- Price of each unit of the product\n",
" quantity INTEGER -- Current quantity in stock\n",
");\n",
"\n",
"CREATE TABLE customers (\n",
" customer_id INTEGER PRIMARY KEY, -- Unique ID for each customer\n",
" name VARCHAR(50), -- Name of the customer\n",
" address VARCHAR(100) -- Mailing address of the customer\n",
");\n",
"\n",
"CREATE TABLE salespeople (\n",
" salesperson_id INTEGER PRIMARY KEY, -- Unique ID for each salesperson\n",
" name VARCHAR(50), -- Name of the salesperson\n",
" region VARCHAR(50) -- Geographic sales region\n",
");\n",
"\n",
"CREATE TABLE sales (\n",
" sale_id INTEGER PRIMARY KEY, -- Unique ID for each sale\n",
" product_id INTEGER, -- ID of product sold\n",
" customer_id INTEGER, -- ID of customer who made purchase\n",
" salesperson_id INTEGER, -- ID of salesperson who made the sale\n",
" sale_date DATE, -- Date the sale occurred\n",
" quantity INTEGER -- Quantity of product sold\n",
");\n",
"\n",
"CREATE TABLE product_suppliers (\n",
" supplier_id INTEGER PRIMARY KEY, -- Unique ID for each supplier\n",
" product_id INTEGER, -- Product ID supplied\n",
" supply_price DECIMAL(10,2) -- Unit price charged by supplier\n",
");\n",
"\n",
"-- sales.product_id can be joined with products.product_id\n",
"-- sales.customer_id can be joined with customers.customer_id\n",
"-- sales.salesperson_id can be joined with salespeople.salesperson_id\n",
"-- product_suppliers.product_id can be joined with products.product_id\n",
"\n",
"### Response:\n",
"Based on your instructions, generate SQL query to answer the question `{question}`:\n",
"\n",
" '''\n",
" },\n",
"])\n",
"\n",
"print(response['message']['content'])"
],
"metadata": {
"colab": {
"base_uri": "https://localhost:8080/"
},
"id": "BntGVbFbepHi",
"outputId": "2a2361e2-f603-46dd-b049-ed04a655323b"
},
"execution_count": null,
"outputs": [
{
"output_type": "stream",
"name": "stdout",
"text": [
" SELECT SUM(CASE WHEN sales.product_id IN (SELECT product_id FROM products WHERE price > 50) THEN quantity ELSE 0 END) AS high_price_sales, SUM(CASE WHEN sales.product_id NOT IN (SELECT product_id FROM products WHERE price > 50) THEN quantity ELSE 0 END) AS low_price_sales FROM sales;\n"
]
}
]
},
{
"cell_type": "markdown",
"source": [
"## REF\n",
"\n",
"- memory requirements determine\n",
"\n",
"https://discuss.huggingface.co/t/how-to-quickly-determine-memory-requirements-for-model/43426\n",
"\n",
"\n",
"- sqlcoder \n",
"\n",
"https://ollama.com/library/sqlcoder \n",
"\n",
"https://huggingface.co/defog/sqlcoder\n",
"\n",
"\n",
"- vector database \n",
"\n",
"https://colab.research.google.com/github/GoogleCloudPlatform/python-docs-samples/blob/main/cloud-sql/postgres/pgvector/notebooks/pgvector_gen_ai_demo.ipynb#scrollTo=aMLXHsMcme8D\n",
"\n",
"https://colab.research.google.com/github/neuml/txtai/blob/master/examples/01_Introducing_txtai.ipynb"
],
"metadata": {
"id": "gSQvUz_9a9KP"
}
}
]
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment