### Install Python Libraries

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Installs necessary Python libraries, 'folium' for map visualization and 'shapely' for geospatial operations, using pip. The '--quiet' flag suppresses verbose output during installation.

```bash
!pip install folium shapely --quiet
```

--------------------------------

### Install and Run Pre-commit Hooks

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/notebook-style-guide/notebook.ipynb

Instructions for installing the pre-commit Python package and enabling it in the repository. This ensures checks run automatically before commits.

```sh
# Install the pre-commit Python package (only needed once)
pip3 install pre-commit

# Run this command in the repository clone (only needed once)
pre-commit install

# Run all checks at any time
pre-commit run --all-files
```

--------------------------------

### Insert Sample Customer and Order Data

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Populates the 'customers' and 'orders' tables with sample records. This data is used for subsequent query examples to demonstrate database functionality.

```sql
INSERT INTO customers (customer_id, customer_name, country) VALUES
(1, "John Doe", "Canada"),
(2, "Jane Smith", "Canada"),
(3, "Sam Brown", "Canada"),
(4, "Lisa White", "Canada"),
(5, "Mark Black", "Canada");

INSERT INTO orders (order_id, customer_id, amount, product) VALUES
(101, 1, 150.00, "Book"),
(102, 2, 200.00, "Pen"),
(103, 3, 50.00, "Notebook"),
(104, 1, 300.00, "Laptop"),
(105, 4, 250.00, "Tablet");
```

--------------------------------

### Install SingleStoreDB DataFrame Package

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Installs the necessary Python packages for SingleStoreDB integration with DataFrames, including the client, SQLAlchemy dialect, and Ibis backend. This command ensures all required libraries are available for use.

```bash
pip install 'singlestoredb[dataframe]'
```

--------------------------------

### Import Libraries for Data Handling

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-notebooks/notebook.ipynb

Imports necessary libraries like pandas for data manipulation and shapely for handling geographic data. It also installs the shapely library quietly.

```python
!pip3 install shapely --quiet

import pandas as pd
import shapely.wkt
```

--------------------------------

### Full-Text Search: Using Operators (+, *, ?)

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Demonstrates advanced full-text search capabilities using operators. It searches for products where 'product' starts with 'oo' (using '?') or the 'description' contains words starting with 'versa' (using '*'). The '+' operator ensures a term must appear.

```SQL
%%sql
SELECT
    product
FROM
    orders
WHERE
    MATCH (TABLE orders) AGAINST ("product:(+oo?) OR description:versa*");
```

--------------------------------

### Install PyMongo and Import Modules

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-mongocdc/notebook.ipynb

Installs the pymongo library, which is essential for interacting with MongoDB, and imports necessary Python modules like pymongo and random for the replication process.

```python
!pip3 install pymongo --quiet

import pymongo
import random
```

--------------------------------

### Connect to SingleStoreDB with SQLAlchemy

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-notebooks/notebook.ipynb

Establishes a connection to SingleStoreDB using SQLAlchemy, either via a connection URL or automatically from the SINGLESTOREDB_URL environment variable.

```Python
import sqlalchemy as sa

# Create a SQLAlchemy engine and connect
db_connection = sa.create_engine(connection_url).connect()
```

```Python
import singlestoredb as s2

# Create a SQLAlchemy engine and connect, without having to specify the connection URL
db_connection = s2.create_engine().connect()
```

--------------------------------

### Query Data with SQL

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-notebooks/notebook.ipynb

Executes SQL queries against the SingleStoreDB database. Demonstrates selecting data and using the `result1 <<` syntax to capture query results into a Python variable for further processing.

```SQL
SELECT * FROM {{database_name}}.sf_restaurant_scores LIMIT 10;
```

```SQL
SELECT
    DATE_TRUNC('month', inspection_date) AS month,
    COUNT(*) AS count_inspection
FROM
    {{database_name}}.sf_restaurant_scores
GROUP BY
    MONTH
ORDER BY
    MONTH DESC;
```

--------------------------------

### Install and Run Pre-commit Checks (Bash)

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/CONTRIBUTING.md

Commands to install the pre-commit tool and set up Git hooks to run validation and code reformatting checks automatically before commits. Also includes a command to manually run all checks.

