spark-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Mich Talebzadeh <mich.talebza...@gmail.com>
Subject Re: How can I write data to hive with jdbc
Date Wed, 04 Aug 2021 12:52:28 GMT
Actually if read from that table you can just do
val tableName = "joinwarehouse.ods_job_log"
option("dbtable", tableName)

Also I made a typo in the url.

val url = "jdbc:hive2://tidb4ser:11000/default"

Just put default there for the database name.

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 Wed, 4 Aug 2021 at 12:56, Mich Talebzadeh <mich.talebzadeh@gmail.com>
wrote:

> Caused by: org.apache.spark.sql.catalyst.analysis.NoSuchTableException:
> Table or view 'ods_job_log' not found in database 'default';
>
>
> Where is your table? It is looking at *default database*
>
>
> In general this is the format
>
>
>        read_df = spark.read. \
>
>             format("jdbc"). \
>
>             option("url", url). \
>
>             option("dbtable", tableName). \
>
>             option("user", user). \
>
>             option("password", password). \
>
>             option("driver", driver). \
>
>             option("fetchsize", fetchsize). \
>
>             load()
>
>
>
> But you have specified stuff in your url with a different database name
> joinwarehouse
>
>
> ("url"->"jdbc:hive2://tidb4ser:11000/joinwarehouse;user=jztwk;password=123456;hive.server2.proxy.user=jztwk")
>     cfg += ("dbtable"->"ods_job_log")
>     cfg += ("user"->"jztwk")
>     cfg += ("passwrod"-> "123456")
>
> You have repeated your username and password
>
>
> url should be simply
>
>
> val url = "jdbc:hive2://" + "hiveHost" + ":" + "hivePort" + "/default"
>
>
> In your case
>
>
> val url = "jdbc:hive2://tidb4setidb4ser:11000/joinwarehouse"
>
>
> 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 Wed, 4 Aug 2021 at 10:35, igyu <igyu@21cn.com> wrote:
>
>>     var cfg:Map[String,String] = Map()
>>     cfg += ("url"->"jdbc:hive2://tidb4ser:11000/joinwarehouse;user=jztwk;password=123456;hive.server2.proxy.user=jztwk")
>>     cfg += ("dbtable"->"ods_job_log")
>>     cfg += ("user"->"jztwk")
>>     cfg += ("passwrod"-> "123456")
>>     cfg += ("driver"-> "org.apache.hive.jdbc.HiveDriver")
>>
>>     val partitions = new Array[String](1)
>>     partitions(0) = "created"
>>
>>
>>
>>     DF.write.format("jdbc")
>>       .options(cfg)
>> //      .partitionBy(partitions:_*)
>>       .mode(SaveMode.Overwrite)
>>       .insertInto("ods_job_log")
>>
>> Table or view not found: ods_job_log
>> ods_job_log is not in default , it is in joinwarehouse
>> my url set database;
>>
>> Exception in thread "main" org.apache.spark.sql.AnalysisException: Table
>> or view not found: ods_job_log;
>> at
>> org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:47)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$.org$apache$spark$sql$catalyst$analysis$Analyzer$ResolveRelations$$lookupTableFromCatalog(Analyzer.scala:733)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$$anonfun$apply$8.applyOrElse(Analyzer.scala:710)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$$anonfun$apply$8.applyOrElse(Analyzer.scala:708)
>> at
>> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$$anonfun$resolveOperatorsUp$1$$anonfun$apply$1.apply(AnalysisHelper.scala:90)
>> at
>> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$$anonfun$resolveOperatorsUp$1$$anonfun$apply$1.apply(AnalysisHelper.scala:90)
>> at
>> org.apache.spark.sql.catalyst.trees.CurrentOrigin$.withOrigin(TreeNode.scala:70)
>> at
>> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$$anonfun$resolveOperatorsUp$1.apply(AnalysisHelper.scala:89)
>> at
>> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$$anonfun$resolveOperatorsUp$1.apply(AnalysisHelper.scala:86)
>> at
>> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.allowInvokingTransformsInAnalyzer(AnalysisHelper.scala:194)
>> at
>> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$class.resolveOperatorsUp(AnalysisHelper.scala:86)
>> at
>> org.apache.spark.sql.catalyst.plans.logical.LogicalPlan.resolveOperatorsUp(LogicalPlan.scala:29)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$.apply(Analyzer.scala:708)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$.apply(Analyzer.scala:654)
>> at
>> org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1$$anonfun$apply$1.apply(RuleExecutor.scala:87)
>> at
>> org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1$$anonfun$apply$1.apply(RuleExecutor.scala:84)
>> at
>> scala.collection.LinearSeqOptimized$class.foldLeft(LinearSeqOptimized.scala:124)
>> at scala.collection.immutable.List.foldLeft(List.scala:84)
>> at
>> org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1.apply(RuleExecutor.scala:84)
>> at
>> org.apache.spark.sql.catalyst.rules.RuleExecutor$$anonfun$execute$1.apply(RuleExecutor.scala:76)
>> at scala.collection.immutable.List.foreach(List.scala:392)
>> at
>> org.apache.spark.sql.catalyst.rules.RuleExecutor.execute(RuleExecutor.scala:76)
>> at org.apache.spark.sql.catalyst.analysis.Analyzer.org
>> $apache$spark$sql$catalyst$analysis$Analyzer$$executeSameContext(Analyzer.scala:127)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer.execute(Analyzer.scala:121)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer$$anonfun$executeAndCheck$1.apply(Analyzer.scala:106)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer$$anonfun$executeAndCheck$1.apply(Analyzer.scala:105)
>> at
>> org.apache.spark.sql.catalyst.plans.logical.AnalysisHelper$.markInAnalyzer(AnalysisHelper.scala:201)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer.executeAndCheck(Analyzer.scala:105)
>> at
>> org.apache.spark.sql.execution.QueryExecution.analyzed$lzycompute(QueryExecution.scala:57)
>> at
>> org.apache.spark.sql.execution.QueryExecution.analyzed(QueryExecution.scala:55)
>> at
>> org.apache.spark.sql.execution.QueryExecution.assertAnalyzed(QueryExecution.scala:47)
>> at
>> org.apache.spark.sql.execution.QueryExecution.withCachedData$lzycompute(QueryExecution.scala:61)
>> at
>> org.apache.spark.sql.execution.QueryExecution.withCachedData(QueryExecution.scala:60)
>> at
>> org.apache.spark.sql.execution.QueryExecution.optimizedPlan$lzycompute(QueryExecution.scala:66)
>> at
>> org.apache.spark.sql.execution.QueryExecution.optimizedPlan(QueryExecution.scala:66)
>> at
>> org.apache.spark.sql.execution.QueryExecution.sparkPlan$lzycompute(QueryExecution.scala:72)
>> at
>> org.apache.spark.sql.execution.QueryExecution.sparkPlan(QueryExecution.scala:68)
>> at
>> org.apache.spark.sql.execution.QueryExecution.executedPlan$lzycompute(QueryExecution.scala:77)
>> at
>> org.apache.spark.sql.execution.QueryExecution.executedPlan(QueryExecution.scala:77)
>> at
>> org.apache.spark.sql.execution.SQLExecution$$anonfun$withNewExecutionId$1.apply(SQLExecution.scala:76)
>> at
>> org.apache.spark.sql.execution.SQLExecution$.withSQLConfPropagated(SQLExecution.scala:125)
>> at
>> org.apache.spark.sql.execution.SQLExecution$.withNewExecutionId(SQLExecution.scala:73)
>> at
>> org.apache.spark.sql.DataFrameWriter.runCommand(DataFrameWriter.scala:668)
>> at
>> org.apache.spark.sql.DataFrameWriter.insertInto(DataFrameWriter.scala:325)
>> at
>> org.apache.spark.sql.DataFrameWriter.insertInto(DataFrameWriter.scala:311)
>> at com.join.hive.writer.HiveWriter.saveTo(HiveWriter.scala:39)
>> at com.join.synctool$.main(synctool.scala:43)
>> at com.join.synctool.main(synctool.scala)
>> Caused by: org.apache.spark.sql.catalyst.analysis.NoSuchTableException:
>> Table or view 'ods_job_log' not found in database 'default';
>> at
>> org.apache.spark.sql.hive.client.HiveClient$$anonfun$getTable$1.apply(HiveClient.scala:81)
>> at
>> org.apache.spark.sql.hive.client.HiveClient$$anonfun$getTable$1.apply(HiveClient.scala:81)
>> at scala.Option.getOrElse(Option.scala:121)
>> at
>> org.apache.spark.sql.hive.client.HiveClient$class.getTable(HiveClient.scala:81)
>> at
>> org.apache.spark.sql.hive.client.HiveClientImpl.getTable(HiveClientImpl.scala:84)
>> at
>> org.apache.spark.sql.hive.HiveExternalCatalog.getRawTable(HiveExternalCatalog.scala:120)
>> at
>> org.apache.spark.sql.hive.HiveExternalCatalog$$anonfun$getTable$1.apply(HiveExternalCatalog.scala:737)
>> at
>> org.apache.spark.sql.hive.HiveExternalCatalog$$anonfun$getTable$1.apply(HiveExternalCatalog.scala:737)
>> at
>> org.apache.spark.sql.hive.HiveExternalCatalog.withClient(HiveExternalCatalog.scala:99)
>> at
>> org.apache.spark.sql.hive.HiveExternalCatalog.getTable(HiveExternalCatalog.scala:736)
>> at
>> org.apache.spark.sql.catalyst.catalog.ExternalCatalogWithListener.getTable(ExternalCatalogWithListener.scala:146)
>> at
>> org.apache.spark.sql.catalyst.catalog.SessionCatalog.lookupRelation(SessionCatalog.scala:701)
>> at
>> org.apache.spark.sql.catalyst.analysis.Analyzer$ResolveRelations$.org$apache$spark$sql$catalyst$analysis$Analyzer$ResolveRelations$$lookupTableFromCatalog(Analyzer.scala:730)
>>
>> ------------------------------
>> igyu
>>
>

Mime
View raw message