Table Methods

Collection Methods apply to FPL tables

IsEmpty

IsEmpty()
- return true if table is empty, false otherwise

if (testTable.IsEmpty()) {
  printf("Table is empty")
} else {
  // code block
}

RenameColumn

RenameColumn(columnName, newColumnName)
- finds colName from the table and renames it to newColumnName if colName exists

ID	value
1	Rockville
2	Silver Spring
3	Baltimore

testTable.RenameColumn("value", "City")

Table t4 testTable after renameColumn function call

ID	City
1	Rockville
2	Silver Spring
3	Baltimore

RemoveColumn

RemoveColumn(columnName)
- finds columnName from the table and removes that column from the table if columnName exists

Table t4 testTable before removeColumn function calls

ID	City	State	Country
1	Rockville	Maryland	US
2	Silver Spring	Maryland	US
3	Baltimore	Maryland	US

testTable.RemoveColumn("State") // removes the State column from testTable
testTable.RemoveColumn("Country") // removes col2 from testTable

Table t4 testTable after removeColumn function calls

ID	City
1	Rockville
2	Silver Spring
3	Baltimore

GetColumnValues

GetColumnValues(columnName)
- return a list of values on columnName from the table
- throws an Error Exception if columnName does not exist in the table

ID	City	State	Country
1	Rockville	Maryland	US
2	Silver Spring	Maryland	US
3	Baltimore	Maryland	US

testTable.GetColumnValues("City") // returns ["Rockville", "Silver Spring", "Baltimore"]

GetKeys

GetKeys()
- return list of values from the key column: "ID" before the merge, "_globalID" after the merge.
- throws an Error Exception if the column "ID" or "_globalID" dies bit exist in the table

ID	City	State	Country
1	Rockville	Maryland	US
2	Silver Spring	Maryland	US
3	Baltimore	Maryland	US

testTable.GetKeys() // return [1,2,3]

SetColumnUnit

SetColumnUnit(column, unit)
- set the unit of column

ID	Item	Cost
1	Pen	2.99
2	Eraser	5.99
3	Ruler	1.99

testTable.SetColumnUnit("Cost", "USD") // sets the Cost column to USD

ID	Item	Cost(USD)
1	Pen	2.99
2	Eraser	5.99
3	Ruler	1.99

Sort

Sort(limit, "+col1", "-col2"…)
- sort the table by column values and limit to the first N.
- limit = 0 will return all results.
- "+" for ascending and "-" for descending, if not specified then defaults to descending order
- throws a ReferenceError Exception if any of the column values does not exist in the table

ID	Item	Cost
1	Pen	2.99
2	Eraser	5.99
3	Ruler	1.99

// return top 10 rows, sort by "Cost" column in descending order
natGateways.Sort(10, "-Cost")
// sort testTable by the "Cost" column in descending order
testTable.Sort(0, "Cost")

ID	Item	Cost
2	Eraser	5.99
1	Pen	2.99
3	Ruler	1.99

Join

Join(rightTable, ({keyColumn1, keyColumn2…}, {OtherColumns…}) ⇒ joinType )
- this method will update the calling table (left table)
- the join configuration is specified as a lambda function
- joinType is one of the following: "inner", "fullouter"
- join rightTable on keyColumn(s). if "OtherColumns" are not provided, all columns from the rightTable will be joined.
- {ID}: Both left column and right column is named "ID"
- {ID2:ID}: Right table column "ID2" is renamed to "ID" in left table

// outer join example
let arr1 = [
  {ID: "a", Col1: 3},
  {ID: "b", Col1: 2}
]
let t1 = jsonTable(arr1)

let arr2 = [
  {ID2: "a", Col2: "foo"},
  {ID2: "c", Col2: "bar"}
]
let t2 = jsonTable(arr2)
t1.Join(t2, ({ID2:ID}, {Col2:Col21}) => "fullouter")
// keeps all elements from both tables and combines them if same ID

Table t1 after the fullouter join call

