Create and Insert to Hive table example

Create table :

hive> CREATE TABLE students (name VARCHAR(64), age INT, gpa DECIMAL(3, 2));

OK
Time taken: 1.084 seconds

List tables :

hive> show tables;
OK
students
values__tmp__table__1
Time taken: 0.023 seconds, Fetched: 2 row(s)

Describe table :

hive> describe students;

OK

name                 varchar(64)

age                 int

gpa                 decimal(3,2)

Time taken: 0.052 seconds, Fetched: 3 row(s)

Insert sample data to the above created table :

hive> INSERT INTO TABLE students VALUES (‘ABC’, 35, 1.28), (‘DEF’, 32, 2.32), (‘KLM’, 37, 3.22);
Query ID = root_20180206225557_c24602db-d9bf-480c-ac98-ece3fe4381e8
Total jobs = 1
Launching Job 1 out of 1
Status: Running (Executing on YARN cluster with App id application_1517954394617_0001)


Show data from the above table

hive> select * from students;

OK

ABC 35 1.28

DEF 32 2.32

KLM 37 3.22

Time taken: 0.212 seconds, Fetched: 3 row(s)

Advertisements

Exception when creating hive table from hdfs parquet file

Problem

FAILED: SemanticException Cannot find class ‘parquet.hive.DeprecatedParquetInputFormat’

Solution

[hadoop@ip-123-45-67-890 extjars]$mkdir extjars

[hadoop@ip-123-45-67-890 extjars]$cd extjars/

Now Download required jars:

[hadoop@ip-123-45-67-890 extjars]$for f in parquet-avro parquet-cascading parquet-column parquet-common parquet-encoding parquet-generator parquet-hadoop parquet-hive parquet-pig parquet-scrooge parquet-test-hadoop2 parquet-thrift
do
curl -O https://oss.sonatype.org/service/local/repositories/releases/content/com/twitter/${f}/1.2.4/${f}-1.2.4.jar
done
curl -O https://oss.sonatype.org/service/local/repositories/releases/content/com/twitter/parquet-format/1.0.0/parquet-format-1.0.0.jar

 

[hadoop@ip-123-45-67-890 extjars]$ ls -ltr
total 5472
-rw-rw-r– 1 hadoop hadoop 891821 Dec 12 23:05 parquet-avro-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 13205 Dec 12 23:05 parquet-cascading-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 648465 Dec 12 23:05 parquet-column-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 10934 Dec 12 23:05 parquet-common-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 271868 Dec 12 23:05 parquet-encoding-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 10102 Dec 12 23:05 parquet-generator-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 1085440 Dec 12 23:05 parquet-hadoop-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 70280 Dec 12 23:05 parquet-hive-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 1019306 Dec 12 23:05 parquet-pig-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 6008 Dec 12 23:05 parquet-scrooge-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 1758 Dec 12 23:05 parquet-test-hadoop2-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 1107251 Dec 12 23:05 parquet-thrift-1.2.4.jar
-rw-rw-r– 1 hadoop hadoop 440035 Dec 12 23:05 parquet-format-1.0.0.jar

Copy downloaded jars to hadoop :

[hadoop@ip-123-45-67-890 extjars]$ hadoop fs -mkdir /home/hadoop/extjars

