Mobius: Spark Sql : Cannot implicitly convert type 'Microsoft.Spark.CSharp.Sql.DataFrame' to 'System.Collections.IEnumerable'

Question

I am trying to get some records from MySQL table using spark SQL i getting an error while executing that query

i want to get the data according to date of the column the column is having data type is DATETIME in MySQL i am working spark with .Net Technology

here is my query that i am executing

Old:

spark.sqlContext("select count(*), classification_id, to_date(cast('total_start' as date)) from call_stats where campaign_id = 172 and contact_list_id = 6288 and total_start between '2017-07-06 00:00:00' and '2017-07-07 23:59:00' group by to_date(cast('total_start' as date)) , classification_id").Collect();

here total_start is my table column having type datetime i want to get output as a date of total_start, week of total_start, month of a total_start and year of a total_start

Exception

Method or operation is not implemented

Update

after changing qyery To :

select count(*), classification_id, date_format( cast( total_start as date), 'yyyy-MM-dd') from call_stats where campaign_id = 172 and contact_list_id = 6288 and total_start between '2017-07-06 00:00:00' and '2017-07-07 23:59:00' group by date_format( cast( total_start as date), 'yyyy-MM-dd'), classification_id 

got the new exception :

Anonymously Hosted DynamicMethods Assembly : Cannot implicitly convert type 'Microsoft.Spark.CSharp.Sql.DataFrame' to 'System.Collections.IEnumerable'. An explicit conversion exists (are you missing a cast?)

any help will be appreciated

Answer 1

i was using a method which is exists but not in correct way so i just need to change that query like :

select count(*) as count, classification_id, date_format( cast(total_start as date), 'dd-MM-yyyy')as label from call_stats where campaign_id = 172 and contact_list_id = 6288 and total_start between '2017-07-06 00:00:00' and '2017-07-14 23:59:00' group by date_format( cast(total_start as date), 'dd-MM-yyyy') ,classification_id

and same for week, month and year i got the answer for year example of query:

select count(*) as count, classification_id, year(total_start) as label from call_stats where campaign_id = 172 and contact_list_id = 6288 and total_start between '2015-08-01 00:00:00' and '2017-09-22 23:59:00' group by year(total_start) , classification_id

for month example of query is :

select count(*) as count, classification_id, month(total_start) as label from call_stats where campaign_id = 77 and contact_list_id = 6037 and total_start between '2017-04-06 00:00:00' and '2017-05-26 23:59:00' group by month(total_start) , classification_id

for week example of query :

select count(*) as count, classification_id, weekofyear(total_start) as label from call_stats where campaign_id = 172 and contact_list_id = 6288 and total_start between '2017-07-06 00:00:00' and '2017-07-14 23:59:00' group by weekofyear(total_start) , classification_id