markhneedham/blog huggingface dataset:
ClickHouse: Nested type Array(String) cannot be inside Nullable type (ILLEGAL_TYPE_OF_ARGUMENT)
Date: 2023-11-03 00:44:37
In this post, we'll learn how to process a tab-separated value inside a CSV file with ClickHouse.

:icons: font

I've been working with some data that's in CSV format but has tab-separated values in some columns.
In this blog post, we're going to learn how to process that data in ClickHouse.

The CSV file that we're working with looks like this:

.data.csv
[format="csv", options="header"]
|===
include::content/2023/11/03/data.csv[]
|===

We'll launch ClickHouse Local (`clickhouse local`) and then run the following:

[source, sql]
----
FROM file('data.csv', CSVWithNames) 
SELECT *;
----

.Output
[source, text]
----
┌─value─────┐
│ foo   bar │
└───────────┘
----

Let's try to split the `value` field on tab using the `splitByString` function:

[source, sql]
----
FROM file('data.csv', CSVWithNames) 
SELECT splitByString('\t', value);
----

.Output
[source, text]
----
Received exception:
Code: 43. DB::Exception: Nested type Array(String) cannot be inside Nullable type: While processing splitByString('\t', value). (ILLEGAL_TYPE_OF_ARGUMENT)
----

The problem we've run into here is that ClickHouse's CSV reader assumes that fields are nullable since it's possible that there could be nulls.
We can disable the `schema_inference_make_columns_nullable` setting to tell it to not treat fields as nullable.

[source, sql]
----
FROM file('data.csv', CSVWithNames)
SELECT splitByString('\t', value)
SETTINGS schema_inference_make_columns_nullable = 0;
----

.Output
[source, text]
----
┌─splitByString('\t', value)─┐
│ ['foo','bar']              │
└────────────────────────────┘
----

Alternatively, we can use the `assumeNotNull` function if we want it to keep all other fields nullable:

[source, sql]
----
FROM file('data.csv', CSVWithNames)
SELECT splitByString('\t', assumeNotNull(value));
----

.Output
[source, text]
----
┌─splitByString('\t', assumeNotNull(value))─┐
│ ['foo','bar']                             │
└───────────────────────────────────────────┘
----

But this function will return an arbitrary value if a null is found.
So let's say we update our CSV file to look like this:

.data.csv
[format="csv", options="header"]
|===
include::content/2023/11/03/data-update.csv[]
|===

If we run that last query, we'll see the following output:

.Output
[source, text]
----
┌─splitByString('\t', assumeNotNull(value))─┐
│ ['foo','bar']                             │
│ ['']                                      │
│ ['mark','bar']                            │
└───────────────────────────────────────────┘
----

So we might prefer to use `ifNull`, which lets us choose our own default value to use if a null value is encountered:


[source, sql]
----
FROM file('data.csv', CSVWithNames)
SELECT splitByString('\t', ifNull(value, 'N/A'));
----

.Output
[source, text]
----
┌─splitByString('\t', ifNull(value, 'N/A'))─┐
│ ['foo','bar']                             │
│ ['N/A']                                   │
│ ['mark','bar']                            │
└───────────────────────────────────────────┘
----
Poetry: OSError: Precompiled binaries are not available for the current platform. Please reinstall from source
Date: 2023-11-02 00:44:37
In this post, we'll learn how to install a library from source using Poetry.

:icons: font

I've been playing around with the https://github.com/marella/ctransformers/tree/main[CTransformers^] library recently and getting it installed via Poetry was a bit fiddly.
In this post, we'll run through what I've ended up doing.

If we try to add the library in the usual way:

[source, bash]
----
poetry add ctransformers
----

We'll get the following error:

.Output
[source, text]
----
OSError: Precompiled binaries are not available for the current platform. Please reinstall from source using:

  pip uninstall ctransformers --yes
  CT_METAL=1 pip install ctransformers --no-binary ctransformers
----

Instead, we need to call the following command to tell Poetry to install the library from source:

[source, bash]
----
poetry config --local installer.no-binary ctransformers
----

We can then run the following command again:

[source, bash]
----
poetry add ctransformers
----

And this time it will install from source.
Unfortunately, I still haven't worked out how to have Poetry pickup the `CT_METAL` environment variable, which means it doesn't get installed with GPU support.
To get GPU support I'm having to go around Poetry's dependency management by running `pip` directly:

