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

Source of data in the pseudo table, which can be a composite table or a multizone composite table. This attribute is indispensable. 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

var

The table sequence/in-memory table variable name; an in-memory pseudo table will be generated when attribute file isn’t defined

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:

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

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 value 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

@v

Enable to use pure-table-sequence based column-wise computation on a pseudo table generated from a composite table

A

1

=create(file).record(["D:/file/pseudo/app.ctx"])

Define 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,zone,date).record(["pseudo/OrderInfo.ctx",[1,2],"OTime"])

Define 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 according to the zone column 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 a pseudo table.

3

=pseudo(A2)

Generate a pseudo table object.

4

=A3.select(STATEID==3)

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

5

=A4.import()

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"]}]])

Bools Define 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: