[SPARK-31576] Unable to return Hive data into Spark via Hive JDBC driver Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED

• Type: Bug
• Status: Closed
• Priority: Major
• Resolution: Fixed
• Affects Version/s: 2.3.1
• Component/s: Spark Shell, Spark Submit

Environment

hdp 3.0,hadoop 3.1.1，spark 2.3.1

• Assignee: Unassigned
• Reporter: liuzhang
• Created: 27/Apr/20 13:41
• Updated: 30/Apr/20 05:30
• Resolved: 30/Apr/20 05:30

Description

I'm trying to fetch back data in Spark SQL using a JDBC connection to Hive. Unfortunately, when I try to query data that resides in every column I get the following error:

Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference 'test.aname': (possible column names are: aname, score, banji)
at org.apache.hive.service.cli.operation.Operation.toSQLException(Operation.java:335)
at org.apache.hive.service.cli.operation.SQLOperation.prepare(SQLOperation.java:199)

1)  On Hive create a simple table,its name is "test"，it have three column(aname,score,banji),their type both are "String"

2)important code:

object HiveDialect extends JdbcDialect

{ override def canHandle(url: String): Boolean = url.startsWith("jdbc:hive2")|| url.contains("hive2")                                                                                                                    override def quoteIdentifier(colName: String): String = s"`$colName`" }

-------------------------------------------------------------------

object callOffRun {
def main(args: Array[String]): Unit =

{ val spark = SparkSession.builder().enableHiveSupport().getOrCreate() JdbcDialects.registerDialect(HiveDialect)                                                                                 val props = new Properties()                                                                                                          props.put("driver","org.apache.hive.jdbc.HiveDriver")                                  props.put("user","username")                                                         props.put("password","password")                                                           props.put("fetchsize","20")                                                                                                     val table=spark.read .jdbc("jdbc:hive2://xxxxxxxx:10000","test",props)                          table.show() }

}

3)spark-submit ,After running,it have error

Caused by: org.apache.hive.service.cli.HiveSQLException: Error while compiling statement: FAILED: SemanticException [Error 10004]: Line 1:7 Invalid table alias or column reference 'test.aname': (possible column names are: aname, score, banji)

4)table.count() have result 

5) I try some method to print result,They all reported the same error