```bash
pip3 install pre-commit==3.7.1
pre-commit install
```

```bash
pre-commit run --all-files
```

--------------------------------

### Create and Use Database

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-notebooks/notebook.ipynb

Dynamically creates a database for the notebook or uses the current one if on a shared tier. It demonstrates using Python variables within SQL cells via Jinja templating.

```Python
shared_tier_check = %sql show variables like 'is_shared_tier'
if not shared_tier_check or shared_tier_check[0][1] == 'OFF':
    database_name = 'getting_started_notebook'
    %sql DROP DATABASE IF EXISTS {{database_name}};
    %sql CREATE DATABASE {{database_name}};
else:
    current_database = %sql SELECT DATABASE() as CurrentDatabase
    database_name = current_database[0][0]
```

--------------------------------

### Bash: Install and Initialize Pre-commit Hooks

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/CONTRIBUTING.md

This Bash snippet installs a specific version of the `pre-commit` tool using `pip3` and then initializes the pre-commit hooks in the current Git repository. This ensures that validation checks and code reformatting are run automatically before commits.

```bash
pip3 install pre-commit==3.7.1
pre-commit install
```

--------------------------------

### Display Available Fusion SQL Commands

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-fusion-sql/notebook.ipynb

Lists all available Fusion SQL commands. The output can be filtered using the LIKE clause to find specific commands.

```python
commands = %sql SHOW FUSION COMMANDS
for cmd in commands:
    print(*cmd, '\n')
```

```python
commands = %sql SHOW FUSION COMMANDS LIKE '%stage%'
for cmd in commands:
    print(*cmd, '\n')
```

--------------------------------

### Connect to Workspace Database Endpoint

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-fusion-sql/notebook.ipynb

Demonstrates how to establish a database connection to a workspace using its endpoint information retrieved from `SHOW WORKSPACES EXTENDED`. It uses the `singlestoredb` Python client.

```python
import singlestoredb as s2

with s2.connect(f'admin:{ password }@{ workspaces[0].Endpoint }:3306') as conn:
    with conn.cursor() as cur:
        cur.execute('show databases')
        for row in cur:
            print(*row)
```

--------------------------------

### Establish Database Connection

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Connects to the database using SQLAlchemy. It requires a 'connection_url' variable, which is assumed to be configured elsewhere, to establish the engine for database interactions.

```python
from sqlalchemy import *

db_connection = create_engine(connection_url)
```

--------------------------------

### Singlestore Connection API

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Provides documentation for key methods available on a Singlestore connection object (`conn`) and table objects (`phones_tbl`) for data management and inspection.

```APIDOC
conn.create_table(name: str, obj: Union[pd.DataFrame, ibis.expr.types.Table], overwrite: bool = False)
  - Creates a table in the database from a DataFrame or Ibis expression.
  - Parameters:
    - name: The name of the table to create.
    - obj: The data source, either a pandas DataFrame or an Ibis Table expression.
    - overwrite: If True, drops the table if it already exists before creating.
  - Returns: An Ibis Table object representing the newly created table.

conn.drop_table(name: str)
  - Drops a table from the database.
  - Parameters:
    - name: The name of the table to drop.

conn.show.create_table(name: str)
  - Retrieves the SQL CREATE TABLE statement for a given table.
  - Parameters:
    - name: The name of the table.
  - Returns: A list of dictionaries, where each dictionary contains a 'CreateTable' key with the SQL statement.

conn.show.tables()
  - Lists all tables in the current database.
  - Returns: A list of dictionaries, each representing a table with its schema information.

Table.head(n: int = 5)
  - Returns the first `n` rows of the table as a pandas DataFrame.
  - Parameters:
    - n: The number of rows to return.

Table.info()
  - Displays schema information for the table, including column names, types, and nullability.

Table.columns
  - An attribute that provides a list of column names in the table.

Table.schema()
  - Returns a Schema object representing the table's schema, allowing programmatic access to schema details.

ColumnExpression.cast(type: str)
  - Casts a column expression to a specified data type (e.g., 'timestamp', 'int').
  - Parameters:
    - type: The target data type string.
  - Returns: A new ColumnExpression with the casted type.

ColumnExpression.name(name: str)
  - Assigns a name to a column expression.
  - Parameters:
    - name: The desired name for the column expression.
  - Returns: A new ColumnExpression with the assigned name.
```

