Support Questions
Find answers, ask questions, and share your expertise

Can't insert data with Hive (+ ted service check failed)

New Contributor


Hi,

I'm a new Hive user, I'm trying to insert data with and nothing happens, the command just seems to freeze.

This is what I've tried :

beeline -u "jdbc:hive2://srv-hadoop-3,srv-hadoop-1,srv-hadoop-2/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2" -n hive
0: jdbc:hive2://srv-hadoop-3> use myDb;
No rows affected (0,147 seconds)
0: jdbc:hive2://srv-hadoop-3> create table test_loic (firstname VARCHAR(50), lastname VARCHAR(50)) ROW FORMAT DELIMITED STORED AS ORC;
No rows affected (0,642 seconds)
0: jdbc:hive2://srv-hadoop-3> insert into test_loic values ('john', 'doe');
INFO  : Tez session hasn't been created yet. Opening session



There is no error but the command never ends.


Here is my configuration :

The cluser has 3 nodes with 260 Gb of RAM on each node.

I'm using HDP 2.5.3.0-37

Hive is configured as following :

HiveServer2 Heap Size: 94 Gb
Metastore Heap Size: 31 Gb
Default ORC Stripe Size 64 Mb
Client Heap Size: 1024 Mb
Memory for Map Join, per Map memory threshold: 6Gb
Data per Reducer: 64 Mb
Database : MySQL

Yarn is configured like this :

Memory allocated for all YARN containers on a node: 32 GB
Minimum Container Size (Memory) : 1024 Mb
Maximum Container Size (Memory): 32 Gb

I've tried to run a Tez service check on Ambari and it failed :

