### Basic chdb Query Example

Source: https://github.com/chdb-io/chdb/blob/main/docs/api.md

Shows how to connect to chdb, execute a simple query to get the version, and fetch the result.

```python
import chdb.dbapi as dbapi

print("chdb driver version: {0}".format(dbapi.get_client_info()))

# Create connection and cursor
conn = dbapi.connect()
cur = conn.cursor()

# Execute query
cur.execute('SELECT version()')
print("description:", cur.description)
print("data:", cur.fetchone())

# Clean up
cur.close()
conn.close()
```

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

### Install chdb

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_urleng_persistence.ipynb

Installs the chdb library. Run this command before using chdb.

```python
!pip install chdb --upgrade --quiet
```

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

### Complete DataStore Example: Reading, Joining, and Generating Data

Source: https://github.com/chdb-io/chdb/blob/main/docs/FACTORY_METHODS.md

A comprehensive example demonstrating reading data from S3 and MySQL, joining them, filtering, selecting columns, and generating test data using factory methods.

```python
from datastore import DataStore

# Read from S3 with auto-detection
s3_data = DataStore.from_s3(
    "s3://my-bucket/events/*.parquet",
    access_key_id="KEY",
    secret_access_key="SECRET"
)

# Read from MySQL
mysql_data = DataStore.from_mysql(
    host="localhost:3306",
    database="mydb",
    table="users",
    user="root",
    password="pass"
)

# Join different sources
result = s3_data.join(
    mysql_data,
    left_on="user_id",
    right_on="id"
).select(
    "user_id",
    "event_type",
    "name",
    "email"
).filter(
    s3_data.event_date >= '2024-01-01'
).execute()

# Generate test data
numbers = DataStore.from_numbers(1000)
test_data = numbers.select(
    numbers.number.as_("id"),
    (numbers.number * 10).as_("value")
).execute()
```

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

### Install Beta Versions of chDB

Source: https://github.com/chdb-io/chdb/blob/main/VERSION-GUIDE.md

Use the `--pre` flag to install the latest beta version. To install a specific beta version, specify the exact version number.

```bash
# Install latest beta version
pip install --pre chdb

# Install specific beta version
pip install chdb==2.2.0b0
```

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

### Install chDB and Dependencies

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_demos.ipynb

Installs necessary libraries like pandas and pyarrow, then installs and shows information about the chdb package.

```python
!pip install pandas pyarrow --upgrade >/dev/null 2>&1
!pip install chdb --upgrade >/dev/null 2>&1
!pip show chdb
```

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

### Install and Show Packages

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_movielens_DNN.ipynb

Installs or upgrades TensorFlow, chDB, Pandas, PyArrow, Scikit-learn, NumPy, and Matplotlib. Then, it displays the installed versions of tensorflow-cpu and chdb.

```python
%pip install -q -i https://pypi.tuna.tsinghua.edu.cn/simple --upgrade tensorflow-cpu chdb pandas pyarrow scikit-learn numpy matplotlib
%pip show tensorflow-cpu chdb
```

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

### Install chDB and Download Data

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_Taxi_Parquet.ipynb

Installs the chDB library and downloads sample taxi Parquet files. Ensure you have sufficient disk space.

```python
!pip install chdb --pre --upgrade

!mkdir -p taxi
!wget https://github.com/cwida/duckdb-data/releases/download/v1.0/taxi_2019_04.parquet -O taxi/201904.parquet
!wget https://github.com/cwida/duckdb-data/releases/download/v1.0/taxi_2019_05.parquet -O taxi/201905.parquet
!wget https://github.com/cwida/duckdb-data/releases/download/v1.0/taxi_2019_06.parquet -O taxi/201906.parquet
```

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

### Install Requirements

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_GPT.ipynb

Installs the necessary Python libraries, openai and chdb, for the project. Use this at the beginning of your environment setup.

```python
#@title Install Requirements { display-mode: "form" }
!pip install openai chdb --quiet
```

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

### Install and Show chdb Package

Source: https://github.com/chdb-io/chdb/blob/main/benchmark/dataframe.ipynb

Installs the necessary libraries including chdb, duckdb, pandas, and polars. It then displays information about the installed chdb package.

```python
!pip install -U duckdb
!pip install -U pandas
!pip install -U polars
!pip install -U 'chdb>=2.0.0b1'
!pip show chdb
```

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

### Clone and Install Repository

Source: https://github.com/chdb-io/chdb/blob/main/CONTRIBUTING.md

Clone the chdb repository and install it in editable mode. This setup allows for local development and testing.

```bash
git clone git@github.com:YourLogin/chdb.git
cd chdb
pip install -U pip setuptools -e .
```

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

### Install and Show Package Versions

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_vector_search.ipynb

Installs required Python packages and then displays their versions. Ensure you have the correct versions for compatibility.

```python
%pip install -q --upgrade tensorflow gensim chdb pandas pyarrow numpy==1.23.5 matplotlib
%pip show tensorflow chdb gensim numpy
```

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

### Install chdb

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/SKILL.md

Install the chdb package using pip to begin using the DataStore functionality.

```bash
pip install chdb
```

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

### Install and Show chDB Package

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_tpch.ipynb

Installs the chdb and matplotlib packages and then displays information about the installed chdb package. Restart the kernel if prompted.

```python
%pip install chdb matplotlib --upgrade --quiet
%pip show chdb
```

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

### Create Dedicated Virtual Environment for Tox

Source: https://github.com/chdb-io/chdb/blob/main/CONTRIBUTING.md

