Is there a way to get a boolean value using 0 and 1 in Athena?-CodePudding

I have a CSV file that I want to query using Amazon Athena. The problem I am running into is that there is a boolean value that uses 0 for false and 1 for true. I thought that the Athena SerDe would be able to parse this but the field is coming up as blank when I query the table so it would appear that it doesn't. If there is a way for me to parse 0 and 1 as boolean values in Athena can someone please let me know? I would rather not edit the CSV file itself as it is very large and that would be very time consuming.

CodePudding user response：

you can use CASE WHEN to evaluate the value and produce the desired output by comparing it to null or 0. For example:

SELECT 
   CASE 
      when (id isnull or id = 0) THEN 'FALSE' 
      ELSE 'TRUE' 
   END BooleanOutput 
FROM students;

Produces the output:

FALSE
TRUE
TRUE

Using the source code below or from this link for live example.

-- create a table
CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  gender TEXT NOT NULL
);
-- insert some values
INSERT INTO students VALUES (0, 'Felipe', 'M');
INSERT INTO students VALUES (1, 'Ryan', 'M');
INSERT INTO students VALUES (2, 'Joanna', 'F');
-- fetch some values
SELECT CASE when (id isnull or id = 0) THEN 'FALSE' ELSE 'TRUE' END BooleanOutput FROM students;

CodePudding user response：

You can use the IF function. If your column is called zero_or_one you could write this:

SELECT IF(zero_or_one, 1) AS true_or_false
FROM …

to convert the number 1 to TRUE and all other values to FALSE. If your column is a string intsead do IF(zero_or_one, '1').