pseudo()

pd

The pseudo table definition record, which is a table sequence record having the specified structure. Fields of the record and their descriptions are listed below:

file

Name of a ctx/btx file. This attribute is indispensable.

Source of data in the pseudo table can be a bin file (btx), a composite table (ctx) or a multizone composite table, but cannot be an attached table. With a btx file, its data will be wholly imported automatically. Generate an in-memory pseudo table when it is a btx file.

The file path can be a relative path, which is relative to the main directory, or an absolute path.

[zone]

A list of zone table numbers when attribute “file” is a multizone composite table.  It should not be a cluster file.

key

Primary key of the btx file, which also can be a time key and on which index is created after the file data is imported. The symbol # representing a base key means a sequence number. The value of attribute key is represented by function expression keys(ki,…).

var

The table sequence/in-memory table variable; primary key and index are required to be created for the table in advance. .

date

Zone column expression, which is valid only for a multizone composite table and which is generally a date, time or datetime field. In a multizone composite table, data is stored in multiple zone tables according to a time type field, and each zone table stores data in a certain period of time; such a time type field is called zone column expression, which is defined to make data filtering more convenient;

It is required that date field be ordered by the multiple files that are involved, but it is not required that the field be ordered in each single file; the interval of data values in each file is recorded when creating the pseudo table, and the target file will be automatically located during the query or filtering.

[column]

User-defined field definition, which is one or more table sequence records having the specified structure. Fields of the record and their descriptions are listed below:

Note: Cannot use the special field definition functionality when parameter file is a bin file.

name

The name of a real field, which is a field of the physical table; we define pseudo table fields according to this attribute;

We can use a pseudo field in the filtering expression; the pseudo table will convert the pseudo field computation to a real field computation, during which the pseudo field involved and the conversion method are defined through the other attributes; compute the pseudo field if they cannot be converted. For append/update operation, the corresponding real field values will be computed according to the related attributes.

date

Name of date transfer type pseudo field; the real field is days@o (pseudo field).

[alias]

The list of pseudo field aliases. When its value is a string, it is alias of the binary dimension pseudo field or the real field; can set up multiple values to represent multiple aliases; when its value is an expression, return the result of computing the current expression.

enum

Enumerated pseudo field name, whose values are members of a list. You can use ==, != and contain in an enum filtering expression.

list

The list of values of enumerated pseudo field. Real field values are sequence numbers and pseudo field values are members of this list.

[bits]

Binary dimension pseudo field names. In order to reduce storage usage, we can use a binary field (whose value is 1 or 0) to store Boolean field values; when there are many binary fields, we can use a binary dimension field to store them; a binary dimension field can store 32 binary fields at most. The definition arranges fields under Bools from low order to high order. Values of each field are bool true or false.

exp

The expression of a real field’s alias used for append/update operation;

The expression of a redundant field. When no pseudo field name is specified but there is attribute exp in an expression, just take the redundant field as value of exp;

Use this expression to compute the current field during the append/update operation.

n

Number of subcursors in a multicursor; get a unicursor by default. When value is 0, the number of subcursors is Default Number of Subcursors in a Multicursor configured in the designer. When esProc SPL is integrated into a third-party application, the default number of subcursors is cursorParallelNum value set in raqsoftConfig.xml.

Note: Parameters enclosed by [ ] are of sequence type.

@v

Use pure-table-sequence based column-wise computation on a pseudo table generated from a composite table.

A

1

=[{file:" D:/file/pseudo/app.ctx "}]

Create a pseudo table definition record, where the pseudo table’s data source is a composite table.

2

=pseudo(A1)

Generate a pseudo table object.

3

=A2.import()

Get a table sequence from the pseudo table.

A

1

=create(file,key).record(["emp.btx","keys@t(EID,BIRTHDAY)"])

Create pseudo table definition: use a bin file as the source of pseudo table data, define EID as the pseudo table’s base key and BIRTHDAY as the time key.

2

=pseudo(A1)

Generate a pseudo table object.

3

=A2.import()

Extract a table sequence from the pseudo table:

A

1

=create(file,zone).record(["pseudo/OrderInfo.ctx",[1,2]])

Create a pseudo table definition record, where the pseudo table’s data source is a multizone composite table; OTime field in the two zone tables 1.OrderInfo.ctx and 1.OrderInfo.ctx is time type; the field corresponds data in the year 2020 and 2021.

2

=pseudo(A1)

Generate a pseudo table object.

3

=A2.select(OTime>date(2020,12,20))

Filter data in the pseudo table and the program automatically gets data from the corresponding zone table.

4

=A3.import()

Return the filtering result as a table sequence.

A

1

=create(file).record(["emp.ctx"])

2

=pseudo(A1,3)

Generate a pseudo table object where parameter n is 3.

3

=A2.cursor()

Get a multicursor having 3 subcursors from the pseudo table.

A

1

=create(file).record(["Emp.ctx"])

2

=pseudo@v(A1)

Generate a pseudo table object from a composite table and use @v option to enable pure-table-sequence-based, column-wise computation.

3

=A2.cursor()

Generate a column-wise cursor.

4

=A2.import()

Generate a pure table sequence.

5

=A2.memory()

Generate a column-wise, in-memory table.

A

1

>tab=demo.query("select * from CITIES")

Get CITIES table and store it in variable tab.

2

=create(var).record([tab])

