Блог

Главная - Вопросы по программированию - Есть ли способ преобразовать значения строк в ключи столбцов с помощью hive (HQL)?

Есть ли способ преобразовать значения строк в ключи столбцов с помощью hive (HQL)?

#sql #database #hive

#sql #База данных #улей

Вопрос:

У меня одна большая база данных, и некоторые значения отформатированы сложным типом. Например, ключи столбцов ‘sat’ форматируются следующим образом:

 id       sat
5  'a:100, b:200'
6  'b:300, c:150'
7  'some_other_unknown_key:900'

Я хочу преобразовать таблицу в следующий формат и выполнить дополнительную статистику.

 id        a       b        c  some_other_unknown_key
5        100    200    null   null
6        null    300    150   null
7        null    null   null  900

Есть ли какой-нибудь способ сделать это без создания новых таблиц с помощью HQL?

Комментарии:

1. Согласно кодексу поведения, никогда не добавляйте «Спасибо» в конце вопроса.

2. Какую СУБД вы используете? Oracle, MySQL, SQL Server?

3. Я не уверен в СУБД. Другие ребята настроили его и предоставили веб-интерфейс, где я могу запустить запрос HQL. Они называют это HIVE.

Ответ №1:

используйте REGXP_EXTRACT 

 select id, regexp_extract(foo,'a:([0-9] )',1) as a,
           regexp_extract(foo,'b:([0-9] )',1) as b,
           regexp_extract(foo,'c:([0-9] )',1) as c from t;

Комментарии:

1. Спасибо. Это может решить большую часть моей проблемы. Что, если ‘a’, ‘b’, ‘c’ — это случайное значение, и я не знаю, что это на самом деле перед запросом?

2. @oliver: То, что вы ищете, — это динамический свод. Прямого способа сделать это нет. Вы должны использовать другой процедурный язык / скрипт (Java / Shell script) для динамического построения запроса, подобного приведенному выше, перед его запуском. Лучшим подходом является изменение дизайна таблицы и сохранение значений отдельно или с использованием json, поскольку столбец sat выглядит близким к json.

Ответ №2:

Также вы можете попытаться преобразовать свою строку в допустимый JSON (добавить двойные кавычки и фигурные скобки), затем использовать json_tuple для извлечения столбцов, но в любом случае вы должны знать имена столбцов и перечислять их в запросе, hive не может динамически выбирать столбцы.

ДЕМОНСТРАЦИЯ:

 with your_table as (--Use your table instead of this
select stack(3, 5, 'a:100, b:200',
                6, 'b:300, c:150',
                7, 'some_other_unknown_key:900'
             ) as (id, sat) 
)

 select t.id, p.a, p.b, p.c, p.some_other_unknown_key
   from your_table t
        lateral view outer json_tuple(concat('{',regexp_replace(t.sat,'([a-z_A-Z]*):','\"$1\":'),'}'), 'a','b','c','some_other_unknown_key') p as a,b,c,some_other_unknown_key;

Результат:

 OK
id      a       b       c       some_other_unknown_key
5       100     200     NULL    NULL
6       NULL    300     150     NULL
7       NULL    NULL    NULL    900
Time taken: 0.128 seconds, Fetched: 3 row(s)

Если данные содержат некоторые ключи, не перечисленные в запросе, они не будут извлечены.

Метки: