Tag Archives: Hive

[Solved] Spark job failed during runtime. Please check stacktrace for the root cause.

[Solved] ANTRL Compile HiveLexer.g File Error: syntax error: antlr: NoViableAltException(@[])

Project scenario:

The need for programming to obtain data pedigrees is not common for data warehouses, and separate data pedigree work is only necessary when the scale of data reaches a significant level, or the number of reports with complex data production relationships increases to a significant level.
Until scale is reached, manual identification and management is more cost effective.

antlr is the open source syntax parser that automatically generates syntax trees based on input and displays them visually. antlr-Another Tool for Language Recognition, formerly PCCTS, provides a syntax tree for languages including Java, C++, and C# by description to automatically construct a custom language recognizer (recognizer), compiler (parser) and interpreter (translator) framework. ANTLR is available in v2 v3 v4, with most of the Chinese documentation being in v2 and the hive 1.1.0 version mentioning antlr 3.4 in the comments. antlr combines the above by allowing us to define lexical rules for recognizing character streams and syntactic parser rules for interpreting Token streams. ANTLR will then automatically generate the appropriate lexical/syntactic parser based on the syntax file provided by the user. The user can use them to compile the input text and convert it into other forms (e.g. AST-Abstract Syntax Tree).

Use ANTLR version 3.5.2 to parse Hivesql’s source code grammar file for HiveLexer.g, and place the code:

@lexer::header {
package org.apache.hadoop.hive.ql.parse

import org.apache.hadoop.conf.Configuration
import org.apache.hadoop.hive.conf.HiveConf
}

@lexer::members {
  private Configuration hiveConf
  
  public void setHiveConf(Configuration hiveConf) {
    this.hiveConf = hiveConf
  }
  
  protected boolean allowQuotedId() {
    if(hiveConf == null){
      return false
    }
    String supportedQIds = HiveConf.getVar(hiveConf, HiveConf.ConfVars.HIVE_QUOTEDID_SUPPORT)
    return !"none".equals(supportedQIds)
  }
}

This paragraph needs to be annotated so that Java compiled with ANTLR does not contain this package.

Problem description

