Bitwise operation support in omniscidb

Follow

Dipanwita Mallick

• December 02, 2020 21:21

Does omniScidB support bitwise operation? For example, if I want to extract the first 32 bits from a number using a SQL query, I will simply do:

"SELECT time_stamp, col_a, (col_a & 0x00000000ffffffff) as 'bit-31-0' FROM table order by time_stamp;"

Running the same query in OmniSci dB results in an error saying: "Exception: Parse failed: Lexical error at line 1, column 50. Encountered: "&" (38), after : "" unable to rollback\

1

• 

  Candido Dessanti

  • December 03, 2020 16:03

  Hi @dipanwita2019,

  As today omniscidb doesn't support bitwise operators like AND, OR, SHIFT.

  I think implements them with standard syntax; it's not an easy task because of the calcite implementation.

  Anyway, you can try to use this script that would define AND/OR/RIGHT SHIFT/LEFT SHIFT/NOT as UDF (User Defined Functions)

  the operations defined are

  • bitwise_or
  • bitwise_and
  • bitwise_lshift
  • bitwise_rshift
  • bitwise_xor
  • bitwise_not

  udf_bitwise.txt|attachment (2,4 KB) to use them, you have to place the file into the server and rename into udf_bitwise.cpp where omnisci is running, then run omnisci server adding 2 udf parameters e.g.

  /omnisci_server --data /mapd_storage/data_test/ --udf "/mapd_storage/udf_bitwise.cpp" --enable-runtime-udf true if running omniscidb as a service you have to add the UDF and ENABLE-RUNTIME-UDF into omnisci.conf file

  all functions except bitwise_not take 2 arguments.

  select bitwise_and(depdelay,arrdelay),bitwise_or(depdelay,arrdelay),bitwise_xor(depdelay,arrdelay),bitwise_lshift(depdelay,2),bitwise_rshift(depdelay,2),bitwise_not(depdelay),depdelay from flights_sk;

  everything looks to works on projections, aggregates when running the server in CPU mode, but there could be issues in GPU mode.

  Anyway, take this solution as a temporary one.

  I just changed the udf_bitwise.txt file, with corrections needed to run on a Gpu Build. I forgot to say, that to be able to use UDF on database clang has to be installed into the system

  0

  Comment actions Permalink

Please sign in to leave a comment.