Set up a clean virtual environment and install tox within it to resolve potential conflicts or issues with existing installations.

```bash
virtualenv .venv
source .venv/bin/activate
.venv/bin/pip install tox
.venv/bin/tox -e all
```

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

### Install chDB and Dependencies

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_Hugginface_Parquet.ipynb

Install the necessary libraries, pyarrow and chdb, using pip. Ensure chdb is upgraded to the latest pre-release version.

```python
!pip install pyarrow pandas
!pip install chdb --pre --upgrade
```

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

### Example Values for ClickHouse Settings

Source: https://github.com/chdb-io/chdb/blob/main/refs/clickhouse-formats-settings.md

This dictionary provides example values for various ClickHouse settings, illustrating the expected input for different types of parameters such as compression methods, date/time formats, schema sources, escaping rules, Parquet versions, and Avro codecs.

```python
SETTING_EXAMPLES = {
    # Compression methods
    'compression_method': ['lz4', 'snappy', 'zstd', 'gzip', 'brotli', 'none'],
    
    # Date/Time formats
    'date_time_input_format': ['best_effort', 'best_effort_us', 'basic'],
    'date_time_output_format': ['simple', 'iso', 'unix_timestamp'],
    
    # Schema sources
    'format_schema_source': ['file', 'string', 'query'],
    
    # Escaping rules
    'escaping_rule': ['Escaped', 'Quoted', 'CSV', 'JSON', 'XML', 'Raw'],
    
    # Parquet versions
    'parquet_version': ['1.0', '2.4', '2.6', '2.latest'],
    
    # Avro codecs
    'avro_codec': ['null', 'deflate', 'snappy', 'zstd'],
}
```

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

### Install and Configure Pre-commit Hooks

Source: https://github.com/chdb-io/chdb/blob/main/CONTRIBUTING.md

Install pre-commit and its hooks to automatically check code quality before commits. This helps maintain code standards.

```bash
pip install pre-commit
pre-commit install
```

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

### Install chDB AI Skill for Project

Source: https://github.com/chdb-io/chdb/blob/main/README.md

Installs the chDB AI Skill for project-level use, making it version-controlled and shareable with a team. Use the --project flag for this purpose.

```bash
curl -sL https://raw.githubusercontent.com/chdb-io/chdb/main/install_skill.sh | bash -s -- --project
```

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

### Read from HDFS

Source: https://github.com/chdb-io/chdb/blob/main/refs/clickhouse-table-functions.md

Example of reading data from HDFS using its URI.

```sql
SELECT * FROM hdfs('hdfs://namenode:9000/data/*.csv', 'CSV');

```

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

### Check Tox Version and Path

Source: https://github.com/chdb-io/chdb/blob/main/CONTRIBUTING.md

Verify your tox installation and its location. This helps in troubleshooting tox-related issues.

```bash
tox --version
# OR
which tox
```

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

### Session Initialization Examples

Source: https://github.com/chdb-io/chdb/blob/main/docs/api.md

Demonstrates various ways to initialize a Session, including in-memory databases, relative and absolute file paths, and paths with query parameters.

```python
from chdb import Session

session = Session("test.db")
session = Session(":memory:")
session = Session("[file:test.db](file:test.db)")
session = Session("/path/to/test.db")
session = Session("[file:/path/to/test.db](file:/path/to/test.db)")
session = Session("[file:test.db?param1=value1&param2=value2](file:test.db?param1=value1&param2=value2)")
session = Session("[file::memory](file::memory):?verbose&log-level=test")
session = Session("///path/to/test.db?param1=value1&param2=value2")
```

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

### Complete DataStore Workflow Example

Source: https://github.com/chdb-io/chdb/blob/main/docs/REMOTE_SESSION_DESIGN.md

Illustrates a full workflow using DataStore, from connecting to a data source, exploring databases and tables, analyzing data with SQL, and performing further analysis with a pandas-style API or directly with a table.

```python
# Cell 1: Connect
from datastore import DataStore

ds = DataStore(
    source="clickhouse",
    host="analytics.company.com:9440",
    user="analyst",
    password="***",
    secure=True
)

# Cell 2: Explore
ds.databases()           # ['production', 'staging', 'ml']
ds.tables("production")  # ['users', 'orders', 'events']
ds.describe("production", "orders")

# Cell 3: Analyze with SQL
ds.use("production")

top_customers = ds.sql(''
    SELECT 
        u.name,
        SUM(o.amount) as total_spend
    FROM users u
    JOIN orders o ON u.id = o.user_id
    GROUP BY u.name
    ORDER BY total_spend DESC
    LIMIT 100
'')

# Cell 4: Continue with pandas-style API
vip = top_customers[top_customers['total_spend'] > 10000]
vip.sort_values('total_spend', ascending=False)

# Cell 5: Or work directly with a table
users = ds.table("users")  # uses default database from use()
active = users[users['status'] == 'active']  # pandas-style filter
active.groupby('country').count()
```

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

### Execute a query and get DataFrame results

Source: https://github.com/chdb-io/chdb/blob/main/docs/api.md

Executes a SQL query and specifies 'dataframe' as the format to receive results as a Pandas DataFrame. Requires pandas to be installed.

```pycon
>>> # DataFrame format
>>> df = conn.query("SELECT number FROM numbers(5)", "dataframe")
>>> print(df)
   number
0       0
1       1
2       2
3       3
4       4
```

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

### Quick Start: Enable, Operate, and Report

Source: https://github.com/chdb-io/chdb/blob/main/docs/PROFILING.md

Enable profiling before DataStore operations, perform your operations, and then retrieve and report the profiling results. Remember to disable profiling when done.

