An extension package of 🤗 Datasets that provides support for executing arbitrary SQL queries on HF datasets

Last update: Dec 15, 2022

Related tags

Overview

datasets_sql

A 🤗 Datasets extension package that provides support for executing arbitrary SQL queries on HF datasets. It uses DuckDB as a SQL engine and follows its query syntax.

Installation

pip install datasets_sql

Quick Start

from datasets import load_dataset, Dataset
from datasets_sql import query

imdb_dset = load_dataset("imdb", split="train")

# Remove the rows where the `text` field has less than 1000 characters
imdb_query_dset1 = query("SELECT text FROM imdb_dset WHERE length(text) > 1000")

# Count the number of rows per label
imdb_query_dset2 = query("SELECT label, COUNT(*) as num_rows FROM imdb_dset GROUP BY label")

# Remove duplicated rows
imdb_query_dset3 = query("SELECT DISTINCT text FROM imdb_dset")

# Get the average length of the `text` field
imdb_query_dset4 = query("SELECT AVG(length(text)) as avg_text_length FROM imdb_dset")

order_customer_dset = Dataset.from_dict({
    "order_id": [10001, 10002, 10003],
    "customer_id": [3, 1, 2],
})

customer_dset = Dataset.from_dict({
    "customer_id": [1, 2, 3],
    "name": ["John", "Jane", "Mary"],
})

# Join two tables
join_query_dset = query(
    "SELECT order_id, name FROM order_customer_dset INNER JOIN customer_dset ON order_customer_dset.customer_id = customer_dset.customer_id"
)

You might also like...

SQL for Humans™

Records: SQL for Humans™ Records is a very simple, but powerful, library for making raw SQL queries to most relational databases. Just write SQL. No b

6.9k Jan 7, 2023

SQL for Humans™

Records: SQL for Humans™ Records is a very simple, but powerful, library for making raw SQL queries to most relational databases. Just write SQL. No b

6.9k Jan 3, 2023

Anomaly detection on SQL data warehouses and databases

With CueObserve, you can run anomaly detection on data in your SQL data warehouses and databases. Getting Started Install via Docker docker run -p 300

171 Dec 18, 2022

Simple DDL Parser to parse SQL (HQL, TSQL, AWS Redshift, Snowflake and other dialects) ddl files to json/python dict with full information about columns: types, defaults, primary keys, etc.

Simple DDL Parser Build with ply (lex & yacc in python). A lot of samples in 'tests/. Is it Stable? Yes, library already has about 5000+ usage per day

95 Jan 5, 2023

PyRemoteSQL is a python SQL client that allows you to connect to your remote server with phpMyAdmin installed.

PyRemoteSQL Python MySQL remote client Basically this is a python SQL client that allows you to connect to your remote server with phpMyAdmin installe

3 Nov 4, 2022

edaSQL is a library to link SQL to Exploratory Data Analysis and further more in the Data Engineering.

edaSQL is a python library to bridge the SQL with Exploratory Data Analysis where you can connect to the Database and insert the queries. The query results can be passed to the EDA tool which can give greater insights to the user.

8 Dec 12, 2022

Python script to clone SQL dashboard from one workspace to another

Databricks dashboard clone Unofficial project to allow Databricks SQL dashboard copy from one workspace to another. Resource clone Setup: Create a fil

12 Jan 1, 2023

Some scripts for microsoft SQL server in old version.

MSSQL_Stuff Some scripts for microsoft SQL server which is in old version. Table of content Overview Usage References Overview These script works when

5 Dec 29, 2022

Making it easy to query APIs via SQL

Shillelagh Shillelagh (ʃɪˈleɪlɪ) is an implementation of the Python DB API 2.0 based on SQLite (using the APSW library): from shillelagh.backends.apsw

207 Dec 30, 2022

Comments

How to use query function if dataset is a class attribute
Awesome library!

This is probably a generic duckdb question but figured I'd ask here first. If I store a reference to a dataset in a class attribute, how do I get query to find my dataset?

Repro:

class DatasetQuery: def __init__(self, dataset_name, split="train"): ds = datasets.load_dataset(dataset_name, split="train") self.dataset = ds def query(self, query_str): return query(query_str) dq = DatasetQuery("huggingnft/boredapeyachtclub") dq.query("select * from ?? limit 10;")

What do I put in the from clause? I tried ds and self.dataset but neither work. I get ValueError: The datasetdsnot found in the namespace.
opened by freddyaboulton 4
The readme demos are broken
I tried running an example from the repo but the code is broken:

imdb_dset = load_dataset("imdb", split="train") dataset = query( "SELECT text FROM imdb_dset" )

results in AttributeError: 'duckdb.DuckDBPyConnection' object has no attribute 'fetch_arrow_chunk'

I am using datasets_sql version 0.1.1 and datasets version 2.5.2
opened by mo6zes 1
Be able to stream the results of query
I'd like to query a large remote dataset (on the hub or elsewhere) and then stream the results of the query so that I don't have to download the entire dataset to my machine.

