外部表格不会返回其文件夹中的数据 [英] External table does not return the data in its folder

问题描述

  CREATE EXTERNAL TABLE tb 
（
 ... 
）
 PARTITIONED BY（datehour INT）
行格式SERDE'com.cloudera.hive.serde.JSONSerDe'
 LOCATION'/ user / cloudera /数据'; 
  

数据存在于文件夹中，但是当我查询表格时，它不会返回任何内容。该表的结构适合数据结构。

  SELECT * FROM tb LIMIT 3; 
  

Hive表存在一种权限问题：特定用户是否有权查询某些表？
您知道一些解决方案或解决方法吗？

解决方案

您已将表格创建为分区表基于 datehour 列，但是您将数据放入 / user / cloudera / data 中。 Hive将在 / user / cloudera / data / datehour =（某些int值）中查找数据。由于它是外部表格配置单元不会更新Metastore。你需要运行一些alter语句来更新它。

因此，下面是使用分区的外部表的步骤：

1.在您的外部位置 / user / cloudera / data 中，创建一个目录 datehour = 0909201401

<$ p使用LOAD DATA [LOCAL] INPATH'加载数据$ p $ OR


/ path / to / data / file'INTO TABLE分区（datehour = 0909201401）

2）创建表之后，运行alter语句：
ALTER TABLE ADD PARTITION（datehour = 0909201401）

希望它有帮助... !!!

I have created an external table in Hive with at this location :

CREATE EXTERNAL TABLE tb 
(
...
) 
PARTITIONED BY (datehour INT)
ROW FORMAT SERDE 'com.cloudera.hive.serde.JSONSerDe'
LOCATION '/user/cloudera/data';

The data is present in the folder but when I query the table, it returns nothing. The table is structured in a way that it fits the data structure.

SELECT * FROM tb LIMIT 3;

Is there a kind of permission issue with Hive tables: do specific users have permissions to query some tables? Do you know some solutions or workarounds?

解决方案

You have created your table as partitioned table base on column datehour, but you are putting your data in /user/cloudera/data. Hive will look for data in /user/cloudera/data/datehour=(some int value). Since it is an external table hive will not update the metastore. You need to run some alter statement to update that

So here are the steps for external tables with partition:

1.) In you external location /user/cloudera/data, create a directory datehour=0909201401

                                OR

Load data using: LOAD DATA [LOCAL] INPATH '/path/to/data/file' INTO TABLE partition(datehour=0909201401)

2.) After creating your table run a alter statement: ALTER TABLE ADD PARTITION (datehour=0909201401)

Hope it helps...!!!

这篇关于外部表格不会返回其文件夹中的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！