After this paragraph is annotated with/* * /, it is still compiled and an error is found:

[10:58:10] error(100): HiveLexer.g:42:1: syntax error: antlr: NoViableAltException(9@[])
[10:58:10] error(100): HiveLexer.g:42:2: syntax error: antlr: NoViableAltException(49@[])
[10:58:10] error(100): HiveLexer.g:42:7: syntax error: antlr: MissingTokenException(inserted [@-1,0:0='<missing ACTION>',<4>,42:6] at :)
[10:58:10] error(100): HiveLexer.g:42:8: syntax error: antlr: NoViableAltException(22@[])
[10:58:10] error(100): HiveLexer.g:42:9: syntax error: antlr: NoViableAltException(80@[])
[10:58:10] error(100): HiveLexer.g:42:9: syntax error: antlr: NoViableAltException(80@[])
[10:58:10] error(100): HiveLexer.g:47:1: syntax error: antlr: MissingTokenException(inserted [@-1,0:0='<missing SEMI>',<82>,47:0] at KW_TRUE)

Cause analysis:

Query data discovery

‘; ‘ is a terminator in comment and so error occurred

Solution:

Delete all the original annotation documents or delete them; Delete it.

Problem solved.

Hive operation TMP file viewing content error [How to Solve]

1. Hive operation TMP file viewing content error

Permission denied: user=dr.who, access=READ_EXECUTE, inode="/tmp":hadoopadmin:supergroup:drwx-wx-wx

2. Cause analysis:

In case of insufficient user permissions, you can see that this tmp folder does not have read r permission for other users. The default login user of the page is dr.who users

3. Solution:

1. Change the permissions of TMP

Just execute the code:

hdfs dfs -chmod -R 777 /tmp

2. Modify the default login user of 50070

The core-site.xml can be configured as the user name corresponding to Hadoop

<property>
    <name>hadoop.http.staticuser.user</name>
    <value>username</value>
</property>

Hive: How to Solve dearby database initialization error

Error Messages:

Metastore connection URL:     jdbc:derby:;databaseName=metastore_db;create=true
Metastore Connection Driver :     org.apache.derby.jdbc.EmbeddedDriver
Metastore connection User:     APP
Starting metastore schema initialization to 3.1.0
Initialization script hive-schema-3.1.0.derby.sql
 
Error: FUNCTION 'NUCLEUS_ASCII' already exists. (state=X0Y68,code=30000)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
Underlying cause: java.io.IOException : Schema script failed, errorcode 2
Use --verbose for detailed stacktrace.
*** schemaTool failed ***

Solution:

Go to share/common/lib/guava-27.0-jre.jar in hadoop

and Replace the lib/guava-27.0-jre.jar file in hive.

[Solved] Error while processing statement: FAILED: Execution Error, return code 3 from org.apache.

Error while processing statement: FAILED: Execution Error, return code 3 from org.apache.

This error occurs when executing sql with a collection in the hive on spark engine:
[42000][3] Error while processing statement: FAILED: Execution Error, return code 3 from org.apache.hadoop.hive.ql.exec.spark.SparkTask. Spark job failed during runtime. Please check stacktrace for the root cause.

Solution 1: Switching engine using mr
set hive.execution.engine=mr;

Solution 2:
set mapred.map.tasks.speculative.execution=true
set mapred.reduce.tasks.speculative.execution=true

[Solved] python Connect hive to Install sasl Error: Building wheel for sasl (setup.py) … error

Collecting sasl
  Using cached sasl-0.3.1.tar.gz (44 kB)
Requirement already satisfied: six in c:\programdata\anaconda3\lib\site-packages (from sasl) (1.16.0)
Building wheels for collected packages: sasl
  Building wheel for sasl (setup.py) ... error
  ERROR: Command errored out with exit status 1:
   command: 'C:\ProgramData\Anaconda3\python.exe' -u -c 'import io, os, sys, setuptools, tokenize; sys.argv[0] = '"'"'D:\\users\\jingtao.CENTALINE\\AppData\\Local\\Temp\\pip-install-2scxm6jd\\sasl_bdd59789a4b54e288cc48a38704a6ad4\\setu
p.py'"'"'; __file__='"'"'D:\\users\\jingtao.CENTALINE\\AppData\\Local\\Temp\\pip-install-2scxm6jd\\sasl_bdd59789a4b54e288cc48a38704a6ad4\\setup.py'"'"';f = getattr(tokenize, '"'"'open'"'"', open)(__file__) if os.path.exists(__file__) e
lse io.StringIO('"'"'from setuptools import setup; setup()'"'"');code = f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' bdist_wheel -d 'D:\users\jingtao.CENTALINE\AppData\Local\Te
mp\pip-wheel-ea8xc0cu'
       cwd: D:\users\jingtao.CENTALINE\AppData\Local\Temp\pip-install-2scxm6jd\sasl_bdd59789a4b54e288cc48a38704a6ad4\
  Complete output (28 lines):
  running bdist_wheel
  running build
  running build_py
  creating build
  creating build\lib.win-amd64-3.9
  creating build\lib.win-amd64-3.9\sasl
  copying sasl\__init__.py -> build\lib.win-amd64-3.9\sasl
  running egg_info
  writing sasl.egg-info\PKG-INFO
  writing dependency_links to sasl.egg-info\dependency_links.txt
  writing requirements to sasl.egg-info\requires.txt
  writing top-level names to sasl.egg-info\top_level.txt
  reading manifest file 'sasl.egg-info\SOURCES.txt'
  reading manifest template 'MANIFEST.in'
  adding license file 'LICENSE.txt'
  writing manifest file 'sasl.egg-info\SOURCES.txt'
  copying sasl\saslwrapper.cpp -> build\lib.win-amd64-3.9\sasl
  copying sasl\saslwrapper.h -> build\lib.win-amd64-3.9\sasl
  copying sasl\saslwrapper.pyx -> build\lib.win-amd64-3.9\sasl
  running build_ext
  building 'sasl.saslwrapper' extension
  creating build\temp.win-amd64-3.9
  creating build\temp.win-amd64-3.9\Release
  creating build\temp.win-amd64-3.9\Release\sasl
  C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\MSVC\14.29.30133\bin\HostX86\x64\cl.exe /c /nologo /Ox /W3 /GL /DNDEBUG /MD -Isasl -IC:\ProgramData\Anaconda3\include -IC:\ProgramData\Anaconda3\include -IC:\Prog
ram Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\MSVC\14.29.30133\ATLMFC\include -IC:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\MSVC\14.29.30133\include -IC:\Program Files (x86)\Windows Kits\10\
include\10.0.19041.0\ucrt -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\shared -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\um -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\winrt -IC:\Pr
ogram Files (x86)\Windows Kits\10\include\10.0.19041.0\cppwinrt /EHsc /Tpsasl/saslwrapper.cpp /Fobuild\temp.win-amd64-3.9\Release\sasl/saslwrapper.obj
  saslwrapper.cpp
  D:\users\jingtao.CENTALINE\AppData\Local\Temp\pip-install-2scxm6jd\sasl_bdd59789a4b54e288cc48a38704a6ad4\sasl\saslwrapper.h(22): fatal error C1083: 无法打开包括文件: “sasl/sasl.h”: No such file or directory
  error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio\\2019\\Community\\VC\\Tools\\MSVC\\14.29.30133\\bin\\HostX86\\x64\\cl.exe' failed with exit code 2
  ----------------------------------------
  ERROR: Failed building wheel for sasl
  Running setup.py clean for sasl
Failed to build sasl
Installing collected packages: sasl
    Running setup.py install for sasl ... error
    ERROR: Command errored out with exit status 1:
     command: 'C:\ProgramData\Anaconda3\python.exe' -u -c 'import io, os, sys, setuptools, tokenize; sys.argv[0] = '"'"'D:\\users\\jingtao.CENTALINE\\AppData\\Local\\Temp\\pip-install-2scxm6jd\\sasl_bdd59789a4b54e288cc48a38704a6ad4\\se
tup.py'"'"'; __file__='"'"'D:\\users\\jingtao.CENTALINE\\AppData\\Local\\Temp\\pip-install-2scxm6jd\\sasl_bdd59789a4b54e288cc48a38704a6ad4\\setup.py'"'"';f = getattr(tokenize, '"'"'open'"'"', open)(__file__) if os.path.exists(__file__)
 else io.StringIO('"'"'from setuptools import setup; setup()'"'"');code = f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' install --record 'D:\users\jingtao.CENTALINE\AppData\Loca
l\Temp\pip-record-l_ubu2on\install-record.txt' --single-version-externally-managed --compile --install-headers 'C:\ProgramData\Anaconda3\Include\sasl'
         cwd: D:\users\jingtao.CENTALINE\AppData\Local\Temp\pip-install-2scxm6jd\sasl_bdd59789a4b54e288cc48a38704a6ad4\
    Complete output (28 lines):
    running install
    running build
    running build_py
    creating build
    creating build\lib.win-amd64-3.9
    creating build\lib.win-amd64-3.9\sasl
    copying sasl\__init__.py -> build\lib.win-amd64-3.9\sasl
    running egg_info
    writing sasl.egg-info\PKG-INFO
    writing dependency_links to sasl.egg-info\dependency_links.txt
    writing requirements to sasl.egg-info\requires.txt
    writing top-level names to sasl.egg-info\top_level.txt
    reading manifest file 'sasl.egg-info\SOURCES.txt'
    reading manifest template 'MANIFEST.in'
    adding license file 'LICENSE.txt'
    writing manifest file 'sasl.egg-info\SOURCES.txt'
    copying sasl\saslwrapper.cpp -> build\lib.win-amd64-3.9\sasl
    copying sasl\saslwrapper.h -> build\lib.win-amd64-3.9\sasl
    copying sasl\saslwrapper.pyx -> build\lib.win-amd64-3.9\sasl
    running build_ext
    building 'sasl.saslwrapper' extension
    creating build\temp.win-amd64-3.9
    creating build\temp.win-amd64-3.9\Release
    creating build\temp.win-amd64-3.9\Release\sasl
    C:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\MSVC\14.29.30133\bin\HostX86\x64\cl.exe /c /nologo /Ox /W3 /GL /DNDEBUG /MD -Isasl -IC:\ProgramData\Anaconda3\include -IC:\ProgramData\Anaconda3\include -IC:\Pr
ogram Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\MSVC\14.29.30133\ATLMFC\include -IC:\Program Files (x86)\Microsoft Visual Studio\2019\Community\VC\Tools\MSVC\14.29.30133\include -IC:\Program Files (x86)\Windows Kits\1
0\include\10.0.19041.0\ucrt -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\shared -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\um -IC:\Program Files (x86)\Windows Kits\10\include\10.0.19041.0\winrt -IC:\
Program Files (x86)\Windows Kits\10\include\10.0.19041.0\cppwinrt /EHsc /Tpsasl/saslwrapper.cpp /Fobuild\temp.win-amd64-3.9\Release\sasl/saslwrapper.obj
    saslwrapper.cpp
    D:\users\jingtao.CENTALINE\AppData\Local\Temp\pip-install-2scxm6jd\sasl_bdd59789a4b54e288cc48a38704a6ad4\sasl\saslwrapper.h(22): fatal error C1083: 无法打开包括文件: “sasl/sasl.h”: No such file or directory
    error: command 'C:\\Program Files (x86)\\Microsoft Visual Studio\\2019\\Community\\VC\\Tools\\MSVC\\14.29.30133\\bin\\HostX86\\x64\\cl.exe' failed with exit code 2
    ----------------------------------------
ERROR: Command errored out with exit status 1: 'C:\ProgramData\Anaconda3\python.exe' -u -c 'import io, os, sys, setuptools, tokenize; sys.argv[0] = '"'"'D:\\users\\jingtao.CENTALINE\\AppData\\Local\\Temp\\pip-install-2scxm6jd\\sasl_bdd
59789a4b54e288cc48a38704a6ad4\\setup.py'"'"'; __file__='"'"'D:\\users\\jingtao.CENTALINE\\AppData\\Local\\Temp\\pip-install-2scxm6jd\\sasl_bdd59789a4b54e288cc48a38704a6ad4\\setup.py'"'"';f = getattr(tokenize, '"'"'open'"'"', open)(__fi
le__) if os.path.exists(__file__) else io.StringIO('"'"'from setuptools import setup; setup()'"'"');code = f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' install --record 'D:\use
rs\jingtao.CENTALINE\AppData\Local\Temp\pip-record-l_ubu2on\install-record.txt' --single-version-externally-managed --compile --install-headers 'C:\ProgramData\Anaconda3\Include\sasl' Check the logs for full command output.

Solution:
Download a sasl file that corresponds to the Python version and Windows version you are using: https://www.lfd.uci.edu/~gohlke/pythonlibs/#sasl. For example, sasl-0.2.1-cp36 -cp36m-win_amd64.whl, which corresponds to Python version 3.6, and corresponds to Windows system 64-bit.
Install the executable pip install sasl-0.2.1-cp37-cp37m-win_amd64.whl

[Solved] Schema initialization FAILED Metastore state would be inconsistent

[root@node3 hive-2.3.4]# schematool -dbType mysql -initSchema
SLF4J: Class path contains multiple SLF4J bindings.
SLF4J: Found binding in [jar:file:/opt/hive-2.3.4/lib/log4j-slf4j-impl-2.6.2.jar!/org/slf4j/impl/StaticLoggerBinder.class]
SLF4J: Found binding in [jar:file:/opt/hadoop-2.6.5/share/hadoop/common/lib/slf4j-log4j12-1.7.5.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]
Metastore connection URL:     jdbc:mysql://node1:3306/hive?createDatabaseIfNotExist=true
Metastore Connection Driver :     com.mysql.jdbc.Driver
Metastore connection User:     root
Starting metastore schema initialization to 2.3.0
Initialization script hive-schema-2.3.0.mysql.sql
Error: Duplicate key name 'PCS_STATS_IDX' (state=42000,code=1061)
org.apache.hadoop.hive.metastore.HiveMetaException: Schema initialization FAILED! Metastore state would be inconsistent !!
Underlying cause: java.io.IOException : Schema script failed, errorcode 2
Use --verbose for detailed stacktrace.
*** schemaTool failed ***

Cause: hive failed to initialize the metadatabase
Solution: delete the metadatabase for the hive database in hive-site.xml

Then go to mysql: mysql -uroot -p your password
drop database hive
Exit mysql to reformat
schematool -initSchema -dbType mysql

Ranger Install Error: [E] ranger_core_db_mysql.sql file import failed!

Exception information:

Error executing: CREATE FUNCTION `getXportalUIdByLoginId`(input_val VARCHAR(100)) RETURNS int(11) BEGIN DECLARE myid INT; SELECT x_portal_user.id into myid FROM x_portal_user WHERE x_portal_user.login_id = input_val; RETURN myid; END  
java.sql.SQLException: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)
SQLException : SQL state: HY000 java.sql.SQLException: This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable) ErrorCode: 1418
2022-02-24 18:24:06,439  [E] ranger_core_db_mysql.sql file import failed!
2022-02-24 18:24:06,439  [I] Unable to create DB schema, Please drop the database and try again

...
2022-02-24 18:24:08,667  [E] CORE_DB_SCHEMA import failed!

This error is reported when Ranger is installed

Solution:

SET GLOBAL log_bin_trust_function_creators = 1;

hive Run Error: Error: Java heap space [How to Solve]

Error: Java heap space solution

When using MR engine:

set mapreduce.map.memory.mb=12000;
set mapreduce.reduce.memory.mb=12000;
set mapred.map.child.java.opts=-server -Xmx10000m -Djava.net.preferIPv4Stack=true;
set io.sort.mb=100;
set mapred.reduce.child.java.opts=-server -Xmx10000m -Djava.net.preferIPv4Stack=true;

When using tez engine:

set hive.execution.engine=tez;
set tez.am.resource.memory.mb=9216;
set hive.exec.orc.split.strategy=BI;

[Solved] Hive On Spark Error: Remote Spark Driver – HiveServer2 connection has been closed

Error Messages:

Failed to monitor Job[-1] with exception ‘java.lang.IllegalStateException(Connection to remote Spark driver was lost)’ Last known state = SENT
FAILED: Execution Error, return code 1 from org.apache.hadoop.hive.ql.exec.spark.SparkTask. Unable to send message SyncJobRequest{job=org.apache.hadoop.hive.ql.exec.spark.status.impl.RemoteSparkJobStatus$GetAppIDJob@7805478c} because the Remote Spark Driver - HiveServer2 connection has been closed.

The real cause of this problem requires going to Yarn and looking at the Application’s detailed logs at

It turns out that the executor-memory is too small and needs to be modified in the hive configuration page

Save the changes and restart the relevant components, the problem is solved.

[Solved] Hbase-shell 2.x Error: Unhandled Java exception: java.lang.IncompatibleClassChangeError: Found class jline.Terminal…

I. Error message:

Unhandled Java exception: java.lang.IncompatibleClassChangeError: Found class jline.Terminal, but interface was expected
java.lang.IncompatibleClassChangeError: Found class jline.Terminal, but interface was expected

II. Solution
hadoop/share/hadoop/yarn/lib/ Replace jline-0.9.94.jar package with jline-2.12.jar if you have it, or upload jline-2.12.jar if you don’t

Note: How to obtain jline-2.12.jar:
Can be obtained from the hive package
hive package download: https://downloads.apache.org/hive/hive-2.3.9/apache-hive-2.3.9-bin.tar.gz

[Solved] sqoop Import Datas to mysql error: ERROR tool.ExportTool: Error during export: Export job failed

An error occurred when exporting and importing data from hive warehouse into MySQL database with sqoop tool in Ubuntu pseudo distributed HDFS.

Give it a try

In the folder generated in ~/TMP/sqoop Chen/compile/jar. java. Copy the calss file to the/usr/local/sqoop/lib/folder, and then run the export and Import command again

The above sqoop-chen directory is sqoop user name

Export-Import command:

The above solution:

After running again, the following figure shows that the export and import succeeded:

View MySQL:

ProgrammerAH

Programmer Guide, Tips and Tutorial