Change default precision and scale for NUMERIC fields

smontielsmontiel Vertica Customer
in General Discussion

We recently noticed that some operations such as division, square root or power involving decimals would sometimes produce a column with a NUMERIC field when not specified by a table schema. The NUMERIC type is fine with us, but some fields are being output with values for precision and scale outside of the PARQUET standard for exporting data out of Vertica.

We can CAST the fields, but we were hoping there was a way to change the default behavior of Vertica by not going above the 38 digits of precision or the 9 digits of scale.

Tagged:

Answers

  • anatolianatoli Vertica Employee Employee

    FYI we just increased (in Vertica 24.4) the supported precision of NUMERIC in Parquet format from 38 to 153, which should help with this issue.

  • scherepanovscherepanov ✭✭
    edited September 13

    Hi @anatoli ,

    Would be nice if Vertica start exporting to parquet TIME datatype. Right now, export to Parquet outright reject to work if source contains TIME. Nice workaround would be if you will export TIME as INT, either number of micros since unix epoch or just internal INT presentation of datatype (i.e. number of micros since PgSQL epoch). Will be OK if export to parquet will require additional parameter to do conversion from TIME to INT. Just please do not reject exporting to parquet.

    Right now it is a big hassle to export tables with TIME datatype - you need to run python to find if source table has TIME columns, generate SQL that will do conversion, and run export to Parquet using this SQL.

    We do use TIME datatype pretty much in all tables, especially very big, as it is matching how business needs to query data - range of days, with time in some range for every day.

    Generally speaking, best solution is just to dump to Parquet internal Vertica representation without any changes, if Parquet not supporting datatype natively. That makes querying Parquet very straightforward. I am using c++ scalar UDX for conversion between INT and various DATE/time/timetz/timestamp/timestamptz/interrval/intervalyd, here is link https://github.com/scherepanov/verticaudxutilities/blob/main/docs/DATE_TIME_CONVERSIONS.md. Conversion to/from string and float should be avoided.

    Thank you
    Sergey

  • SruthiASruthiA Administrator

    @scherepanov we are supporting TIME with PARQUET starting 24.3 and it is backported to all versions till 23.4

    https://docs.vertica.com/24.3.x/en/sql-reference/statements/copy/parsers/parquet/

This discussion has been closed.