[source, bash]
----
CT_METAL=1 poetry run pip install ctransformers
----

Which works, but isn't ideal.
If anybody knows how to have Poetry pick up the environment variable, please let me know in the comments.
ClickHouse - How to get the first 'n' values from an array
Date: 2023-11-09 00:44:37
In this post, we'll learn how to get the first few entries from an array in ClickHouse

:icons: font

I was recently working with some very long arrays in ClickHouse and I wanted to select just a few values so that they didn't take up the entire screen.
The way I thought would 'just work' (TM) didn't, so this blog documents how to do it.

If you want to follow along, you'll need to https://clickhouse.com/docs/en/install[install ClickHouse^].
On a Mac, Brew is a pretty good option:

[source, bash]
----
brew install clickhouse
----

Once you've done that, launch ClickHouse Local:

[source, bash]
----
clickhouse local -m
----

.Output
[source, text]
----
ClickHouse local version 23.10.1.1976 (official build).

MN :)
----

And then we're going to return an array that contains 10 values:

[source, sql]
----
SELECT range(0,10);
----

.Output
[source, text]
----
┌─range(0, 10)──────────┐
│ [0,1,2,3,4,5,6,7,8,9] │
└───────────────────────┘
----

I wanted to get just the first 5 values, so I tried to use the Python-esque syntax:

[source, sql]
----
SELECT range(0,10)[:5];
----

But that wasn't too happy with me:

.Output
[source, text]
----
Expected one of: token, Comma, ClosingSquareBracket, CAST operator, NOT, INTERVAL, CASE, DATE, TIMESTAMP, tuple, collection of literals, array, number, literal, NULL, Bool, true, false, string literal, asterisk, qualified asterisk, compound identifier, list of elements, identifier, COLUMNS matcher, COLUMNS, qualified COLUMNS matcher, substitution, MySQL-style global variable
----

What we need is the https://clickhouse.com/docs/en/sql-reference/functions/array-functions#arrayslice[`arraySlice`^] function, which lets us extract a variable number of values from an array.
We have to specify an `offset` (remembering that array indexing in ClickHouse starts from `1`) and then a `length`, which is the number of values to return.

The following does the job:

[source, sql]
----
SELECT arraySlice(range(0,10), 1, 5);
----

.Output
[source, text]
----
┌─arraySlice(range(0, 10), 1, 5)─┐
│ [0,1,2,3,4]                    │
└────────────────────────────────┘
----
ClickHouse - AttributeError: 'NoneType' object has no attribute 'array'
Date: 2023-11-08 00:44:37
In this post, we'll learn how to fix an error that we might get when creating a Pandas DataFrame from a query result.

I was querying a ClickHouse server from a Python script a couple of days ago and ran into an error message when trying to create a Pandas DataFrame.
In this blog, we'll see the error message and how to fix it.

I'm gonna assume that we have a ClickHouse Server running and we're going to connect to it like this:

[source, bash]
----
./clickhouse client
----

.Output
[source, text]
----
ClickHouse client version 23.10.1.1709 (official build).
Connecting to localhost:9000 as user default.
Connected to ClickHouse server version 23.11.1 revision 54466.
----

Next, let's create a table with one column and populate it with some values:

[source, sql]
----
CREATE TABLE x(value Int32) 
ENGINE MergeTree() 
ORDER BY tuple();

INSERT INTO x VALUES(1) (4) (7) (12) (24);
----

Now we're going to query this table from a Python script.
We'll first install https://pypi.org/project/clickhouse-connect/[`clickhouse-connect`^]:

[source, bash]
----
pip install clickhouse-connect
----

And then we'll try to get back a Pandas DataFrame containing our result:

[source, python]
----
import clickhouse_connect

client = clickhouse_connect.get_client(host='localhost')

client.query_df("FROM x SELECT *")
----

.Output
[source, text]
----
File ~/Library/Caches/pypoetry/virtualenvs/fastembed-clickhouse-4QwM5Uh0-py3.11/lib/python3.11/site-packages/clickhouse_connect/datatypes/string.py:40, in String._finalize_column(self, column, ctx)
     38 def _finalize_column(self, column: Sequence, ctx: QueryContext) -> Sequence:
     39     if ctx.use_extended_dtypes and self.read_format(ctx) == 'native':
