Ask Your Question

Hive query does not return results

asked 2018-06-15 11:42:45 -0600

rupal gravatar image

A StreamSets Data Collector pipeline is running and it is inserting records into Hive table(s). However, when running a query in hive to select records, it returns no results.

edit retag flag offensive close merge delete

1 Answer

Sort by ยป oldest newest most voted

answered 2018-06-15 11:46:45 -0600

rupal gravatar image

updated 2018-06-19 13:49:00 -0600

metadaddy gravatar image

Regarding querying data with Hive, this is the reason: When data is being written to HDFS (via Hive) a temporary file is kept open for appending records to it. If you take a look at your hive table path /hive/user/warehouse/<database><table> you'll see _tmp* files. These temporary files are kept 'open' for appending records based on the settings you've in Hadoop FS destination > Output Files:

image description

With default settings, the files will be kept open indefinitely or if the file did not receive records for an hour, then the file will be closed and renamed to a valid name. Ensure you modify these settings based on how small/large files you want created in HDFS. Typical recommendation is at least a block size. Don't leave it as 0. If you update the setting as follows:

image description

The file will be kept open for appending records until any one of the conditions is met. The first condition met will trigger the file to be closed, and hence renamed from _tmp* to sdc_*.

When querying with Hive, each query runs a MapReduce task. A MapReduce job ignores files that are prefixed with an underscore, hence in your case your query doesn't return anything. On stopping the pipeline, the files are closed and your query returns data. Ensure you modify the Hadoop FS settings based on how often you want to query data.

Note: Hive is not meant for real-time data access. If you want find the need to query data in real-time, use HBase or Kudu or other storage options that allow this.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-06-15 11:42:45 -0600

Seen: 1,260 times

Last updated: Jun 19 '18