CODEWITHSUNDEEP
pythonsql-serverapache-zeppelin
Travis
Travis

Reputation: 687

Apache Zeppelin Python UDF into SQL

I am trying to get a function that I wrote in python to add a new column into an SQL table. I can't figure out how to pass a UDF from that function to the SQL table. I believe that the way to do this is withColumn, I just don't know how to.

The goal is to grab the day/week/year from the SQL table and calculate the month from the given data. The function below works if I set day/week/year equal to values.

Here is the Function: 

def getmonth(day,week,year):
x = datetime.strptime('{}_{}_{}{}'.format(day,week,year,-0), '%d_%W_%Y%w')
month = x.strftime('%m')
udf(getmonth)

The SQL...

DriveConfig = sqlContext.sql(""" 
SELECT 
daymade as day,
weekmade as week,
yearmade as year  
FROM datatable2  """)

This is what my table looks like roughly, I want to add that month column that is between week and year

day week year 2 42 2017 3 2 2011 1 14 2005 ...

Upvotes: 1

Views: 314

Answers (2)

user20929302
user20929302

Reputation: 403

def getmonth(day,week,year):
    x = datetime.strptime('{}_{}_{}{}'.format(day,week,year,-0), '%d_%W_%Y%w')
    month = x.strftime('%m')
    return udf(month)

month = udf(getmonth, IntegerType())

DriveConfig = DriveConfig.withColumn("month", month(DriveConfig.day, DriveConfig.week, DriveConfig.year))

Upvotes: 1

Sean Lange
Sean Lange

Reputation: 33581

Something along these lines. I changed the column names because reserved words should be avoided as they cause confusion and are more difficult to work with.

DECLARE @Something TABLE
(
    MyDay INT
    , MyWeek INT
    , MyYear INT
)

INSERT @Something
(
    MyDay,
    MyWeek,
    MyYear
)
VALUES
(2, 42, 2017)
,(3, 2, 2011)
, (1, 14, 2005)


SELECT DATEADD(day, MyDay, DATEADD(WEEK, MyWeek, CONVERT(CHAR(4), MyYear) + '0101'))
FROM @Something

Upvotes: 1

Related Questions