Use an in-memory table to define an in-memory pseudo table.

3

=pseudo(A2)

Generate an in-memory pseudo table object.

4

=A3.select(STATEID==3)

Get the information where STATEID is 3 from the pseudo table.

5

=A4.import()

6

=[{file:"stu.btx",key:"keys(Class,Name)"}]

Use the bin file to define an in-memory pseudo table, where Class and Name are the primary key fields.

7

=pseudo(A6)

Generate an in-memory pseudo table object.

A

1

=create(file,column).record(["pseudo/emps.ctx",[{name:"DeptID",enum:"Dept",list:["Sales","Technology","R&D","Financial","Admin"]},

{name:"Bools",bits:["IfMarried","IfLady"]}]])

Create a pseudo table definition record, which contains a enumerated pseudo field Dept and a binary dimension pseudo field.

2

=pseudo(A1)

Generate a pseudo table object.

3

=A2.import(DeptID,Dept)

DeptID field values 1-5 correspond to  ["Sales","Technology","R&D","Financial","Admin"] respectively.

4

=A2.import(Gender,Married,IfMarried,IfLady)

View the correspondence between Gender/Married and IfLady/IfMarried:

A

1

=create(file,column).record(["pseudo/Details.ctx",[{name:"custInfoComment",exp:"custInfo.comment"}]])

Define exp:"custInfo.comment" for the pseudo table and its corresponding real field custInfoComment.

2

=pseudo(A1)

Generate a pseudo table object.

3

=A2.select(pos(custInfo.comment,"risk"))

As the filtering condition contains an expression, instead of retrieving custInfo records and then their fields, custInfo will be replaced by the real field custInfoComment.

4

=A3.import()

Import the filttering result.

5

=file("pseudo/details_new.btx").cursor@b(id,ddate,device,amt,bools,custInfo)

Below is data of details_new.btx:

6

=A2.append(A5)

Append A5’s data to A2, during which the program will automatically convert and generate custInfoComment

7

=A3.import()

Data of details.ctx after data appending:

A

1

=create(file,column).record(["pseudo/Events.ctx",[{name:"eventType",alias:null,enum:"eventTypeString",list:["appInstall","appStart","appEnd"],exp:null},

{name:"s1",alias:["browser","appStart_page","appEnd_page"],enum:null,list:null,exp:"case(eventType,1:eventInfo.browser,2:eventInfo.page,3:eventInfo.page)"},

{name:"s2",alias:["device","appStart_title","appEnd_title"],enum:null,list:null,exp:"case(eventType,1:eventInfo.device,2:eventInfo.title,3:eventInfo.title)"},

{name:"f1",alias:["reward","amount"],enum:null,list:null,exp:"case(eventType,1:eventInfo.reward,3:eventInfo.amount)"},

{name:"dt1",alias:["appStart_dt","appEnd_dt"],enum:null,list:null,exp:"case(eventType,2:eventInfo.dt,3:eventInfo.dt)"}]])

Configure multiple aliases having business meanings for each real field in the pseudo table definition.

Three aliases of real field s1: "browser","appStart_page","appEnd_page", and their corresponding expressions: exp:"case(eventType, 1:eventInfo.browser, 2: eventInfo.page, 3: eventInfo.page)".

If value of eventType isn't 1, 2 or 3，value of real field s1 should be null. Therefore, here the code should not be case(eventType, 1:eventInfo.browser;eventInfo.page).

It is similar to configure aliases for s2, f1 and dt1.

2

=pseudo(A1)

Generate a pseudo table object.

3

=A2.select(eventTypeString=="appInstall" && browser=="firefox")

There is a field alias in the filter condition, so SPL won't first retrieve eventInfo records and then their fields; rather, it automatically replaces it with the real field s1 and uses values of the redundant field to peform the subsequent computations.

4

=A3.import(id,eventTypeString,browser,reward)

Get the filter result

5

=file("pseudo/events_new.btx").cursor@b(id,eventTypeString,eventInfo)

Below is data in events_new.btx:

6

=A2.append(A5)

Append data, during which the system automatically generate s1, f1 and other fields according to eventTypeString and eventInfo.

7

=A2.import()

Events.ctx with appened data:

A

1

=create(file,column).record(["emdate.ctx",  [{name:"Birdays",date:"Bdate"}]])

Create a pseudo table definition record: use emdate.ctx as source of pseudo table data, define a date conversion type pseudo field named Bdate and  whose corresponding real field is Birdays:

2

=pseudo(A1)

Generate a pseudo table object.

3

=A2.import()

Extract a table sequence from the pseudo table, where Bdate is the pseudo field:

4

=A2.select(Bdate<date("1969-01-01"))

Filter pseudo table records through the pseudo field to select those where Bdate values are less than 1969-01-01 and return them to the new pseudo table.

5

=A4.import()

Retrieve data from A4’s pseudo table:

T

A segmented pseudo table object.

T’

A pseudo table object.

A

1

=create(file).record(["emp1.ctx"])

2

=pseudo(A1,3)

Generate a pseudo table object having 3 subcursors from a composite table.

3

=create(file).record(["emp2.ctx"])

4

=pseudo(A3,5)

Generate a pseudo table object, with the number of subcursorss being 5.

5

=A2.pseudo(A4)

Segment pseudo table A4 the same way as pseudo table A2.

6

=A4.cursor()

Get a multicursor having 3 subcursors from pseudo table A4.