pseudo()

Read(1925) Label: pseudo table,

Here are how to use pseudo() functions.

pseudo()

Description:

Generate a pseudo table definition object.

Syntax:

pseudo(pd,n)

Note:

The function generates a pseudo table definition object by specifying the definition record pd. By setting up parameter n, you can get a multicursor from the pseudo table object; n is the number of subcursors in a multicursor.

Parameter:

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

Option:

@v

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

Return value:

Pseudo table object

Example:

When data source of the pseudo table is 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.

 

When data source of the pseudo table is a multizone composite 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.

 

Set parameter n:

 

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.

 

When using @v option:

 

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.

 

For an in-memory pseudo 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()

 

Define pseudo table’s user-defined fields:

Below is data in emps.ctx:

In the data file, the range of DeptID field values is 1-5, which corresponds to five departments – Sales, Technology, R&D, Financial and Admin.

Bools field stores data of two binary fields – Gender and Married. In field Gender, 0 represents male and 1 represents female; In field Married, 0 represents single and 1 represents married. Bools field values are results of computing bits(Gender,Married).

 

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:

 

Define pseudo table’s user-defined fields – the redundant field:

Below is data of Details.ctx:

custInfoComment is a redundant field that stores values of custInfo.comment field:

 

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:

 

Define pseudo table’s user-defined fields – field aliases:

Below is data of Events.ctx:

Each row of data is an event. Each event stores eventType and the corresponding property information eventInfo.

eventType field values are 1-3, which correspond to appInstall, appStart and append.

 

eventInfo contains records that also contain multiple fields for storing different properties.

 

s1, s2, f1 and dt1 are real fields that store the properties of the three types of events. s1 stores three string properties of the three types of events – browser, page and page; s2 stores three string properties of the three types of events – device, title and title; f1 stores two numeric properties – reward and amount, for appInstall event and append event; dt1 stores two datetime properties – dt and dt for appInstall event and append event.

 

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

T.pseudo(T ’ ,…)

Description:

Segment a pseudo table according to the segmentation way of another.

Syntax:

T.pseudo(T’,…)

Note:

The function sets up pseudo table T’,… according to pseudo table T. The former is segmented the same way as the latter. After segmentation way is set up for the new pseudo table, you can retrieve a multicursor from this object.

Parameter:

T

A segmented pseudo table object

T’

A pseudo table object to be segmented

Example:

 

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)

Generate a pseudo table object.

5

=A2.pseudo(A4)

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

6

=A4.cursor()

Get a multicursor from pseudo table A4.