SQL Query That Returns The Names of the Clusters for a Class

[SDIPro]

We need a SQL query that can return the names of the clusters that are associated with a class.

[rdelangh]

+1
I would require this, to identify the clusters which

• have not yet been backed up
• contain old data that can be trashed (given appropriate naming conventions of my self-created clusters)

[lvca]

@rdelangh what do you mean with both your points? Could you please explain them better?

[rdelangh]

@lvca
The BACKUP DATABASE mechanism (= full backup, not realistic for huge dbases), or the discussed incremental backup mechanism (not our requirement, we wish to backup specific data-ranges of records) do not allow to keep tight control over which data is actually in need of backup, and which backup-file would contain data that must be restored after e.g. erroneous DELETE.

So, what I hope to figure out in the coming days is a way that resembles very much traditional databases' partitioning mechanisms, where

• you upfront create partitions (here: clusters) with a name that reflects some date-range (ie "mycluster_20160728" for records of 28th July 2016), then intelligently insert records in the appropriate cluster, by their date
• selecting records for a given date, translates into a SELECT ... FROM cluster:mycluster_20160728 (or similar)
• backing up records for a given date, can (hopefully) be done with an EXPORT of the corresponding specific cluster only; similarly to restore the contents of such cluster
• cleanup of old, obsoleted data, is a DROP CLUSTER xyz thing, very neat and fast

[lvca]

Got it.

[lvca]

In order to do this, we need cluster level indexes too.

[rdelangh]

Luca,
Andrey commented (orientechnologies/orientdb-labs#7 (comment)) in another subject that

1. indexes are automatically removed for records which are in a cluster that gets DROPped
2. the free space in the index files for such removed indexes are re-used

-> do you mean that this mechanism is not sufficient to support cluster-based INSERT, cluster-based EXPORT, and DROP of clusters ?
-> do you suggest that the indexes might become unusable/inconsistent (with a need to rebuild them, a very lengthy process when there is 30 billion records in the class) when working this way?

[luigidellaquila]

For cluster IDs in a class you can use

select expand(classes) from metadata:schema

For clusters in the server you can use

select expand(clusters) from metadata:storage

Then you can mix them to get info about clusters in a single class:

select from (
  select expand(clusters) from metadata:storage
) WHERE id in (select classes[name="YourClassName"][0].clusterIds from metadata:schema)

(tested in v 3.0)

Thanks

Luigi

[rdelangh]

hi @luigidellaquila
before we can start to use v3, could you please help to fix our issue with Lucene indexes on multiple-fields, in v3 : #8323 ?

[rdelangh]

in v2.x, there is no such thing as "metadata:storage", to query the cluster-ids from, or is there another place to look for cluster details?

[luigidellaquila]

Hi @rdelangh

In v 2.x there is no way to retrieve the clusters from SQL. You have to do it via API.

eg. in Java:

OClass clazz = db.getMetadata().getSchema().getClass("className");
clazz.getClusterIds();

...

db.getClusterNameById(id);

Thanks

Luigi