Exception: Columnar conversion not supported for variable length types


#1

What exactly does this exception mean? I’ve been testing the performance of a query as I load more and more data, and the query was working until I got to about 6 days worth of data when it started failing with this exception. Could not find anything in the docs.

Thanks.


#2

Hi

This is not normally a volume related issue.

Can you share your query, schema and some data.

Regards


#3

Sure. Schema (I’ve removed several columns that I think are irrelevant):

mapdql> \d profiles
CREATE TABLE profiles (
id TEXT NOT NULL ENCODING DICT(32),
audience_ids TEXT[] NOT NULL ENCODING DICT(32))

mapdql> \d stories
CREATE TABLE stories (
id TEXT NOT NULL ENCODING DICT(32),
profile_id TEXT NOT NULL ENCODING DICT(32),
dma TEXT ENCODING DICT(32))

Query:

mapdql> select count(p.id) from stories s, profiles p where s.dma = '501' and s.profile_id = p.id and 'interest:afam' = ANY p.audience_ids;
Exception: Columnar conversion not supported for variable length types

Some example data:

story:
mapdql> select * from stories where dma = '501' limit 1;
id|profile_id|dma
2017-06-i-REDACTED|<REDACTED>|501

profile:
mapdql> select * from profiles where id = '<REDACTED>';
id|audience_ids
<REDACTED>|{interest:foodie, interest:luxury-fashion, interest:music, interest:afam}

#4

Hi,

I am going to need some more info.

What cardinalities do you have in the tables here? What kind of results counts are you expecting with full load.

What exact version are you seeing this issue on?

regards


#5

Cardinality is roughly 35M profiles, and 455M stories for the full data set. As for the counts that queries like this will return, its sort of all over the place depending on the story field and value. Tough to even give an average. This error is against mapd-os-3.1.2dev-20170705-384869b-Linux-x86_64-open pulled down from your jenkins server (as noted here: https://github.com/mapd/mapd-core/issues/38#issuecomment-312685348).

Let me know if you need more info.


#6

Any ideas? This is a show stopper in our evaluation of MapD. Thanks in advance.


#7

Hi

I cannot currently reproduce your issue, please give me an idea of the ranges you were getting and when it stopped working so I can synthesize some matching data.

To expedite the resolution of this issue it would be best if you supplied an obsfucated dataset which reproduces the issue.

regards


#8

The query worked correctly up to 5 days worth of data (counts below):

mapdql> select count(*) from profiles;
EXPR$0
3742161
1 rows returned.
Execution time: 16 ms, Total time: 17 ms

mapdql> select count(*) from stories;
EXPR$0
7866571
1 rows returned.
Execution time: 12 ms, Total time: 13 ms

mapdql> select count(p.id) from stories s, profiles p where s.dma = '501' and s.profile_id = p.id and 'interest:hispanic' = ANY p.audience_ids;
EXPR$0
150803
1 rows returned.
Execution time: 24 ms, Total time: 25 ms

With six days worth of data it stopped working:

mapdql> select count(*) from profiles;
EXPR$0
4115349
1 rows returned.
Execution time: 61 ms, Total time: 62 ms

mapdql> select count(*) from stories;
EXPR$0
9178735
1 rows returned.
Execution time: 11 ms, Total time: 11 ms

mapdql> select count(p.id) from stories s, profiles p where s.dma = '501' and s.profile_id = p.id and 'interest:hispanic' = ANY p.audience_ids;
Exception: Columnar conversion not supported for variable length types

I’d be happy to share the test data with you (privately) but each day is several hundred MBs gzipped.


#9

Hi,

MapD has a concept of a fragment. A fragment is the smallest number of rows we will deal with at a time when working on a query. By default the size of a fragment is 32M rows, the size can be adjusted as a parameter to the CREATE TABLE statement. There is also a limit for a single fragment on a single column of 1GB of size (all the data for that column must fit within the 1GB limitation).

In the case we are looking at here we believe that the error is occurring when your inner table profile is crossing a single fragment boundary due to the size of the content being stored in the profile.audience_ids. Currently the code does not support ARRAY handling when multiple fragments occurs.

We are planning to fix this in the future.

An option you could explore currently is a new feature which has landed giving the ability to shard a table for join purposes. In your case if you sharded the tables on id and profile_id the join and created enough shards the number of fragments could be kept at one for each of the sharded joins.

The syntax would be

CREATE TABLE stories(profile_id text, dma text, SHARD KEY (profile_id))  WITH (shard_count = 10);
CREATE TABLE profile(id text, audience_ids text[], SHARD KEY (id), SHARED DICTIONARY (id) REFERENCES stories(profile_id)) WITH (shard_count = 10);

you could try this with a latest build from OS to see if that gets you past your current issue

regards


#10

Thanks @dwayneberry that’s helpful. I will give this work around a try and see what happens. Do you have an ETA on when the real fix for ARRAYs and fragments will land in the open source version?


#11

@dwayneberry is the REFERENCES table(column) bit required? I can’t use these because the other table doesn’t exist when I try to create the table in question (i.e. it’s a circular reference: profiles depends on stories, and stories depends on profiles).


#12

Hi

Sorry my mistake, too much cutting and pasting.

I have corrected the syntax above

regards


#13

@dwayneberry is this an enterprise edition feature only? I’m getting this error with MapD Server Version: 3.1.2-20170719-9b89178 (community edition):

Exception: Sharded tables are not supported yet

#14

Hi

This feature is only available with latest OS (Open Source) build. It will not be in release product for a little while yet until we finalize it.

Regards


#15

Ah, ok. Let me grab the latest build from master and try again…


#16

I tried the latest build from master and the COPY command is causing the server to core dump, so I can’t load any of my test data. :frowning:


#17

Hi

Sorry there is a bug, raised issue to resolve. We are being overly restrictive of shard key type currently

see: https://github.com/mapd/mapd-core/issues/50

Should be resolved soon

regards


#18

Ah great, thanks for letting me know @dwayneberry!


#19

Hi

The issue has been resolved and closed in the latest version of the OS version.

regards


#20

@dwayneberry thanks will give the master build a whirl.