DEV Community: Chih-Yu Yeh

Querying Your Data Easily and Smartly through Hugging Face

Chih-Yu Yeh — Tue, 29 Aug 2023 09:48:47 +0000

TLDR: VulcanSQL, a free and open-source data API framework built specifically for data applications, empowers data professionals to generate and distribute data APIs quickly and effortlessly. It takes your SQL templates and transforms them into data APIs, with no backend expertise necessary.

Preface

Normally, in order to retrieve the data we need from a data source, we have to write SQL statements. However, this process could be time-consuming especially when the data consumers have different requirements in short time. Now, to make this process easier and more flexible to data consumers, VulcanSQL has integrated HuggingFace inference capabilities. This allows us to reduce the need for changing SQL templates by simply allowing data consumers to ask questions and getting the results they need.

VulcanSQL HuggingFace Filters

VulcanSQL leverages the Hugging Face Inference feature through the VulcanSQL Filters statement.

What is Hugging Face

Hugging Face is an AI community that builds tools to enable users to build, train, and deploy machine learning models. Hugging Face makes it easy to share tools, models, model weights, and datasets among other practitioners through its toolkit.

Hugging Face provides the Inference API feature that allows users to run pre-trained AI models for various natural language processing (NLP) tasks, making it easier to integrate powerful language models into applications and services.

Table Question Answering Task Filter

"Table Question Answering" is one of the NLP (Natural Language Processing) tasks provided by Hugging Face. Table Question Answering involves answering a question about the information in a given table. It allows for simulating SQL execution by inputting a table through its model.

VulcanSQL currently integrates the table question answering feature by creating the filter named huggingface_table_question_answering and allows you to apply functions to variables using the pipe operator (|).

Sample 1 - send the data from the variable set tag:

You could give the dataset with the set tag and give the question with the query field:

{% set data = [
  {
    "repository": "vulcan-sql",
    "topic": ["analytics", "data-lake", "data-warehouse", "api-builder"],
    "description":"Create and share Data APIs fast! Data API framework for DuckDB, ClickHouse, Snowflake, BigQuery, PostgreSQL"
  },
  {
    "repository": "accio",
    "topic": ["data-analytics", "data-lake", "data-warehouse", "bussiness-intelligence"],
    "description": "Query Your Data Warehouse Like Exploring One Big View."
  },
  {
    "repository": "hello-world",
    "topic": [],
    "description": "Sample repository for testing"
  }
] %}

-- The source data for "huggingface_table_question_answering" needs to be an array of objects.
SELECT {{ data | huggingface_table_question_answering(query="How many repositories related to data-lake topic?") }} as result

Here is a response returned by huggingface_table_question_answering:

[
  {
    "result": "{\"answer\":\"COUNT > vulcan-sql, accio\",\"coordinates\":[[0,0],[1,0]],\"cells\":[\"vulcan-sql\",\"accio\"],\"aggregator\":\"COUNT\"}"
  }
]

The result will be converted to a JSON string from huggingface_table_question_answering. You could decompress the JSON string and use the result by yourself.

Sample 2 - send the data from the req tag:

You could also use the req tag to keep the query result from the previous SQL condition and save it to a variable named repositories. Then you can use .value() to get the data result and pass it to huggingface_table_question_answering with the pipe operator |.

{% req repositories %}
 SELECT * FROM read_csv_auto('Top200StaredRepositories.csv')
{% endreq %}

{% set question = context.params.question %}

SELECT {{ repositories.value() | huggingface_table_question_answering(query=question, wait_for_model=true) }} as result

You may see we also pass the value true to the wait_for_model field, which means waiting for the HuggingFace table question answering to load the pre-trained model; otherwise it may fail due to the model not being loaded completely. For more information, please see the VulcanSQL's Hugging Face Table Question Answering Filter Extension Documentation.

Now we could request API with different questions by the parameter question and get different results!

Scenario 1 - We asked Find the repository has the most stars?, and the Hugging Face model told us that freeCodeCamp has the most stars.

Scenario 2 - We asked How many repositories use Python language? Give repository name, and the Hugging Face model told us awesome-python, httpie and thefuck are using Python language.

As you see, after using the HuggingFace Table Answering Filter, the benefit is:

Don't need to change the SQL template file.
Don't need to re-build the SQL template file.
Don't need to create another SQL template file to satisfy two query scenarios.

Notes: The quality of response depends on the model used in the HuggingFace Table Answering Filter and how we ask the questions to the model.

However, VulcanSQL not only provides the Table Question Answering Filter feature but also publishes a popular Text Generation Filter for the Hugging Face Filters Extension.

It means you could use the popular models Meta Llama2! 🥳