For example, you could query diffusiondb for images generated with prompts containing the word "ceo" to visualize biases:

SELECT * from poloclub/diffusiondb WHERE contains('prompt', 'ceo')

This combined with https://github.com/huggingface/datasets-server/issues/398 would open the door for a lot of cool applications of gradio + datasets where users could interactively explore datasets that don't fit on their machines and create spaces without having to download/store large datasets.

I see that data can be streamed from duckdb with pyarrow: https://duckdb.org/2021/12/03/duck-arrow.html . I wonder if this can be leveraged for this use case.
opened by freddyaboulton 5

Releases(0.3.0)

0.3.0(Nov 28, 2022)

Full Changelog: https://github.com/mariosasko/datasets_sql/compare/0.2.0...0.3.0
Source code(tar.gz)
Source code(zip)
0.2.0(Nov 6, 2022)

Full Changelog: https://github.com/mariosasko/datasets_sql/compare/0.1.1...0.2.0
Source code(tar.gz)
Source code(zip)
0.1.1(Mar 15, 2022)

Full Changelog: https://github.com/mariosasko/datasets_sql/commits/0.1.1
Source code(tar.gz)
Source code(zip)

Owner

Mario Šaško

SWE at Hugging Face

GitHub Repository

MongoDB data stream pipeline tools by YouGov (adopted from MongoDB)

mongo-connector The mongo-connector project originated as a MongoDB mongo-labs project and is now community-maintained under the custody of YouGov, Pl

1.9k Jan 04, 2023

DBMS Mini-project: Recruitment Management System

# Hire-ME DBMS Mini-project: Recruitment Management System. 💫 ✨ Features Python + MYSQL using mysql.connector library Recruiter and Client Panel Beau

35 Dec 23, 2022

SQL for Humans™

Records: SQL for Humans™ Records is a very simple, but powerful, library for making raw SQL queries to most relational databases. Just write SQL. No b

6.9k Jan 07, 2023

The Database Toolkit for Python

SQLAlchemy The Python SQL Toolkit and Object Relational Mapper Introduction SQLAlchemy is the Python SQL toolkit and Object Relational Mapper that giv

6.5k Jan 01, 2023

Example Python codes that works with MySQL and Excel files (.xlsx)

Python x MySQL x Excel by Zinglecode Example Python codes that do the processes between MySQL database and Excel spreadsheet files. YouTube videos MyS

1 Feb 07, 2022

dbd is a database prototyping tool that enables data analysts and engineers to quickly load and transform data in SQL databases.

dbd: database prototyping tool dbd is a database prototyping tool that enables data analysts and engineers to quickly load and transform data in SQL d

47 Dec 07, 2022

An extension package of 🤗 Datasets that provides support for executing arbitrary SQL queries on HF datasets

Related tags

Overview

datasets_sql

Installation

Quick Start

You might also like...

SQL for Humans™

SQL for Humans™

Anomaly detection on SQL data warehouses and databases

Simple DDL Parser to parse SQL (HQL, TSQL, AWS Redshift, Snowflake and other dialects) ddl files to json/python dict with full information about columns: types, defaults, primary keys, etc.

PyRemoteSQL is a python SQL client that allows you to connect to your remote server with phpMyAdmin installed.

edaSQL is a library to link SQL to Exploratory Data Analysis and further more in the Data Engineering.

Python script to clone SQL dashboard from one workspace to another

Some scripts for microsoft SQL server in old version.

Making it easy to query APIs via SQL

Comments

How to use query function if dataset is a class attribute

The readme demos are broken

Be able to stream the results of query

Releases(0.3.0)

0.3.0(Nov 28, 2022)

0.2.0(Nov 6, 2022)

0.1.1(Mar 15, 2022)

Owner

Mario Šaško

MongoDB data stream pipeline tools by YouGov (adopted from MongoDB)

DBMS Mini-project: Recruitment Management System

SQL for Humans™

The Database Toolkit for Python

Example Python codes that works with MySQL and Excel files (.xlsx)

dbd is a database prototyping tool that enables data analysts and engineers to quickly load and transform data in SQL databases.

SAP HANA Connector in pure Python

edaSQL is a library to link SQL to Exploratory Data Analysis and further more in the Data Engineering.

ClickHouse Python Driver with native interface support

Py2neo is a comprehensive toolkit for working with Neo4j from within Python applications or from the command line.

Pystackql - Python wrapper for StackQL

A Python wheel containing PostgreSQL

A Relational Database Management System for a miniature version of Twitter written in MySQL with CLI in python.

PyMongo - the Python driver for MongoDB

Use SQL query in a jupyter notebook!

A simple Python tool to transfer data from MySQL to SQLite 3.

AWS SDK for Python

Dinamopy is a python helper library for dynamodb

Little wrapper around asyncpg for specific experience.

Pandas Google BigQuery