ID	Col1	Col21
a	3	foo
b	2
c		bar

// inner join example
let arr1 = [
  {ID: "a", Col1: 3},
  {ID: "b", Col1: 2}
]
let t1 = jsonTable(arr1)

let arr2 = [
  {ID: "a", Col2: "foo", col3: "bar"},
  {ID: "c", Col2: "bar", col3: "foo"}
]
let t2 = jsonTable(arr2)
t1.Join(t2, ({ID}) => "inner")
// only keeps elements with the same ID in both tables

Table t1 after the inner join call

ID	Col1	Col2	Col3
a	3	foo	bar

Append

Append(table1, table2 …)
- merged the input tables into the calling table
- this method will throw an exception if any of the arguments passed it is not a Table object
- calling this function on an empty table will work

Table t1 before the append call

ID	City	State	Country
1	Rockville	Maryland	US

Table t2

ID	City	State	Country
2	Silver Spring	Maryland	US

Table t3

ID	City	State	Country
3	Baltimore	Maryland	US

Append(t1, t2, t3)

Table t1 after the append call

ID	City	State	Country
1	Rockville	Maryland	US
2	Silver Spring	Maryland	US
3	Baltimore	Maryland	US

Map

Map( (row) ⇒ x )
- return a new list populated with the results of calling a provided function on every row in the calling table

let arr1 = [
  {ID: "a", Col1: 3},
  {ID: "b", Col1: 2}
]

let t1 = jsonTable(arr1)
t1.Map( (row) => row.ID) // returns the list ["a","b"]

Filter

Filter( (row) ⇒ predicate(row) )
- remove rows where predicate function return false from the calling table

let arr1 = [
  {ID: "a", Col1: 3},
  {ID: "b", Col1: 2},
  {ID: "c", Col1: 1}
]

let t1 = jsonTable(arr1)
t1.Filter( (row) => row.ID == "a")

Table t1 after the filter call

ID	Col1
a	3

Each

Each( (row) ⇒ { })
- for each row, run the lambda function

Example Code:

    let arr1 = [
      {ID: "a", Col1: 3},
      {ID: "b", Col1: 2}
    ]

    let t1 = jsonTable(arr1)

    t1.Each( ({ID,Col1}) => {
      printf("%s, %d",ID,Col1)
    })

Example Output:

printf: a, 3
printf: b, 2

Example Code:

    let arr1 = [
      {ID: "a", Col1: 3},
      {ID: "b", Col1: 2}
    ]

    let t1 = jsonTable(arr1)

    t1.Each( ({ID,Col1,C2}) => { // Note: Column 'C2' does not exist
      printf("%s, %d, %v",ID,Col1,C2)
    })

Example Output:

printf: a, 3, undefined
printf: b, 2, undefined

Aggregate

