Сохраняйте числовую точность, используя OBJECT_CONSTRUCT
У меня есть значение валюты в таблице, которую я хочу включить в ОБЪЕКТ. Проблема в том, что похоже, что Snowflake снижает точность до двух десятичных знаков, которую я хочу сохранить при преобразовании в ОБЪЕКТ.
Пример:
select to_number('124.00', 18, 2) desired_output, object_construct('my_key', to_number('124.00', 18, 2)) incorrect_output;
DESIRED_OUTPUT INCORRECT_OUTPUT
124.00 { "my_key": 124 }
DESIRED_OUTPUT сохраняет желаемую точность. Но как только я пытаюсь установить его как значение объекта, оно удаляется. Есть ли способ сохранить формат без преобразования его в VARCHAR?
Ответы 3
В Snowflake появился новый тип: структурированные типы данных.
Структурированный тип — это ARRAY, OBJECT или MAP, который содержит элементы или пары «ключ-значение» с определенными типами данных Snowflake. Ниже приведены примеры структурированных типов:
- МАССИВ ЦЕЛЫХ элементов.
- ОБЪЕКТ с парами ключ-значение VARCHAR и NUMBER.
- MAP, который связывает ключ VARCHAR со значением DOUBLE. https://docs.snowflake.com/en/sql-reference/data-types-structured
Однако:
В настоящее время таблицы, отличные от таблиц Iceberg, не поддерживают структурированные типы. В обычную таблицу нельзя добавить столбец структурированного типа.
Для обычных вариантов вы не можете сохранить тип через них (поэтому решение состоит в том, чтобы закодировать как строку)
OBJECT_CONSTRUCT сохраняет точность источника, просто в вашем примере ваши данные не имеют десятичной точности: 124 == 124,00
Попробуй это:
select
to_number('124.00', 18, 2) desired_output1,
object_construct('my_key', to_number('124.00', 18, 2)) incorrect_output1,
to_number('132.54', 18, 2) desired_output2,
object_construct('my_key', to_number('132.54', 18, 2)) correct_output2;
Я отправил запрос в службу поддержки Snowflake по этому поводу, и они сказали мне, что object_construct «автоматически преобразует числа в их наиболее компактное представление, что означает, что он будет отбрасывать конечные нули после десятичной точки для целочисленных значений». Я не уверен, какую пользу это имеет, но они предложили два решения:
- Преобразуйте значение в строку с помощью
TRIM(TO_CHAR(TO_NUMBER(100, 18, 2), '9999999999999999D00')) - Вручную создайте json вот так
SELECT '{"fieldname": ' || TO_NUMBER(100, 18, 2) || '}';
(100 используется просто как статическое значение для воспроизведения проблемы). Номер 2 кажется мне действительно хакерским решением. В итоге я выбрал вариант 1, поскольку этот вывод загружается в opensearch, и по какой-то причине opensearch принимает строки, когда поле имеет сопоставление типов с плавающей запятой, даже если оно не принимает целые числа.
Да, я понимаю эквивалентность. Проблема в том, что эти данные отправляются в индекс OpenSearch, который довольно строг и будет жаловаться, когда целочисленные значения отправляются в поле, определенное как число с плавающей запятой. Все в порядке, когда значение в Snowflake равно 132,54. Проблема в том, что это точная сумма в долларах.