--------------------------------

### Add Full-Text Index

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Adds a full-text index named 'orders_ft_index' to the 'orders' table, covering the 'product' and 'description' columns using VERSION 2. It then optimizes the table and flushes changes.

```SQL
%%sql
ALTER TABLE orders ADD FULLTEXT USING VERSION 2 orders_ft_index (product, description);
OPTIMIZE TABLE orders FLUSH;
```

--------------------------------

### Display Orders with Descriptions

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Retrieves all columns from the 'orders' table, including the newly added and populated 'description' column. This step verifies that the descriptions have been correctly updated.

```SQL
%%sql
SELECT
    *
FROM
    orders;
```

--------------------------------

### Inspect Table Info

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Retrieves and displays information about the table created on the Singlestore server, similar to `DataFrame.info()`. Shows column details and types.

```python
phones_tbl.info()
```

--------------------------------

### Save Map to Stage (Python)

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Writes HTML content to a stage file named 'map.html'. This operation requires the 'singlestoredb.notebook' library and is restricted to the Standard Tier.

```python
from singlestoredb import notebook as nb

if not shared_tier_check or shared_tier_check[0][1] == "OFF":
    with nb.stage.open("map.html", "w") as st:
        st.write(html_content)
```

--------------------------------

### Create a Workspace Group

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-fusion-sql/notebook.ipynb

Executes the Fusion SQL command to create a workspace group with a specified name, region ID, firewall access from all IPs ('0.0.0.0/0'), and a generated password.

```python
%%sql
CREATE WORKSPACE GROUP '{{ wsg_name }}' IN REGION ID '{{ region_id }}'
       WITH FIREWALL RANGES '0.0.0.0/0' WITH PASSWORD '{{ password }}'
```

--------------------------------

### Visualize Data with Plotly

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-notebooks/notebook.ipynb

Uses Plotly Express to create a bar chart visualizing the count of inspections by month. Requires converting the query result to a pandas DataFrame first.

```Python
import plotly.express as px

px.bar(result1_df, x='month', y='count_inspection', title='Inspections by Month')
```

--------------------------------

### Connect to SingleStoreDB with Ibis

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Establishes a connection to the SingleStoreDB database using the Ibis library. It utilizes default connection parameters, often sourced from environment variables in notebook environments.

```python
conn = ibis.singlestoredb.connect()
```

--------------------------------

### Database Initialization and Setup

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/inserting-embeddings-from-multiple-models-into-singlestore-using-external-functions/notebook.ipynb

This snippet demonstrates the initial steps for setting up a database environment in SingleStoreDB. It includes dropping an existing database if present, creating a new database named 'embeddings_demo', and then switching the current context to this new database for subsequent operations.

```sql
%%sql
DROP DATABASE IF EXISTS embeddings_demo;
CREATE DATABASE embeddings_demo;
USE embeddings_demo;
```

--------------------------------

### Create and Use Database

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/basic-query-examples/notebook.ipynb

Creates a new database named 'memsql_example' and sets it as the active database for subsequent operations. This is a foundational step before creating tables or running queries.

```sql
CREATE DATABASE memsql_example;
USE memsql_example;
```

--------------------------------

### Fusion SQL Command Syntax: SHOW REGIONS

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-fusion-sql/notebook.ipynb

Specifies the syntax for retrieving region information. It supports filtering with a LIKE pattern, ordering results, and limiting the number of returned regions.

```APIDOC
SHOW REGIONS [ LIKE '<pattern>' ] [ ORDER BY '<key>' [ ASC | DESC ],... ] [ LIMIT <integer> ];
```

--------------------------------

### Start Pipeline - SQL

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/singlestore-cheat-sheet/notebook.ipynb

Initiates a previously defined pipeline to begin loading data. This command starts the data ingestion process.

```SQL
START PIPELINE SalesData_Pipeline;
```

--------------------------------

### Get CREATE TABLE Statement

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Retrieves the SQL `CREATE TABLE` statement generated by the data upload process. This allows inspection of how data types were mapped to SQL types.

```python
ct = conn.show.create_table('phones')
print(ct[0]['CreateTable'])
```

--------------------------------

