TL;DR

Although LB doesn’t yet have a built-in facility for browsing data in a workspace, you aren’t limited to just using lb print to dump the contents of individual predicates. You can use commands like

lb exec WORKSPACE --print _ --exclude-ids --csv -f some_file.logic

to run the same complex queries over and over, easily load the results into a spreadsheet, and share queries with your friends. This approach is much easier than using lb print to dump predicates, and is simpler than building TDX services to export data, or introducing maintained logic into a project just for doing ad hoc investigation.

Introduction

This note describes some tools for doing ad hoc querying of an LB workspace. This will allow you to view the data in a workspace without writing a full UI application, or even TDX services. This can be useful as you are developing a system, or when you need to troubleshoot problems.

The LB command line tool contains several tools for retrieving data from an LB workspace. You can run each of these with the –help option to get more information:

  • lb list : list all the predicates in a workspace
  • lb predinfo : print the structure of a single predicate
  • lb print : print the contents of a single predicate
  • lb query : execute a single query against a workspace
  • lb exec : execute LogiQL statements, which can be a simple as a single query.

Often just printing the contents of a single predicate does not provide enough information – you need to combine information from multiple predicates with queries. Both lb exec and lb query can be used to execute a query and return the results. Since lb exec has a couple options that lb query doesn’t have, let’s focus only on lb exec for now.

If you will need to run the same query more than once, it is a good practice to save the query in a file. lb exec has an option to execute logic from a file. Sharing such query files within a project team can be helpful, allowing others to get a better understanding of the overall data model as well as help with troubleshooting. You may want to accumulate a set of such files over the course of a project, and keep them in the project code repository so the entire team has access to them. Make sure to include comments in the query files so others understand their purpose.

Here are the options of lb exec that are most relevant for our purposes:

$ lb exec --help
usage: lb exec [-f FILE] [--raw] [--exclude-ids]
               [--csv] [--print [PRED]]
               WORKSPACE

positional arguments:
  WORKSPACE             name of workspace

optional arguments:
  -f FILE, --file FILE  logic file to execute
  --raw                 print query result without escaping
  --exclude-ids         output only refmode values of entities
  --csv                 print query result to csv files
  --print [PRED]        print local predicate PRED. Default '_'

Setup

Here is the query file used in the following examples.

$ cat collect_vendors.query 

// lb query bn -f collect_vendors.query
_( v, vendor_id, name, city, state ) <-
    vendor:vendor_id[v]      = vendor_id,
    vendor:name[v]           = name,
    vendor:city[v]           = city,
    vendor:state[v]          = state,
    vendor:collect_vendors(v)    // Prefix this line with ! to get the opposite set of vendors.

As you might guess, the data model looks like

vendor(v), vendor_id(v:id) -> string(id).
name[v] = s -> vendor(v), string(s).
city[v] = c -> vendor(v), string(c).
state[v] = s -> vendor(v), string(s).
collect_vendors(v) -> vendor(v).      // A list of specific vendors.

Examples

Here’s the simplest case, execute a query with the results going to stdout.

$ lb exec bn --print _ -f collect_vendors.query 
/--------------- _ ---------------\
[10000089034] "1089655" "1089655" "VENDOR A"         "CAMBRIDGE"       "MA"
[10000089275] "1126036" "1126036" "VENDOR B"         "PORTLAND"        "OR"
[10000089287] "1126432" "1126432" "VENDOR C"         "ATLANTA"         "GA"
[10000089798] "12079"   "12079"   "VENDOR D"         "AUSTIN"          "TX"
[10000089824] "121219"  "121219"  "VENDOR E"         "COLUMBUS"        "MO"
\--------------- _ ---------------/

Notice the header and footer. Although they are not very helpful for these particular examples, they are helpful as separators if the results contain multiple predicates. Note that the result predicate name, _, is embedded in the separators.

Add –raw to get rid of the quotes around each field.

$ lb exec bn --print _ --raw -f collect_vendors.query 
/--------------- _ ---------------\
[10000089034] 1089655 1089655 VENDOR A         CAMBRIDGE       MA
[10000089275] 1126036 1126036 VENDOR B         PORTLAND        OR
[10000089287] 1126432 1126432 VENDOR C         ATLANTA         GA
[10000089798] 12079   12079   VENDOR D         AUSTIN          TX
[10000089824] 121219  121219  VENDOR E         COLUMBUS        MO
\--------------- _ ---------------/

Add –exclude-ids to get drop entity IDs, and only print their refmode values:

$ lb exec bn --print _ --raw --exclude-ids -f collect_vendors.query 
/--------------- _ ---------------\
1089655 1089655 VENDOR A         CAMBRIDGE       MA
1126036 1126036 VENDOR B         PORTLAND        OR
1126432 1126432 VENDOR C         ATLANTA         GA
12079   12079   VENDOR D         AUSTIN          TX
121219  121219  VENDOR E         COLUMBUS        MO
\--------------- _ ---------------/

Add –csv to get the result written to a CSV text file, without the dashed header and footer row.

$ lb exec bn --print _ --raw --exclude-ids --csv -f collect_vendors.query 
/--------------- _ ---------------\
Output written to _.csv
\--------------- _ ---------------/

$ cat _.csv
1089655,1089655,VENDOR A        ,CAMBRIDGE      ,MA
1126036,1126036,VENDOR B        ,PORTLAND       ,OR
1126432,1126432,VENDOR C        ,ATLANTA        ,GA
12079  ,12079  ,VENDOR D        ,AUSTIN         ,TX
121219 ,121219 ,VENDOR E        ,COLUMBUS       ,MO
$ 

If you need to load your results into a spreadsheet your best bet may be to use csv mode but exclude IDs. (I’m not sure how quotes within a data value are handled.)

$ lb exec bn --print _ --exclude-ids --csv -f collect_vendors.query 
/--------------- _ ---------------\
Output written to _.csv
\--------------- _ ---------------/
$ cat _.csv
"1089655","1089655","VENDOR A"        ,"CAMBRIDGE"      ,"MA"
"1126036","1126036","VENDOR B"        ,"PORTLAND"       ,"OR"
"1126432","1126432","VENDOR C"        ,"ATLANTA"        ,"GA"
"12079"  ,"12079"  ,"VENDOR D"        ,"AUSTIN"         ,"TX"
"121219" ,"121219" ,"VENDOR E"        ,"COLUMBUS"       ,"MO"
$

Summary

Saving queries in files, and using lb exec, can be a big time saver, and reduce duplicate work across a project team. You may even want to bundle relevant queries in deployments, at least during development and testing phases of a project, so that useful queries are available when troubleshooting on remote servers.

0 Comments

Leave a reply

©2017 LogicBlox

Log in with your credentials

Forgot your details?