Spark Data frame search column starting with a string

Question

I have a requirement to filter a data frame based on a condition that a column value should starts with a predefined string.

I am trying following:

 val domainConfigJSON = sqlContext.read
    .jdbc(url, "CONFIG", prop)
    .select("DID", "CONF", "KEY").filter("key like 'config.*'")

And getting exception:

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'KEY = 'config.*'' at line 1

Using spark: 1.6.1

`KEY` is also a reserved word in MariaDB. – philantrovert Aug 07 '17 at 17:44 — philantrovert, Aug 07 '17 at 17:44

score 34 · Accepted Answer · edited Aug 16 '20 at 11:41

34

You can use the startsWith function present in Column class.

myDataFrame.filter(col("columnName").startswith("PREFIX"))

edited Aug 16 '20 at 11:41

Nick.Mc

18,304
6
61
91

answered Aug 07 '17 at 17:40

jeanr

1,031
8
15

13

I had to use `startswith` - all lower-case – Neil Jun 11 '19 at 15:28
1

`startsWith` gives the error _column is not callable_. – Nick.Mc Aug 16 '20 at 11:41

score 0 · Answer 2 · edited Sep 08 '21 at 11:23

0

I used the same function but I was getting errors then I checked what is the error?

actually, we need to use startsWith(literals: String) but the above function having lowercase startswith().

Ex : df.filter(col("ACCOUNT_NUMBER").startsWith("9"))

edited Sep 08 '21 at 11:23

m4n0

29,823
27
76
89

answered Sep 06 '21 at 04:55

R. RamkumarYugo

31
7

Spark Data frame search column starting with a string

2 Answers2