### List Numeric Methods on Ibis Column

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Iterates through the methods available on an Ibis numeric column object and prints those that do not start with an underscore. This helps in discovering available numeric operations.

```Python
for x in dir(phones_tbl.overall):
    if not x.startswith('_'):
        print(x)
```

--------------------------------

### Initialize Environment and OpenAI Client

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/insure-gpt-demo/notebook.ipynb

Sets up the Python environment by importing necessary libraries and configuring essential services. It sets the SingleStoreDB connection URL via an environment variable and initializes the OpenAI client with an API key, preparing for data processing and AI model interactions.

```python
from fpdf import FPDF
from langchain.text_splitter import CharacterTextSplitter
from langchain_community.document_loaders import TextLoader
from langchain_community.embeddings import OpenAIEmbeddings
from openai import OpenAI
import os
import base64
from langchain_community.vectorstores.singlestoredb import SingleStoreDB

os.environ["SINGLESTOREDB_URL"] = "<SINGLESTOREDB URL"
client = OpenAI(api_key="YOUR OPENAI KEY")
```

--------------------------------

### List Date/Time Methods on Ibis Column

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Iterates through the methods available on an Ibis date/time column object and prints those that do not start with an underscore. This helps in discovering available date/time operations.

```Python
for x in dir(phones_tbl.date):
    if not x.startswith('_'):
        print(x)
```

--------------------------------

### List Workspace Groups

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-fusion-sql/notebook.ipynb

Uses Fusion SQL to display workspace groups, filtering the results to show only those whose names start with 'Fusion'. This verifies the creation of the workspace group.

```python
%%sql
SHOW WORKSPACE GROUPS LIKE 'Fusion%'
```

--------------------------------

### List String Methods on Ibis Column

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Iterates through the methods available on an Ibis string column object (like 'texpr') and prints those that do not start with an underscore. This helps in discovering available string operations.

```Python
for x in dir(texpr):
    if not x.startswith('_'):
        print(x)
```

--------------------------------

### Specify Source Collection and Start Replication

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-mongocdc/notebook.ipynb

Configures the replication process by specifying the source MongoDB link, database, and collection ('scores'). It then creates the 'scores' collection in SingleStore, initiating the data pull from MongoDB.

```python
create_col_args = {"from": {"link": "mongolink", "database": "cdcdemo", "collection": "scores"}}
res = s2db.create_collection("scores", **create_col_args)
```

--------------------------------

### Initialize Langchain and SingleStoreDB Components

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/building-a-multi-agent-ai-app-with-autogen/notebook.ipynb

Sets up the necessary Langchain components, including loading documents from 'example.md', splitting them into chunks, initializing OpenAI embeddings, and configuring the SingleStoreDB connection URL.

```python
from langchain_community.vectorstores import SingleStoreDB
from langchain_openai import OpenAIEmbeddings
from langchain_community.document_loaders import UnstructuredMarkdownLoader
from langchain_text_splitters import CharacterTextSplitter
from typing import List, Dict, Union
import os

loader = UnstructuredMarkdownLoader("./example.md")

os.environ["OPENAI_API_KEY"] = "api-key"

data = loader.load()

text_splitter = CharacterTextSplitter()

docs = text_splitter.split_documents(data)

embeddings = OpenAIEmbeddings()

os.environ["SINGLESTOREDB_URL"] = "admin:pass@host:3306/db"
```

--------------------------------

### Cleanup Tables (SQL)

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Drops existing 'customers', 'orders', and 'neighborhoods' tables. This is a standard SQL cleanup operation.

```sql
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS neighborhoods;
```

--------------------------------

### Setup SingleStore Database

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/cdc-mongodb-sql-commands/notebook.ipynb

Initializes the SingleStore environment by dropping an existing database if it exists and then creating a new one named 'sample_airbnb'. This ensures a clean slate for the demonstration.

```sql
DROP DATABASE IF EXISTS sample_airbnb;
CREATE DATABASE sample_airbnb;
```

--------------------------------

### Calculate Total Sales

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Calculates the sum of the 'amount' column from the 'orders' table to determine the total sales value.

```sql
SELECT
    SUM(amount) AS total_sales
FROM
    orders;
```

--------------------------------

### Start Pipeline

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/network-intrusion-detection-part-3/notebook.ipynb

