DuckDB SQL query to datasets.Dataset

duckdb_query_to_datasets.py

import duckdb
from datasets import Dataset

# Create DuckDB connection
con = duckdb.connect()
con.execute("INSTALL httpfs;")
con.execute("LOAD httpfs;")

# Query the dataset
query = """
SELECT
  id,
  ARRAY_TRANSFORM(
    conversations,
      x -> JSON({
        'role': CASE 
        WHEN LOWER(x['from']) = 'human' THEN 'user'
        ELSE x['from']
        END,
      'content': x['value']
    })
  ) AS messages
FROM 'hf://datasets/NousResearch/hermes-function-calling-v1/func-calling-singleturn.json'
LIMIT 10;
"""

# Execute the query
result = con.execute(query)

# Transform to Arrow and then to Dataset
table = result.arrow()
dataset = Dataset(table)

# Dataset({
#     features: ['id', 'messages'],
#     num_rows: 1
# })