Generating pseudo table from multizone composite table

In real-world cases, a multiple composite table is a composite table consisting of multiple composite tables of same structure. It stores zone tables, whose names are “zone table number + same composite table name (such as OrderInfo), as follows:

In this multizone composite table, numbers of zone tables 1.OrderInfo.ctx and 2.OrdderInfo.ctx are 1 and 2 respectively. They store orders data in the year 2020 and that in the year 2021 respectively and are made of SID (salesperson ID), OID (order ID), OTime (Ordering time) and Amount (Order amount).

Look at how to define a pseudo table based on a multizone composite table through the following examples.

 

Step 1: Load the composite table file

Open the metadata file demo.glmd and click  (Add pseudo table) to add a new pseudo table.

Click Select to open any zone table in the pseudo folder, such as 1.OrderInfo.ctx.

Now the file name is automatically retrieved into Pseudo table name. Under Select field(s), the composite table’s fields and primary key information is listed.

 

The pseudo table name contains a dot symbol. In order to avoid amibiguity during the query, we modify the name as OrderInfo.

l  Pseudo table name: For setting name of the pseudo table. Select a file and the system automatically recoginzes the file name as the pseudo table name. Users can also define their own name.

 

Change CTX file path into a relative one and use the file name without the zone table number as the composite table file name:

l  CTX file: For setting the physical table corresponding to the pseudo table; only support composite table file and bin file.

The file path can be a relative one or an absolute one. The relative path is relative to the Main path configured in Tool-Options-Esproc options.

 

Step 2: Set the list of zone table numbers

Enter numbers of physical zone tables 1.OrderInfo.ctx and 2.OrderInfo.ctx – 1 and 2 respectively – after Zone, the list of zone table numbers.

Click Edit to set up the zone table number list:

 

Click OK to get the following zone table number list:

 

l  Use columnar storage: Set whether to use columnar storage to increase performance. It is checked by default.

Step 3: Edit zone expression

Set the multizone composite table file’s Zone expression as OTime:

l  Zone expression: The zone expression is only valid for a multizone composite table. It is used to set date field for a pseudo table for data filtering.

In a multizone composite table, data is stored in zone tables, usually according to a time type field. Each zone table stores data in a certain time interval. Such a time field is called zone expression. In this example, OTime field in the two zone tables is datetime type and stores data in the years 2020 and 2021. Here OTime is the zone expression.

The data type of a date field is generally date, time or datetime type. We can convert one type to another using an expression to increase the query performance.

The date field should be ordered according to the zone table files if there are multiple of them. But it isn’t necessarily to be ordered for each single zone table file. The date field value intervals of each file are recorded at the creation of the pseudo table, and the corresponding file will be automatically located for handling a query or filtering. In this example, though two zone tables are divided according to the year, the OTime field in each of them isn’t required to be ordered.

 

Below is the DQL query result: