You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@spark.apache.org by KhajaAsmath Mohammed <md...@gmail.com> on 2017/02/06 01:19:05 UTC
Cannot read Hive Views in Spark SQL
Hi,
I have a hive view which is basically set of select statements on some
tables. I want to read the hive view and use hive builtin functions
available in spark sql.
I am not able to read that hive view in spark sql but can retreive data in
hive shell.
can't spark access hive views?
Thanks,
Asmath
Re: Cannot read Hive Views in Spark SQL
Posted by KhajaAsmath Mohammed <md...@gmail.com>.
we are on 1.6.1 version of spark under CDH5.7.1
On Mon, Feb 6, 2017 at 2:53 PM, Xiao Li <ga...@gmail.com> wrote:
> Which Spark version are you using?
>
> 2017-02-06 12:25 GMT-05:00 vaquar khan <va...@gmail.com>:
>
>> Did you try MSCK REPAIR TABLE ?
>>
>> Regards,
>> Vaquar Khan
>>
>> On Feb 6, 2017 11:21 AM, "KhajaAsmath Mohammed" <md...@gmail.com>
>> wrote:
>>
>>> I dont think so, i was able to insert overwrite other created tables in
>>> hive using spark sql. The only problem I am facing is, spark is not able
>>> to recognize hive view name. Very strange but not sure where I am doing
>>> wrong in this.
>>>
>>> On Mon, Feb 6, 2017 at 11:03 AM, Jon Gregg <co...@gmail.com> wrote:
>>>
>>>> Confirming that Spark can read newly created views - I just created a
>>>> test view in HDFS and I was able to query it in Spark 1.5 immediately after
>>>> without a refresh. Possibly an issue with your Spark-Hive connection?
>>>>
>>>> Jon
>>>>
>>>> On Sun, Feb 5, 2017 at 9:31 PM, KhajaAsmath Mohammed <
>>>> mdkhajaasmath@gmail.com> wrote:
>>>>
>>>>> Hi Khan,
>>>>>
>>>>> It didn't work in my case. used below code. View is already present in
>>>>> Hive but I cant read that in spark sql. Throwing exception that table not
>>>>> found
>>>>>
>>>>> sqlCtx.refreshTable("schema.hive_view")
>>>>>
>>>>>
>>>>> Thanks,
>>>>>
>>>>> Asmath
>>>>>
>>>>>
>>>>> On Sun, Feb 5, 2017 at 7:56 PM, vaquar khan <va...@gmail.com>
>>>>> wrote:
>>>>>
>>>>>> Hi Ashmath,
>>>>>>
>>>>>> Try refresh table
>>>>>>
>>>>>> // spark is an existing SparkSession
>>>>>> spark.catalog.refreshTable("my_table")
>>>>>>
>>>>>>
>>>>>> http://spark.apache.org/docs/latest/sql-programming-guide.ht
>>>>>> ml#metadata-refreshing
>>>>>>
>>>>>>
>>>>>>
>>>>>> Regards,
>>>>>> Vaquar khan
>>>>>>
>>>>>>
>>>>>>
>>>>>> On Sun, Feb 5, 2017 at 7:19 PM, KhajaAsmath Mohammed <
>>>>>> mdkhajaasmath@gmail.com> wrote:
>>>>>>
>>>>>>> Hi,
>>>>>>>
>>>>>>> I have a hive view which is basically set of select statements on
>>>>>>> some tables. I want to read the hive view and use hive builtin functions
>>>>>>> available in spark sql.
>>>>>>>
>>>>>>> I am not able to read that hive view in spark sql but can retreive
>>>>>>> data in hive shell.
>>>>>>>
>>>>>>> can't spark access hive views?
>>>>>>>
>>>>>>> Thanks,
>>>>>>> Asmath
>>>>>>>
>>>>>>
>>>>>>
>>>>>>
>>>>>> --
>>>>>> Regards,
>>>>>> Vaquar Khan
>>>>>> +1 -224-436-0783 <(224)%20436-0783>
>>>>>>
>>>>>> IT Architect / Lead Consultant
>>>>>> Greater Chicago
>>>>>>
>>>>>
>>>>>
>>>>
>>>
>
Re: Cannot read Hive Views in Spark SQL
Posted by Xiao Li <ga...@gmail.com>.
Which Spark version are you using?
2017-02-06 12:25 GMT-05:00 vaquar khan <va...@gmail.com>:
> Did you try MSCK REPAIR TABLE ?
>
> Regards,
> Vaquar Khan
>
> On Feb 6, 2017 11:21 AM, "KhajaAsmath Mohammed" <md...@gmail.com>
> wrote:
>
>> I dont think so, i was able to insert overwrite other created tables in
>> hive using spark sql. The only problem I am facing is, spark is not able
>> to recognize hive view name. Very strange but not sure where I am doing
>> wrong in this.
>>
>> On Mon, Feb 6, 2017 at 11:03 AM, Jon Gregg <co...@gmail.com> wrote:
>>
>>> Confirming that Spark can read newly created views - I just created a
>>> test view in HDFS and I was able to query it in Spark 1.5 immediately after
>>> without a refresh. Possibly an issue with your Spark-Hive connection?
>>>
>>> Jon
>>>
>>> On Sun, Feb 5, 2017 at 9:31 PM, KhajaAsmath Mohammed <
>>> mdkhajaasmath@gmail.com> wrote:
>>>
>>>> Hi Khan,
>>>>
>>>> It didn't work in my case. used below code. View is already present in
>>>> Hive but I cant read that in spark sql. Throwing exception that table not
>>>> found
>>>>
>>>> sqlCtx.refreshTable("schema.hive_view")
>>>>
>>>>
>>>> Thanks,
>>>>
>>>> Asmath
>>>>
>>>>
>>>> On Sun, Feb 5, 2017 at 7:56 PM, vaquar khan <va...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi Ashmath,
>>>>>
>>>>> Try refresh table
>>>>>
>>>>> // spark is an existing SparkSession
>>>>> spark.catalog.refreshTable("my_table")
>>>>>
>>>>>
>>>>> http://spark.apache.org/docs/latest/sql-programming-guide.ht
>>>>> ml#metadata-refreshing
>>>>>
>>>>>
>>>>>
>>>>> Regards,
>>>>> Vaquar khan
>>>>>
>>>>>
>>>>>
>>>>> On Sun, Feb 5, 2017 at 7:19 PM, KhajaAsmath Mohammed <
>>>>> mdkhajaasmath@gmail.com> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I have a hive view which is basically set of select statements on
>>>>>> some tables. I want to read the hive view and use hive builtin functions
>>>>>> available in spark sql.
>>>>>>
>>>>>> I am not able to read that hive view in spark sql but can retreive
>>>>>> data in hive shell.
>>>>>>
>>>>>> can't spark access hive views?
>>>>>>
>>>>>> Thanks,
>>>>>> Asmath
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Regards,
>>>>> Vaquar Khan
>>>>> +1 -224-436-0783 <(224)%20436-0783>
>>>>>
>>>>> IT Architect / Lead Consultant
>>>>> Greater Chicago
>>>>>
>>>>
>>>>
>>>
>>
Re: Cannot read Hive Views in Spark SQL
Posted by KhajaAsmath Mohammed <md...@gmail.com>.
Tried below in spark shell and in dataframes. None of them worked. Can
access same view in HUE.
scala> hiveObj.refreshTable("dtmlab.vehscan_jackwagon_xml_mart_view")
scala> val sample = sqlContext.sql("select * from
dtmlab.vehscan_jackwagon_xml_mart_view").collect()
org.apache.spark.sql.AnalysisException: Table not found:
`dtmlab`.`vehscan_jackwagon_xml_mart_view`; line 1 pos 21
at
org.apache.spark.sql.catalyst.analysis.package$AnalysisErrorAt.failAnalysis(package.scala:42)
at
org.apache.spark.sql.catalyst.analysis.CheckAnalysis$$anonfun$checkAnalysis$1.apply(CheckAnalysis.scala:54)
at
org.apache.spark.sql.catalyst.analysis.CheckAnalysis$$anonfun$checkAnalysis$1.apply(CheckAnalysis.scala:50)
at
org.apache.spark.sql.catalyst.trees.TreeNode.foreachUp(TreeNode.scala:121)
at
org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$foreachUp$1.apply(TreeNode.scala:120)
at
org.apache.spark.sql.catalyst.trees.TreeNode$$anonfun$foreachUp$1.apply(TreeNode.scala:120)
at scala.collection.immutable.List.foreach(List.scala:318)
at
org.apache.spark.sql.catalyst.trees.TreeNode.foreachUp(TreeNode.scala:120)
at
org.apache.spark.sql.catalyst.analysis.CheckAnalysis$class.checkAnalysis(CheckAnalysis.scala:50)
at
org.apache.spark.sql.catalyst.analysis.Analyzer.checkAnalysis(Analyzer.scala:44)
at
org.apache.spark.sql.execution.QueryExecution.assertAnalyzed(QueryExecution.scala:34)
at org.apache.spark.sql.DataFrame.<init>(DataFrame.scala:133)
at org.apache.spark.sql.DataFrame$.apply(DataFrame.scala:52)
at org.apache.spark.sql.SQLContext.sql(SQLContext.scala:817)
at
$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:29)
at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:34)
at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:36)
at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:38)
at $iwC$$iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:40)
at $iwC$$iwC$$iwC$$iwC$$iwC.<init>(<console>:42)
at $iwC$$iwC$$iwC$$iwC.<init>(<console>:44)
Any suggestions
Thanks,
Asmath
On Mon, Feb 6, 2017 at 11:25 AM, vaquar khan <va...@gmail.com> wrote:
> Did you try MSCK REPAIR TABLE ?
>
> Regards,
> Vaquar Khan
>
> On Feb 6, 2017 11:21 AM, "KhajaAsmath Mohammed" <md...@gmail.com>
> wrote:
>
>> I dont think so, i was able to insert overwrite other created tables in
>> hive using spark sql. The only problem I am facing is, spark is not able
>> to recognize hive view name. Very strange but not sure where I am doing
>> wrong in this.
>>
>> On Mon, Feb 6, 2017 at 11:03 AM, Jon Gregg <co...@gmail.com> wrote:
>>
>>> Confirming that Spark can read newly created views - I just created a
>>> test view in HDFS and I was able to query it in Spark 1.5 immediately after
>>> without a refresh. Possibly an issue with your Spark-Hive connection?
>>>
>>> Jon
>>>
>>> On Sun, Feb 5, 2017 at 9:31 PM, KhajaAsmath Mohammed <
>>> mdkhajaasmath@gmail.com> wrote:
>>>
>>>> Hi Khan,
>>>>
>>>> It didn't work in my case. used below code. View is already present in
>>>> Hive but I cant read that in spark sql. Throwing exception that table not
>>>> found
>>>>
>>>> sqlCtx.refreshTable("schema.hive_view")
>>>>
>>>>
>>>> Thanks,
>>>>
>>>> Asmath
>>>>
>>>>
>>>> On Sun, Feb 5, 2017 at 7:56 PM, vaquar khan <va...@gmail.com>
>>>> wrote:
>>>>
>>>>> Hi Ashmath,
>>>>>
>>>>> Try refresh table
>>>>>
>>>>> // spark is an existing SparkSession
>>>>> spark.catalog.refreshTable("my_table")
>>>>>
>>>>>
>>>>> http://spark.apache.org/docs/latest/sql-programming-guide.ht
>>>>> ml#metadata-refreshing
>>>>>
>>>>>
>>>>>
>>>>> Regards,
>>>>> Vaquar khan
>>>>>
>>>>>
>>>>>
>>>>> On Sun, Feb 5, 2017 at 7:19 PM, KhajaAsmath Mohammed <
>>>>> mdkhajaasmath@gmail.com> wrote:
>>>>>
>>>>>> Hi,
>>>>>>
>>>>>> I have a hive view which is basically set of select statements on
>>>>>> some tables. I want to read the hive view and use hive builtin functions
>>>>>> available in spark sql.
>>>>>>
>>>>>> I am not able to read that hive view in spark sql but can retreive
>>>>>> data in hive shell.
>>>>>>
>>>>>> can't spark access hive views?
>>>>>>
>>>>>> Thanks,
>>>>>> Asmath
>>>>>>
>>>>>
>>>>>
>>>>>
>>>>> --
>>>>> Regards,
>>>>> Vaquar Khan
>>>>> +1 -224-436-0783 <(224)%20436-0783>
>>>>>
>>>>> IT Architect / Lead Consultant
>>>>> Greater Chicago
>>>>>
>>>>
>>>>
>>>
>>
Re: Cannot read Hive Views in Spark SQL
Posted by vaquar khan <va...@gmail.com>.
Did you try MSCK REPAIR TABLE ?
Regards,
Vaquar Khan
On Feb 6, 2017 11:21 AM, "KhajaAsmath Mohammed" <md...@gmail.com>
wrote:
> I dont think so, i was able to insert overwrite other created tables in
> hive using spark sql. The only problem I am facing is, spark is not able
> to recognize hive view name. Very strange but not sure where I am doing
> wrong in this.
>
> On Mon, Feb 6, 2017 at 11:03 AM, Jon Gregg <co...@gmail.com> wrote:
>
>> Confirming that Spark can read newly created views - I just created a
>> test view in HDFS and I was able to query it in Spark 1.5 immediately after
>> without a refresh. Possibly an issue with your Spark-Hive connection?
>>
>> Jon
>>
>> On Sun, Feb 5, 2017 at 9:31 PM, KhajaAsmath Mohammed <
>> mdkhajaasmath@gmail.com> wrote:
>>
>>> Hi Khan,
>>>
>>> It didn't work in my case. used below code. View is already present in
>>> Hive but I cant read that in spark sql. Throwing exception that table not
>>> found
>>>
>>> sqlCtx.refreshTable("schema.hive_view")
>>>
>>>
>>> Thanks,
>>>
>>> Asmath
>>>
>>>
>>> On Sun, Feb 5, 2017 at 7:56 PM, vaquar khan <va...@gmail.com>
>>> wrote:
>>>
>>>> Hi Ashmath,
>>>>
>>>> Try refresh table
>>>>
>>>> // spark is an existing SparkSession
>>>> spark.catalog.refreshTable("my_table")
>>>>
>>>>
>>>> http://spark.apache.org/docs/latest/sql-programming-guide.ht
>>>> ml#metadata-refreshing
>>>>
>>>>
>>>>
>>>> Regards,
>>>> Vaquar khan
>>>>
>>>>
>>>>
>>>> On Sun, Feb 5, 2017 at 7:19 PM, KhajaAsmath Mohammed <
>>>> mdkhajaasmath@gmail.com> wrote:
>>>>
>>>>> Hi,
>>>>>
>>>>> I have a hive view which is basically set of select statements on some
>>>>> tables. I want to read the hive view and use hive builtin functions
>>>>> available in spark sql.
>>>>>
>>>>> I am not able to read that hive view in spark sql but can retreive
>>>>> data in hive shell.
>>>>>
>>>>> can't spark access hive views?
>>>>>
>>>>> Thanks,
>>>>> Asmath
>>>>>
>>>>
>>>>
>>>>
>>>> --
>>>> Regards,
>>>> Vaquar Khan
>>>> +1 -224-436-0783 <(224)%20436-0783>
>>>>
>>>> IT Architect / Lead Consultant
>>>> Greater Chicago
>>>>
>>>
>>>
>>
>
Re: Cannot read Hive Views in Spark SQL
Posted by KhajaAsmath Mohammed <md...@gmail.com>.
I dont think so, i was able to insert overwrite other created tables in
hive using spark sql. The only problem I am facing is, spark is not able
to recognize hive view name. Very strange but not sure where I am doing
wrong in this.
On Mon, Feb 6, 2017 at 11:03 AM, Jon Gregg <co...@gmail.com> wrote:
> Confirming that Spark can read newly created views - I just created a test
> view in HDFS and I was able to query it in Spark 1.5 immediately after
> without a refresh. Possibly an issue with your Spark-Hive connection?
>
> Jon
>
> On Sun, Feb 5, 2017 at 9:31 PM, KhajaAsmath Mohammed <
> mdkhajaasmath@gmail.com> wrote:
>
>> Hi Khan,
>>
>> It didn't work in my case. used below code. View is already present in
>> Hive but I cant read that in spark sql. Throwing exception that table not
>> found
>>
>> sqlCtx.refreshTable("schema.hive_view")
>>
>>
>> Thanks,
>>
>> Asmath
>>
>>
>> On Sun, Feb 5, 2017 at 7:56 PM, vaquar khan <va...@gmail.com>
>> wrote:
>>
>>> Hi Ashmath,
>>>
>>> Try refresh table
>>>
>>> // spark is an existing SparkSession
>>> spark.catalog.refreshTable("my_table")
>>>
>>>
>>> http://spark.apache.org/docs/latest/sql-programming-guide.ht
>>> ml#metadata-refreshing
>>>
>>>
>>>
>>> Regards,
>>> Vaquar khan
>>>
>>>
>>>
>>> On Sun, Feb 5, 2017 at 7:19 PM, KhajaAsmath Mohammed <
>>> mdkhajaasmath@gmail.com> wrote:
>>>
>>>> Hi,
>>>>
>>>> I have a hive view which is basically set of select statements on some
>>>> tables. I want to read the hive view and use hive builtin functions
>>>> available in spark sql.
>>>>
>>>> I am not able to read that hive view in spark sql but can retreive data
>>>> in hive shell.
>>>>
>>>> can't spark access hive views?
>>>>
>>>> Thanks,
>>>> Asmath
>>>>
>>>
>>>
>>>
>>> --
>>> Regards,
>>> Vaquar Khan
>>> +1 -224-436-0783 <(224)%20436-0783>
>>>
>>> IT Architect / Lead Consultant
>>> Greater Chicago
>>>
>>
>>
>
Re: Cannot read Hive Views in Spark SQL
Posted by Jon Gregg <co...@gmail.com>.
Confirming that Spark can read newly created views - I just created a test
view in HDFS and I was able to query it in Spark 1.5 immediately after
without a refresh. Possibly an issue with your Spark-Hive connection?
Jon
On Sun, Feb 5, 2017 at 9:31 PM, KhajaAsmath Mohammed <
mdkhajaasmath@gmail.com> wrote:
> Hi Khan,
>
> It didn't work in my case. used below code. View is already present in
> Hive but I cant read that in spark sql. Throwing exception that table not
> found
>
> sqlCtx.refreshTable("schema.hive_view")
>
>
> Thanks,
>
> Asmath
>
>
> On Sun, Feb 5, 2017 at 7:56 PM, vaquar khan <va...@gmail.com> wrote:
>
>> Hi Ashmath,
>>
>> Try refresh table
>>
>> // spark is an existing SparkSession
>> spark.catalog.refreshTable("my_table")
>>
>>
>> http://spark.apache.org/docs/latest/sql-programming-guide.ht
>> ml#metadata-refreshing
>>
>>
>>
>> Regards,
>> Vaquar khan
>>
>>
>>
>> On Sun, Feb 5, 2017 at 7:19 PM, KhajaAsmath Mohammed <
>> mdkhajaasmath@gmail.com> wrote:
>>
>>> Hi,
>>>
>>> I have a hive view which is basically set of select statements on some
>>> tables. I want to read the hive view and use hive builtin functions
>>> available in spark sql.
>>>
>>> I am not able to read that hive view in spark sql but can retreive data
>>> in hive shell.
>>>
>>> can't spark access hive views?
>>>
>>> Thanks,
>>> Asmath
>>>
>>
>>
>>
>> --
>> Regards,
>> Vaquar Khan
>> +1 -224-436-0783 <(224)%20436-0783>
>>
>> IT Architect / Lead Consultant
>> Greater Chicago
>>
>
>
Re: Cannot read Hive Views in Spark SQL
Posted by KhajaAsmath Mohammed <md...@gmail.com>.
Hi Khan,
It didn't work in my case. used below code. View is already present in Hive
but I cant read that in spark sql. Throwing exception that table not found
sqlCtx.refreshTable("schema.hive_view")
Thanks,
Asmath
On Sun, Feb 5, 2017 at 7:56 PM, vaquar khan <va...@gmail.com> wrote:
> Hi Ashmath,
>
> Try refresh table
>
> // spark is an existing SparkSession
> spark.catalog.refreshTable("my_table")
>
>
> http://spark.apache.org/docs/latest/sql-programming-guide.
> html#metadata-refreshing
>
>
>
> Regards,
> Vaquar khan
>
>
>
> On Sun, Feb 5, 2017 at 7:19 PM, KhajaAsmath Mohammed <
> mdkhajaasmath@gmail.com> wrote:
>
>> Hi,
>>
>> I have a hive view which is basically set of select statements on some
>> tables. I want to read the hive view and use hive builtin functions
>> available in spark sql.
>>
>> I am not able to read that hive view in spark sql but can retreive data
>> in hive shell.
>>
>> can't spark access hive views?
>>
>> Thanks,
>> Asmath
>>
>
>
>
> --
> Regards,
> Vaquar Khan
> +1 -224-436-0783 <(224)%20436-0783>
>
> IT Architect / Lead Consultant
> Greater Chicago
>
Re: Cannot read Hive Views in Spark SQL
Posted by vaquar khan <va...@gmail.com>.
Hi Ashmath,
Try refresh table
// spark is an existing SparkSession
spark.catalog.refreshTable("my_table")
http://spark.apache.org/docs/latest/sql-programming-guide.html#metadata-refreshing
Regards,
Vaquar khan
On Sun, Feb 5, 2017 at 7:19 PM, KhajaAsmath Mohammed <
mdkhajaasmath@gmail.com> wrote:
> Hi,
>
> I have a hive view which is basically set of select statements on some
> tables. I want to read the hive view and use hive builtin functions
> available in spark sql.
>
> I am not able to read that hive view in spark sql but can retreive data in
> hive shell.
>
> can't spark access hive views?
>
> Thanks,
> Asmath
>
--
Regards,
Vaquar Khan
+1 -224-436-0783
IT Architect / Lead Consultant
Greater Chicago