Calculate number of days excluding weekends between two columns from table A into a column in Table B in Hive

user_16375676 0
Viewed 96

I'm quite new to Hive and Spark so bear with me here.

I have a python script to load data from table A to table B in Hive using PySpark.

  • In table A, there are 2 columns which are date_A and date_B
  • In table B, I need to calculate the number of days excluding weekends between date_A and date_B into the column "Count"
  • Weekend is Saturday and Sunday

Note on datatype:

  • date_A: timestamp
  • date_B: timestamp
  • count: string

Below is my current script in Python:

df_selected = sqlContext.sql('select id, date_A, date_B,' +
datestr +' AS partition_column '+ 
'FROM bda_A.table_A')

df_selected = df_selected.withColumn('days', f.expr('sequence(date_A, date_B, interval 1 day)'))
.withColumn('weekdays', f.expr('filter(transform(days, day->(day, extract(dow_iso from day))), day -> day.col2 <=5).day'))
.withColumn('count', f.expr('size(weekdays)'))

df_selected.write.partitionBy("partition_column").format('parquet').mode("append")
.saveAsTable("bda_B.table_B", path="/BDA/bda_B/table_B");

The expected output in table B should be as follows:

+---+-------------------+-------------------+-----+
| id|             date_A|             date_B|count|
+---+-------------------+-------------------+-----+
|  1|2021-08-18 00:00:00|2021-08-20 00:00:00|  3.0|     
|  2|2021-08-19 00:00:00|2021-08-24 00:00:00|  4.0|             
+---+-------------------+-------------------+-----+

When I ran the script it returns this error:

cannot resolve 'sequence(bda_A.table_A.`date_A`, bda_A.table_A.`date_B`, interval 1 days)' due to data type mismatch: sequence only supports integral, timestamp or date types;
pyspark apache-spark hive python
1 Answers

Answering my own question, this seems to work. Although the return value (count) is an integer (1, 2, 3.....) instead of (1.0, 2.0, 3.0.....)

df_selected = sqlContext.sql('select id, date_A, date_B,' +
'datediff(date_B, date_A)-((weekofyear(date_B)-weekofyear(date_A))*2)+1 as count,' +
datestr +' AS partition_column '+ 
'FROM bda_A.table_A')
user_163756760
Related