arrays - 在配置单元中的数组内查询
问题描述
我已经看到了这一点,但它不适用于我的数据。
我有这个数据:
1, John, a@com;b@com2,32
2, Jack, ab@com;c@com2,33
并通过以下方式将它们加载到蜂巢:
create table t7(id int,name string, email Array<string>, age int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION items terminated by ';'
STORED AS textfile;
Load data inpath '/user/maria_dev/7.txt' into table t7;
和选择输出
那么,我错过了什么?
解决方案
你的不起作用的原因是你在第一个元素之前有空格,所以你必须使用 trim
select * from t7 where trim(email[0]) like "%a@%";
CREATE TABLE `t7`(
`id` int,
`name` string,
`email` array<string>,
`age` int)
ROW FORMAT DELIMITED
FIELDS TERMINATED BY ','
COLLECTION ITEMS TERMINATED BY '\;'
hive> select * from t7 where trim(email[0])="a@com";
OK
1 John [" a@com","b@com2"] 32
hive> select * from t7 LATERAL VIEW explode(email) exploded_table as id_email where id_email like "%com2%";
OK
1 John [" a@com","b@com2"] 32 b@com2
2 Jack [" ab@com","c@com2"] 33 c@com2
推荐阅读
- python - 浏览器图像调整大小,优于最高 PIL 质量
- spring - How to permit single url in spring security
- symfony - Symfony4 How to get objects with query builder
- spring - Spring Security - 过滤器排序和多个 HttpSecurity
- python - 在 Pandas 系列的行(单词列表)和给定列表之间查找匹配的元素
- c - 无法执行 netcat
- r - 从日期范围将总计分配给正确的月份
- sql-server - SQL 具有和圆形数据类型
- r - 提取R中两个逗号之间的单词
- php - PDO query updating a datetime column not in query