```python
from datastore import DataStore, enable_profiling, disable_profiling, get_profiler

# Enable profiling before your operations
enable_profiling()

# Perform DataStore operations
ds = DataStore.from_file("data.csv")
result = (
ds
    .filter(ds.age > 25)
    .groupby("department")
    .agg({"salary": "mean"})
    .to_df())

# Get the profiler and view results
profiler = get_profiler()
profiler.report()

# Disable profiling when done
disable_profiling()
```

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

### Query with Arrow Table Output

Source: https://github.com/chdb-io/chdb/blob/main/docs/quickstart.md

Get query results as an Arrow Table, suitable for efficient data manipulation and interoperability with other systems. Requires pyarrow to be installed.

```python
table = chdb.query("SELECT number FROM numbers(1000)", "ArrowTable")
print(type(table))  # <class 'pyarrow.lib.Table'>
print(f"Rows: {len(table)}")
```

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

### DataStore Creation using Factory Method (Recommended)

Source: https://github.com/chdb-io/chdb/blob/main/docs/FACTORY_METHODS.md

Illustrates creating a DataStore from S3 using a factory method, benefiting from IDE auto-completion and type hints.

```python
ds = DataStore.from_s3(
    url="s3://bucket/data.parquet",
    access_key_id="KEY",
    secret_access_key="SECRET"
)
```

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

### Execute SQL Query with One-Shot API

Source: https://github.com/chdb-io/chdb/blob/main/README.md

Provides examples of using the chdb.query function for quick SQL executions, including getting the version and querying data from files in various formats like Parquet and CSV. It also shows how to retrieve metadata about the query execution.

```python
import chdb
res = chdb.query('select version()', 'Pretty'); print(res)
```

```python
# See more data type format in tests/format_output.py
res = chdb.query('select * from file("data.parquet", Parquet)', 'JSON'); print(res)
res = chdb.query('select * from file("data.csv", CSV)', 'CSV');  print(res)
print(f"SQL read {res.rows_read()} rows, {res.bytes_read()} bytes, storage read {res.storage_rows_read()} rows, {res.storage_bytes_read()} bytes, elapsed {res.elapsed()} seconds")
```

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

### Performance Tip: Use Parquet over CSV

Source: https://github.com/chdb-io/chdb/blob/main/docs/PANDAS_MIGRATION_GUIDE.md

Demonstrates the difference in DataStore initialization between slow CSV files and faster Parquet files.

```python
from datastore import DataStore

# ❌ CSV is slow - full table scan
ds = DataStore.uri("data.csv")

# ✅ Parquet is 10-100x faster - columnar storage, reads only needed columns
ds = DataStore.uri("data.parquet")

```

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

### Preview Documentation Locally

Source: https://github.com/chdb-io/chdb/blob/main/CONTRIBUTING.md

Serve the compiled documentation using Python's http.server for local preview. Access the documentation at http://localhost:8000.

```bash
python3 -m http.server --directory 'docs/_build/html'
```

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

### Initialize and Close Session

Source: https://github.com/chdb-io/chdb/blob/main/docs/api.md

Demonstrates how to create a database session and explicitly close it when done.

```python
>>> session = Session("test.db")
>>> session.query("SELECT 1")
>>> session.close()  # Explicitly close the session
```

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

### Run Unit Tests with Tox

Source: https://github.com/chdb-io/chdb/blob/main/CONTRIBUTING.md

Execute all unit tests using tox to ensure your changes do not break existing functionality. Install tox if necessary.

```bash
tox
```

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

### Force Reinstall or Install Specific chDB Version

Source: https://github.com/chdb-io/chdb/blob/main/docs/troubleshooting.md

Use pip to force a reinstallation of chDB or install a specific version to resolve installation issues.

```bash
# Force reinstall
pip install --force-reinstall chdb
```

```bash
# Install specific version
pip install chdb==3.7.0
```

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

### Debugging Queries with 'Pretty' Format and EXPLAIN

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-sql/examples/examples.md

Shows how to use the 'Pretty' output format for quick result inspection and `EXPLAIN` to analyze query execution plans. Also demonstrates checking column types.

```python
import chdb

# Use Pretty format to quickly inspect results
chdb.query("SELECT * FROM file('data.parquet', Parquet) LIMIT 5", "Pretty").show()

# Check column types
chdb.query (
    """
    SELECT name, toTypeName(name) AS name_type, toTypeName(value) AS value_type
    FROM file('data.parquet', Parquet)
    LIMIT 1
""", "Pretty").show()

# Explain query execution plan
chdb.query("EXPLAIN SELECT * FROM file('data.parquet', Parquet) WHERE x > 100").show()
```

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

### Go CSV Settings Example

Source: https://github.com/chdb-io/chdb/blob/main/refs/clickhouse-formats-settings.md

Illustrates building a SQL query with custom CSV settings in Go. It uses a map to store settings and then formats them into a comma-separated string for the query.

```go
settings := map[string]interface{}{
    "format_csv_delimiter": "|",
    "input_format_csv_skip_first_lines": 1,
    "input_format_csv_trim_whitespaces": 1,
}

// Build query with settings
var settingsStr []string
for k, v := range settings {
    settingsStr = append(settingsStr, fmt.Sprintf("%s=%v", k, v))
}
query := fmt.Sprintf("SELECT * FROM file('data.csv', 'CSV') SETTINGS %s", 
    strings.Join(settingsStr, ", "))
```

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

### Install chDB AI Skill

Source: https://github.com/chdb-io/chdb/blob/main/README.md