---> 40         return pd.array(column, dtype=pd.StringDtype())
     41     if ctx.use_numpy and ctx.max_str_len:
     42         return np.array(column, dtype=f'<U{ctx.max_str_len}')

AttributeError: 'NoneType' object has no attribute 'array'
----

It looks like we need to install Pandas, which I guess kinda makes sense.

[source, bash]
----
pip install pandas
----

And then if we re-run our script, we'll see the following output:

.Output
[source, text]
----
   value
0      1
1      4
2      7
3     12
4     24
----

Job done.
ClickHouse: Convert date or datetime to epoch
Date: 2023-11-06 00:44:37
In this post, we'll learn how to convert a date or datetime to epoch in milliseconds in ClickHouse

:icons: font

I've been working with dates in ClickHouse today and I wanted to convert some values into epoch seconds/milliseconds to use with another tool.
We're going to document how to do that in this blog post, for future me if no one else.

Let's start an instance of ClickHouse Local:

[source, bash]
----
clickhouse local -m
----

And now we'll write a query that returns the current date/time:

[source, sql]
----
SELECT now() AS time;
----

.Output
[source, text]
----
┌────────────────time─┐
│ 2023-11-06 14:58:19 │
└─────────────────────┘
----

If we want to convert this value to epoch seconds, we can use the `toUnixTimestamp` function.

[source, sql]
----
SELECT toUnixTimestamp(now()) AS time
----

.Output
[source, text]
----
┌───────time─┐
│ 1699282944 │
└────────────┘
----

There's an equivalent function, `toUnixTimestamp64Milli`, that will give us epoch millis.
Let's try that:

[source, sql]
----
SELECT toUnixTimestamp64Milli(now()) AS time;
----

.Output
[source, text]
----
Received exception:
Code: 43. DB::Exception: The only argument for function toUnixTimestamp64Milli must be DateTime64: While processing toUnixTimestamp64Milli(now()) AS time. (ILLEGAL_TYPE_OF_ARGUMENT)
----

Hmmm, looks like we'll need to do some casting.

[source, sql]
----
SELECT toUnixTimestamp64Milli(cast(now(), 'DateTime64')) AS time;
----

Or we could use the https://clickhouse.com/docs/en/sql-reference/data-types/datetime64[`toDateTime64`^] function

[source, sql]
----
SELECT toUnixTimestamp64Milli(toDateTime64(now(), 3)) AS time; -- <.>
----
<.> `3` means a granularity of `milliseconds`

.Output
[source, text]
----
┌──────────time─┐
│ 1699283200000 │
└───────────────┘
----

If we genuinely do want the current time down to the millisecond, we'll need to use the `now64` function instead:

[source, sql]
----
SELECT toUnixTimestamp64Milli(now64()) AS time;
----

.Output
[source, text]
----
┌──────────time─┐
│ 1699283352267 │
└───────────────┘
----

How about if we only have a date, without a time?
We can get that in epoch seconds like this:

[source, sql]
----
select toUnixTimestamp(today()) AS date;
----

.Output
[source, text]
----
┌───────date─┐
│ 1699228800 │
└────────────┘
----

If we want to get epoch millis, we need to convert the value to a `DateTime64`:

[source, sql]
----
SELECT toUnixTimestamp64Milli(toDateTime64(today(), 3)) AS date;
----

.Output
[source, text]
----
┌──────────date─┐
│ 1699228800000 │
└───────────────┘
----
Poetry: Updating a package to a new version
Date: 2023-10-04 00:44:37
In this post, we'll learn how to update a package when using the Poetry Python package manager.

:icons: font

I'm using the Poetry package manager for all my Python projects these days and wanted to upgrade as library that I installed a few weeks ago.
I got myself all tangled up and wanted to write down how to do it for future me.

Let's create a simple project to demonstrate what to do:

[source, bash]
----
poetry init
----

.pyproject.toml
[source, toml]
----
[tool.poetry]
name = "update-blog"
version = "0.1.0"
description = ""
authors = ["Mark Needham <m.h.needham@gmail.com>"]
readme = "README.md"
packages = [{include = "update_blog"}]

[tool.poetry.dependencies]
python = "^3.11"
duckdb = "0.8.1"


[build-system]
requires = ["poetry-core"]
build-backend = "poetry.core.masonry.api"

