HIVE - Error loading twitter JSON data

2
votes

Hive path =/usr/local/hive/

Hadoop path = /usr/local/hadoop/

Hadoop version = 2.6.0

Hive version = 2.3.2

I have added .jar in /lib directory of both paths and HDFS in /input

Download link = here (hive-serdes-1.0-SNAPSHOT)

I added the .jar file in Hive shell add jar /usr/local/hive/lib/hive-serdes-1.0-SNAPSHOT.jar;

While creating an external table to store the data from the JSON file, I get the following error

CREATE EXTERNAL TABLE twitter(id BIGINT,text STRING) ROW FORMAT SERDE 'com.cloudera.hive.serde.JSONSerDe' LOCATION '/input/';

Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. org/apache/hadoop/hive/serde2/SerDe

LOG FILE -

> 2018-01-24T19:57:40,386  INFO [e81a3c51-48a3-49e9-8121-e50b1ca97a90 main] ql.Driver: Executing command(queryId=infoobjects_20180124195740_04de95b6-9188-4b4e-9561-66c9db233cb9): create external table twitter(id BIGINT,text STRING) ROW FORMAT SERDE 'com.cloudera.hive.serde.JSONSerDe' LOCATION '/input/'
2018-01-24T19:57:40,387  INFO [e81a3c51-48a3-49e9-8121-e50b1ca97a90 main] ql.Driver: Starting task [Stage-0:DDL] in serial mode
2018-01-24T19:57:40,388 ERROR [e81a3c51-48a3-49e9-8121-e50b1ca97a90 main] exec.DDLTask: java.lang.NoClassDefFoundError: org/apache/hadoop/hive/serde2/SerDe
    at java.lang.ClassLoader.defineClass1(Native Method)
    at java.lang.ClassLoader.defineClass(ClassLoader.java:763)
    at java.security.SecureClassLoader.defineClass(SecureClassLoader.java:142)
    at java.net.URLClassLoader.defineClass(URLClassLoader.java:467)
    at java.net.URLClassLoader.access$100(URLClassLoader.java:73)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:368)
    at java.net.URLClassLoader$1.run(URLClassLoader.java:362)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.net.URLClassLoader.findClass(URLClassLoader.java:361)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:338)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:411)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    at java.lang.Class.forName0(Native Method)
    at java.lang.Class.forName(Class.java:348)
    at org.apache.hadoop.conf.Configuration.getClassByNameOrNull(Configuration.java:2013)
    at org.apache.hadoop.conf.Configuration.getClassByName(Configuration.java:1978)
    at org.apache.hadoop.hive.ql.exec.DDLTask.validateSerDe(DDLTask.java:4213)
    at org.apache.hadoop.hive.ql.plan.CreateTableDesc.toTable(CreateTableDesc.java:723)
    at org.apache.hadoop.hive.ql.exec.DDLTask.createTable(DDLTask.java:4321)
    at org.apache.hadoop.hive.ql.exec.DDLTask.execute(DDLTask.java:354)
    at org.apache.hadoop.hive.ql.exec.Task.executeTask(Task.java:199)
    at org.apache.hadoop.hive.ql.exec.TaskRunner.runSequential(TaskRunner.java:100)
    at org.apache.hadoop.hive.ql.Driver.launchTask(Driver.java:2183)
    at org.apache.hadoop.hive.ql.Driver.execute(Driver.java:1839)
    at org.apache.hadoop.hive.ql.Driver.runInternal(Driver.java:1526)
    at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1237)
    at org.apache.hadoop.hive.ql.Driver.run(Driver.java:1227)
    at org.apache.hadoop.hive.cli.CliDriver.processLocalCmd(CliDriver.java:233)
    at org.apache.hadoop.hive.cli.CliDriver.processCmd(CliDriver.java:184)
    at org.apache.hadoop.hive.cli.CliDriver.processLine(CliDriver.java:403)
    at org.apache.hadoop.hive.cli.CliDriver.executeDriver(CliDriver.java:821)
    at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:759)
    at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:686)
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.hadoop.util.RunJar.run(RunJar.java:221)
    at org.apache.hadoop.util.RunJar.main(RunJar.java:136)
Caused by: java.lang.ClassNotFoundException: org.apache.hadoop.hive.serde2.SerDe
    at java.net.URLClassLoader.findClass(URLClassLoader.java:381)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:424)
    at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:338)
    at java.lang.ClassLoader.loadClass(ClassLoader.java:357)
    ... 40 more
2018-01-24T19:57:40,388 ERROR [e81a3c51-48a3-49e9-8121-e50b1ca97a90 main] ql.Driver: FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.DDLTask. org/apache/hadoop/hive/serde2/SerDe

I apologize for any mistake, it's my first question here (as I couldn't find a solution on the net). Thanks in advance.

UPDATE: Ali's(accepted) answer worked for me. In addition, I also had to re-format my JSON to contain single line JSON objects.

2
jsonhadoophivehiveql
There are multiple similar questions in the stack with no accepted answers. As the exception is class not found, I would suggest few thing to check, one is to check if the jar file has proper permissions or not and then I would suggest you to add the jar from hive/beeline shell before executing the DDL. – roh
"I added the .jar file in Hive"... Where and how? – OneCricketeer
@rob Exactly my reason to post the question. I tried both the options, same issue. (adding jar from shell and .jar permissions) @cricket_007 (from the hive shell) add jar /usr/local/hive/lib/hive-serdes-1.0-SNAPSHOT.jar; – Varun Chaudhary
Hey @VarunChaudhary did you find a solution to your problem? As I have a similar issue – Ali
@Ali Nope. I tried all that I found on the internet with no success. – Varun Chaudhary

2 Answers

0
votes

I finally found it.

Starting from Hive 0.12 it come with a built-in

JsonSerDe (Hive 0.12 and later in hcatalog-core).

All the serde we were using are not compatible with the versions we use (in my case Hive 2.3.2)

You can add the jar corresponding to your version add jar HIVE_HOME/lib/hive-hcatalog-core-2.3.2.jar and then on your query change 'com.cloudera....' by

ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'

Hope it helps

0
votes

I have same error with you, but when i modify to "ROW FORMAT SERDE 'org.apache.hive.hcatalog.data.JsonSerDe'", It has succeed but when i select * from tables; this only show empty table; "hive> SELECT count(*) FROM tweets; OK 0"