Installs the chDB AI Skill for use with AI coding agents. This command fetches and executes an installation script.

```bash
curl -sL https://raw.githubusercontent.com/chdb-io/chdb/main/install_skill.sh | bash
```

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

### SQL Parquet Optimization Example

Source: https://github.com/chdb-io/chdb/blob/main/refs/clickhouse-formats-settings.md

Shows how to apply optimization settings for Parquet files in a SQL query. This includes push-down filters and bloom filters, along with block size configuration.

```sql
-- Parquet with optimization
SELECT * FROM file('data.parquet', 'Parquet')
SETTINGS 
    input_format_parquet_filter_push_down = 1,
    input_format_parquet_bloom_filter_push_down = 1,
    input_format_parquet_max_block_size = 131072;
```

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

### Explain and Profile

Source: https://github.com/chdb-io/chdb/blob/main/refs/chdb-data-science-api.md

Generate an execution plan or profile for the DataStore operations.

```APIDOC
## Explain & Profile

### Description
Provides methods to inspect the execution plan or profile the performance of DataStore operations.

### Methods

- `explain()`: Prints the final SQL statement or execution plan.
- `profile()`: Prints the final SQL statement execution plan and execution time.

### Code Examples

```python
from chdb import DataStore

data_store = DataStore("...") # Assume data_store is initialized

# Print the execution plan
data_store.explain()

# Print the execution plan and execution time
data_store.profile()
```
```

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

### Explain Execution Plan

Source: https://github.com/chdb-io/chdb/blob/main/refs/chdb-data-science-api.md

Demonstrates how to use `ds.explain()` to view the execution plan or the final SQL statement generated by the DataStore operations. This helps in understanding how the query will be executed.

```python
ds['grade'] = ds.when(ds['score'] >= 90, 'A').otherwise('B')
ds.explain()
# Shows: [chDB] Assign column 'grade' = CASE WHEN ...

```

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

### DataStore Quick Reference

Source: https://github.com/chdb-io/chdb/blob/main/docs/FUNCTIONS.md

Demonstrates the three primary ways to access DataStore functions: the accessor pattern (recommended for chaining), direct expression methods, and the explicit F namespace.

```python
from datastore import DataStore, F, Field

ds = DataStore.from_file('data.csv')

# Accessor pattern (recommended for chaining)
ds['name'].str.upper()           # String function
ds['date'].dt.year               # DateTime function
ds['tags'].arr.length            # Array function
ds['data'].json.get_string('name')  # JSON function
ds['link'].url.domain()          # URL function
ds['ip_addr'].ip.to_ipv4()       # IP function
ds['coords'].geo.l2_distance(other)  # Geo function

# Expression methods
ds['value'].abs()                # Math function
ds['price'].sum()                # Aggregate function
ds['value'].cast('Float64')      # Type conversion

# F namespace (explicit)
F.upper(Field('name'))
F.sum(Field('value'))
```

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

### Instantiate DataStore

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/api-reference.md

Construct a DataStore instance using various source types like dictionaries, DataFrames, file paths, or database connections.

```python
DataStore(source=None, table=None, database=":memory:", connection=None, **kwargs)
```

```python
DataStore({'col1': [1, 2], 'col2': ['a', 'b']})
```

```python
DataStore(df)
```

```python
DataStore("file", path="data.parquet")
```

```python
DataStore("mysql", host="host:3306", database="db", table="t", user="u", password="p")
```

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

### Create and Use a Temporary Session

Source: https://github.com/chdb-io/chdb/blob/main/docs/session.md

Demonstrates creating a temporary session for auto-cleanup and executing SQL commands to create databases, tables, and views, then querying the view.

```python
from chdb import session as chs

# Create a temporary session (auto-cleanup)
sess = chs.Session()

# Execute queries with persistent state
sess.query("CREATE DATABASE IF NOT EXISTS db_xxx ENGINE = Atomic")
sess.query("CREATE TABLE IF NOT EXISTS db_xxx.log_table_xxx (x String, y Int) ENGINE = Log;")
sess.query("INSERT INTO db_xxx.log_table_xxx VALUES ('a', 1), ('b', 3), ('c', 2), ('d', 5);")
sess.query("CREATE VIEW db_xxx.view_xxx AS SELECT * FROM db_xxx.log_table_xxx LIMIT 4;")

print("Select from view:")
print(sess.query("SELECT * FROM db_xxx.view_xxx", "Pretty"))

# Session automatically cleaned up when object is destroyed
```

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

### Install and Verify chDB

Source: https://github.com/chdb-io/chdb/blob/main/docs/troubleshooting.md

Use pip to uninstall and reinstall chDB to resolve import errors. Verify the installation by checking the chDB version.

```bash
pip uninstall chdb
pip install chdb
```

```python
import chdb
print(f"chDB version: {chdb.__version__}")
print(f"Engine version: {chdb.engine_version}")
```

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

### Load DataStore from MySQL

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/connectors.md

Connect to a MySQL database using `from_mysql`. Specify host, database, table, user, and password. The port can be included in the host string or passed separately.

```python
DataStore.from_mysql(host, database=None, table=None, user=None, password="", port=None, **kwargs)
```

```python
ds = DataStore.from_mysql(
    host="db.example.com:3306", database="shop",
    table="orders", user="root", password="pass")
```

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

### Verify chDB Installation and Functionality

Source: https://github.com/chdb-io/chdb/blob/main/docs/installation.md

Run this Python code to test basic chDB functionality and check the installed versions of chDB and the ClickHouse engine.