Initiates the data loading process for the `siem_log_real` pipeline, allowing data to flow from Kafka into the SingleStore table.

```sql
START PIPELINE siem_log_real;
```

--------------------------------

### Count the Number of Orders

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Counts the total number of rows in the 'orders' table, indicating the total number of orders placed.

```sql
SELECT
    COUNT(*) AS number_of_orders
FROM
    orders;
```

--------------------------------

### Create SingleStore Database for CDC Demo

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-mongocdc/notebook.ipynb

Initializes the SingleStore environment by dropping an existing database named 'cdcdemo' if it exists, and then creating a fresh database to host the replicated MongoDB data.

```sql
DROP DATABASE IF EXISTS cdcdemo;
CREATE DATABASE cdcdemo;
```

--------------------------------

### Create and Use Database

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

This SQL snippet demonstrates how to conditionally drop an existing database and then create a new one named 'ibis_getting_started'. It's designed for environments where a dedicated database is needed for the notebook's operations.

```sql
shared_tier_check = %sql show variables like 'is_shared_tier'
if not shared_tier_check or shared_tier_check[0][1] == 'OFF':
    %sql DROP DATABASE IF EXISTS ibis_getting_started;
    %sql CREATE DATABASE ibis_getting_started;
```

--------------------------------

### Calculate Average Order Amount

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Computes the average of the 'amount' column from the 'orders' table, rounded to two decimal places.

```sql
SELECT
    ROUND(AVG(amount), 2) AS avg_order_amount
FROM
    orders;
```

--------------------------------

### Drop Table

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Removes a specified table from the Singlestore database. This is a utility function for cleaning up or managing database objects.

```python
conn.drop_table('phones2')
```

--------------------------------

### Connect to SingleStoreDB Workspace Endpoint (Python)

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-fusion-sql/notebook.ipynb

Establishes a database connection to a SingleStoreDB workspace using the singlestoredb Python client library, leveraging the workspace's endpoint information. It then executes a sample query to show available databases.

```python
import singlestoredb as s2

with s2.connect(f'admin:{ password }@{ workspaces[0].Endpoint }:3306') as conn:
    with conn.cursor() as cur:
        cur.execute('show databases')
        for row in cur:
            print(*row)
```

--------------------------------

### Drop Database (Conditional)

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Checks if the database is not a shared tier and, if so, drops the 'ibis_getting_started' database. This is a cleanup operation for non-starter workspaces.

```Python
shared_tier_check = %sql show variables like 'is_shared_tier'
if not shared_tier_check or shared_tier_check[0][1] == 'OFF':
    %sql DROP DATABASE IF EXISTS ibis_getting_started;
```

--------------------------------

### Disable Ibis Verbose Mode

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Disables the verbose mode for Ibis, preventing the display of SQL queries executed in the background.

```Python
ibis.options.verbose = False
```

--------------------------------

### Install a New Python Package

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/notebook-basics/notebook.ipynb

Installs a package from PyPI, such as the Kaggle open dataset library, into the notebook environment using pip.

```shell
!pip3 install opendatasets
```

--------------------------------

### Populate Sample Tables with Data

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/basic-query-examples/notebook.ipynb

Inserts sample data into the 'departments', 'employees', and 'salaries' tables. This data is used in subsequent examples to illustrate query results.

```sql
INSERT INTO departments (id, name) VALUES
  (1, 'Marketing'), (2, 'Finance'), (3, 'Sales'), (4, 'Customer Service');

INSERT INTO employees (id, deptId, managerId, name, hireDate, state) VALUES
  (1, 2, NULL, "Karly Steele", "2011-08-25", "NY"),
  (2, 1, 1, "Rhona Nichols", "2008-09-11", "TX"),
  (3, 4, 2, "Hedda Kent", "2005-10-27", "TX"),
  (4, 2, 1, "Orli Strong", "2001-07-01", "NY"),
  (5, 1, 1, "Leonard Haynes", "2011-05-30", "MS"),
  (6, 1, 5, "Colette Payne", "2002-10-22", "MS"),
  (7, 3, 4, "Cooper Hatfield", "2010-08-19", "NY"),
  (8, 2, 4, "Timothy Battle", "2001-01-21", "NY"),
  (9, 3, 1, "Doris Munoz", "2008-10-22", "NY"),
  (10, 4, 2, "Alea Wiggins", "2007-08-21", "TX");

INSERT INTO salaries (employeeId, salary) VALUES
  (1, 885219), (2, 451519), (3, 288905), (4, 904312), (5, 919124),
  (6, 101538), (7, 355077), (8, 900436), (9, 41557), (10, 556263);
```