Python script has been killed due to timeout after waiting 300 secs
2018-09-05 15:17:03,628 - Using hadoop conf dir: /usr/hdp/2.5.3.0-37/hadoop/conf
2018-09-05 15:17:03,642 - File['/var/lib/ambari-agent/tmp/sample-tez-test'] {'content': 'foo\nbar\nfoo\nbar\nfoo', 'mode': 0755}
2018-09-05 15:17:03,645 - HdfsResource['/tmp/tezsmokeoutput'] {'security_enabled': False, 'hadoop_bin_dir': '/usr/hdp/2.5.3.0-37/hadoop/bin', 'keytab': [EMPTY], 'dfs_type': '', 'default_fs': 'hdfs://srv-hadoop-1:8020', 'hdfs_resource_ignore_file': '/var/lib/ambari-agent/data/.hdfs_resource_ignore', 'hdfs_site': ..., 'kinit_path_local': 'kinit', 'principal_name': [EMPTY], 'user': 'hdfs', 'action': ['delete_on_execute'], 'hadoop_conf_dir': '/usr/hdp/2.5.3.0-37/hadoop/conf', 'type': 'directory', 'immutable_paths': [u'/apps/hive/warehouse', u'/mr-history/done', u'/app-logs', u'/tmp']}
2018-09-05 15:17:03,649 - call['ambari-sudo.sh su hdfs -l -s /bin/bash -c 'curl -sS -L -w '"'"'%{http_code}'"'"' -X GET '"'"'http://srv-hadoop-1:50070/webhdfs/v1/tmp/tezsmokeoutput?op=GETFILESTATUS&user.name=hdfs'"'"' 1>/tmp/tmpmqG3ob 2>/tmp/tmp55Wwfy''] {'logoutput': None, 'quiet': False}
2018-09-05 15:17:03,821 - call returned (0, '')
2018-09-05 15:17:03,824 - HdfsResource['/tmp/tezsmokeinput'] {'security_enabled': False, 'hadoop_bin_dir': '/usr/hdp/2.5.3.0-37/hadoop/bin', 'keytab': [EMPTY], 'dfs_type': '', 'default_fs': 'hdfs://srv-hadoop-1:8020', 'hdfs_resource_ignore_file': '/var/lib/ambari-agent/data/.hdfs_resource_ignore', 'hdfs_site': ..., 'kinit_path_local': 'kinit', 'principal_name': [EMPTY], 'user': 'hdfs', 'owner': 'ambari-qa', 'hadoop_conf_dir': '/usr/hdp/2.5.3.0-37/hadoop/conf', 'type': 'directory', 'action': ['create_on_execute'], 'immutable_paths': [u'/apps/hive/warehouse', u'/mr-history/done', u'/app-logs', u'/tmp']}
2018-09-05 15:17:03,826 - call['ambari-sudo.sh su hdfs -l -s /bin/bash -c 'curl -sS -L -w '"'"'%{http_code}'"'"' -X GET '"'"'http://srv-hadoop-1:50070/webhdfs/v1/tmp/tezsmokeinput?op=GETFILESTATUS&user.name=hdfs'"'"' 1>/tmp/tmp_2WBzV 2>/tmp/tmpG9mIzT''] {'logoutput': None, 'quiet': False}
2018-09-05 15:17:03,978 - call returned (0, '')
2018-09-05 15:17:03,981 - HdfsResource['/tmp/tezsmokeinput/sample-tez-test'] {'security_enabled': False, 'hadoop_bin_dir': '/usr/hdp/2.5.3.0-37/hadoop/bin', 'keytab': [EMPTY], 'source': '/var/lib/ambari-agent/tmp/sample-tez-test', 'dfs_type': '', 'default_fs': 'hdfs://srv-hadoop-1:8020', 'hdfs_resource_ignore_file': '/var/lib/ambari-agent/data/.hdfs_resource_ignore', 'hdfs_site': ..., 'kinit_path_local': 'kinit', 'principal_name': [EMPTY], 'user': 'hdfs', 'owner': 'ambari-qa', 'hadoop_conf_dir': '/usr/hdp/2.5.3.0-37/hadoop/conf', 'type': 'file', 'action': ['create_on_execute'], 'immutable_paths': [u'/apps/hive/warehouse', u'/mr-history/done', u'/app-logs', u'/tmp']}
2018-09-05 15:17:03,983 - call['ambari-sudo.sh su hdfs -l -s /bin/bash -c 'curl -sS -L -w '"'"'%{http_code}'"'"' -X GET '"'"'http://srv-hadoop-1:50070/webhdfs/v1/tmp/tezsmokeinput/sample-tez-test?op=GETFILESTATUS&user.name=hdfs'"'"' 1>/tmp/tmp5HMH_c 2>/tmp/tmpna_9TY''] {'logoutput': None, 'quiet': False}
2018-09-05 15:17:04,137 - call returned (0, '')
2018-09-05 15:17:04,139 - DFS file /tmp/tezsmokeinput/sample-tez-test is identical to /var/lib/ambari-agent/tmp/sample-tez-test, skipping the copying
2018-09-05 15:17:04,146 - Called copy_to_hdfs tarball: tez
2018-09-05 15:17:04,146 - Stack Feature Version Info: Cluster Stack=2.5, Command Stack=None, Command Version=2.5.3.0-37 -> 2.5.3.0-37
2018-09-05 15:17:04,146 - Tarball version was calcuated as 2.5.3.0-37. Use Command Version: True
2018-09-05 15:17:04,147 - Source file: /usr/hdp/2.5.3.0-37/tez/lib/tez.tar.gz , Dest file in HDFS: /hdp/apps/2.5.3.0-37/tez/tez.tar.gz
2018-09-05 15:17:04,147 - Preparing the Tez tarball...
2018-09-05 15:17:04,147 - Stack Feature Version Info: Cluster Stack=2.5, Command Stack=None, Command Version=2.5.3.0-37 -> 2.5.3.0-37
2018-09-05 15:17:04,147 - Tarball version was calcuated as 2.5.3.0-37. Use Command Version: True
2018-09-05 15:17:04,148 - Stack Feature Version Info: Cluster Stack=2.5, Command Stack=None, Command Version=2.5.3.0-37 -> 2.5.3.0-37
2018-09-05 15:17:04,148 - Tarball version was calcuated as 2.5.3.0-37. Use Command Version: True
2018-09-05 15:17:04,149 - Extracting /usr/hdp/2.5.3.0-37/hadoop/mapreduce.tar.gz to /var/lib/ambari-agent/tmp/mapreduce-tarball-krTF0M
2018-09-05 15:17:09,344 - Extracting /usr/hdp/2.5.3.0-37/tez/lib/tez.tar.gz to /var/lib/ambari-agent/tmp/tez-tarball-3oH0aG
2018-09-05 15:17:09,345 - Execute[('tar', '-xf', u'/usr/hdp/2.5.3.0-37/tez/lib/tez.tar.gz', '-C', '/var/lib/ambari-agent/tmp/tez-tarball-3oH0aG/')] {'tries': 3, 'sudo': True, 'try_sleep': 1}
2018-09-05 15:17:10,185 - Execute[('cp', '-a', '/var/lib/ambari-agent/tmp/mapreduce-tarball-krTF0M/hadoop/lib/native', '/var/lib/ambari-agent/tmp/tez-tarball-3oH0aG/lib')] {'sudo': True}
2018-09-05 15:17:10,212 - Stack Feature Version Info: Cluster Stack=2.5, Command Stack=None, Command Version=2.5.3.0-37 -> 2.5.3.0-37
2018-09-05 15:17:10,221 - Directory['/var/lib/ambari-agent/tmp/tez-tarball-3oH0aG/lib'] {'recursive_ownership': True, 'mode': 0755, 'cd_access': 'a'}
2018-09-05 15:17:10,224 - Creating a new Tez tarball at /var/lib/ambari-agent/tmp/tez-native-tarball-staging/tez-native.tar.gz
2018-09-05 15:17:17,513 - HdfsResource['/hdp/apps/2.5.3.0-37/tez'] {'security_enabled': False, 'hadoop_bin_dir': '/usr/hdp/2.5.3.0-37/hadoop/bin', 'keytab': [EMPTY], 'dfs_type': '', 'default_fs': 'hdfs://srv-hadoop-1:8020', 'hdfs_resource_ignore_file': '/var/lib/ambari-agent/data/.hdfs_resource_ignore', 'hdfs_site': ..., 'kinit_path_local': 'kinit', 'principal_name': [EMPTY], 'user': 'hdfs', 'owner': 'hdfs', 'hadoop_conf_dir': '/usr/hdp/2.5.3.0-37/hadoop/conf', 'type': 'directory', 'action': ['create_on_execute'], 'immutable_paths': [u'/apps/hive/warehouse', u'/mr-history/done', u'/app-logs', u'/tmp'], 'mode': 0555}
2018-09-05 15:17:17,518 - call['ambari-sudo.sh su hdfs -l -s /bin/bash -c 'curl -sS -L -w '"'"'%{http_code}'"'"' -X GET '"'"'http://srv-hadoop-1:50070/webhdfs/v1/hdp/apps/2.5.3.0-37/tez?op=GETFILESTATUS&user.name=hdfs'"'"' 1>/tmp/tmpdyJFCI 2>/tmp/tmp_6DqQX''] {'logoutput': None, 'quiet': False}
2018-09-05 15:17:17,687 - call returned (0, '')
2018-09-05 15:17:17,690 - HdfsResource['/hdp/apps/2.5.3.0-37/tez/tez.tar.gz'] {'security_enabled': False, 'hadoop_bin_dir': '/usr/hdp/2.5.3.0-37/hadoop/bin', 'keytab': [EMPTY], 'source': '/var/lib/ambari-agent/tmp/tez-native-tarball-staging/tez-native.tar.gz', 'dfs_type': '', 'default_fs': 'hdfs://srv-hadoop-1:8020', 'replace_existing_files': False, 'hdfs_resource_ignore_file': '/var/lib/ambari-agent/data/.hdfs_resource_ignore', 'hdfs_site': ..., 'kinit_path_local': 'kinit', 'principal_name': [EMPTY], 'user': 'hdfs', 'owner': 'hdfs', 'group': 'hadoop', 'hadoop_conf_dir': '/usr/hdp/2.5.3.0-37/hadoop/conf', 'type': 'file', 'action': ['create_on_execute'], 'immutable_paths': [u'/apps/hive/warehouse', u'/mr-history/done', u'/app-logs', u'/tmp'], 'mode': 0444}
2018-09-05 15:17:17,692 - call['ambari-sudo.sh su hdfs -l -s /bin/bash -c 'curl -sS -L -w '"'"'%{http_code}'"'"' -X GET '"'"'http://srv-hadoop-1:50070/webhdfs/v1/hdp/apps/2.5.3.0-37/tez/tez.tar.gz?op=GETFILESTATUS&user.name=hdfs'"'"' 1>/tmp/tmpCiCd_u 2>/tmp/tmpPCYGGZ''] {'logoutput': None, 'quiet': False}
2018-09-05 15:17:17,843 - call returned (0, '')
2018-09-05 15:17:17,845 - Not replacing existing DFS file /hdp/apps/2.5.3.0-37/tez/tez.tar.gz which is different from /var/lib/ambari-agent/tmp/tez-native-tarball-staging/tez-native.tar.gz, due to replace_existing_files=False
2018-09-05 15:17:17,845 - Will attempt to copy tez tarball from /var/lib/ambari-agent/tmp/tez-native-tarball-staging/tez-native.tar.gz to DFS at /hdp/apps/2.5.3.0-37/tez/tez.tar.gz.
2018-09-05 15:17:17,846 - HdfsResource[None] {'security_enabled': False, 'hadoop_bin_dir': '/usr/hdp/2.5.3.0-37/hadoop/bin', 'keytab': [EMPTY], 'dfs_type': '', 'default_fs': 'hdfs://srv-hadoop-1:8020', 'hdfs_resource_ignore_file': '/var/lib/ambari-agent/data/.hdfs_resource_ignore', 'hdfs_site': ..., 'kinit_path_local': 'kinit', 'principal_name': [EMPTY], 'user': 'hdfs', 'action': ['execute'], 'hadoop_conf_dir': '/usr/hdp/2.5.3.0-37/hadoop/conf', 'immutable_paths': [u'/apps/hive/warehouse', u'/mr-history/done', u'/app-logs', u'/tmp']}
2018-09-05 15:17:17,847 - ExecuteHadoop['jar /usr/hdp/current/tez-client/tez-examples*.jar orderedwordcount /tmp/tezsmokeinput/sample-tez-test /tmp/tezsmokeoutput/'] {'try_sleep': 5, 'tries': 3, 'bin_dir': '/usr/hdp/2.5.3.0-37/hadoop/bin', 'user': 'ambari-qa', 'conf_dir': '/usr/hdp/2.5.3.0-37/hadoop/conf'}
2018-09-05 15:17:17,848 - Execute['hadoop --config /usr/hdp/2.5.3.0-37/hadoop/conf jar /usr/hdp/current/tez-client/tez-examples*.jar orderedwordcount /tmp/tezsmokeinput/sample-tez-test /tmp/tezsmokeoutput/'] {'logoutput': None, 'try_sleep': 5, 'environment': {}, 'tries': 3, 'user': 'ambari-qa', 'path': ['/usr/hdp/2.5.3.0-37/hadoop/bin']}
Command failed after 1 tries