Text Generation Task Filter

"Text Generation" is another NLP (Natural Language Processing) task provided by Hugging Face. Text generation means producing new text. These models can, for example, fill in incomplete text or paraphrase, or even answer your question according based on your input context.

VulcanSQL also integrates the text generation feature by creating the filter named huggingface_text_generation and allows you to apply functions to variables using the pipe operator (|).

Besides, Hugging Face provides the popular Meta Llama2 models, a collection of pre-trained and fine-tuned generative text models ranging in scale from 7 billion to 70 billion parameters.

In the next sample, we are going to demonstrate how VulcanSQL uses the huggingface_text_generation filter with the Llama2 model meta-llama/Llama-2-13b-chat-hf to answer your question.

Notes: If you would like to use the Meta Llama2 model, you have at least two options to choose from:

Subscribe to the Hugging Face Pro Account.
Use Hugging Face Inference Endpoints.

For more information, please see VulcanSQL's Text Generation Filter Extension Documentation.

Sample - send the data with the req tag:

The sample uses the HuggingFace access token of the Pro Account to get
the result by using the meta-llama/Llama-2-13b-chat-hf model.

-- Using the `meta-llama/Llama-2-13b-chat-hf` model, data must have less than 4096 tokens, so need to limit data row and column for universities.
{% req universities %}
 SELECT rank, institution, "location code", "location" FROM read_csv_auto('2023-QS-World-University-Rankings.csv') LIMIT 100
{% endreq %}

{% set question = context.params.question %}

SELECT {{ universities.value() | huggingface_text_generation(query=question, model="meta-llama/Llama-2-13b-chat-hf", wait_for_model=true) }} as result

Scenario 1 - We asked Which university is the top-ranked university?, and the model gave us the top-ranked university.

Scenario 2 - We asked Which university located in the UK is ranked at the top of the list?, and the model gave us the top-ranked university that is located in the UK.

Wow! It's really amazing that the HuggingFace Text Generation Filter can answer your question based on the given dataset!

Conclusion

With more and more great machine learning models coming out, it's great that we can utilize their power to make our daily work easier! We hope this blog post can give you a glimpse on how VulcanSQL can be involved in this revolutionary event in human history!

Imaging a world that you can deliver APIs that users only need to query questions they have, the model handles SQL logic for you, and VulcanSQL takes care of the data privacy and API things. It sounds exciting, isn't it?

In the near future, we'll publish detailed step-by-step guides to help you write your own AI-enabled filter extensions! Stay tuned!

Make a Admin Panel in Appsmith

Chih-Yu Yeh — Tue, 18 Jul 2023 02:00:54 +0000

Introduction

In this demo, we'll showcase how VulcanSQL and Appsmith make it effortless to create a Data API and build an application. Appsmith is a powerful open-source developer tool that simplifies the creation of internal tools like dashboards and database interfaces. With drag-and-drop functionality, Appsmith enables fast and customizable UI design. By combining VulcanSQL's Data API with Appsmith, you can quickly develop and deploy user-friendly applications.

The Difficulties

Managing access and preventing information leakage

Table sharing should be managed in a secure manner to limit access to all data and prevent the spreading of sensitive information. It is crucial to ensure the confidentiality, integrity, and availability of data throughout its lifecycle.

Data lake or data warehouse not suitable for applications

Data lakes and data warehouses may not be ideal for applications that require fast and frequent data access. Users today expect quicker and more frequent access to data for real-time analytics and data-driven decision-making.

Hard to control cost

One of the challenges with frequent and fast data access is the increased cost associated with it. Processing large volumes of data and providing real-time access can lead to higher infrastructure costs and resource utilization. Organizations need to carefully optimize their data access strategies to balance the need for speed with cost efficiency.

Why VulcanSQL

Data access restrictions

With VulcanSQL's dynamic parameter feature, users have precise control over the data they access. This enhances data privacy and reduces data transfer, improving efficiency and security. Moreover, VulcanSQL's data privacy handling ensures the protection of sensitive information through encryption and access control.

Enhance performance with caching mechanism

To address the challenges of low-latency, VulcanSQL implements a caching mechanism using DuckDB. This caching layer allows VulcanSQL to synchronize data from DuckDB and serve it to users without the need to repeatedly connect to the data source.

Optimize cost with caching layer

By leveraging the DuckDB caching layer, VulcanSQL significantly reduces the need to fetch data from the data warehouse for most queries. This results in cost savings as it minimizes the resources consumed by querying the data warehouse. Instead, VulcanSQL can retrieve the data directly from the DuckDB cache, which offers faster access and eliminates the need for frequent queries to the data warehouse. This caching mechanism improves performance and helps optimize costs in data retrieval and processing.