--------------------------------

### Find Maximum Order Amount

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Retrieves the maximum value from the 'amount' column in the 'orders' table, representing the largest order value.

```sql
SELECT
    MAX(amount) AS max_order_amount
FROM
    orders;
```

--------------------------------

### Create Customers and Orders Tables

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Sets up two essential tables: 'customers' for customer details and 'orders' for transaction information. This includes defining primary keys and appropriate data types for relational data storage.

```sql
DROP TABLE IF EXISTS customers;
DROP TABLE IF EXISTS orders;

CREATE TABLE IF NOT EXISTS customers /* Creating table for sample data. */(
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50),
    country VARCHAR(50)
);

CREATE TABLE IF NOT EXISTS orders /* Creating table for sample data. */(
    order_id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    product VARCHAR(50)
);
```

--------------------------------

### Find Minimum Order Amount

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Retrieves the minimum value from the 'amount' column in the 'orders' table, representing the smallest order value.

```sql
SELECT
    MIN(amount) AS min_order_amount
FROM
    orders;
```

--------------------------------

### Start Dash App Server

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/create-dash-app/notebook.ipynb

Initiates the execution of the configured Dash application. This command makes the dashboard accessible via a web interface, allowing interactive data exploration.

```python
connectionInfo = await apps.run_dashboard_app(app)
```

--------------------------------

### Convert Query Result to DataFrame

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-notebooks/notebook.ipynb

Shows how to check the type of a query result and convert it into a pandas DataFrame for easier manipulation and visualization.

```Python
type(result1)
```

```Python
result1_df = pd.DataFrame(result1)
result1_df[:5]
```

--------------------------------

### Show Workspaces

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-fusion-sql/notebook.ipynb

Displays information about workspaces within a specified group. It can fetch extended details including endpoint information.

```sql
SHOW WORKSPACES IN GROUP "{{ wsg_name }}"
```

```sql
SHOW WORKSPACES IN GROUP '{{ wsg_name }}' ORDER BY Name EXTENDED
```

```python
workspaces = %sql SHOW WORKSPACES IN GROUP '{{ wsg_name}}' EXTENDED
workspaces
```

--------------------------------

### Setup SingleStore Connection Pool (Python)

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/create-dash-app/notebook.ipynb

Establishes a connection pool using SQLAlchemy to manage connections to SingleStore. Includes downloading CA certificate and defining a function to execute queries against the database.

```python
from sqlalchemy import create_engine, text
import requests
import pandas as pd

ca_cert_url = "https://portal.singlestore.com/static/ca/singlestore_bundle.pem"
ca_cert_path = "/tmp/singlestore_bundle.pem"

response = requests.get(ca_cert_url)
with open(ca_cert_path, "wb") as f:
    f.write(response.content)

sql_connection_string = connection_url.replace("singlestoredb", "mysql+pymysql")
engine = create_engine(
    f"{sql_connection_string}?ssl_ca={ca_cert_path}",
    pool_size=10,           # Maximum number of connections in the pool is 10
    max_overflow=5,         # Allow up to 5 additional connections (temporary overflow)
    pool_timeout=30         # Wait up to 30 seconds for a connection from the pool
)

def execute_query(query: str):
    with engine.connect() as connection:
        return pd.read_sql_query(query, connection)
```

--------------------------------

### Conditional Database Cleanup (Python/SQL)

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Conditionally drops the 'multi_model' database. It first checks the 'is_shared_tier' variable and proceeds with the drop only if the environment is not a shared tier.

```python
shared_tier_check = %sql SHOW VARIABLES LIKE "is_shared_tier"
if not shared_tier_check or shared_tier_check[0][1] == "OFF":
    %sql DROP DATABASE IF EXISTS multi_model;
```

--------------------------------

