#
Perform SQL operations

Performing SQL operations with DuckDB opens up a world of possibilities for querying datasets efficiently. Let’s dive into some examples showcasing the power of DuckDB functions.

For our demonstration, we’ll explore a fascinating dataset. The MMLU dataset is a multitask test containing multiple-choice questions spanning various knowledge domains.

To preview the dataset, let’s select a sample of 3 rows:

    FROM 'hf://datasets/cais/mmlu/all/test-*.parquet' USING SAMPLE 3;

    ┌──────────────────────┬──────────────────────┬──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬────────┐
    │       question       │       subject        │                                                                         choices                                                                          │ answer │
    │       varchar        │       varchar        │                                                                        varchar[]                                                                         │ int64  │
    ├──────────────────────┼──────────────────────┼──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼────────┤
    │ The model of light…  │ conceptual_physics   │ [wave model, particle model, Both of these, Neither of these]                                                                                            │      1 │
    │ A person who is lo…  │ professional_psych…  │ [his/her life scripts., his/her own feelings, attitudes, and beliefs., the emotional reactions and behaviors of the people he/she is interacting with.…  │      1 │
    │ The thermic effect…  │ nutrition            │ [is substantially higher for carbohydrate than for protein, is accompanied by a slight decrease in body core temperature., is partly related to sympat…  │      2 │
    └──────────────────────┴──────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────┘

This command retrieves a random sample of 3 rows from the dataset for us to examine.

Let’s start by examining the schema of our dataset. The following table outlines the structure of our dataset:

    DESCRIBE FROM 'hf://datasets/cais/mmlu/all/test-*.parquet' USING SAMPLE 3;
    ┌─────────────┬─────────────┬─────────┬─────────┬─────────┬─────────┐
    │ column_name │ column_type │  null   │   key   │ default │  extra  │
    │   varchar   │   varchar   │ varchar │ varchar │ varchar │ varchar │
    ├─────────────┼─────────────┼─────────┼─────────┼─────────┼─────────┤
    │ question    │ VARCHAR     │ YES     │         │         │         │
    │ subject     │ VARCHAR     │ YES     │         │         │         │
    │ choices     │ VARCHAR[]   │ YES     │         │         │         │
    │ answer      │ BIGINT      │ YES     │         │         │         │
    └─────────────┴─────────────┴─────────┴─────────┴─────────┴─────────┘

Next, let’s analyze if there are any duplicated records in our dataset:

    SELECT   *,
             COUNT(*) AS counts
    FROM     'hf://datasets/cais/mmlu/all/test-*.parquet'
    GROUP BY ALL
    HAVING   counts > 2;

    ┌──────────┬─────────┬───────────┬────────┬────────┐
    │ question │ subject │  choices  │ answer │ counts │
    │ varchar  │ varchar │ varchar[] │ int64  │ int64  │
    ├──────────┴─────────┴───────────┴────────┴────────┤
    │                      0 rows                      │
    └──────────────────────────────────────────────────┘

Fortunately, our dataset doesn’t contain any duplicate records.

Let’s see the proportion of questions based on the subject in a bar representation:

    SELECT
        subject,
        COUNT(*) AS counts,
        BAR(COUNT(*), 0, (SELECT COUNT(*) FROM 'hf://datasets/cais/mmlu/all/test-*.parquet')) AS percentage
    FROM
        'hf://datasets/cais/mmlu/all/test-*.parquet'
    GROUP BY
        subject
    ORDER BY
        counts DESC;

    ┌──────────────────────────────┬────────┬────────────────────────────────────────────────────────────────────────────────┐
    │           subject            │ counts │                                   percentage                                   │
    │           varchar            │ int64  │                                    varchar                                     │
    ├──────────────────────────────┼────────┼────────────────────────────────────────────────────────────────────────────────┤
    │ professional_law             │   1534 │ ████████▋                                                                      │
    │ moral_scenarios              │    895 │ █████                                                                          │
    │ miscellaneous                │    783 │ ████▍                                                                          │
    │ professional_psychology      │    612 │ ███▍                                                                           │
    │ high_school_psychology       │    545 │ ███                                                                            │
    │ high_school_macroeconomics   │    390 │ ██▏                                                                            │
    │ elementary_mathematics       │    378 │ ██▏                                                                            │
    │ moral_disputes               │    346 │ █▉                                                                             │
    ├──────────────────────────────┴────────┴────────────────────────────────────────────────────────────────────────────────┤
    │ 57 rows (8 shown)                                                                                           3 columns  │
    └────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

Now, let’s prepare a subset of the dataset containing questions related to nutrition and create a mapping of questions to correct answers. Notice that we have the column choices from which we can get the correct answer using the answer column as an index.

    SELECT *
    FROM   'hf://datasets/cais/mmlu/all/test-*.parquet'
    WHERE  subject = 'nutrition' LIMIT 3;

    ┌──────────────────────┬───────────┬─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬────────┐
    │       question       │  subject  │                                                                               choices                                                                               │ answer │
    │       varchar        │  varchar  │                                                                              varchar[]                                                                              │ int64  │
    ├──────────────────────┼───────────┼─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼────────┤
    │ Which foods tend t…  │ nutrition │ [Meat, Confectionary, Fruits and vegetables, Potatoes]                                                                                                              │      2 │
    │ In which one of th…  │ nutrition │ [If the incidence rate of the disease falls., If survival time with the disease increases., If recovery of the disease is faster., If the population in which the…  │      1 │
    │ Which of the follo…  │ nutrition │ [The flavonoid class comprises flavonoids and isoflavonoids., The digestibility and bioavailability of isoflavones in soya food products are not changed by proce…  │      0 │
    └──────────────────────┴───────────┴─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴────────┘

    SELECT question,
           choices[answer] AS correct_answer
    FROM   'hf://datasets/cais/mmlu/all/test-*.parquet'
    WHERE  subject = 'nutrition' LIMIT 3;

    ┌─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─────────────────────────────────────────────┐
    │                                                              question                                                               │               correct_answer                │
    │                                                               varchar                                                               │                   varchar                   │
    ├─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────┤
    │ Which foods tend to be consumed in lower quantities in Wales and Scotland (as of 2020)?\n                                           │ Confectionary                               │
    │ In which one of the following circumstances will the prevalence of a disease in the population increase, all else being constant?\n │ If the incidence rate of the disease falls. │
    │ Which of the following statements is correct?\n                                                                                     │                                             │
    └─────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────┘

To ensure data cleanliness, let’s remove any newline characters at the end of the questions and filter out any empty answers:

    SELECT regexp_replace(question, '\n', '') AS question,
           choices[answer] AS correct_answer
    FROM   'hf://datasets/cais/mmlu/all/test-*.parquet'
    WHERE  subject = 'nutrition' AND LENGTH(correct_answer) > 0 LIMIT 3;

    ┌───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┬─────────────────────────────────────────────┐
    │                                                             question                                                              │               correct_answer                │
    │                                                              varchar                                                              │                   varchar                   │
    ├───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┼─────────────────────────────────────────────┤
    │ Which foods tend to be consumed in lower quantities in Wales and Scotland (as of 2020)?                                           │ Confectionary                               │
    │ In which one of the following circumstances will the prevalence of a disease in the population increase, all else being constant? │ If the incidence rate of the disease falls. │
    │ Which vitamin is a major lipid-soluble antioxidant in cell membranes?                                                             │ Vitamin D                                   │
    └───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────┘

Finally, lets highlight some of the DuckDB functions used in this section:

- `DESCRIBE`, returns the table schema.
- `USING SAMPLE`, samples are used to randomly select a subset of a dataset.
- `BAR`, draws a band whose width is proportional to (x - min) and equal to width characters when x = max. Width defaults to 80.
- `string[begin:end]`, extracts a string using slice conventions. Missing begin or end arguments are interpreted as the beginning or end of the list respectively. Negative values are accepted.
- `regexp_replace`, if the string contains the regexp pattern, replaces the matching part with replacement.
- `LENGTH`, gets the number of characters in the string.

> There are plenty of useful functions available in DuckDB’s SQL functions overview. The best part is that you can use them directly on Hugging Face datasets.

Update on GitHub