Questions tagged [user-defined-functions]

1

votes
2

answer
44

Views

How to find a cell that contains parenthesis around a number - e.g. (1)

The formula I'm using is: =IF(SUM(COUNTIF(K6,'*'&{'current','(1)'}&'*')),'within 5 minutes', IF(SUM(COUNTIF(K6,'*'&{'current','(2)'}&'*')),'within 10 minutes', IF(SUM(COUNTIF(K6,'*'&{'current','(3)'}&'*')),'within 15 minutes', IF(SUM(COUNTIF(K6,'*'&{'current','(4)'}&'*')),'within 20 minutes', IF(SUM...
max
1

votes
2

answer
28

Views

replace java command with custom key word?

I have a method that i'm calling a lot: Integer.parseInt(input); Is there a way i can take this text and replace with a shorter key word like parseInput so that i could use it as follows month = parseInput I've been told that Java does not have #define like C does, but is there no other way at all...
Mixtape Mania
1

votes
2

answer
69

Views

SOLVED: How to create UDF for multi-variable Index Match formula

I want to create a UDF for the following Excel index-match formula: {=INDEX($A$1:$J$7,MATCH(B9,$A$1:$A$7,0),MATCH(1,($A$1:$J$1=B10)*($A$2:$J$2=B11),0))} enter image description here AA AA AA BB BB BB CC CC CC a b c a b c a b c 1 10 55 24 48 95 19 5 28 65 2 16 48...
SteveC
1

votes
0

answer
11

Views

Code works perfectly until it's placed into a function

very new to coding so please ignore my ignorance. I am having an issue with my code i typed the code and it was doing what I wanted to it and then when I was trying to place it into user functions it stopped working I think it's a simple error but I cant find it. the error message I'm getting as is...
Jackyboy
1

votes
1

answer
283

Views

Spark UDF written in Java Lambda raises ClassCastException

Here's the exception: java.lang.ClassCastException: cannot assign instance of java.lang.invoke.SerializedLambda to ... of type org.apache.spark.sql.api.java.UDF2 in instance of ... If I don't implement the UDF by Lambda expression, it's ok. Like: private UDF2 funUdf = new UDF2() { @Override public S...
secfree
1

votes
1

answer
675

Views

PySpark - Calling a sub-setting function within a UDF

I have to find neighbors of a specific data point in a pyspark dataframe. a= spark.createDataFrame([('A', [0,1]), ('B', [5,9]), ('D', [13,5])],['Letter', 'distances']) I have created this function that will take in the dataframe (DB) and then check the closest data points to a fixed point (Q) using...
Bryce Ramgovind
1

votes
0

answer
250

Views

UnicodeDecodeError when filtering then counting a pyspark DataFrame in spark-submit

I have a parquet table that I need to filter based on a UDF and then count the results for further processing. This works interactively, but not in spark-submit. I've boiled the issue down to the simplest possible example. Here is the interactive code: In [26]: table = table.filter(F.udf(lambda x: T...
LateCoder
1

votes
0

answer
269

Views

Scala UDFs as external Jars

I have a object where I have written my Udf package com.udf.sample object UDFDetails { def udfIndexOf = (inputValue: String, matchCriteria: String) => { inputValue.toUpperCase().indexOf(matchCriteria) } } If I register it directly then I get the return type as org.apache.spark.sql.expressions.User...
merdem9456
1

votes
0

answer
70

Views

Pyspark Dataframes: Does when(cond,value) always evaluate value?

