I have trying to create table in hive from spark itself,
And using local mode it will work what I am trying here is from spark
standalone I want to create the manage table in hive (another spark cluster
basically CDH) using jdbc mode.
When I try that below are the error I am facing.
On Thu, 15 Jul, 2021, 9:55 pm Mich Talebzadeh, <mich.talebza...@gmail.com>
wrote:
> Have you created that table in Hive or are you trying to create it from
> Spark itself.
>
> You Hive is local. In this case you don't need a JDBC connection. Have you
> tried:
>
> df2.write.mode("overwrite").saveAsTable(mydb.mytable)
>
> HTH
>
>
>
>
> view my Linkedin profile
> <https://www.linkedin.com/in/mich-talebzadeh-ph-d-5205b2/>
>
>
>
> *Disclaimer:* Use it at your own risk. Any and all responsibility for any
> loss, damage or destruction of data or any other property which may arise
> from relying on this email's technical content is explicitly disclaimed.
> The author will in no case be liable for any monetary damages arising from
> such loss, damage or destruction.
>
>
>
>
> On Thu, 15 Jul 2021 at 12:51, Badrinath Patchikolla <
> pbadrinath1...@gmail.com> wrote:
>
>> Hi,
>>
>> Trying to write data in spark to the hive as JDBC mode below is the
>> sample code:
>>
>> spark standalone 2.4.7 version
>>
>> 21/07/15 08:04:07 WARN util.NativeCodeLoader: Unable to load
>> native-hadoop library for your platform... using builtin-java classes where
>> applicable
>> Setting default log level to "WARN".
>> To adjust logging level use sc.setLogLevel(newLevel). For SparkR, use
>> setLogLevel(newLevel).
>> Spark context Web UI available at http://localhost:4040
>> Spark context available as 'sc' (master = spark://localhost:7077, app id
>> = app-20210715080414-0817).
>> Spark session available as 'spark'.
>> Welcome to
>> ____ __
>> / __/__ ___ _____/ /__
>> _\ \/ _ \/ _ `/ __/ '_/
>> /___/ .__/\_,_/_/ /_/\_\ version 2.4.7
>> /_/
>>
>> Using Scala version 2.11.12 (OpenJDK 64-Bit Server VM, Java 1.8.0_292)
>> Type in expressions to have them evaluated.
>> Type :help for more information.
>>
>> scala> :paste
>> // Entering paste mode (ctrl-D to finish)
>>
>> val df = Seq(
>> ("John", "Smith", "London"),
>> ("David", "Jones", "India"),
>> ("Michael", "Johnson", "Indonesia"),
>> ("Chris", "Lee", "Brazil"),
>> ("Mike", "Brown", "Russia")
>> ).toDF("first_name", "last_name", "country")
>>
>>
>> df.write
>> .format("jdbc")
>> .option("url",
>> "jdbc:hive2://localhost:10000/foundation;AuthMech=2;UseNativeQuery=0")
>> .option("dbtable", "test.test")
>> .option("user", "admin")
>> .option("password", "admin")
>> .option("driver", "com.cloudera.hive.jdbc41.HS2Driver")
>> .mode("overwrite")
>> .save
>>
>>
>> // Exiting paste mode, now interpreting.
>>
>> java.sql.SQLException: [Cloudera][HiveJDBCDriver](500051) ERROR
>> processing query/statement. Error Code: 40000, SQL state:
>> TStatus(statusCode:ERROR_STATUS,
>> infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while
>> compiling statement: FAILED: ParseException line 1:39 cannot recognize
>> input near '"first_name"' 'TEXT' ',' in column name or primary key or
>> foreign key:28:27,
>> org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:329,
>> org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:207,
>> org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:290,
>> org.apache.hive.service.cli.operation.Operation:run:Operation.java:260,
>> org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:504,
>> org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:490,
>> sun.reflect.GeneratedMethodAccessor13:invoke::-1,
>> sun.reflect.DelegatingMethodAccessorImpl:invoke:DelegatingMethodAccessorImpl.java:43,
>> java.lang.reflect.Method:invoke:Method.java:498,
>> org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:78,
>> org.apache.hive.service.cli.session.HiveSessionProxy:access$000:HiveSessionProxy.java:36,
>> org.apache.hive.service.cli.session.HiveSessionProxy$1:run:HiveSessionProxy.java:63,
>> java.security.AccessController:doPrivileged:AccessController.java:-2,
>> javax.security.auth.Subject:doAs:Subject.java:422,
>> org.apache.hadoop.security.UserGroupInformation:doAs:UserGroupInformation.java:1875,
>> org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:59,
>> com.sun.proxy.$Proxy35:executeStatementAsync::-1,
>> org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:295,
>> org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:507,
>> org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1437,
>> org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1422,
>> org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39,
>> org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39,
>> org.apache.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56,
>> org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286,
>> java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1149,
>> java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:624,
>> java.lang.Thread:run:Thread.java:748,
>> *org.apache.hadoop.hive.ql.parse.ParseException:line 1:39 cannot recognize
>> input near '"first_name"' 'TEXT' ',' in column name or primary key or
>> foreign key:33:6,
>> org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:221,
>> org.apache.hadoop.hive.ql.parse.ParseUtils:parse:ParseUtils.java:75,
>> org.apache.hadoop.hive.ql.parse.ParseUtils:parse:ParseUtils.java:68,
>> org.apache.hadoop.hive.ql.Driver:compile:Driver.java:564,
>> org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1425,
>> org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1398,
>> org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:205],
>> sqlState:42000, errorCode:40000, errorMessage:Error while compiling
>> statement: FAILED: ParseException line 1:39 cannot recognize input near
>> '"first_name"' 'TEXT' ',' in column name or primary key or foreign key),
>> Query: CREATE TABLE test.test("first_name" TEXT , "last_name" TEXT ,
>> "country" TEXT ).
>> at
>> com.cloudera.hiveserver2.hivecommon.api.HS2Client.executeStatementInternal(Unknown
>> Source)
>> at
>> com.cloudera.hiveserver2.hivecommon.api.HS2Client.executeStatement(Unknown
>> Source)
>> at
>> com.cloudera.hiveserver2.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.executeHelper(Unknown
>> Source)
>> at
>> com.cloudera.hiveserver2.hivecommon.dataengine.HiveJDBCNativeQueryExecutor.execute(Unknown
>> Source)
>> at
>> com.cloudera.hiveserver2.jdbc.common.SStatement.executeNoParams(Unknown
>> Source)
>> at
>> com.cloudera.hiveserver2.jdbc.common.SStatement.executeAnyUpdate(Unknown
>> Source)
>> at
>> com.cloudera.hiveserver2.jdbc.common.SStatement.executeUpdate(Unknown
>> Source)
>> at
>> org.apache.spark.sql.execution.datasources.jdbc.JdbcUtils$.createTable(JdbcUtils.scala:863)
>> at
>> org.apache.spark.sql.execution.datasources.jdbc.JdbcRelationProvider.createRelation(JdbcRelationProvider.scala:81)
>> at
>> org.apache.spark.sql.execution.datasources.SaveIntoDataSourceCommand.run(SaveIntoDataSourceCommand.scala:45)
>> at
>> org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult$lzycompute(commands.scala:70)
>> at
>> org.apache.spark.sql.execution.command.ExecutedCommandExec.sideEffectResult(commands.scala:68)
>> at
>> org.apache.spark.sql.execution.command.ExecutedCommandExec.doExecute(commands.scala:86)
>> at
>> org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:131)
>> at
>> org.apache.spark.sql.execution.SparkPlan$$anonfun$execute$1.apply(SparkPlan.scala:127)
>> at
>> org.apache.spark.sql.execution.SparkPlan$$anonfun$executeQuery$1.apply(SparkPlan.scala:155)
>> at
>> org.apache.spark.rdd.RDDOperationScope$.withScope(RDDOperationScope.scala:151)
>> at
>> org.apache.spark.sql.execution.SparkPlan.executeQuery(SparkPlan.scala:152)
>> at org.apache.spark.sql.execution.SparkPlan.execute(SparkPlan.scala:127)
>> at
>> org.apache.spark.sql.execution.QueryExecution.toRdd$lzycompute(QueryExecution.scala:83)
>> at
>> org.apache.spark.sql.execution.QueryExecution.toRdd(QueryExecution.scala:81)
>> at
>> org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:696)
>> at
>> org.apache.spark.sql.DataFrameWriter$$anonfun$runCommand$1.apply(DataFrameWriter.scala:696)
>> at
>> org.apache.spark.sql.execution.SQLExecution$$anonfun$withNewExecutionId$1.apply(SQLExecution.scala:80)
>> at
>> org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:127)
>> at
>> org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:75)
>> at
>> org.apache.spark.sql.DataFrameWriter.runCommand(DataFrameWriter.scala:696)
>> at
>> org.apache.spark.sql.DataFrameWriter.saveToV1Source(DataFrameWriter.scala:305)
>> at org.apache.spark.sql.DataFrameWriter.save(DataFrameWriter.scala:291)
>> ... 48 elided
>> Caused by: com.cloudera.hiveserver2.support.exceptions.GeneralException:
>> [Cloudera][HiveJDBCDriver](500051) ERROR processing query/statement. Error
>> Code: 40000, SQL state: TStatus(statusCode:ERROR_STATUS,
>> infoMessages:[*org.apache.hive.service.cli.HiveSQLException:Error while
>> compiling statement: FAILED: ParseException line 1:39 cannot recognize
>> input near '"first_name"' 'TEXT' ',' in column name or primary key or
>> foreign key:28:27,
>> org.apache.hive.service.cli.operation.Operation:toSQLException:Operation.java:329,
>> org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:207,
>> org.apache.hive.service.cli.operation.SQLOperation:runInternal:SQLOperation.java:290,
>> org.apache.hive.service.cli.operation.Operation:run:Operation.java:260,
>> org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementInternal:HiveSessionImpl.java:504,
>> org.apache.hive.service.cli.session.HiveSessionImpl:executeStatementAsync:HiveSessionImpl.java:490,
>> sun.reflect.GeneratedMethodAccessor13:invoke::-1,
>> sun.reflect.DelegatingMethodAccessorImpl:invoke:DelegatingMethodAccessorImpl.java:43,
>> java.lang.reflect.Method:invoke:Method.java:498,
>> org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:78,
>> org.apache.hive.service.cli.session.HiveSessionProxy:access$000:HiveSessionProxy.java:36,
>> org.apache.hive.service.cli.session.HiveSessionProxy$1:run:HiveSessionProxy.java:63,
>> java.security.AccessController:doPrivileged:AccessController.java:-2,
>> javax.security.auth.Subject:doAs:Subject.java:422,
>> org.apache.hadoop.security.UserGroupInformation:doAs:UserGroupInformation.java:1875,
>> org.apache.hive.service.cli.session.HiveSessionProxy:invoke:HiveSessionProxy.java:59,
>> com.sun.proxy.$Proxy35:executeStatementAsync::-1,
>> org.apache.hive.service.cli.CLIService:executeStatementAsync:CLIService.java:295,
>> org.apache.hive.service.cli.thrift.ThriftCLIService:ExecuteStatement:ThriftCLIService.java:507,
>> org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1437,
>> org.apache.hive.service.rpc.thrift.TCLIService$Processor$ExecuteStatement:getResult:TCLIService.java:1422,
>> org.apache.thrift.ProcessFunction:process:ProcessFunction.java:39,
>> org.apache.thrift.TBaseProcessor:process:TBaseProcessor.java:39,
>> org.apache.hive.service.auth.TSetIpAddressProcessor:process:TSetIpAddressProcessor.java:56,
>> org.apache.thrift.server.TThreadPoolServer$WorkerProcess:run:TThreadPoolServer.java:286,
>> java.util.concurrent.ThreadPoolExecutor:runWorker:ThreadPoolExecutor.java:1149,
>> java.util.concurrent.ThreadPoolExecutor$Worker:run:ThreadPoolExecutor.java:624,
>> java.lang.Thread:run:Thread.java:748,
>> *org.apache.hadoop.hive.ql.parse.ParseException:line 1:39 cannot recognize
>> input near '"first_name"' 'TEXT' ',' in column name or primary key or
>> foreign key:33:6,
>> org.apache.hadoop.hive.ql.parse.ParseDriver:parse:ParseDriver.java:221,
>> org.apache.hadoop.hive.ql.parse.ParseUtils:parse:ParseUtils.java:75,
>> org.apache.hadoop.hive.ql.parse.ParseUtils:parse:ParseUtils.java:68,
>> org.apache.hadoop.hive.ql.Driver:compile:Driver.java:564,
>> org.apache.hadoop.hive.ql.Driver:compileInternal:Driver.java:1425,
>> org.apache.hadoop.hive.ql.Driver:compileAndRespond:Driver.java:1398,
>> org.apache.hive.service.cli.operation.SQLOperation:prepare:SQLOperation.java:205],
>> sqlState:42000, errorCode:40000, errorMessage:Error while compiling
>> statement: FAILED: ParseException line 1:39 cannot recognize input near
>> '"first_name"' 'TEXT' ',' in column name or primary key or foreign key),
>> Query: CREATE TABLE profile_test.person_test ("first_name" TEXT ,
>> "last_name" TEXT , "country" TEXT ).
>> ... 77 more
>>
>>
>> Found similar issue ion Jira :
>>
>> https://issues.apache.org/jira/browse/SPARK-31614
>>
>> There no comments in that and the resolution is Incomplete, is there any
>> way we can do in spark to write data into the hive as JDBC mode.
>>
>> Thanks for any help.
>>
>>
>> Thanks,
>> Badrinath.
>>
>