```python
import chdb

# Test basic functionality
result = chdb.query("SELECT 'chDB is working!' as message")
print(result)

# Check version
print(f"chDB version: {chdb.__version__}")
print(f"ClickHouse engine: {chdb.engine_version}")
```

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

### DataStore Quick Start: Loading and Basic Operations

Source: https://github.com/chdb-io/chdb/blob/main/docs/PANDAS_COMPATIBILITY.md

Demonstrates how to load data from a CSV file into a DataStore and perform common pandas transformations like dropping columns, filling missing values, creating new columns, sorting, and selecting the head of the DataFrame.

```python
from datastore import DataStore

ds = DataStore.from_file("data.csv")

# Use any pandas method
df = ds.drop(columns=['unused'])
      .fillna(0)
      .assign(revenue=lambda x: x['price'] * x['quantity'])
      .sort_values('revenue', ascending=False)
      .head(10)
```

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

### Execute Query and Get Column Descriptions

Source: https://github.com/chdb-io/chdb/blob/main/docs/api.md

Demonstrates executing a SELECT query and iterating through the cursor's description to get column names and types.

```pycon
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT id, name FROM users LIMIT 1")
>>> for desc in cursor.description:
...     print(f"Column: {desc[0]}, Type: {desc[1]}")
Column: id, Type: Int32
Column: name, Type: String
```

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

### Install chDB with DataFrame and PyArrow Support

Source: https://github.com/chdb-io/chdb/blob/main/docs/installation.md

Install chDB with optional dependencies for enhanced DataFrame and PyArrow table support, enabling efficient data interchange.

```bash
pip install chdb[pandas,pyarrow]
```

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

### Load DataStore from PostgreSQL

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/connectors.md

Connect to a PostgreSQL database using `from_postgresql`. Specify host, database, table, user, and password. The port can be included in the host string or passed separately.

```python
DataStore.from_postgresql(host, database=None, table=None, user=None, password="", port=None, **kwargs)
```

```python
ds = DataStore.from_postgresql(
    host="pg:5432", database="analytics",
    table="events", user="user", password="pass")
```

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

### Create a cursor and execute queries

Source: https://github.com/chdb-io/chdb/blob/main/docs/api.md

Demonstrates creating a cursor object from a connection and using it to execute SQL commands like CREATE TABLE and INSERT.

```pycon
>>> conn = connect(":memory:")
>>> cursor = conn.cursor()
>>> cursor.execute("CREATE TABLE test (id INT, name String)")
>>> cursor.execute("INSERT INTO test VALUES (1, 'Alice')")
>>> cursor.execute("SELECT * FROM test")
>>> rows = cursor.fetchall()
>>> print(rows)
((1, 'Alice'),)
```

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

### Create Parquet and ClickHouse Data Sources

Source: https://github.com/chdb-io/chdb/blob/main/refs/chdb-data-science-api.md

Demonstrates creating DataStore objects for local Parquet files and remote ClickHouse tables. Ensure the 'data/sales.parquet' file exists for the Parquet example.

```python
pq = DataStore("file", path="data/sales.parquet")
pq.connect()
```

```python
ch_table = DataStore("clickhouse", host="localhost", table="customer_info")
```

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

### Install Libraries and Download Data

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_vs_Pandas_Parquet.ipynb

Installs required Python libraries (pyarrow, pandas, chdb) and downloads Parquet files for taxi data. Ensure you have sufficient disk space and network bandwidth.

```python
!pip install pyarrow pandas
!pip install chdb --pre --upgrade

!mkdir -p taxi
!wget https://github.com/cwida/duckdb-data/releases/download/v1.0/taxi_2019_04.parquet -O taxi/201904.parquet
!wget https://github.com/cwida/duckdb-data/releases/download/v1.0/taxi_2019_05.parquet -O taxi/201905.parquet
!wget https://github.com/cwida/duckdb-data/releases/download/v1.0/taxi_2019_06.parquet -O taxi/201906.parquet
```

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

### Time Series Analysis Example

Source: https://github.com/chdb-io/chdb/blob/main/docs/PANDAS_COMPATIBILITY.md

An example of time series analysis operations including setting and sorting the index, setting frequency, forward filling missing values, calculating a rolling mean, and shifting.

```python
ts_result = (
    ds
    .set_index('date')
    .sort_index()
    .asfreq('D')
    .fillna(method='ffill')
    .rolling(window=7).mean()
    .shift(1))
```

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

### Persistent Storage Setup and Query

Source: https://github.com/chdb-io/chdb/blob/main/docs/quickstart.md

Set up a persistent chDB database file to store data across sessions. Includes creating tables, inserting data, and querying.

```python
# Create a persistent database
conn = chdb.connect("my_database.chdb")
cur = conn.cursor()

# Create and populate table
cur.execute("""
    CREATE TABLE IF NOT EXISTS users (
        id UInt32,
        name String,
        email String
    ) ENGINE = MergeTree() ORDER BY id
")

cur.execute("INSERT INTO users VALUES (1, 'Alice', 'alice@example.com')")
cur.execute("INSERT INTO users VALUES (2, 'Bob', 'bob@example.com')")

# Query the persistent data
cur.execute("SELECT * FROM users ORDER BY id")
for row in cur:
    print(row)

conn.close()
```

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

### Performance Tip: Use LIMIT

Source: https://github.com/chdb-io/chdb/blob/main/docs/PANDAS_MIGRATION_GUIDE.md

Demonstrates using the `limit()` method to retrieve a subset of data, useful for previewing or sampling.

```python
from datastore import DataStore

ds = DataStore.uri("data.parquet")

# Preview data
preview = ds.limit(100).to_df()

```

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