### Update Orders Table with Descriptions

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Populates the 'description' column in the 'orders' table based on the 'product' type using a CASE statement. This assigns specific descriptions to different products like 'Book', 'Pen', 'Notebook', etc.

```SQL
%%sql
UPDATE orders
SET description = CASE
    WHEN product = "Book" THEN "A high-quality book that offers insightful content and engaging narratives."
    WHEN product = "Pen" THEN "A smooth-writing pen designed for comfort and precision."
    WHEN product = "Notebook" THEN "A versatile notebook perfect for notes, sketches, and ideas."
    WHEN product = "Laptop" THEN "A powerful laptop with high performance and sleek design for all your computing needs."
    WHEN product = "Tablet" THEN "A compact tablet with a vibrant display and versatile functionality."
    ELSE "A product with excellent features and quality."
END;
```

--------------------------------

### Display Orders Table

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Retrieves all columns from the 'orders' table. This is used to verify the data after updates and to show the structure of the table, including the 'dimensions' column.

```SQL
%%sql
SET vector_type_project_format = JSON;

SELECT
    *
FROM
    orders;
```

--------------------------------

### Start FastAPI Server

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/cloud-functions-template/notebook.ipynb

Provides the Python code to start and run a FastAPI application. It uses the `singlestoredb.apps` module to manage the application lifecycle and provides connection information.

```Python
import singlestoredb.apps as apps
connection_info = await apps.run_function_app(app)
```

--------------------------------

### Download Example Markdown Document

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/building-a-multi-agent-ai-app-with-autogen/notebook.ipynb

Fetches a sample Markdown file from a remote URL using the 'requests' library and saves it locally as 'example.md'. This file is used for populating the vector store.

```python
import requests

r = requests.get("https://raw.githubusercontent.com/microsoft/FLAML/main/website/docs/Examples/Integrate%20-%20Spark.md")
open('example.md', 'wb').write(r.content)
```

--------------------------------

### User Interaction Example

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/searching-all-of-wikipedia/notebook.ipynb

Provides a simple command-line interface for users to ask questions. It prompts the user for input and then calls the `ask_wiki_page` function to get and display an answer.

```python
query = input('Ask me a question about video games!')
ask_wiki_page(query)
```

--------------------------------

### Run Pre-commit for Notebook Formatting

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/notebook-style-guide/notebook.ipynb

Executes the pre-commit program to automatically apply notebook headers and footers based on the metadata configuration.

```sh
pre-commit run --all-files
```

--------------------------------

### Install Required Python Libraries

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/resume-evaluator-ANN-index-search/notebook.ipynb

Installs essential Python packages for PDF processing, OpenAI integration, AWS S3 interaction, and SingleStoreDB connectivity. The '-q' flag ensures quiet installation.

```shell
!pip install -q pdfminer.six openai boto3 sqlalchemy.singlestoredb
```

--------------------------------

### Count Orders by Day

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Aggregates and counts the number of orders for each day. It parses the 'order_date' from the 'additional_details' column, formats it to a 'YYYY-MM-DD' string, and groups the counts by date.

```sql
SELECT
    DATE_FORMAT(STR_TO_DATE(additional_details::order_date, '"%Y-%m-%d"'), '%Y-%m-%d') AS order_date,
    COUNT(*) AS order_count
FROM
    orders
GROUP BY
    order_date
ORDER BY
    order_date;
```

--------------------------------

### Measure Distances Between Neighborhoods

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Calculates the geographical distance between the centroids and shapes of neighborhoods. This query specifically measures distances from Vancouver to all other neighborhoods, ordering the results by distance.

```sql
SELECT
    b.name AS town,
    ROUND(GEOGRAPHY_DISTANCE(a.centroid, b.centroid), 0) AS distance_from_center,
    ROUND(GEOGRAPHY_DISTANCE(a.shape, b.shape), 0) AS distance_from_border
FROM
    neighborhoods a, neighborhoods b
WHERE
    a.name = "Vancouver"
ORDER BY
    2;
```

--------------------------------

### Create Sample Tables

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/basic-query-examples/notebook.ipynb

Defines the schema for three tables: 'departments', 'employees', and 'salaries'. These tables are structured to hold sample data for demonstrating various SQL query functionalities.

