How do I create VARCHAR in a column table DDL?

Question

This DDL for a column table results in CLOB for the id_ and name_ fields.

How can I get VARCHARs instead?

snappy> CREATE TABLE EXAMPLE_COLUMN_TABLE (
    id_ VARCHAR(64),
    name_ VARCHAR(128),
    time_ TIMESTAMP,
    number_ INTEGER
)
USING column
OPTIONS(PARTITION_BY 'time_', buckets '113', PERSISTENT 'ASYNCHRONOUS');

As @jagsr mentioned below, its due to missing VARCHAR type in Spark SQL (https://jira.snappydata.io/browse/SNAP-735). We will be adding explicit handling of VARCHAR for CREATE TABLE in the next release sometime this month. Spark SQL execution engine will still see it as a STRING because there is no concept of size-limited strings, but JDBC/ODBC clients will see it fine. — Sumedh, Aug 07 '16 at 08:10

score 0 · Answer 1 · answered Aug 05 '16 at 05:35

Unfortunately, this is due to lack of a VARCHAR type in Spark SQL. So, we turn this into a 'String' which lands up becoming a CLOB in our data dictionary. In any case, this will be resolved in the upcoming release. VARCHARs will be fully honored. And, String types will in fact be turned into VARCHAR with unspecified length.

For now, can you just use the CAST function?

select CAST(id_ AS VARCHAR(64)), ... from example_column_table

How do I create VARCHAR in a column table DDL?

1 Answers1