Aggregate(({col1, col2 … }) ⇒ { groupBy:{groupByKey1,…}, columns:{ aggregates }}
- this method will generate a new table
- the input is a lambda function.
- the input object destructuring pick up the columns in the table
- the return is a object.
- the "groupBy" object is optional, specify the groupBy column(s).
- the "columns" object specify the aggreated columns by the functions
- sum is the aggregation function
- Total is the column name.
- sum:{Total: col2} define a new column "Total" which is the sum of the column "col2". this is equivalent to "sum(col2) as Total" in SQL
- sum:{col2} is equivalent to sum:{col2:col2}
- for "count" aggregate, a bool expression is expected.
- count: {Count:true}
- available aggregate functions: count, sum, avg, min, max, values, dcount, coalesce, first, argmin, argmax

/*
  customerTable will have the columns Customer and S3_Cost
  where S3_Cost is the sum of rows grouped by the same Customer
*/
let customerTable = bucketTable.Aggregate(({Customer, S3_Cost}) =>  {
  return {
    groupBy: {Customer},
    columns: {
      sum: {S3_Cost}
    }
  }
})

// More than one columns can be specified and each column can be aggregated with a different function
let arr = [
  {Item: "Pencil", Cost: 9.99, Quantity: 5},
  {Item: "Eraser", Cost: 3.99, Quantity: 2},
  {Item: "Eraser", Cost: 3.99, Quantity: 2},
  {Item: "Ruler", Cost: 15.99, Quantity: 3},
  {Item: "Pencil", Cost: 1.99, Quantity: 1}
]
let t1 = jsonTable(arr)
let t2 = t1.Aggregate(({Item, Cost, Quantity}) => {
  return {
    groupBy: {Item},
    columns: {
      sum: {TotalCost: Cost},
      sum: {TotalQuantity: Quantity},
      count: {NumberOfSales: true},
      max: {LargestSale: Cost},
      max: {MostSold: Quantity},
      avg: {AverageSale: Cost}
    }
  }
})

Aggregated table t2

Item	TotalCost	TotalQuantity	NumberOfSales	LargestSale	MostSold	AverageSale
Pencil	11.98	6	2	9.99	5	5.99
Eraser	7.98	4	2	3.99	2	3.99
Ruler	15.99	3	2	15.99	3	15.99

argmax and argmin

argmax: {col1, otherColumns...}
- this aggregate function will return the row with the max value of col1 and otherColumns specified as is
- if no otherColumns is given, it will just return the aggregated col1
argmin: {col1, otherColumns...}
- works the same as argmax but returns the row with the min value of col1 instead

let arr = [
  {ID: "a", Col1: "1900", Col2: "abc"},
  {ID: "a", Col1: "1921", Col2: "mno"}
]

let t = jsonTable(arr)
let tg = t.Aggregate( ({ID, Col1, Col2}) => {
  return {
    groupBy:{ID},
    columns:{
      argmax:{Col1, Col2}
    }
  }
})

Table tg after the aggregate function call with argmax

ID	Col1	Col2
a	1921	mno

ColumnAggregate

ColumnAggregate(columnName, unit, (ID, columnName, value, sum) ⇒ { }, initialValue)
- return a new table the same ID column, plus one new column which is the aggregated result from the calling table.
- the aggregated result will be added to the initialValue which must be a Number and is typically 0

// simple sum of all columns from the table named "bucketTable"
// new table "byteSummary" has the same ID column plus one "Total_Bytes" column
let byteSummary = bucketTable.ColumnAggregate("Total_Bytes", "Byte", (ID, col, value, sum) => {
      return sum + value
},0)

// more complicate example, the lambda calls AWSPrice API to get the monthly cost of different S3 storage types.
// the table "bucketTable" is derived from the "DimensionTable" method of a metric stream, each storage type has one column
let costSummary = bucketTable.ColumnAggregate("Total_Monthly_Cost", "Dollar", (ID, col, value, sum) => {
      return sum + AWSPrice("S3", "StorageType", {Size:value, Type:col})
}, 0)

// simple example showing how it works
let arr = [
  {Item: "Pencil", FirstSale: 2, SecondSale: 5},
  {Item: "Eraser", FirstSale: 2, SecondSale: 2},
  {Item: "Ruler", FirstSale: 3, SecondSale: 2},
]
let t1 = jsonTable(arr)
let t2 = t1.columnAggregate("TotalSale", "USD", (ID, col, value, sum) => {
  return sum + value
}, 0)

Table t2 after the column aggregate function call

ID	TotalSale (USD)
Pencil	7
Eraser	4
Ruler	5

NewColumnLambda

NewColumnLambda(columnName, unit, (row) ⇒ { })
- Generates a new column on the calling table. The column value is the return value of the lambda function.

// create a new column "AverageSize"  on table "bucketTable".  The new column will read the two column named "Total_Bytes" and "Total_Object_count" respectively and calculate the the average as column value.
bucketTable.NewColumnLambda("AverageSize", "Byte", (row) => row.Total_Bytes / row.Total_Object_Count)
// OR
bucketTable.NewColumnLambda("AverageSize", "Byte", ({Total_Bytes, Total_Object_Count}) => Total_Bytes / Total_Object_Count)

NewColumns

NewColumns( (row) ⇒ {})
- Generate new columns on the calling table.
- the lambda function will return a object holding the new columns

Example Code:

    let arr1 = [
        {ID: "a", Col1: 3},
        {ID: "b", Col1: 2}
    ]

    let t1 = jsonTable(arr1)

    printf("Before:")
    t1.Each( ({ID,Col1,C2})=> {
        printf("%s, %d, %v",ID,Col1,C2)
    })

    t1.NewColumns( ({Col1}) => {
        let C2 = Col1 * 2
        return {C2}
    })

    printf("After:")
    t1.Each( ({ID,Col1,C2})=> {
        printf("%s, %d, %v",ID,Col1,C2)
    })

Example Output:

printf: Before:
printf: a, 3, undefined
printf: b, 2, undefined
printf: After:
printf: a, 3, 6
printf: b, 2, 4

Clone

Clone()
- Generates a new table which is a copy of the calling table
- This is a deep copy and any changes made to the copied table will not affect the original table

let t2 = t1.Clone() // t2 is a copy of t1

Clone("Col1", "Col2" …)
- Generates a new table which is a copy of the calling table, with only the specified columns
- if no columns are specified, all columns will be copied

GetRow

GetRow(columnMap)
- return row with the specified column values
- undefined if no row matches the values

let arr = [
  {col1: "foo", col2: "bar", col3: "abc"}
]
let t = jsonTable(arr)
let row = t.GetRow({col1:"foo", col2:"bar"}) // row = {col1: "foo", col2: "bar", col3: "abc"}

JoinStream

JoinStream(stream, aggregationType, columnName, unit)
- Generate one new column on the calling table. The column value is the aggregated result of each data series.

function getNatBandwidth(assetTable) {
  let options = {from: "-24h@h", to: "@h", dimensions: "NatGatewayId", namespace: "AWS/NATGateway", period: "1h", unit:"Byte", stat: "Sum"}
  let filters = {NatGatewayId: assetTable}
  let download = AWS_GetMetric("BytesInFromDestination", options, filters)
  let upload = AWS_GetMetric("BytesOutToDestination", options, filters)
  let localUpload = AWS_GetMetric("BytesInFromSource", options, filters)
  let localDownload = AWS_GetMetric("BytesOutToSource", options, filters)
  let totalBytes = download + upload + localUpload + localDownload
  let processCost = AWS_GetPrice("NatGateway", "GB")
  let hourlyCost =  AWS_GetPrice("NatGateway", "Hour")
  let cost = (hourlyCost * 3600 / totalBytes.GetInterval()) +  totalBytes * processCost / (1024 * 1024 * 1024)
  return {download, upload, totalBytes, cost}
}

function main() {
  return AWS_AccountRegionLambda("*", "*", (account, region) => {
    let natGateways = AWS_LoadAsset("ec2:natgateway", (obj) => {
       let {NatGatewayId:ID, State, VpcId} = obj
       let PublicIp = obj.NatGatewayAddresses[0].PublicIp
       return {ID, State, VpcId, PublicIp}
    })

    let {totalBytes} = getNatBandwidth(natGateways)
    natGateways.JoinStream(totalBytes,"Sum", "TotalBytes", "Byte")
    return {natGateways}
  })
}

Table Methods

IsEmpty​

RenameColumn​

RemoveColumn​

GetColumnValues​

GetKeys​

SetColumnUnit​

Sort​

Join​

Append​

Map​

Filter​

Each​

Aggregate​

argmax and argmin​

ColumnAggregate​

NewColumnLambda​

NewColumns​

Clone​

GetRow​

JoinStream​

IsEmpty

RenameColumn

RemoveColumn

GetColumnValues

GetKeys

SetColumnUnit

Sort

Join

Append

Map

Filter

Each

Aggregate

argmax and argmin

ColumnAggregate

NewColumnLambda

NewColumns

Clone

GetRow

JoinStream