Presto / Athena - запрос для обнаружения частот атрибутов JSON?

Я определил таблицу Hive, в которой один столбец содержит текст JSON:

CREATE EXTERNAL TABLE IF NOT EXISTS my.rawdata (
  json string
)
PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.apache.hadoop.hive.serde2.OpenCSVSerde'
WITH SERDEPROPERTIES (
   'separatorChar' = '\n',
   'quoteChar' = '\0',
   'escapeChar' = '\r'
)
STORED AS TEXTFILE
LOCATION 's3://mydata/';

Есть ли запрос Presto / Athena, который может перечислить все имена полей, которые встречаются в JSON, и их частоту (то есть общее количество раз, когда атрибут появляется в таблице)?


json amazon-athena presto
person Alex R    schedule 10.04.2017    source источник
comment
P.s. Определение таблицы - беспорядок. OpenCSVSerde? quoteChar? escapeChar?   -  person David דודו Markovitz    schedule 11.04.2017
comment
OpenCSVSerde был одним из вариантов, предоставленных Афиной. Сейчас я использую org.apache.hadoop.hive.serde2.lazy.LazySimpleSerDe, но на самом деле это не имеет никакого значения. Для quoteChar и escapeChar мне просто нужен был символ, которого нет в файле.   -  person Alex R    schedule 23.04.2018

Ответы (2)


arrow_upward
4
arrow_downward

Используйте функции JSON, чтобы проанализировать JSON и превратить его в карту. Затем извлеките ключи и разложите их. Наконец, используйте обычную агрегацию SQL:

SELECT key, count(*)
FROM (
  SELECT map_keys(cast(json_parse(json) AS map(varchar, json))) AS keys
  FROM rawdata
)
CROSS JOIN UNNEST (keys) AS t (key)
GROUP BY key
person David Phillips    schedule 10.04.2017
comment
Ограничено одноуровневыми документами - person David דודו Markovitz; 11.04.2017
comment
P.s. Нет необходимости в подзапросе, UNNEST может выполняться для выражения с псевдонимом keys - person David דודו Markovitz; 11.04.2017
comment
Это прекрасно работает. Другой ответ с использованием регулярного выражения неправильно обрабатывает экранированные кавычки. - person Alex R; 23.04.2018

arrow_upward
0
arrow_downward

  • Поддерживает многоуровневые документы
  • Игнорирует ключи элементов вложенности
select    key
         ,count(*)
from      t cross join 
          unnest (regexp_extract_all(json,'"([^"]+)"\s*:\s*("[^"]+"|[^,{}]+)',1)) u (key)
group by  key
;
person David דודו Markovitz    schedule 10.04.2017
comment
Работает хорошо, мощно. Неправильно использовать регулярное выражение в json, хотя в этом вопросе поле json действительно является типом varchar (строка улья), поэтому регулярное выражение, вероятно, подходит для этого типа данных. Как вы отметили, определение таблицы - беспорядок. - person Davos; 06.03.2018
comment
Кроме того, этот ответ торгует ограничением одноуровневости в другом ответе с опасностью, что если один и тот же ключ появится на нескольких уровнях в json, результат будет неверным. - person Davos; 06.03.2018