----

Next, we're going to add DuckDB version 0.8.1 to our project:

[source, bash]
----
poetry add duckdb@0.8.1
----

I want to update this to version 0.9.0, so I initially did this:

[source, bash]
----
poetry update duckdb 
----

.Output
[source, text]
----
Updating dependencies
Resolving dependencies... (0.4s)

No dependencies to install or update
----

Hmmm, that doesn't do anything. 
I came across https://nick.groenen.me/notes/upgrade-python-packages-to-their-latest-version-when-using-poetry/[Nick Groene's] blog post and he said you need to append `@latest` to force an update.
Let's try taht:

[source, bash]
----
poetry add duckdb@latest
----

.Output
[source, text]
----
Using version ^0.9.0 for duckdb

Updating dependencies
Resolving dependencies... (0.1s)

Package operations: 0 installs, 1 update, 0 removals

  • Updating duckdb (0.8.1 -> 0.9.0)

Writing lock file
----

Happy days!
I still don't know how to update all the dependencies for a project to the latest version so if you know how to do that, please let me know!
DuckDB: Show a list of views
Date: 2023-10-02 00:44:37
In this post, we'll learn how to get a list of views in DuckDB.

:icons: font

I recently wanted to get a list of the views that I'd created in a DuckDB database and it took me a while to figure out how to do it.
So this blog post is for future Mark more than anyone else!

We're going to start with the following CSV file:

.data/sales.csv
[source, csv]
----
date,product_id,quantity,sales_amount
2021-01-01,101,5,50
2021-01-02,102,3,30
2021-02-01,101,4,40
2021-02-02,103,6,60
----

And now we'll create a table from the https://duckdb.org/docs/api/cli.html[DuckDB CLI^]:

[source, sql]
----
CREATE TABLE sales AS 
SELECT * from 'data/sales.csv';
----

The next task is to create a view called `monthly_sales` that aggregates the sales by month:

[source, sql]
----
CREATE VIEW monthly_sales AS
SELECT
    DATE_TRUNC('MONTH', date) AS month,
    product_id,
    SUM(quantity) AS total_quantity,
    SUM(sales_amount) AS total_sales_amount
FROM sales
GROUP BY ALL
ORDER BY ALL
----

Now that I've created the view, I wanted to figure out how to list the views in my database.
It took me a while to figure out how to do this from the docs, but I eventually found the solution on the https://duckdb.org/docs/sql/duckdb_table_functions[DuckDB_% Metadata Functions^] page.

To get a list of all the views, we can run this query:

[source, sql]
----
FROM duckdb_views();
----

But that returns a lot of rows as it includes all the internal views as well.
To find just the ones that we've defined, we need to add the following `WHERE` clause:

[source, sql]
----
FROM duckdb_views() 
SELECT * EXCLUDE(sql)
WHERE NOT internal;
----

.Output
[options="header"]
|===
| database_name | database_oid | schema_name | schema_oid | view_name | view_oid | internal | temporary | column_count
| sales | 992 | main | 994 | monthly_sales | 1006 | false | false | 4
|===

Or, if we want to write even less code, we can query the `duckdb_views` view rather than the function.
The view automatically strips out the internal views:

[source, sql]
----
FROM duckdb_views
SELECT * EXCLUDE(sql);
----

.Output
[options="header"]
|===
| database_name | database_oid | schema_name | schema_oid | view_name | view_oid | internal | temporary | column_count
| sales | 992 | main | 994 | monthly_sales | 1006 | false | false | 4
|===

Job done!
iPython: How to disable autocomplete
Date: 2023-10-29 00:44:37
In this post, we'll learn how to disable autocomplete when using the iPython REPL.

:icons: font

I've been toying with the idea of using iPython as the Python REPL for videos on https://www.youtube.com/channel/UCKEk670ECmteGBehmDjVSSg[@LearnDataWithMark^], but I wanted to disable the autocomplete functionality as I find it too distracting.
In this blog post, I'll show how to do it.

First, let's install iPython:

[source, bash]
----
poetry add ipython
----

And now we'll launch the iPython REPL:

[source, bash]
----
poetry run ipython
----

.Output
[source, text]
----
Python 3.11.4 (main, Jun 20 2023, 17:23:00) [Clang 14.0.3 (clang-1403.0.22.14.1)]
Type 'copyright', 'credits' or 'license' for more information
IPython 8.16.1 -- An enhanced Interactive Python. Type '?' for help.
----

