为什么布尔字段在Hive中不起作用? [英] why boolean field is not working in Hive?

问题描述

我的配置单元表中有一列，其数据类型为布尔值.当我尝试从csv导入数据时，它存储为NULL.

I have a column in my hive table which datatype is boolean. when I tried to import data from csv, it stored as NULL.

这是我的示例表:

CREATE tABLE if not exists Engineanalysis(

EngineModel String,

EnginePartNo String ,

Location String,

Position String,

InspectionReq boolean)

ROW FORMAT DELIMITED

FIELDS TERMINATED BY ','

LINES TERMINATED BY '\n';

我的样本数据:

AB01,AS01-IT01,AIRFRAME,,0

AB02,AS01-IT02,AIRFRAME,,1

AB03,AS01-IT03,AIRFRAME,,1

AB04,AS01-IT04,AIRFRAME,,1

AB05,AS01-IT05,HEAD,,1

AB06,AS01-IT06,HEAD,,0

AB07,AS01-IT07,HEAD,,0

AB08,AS01-IT08,HEAD,,0

AB09,AS01-IT09,NOSE,,1

AB10,AS01-IT10,NOSE,,0

结果:

AB01 AS01-IT01 AIRFRAME NULL

AB02 AS01-IT02 AIRFRAME NULL

AB03 AS01-IT03 AIRFRAME NULL

AB04 AS01-IT04 AIRFRAME NULL

AB05 AS01-IT05 HEAD NULL

AB06 AS01-IT06 HEAD NULL

AB07 AS01-IT07 HEAD NULL

AB08 AS01-IT08 HEAD NULL

AB09 AS01-IT09 NOSE NULL

AB10 AS01-IT10 NOSE NULL

手动加载时:

insert into Engineanalysis select 'AB11','AS01-IT11','AIRFRAME','',0;

结果:

AB11 AS01-IT11 AIRFRAME false

有人可以解释为什么这种差异吗?

can someone explain why this dissimilarity?

推荐答案

布尔类型需要文字表示.SQL标准仅为布尔值定义三个值:TRUE，FALSE和UNKNOWN(在Hive中为NULL).尽管许多数据库都支持整数，但SQL中并未使用整数进行标准化.

Boolean type needs literals representation. SQL standard defines only three values for boolean: TRUE, FALSE, and UNKNOWN(=NULL in Hive). Using integers is not standardized in SQL, though many databases supports them.

您正在使用LazySimpleSerDe反序列化表数据. LazySimpleSerDe 使用此属性 hive.lazysimple.extended_boolean_literal 以确定除将"T"，"t"，"F"，"f"，"1"和"0"视为扩展的合法布尔文字外，是否还要将其视为"T"，"t"，"t"，"f"，"1"和"0"'真假'.默认值为false，这意味着只有'TRUE'和'FALSE'被视为合法的布尔文字.

You are using LazySimpleSerDe for deserealizing table data. LazySimpleSerDe uses this property hive.lazysimple.extended_boolean_literal to determine if it treats 'T', 't', 'F', 'f', '1', and '0' as extended, legal boolean literals, in addition to 'TRUE' and 'FALSE'. The default is false, which means only 'TRUE' and 'FALSE' are treated as legal boolean literals.

将此属性设置为能够读取以 1 和 0 作为布尔值的CSV文件:

Set this property to be able to read CSV files with 1 and 0 as Booleans:

hive.lazysimple.extended_boolean_literal=true;

请参阅此Jira HIVE-3635 也可以尝试在表DDL:

See this Jira HIVE-3635 Try also to set this property in the table DDL:

TBLPROPERTIES("hive.lazysimple.extended_boolean_literal" ="true")

关于在Hive查询语言中使用非TRUE或FALSE布尔文字，官方文档说不可能将其他类型隐式转换为Boolean:

About using other than TRUE or FALSE boolean literals in Hive query language official documentation says implicit conversion of other types to Boolean is not possible: AllowedImplicitConversions though as you can see it works.

很少有将字符串和整数转换为布尔值的测试:

Few tests with casting strings and integers to boolean:

hive> select cast('' as boolean);
OK
false
Time taken: 8.642 seconds, Fetched: 1 row(s)
hive> select cast('1' as boolean);
OK
true
Time taken: 4.773 seconds, Fetched: 1 row(s)
hive> select cast('f' as boolean);
OK
true
Time taken: 8.548 seconds, Fetched: 1 row(s)
hive> select cast('0' as boolean);
OK
true
Time taken: 0.851 seconds, Fetched: 1 row(s)
hive> select cast(0 as boolean);
OK
false
Time taken: 1.713 seconds, Fetched: 1 row(s)
hive> select cast(1 as boolean);
OK
true
Time taken: 4.604 seconds, Fetched: 1 row(s)

也请看一下这个Jira: HIVE-3604 和<一个href ="https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-TypeConversionFunctions" rel ="nofollow noreferrer">类型转换函数文档，其中说:如果cast(expr为boolean)，Hive对于非空字符串返回true.实际上，这与

Also have a look at this Jira: HIVE-3604 and Type Conversion Functions documentation, it says: If cast(expr as boolean) Hive returns true for a non-empty string. And actually this conforms with UDFToBoolean source code.

因此，最好对布尔类型使用正式允许的文字，以确保您也没有本文也描述的副作用:蜂巢:布尔值太混乱了，无法使用

So, better use officially allowed literals for Boolean type to make sure you have no side effects also described in this article: Hive: Booleans Are Too Confusing To Be Usable

这篇关于为什么布尔字段在Hive中不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！