Thanks!

Loïc

5 REPLIES 5

Hello @Loic Descotte!
Could you run the following command and share with us?

beeline -u <YOOUR CONNECTION STRING> -e "set -v;" > /tmp/beeline_parameters.out

Btw, in your output. Are able to see any applicationId? Would be like this:

INFO  : Status: Running (Executing on YARN cluster with App id application_1535997024303_0032)
If so, then execute the following command, and share with us please (change w/ your respective AppId):
yarn logs -applicationId "application_1535997024303_0032

I made a quick test, and here's the outputs:

[hive@node3 ~]$ beeline -u 'jdbc:hive2://node2:2181,node3:2181,node4:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2' -n hive
Connecting to jdbc:hive2://node2:2181,node3:2181,node4:2181/;serviceDiscoveryMode=zooKeeper;zooKeeperNamespace=hiveserver2
Connected to: Apache Hive (version 1.2.1000.2.6.5.0-292)
Driver: Hive JDBC (version 1.2.1000.2.6.5.0-292)
Transaction isolation: TRANSACTION_REPEATABLE_READ
Beeline version 1.2.1000.2.6.5.0-292 by Apache Hive
0: jdbc:hive2://node2> 
0: jdbc:hive2://node2> 
0: jdbc:hive2://node2> create table test_loic (firstname VARCHAR(50), lastname VARCHAR(50)) ROW FORMAT DELIMITED STORED AS ORC; 
No rows affected (1.385 seconds)
0: jdbc:hive2://node2> insert into test_loic values ('john', 'doe');
INFO  : Tez session hasn't been created yet. Opening session
DEBUG : Adding local resource: scheme: "hdfs" host: "node2" port: 8020 file: "/tmp/hive/hive/_tez_session_dir/45443eec-efcb-4b79-ad54-5b41ecfaf73c/hive-hcatalog-core.jar"
INFO  : Dag name: insert into test_loic values ('john', 'doe')(Stage-1)
DEBUG : DagInfo: {"context":"Hive","description":"insert into test_loic values ('john', 'doe')"}
DEBUG : Setting Tez DAG access for queryId=hive_20180906150817_ad77c7d1-cbff-47fc-b079-22591e78073f with viewAclString=hive, modifyStr=hive
INFO  : Status: Running (Executing on YARN cluster with App id application_1535997024303_0032)
--------------------------------------------------------------------------------
        VERTICES      STATUS  TOTAL  COMPLETED  RUNNING  PENDING  FAILED  KILLED
--------------------------------------------------------------------------------
Map 1 ..........   SUCCEEDED      1          1        0        0       0       0
--------------------------------------------------------------------------------
VERTICES: 01/01  [==========================>>] 100%  ELAPSED TIME: 10.74 s    
--------------------------------------------------------------------------------
INFO  : Loading data to table default.test_loic from hdfs://node2:8020/apps/hive/warehouse/test_loic/.hive-staging_hive_2018-09-06_15-08-17_007_4548436970085589642-1/-ext-10000
INFO  : Table default.test_loic stats: [numFiles=1, numRows=1, totalSize=315, rawDataSize=175]
No rows affected (26.135 seconds)
Thanks

New Contributor

I've killed all my pending jobs in yarn and now it is working 🙂

Thanks

Loïc

Hi @Loic Descotte!
Good to know! I'd kindly ask you, to you accept your own answer as best answer, doing so will help the other HCC users to find the best answer faster.
Thanks.

Contributor

how large is this cluster? There's another thread on here discussing similar behavior on small clusters

New Contributor

It's a 3 nodes cluster, 16 cores on each node, 260 Gb Ram on each.