### Manage Multiple Databases and Tables

Source: https://github.com/chdb-io/chdb/blob/main/docs/session.md

Demonstrates creating multiple databases and tables within those databases. Use this to organize your data logically across different domains.

```python
sess = chs.Session("multi_db.chdb")

# Create multiple databases
sess.query("CREATE DATABASE sales ENGINE = Atomic")
sess.query("CREATE DATABASE analytics ENGINE = Atomic")

# Create tables in different databases
sess.query("""
    CREATE TABLE sales.transactions (
        id UInt32,
        customer_id UInt32,
        amount Decimal(10,2),
        timestamp DateTime
    ) ENGINE = MergeTree() ORDER BY timestamp
")

sess.query("""
    CREATE TABLE analytics.daily_summary AS
    SELECT
        toDate(timestamp) as date,
        count(*) as transaction_count,
        sum(amount) as total_amount
    FROM sales.transactions
    GROUP BY date
")
```

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

### Write to S3 with different methods

Source: https://github.com/chdb-io/chdb/blob/main/refs/clickhouse-table-functions.md

Shows how to insert data into S3, including partitioned writes.

```sql
INSERT INTO TABLE FUNCTION s3(
    'https://bucket.s3.amazonaws.com/output.csv',
    'ACCESS_KEY',
    'SECRET_KEY',
    'CSV',
    'a UInt32, b String'
) VALUES (1, 'test');

```

```sql
-- Partitioned write
INSERT INTO TABLE FUNCTION s3(
    'http://bucket.amazonaws.com/my_bucket_{_partition_id}/file.csv',
    'CSV',
    'a UInt32, b UInt32, c UInt32'
) PARTITION BY a VALUES (1, 2, 3), (10, 11, 12);

```

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

### String Length Calculation

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/api-reference.md

Get the length of strings in a column using `.str.len()`.

```python
ds["column"].str.len()
```

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

### Array Properties

Source: https://github.com/chdb-io/chdb/blob/main/docs/FUNCTIONS.md

Properties to get information about arrays, such as their length, size, and whether they are empty.

```APIDOC
## Array Properties (`.arr` accessor)

Access array properties via `ds['column'].arr.<property>`.

### Properties
- `length`: Returns the number of elements in the array.
- `size`: Alias for `length`.
- `empty`: Returns `True` if the array is empty, `False` otherwise.
- `not_empty`: Returns `True` if the array is not empty, `False` otherwise.

### Example Usage
```python
ds['tags'].arr.length
ds['tags'].arr.size
ds['tags'].arr.empty
ds['tags'].arr.not_empty
```
```

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

### Create MovieLens Database and Views

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_movielens_DNN.ipynb

Initializes a chDB session, creates a 'movielens' database, and defines views for movies, ratings, and tags by referencing their respective CSV files. It then prints the first 5 rows of each view in CSV format with headers.

```python
# Create tables for the tables of movieLens dataset
chs = session.Session()
chs.query("CREATE DATABASE IF NOT EXISTS movielens ENGINE = Atomic")
chs.query("USE movielens")
chs.query(
    "CREATE VIEW movies AS SELECT movieId, title, genres FROM file('ml-25m/movies.csv')"
)
chs.query(
    "CREATE VIEW ratings AS SELECT userId, movieId, rating, timestamp FROM file('ml-25m/ratings.csv')"
)
chs.query(
    "CREATE VIEW tags AS SELECT userId, movieId, tag, timestamp FROM file('ml-25m/tags.csv')"
)
print(chs.query("SELECT * FROM movies LIMIT 5", "CSVWithNames"))
print(chs.query("SELECT * FROM ratings LIMIT 5", "CSVWithNames"))
print(chs.query("SELECT * FROM tags LIMIT 5", "CSVWithNames"))
```

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

### Explain and Profile DataStore Operations

Source: https://github.com/chdb-io/chdb/blob/main/refs/chdb-data-science-api.md

Provides methods to explain the execution plan (`explain()`) and profile the execution time (`profile()`) of DataStore operations, aiding in performance analysis.

```python
ds.explain() # Print final SQL statement or execution plan
ds.profile() # Print final SQL statement execution plan and execution time
```

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

### Output Formats

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-sql/references/api-reference.md

Lists the available output formats for query results and provides examples.

```APIDOC
## Output Formats

### Description
Lists the available output formats for query results and provides examples.

| Format | Description | Use case |
|--------|-------------|----------|
| `"CSV"` | Comma-separated (default) | General export |
| `"CSVWithNames"` | CSV with header row | Spreadsheet import |
| `"JSON"` | JSON object with metadata | API responses |
| `"JSONEachRow"` | One JSON object per line | Streaming / NDJSON |
| `"DataFrame"` | pandas DataFrame | Python analysis |
| `"Arrow"` | Apache Arrow bytes | IPC format |
| `"ArrowTable"` | pyarrow.Table | Arrow ecosystem |
| `"Parquet"` | Parquet bytes | File export |
| `"Pretty"` | Formatted table | Terminal display |
| `"PrettyCompact"` | Compact table | Terminal display |
| `"TabSeparated"` | TSV | Tab-delimited export |
| `"Debug"` | Debug info | Troubleshooting |

### Example
```python
import chdb

chdb.query("SELECT 1", "Pretty").show()            # formatted table
df = chdb.query("SELECT * FROM numbers(5)", "DataFrame")  # pandas DataFrame
arrow = chdb.query("SELECT 1", "ArrowTable")        # pyarrow Table
```
```

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

