StringTokenizerDelim function in MapD


#1

Hi,

I’m trying to use StringTokenizerDelim in MapD table because in my MapD CDR table, comma delimiter is present in STAT column. But I’ m getting exception.

SELECT MSISDN,StringTokenizerDelim(STAT, ‘,’) OVER (PARTITION BY MSISDN ORDER BY MSISDN) FROM CDR ORDER BY 1,2;
Exception: Exception occurred: org.apache.calcite.runtime.CalciteContextException: From line 1, column 18 to line 1, column 52: No match found for function signature StringTokenizerDelim(, )

Thanks


#2

Hi @Ishika -

You have two issues in this query: first, MapD doesn’t support StringTokenizerDelim as a function, nor are window functions supported in our current release.

Are you translating queries from a different system? If you give an indication of what you are hoping to do, we might be able to provide a work-around.

Thanks,
Randy


#3

Hi,

Thanks for reply @randyzwitch.

SELECT * FROM test;
c1 | c2
----±------
1 | A|B|C
2 | D|E|F
(2 rows)

SELECT c1, words FROM (SELECT c1, StringTokenizerDelim(c2, ‘|’) OVER (PARTITION BY c1 ORDER BY c1) FROM test) foo ORDER BY 1, 2;
c1 | words
----±------
1 | A
1 | B
1 | C
2 | D
2 | E
2 | F
(6 rows)

Above Query is of vertica. I want same in MapD(split a string into rows by a given delimiter).

Thanks


#4

Thanks. Yes, in this case, we don’t support this type of column-to-rows query.


#5

Hi @Ishika

We do however support arrays as well as the UNNEST operator, which if you can do the split of c2 pre-import might work. See here for more details.

Regards