so I am trying to conditionally apply an udf some_function() to column b1, based on the value in a1. (otherwise don't apply). Using pyspark.sql.functions.when(condition, value) and a simple udf some_function = udf(lambda x: x.translate(...)) df = df.withColumn('c1',when(df.a1 == 1, some_function...
fluxens
1

votes
1

answer
991

Views

java.io.NotSerializableException: org.apache.spark.sql.Column when creating a new column conditionally, using a map of UDFs

I have a data of device IDs with startTime and some feature vectors, which needs to be merged based on hour or weekday_hour. The sample data is as follows: +-----+-------------------+--------------------+ |hh_id| startTime| hash| +-----+-------------------+-------------------...
Sai Kiriti Badam
1

votes
0

answer
141

Views

Spark SQL : custom Hive UDF GenericInternalRow cannot be cast ArrayData

I'm using Spark 1.6 with Scala and R (throught SparkR and SparkLyr) I have a dataframe containing binary data representing a Double 2D array. I want to deserialize binary data with an Hive UDF (for compatibility with R), but Spark crash with the error : java.lang.ClassCastException: org.apache.spark...
OcterA
1

votes
0

answer
238

Views

Amazon Redshift Python UDF with Shapely: permission denied error when trying to use GEOS functions

I have an Amazon Redshift database set up, which contains a table with geographical markers using 3 fields: id, latitude (float), longitude (float). My goal is to write a Python UDF using the shapely library to: Parse a multipolygon in the form of a WKT string, and Return a boolean stating whether t...
user8834864
1

votes
1

answer
51

Views

Speeding Up a User Defined Function

I have user defined function that is tied to 2 other user defined functions and when I update the cell it can take several minutes (5-10) as the file has up to 400k cells, and it is is running through 2 other custom functions to create a date format we use at my work. It seems to be looping through...
Adam H
1

votes
1

answer
357

Views

register two udfs with the same name on sparkSession

During migration form Spark 1.6 to Spark 2.2, I was faced with the problem. Implementation on Spark 1.6 has two UDFs: SparkSQL UDF (implementation of org.apache.spark.sql.api.java.UDF2 registered via SQLContext.udf().register(…)) Java custom UDFs designed for Hive (implementation of org.apache.had...
Lunna
1

votes
1

answer
120

Views

Accessing Spoitify API with Rspotify to obtain genre information for multiple artisrts

I am using RStudio 3.4.4 on a windows 10 machine. I have got a vector of artist names and I am trying to get genre information for them all on spotify. I have successfully set up the API and the RSpotify package is working as expected. I am trying to build up to create a function but I am failing pr...
Polly
1

votes
1

answer
45

Views

Why UDF call is being executed before the WHERE clause in a CTE?

I'm trying to understand why a CTE (Common table expression) with a UDF (User defined function) call is so slow. Table TABLE1 has 10 million rows. The last where clause (ROWN = 1) is filtering and returning only 10 records. For a unknow reason, MYFUNCTION is called million of times (before the WHER...
Zanoni
1

votes
2

answer
54

Views

Excel formula keeps reverting to the original UDF name

I made a UDF called 'GetField' and made a spreadsheet that called it 384 times. Then the boss noted that, in keeping with Excel's own styles, that should really be GETFIELD. So I changed it. But now I can't change the formulas in the sheet to use the new version of the name. If I click in the field...
1

votes
0

answer
119

Views

How to Call AWS Lambda Service from Spark

I would like to take each row of my Spark dataframe and process the data through a microservice that returns a new value (list) that I can then add as a new column. Since the dataframe will be processed concurrently, my thought was to structure this as a UDF that then calls a amazon lambda microserv...
SriK
1

votes
0

answer
58

Views

How to access table's schema from Generic UDF

I have a table with columns that can have two possible data variants 1 or 0 as a field value. As in example bellow: question |John |Ann |Bob |Melisa first |1 |0 |1 |0 second |0 |0 |1 |0 third |1 |1 |1 |0 fourth |1 |0...
Alex Strong
1

votes
1

answer
274

Views

How to pass columns as value in UDF in Spark Scala for checking condition

Here is my data frame uniqueFundamentalSet PeriodId SourceId StatementTypeCode StatementCurrencyId UpdateReason_updateReasonId UpdateReasonComment UpdateReasonComment_languageId UpdateReasonEnumerationId FFAction|!| DataPartition PartitionYear TimeStamp 192730230775 297 182 INC...
Shailendra
1

votes
1

answer
198

Views

How do I use COLLATE based on a function in T-SQL? [duplicate]

This question already has an answer here: COLLATE in UDF does not work as expected 8 answers I have the following function for setting COLLATE for the column @p based on the @c parameter. I was inspired by this answer. CREATE FUNCTION [dbo].[fnsConvert] ( @p NVARCHAR(2000), @c NVARCHAR(2000) ) RETU...
1

votes
2

answer
286

Views

SemanticException [Error 10014] Hive UDF

i'm using apache hive with and UDF function create in eclipse. So when i call the function in my sql query, i see this error: FAILED: SemanticException [Error 10014]: Line 1:7 Wrong arguments 'summary': No matching method for class HiveUDF.TokenizeString with (string). Possible choices: Where is th...
1

votes
1

answer
320

Views

Performance of SQL UDF's vs Javascript UDF's in SnowFlake

We are trying to implement addition of interval values in Snowflake.Since there is no interval data type supported in Snowflake we are trying to implement the same using an UDF. We are converting the intervals into seconds and after getting the total seconds we are converting the total second into t...
indranil
1

votes
0

answer
162

Views

PySpark applying UDF for exponential weighted mean from collect_list array

I'm ultimately hoping to recover similar functionality to that detailed in Pyspark SPARK-22239, which will enable the use of window functions with Pandas user-defined functions. Specifically, I'm performing a timestamp-based windowing of underlying numerical observations, and then computing the expo...
twolffpiggott
1

votes
3

answer
281

Views

UDF Returning Different SQL statements

I am trying to return two different SQL statements in an UDF based on a IF condition. So far I haven't been able to do it correctly, the code below does not work. How can I do this? CREATE FUNCTION BELSIZE.TEST (aSTRING VARCHAR(50)) RETURNS TABLE( column1 INTEGER , column2 INTEGER , column3 VARCH...
VN Khan
0

votes
2

answer
1.5k

Views

scala spark dataframe explode is slow - so, alternate method - create columns and rows from arrays in a column

Scala 2.11.8, spark 2.0.1 The explode function is very slow - so, looking for an alternate method. I think it is possible with RDD's with flatmap - and, help is greatly appreciated. I have an udf that returns List(String, String, String, Int) of varying lengths. For each row in the dataframe, I wa...
Terry
1

votes
0

answer
16

Views

R: Helper function that checks arguments within an environment

In R (3.4.3), I'm trying to make my code more succinct before I move it to a package. I'm struggling to find a simple way to check actual arguments passed to multiple functions within an environment, especially when some of those arguments have default values. My goal is not paste redundant code in...
shea.fyffe
1

votes
0

answer
146

Views

getting a unexpected EOF error when running a udf in kapacitor

i wrote a simple udf for kapacitor that is basically changing one of the filed values,should be very simple and straightforward. when i run it i get an error like so : ts=2018-06-07T10:56:52.957Z lvl=error msg='node failed' service=kapacitor task_master=main task=anom_generator node=anomalyGen2 err...
David Gidony
1

votes
1

answer
39

Views

R: Function varies the length of its output when used with data.table

I have the problem, that the use of a user-defined function in data.table varies the functions output. I have constructed a simple version which has the same problem: library(data.table) tmp.f
user9938203
1

votes
1

answer
501

Views

how to get lib_mysqludf_sys.dll file on windows

Anybody knows where can i find the lib_mysqludf_sys.dll file? It doesn't matter if it is 32bits or 64. I've been looking but i can't find it. Or ther is a way to install the file instead of download it?
1

votes
0

answer
83

Views

Ngrams works in Hive but not in spark-shell (Scala)

I really stuck in my issue and I searched extensively in the Net, but I couldn't find a solution for that, and I'm new to Spark-shell (Scala). ngrams function works in Hive perfectly fine by the below command: select ngrams(split(name, '\\W+'), 2, 3) from mytable which returns top 3 bigram of column...
Dio
1

votes
1

answer
47

Views

MS Access 2016 can't find my function / module

I'm still new to VBA, but I'm having an issue trying to automate some function. Namely, I can't seem to be able to run it in immediate (Ctrl+G), and when I try to call it in a Macro, I get the can't find the named function error. It works when I run the code with no variables, like emailPaste(), an...
Kevin F
1

votes
1

answer
300

Views

How to fix this type error, after using UDF function in Spark SQL?

I want to explode my features(type: sparse vector of ml.linalg) as each feature's index and value,so I do the following things: def zipKeyValue(vec:linalg.Vector) : Array[(Int,Double)] = { val indice:Array[Int] = vec.toSparse.indices; val value:Array[Double] = vec.toSparse.values; indice.zip(value)...
nick_liu
1

votes
0

answer
68

Views

Anyway to log information from a UDF in Databricks-spark?

I am creating some scala UDFs to process data in a Dataframe, and am wondering if it is possible to log information within a UDF? Any examples on how to do this?
test acc
1

votes
1

answer
26

Views

HIVE UDF class missing

I have created below UDF functions : package co.hive.udf; import java.security.*; import javax.crypto.*; import javax.crypto.spec.SecretKeySpec; import sun.misc.*; import org.apache.hadoop.hive.ql.exec.UDF; public class encryptHivecolumn extends UDF { private static final String ALGO = 'AES'; privat...
pawan likhi
1

votes
0

answer
59

Views

org.apache.spark.SparkException: Job aborted due to stage failure: Task not serializable

While executing the apache spark example the below error is coming org.apache.spark.SparkException: Job aborted due to stage failure: Task not serializable: java.io.NotSerializableException: org.apache.spark.sql.TypedColumn The error is coming while executing last line. import org.apache.spark.sq...
Deepak Gupta
1

votes
1

answer
245

Views

“This function takes no arguments” error when calling UDF

I'm writing a user defined function for an Excel worksheet and having trouble calling it. Every time I try to insert it, the Function Arguments window says 'This function takes no arguments'. It does, in fact, take arguments. I can modify it in the formula bar to pass an argument but it still just d...
shakyjake
1

votes
1

answer
56

Views

mariadb user defined aggregate function

I am using mariadb 10.3.9, and have created a user defined aggregate function (UDAF) and placed in a common_schema. This schema contains my utility functions to be used by other schema/databases on the same server. The issue is that when calling the UDAF while using any other schema, it always retur...
Ibrahim
1

votes
0

answer
37

Views

Is it possible to use target row to initialize a Spark UDAF?

I have a problem that I am trying to solve in Spark by defining my own UDAF by trying to mimic the recommendations given here and here. My eventual goal is to apply a series of complex bit-shifting and bit-wise boolean manipulations to a sequence of integers within a given window. I am having issues...
PMende
1

votes
1

answer
41

Views

Extract OrderID from Text MySql

I want to extract a number like this 306-3584295-9664328 from a text column. The format is always the same, but in front of the number could be text after this number as well. For ex. Blblblbla xxx-xxxxxxx-xxxxxxx Blblalblala I found a regex for this \w\d{2}-?\d{7}-?\d{7} but I cant use it with mysq...
Christian Mazilu

View additional questions