массив в строку с помощью prestodb

#amazon-athena #presto #trino

Вопрос:

У меня есть база данных AWS Athena (parquet), которая в консоли Athena возвращает:

.. из следующего запроса:

 SELECT id, 
TYPEOF(organisations) as type,
cardinality(organisations) as len,
organisations
FROM mydb.tbl;

Чтение полной таблицы удаленно в Python (с помощью awswrangler) не удается, потому organisations что это массив (иногда нулевой длины). Я пытался преобразовать organisaions поле в строковое представление массива (или эквивалентный json), но ничего из того, что я пробовал до сих пор, не работает. Любые сладости очень ценятся.

Ответ №1:

Если вы не заботитесь о сохранении organization charoffset имен полей и, вы можете преобразовать массив в JSON , а затем отформатировать его, как varchar с json_format :

 WITH t(organizations) AS (
   VALUES
        ARRAY[ROW('x', 1), ROW('y', 2)],
        ARRAY[ROW('a', 3), ROW('b', 4)],
        ARRAY[]
)
SELECT json_format(CAST(organizations AS JSON))
FROM t

        _col0
-------------------
 [["x",1],["y",2]]
 [["a",3],["b",4]]
 []

1. Да json_format(CAST(organizations AS JSON)) , это работает на меня! Спасибо, Мартин 🙂

2. Теперь я понимаю, что вместо того, чтобы потерять ключи как ключи (т. Е., но сохраненные в массиве), я потерял их полностью. Например [{type=World City, name=Mumbai, Maharashtra, India, country=IN, adm1=IN16, adm2=70184, latitude=18.975, longitude=72.8258}] [["World City","Mumbai, Maharashtra, India","IN","IN16","70184",18.975,72.8258]] , становится . Есть идеи, как сохранить ключи в массивах?

3. Значения будут упорядочены в зависимости от того, как они отображаются в объектах СТРОК, поэтому вы можете предоставить клиенту возможность их получения (например, путем проверки типа поля организации). Существует давняя проблема, связанная с изменением поведения приведения строки->json для получения того, что вы ищете, но это сложно, потому что это изменение, несовместимое с обратной связью: > github.com/trinodb/trino/issues/3536 . Кроме того, может пройти много времени, прежде чем он попадет в Афину.

4. Хорошо, еще раз спасибо, Мартин. Интересно, есть ли тогда другой метод, который можно использовать. Кажется довольно тривиальной проблемой стать блокировщиком проектов!

Ответ №2:

Если элементы вашего массива можно привести к varchar , вы можете преобразовать массив в строку с помощью array_join функции, см. Документы здесь

https://prestodb.io/docs/current/functions/array.html

Например:

 WITH t(organizations) AS (
   VALUES
        ARRAY[1,2,3,4,5],
        ARRAY[]
)
SELECT array_join(organizations, ',')
FROM t

Если ваши массивы содержат более сложные элементы, вы можете сначала использовать transform функцию, чтобы преобразовать их в varchar «на заказ» и передать это в array_join функцию, например:

 WITH t(organisations) AS (
   VALUES
        ARRAY[cast(ROW('Police Scotland', 6) as ROW(organisation varchar, charoffset integer))],
        ARRAY[]
)
SELECT '{' || array_join(transform(organisations, x -> '{organisation='||x.organisation||',charoffset='||cast(x.charoffset as varchar)||'}'),',') || '}'
FROM t

1. Интересно, но очень неясно, как это должно работать, и в документации нет примеров. Тестирование на моем поле массива array_join(organisations, delimiter = ";", null_replacement = "") возвращает SYNTAX_ERROR: line 9:32: Column 'delimiter' cannot be resolved результаты . Требуется ли для этой функции сопоставление?

2. varchar указываются в одинарных кавычках. Но действительно, приведенное выше решение будет работать только тогда , когда элементы массива могут быть приведены varchar , что, похоже, не относится к вашему случаю. Я отредактировал ответ.