[hadoop@ip-123-45-67-890 extjars]$ hadoop fs -copyFromLocal extjars/* /home/hadoop/extjars/

[hadoop@ip-123-45-67-890 extjars]$ hadoop fs -ls /home/hadoop/extjars
Found 13 items
-rw-r–r– 3 hadoop hadoop 891821 2017-12-12 23:24 /home/hadoop/extjars/parquet-avro-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 13205 2017-12-12 23:24 /home/hadoop/extjars/parquet-cascading-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 648465 2017-12-12 23:24 /home/hadoop/extjars/parquet-column-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 10934 2017-12-12 23:24 /home/hadoop/extjars/parquet-common-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 271868 2017-12-12 23:24 /home/hadoop/extjars/parquet-encoding-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 440035 2017-12-12 23:24 /home/hadoop/extjars/parquet-format-1.0.0.jar
-rw-r–r– 3 hadoop hadoop 10102 2017-12-12 23:24 /home/hadoop/extjars/parquet-generator-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 1085440 2017-12-12 23:24 /home/hadoop/extjars/parquet-hadoop-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 70280 2017-12-12 23:24 /home/hadoop/extjars/parquet-hive-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 1019306 2017-12-12 23:24 /home/hadoop/extjars/parquet-pig-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 6008 2017-12-12 23:24 /home/hadoop/extjars/parquet-scrooge-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 1758 2017-12-12 23:24 /home/hadoop/extjars/parquet-test-hadoop2-1.2.4.jar
-rw-r–r– 3 hadoop hadoop 1107251 2017-12-12 23:24 /home/hadoop/extjars/parquet-thrift-1.2.4.jar

Run hive console and add jars with fully quaified path:

[hadoop@ip-123-45-67-890 extjars]$ hive

hive>add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-avro-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-avro-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-avro-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-cascading-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-cascading-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-cascading-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-column-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-column-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-column-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-common-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-common-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-common-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-encoding-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-encoding-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-encoding-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-format-1.0.0.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-format-1.0.0.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-format-1.0.0.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-generator-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-generator-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-generator-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-hadoop-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-hadoop-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-hadoop-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-hive-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-hive-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-hive-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-pig-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-pig-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-pig-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-scrooge-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-scrooge-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-scrooge-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-test-hadoop2-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-test-hadoop2-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-test-hadoop2-1.2.4.jar]
hive> add jar hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-thrift-1.2.4.jar;
Added [/mnt/tmp/e941b6f7-f294-4f00-b120-844a6f5418de_resources/parquet-thrift-1.2.4.jar] to class path
Added resources: [hdfs://xxx.xx.xx.xxx:8020/home/hadoop/extjars/parquet-thrift-1.2.4.jar]

 

 

Solved: Hive work directory creation issue

 

Exception:

smartechie:~ sudhir.pradhan$ hive

hiveSLF4J: Class path contains multiple SLF4J bindings.SLF4J: Found binding in [jar:file:/usr/local/Cellar/hive/2.3.1/libexec/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]SLF4J: Found binding in [jar:file:/usr/local/Cellar/hadoop/2.8.0/libexec/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Logging initialized using configuration in jar:file:/usr/local/Cellar/hive/2.3.1/libexec/lib/hive-common-2.3.1.jar!/hive-log4j2.properties Async: trueException in thread “main” java.lang.IllegalArgumentException: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D at org.apache.hadoop.fs.Path.initialize(Path.java:254) at org.apache.hadoop.fs.Path.<init>(Path.java:212) at org.apache.hadoop.hive.ql.session.SessionState.createSessionDirs(SessionState.java:659) at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:582) at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:549) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:750) 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:234) at org.apache.hadoop.util.RunJar.main(RunJar.java:148)Caused by: java.net.URISyntaxException: Relative path in absolute URI: ${system:java.io.tmpdir%7D/$%7Bsystem:user.name%7D at java.net.URI.checkPath(URI.java:1823) at java.net.URI.<init>(URI.java:745) at org.apache.hadoop.fs.Path.initialize(Path.java:251) … 12 more

 

Solution :

<property>
<name>hive.exec.scratchdir</name>
<value>/tmp/hive-${user.name}</value>
</property>

<property>
 <name>hive.exec.local.scratchdir</name>
 <value>/tmp/${user.name}</value>
</property>

<property>
<name>hive.downloaded.resources.dir</name>
<value>/tmp/${user.name}_resources</value>
</property>

<property>
<name>hive.scratch.dir.permission</name>
    <value>733</value>
</property>

Hive : Unable to start metastore issue

Exception :

smartechie:confluent-3.3.0 sudhir.pradhan$ hive

SLF4J: Class path contains multiple SLF4J bindings.SLF4J: Found binding in [jar:file:/usr/local/Cellar/hive/2.1.0/libexec/lib/log4j-slf4j-impl-2.4.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]SLF4J: Found binding in [jar:file:/usr/local/Cellar/hadoop/2.8.0/libexec/share/hadoop/common/lib/slf4j-log4j12-1.7.10.jar!/org/slf4j/impl/StaticLoggerBinder.class]SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.SLF4J: Actual binding is of type [org.apache.logging.slf4j.Log4jLoggerFactory]
Logging initialized using configuration in jar:file:/usr/local/Cellar/hive/2.1.0/libexec/lib/hive-common-2.1.0.jar!/hive-log4j2.properties Async: trueException in thread “main” java.lang.RuntimeException: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:578) at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:518) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:705) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:641) 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:234) at org.apache.hadoop.util.RunJar.main(RunJar.java:148)Caused by: org.apache.hadoop.hive.ql.metadata.HiveException: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient at org.apache.hadoop.hive.ql.metadata.Hive.registerAllFunctionsOnce(Hive.java:226) at org.apache.hadoop.hive.ql.metadata.Hive.<init>(Hive.java:366) at org.apache.hadoop.hive.ql.metadata.Hive.create(Hive.java:310) at org.apache.hadoop.hive.ql.metadata.Hive.getInternal(Hive.java:290) at org.apache.hadoop.hive.ql.metadata.Hive.get(Hive.java:266) at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:545) … 9 moreCaused by: java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1627) at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:80) at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:130) at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:101) at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3317) at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3356) at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3336) at org.apache.hadoop.hive.ql.metadata.Hive.getAllFunctions(Hive.java:3590) at org.apache.hadoop.hive.ql.metadata.Hive.reloadFunctions(Hive.java:236) at org.apache.hadoop.hive.ql.metadata.Hive.registerAllFunctionsOnce(Hive.java:221) … 14 moreCaused by: java.lang.reflect.InvocationTargetException at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1625) … 23 moreCaused by: MetaException(message:Could not connect to meta store using any of the URIs provided. Most recent failure: org.apache.thrift.transport.TTransportException: java.net.ConnectException: Connection refused (Connection refused) at org.apache.thrift.transport.TSocket.open(TSocket.java:226) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open(HiveMetaStoreClient.java:466) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:279) at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.<init>(SessionHiveMetaStoreClient.java:70) at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) at java.lang.reflect.Constructor.newInstance(Constructor.java:423) at org.apache.hadoop.hive.metastore.MetaStoreUtils.newInstance(MetaStoreUtils.java:1625) at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.<init>(RetryingMetaStoreClient.java:80) at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:130) at org.apache.hadoop.hive.metastore.RetryingMetaStoreClient.getProxy(RetryingMetaStoreClient.java:101) at org.apache.hadoop.hive.ql.metadata.Hive.createMetaStoreClient(Hive.java:3317) at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3356) at org.apache.hadoop.hive.ql.metadata.Hive.getMSC(Hive.java:3336) at org.apache.hadoop.hive.ql.metadata.Hive.getAllFunctions(Hive.java:3590) at org.apache.hadoop.hive.ql.metadata.Hive.reloadFunctions(Hive.java:236) at org.apache.hadoop.hive.ql.metadata.Hive.registerAllFunctionsOnce(Hive.java:221) at org.apache.hadoop.hive.ql.metadata.Hive.<init>(Hive.java:366) at org.apache.hadoop.hive.ql.metadata.Hive.create(Hive.java:310) at org.apache.hadoop.hive.ql.metadata.Hive.getInternal(Hive.java:290) at org.apache.hadoop.hive.ql.metadata.Hive.get(Hive.java:266) at org.apache.hadoop.hive.ql.session.SessionState.start(SessionState.java:545) at org.apache.hadoop.hive.ql.session.SessionState.beginStart(SessionState.java:518) at org.apache.hadoop.hive.cli.CliDriver.run(CliDriver.java:705) at org.apache.hadoop.hive.cli.CliDriver.main(CliDriver.java:641) 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:234) at org.apache.hadoop.util.RunJar.main(RunJar.java:148)Caused by: java.net.ConnectException: Connection refused (Connection refused) at java.net.PlainSocketImpl.socketConnect(Native Method) at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350) at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:206) at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188) at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392) at java.net.Socket.connect(Socket.java:589) at org.apache.thrift.transport.TSocket.open(TSocket.java:221) … 31 more) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.open(HiveMetaStoreClient.java:513) at org.apache.hadoop.hive.metastore.HiveMetaStoreClient.<init>(HiveMetaStoreClient.java:279) at org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient.<init>(SessionHiveMetaStoreClient.java:70) … 28 more

 

Solution : Remove all lock files, you should good to go.

rm metastore_db/*.lck

smartechie:confluent-3.3.0 sudhir.pradhan$ ls metastore_db/*.lck

smartechie:confluent-3.3.0 sudhir.pradhan$ ls metastore_db/*.lck

metastore_db/db.lck

dbex.lck