```sql
CREATE TABLE departments /* Creating table for sample data. */(
  id int,
  name varchar(255),
  PRIMARY KEY (id)
);

CREATE TABLE employees /* Creating table for sample data. */(
  id int,
  deptId int,
  managerId int,
  name varchar(255),
  hireDate date,
  state char(2),
  PRIMARY KEY (id)
);

CREATE TABLE salaries /* Creating table for sample data. */(
  employeeId int,
  salary int,
  PRIMARY KEY (employeeId)
);
```

--------------------------------

### Install Libraries

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/hybrid-search/notebook.ipynb

Installs necessary Python libraries for the notebook, including wget for downloading files and the OpenAI client. The --quiet flag suppresses verbose output during installation.

```python
%pip install wget openai==1.3.3 --quiet
```

--------------------------------

### Import Libraries and Connect to SingleStoreDB

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/kebab-case/notebook.ipynb

Imports necessary libraries like pandas and singlestoredb, and establishes a connection to the SingleStore database. This is the initial setup for interacting with the database.

```python
import pandas as pd
import singlestoredb as s2
s2_conn = s2.connect()
s2_cur = s2_conn.cursor()
```

--------------------------------

### Suspend and Resume Workspaces

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-fusion-sql/notebook.ipynb

Provides Fusion SQL commands to suspend and resume individual workspaces. These operations can optionally wait for the state change to complete.

```APIDOC
RESUME WORKSPACE { ID '<workspace-id>' | '<workspace-name>' } [ IN GROUP { ID '<group-id>' | '<group-name>' } ] [ WAIT ON RESUMED ];

SUSPEND WORKSPACE { ID '<workspace-id>' | '<workspace-name>' } [ IN GROUP { ID '<group-id>' | '<group-name>' } ] [ WAIT ON SUSPENDED ];
```

```python
%%sql
SUSPEND WORKSPACE 'workspace-1' IN GROUP '{{ wsg_name }}'
```

```python
%%sql
RESUME WORKSPACE 'workspace-1' IN GROUP '{{ wsg_name }}' WAIT ON RESUMED
```

--------------------------------

### Upload DataFrame to Singlestore

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Creates a new table in the Singlestore server from a pandas DataFrame. The `overwrite=True` parameter ensures that an existing table with the same name is replaced.

```python
phones_tbl = conn.create_table('phones', phones_df, overwrite=True)
phones_tbl.head(3)
```

--------------------------------

### Setup Environment for FastAPI App

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/cloud-functions-template/notebook.ipynb

Configures the environment for a FastAPI application by defining the Data Model using Pydantic and setting up a thread executor for concurrent request handling.

```python
from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from singlestoredb import connect
from concurrent.futures import ThreadPoolExecutor
import asyncio

# Define the Type of the Data
class Item(BaseModel):
    id: int
    name: str
    price: float

# Create an executor that can execute queries on multiple threads simultaneously
executor = ThreadPoolExecutor()
def run_in_thread(fn, *args):
    loop = asyncio.get_event_loop()
    return loop.run_in_executor(executor, fn, *args)
```

--------------------------------

### Count Rows Matching Filter Expression

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-dataframes/notebook.ipynb

Filters the original table ('phones_tbl') using the 'has_iphone_4' boolean expression and then counts the number of rows that satisfy the condition.

```Python
phones_tbl[has_iphone_4].count()
```

--------------------------------

### Install Perspective Library

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/network-intrusion-detection-part-3/notebook.ipynb

Installs the `perspective-python` library, which is essential for creating interactive, real-time dashboards and data visualizations.

```python
%pip install perspective-python --quiet
```

--------------------------------

### Orders Count by Customer Over Time

Source: https://github.com/singlestore-labs/spaces-notebooks/blob/master/notebooks/getting-started-with-singlestore/notebook.ipynb

Provides a daily count of orders for each customer. It parses the 'order_date' from 'additional_details', formats it to 'YYYY-MM-DD', and groups the results by customer ID and date.

```sql
SELECT
    customer_id,
    DATE_FORMAT(STR_TO_DATE(additional_details::order_date, '"%Y-%m-%d"'), '%Y-%m-%d') AS order_date,
    COUNT(*) AS order_count
FROM
    orders
GROUP BY
    customer_id, order_date
ORDER BY
    customer_id, order_date;
```