Bit value process in Spark & Postgresql

Question

When I try write Spark SQL with bit(), it result in an error.

Code

val tpos_dfr = sqlContext.sql("SELECT idcustomer, quantity, CASE WHEN quantity < 0 THEN 1::bit(1) ELSE 0::bit(1) END as isthisreturn FROM pointofsale WHERE iddataloadmanager = 10 and quantity <> 0")

Error

Exception in thread "main" org.apache.spark.sql.catalyst.parser.ParseException:
extraneous input 'quantity' expecting {, ',', 'FROM', 'WHERE', GROUP', 'ORDER', 'HAVING', 'LIMIT', 'LATERAL', 'WINDOW', 'UNION', 'EXCEPT', 'INTERSECT', 'SORT', 'CLUSTER', 'DISTRIBUTE'}(line 1, pos 122)

`sqlContext.sql` creates Spark SQL query, not a PostgreSQL one. — zero323, Sep 26 '16 at 13:06
@zero323, is it possible to execute PostgresSQL with any other method — Jai, Sep 26 '16 at 14:24
Statically. See http://stackoverflow.com/a/38729672 or http://stackoverflow.com/a/35870245/1560062 for example. — zero323, Sep 26 '16 at 18:54

score 1 · Answer 1 · answered Sep 26 '16 at 11:53

You could use a boolean expression, and use the resulting boolean value instead of a bit, so try

SELECT 
    idcustomer, 
    quantity, 
    quantity < 0 as isthisreturn 
  FROM 
    pointofsale 
  WHERE
    iddataloadmanager = 10 and quantity <> 0

then a boolean data type should appear, if you call printSchema() on the data frame.

Bit value process in Spark & Postgresql

Code

Error

1 Answers1