Now, let's say I type `x = ` to populate a variable.
It will suggest something like the following:

.Not so helpful autocomplete
image::{{<siteurl>}}/uploads/2023/10/ipython-autocomplete.png[]

I've only just started the session so this isn't particularly useful.
I'm not sure where it's getting the suggestions from - perhaps from my previous normal Python REPL?

In any case, I want to turn them off, which we can do from the https://ipython.org/ipython-doc/3/config/intro.html[iPython configuration file^].
You can create one of those by running the following command:

[source, bash]
----
poetry run ipython profile create
----

.Output
[source, text]
----
[ProfileCreate] Generating default config file: PosixPath('/Users/markhneedham/.ipython/profile_default/ipython_config.py')
----

If we open that file, the first few lines will read like this:

.~/.ipython/profile_default/ipython_config.py
[source, python]
----
# Configuration file for ipython.

c = get_config()  #noqa
----

To disable autocomplete, let's add the following line:

[source, python]
----
c.TerminalInteractiveShell.autosuggestions_provider = None
----

If we then close the iPython REPL and launch it again, we can try the `x = ` code fragment again:

.No autocomplete
image::{{<siteurl>}}/uploads/2023/10/ipython-autocomplete-off.png[]

Job done!
Poetry: Install does not contain any element
Date: 2023-10-26 00:44:37
In this post, we'll learn how to work around the Poetry 'does not contain any element' error message

:icons: font

I've run into an interesting error a few times when using the Poetry package manager over the last few weeks and wanted to document it in case anyone else has the same problem.
I'm still not sure how to avoid it in the first place, so if you know, please let me know!

Anyway, let's get started.
Imagine we're creating a new project and we type the following:

[source, bash]
----
$ poetry init
----

It will pop up the following dialogue and we'll select the defaults, won't define anything interactively, and will then have it create the file:

.Output
[source, text]
----
This command will guide you through creating your pyproject.toml config.

Package name [tei-tutorial]:
Version [0.1.0]:
Description []:
Author [Mark Needham <m.h.needham@gmail.com>, n to skip]:
License []:
Compatible Python versions [^3.11]:

Would you like to define your main dependencies interactively? (yes/no) [yes] no
Would you like to define your development dependencies interactively? (yes/no) [yes] no
Generated file

[tool.poetry]
name = "tei-tutorial"
version = "0.1.0"
description = ""
authors = ["Mark Needham <m.h.needham@gmail.com>"]
readme = "README.md"
packages = [{include = "tei_tutorial"}]

[tool.poetry.dependencies]
python = "^3.11"


[build-system]
requires = ["poetry-core"]
build-backend = "poetry.core.masonry.api"
----

As you can see from the output at the end, this is what my `pyproject.yaml` file contains:

[source, toml]
----
[tool.poetry]
name = "tei-tutorial"
version = "0.1.0"
description = ""
authors = ["Mark Needham <m.h.needham@gmail.com>"]
readme = "README.md"
packages = [{include = "tei_tutorial"}]

[tool.poetry.dependencies]
python = "^3.11"


[build-system]
requires = ["poetry-core"]
build-backend = "poetry.core.masonry.api"
----

If I then run the following:

[source, bash]
----
poetry install
----

I get the following error message:

.Output
[source, text]
----
Installing dependencies from lock file

No dependencies to install or update

/Users/markhneedham/projects/learndatawithmark/tei-tutorial/tei_tutorial does not contain any element
----

The line in `pyproject.toml` that's causing the problem is this one:

[source, toml]
----
packages = [{include = "tei_tutorial"}]
----

I'm not entirely sure what makes that line get added because for some of my projects, it's added and for others, it isn't!
The line itself does make sense if you're using Poetry to create a library as you'd probably then have your code under the `tei-tutorial` directory in this case.
But in my case, I'm using Poetry to manage projects with a few scripts in the root directory, so I don't have that folder.

So if we want to get rid of the error, we either need to create the `tei_tutorial` directory or we need to delete the `packages` line in `pyproject.toml`
I'm going to delete it, and if I then run `poetry install` again, it's happy:

.Output
[source, text]
----
Installing dependencies from lock file

No dependencies to install or update
----