### Get Summary Statistics

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/api-reference.md

Generate descriptive statistics for columns using `.describe()`. This triggers execution.

```python
print(ds.describe())       # → statistics table
```

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

### String Substring Extraction

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/api-reference.md

Extract substrings from a column using `.str.slice()` with start and stop indices.

```python
ds["code"].str.slice(0, 3)
```

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

### Equivalent DataStore Creation from DataFrame

Source: https://github.com/chdb-io/chdb/blob/main/docs/FACTORY_METHODS.md

Demonstrates two equivalent ways to create a DataStore from a pandas DataFrame using factory methods.

```python
ds = DataStore.from_df(df, name='users')
ds = DataStore.from_dataframe(df, name='users')
```

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

### Query chDB Version

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_vs_Pandas_Parquet.ipynb

A simple query to retrieve the version of chDB. No specific setup is required.

```Python
chdb.query("SELECT version()", 'Dataframe')
```

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

### Check NumPy Version

Source: https://github.com/chdb-io/chdb/blob/main/examples/chDB_vector_search.ipynb

Verifies the installed version of the NumPy library. This is useful for ensuring compatibility with other packages.

```python
import numpy as np
print(np.__version__)
```

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

### Integrate Profiling with explain()

Source: https://github.com/chdb-io/chdb/blob/main/docs/PROFILING.md

Combine DataStore's profiling capabilities with the `explain()` method for a comprehensive analysis of query execution plans and performance.

```python
ds = DataStore.from_file("data.csv")

query = (
ds
    .filter(ds.age > 25)
    .add_prefix('emp_')
    .filter(ds.emp_salary > 50000))

# First, understand the execution plan
query.explain(verbose=True)
```

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

### Data Source Creation

Source: https://github.com/chdb-io/chdb/blob/main/refs/chdb-data-science-api.md

Demonstrates how to create data sources from local Parquet files and ClickHouse tables.

```APIDOC
## Data Source Creation

### Description
Create data sources from local Parquet files or ClickHouse tables.

### Code Examples

```python
from chdb import DataStore

# Create local Parquet file data source
parquet_ds = DataStore("file", path="data/sales.parquet")
parquet_ds.connect()

# Create ClickHouse table data source
# Supports ClickHouse connection strings, reference: <https://clickhouse.com/docs/sql-reference/table-functions/remote>
clickhouse_ds = DataStore("clickhouse", host="localhost", table="customer_info")
```
```

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

### Get DataStore Dimensions

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/api-reference.md

Obtain the number of rows and columns using the `.shape` property. This triggers execution.

```python
print(ds.shape)            # → (1000, 3)
```

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

### DataStore.from_file()

Source: https://github.com/chdb-io/chdb/blob/main/docs/FACTORY_METHODS.md

Creates a DataStore instance from local files. The format is automatically detected from the file extension, but can also be specified explicitly. Supports glob patterns for multiple files and allows defining the structure.

```APIDOC
## DataStore.from_file()

### Description
Create DataStore from local files with automatic format detection.

### Method
`@classmethod from_file(cls, path: str, format: str = None, structure: str = None, compression: str = None, **kwargs) -> 'DataStore'`

### Parameters
#### Path Parameters
- **path** (str) - Required - The path to the file or glob pattern.
- **format** (str) - Optional - The format of the file (e.g., 'CSV', 'PARQUET'). If not provided, it's auto-detected.
- **structure** (str) - Optional - Defines the schema of the data (e.g., 'id UInt32, name String').
- **compression** (str) - Optional - The compression type of the file.

### Request Example
```python
from datastore import DataStore

# Auto-detect format from extension
ds = DataStore.from_file("data.parquet")

# Explicit format
ds = DataStore.from_file("data.csv", format="CSV")

# With structure
ds = DataStore.from_file("data.csv", 
                         format="CSV",
                         structure="id UInt32, name String")

# Glob patterns
ds = DataStore.from_file("logs/*.csv", format="CSV")
```
```

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

### Profiler Get Total Time

Source: https://github.com/chdb-io/chdb/blob/main/docs/PROFILING.md

Retrieves the total execution time recorded during the profiling session in seconds.

```APIDOC
## profiler.get_total_time()

### Description
Get total execution time in seconds.

### Method
`profiler.get_total_time()`

### Parameters
None

### Request Example
```python
total = profiler.get_total_time()
print(f"Total time: {total:.3f}s")
```

### Response
- `float`: Total execution time in seconds.
```

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

### Create DataStore from URL (JSONEachRow)

Source: https://github.com/chdb-io/chdb/blob/main/docs/FACTORY_METHODS.md

Initialize a DataStore by fetching data from an HTTP/HTTPS URL. The format must be specified, such as JSONEachRow.

```python
# Basic URL
ds = DataStore.from_url(
    url="https://example.com/data.json",
    format="JSONEachRow"
)
```

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

### Get Profiler Instance

Source: https://github.com/chdb-io/chdb/blob/main/docs/PROFILING.md

Retrieves the current profiler instance. If profiling is disabled, it returns a no-op profiler.

```APIDOC
## get_profiler()

### Description
Get the current profiler instance. Returns a no-op profiler if profiling is disabled.

### Method
`get_profiler()`

### Parameters
None

### Request Example
```python
from datastore import get_profiler
profiler = get_profiler()
```

### Response
- `Profiler`: An instance of the Profiler class.
```

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

### Migrating from Old Style to New Style DataStore Constructor

Source: https://github.com/chdb-io/chdb/blob/main/docs/FACTORY_METHODS.md

