question Re: Getting all NULLS when selecting from a Hive JSON table in Archives of Support Questions (Read Only)

Getting all NULLS when selecting from a Hive JSON table

SQLShaw — Mon, 19 Aug 2019 09:26:19 GMT

I’m trying to get some twitter JSON to show up in Hive and I’m not having any luck. All I get are NULLS returned and no errors. I've tried the native JSON serde as well as the openx serde but get the same results.

LOAD DATA INPATH '/tmp/tweets_staging/‘ OVERWRITE INTO tweets;

ADD JAR /hadoop/json-serde-1.3.8-SNAPSHOT-jar-with-dependencies.jar;

SELECT * FROM tweets LIMIT 100;

tweet-table.txt

Re: Getting all NULLS when selecting from a Hive JSON table

clukasik — Wed, 04 May 2016 21:22:21 GMT

You might want to check that the JSON is legitimate. You can do this with Spark (spark-sql). Here's what I'd do in spark-sql:

Create temp table over the JSON files
See if "select " statement returns desired results
Describe the temp table and sanity check the schema against what you were using in Hive

To create the temp table in Spark:

CREATE TEMPORARY TABLE tweets_temp
USING org.apache.spark.sql.json
OPTIONS (path '[the path to the JSON dataset]')

Re: Getting all NULLS when selecting from a Hive JSON table

bluesmix — Thu, 05 May 2016 01:46:24 GMT

I suppose is the issue with loading data.

Try to create external table instead..

create EXTERNAL table tweets 
....

row format serde 'org.openx.data.jsonserde.JsonSerDe'
LOCATION '/tmp/tweets_staging/';