A special table type is used to handle database table lookups with optional caching in memory. All of the following column references may be either numeric (column index, starts at 0) or string (column name).

The table lookup only supports int, string and date types.

All functions without the parameter disableCommit=false will not be auto commit after the function has executed.

The following functions for Database Table described here are:

sqlExec

The sqlExec function is used when updating and inserting data into tables. It returns an integer value stating how many rows were updated/inserted. SQL errors will cause runtime errors (the workflow aborts).

This function can use for execute create or drop table statement.

int sqlExec (
 string dbProfile, 
 string sqlQuery )

tableCreate

Returns a table that holds the result of a database query. SQL errors in the table lookup will cause runtime errors (workflow aborts).

table tableCreate ( 
 string dbProfile, 
 string sqlQuery 
 boolean disableCommit)

initialize {  
  table myTab = tableCreate("myFolder.myProfile", "select user from subscribers" );  
 }

kustoTableCreate

The kustoTableCreate function looks up information in Kusto databases. The resulting table is of the same type as that resulting from tableCreate, meaning that it can be used together with functions such as tableLookup, tableGet etc.

The format of this function is the following: 

table kustoTableCreate(string profileName, string, databaseName, string query)

table result = kustoTableCreate("Default.AZDLookupDb", "lookupdb", "test  | where isnotnull(BeginLocation) | project EpisodeNarrative, EventNarrative | limit 5000");

The Kusto data types will be interpreted as the following types in the resulting table object:

tableCreateIndex

Creates an index for one or several columns of a table. This will greatly increase the efficiency of subsequent tableLookup calls on these columns using the equality operator. If the column already has an index, this function has no effect.

void tableCreateIndex ( 
 table tableValue, 
 int|string column1, ... int|string columnN )

initialize {  
  table myTab = tableCreate("myFolder.myProfile", "select id, user from anum" );  
  tableCreateIndex( myTab, "id" );  
 }

tableGet

The tableGet function returns the value of a table entry. The value is returned as an any object, which means the returned value is of the same type as the value extracted.

any tableGet ( 
 table tableValue, 
 int row, 
 int|string column)

tableLookup

The tableLookup function returns a table containing all the rows of the original table matching the specified column value(s). At least one pair of (column, operator, value) group must be specified.

table tableLookup
( table tableValue, 
 int|string column1, 
 string operator1, 
 string|date|int value1, 
 any value1a, //Optional (used only for "between" and "not between") ... 
 int|column columnN, 
 string operatorN, 
 any valueN, 
 any valueNa ) //Optional (used only for "between" and "not between")

table myTab; 
// To avoid performance problems, the table is read 
// from the database once for each time the 
// workflow is activated. 
initialize { 
 myTab = tableCreate( "myFolder.myProfile", "select user from subscribers" ); 
}
consume {  
  table user = tableLookup( myTab, "user", "=", input.anum );  
  if ( tableRowCount( user ) > 0) {  
  // At least one entry found.  
  } else {  
  // No entry found.  
  }  
 }

table myTab;
initialize {
 myTab = tableCreate("myFolder.myProfile","select date_field from all_dates");
}
consume { 
 date dateFilter;
 //The matching value must contain date, time, and timezone.
 strToDate(dateFilter,"01/05/2016 00:00:00","dd/MM/yyyy HH:mm:ss","CET"); 
 table myLookup = tableLookup(myTab,"date_field","=",dateFilter); 
 
 if ( tableRowCount( myLookup ) > 0) { 
 // At least one entry found. 
 } else { 
 // No entry found. 
 } 
}

tableRowCount

The tableRowCount function returns the number of rows in a table.

int tableRowCount (table tableValue )