Shows the transition from the older DataStore constructor syntax to the recommended factory method syntax for file and S3 sources.

```python
ds = DataStore("file", path="data.csv", format="CSV")
ds = DataStore("s3", url="s3://bucket/data.parquet", format="Parquet", nosign=True)
ds = DataStore("mysql", host="localhost:3306", database="db", table="users", ...)
```

```python
ds = DataStore.from_file("data.csv")  # Format auto-detected
ds = DataStore.from_s3("s3://bucket/data.parquet", nosign=True)  # Format auto-detected
ds = DataStore.from_mysql("localhost:3306", "db", "users", ...)
```

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

### Query with DataFrame Output

Source: https://github.com/chdb-io/chdb/blob/main/docs/quickstart.md

Retrieve query results directly as a pandas DataFrame. Ensure pandas is installed for this functionality.

```python
import chdb

df = chdb.query("SELECT number, number*2 as doubled FROM numbers(5)", "DataFrame")
print(type(df))  # <class 'pandas.core.frame.DataFrame'>
print(df.head())
```

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

### Execute Streaming Query

Source: https://github.com/chdb-io/chdb/blob/main/docs/api.md

Shows how to execute a SQL query and get a streaming result iterator for large datasets.

```python
>>> # Example for send_query (not directly executable without context)
>>> # streaming_result = session.send_query("SELECT * FROM large_table")
>>> # for row in streaming_result:
>>> #     print(row)
```

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

### Cursor Execute and FetchOne

Source: https://github.com/chdb-io/chdb/blob/main/docs/api.md

Demonstrates basic cursor usage: creating a cursor, executing a query, fetching a single result, and closing the cursor for resource cleanup.

```python
>>> cursor = conn.cursor()
>>> cursor.execute("SELECT 1")
>>> result = cursor.fetchone()
>>> cursor.close()  # Cleanup cursor resources
```

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

### Basic Streaming with Context Manager

Source: https://github.com/chdb-io/chdb/blob/main/docs/streaming.md

Use the context manager approach for basic streaming queries with automatic resource cleanup. Ensure the session is initialized before use.

```python
from chdb import session as chs

sess = chs.Session()

# Basic streaming with automatic resource cleanup
rows_cnt = 0
with sess.send_query("SELECT * FROM numbers(200000)", "CSV") as stream_result:
    for chunk in stream_result:
        rows_cnt += chunk.rows_read()

print(f"Processed {rows_cnt} rows")  # 200000
```

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

### Get Row Count

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/api-reference.md

Determine the total number of rows in the DataStore using the `len()` function. This triggers execution.

```python
len(ds)
```

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

### DataStore Quick Start: Mixing SQL and Pandas Operations

Source: https://github.com/chdb-io/chdb/blob/main/docs/PANDAS_COMPATIBILITY.md

Illustrates how to combine SQL-style selection and filtering with pandas-style data manipulation (e.g., assign, query, groupby, agg) within a DataStore workflow.

```python
# Mix SQL and pandas
result = (ds
    .select('*')
    .filter(ds.price > 100)              # SQL-style
    .assign(margin=lambda x: x['profit'] / x['revenue'])  # pandas-style
    .query('margin > 0.2')               # SQL-style
    .groupby('category').agg({'revenue': 'sum'}))  # pandas-style
```

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

### Get Column Data Types

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/api-reference.md

Inspect the data types of each column by accessing the `.dtypes` property. This triggers execution.

```python
print(ds.dtypes)
```

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

### Get Column Names

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/api-reference.md

Retrieve a list of column names in the DataStore using the `.columns` property. This triggers execution.

```python
print(ds.columns)          # → ['name', 'age', 'city']
```

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

### Create and Query Local Parquet Data Source

Source: https://github.com/chdb-io/chdb/blob/main/refs/chdb-data-science-api.md

Demonstrates creating a DataStore from a local Parquet file, retrieving its schema, performing a basic filter operation, and executing the query to convert results to a DataFrame.

```python
pq = DataStore("file", path="data/sales.parquet")
schema = pq.schema
print(schema)
filtered_data = pq.filter(col("revenue") > 1000)
result_ds = filtered_data.execute()
result_ds.to_df() # no cost
```

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

### Load DataStore from MongoDB

Source: https://github.com/chdb-io/chdb/blob/main/agent/skills/chdb-datastore/references/connectors.md

Connect to a MongoDB instance using `from_mongodb`. Specify host, database, collection, user, and password.

```python
DataStore.from_mongodb(host, database, collection, user, password="", **kwargs)
```

```python
ds = DataStore.from_mongodb(
    host="mongo:27017", database="app",
    collection="users", user="user", password="pass")
```

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

### Profiler Get Steps

Source: https://github.com/chdb-io/chdb/blob/main/docs/PROFILING.md

Retrieves a list of all recorded profiling steps, each containing details like name and duration.

```APIDOC
## profiler.get_steps()

### Description
Get list of all recorded profiling steps.

### Method
`profiler.get_steps()`

### Parameters
None

### Request Example
```python
steps = profiler.get_steps()
for step in steps:
    print(f"{step.name}: {step.duration:.3f}s")
```

### Response
- `list`: A list of profiling step objects, each with `name` (string) and `duration` (float) attributes.
```

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

### Use Connection as a context manager

Source: https://github.com/chdb-io/chdb/blob/main/docs/api.md

Demonstrates using the Connection object as a context manager to execute a query and fetch the version. This simplifies resource management.

```python
>>> # Context manager usage
>>> with Connection() as cur:
...     cur.execute("SELECT version()")
...     version = cur.fetchone()
```