Hi @alimly13 ,
My previous suggestion for using “DESC” in the order by clause, was a guess as I hadn’t looked at the ClickHouse documentation. Evidently that doesn’t work.
For many of us (me included) the concept of “putting data into the database in a specific order” is alien to us. Working with regular SQL relational databases, there is no concept of the data being stored in any order, since relational databases were born out of “set theory” and sets do not have any order.
The order of the data is defined only at the point you retrieve it, either by providing an ORDER BY clause in the retrieval query, or if none is provided, then by the arbitrary order in which the DBMS happens to find the data during execution, which in theory could change from one execution to another, but in reality generally doesn’t (although there is no guarantee of this).
However, I’m working on the basis that ClickHouse is different in its approach, and that somehow it does order the data, and presumably this is what the ORDER BY clause in the DB Table Creator is doing.
Now based on this assumption, and from what I can see when ClickHouse returns the data in your white-on-black screenshot, it is doing exactly what it is told. (order by column11, which has all the zeroes first, and then the non-zero). The data order in KNIME is, as I already said, likely to be irrelevant, because ClickHouse has ordered the data by column11 as you have told it to do.
I can only guess here because I haven’t used ClickHouse and have no access to it, but as I mentioned (and seeing that @mlauber71 has independently come to a similar conclusion), a possible option is the introduction of a Counter Generation node to create an artificial sequence, like this:
The Counter Generation node will create a column called “Counter”, and because it is introduced after your sorter, it will create a sequence in the order that your data exists in KNIME. (In my mock-up screenshot I realised I had to move the Sorter and the Counter Generation node before the DB Table Creator as the new Counter column will need to be present in the table definition used by the DB Table Creator node).
Perhaps you can try that and then add the following into you DB Table Creator node:
ENGINE = MergeTree() ORDER BY Counter;
or it might have to be
ENGINE = MergeTree() ORDER BY "Counter";
(I don’t know how ClickHouse handles mixed case identifiers)
According to the ClickHouse documentation concerning MergeTree…
Use the
`ORDER BY tuple()`
syntax, if you do not need sorting
… but I don’t know where that will get you since you apparently do require some form of ordering (although strictly speaking what you require is not sorting, since you don’t currently have a value to sort by!)
This is what is says about the ORDER BY clause.
It indicates this will cause the table contents to be ordered by the stated column, which is exactly what it is doing in your example.
In summary, what this comes down to is that if you require that ClickHouse stores the data in your table in the same order that you see it in KNIME, then you have to tell it what that order is. Currently your data contains no primary key, or any other column that actually defines the sort order, so you need to provide such a column (as per above suggestion). I don’t think you can simply insert rows and expect them to be stored and retrieved in the same (arbitrary) order that they were inserted.