Prerequisites

To successfully finish this guide, you'll need:

Install VulcanSQL (Installation)
Appsmith account (Register Appsmith Cloud)

Demo: VulcanSQL + Appsmith

Set up your VulcanSQL starter project

First, you need to follow the instruction to create an init template: https://vulcansql.com/docs/develop/init

Here's what it will look like:

In this case, we will use food_prices_2020.csv to practice.

Write your SQL query

With VulcanSQL's filtering feature, you have the ability to selectively expose specific data to your application. In our example, we have a dataset called food_prices_2020.csv, but we only want to present the fruit prices. To achieve this, we can dynamically add a parameter called filter to specify the desired data filtering.

In food.yaml, we define the parameter we need to pass in the request:

urlPath: /foodrequest:  - fieldName: filter    fieldIn: query    description: filter Fruit or Vegetablesample:  profile: duckdbprofiles:  - duckdb

In food.sql, we can use the if/endif syntax to query the data conditionally.

SELECT * FROM read_csv_auto('data/food_prices_2020.csv'){% if context.params.filter %}    WHERE type = {{context.params.filter}}{% endif %}

Adding parameters to our data operations provides a quick and semantic way to control and filter the accuracy of the data. By specifying parameters, we can precisely retrieve the desired information and ensure that the results are tailored to our specific requirements.

For those datasource on cloud:

In low-latency scenarios, VulcanSQL offers a caching mechanism for datasources stored in data warehouses or data lakes. By caching datasets, the frequency of retrieving data from the original source is reduced, resulting in improved performance. To learn more about VulcanSQL's caching functionality and its usage, refer to the documentation on Caching Datasets.

It can just as simple to use cache/endcache syntax:

{% cache %}SELECT * FROM cached_food{% if context.params.filter %}    WHERE type = {{context.params.filter}}{% endif %}{% endcache %}

Set up Appsmith Application

Now we start to set up Appsmith.

After creating an Appsmith account, it will directly help create and navigate to My first application.

We add a select Widget to canvas and name it foodType to prepare the parameter for REST API in next step. In the Options section, we add the value Fruit and Vegetable for filtering the data and set default selected value to Fruit. Name the select label to Food Type.

Then, we select + New datasource on the sidebar in tab Explorer. Appsmith provides datasources for developers. In this case, we choose REST API for using VulcanSQL Data API as datasource.

After navigate to the Queries/JS section, we edit the name to getFruits. Add the API that was created in VulcanSQL previously. Include the dynamic parameter {{foodType.selectedOptionValue}} in the filter key value. This will enable the API to interact with the food type selection.

Back to the select, we need to add the event to interact with the API's filtering parameter. Scroll down to the Events section and add an event to onOptionChange, choose Execute a query > getFruits.

After setting up the datasource, we can drag a Table to canvas and select getFruits in the table data on the right sidebar. As you complete, you can click the Deploy button on the right top of the screen to deploy the application and test it in the view mode.

Here's the deployed result.

Conclusion

That's it! This concludes our simple example: Vulcan+Appsmith Demo.

To ensure a fast and seamless experience, we invite you to try out our VulcanSQL Demo template on CodeSandbox. By using VulcanSQL and Appsmith, you can fully experience their powerful capabilities. Enjoy the benefits and efficiency that VulcanSQL and Appsmith bring to your data operations.

We have a Discord community and welcome to ask us anything or give us feedback! Also, we definitely welcome you to share interesting projects you've done using VulcanSQL in #showcase channel!

Finally, if VulcanSQL resonates with you, please consider starring us on GitHub!

What is your perspective on this "Data API Framework"?

Chih-Yu Yeh — Wed, 12 Jul 2023 12:30:45 +0000

Hey, friends

If there is a Data API Framework with these functionalities listed below, what do you think about this tool(or what would you do with this tool)?

You only need to write SQL + Jinja(a template language)and the framework generates APIs and API documentation(based on Open API)for you.
It has API catalog that helps non-tech user to explore your APIs and some simple guides to help him/her connect with other applications, such as Google Spreadsheet, Retool, etc.
It supports several databases such as PostgreSQL, DuckDB, ClickHouse, BigQuery and Snowflake. You can also write your own connector.
It has some API best practices built-in such as authorization, authentication, error handling, rate-limiting, CORS, pagination, etc.
It utilizes DuckDB as a caching layer to improve query performance.
It has data privacy mechanisms such as data masking, row/column level security, etc.
It supports several deployment mechanisms such as node.js and Docker, etc.

Disclaimer: I am one of the members of the open source project VulcanSQL