Convert timestamp to unix miliseconds

2021-08-04 Thread Tzahi File 
Hi All,

I'm using spark 2.4 and trying to convert a timestamp column to unix with
milliseconds using the unix_timestamp function.

I tried to convert the result to double cast(unix_timestamp(timestamp) as
double).
I also tried using the timestamp format "-MM-dd HH:mm:ss.sss" and no
matter what I already tested I just kept getting the unix time in seconds.

Looking for any new ideas.


Thanks.

Re: How can I write data to hive with jdbc

2021-08-04 Thread Mich Talebzadeh 
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




*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 
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
> 
>
>
>
> *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  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
>>

Re: How can I write data to hive with jdbc

2021-08-04 Thread Mich Talebzadeh 
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




*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  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
>

How can I write data to hive with jdbc

2021-08-04 Thread igyu 
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