The opposite of pouring information into the database

This is my workflow picture :


I used the nodes below that you can see, and told it to automatically correct the name of the database table , Then, in the next step, when I want to put the information in the database, I used the sorter, which does not put the information in the database the other way around.
Then in db insert, when I want to put the information in the database, it shows me correctly, but when I put the information in the database, it goes the other way around, what should I do?

my query in db table creator node :

When information is poured into the database :

Hi @alimly13, what is the database you are using? I googled the syntax of your sql statement in the db creator screenshot, and it suggested ClickHouse.

Although I’ve assisted with a couple of questions regarding ClickHouse previously here on the forum, I’ve never used it myself, but here goes… :wink:

The ORDER BY clause you specified was “column11”, and I take it that this tells ClickHouse (or whatever db it is) to order the data in the table by that column? With traditional SQL databases, there is no specific ordering for the data in the tables, but as a “Column Oriented sql database”, I think ClickHouse is different to most relational databases in this regard.

Since you are ordering by column11, the default ordering of the data in the database would be in ascending order of column11. It looks to me then that the data in the table is ordered as per that specification, since the zero values appear first, and the one positive value appears at the end. I would think then, that the ordering in the database will be as defined by the DB Insert, no matter what order your data was when it was supplied to the DB Insert node.

Given that all-but-one of the values in Column11 are “0”, I would think that the dbms has free-reign to order those rows arbitrarily, since there is no actual defined order for them.

It isn’t clear what node produced the first output screenshot (of the KNIME table)
What are your screenshots actually showing?

What sort ordering is being performed by the Sorter (prior to the DB Insert)?

What command did you use to produce the output from the database?

3 Likes

I am using Clickhouse database and I have tried different columns such as 1 to sort from this column, but it still throws the opposite information.

The output table, when we use node db insert, it shows me correctly according to the image, but when I see the information from the database, it shows me the opposite.

Hi @alimly13, the database is surely sorting it according to the ORDER BY clause you specify. If you need it to sort it in a specific way, I would expected you’d need a column that contains a sequential sort order with no repetition of values.

You could presumably use ORDER BY column11 DESC which is what your KNIME table is showing, but because of the repetition of 0 in column11, I’d say there are no guarantees how they will be ordered with respect to each other in the database.

After the Sorter, can you maybe add a Counter Generation node, and then ORDER BY that Counter column?

What happens if you don’t specify ORDER BY in the DB Insert? Does it order according to their arrival in the database, or does it fail altogether?

2 Likes

When I type this command (ORDER BY column11 DESC), it gives me this error :
image
If I delete the order by query, it gives me an error when creating the table and does not create the table

1 Like

@alimly13 what your screenshot does show is data which has been inserted not data being derived from the database. Maybe you want to check that.

What I do not understand is what it is you want to accomplish. Store data in a specific order or just load data into Clickhouse? Will you use the sort column in any way? You could think about creating an artificial counter to have one column that has unique values and can function as a sorter.

Edit: I just saw that @takbb has basically suggested the same thing. Ok I will read the threads I comment on more carefully…

2 Likes

It doesn’t put the data into the database in the order it is, and because of this query ( ORDER BY column11; ), it is in the db table creator node, and if I take this order by, it gives me an error when creating a table for the database.
If it is possible, we can connect with any desk and see this and guide me

I’m not familiar with Clickhouse specific but pretty sure that Create table does not support Order by. From other side, if you need to keep some insert order try to use clustered index on the table primary key.

2 Likes

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.

3 Likes

I am really grateful for the advice you gave me, it solved my problem and I used this query as you sent me the message :
ENGINE = MergeTree() ORDER BY Counter;

Another question, I want to open the table that I created and put the information in it in the browser and show me the information of that table. What should I do?

Looks like you opened a separate thread for your latest question. To keep things tidy, let’s discuss there, since @takbb has solved your initial issue here.

2 Likes

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.